Mysql

從 MySQL 到 MariaDB:MyISAM 表的性能不佳問題

  • January 28, 2020

我目前正在將 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 &lt;= 1577454941

並且有

INDEX(deleted, hidden, place,   -- in any order
     start_time)               -- after the others

您會發現 (of MATCH) 的順序與 InnoDB 和 MyISAM 不同。

此外,當涉及多個表時,InnoDB 的行為也會有所不同。將需要兩個單獨的單列索引:FULLTEXT(title), FULLTEXT(metakeywords).

引用自:https://dba.stackexchange.com/questions/256514