生产数据库比开发数据库慢得多？

Question

我正在这两台服务器上运行一个脚本，一个在我的docker本地开发系统中运行，另一个在生产上运行。但是，相同的脚本在生产上的运行要比在开发上慢得多。我在这两种情况下都使用MariaDB 10.5。我的开发系统有12 GB内存，而我的产品是32 GB RAM。它们都是SSD硬盘。我暂时关闭了nginx，并在午夜进行测试，那里基本上没有人提出要求。它也是完全相同的数据库。我还在MySQL控制台上直接运行了这些查询，但是速度仍然要慢得多。此外，在发展和生产方面也没有触发因素。

这在我的发展过程中：

 UPDATE location SET postal_code = REPLACE(postal_code, ' ', '') ;  270.34
 UPDATE location SET postal_code = TRIM(postal_code) ;  292.88
 UPDATE location SET postal_code = REPLACE(postal_code, '       ', ' '); ;  263.64
 UPDATE location SET postal_code = REPLACE(postal_code, '
', ' '); ;  265.08
 UPDATE location SET postal_code = REGEXP_REPLACE(postal_code, '[[:space:]]+', ' '); ;  332.59
 UPDATE location SET postal_code = REPLACE(postal_code, '+', ' ') ;  266.14
 UPDATE location SET postal_code = REPLACE(postal_code, '*', '') ;  275.96
 UPDATE location SET postal_code = REPLACE(postal_code, '%', '') ;  267.72
 UPDATE location SET postal_code = REPLACE(postal_code, '这是我的作品：UPDATE location SET postal_code = REPLACE(postal_code, ' ', '') ;  2188.6
UPDATE location SET postal_code = TRIM(postal_code) ;  2082.06
UPDATE location SET postal_code = REPLACE(postal_code, '       ', ' '); ;  2073.88
UPDATE location SET postal_code = REPLACE(postal_code, '
', ' '); ;  2042.94
UPDATE location SET postal_code = REGEXP_REPLACE(postal_code, '[[:space:]]+', ' '); ;  2086.55
UPDATE location SET postal_code = REPLACE(postal_code, '+', ' ') ;  2004
UPDATE location SET postal_code = REPLACE(postal_code, '*', '') ;  1997.51
UPDATE location SET postal_code = REPLACE(postal_code, '%', '') ;  2076.03
UPDATE location SET postal_code = REPLACE(postal_code, '我不是在找人来解决这个问题，但我该怎么调查呢？可能的罪魁祸首是什么？运行脚本时对开发的htop在运行脚本时在生产中使用htopiotop有相似的结果。@Vérace这是SHOW CREATE TABLE location;在生产和开发中的输出：CREATE TABLE `location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `location_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `unit` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `street_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `street_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `postal_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `province` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'ONTARIO',
  `country` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'CANADA',
  `latitude` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `longitude` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `house_size` int(10) unsigned DEFAULT NULL,
  `house_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ac_exists_boolean` enum('Y','N') COLLATE utf8_unicode_ci DEFAULT NULL,
  `previous_ac_age` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `furnace_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `previous_furnace_age` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `previous_furnace_efficiency` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `electrical_panel_full` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `current_fuel_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `new_fuel_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `gas_conversion` enum('Y','N') COLLATE utf8_unicode_ci DEFAULT NULL,
  `geo_pin` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `geo_pin_valid_boolean` enum('Y','N') COLLATE utf8_unicode_ci DEFAULT NULL,
  `gas_meter_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `nosi` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` mediumtext COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `location_id` (`location_id`),
  KEY `location_street_number_idx` (`street_number`),
  KEY `location_street_name_idx` (`street_name`)
) ENGINE=InnoDB AUTO_INCREMENT=844557 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci@ SHOW ENGINE INNODB STATUS的输出。生产数据库在左边。https://www.diffchecker.com/yMBZnEQV@Vérace，我添加了my.cnf变量的差异。左边是生产的，右边的是发展的。https://www.diffchecker.com/PtgE7sHH这是mysqltuner.pl在生产上的输出。MySQLTuner 1.8.1 - Major Hayden 
[OK] Currently running supported MySQL version 10.5.12-MariaDB-1:10.5.12+maria~focal
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file  doesn't exist
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 15.5G (Tables: 1668)
[--] Data in MyISAM tables: 897.5K (Tables: 59)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 22h 15m 0s (13M q [80.031 qps], 621K conn, TX: 94G, RX: 1G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 31.4G
[--] Max MySQL memory    : 19.4G
[--] Other process memory: 0B
[--] Total buffers: 16.5G global + 19.5M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 19.4G (61.90% of installed RAM)
[OK] Maximum possible memory usage: 19.4G (61.84% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (151/13M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Aborted connections: 0.07%  (414/621325)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (163 temp sorts / 312K sorts)
[!!] Joins performed without indexes: 1054
[!!] Temporary tables created on disk: 77% (262K on disk / 338K total)
[OK] Thread cache hit rate: 99% (353 created / 621K connections)
[OK] Table cache hit rate: 99% (22M hits / 22M requests)
[OK] table_definition_cache(3000) is upper than number of tables(2085)
[OK] Open file limit used: 0% (162/32K)
[OK] Table locks acquired immediately: 100% (5K immediate / 5K locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.5.12-MariaDB-1:10.5.12+maria~focal)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/59.0K
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 16.0G/15.5G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 4.0G * 1/16.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk : 128 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (12593475773 hits/ 12593738654 total)
[!!] InnoDB Write Log efficiency: 246.44% (77137 hits/ 31300 total)
[OK] InnoDB log waits: 0.00% (0 waits / 108437 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/328.0K
[OK] Aria pagecache hit rate: 97.5% (10M cached / 255K reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Reduce or eliminate persistent connections to reduce connection usage
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    join_buffer_size (> 1.0M, or always use indexes with JOINs)编辑：我还从开发中提取了相同的my.cnf，并在生产中使用。还是很慢。所以我不认为它是任何一个mariadb变量。我还运行了命令mysqlcheck -u root -p --auto-repair --optimize --all-databases，但没有成功。Edit 2:生产中ulimit -a的输出左，开发右https://www.diffchecker.com/OJWhXxwiEdit 3 SHOW TABLE STATUS LIKE "location"在开发中的输出MariaDB [phs]> SHOW TABLE STATUS LIKE "location";
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name     | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment | Max_index_length | Temporary |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| location | InnoDB |      10 | Dynamic    | 227116 |             99 |    22593536 |               0 |     43122688 |  29360128 |         844557 | 2021-08-29 16:01:24 | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         |                0 | N         |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.000 sec)SHOW TABLE STATUS LIKE "location"在生产中的产量MariaDB [phs_p]> SHOW TABLE STATUS LIKE "location";
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name     | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment | Max_index_length | Temporary |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| location | InnoDB |      10 | Dynamic    | 227281 |            113 |    25739264 |               0 |     39976960 |   6291456 |         844563 | 2021-08-31 00:37:00 | 2021-08-31 14:03:42 | NULL       | utf8_unicode_ci |     NULL |                |         |                0 | N         |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.000 sec)Edit 4这些是生产服务器24小时正常运行后的结果。我还更新了上面my.cnf的链接，所以它现在开始工作了。我附上了SHOW GLOBAL STATUS -左产品的输出，右开发。https://www.diffchecker.com/StIjIm0y我附上了SHOW GLOBAL VARIABLES -左产品的输出，右开发。https://www.diffchecker.com/N8LPoIs4我附上了SHOW FULL PROCESSLIST -左产品的输出，右开发。https://www.diffchecker.com/z2MmFLGO我附上了STATUS -左产品的输出，右开发。https://www.diffchecker.com/sQ5TmUCY我附上了SHOW ENGINE INNODB STATUS -左产品的输出，右开发。https://www.diffchecker.com/9Vuzgkaj我也做了ANALYZE TABLE location，一切都很好。不过，我确实注意到MySQL服务器已经消失了。它也曾在上面的命令中执行过一次。MariaDB [phs_p]> ANALYZE TABLE location;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    481218
Current database: phs_p

+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| phs_p.location | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set (0.082 sec), '') ;  263.29
 UPDATE location SET postal_code = REPLACE(postal_code, '#', '') ;  265.83这是我的作品：A1我不是在找人来解决这个问题，但我该怎么调查呢？可能的罪魁祸首是什么？运行脚本时对开发的D2B3在运行脚本时在生产中使用D4B5D6有相似的结果。@Vérace这是D7在生产和开发中的输出：A8@ D9的输出。生产数据库在左边。C10@Vérace，我添加了D11变量的差异。左边是生产的，右边的是发展的。C12这是D13在生产上的输出。A14编辑：我还从开发中提取了相同的D15，并在生产中使用。还是很慢。所以我不认为它是任何一个mariadb变量。我还运行了命令D16，但没有成功。Edit 2:生产中D17的输出左，开发右C18Edit 3 D19在开发中的输出A20D21在生产中的产量A22Edit 4这些是生产服务器24小时正常运行后的结果。我还更新了上面D23的链接，所以它现在开始工作了。我附上了D24 -左产品的输出，右开发。C25我附上了D26 -左产品的输出，右开发。C27我附上了D28 -左产品的输出，右开发。C29我附上了D30 -左产品的输出，右开发。C31我附上了D32 -左产品的输出，右开发。C33我也做了D34，一切都很好。不过，我确实注意到MySQL服务器已经消失了。它也曾在上面的命令中执行过一次。A35, '') ;  2078.69
UPDATE location SET postal_code = REPLACE(postal_code, '#', '') ;  2033.26

我不是在找人来解决这个问题，但我该怎么调查呢？可能的罪魁祸首是什么？

运行脚本时对开发的D2

B3

在运行脚本时在生产中使用D4

B5

D6有相似的结果。

@Vérace这是D7在生产和开发中的输出：

A8

@ D9的输出。生产数据库在左边。C10

@Vérace，我添加了D11变量的差异。左边是生产的，右边的是发展的。C12

这是D13在生产上的输出。

A14

编辑：

我还从开发中提取了相同的D15，并在生产中使用。还是很慢。所以我不认为它是任何一个mariadb变量。我还运行了命令D16，但没有成功。

Edit 2:生产中D17的输出左，开发右C18

Edit 3 D19在开发中的输出

A20

D21在生产中的产量

A22

Edit 4这些是生产服务器24小时正常运行后的结果。我还更新了上面D23的链接，所以它现在开始工作了。

我附上了D24 -左产品的输出，右开发。

C25

我附上了D26 -左产品的输出，右开发。

C27

我附上了D28 -左产品的输出，右开发。

C29

我附上了D30 -左产品的输出，右开发。

C31

我附上了D32 -左产品的输出，右开发。

C33

我也做了D34，一切都很好。不过，我确实注意到MySQL服务器已经消失了。它也曾在上面的命令中执行过一次。

A35, '') ; 263.29 UPDATE location SET postal_code = REPLACE(postal_code, '#', '') ; 265.83

这是我的作品：

A1

我不是在找人来解决这个问题，但我该怎么调查呢？可能的罪魁祸首是什么？

运行脚本时对开发的D2

B3

在运行脚本时在生产中使用D4

B5

D6有相似的结果。

@Vérace这是D7在生产和开发中的输出：

A8

@ D9的输出。生产数据库在左边。C10

@Vérace，我添加了D11变量的差异。左边是生产的，右边的是发展的。C12

这是D13在生产上的输出。

A14

编辑：

我还从开发中提取了相同的D15，并在生产中使用。还是很慢。所以我不认为它是任何一个mariadb变量。我还运行了命令D16，但没有成功。

Edit 2:生产中D17的输出左，开发右C18

Edit 3 D19在开发中的输出

A20

D21在生产中的产量

A22

Edit 4这些是生产服务器24小时正常运行后的结果。我还更新了上面D23的链接，所以它现在开始工作了。

我附上了D24 -左产品的输出，右开发。

C25

我附上了D26 -左产品的输出，右开发。

C27

我附上了D28 -左产品的输出，右开发。

C29

我附上了D30 -左产品的输出，右开发。

C31

我附上了D32 -左产品的输出，右开发。

C33

我也做了D34，一切都很好。不过，我确实注意到MySQL服务器已经消失了。它也曾在上面的命令中执行过一次。

A35

Answer 1

在原始mysqltuner输出中可以看到，这个实例的配置允许DB使用物理32的177 DB。这对性能来说不是最优的。在解决性能问题时，隐藏这些信息不是最优的。

如果这是IO问题(CPU可以指示文件系统)，我将检查更新脚本，只更新那些需要提醒的记录，例如：

UPDATE location SET postal_code = REPLACE(postal_code, ' ', '')
  where postal_code like '% %';

当前，您的每个更新都会更新整个表，可能没有任何原因。

Answer 2

每秒速率= RPS

关于10.5.12 Prod服务器部分的建议

innodb_adaptive_max_sleep_delay=10000  # from 0 when busy, give the CPU a quick break
innodb_concurrency_tickets=10000  # from 0 to minimize reque's
read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 35,512
analyze_sample_percentage=0  # from 100 to limit sampling time expended-autocalc
innodb_adaptive_hash_index=ON  # from OFF to minimize deadlocks

有更多的机会来改进您的配置。

您的生产服务器有4个CPU，开发服务器有16个CPU。这可能会导致速度慢。

观察到，MariaDB 10.5.12于2021年6月8日开始通用，仅仅一个月前。您可能在这个版本中发现了一些遗漏的内容，如果您有时间，请与MySQL开发人员一起打开一张麻烦票。这通常可以避免在未来，如果你将避免发布更新，直到他们有90天的街道上，让别人的乐趣发现麻烦，报告，等待纠正释放。

您的com_begin和com_commit计数在3天内相差208个。这将指向在流程结束之前忘记“提交”数据的人，这会释放所使用的资源。

通常我们看到com_savepoint和com_release_savepoint匹配。看起来有36,880个人在3天内没有被释放，导致在处理过程时没有释放资源。