Query-Performance

僅在某些條件下，查詢性能隨時間顯著下降

  • January 18, 2021

更新：升級到 MariaDB 10.4.17 後，此問題中描述的所有問題都消失了。

我遇到了奇怪的情況，查詢隨著時間的推移變得越來越慢。

包含相關數據的表具有以下結構：

CREATE TABLE `items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
 `location` char(1) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
 `owner` int(11) NOT NULL DEFAULT 0,
 `class` tinyint(4) NOT NULL DEFAULT 0,
 `price` mediumint(8) unsigned NOT NULL DEFAULT 0,
 `created` timestamp NOT NULL DEFAULT current_timestamp(),
 PRIMARY KEY (`id`),
 KEY `location` (`location`,`owner`),
 KEY `name` (`name`),
 KEY `class` (`class`)
) ENGINE=InnoDB;

執行這樣的查詢時出現問題：

UPDATE `items` SET `price`=`price`+1 WHERE `location`='d' AND `owner`=5851775 AND `class`=17;
Query OK, 1 row affected (1.158 sec)
Rows matched: 1  Changed: 1  Warnings: 0

上述查詢的附加資訊：

EXPLAIN UPDATE items SET price=price+1 WHERE location='d' AND owner=5851775 AND class=17;
+------+-------------+-------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
| id   | select_type | table | type        | possible_keys  | key            | key_len | ref  | rows | Extra                                       |
+------+-------------+-------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
|    1 | SIMPLE      | items | index_merge | location,class | location,class | 7,1     | NULL | 1    | Using intersect(location,class); Using where |
+------+-------------+-------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
1 row in set (0.000 sec)

有 67 項所有者=5851775

執行相同的查詢但具有不同的所有者值幾乎可以立即執行：

UPDATE `items` SET `price`=`price`+1 WHERE `location`='d' AND `owner`=9406604 AND `class`=17;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

上述查詢的附加資訊：

EXPLAIN UPDATE `items` SET `price`=`price`+1 WHERE `location`='d' AND `owner`=9406604 AND `class`=17;
+------+-------------+-------+-------+----------------+----------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys  | key      | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+----------------+----------+---------+------+------+-------------+
|    1 | SIMPLE      | items | range | location,class | location | 7       | NULL | 1    | Using where |
+------+-------------+-------+-------+----------------+----------+---------+------+------+-------------+

只有 1 項所有者 = 9406604

表包含超過 2M 條記錄，此行為首先在數據庫重新啟動後 2-3 天后在慢查詢日誌中顯示有問題的查詢。有問題的值的查詢時間正在緩慢增加，只有重新啟動 mysql 守護程序才能在接下來的幾天內解決此問題。

到目前為止，我已經完成了以下檢查：

  1. 為這兩個查詢執行解釋。它顯示了索引選擇之間的差異。快速查詢使用 KEY( location)，慢速查詢使用Using intersect(location,class);. 這很奇怪，因為在這兩種情況下都只有一個記錄（而且不可能不止一個）
  2. 在 SLAVE instancje 上執行查詢，該查詢與 MASTER 上的完全相同的數據庫執行了很長時間。索引選擇也有類似的情況，但是在 SLAVE 查詢執行非常快（0.040s）。
  3. 根據 SHOW PROFILE FOR QUERY 的說法，有問題的查詢幾乎花費了所有時間“更新”。
  4. 我很少在執行查詢時得到ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction（大約 1:5000 個查詢）。此表根本沒有應用程序中的事務。
  5. 對於有問題的查詢，使用 USE INDEX( location) 強制索引使其在主從機上執行超快（0.001 秒）。
  6. ANALYZE TABLE items沒有任何區別。
  7. FLUSH TABLES沒什麼區別
  8. 伺服器重新啟動後，查詢在一段時間內執行得相當快。現在重新啟動後 12 小時，對於使用索引合併的查詢，UPDATE 需要 0.001 秒。

我檢查了 KEY( owner) 的數據基數，並嘗試對這些owner值執行有問題的查詢：

  • owner=64399, 0.747 sec, 表中有 113 項具有此所有者值
  • owner=362379, 0.313 sec, 表中有 103 項具有此所有者值
  • owner=1077182, 0.102 sec, 表中有 163 項具有此所有者值
  • owner=1, 0.001 sec, 表中有 17 項具有此所有者值
  • owner=5851775, 1.158 sec, 表中有 67 項具有此所有者值

結果來自SHOW INDEXES FROM items：

Key Name; Seq in index, Colum name, Collation, Cardinality
PRIMARY, 1, id, A, 2390665, BTREE
location, 1, location, A, 11, BTREE
location, 2, owner, A, 68838, BTREE
name, 1, name, A, 24464, BTREE
class, 1, class, A, 27, BTREE

來自解釋的資訊：

EXPLAIN UPDATE items SET price=price+1 WHERE location='d' AND owner=64399 AND class=17;
+------+-------------+-------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
| id   | select_type | table | type        | possible_keys  | key            | key_len | ref  | rows | Extra                                       |
+------+-------------+-------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
|    1 | SIMPLE      | items | index_merge | location,class | location,class | 7,1     | NULL | 1    | Using intersect(location,class); Using where |
+------+-------------+-------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
1 row in set (0.000 sec)

對於SELECT *在相同條件下強制有問題的索引：

EXPLAIN SELECT * FROM items USE INDEX(`class`) WHERE `location`='d' AND `owner`=9402368 AND `class`=17;
+------+-------------+-------+------+---------------+-------+---------+-------+-------+-------------+
| id   | select_type | table | type | possible_keys | key   | key_len | ref   | rows  | Extra       |
+------+-------------+-------+------+---------------+-------+---------+-------+-------+-------------+
|    1 | SIMPLE      | items | ref  | class         | class | 1       | const | 72744 | Using where |
+------+-------------+-------+------+---------------+-------+---------+-------+-------+-------------+

SELECT 也總是執行超快（0.001 秒），但 UPDATE 是慢速 AF。注意：重新啟動後沒有查詢執行緩慢。即使是那些使用 intersect 的人也很快（最多 0.040 秒）。

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_dirty';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_dirty | 18857 |
+--------------------------------+-------+

SHOW GLOBAL STATUS LIKE 'uptime';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 116348 |
+---------------+--------+

我目前在重啟後。我想慢查詢會在接下來的 24 小時內出現，所以我會用新值更新資訊。我還檢查了重啟後 7 小時的髒頁計數，大約是 16k。

現在沒有指出的死鎖SHOW ENGINE INNODB STATUS;。

查詢記憶體被禁用。

SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_strip_comments   | OFF     |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

狀態更新。我現在正在觀察描述的問題。InnoDB 臟頁達到了 20k，但昨天它達到了 26k，並且在 slow_query_log 中沒有查詢。現在有問題描述的查詢（沒有其他）。

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_pages_data    | 2978222  |
| Innodb_buffer_pool_pages_dirty   | 20852    |
| Innodb_buffer_pool_pages_flushed | 41865500 |
| Innodb_buffer_pool_pages_free    | 4833422  |
| Innodb_buffer_pool_pages_misc    | 395716   |
| Innodb_buffer_pool_pages_total   | 8207360  |
+----------------------------------+----------+

有問題的查詢的完整分析資訊：

SHOW PROFILES;
+----------+------------+-------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                           |
+----------+------------+-------------------------------------------------------------------------------------------------+
|        1 | 0.04050855 | SELECT * FROM items USE INDEX(class) WHERE location='d' AND owner=4518486 AND class=17          |
|        2 | 1.11991697 | UPDATE itemy USE INDEX(class) SET price=price WHERE location='d' AND owner=4518486 AND class=17 |
+----------+------------+-------------------------------------------------------------------------------------------------+

SHOW PROFILE FOR QUERY 1;
+------------------------+----------+
| Status                 | Duration |
+------------------------+----------+
| Starting               | 0.000060 |
| Checking permissions   | 0.000011 |
| Opening tables         | 0.000020 |
| After opening tables   | 0.000014 |
| System lock            | 0.000009 |
| Table lock             | 0.000020 |
| Init                   | 0.000029 |
| Optimizing             | 0.000021 |
| Statistics             | 0.000076 |
| Preparing              | 0.000025 |
| Executing              | 0.000010 |
| Sending data           | 0.040111 |
| End of update loop     | 0.000020 |
| Query end              | 0.000007 |
| Commit                 | 0.000007 |
| Closing tables         | 0.000008 |
| Unlocking tables       | 0.000006 |
| Closing tables         | 0.000010 |
| Starting cleanup       | 0.000006 |
| Freeing items          | 0.000010 |
| Updating status        | 0.000016 |
| Reset for next command | 0.000014 |
+------------------------+----------+

SHOW PROFILE FOR QUERY 2;
+------------------------+----------+
| Status                 | Duration |
+------------------------+----------+
| Starting               | 0.000087 |
| Checking permissions   | 0.000010 |
| Opening tables         | 0.000022 |
| After opening tables   | 0.000010 |
| System lock            | 0.000012 |
| Table lock             | 0.000016 |
| Init for update        | 0.000073 |
| Updating               | 1.118274 |
| End of update loop     | 0.000017 |
| Query end              | 0.000008 |
| Commit                 | 0.000006 |
| Writing to binlog      | 0.000019 |
| Commit                 | 0.001209 |
| Closing tables         | 0.000020 |
| Unlocking tables       | 0.000010 |
| Closing tables         | 0.000012 |
| Starting cleanup       | 0.000010 |
| Freeing items          | 0.000013 |
| Updating status        | 0.000021 |
| Logging slow query     | 0.000052 |
| Reset for next command | 0.000014 |
+------------------------+----------+

重啟後：

+----------+------------+---------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                             |
+----------+------------+---------------------------------------------------------------------------------------------------+
|        1 | 0.24960048 | UPDATE items USE INDEX(`class`) SET price=price WHERE location='d' AND owner=8966252 AND klasa=17 |
+----------+------------+---------------------------------------------------------------------------------------------------+

SHOW PROFILE FOR QUERY 1;
+------------------------+----------+
| Status                 | Duration |
+------------------------+----------+
| Starting               | 0.000090 |
| Checking permissions   | 0.000010 |
| Opening tables         | 0.000022 |
| After opening tables   | 0.000009 |
| System lock            | 0.000009 |
| Table lock             | 0.000009 |
| Init for update        | 0.000061 |
| Updating               | 0.247580 |
| End of update loop     | 0.000068 |
| Query end              | 0.000007 |
| Commit                 | 0.000006 |
| Writing to binlog      | 0.000025 |
| Commit                 | 0.001622 |
| Closing tables         | 0.000014 |
| Unlocking tables       | 0.000007 |
| Closing tables         | 0.000010 |
| Starting cleanup       | 0.000009 |
| Freeing items          | 0.000008 |
| Updating status        | 0.000017 |
| Reset for next command | 0.000017 |
+------------------------+----------+

您能否指出任何進一步的測試，以便無需每天重新啟動數據庫來恢復性能？

附加資訊：

  • 伺服器專用於 DB：MariaDB 10.4.13
  • 機器有 256G RAM 16 核 + HT
  • nvme SSD 驅動器
  • InnoDB 池緩衝區為 128GB（64 個實例_，機器上的整個 InnoDB 數據約為 90GB。
  • 機器上的負載相當高，但不會隨時間變化以反映這種查詢時間的下降。

由於有大量有問題的資訊，我在這裡總結了一些結論：

  • 某些 UPDATE 正在使用 intersect INDEX，並且它的性能從 mysql 新啟動開始逐漸惡化。每次查詢都很慢。
  • 在同一時間具有不同參數值的相同更新工作正常（但解釋表明沒有使用相交索引）
  • 當強制 INDEX 與無問題的更新相同時，有問題的更新工作正常。
  • 使用與有問題的相同 WHERE 條件的 SELECT 查詢執行沒有任何問題（也使用相交索引）
  • 似乎使用 INDEX(class) 是問題的根源。不知何故，索引性能會隨著時間的推移而下降。
  • OPTIMIZE TABLE items;不改變查詢時間
  • 為（類）欄位添加新索引並強制查詢使用新索引與原始索引相比不會改變任何內容class。
  • 在技​​術中斷期間對其進行了測試（幾乎沒有使用 DB 的應用程序）。與技術中斷前相比，查詢時間沒有變化。
  • 通過在新的未使用的數據庫中復製表來測試它。新副本的查詢時間與原始副本完全相同 - 似乎它沒有連接到該表的其他活動，因為新副本上沒有。
  • 在等待足夠長的時間（6 天）後，整個 mysql 在所有非索引查詢上都會變慢，並且在 slow_query_log 中有很多不同的查詢。有時 UPDATE 的條件僅在 PRIMARY KEY 上具有一個值。

因為您的“所有者”列具有數百萬行表的最佳基數，請考慮添加此多列索引

KEY `itemy_idx_own_loc_cla` (`owner`,`location`,`class`)

並在您的更新查詢中將 WHERE 子句的順序更改為 owner,location,class

讓優化器做出更好的選擇。

如果所有索引都以表名開頭，那麼您將在探勘時最大限度地減少研究時間。

2012-12-15 處理innodb_buffer_pool_dirty_pages，減少臟頁，考慮這些建議。

innodb_flush_neighbors=2 # for all changes in current EXTENT to be dealt with now 
innodb_max_dirty_pages_pct_lwm=.0001 # to expedite reducing dirty pages 
innodb_max_dirty_pages_pct=.0001 # to expedite reducing dirty pages
innodb_flushing_avg_loops=5  # from 50 to minimize loop delay

請在 24 小時內告訴我們您的髒頁數在哪裡。這些都是動態變數。可以使用 SET GLOBAL (variable_name)=new_value。24 小時後，您已經證明它們適用於您的系統，請更改您的 my.cnf 以每天保持在較低臟頁的位置。

Innodb_buffer_pool_pages_dirty | 18857

那可能是相當大的。將其與Innodb_buffer_pool_pages_total.

INDEX任何類型的寫入（包括更新）都會出現“臟”頁面。也許發生的是另一個查詢，可能是一個UPDATE或DELETE修改或刪除了大量行的查詢。

是的，您的 buffer_pool 很大（128GB），所以它只是空間的一小部分。儘管如此，即使使用 SSD，也可能需要 10 多秒才能將其清除。

這兩個查詢（你的和大更新/刪除的）應該能夠共存，現在你的查詢減慢了那麼多，但顯然他們遇到了麻煩。

請參閱http://mysql.rjweb.org/doc.php/mysql_analysis了解兩件事——

  • GLOBAL STATUS和的分析VARIABLES。有一些設置可能會有所幫助；狀態可能有助於找出問題所在。
  • 慢日誌分析。這將幫助您找到競爭查詢。修復它可能是最好的解決方案。

如果其他查詢很大DELETE，讓我們看看。這裡有幾個提示：http: //mysql.rjweb.org/doc.php/deletebig。大UPDATEs可以通過類似的技巧得到幫助。

嘗試解釋

PRIMARY, 1, id, A, 2390665, BTREE
location, 1, location, A, 11, BTREE
location, 2, owner, A, 68838, BTREE
name, 1, name, A, 24464, BTREE
class, 1, class, A, 27, BTREE

我認為這估計來自一個索引的 2390665/68838 ~= 35 和來自另一個索引的大約 2390665/27 ~= 88K。“相交”建構兩個臨時表（或其他一些資料結構），一個有 35 行，另一個有 88K 行。然後它遍歷它們以查看匹配的內容（“相交”）。所需的空間和時間使得如果其他連接忙於做某事，資源爭用可能會中斷或延遲此操作。

會發生什麼衝突？

（其中一些不太可能適用於您的情況——您的數據集小於您的 buffer_pool。）

  • ALTER TABLE
  • 對該表進行表掃描
  • 由於其他一些表參與大表掃描，記憶體 (buffer_pool) 被刷新。
  • 另一個連接執行涉及一些相同行的事務，死鎖檢測機制決定解決方案是讓這個查詢等到另一個完成。
  • 請注意，即使沒有觸及相同的行，事務之間也可能發生衝突。（參見“間隙鎖定”。）

其中大多數涉及查看其他情況（慢日誌可能是一個很好的起點）。查找與此查詢幾乎同時停止的其他查詢。（注意：慢日誌報告結束時間。）

抱歉，我無法準確解釋您所看到的內容。和中可能有線索。有關設置和慢日誌的更多資訊：http: //mysql.rjweb.org/doc.php/mysql_analysisSHOW GLOBAL STATUS``SHOW VARIABLES

引用自：https://dba.stackexchange.com/questions/281444

相關問答

Mysql

是否有一個布爾列來儲存多值屬性的值是否是一個糟糕的微優化？

  • August 5, 2022
檢視問答
Query-Performance

MariaDB 查詢優化器忽略索引

  • July 4, 2022
檢視問答
Mysql

優化對字元串中多個術語中的任何一個的查詢

  • June 27, 2022
檢視問答
Postgresql

比較跨 DBMS 的查詢性能

  • March 15, 2021
檢視問答
Query-Performance

如何在 Mariadb 中有效地模擬 JSON_TABLE？

  • December 21, 2020
檢視問答
Query-Performance

MariaDB 10.4 查詢優化器錯誤的執行計劃

  • December 13, 2020
檢視問答