Performance
生產數據庫比開發數據庫慢這麼多？
我在兩台伺服器上執行一個腳本，一個在我的 docker 本地開發系統中，一個在生產中。但是，相同的腳本在生產環境中的執行速度要比在開發環境中慢得多。我在兩者上都使用 MariaDB 10.5。我的開發系統有 12 GB RAM，而我的生產系統是 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, '$', '') ; 263.29 UPDATE location SET postal_code = REPLACE(postal_code, '#', '') ; 265.83這是在我的生產中：
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, '$', '') ; 2078.69 UPDATE location SET postal_code = REPLACE(postal_code, '#', '') ; 2033.26我不是要找人來解決這個問題，但我該如何調查呢？可能的罪魁禍首是什麼？
htop在執行腳本時進行開發
htop在執行腳本時進行生產
iotop有類似的結果。@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@mustaccio 這是
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 <major@mhtx.net> [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，但沒有成功。編輯 2：
ulimit -a生產左側的輸出，右側的開發https://www.diffchecker.com/OJWhXxwi
編輯 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)編輯 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)
從原始 mysqltuner 輸出中可以看出，此實例的配置允許 DB 使用物理 32 中的 177Gb。這對於性能來說不是最佳的。隱藏此資訊對於解決性能問題並不是最優的。
如果這被證明是 IO 問題（CPU 可能指示文件系統），我將查看更新腳本以僅更新那些需要更改的記錄，例如：
UPDATE location SET postal_code = REPLACE(postal_code, ' ', '') where postal_code like '% %';目前，您的每個更新都會更新整個表，可能沒有任何原因。
每秒速率 = RPS
為您的 10.5.12 Prod 伺服器考慮的建議
$$ mysqld $$部分
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 年 8 月 6 日正式發布，就在一個月前。您可能會發現此版本中遺漏的一些內容，如果您有時間，請向 MySQL 開發人員送出故障單。這通常可以在以後避免，如果您在他們有 90 天的時間之前避免發布更新，以便讓其他人有樂趣發現問題、報告問題、等待更正版本。
您的 com_begin 和 com_commit 計數在 3 天內相差 208。這表明有人忘記在程序結束之前“送出”數據，從而釋放使用的資源。
通常我們會看到 com_savepoint 與 com_release_savepoint 匹配。看起來 36,880 沒有在 3 天內釋放，導致在完成該過程時沒有釋放資源。