從 MySQL 到 MariaDB:MyISAM 表的性能不佳問題
我目前正在將 Debian 6 MySQL 伺服器 (5.1.73) 遷移到全新的 Debian 10 MariaDB 伺服器 (10.3.18)。請注意,我不是數據庫專家。
所有表都在 MyISAM 引擎儲存下,總共大約 5 Gb。計劃遷移到 InnoDB,但現在我想在新伺服器上遷移而不更新表並確保保持性能。
這是我的問題:我有一個
SELECT
在舊伺服器上執行約 0.2 秒的查詢,而相同的查詢在新伺服器上執行約 1.4 秒。我不知道如何解釋:這大約是全新安裝的 7 倍!這是(簡化的)查詢:
SELECT NOW(), uid, ((MATCH (title) AGAINST ('\"keyword1\" \"keyword2\" \"keyword3\"')) * 4 + (MATCH (metakeywords) AGAINST ('\"keyword1\" \"keyword2\" \"keyword3\"')) * 2 AS MATCH FROM news WHERE wfplace = 'live' AND deleted = 0 AND hidden = 0 AND (magazine IS NOT NULL AND magazine <> '') AND (start_time = 0 OR start_time <= 1577454941) AND (end_time = 0 OR end_time >= 1577454941) ORDER BY MATCH DESC LIMIT 10
MATCH...AGAINST
沒有這些東西的兩台伺服器之間的性能似乎相同。我使用該NOW()
部分來確保結果中不涉及記憶體。請注意,要遷移數據,我只需將 .frm、.MYD 和 MYI 文件從
mysqlhotcopy
轉儲文件同步到新的 .frm 文件/var/lib/mysql/my_db
。這是舊的 my.cnf(僅限自定義設置):
skip-external-locking skip-name-resolve skip-innodb skip-locking key_buffer_size = 2048M max_allowed_packet = 32M table_open_cache = 2048 sort_buffer_size = 8M read_buffer_size = 8M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 256M thread_cache_size = 256 query_cache_size = 256M query_cache_limit = 8M thread_concurrency = 8 interactive_timeout = 90 connect_timeout = 90 wait_timeout = 90 max_connections = 150 long_query_time = 2
Conf:Intel(R) Xeon(R) CPU,16 核和 47 Gb RAM。
這是新的自定義設置:
performance_schema=off skip-name-resolve query_cache_size=0 query_cache_type=0 key_buffer_size=20G innodb_buffer_pool_size=20G innodb_log_file_size=2G innodb_buffer_pool_instances=20 table_open_cache=2048 max_allowed_packet=32M sort_buffer_size=8M myisam_sort_buffer_size=256M read_buffer_size=8M read_rnd_buffer_size=8M wait_timeout=90 interactive_timeout=90 connect_timeout=90 long_query_time=2
Conf:Intel(R) Xeon(R) Silver 4110 CPU,16 核和 93 Gb RAM。
我試圖保留一些舊設置以具有相似的速度,但沒有成功。
我唯一的線索是
EXPLAIN
最後一個“額外”列上兩台伺服器之間的同一查詢的不同結果:我錯過了什麼?謝謝你的幫助!
編輯:
原始
EXPLAIN
(舊伺服器):+----+-------------+-------+------+--------------------------------------------------------------------------------------+---------+---------+-------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+--------------------------------------------------------------------------------------+---------+---------+-------+-------+-----------------------------+ | 1 | SIMPLE | t0_ | ref | hidden,deleted,optim1,deleted_2,topcanonique,sort_date,idx_magazine,wfplace,magazine | wfplace | 27 | const | 28749 | Using where; Using filesort | +----+-------------+-------+------+--------------------------------------------------------------------------------------+---------+---------+-------+-------+-----------------------------+
原始
EXPLAIN
(新伺服器,禁用索引條件下推):+------+-------------+-------+------+----------------------------------------------------------------------------------------------+---------+---------+-------+-------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+----------------------------------------------------------------------------------------------+---------+---------+-------+-------+---------------------------------------------------------------------+ | 1 | SIMPLE | t0_ | ref | hidden,deleted,optim1,deleted_2,topcanonique,sort_date,idx_magazine,wfplace,endtime,magazine | wfplace | 27 | const | 25572 | Using where; Using temporary; Using filesort | +------+-------------+-------+------+----------------------------------------------------------------------------------------------+---------+---------+-------+-------+---------------------------------------------------------------------+
flush status; SELECT ... (query); SHOW STATUS LIKE 'Hand%';
(舊伺服器)的結果:+----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 34980 | | Handler_read_prev | 0 | | Handler_read_rnd | 10 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+
flush status; SELECT ... (query); SHOW STATUS LIKE 'Hand%';
(新伺服器)的結果:+----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 0 | | Handler_icp_attempts | 0 | | Handler_icp_match | 0 | | Handler_mrr_init | 0 | | Handler_mrr_key_refills | 0 | | Handler_mrr_rowid_refills | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 34960 | | Handler_read_prev | 0 | | Handler_read_retry | 0 | | Handler_read_rnd | 10 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 33195 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_tmp_delete | 0 | | Handler_tmp_update | 0 | | Handler_tmp_write | 33194 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+
EXPLAIN FORMAT=JSON SELECT
正如@Rick James 所問的結果:{ "query_block": { "select_id": 1, "filesort": { "sort_key": "(match title against ('"keyword1" "keyword2" "keyword3"')) * 4 + (match metakeywords against ('"keyword1" "keyword2" "keyword3"')) * 2", "temporary_table": { "table": { "table_name": "news", "access_type": "ref", "possible_keys": [ "hidden", "deleted", "optim1", "deleted_2", "topcanonique", "sort_date", "idx_magazine", "wfplace", "magazine" ], "key": "wfplace", "key_length": "27", "used_key_parts": ["wfplace"], "ref": ["const"], "rows": 28727, "filtered": 100, "index_condition": "wfplace = 'live'", "attached_condition": "deleted = 0 and hidden = 0 and magazine is not null and magazine <> '' and (starttime = 0 or starttime <= 1577454941) and (endtime = 0 or endtime >= 1577454941)" } } } } }
嘗試在新伺服器上禁用索引條件下推(預設情況下在 5.6+ 上啟用,在 <=5.5 上不可用)。
MyISAM 在較新版本的 MySQL 上並沒有得到太多的喜愛,您可能想在單獨的伺服器上測試遷移,包括 InnoDB 轉換。否則,您修復的所有優化最終都可能毫無用處,因為某些查詢功能並非在所有引擎上都可用。
(評論太大了)
這將提高性能:將`開始時間的測試更改為簡單
AND start_time <= 1577454941
並且有
INDEX(deleted, hidden, place, -- in any order start_time) -- after the others
您會發現 (of
MATCH
) 的順序與 InnoDB 和 MyISAM 不同。此外,當涉及多個表時,InnoDB 的行為也會有所不同。將需要兩個單獨的單列索引:
FULLTEXT(title), FULLTEXT(metakeywords)
.