MySQL 升級到 5.6 後 tmp 磁碟表過多

在將兩個執行主從複製的 MySQL 伺服器從 Percona MySQL 5.5 升級到 Percona MySQL 5.6 後，在從屬伺服器上創建了過多的臨時表。

大師的速度似乎和以前差不多。

在從站上，我們從平均每秒大約 35 次增加到每秒大約 1000 次。（這些是非常粗略的平均值）。我們在這台伺服器上每秒大約有 3200 個查詢。

root:/var/lib/mysql# mysql -e "show global status like 'Created_tmp%_tables';" -e "show global status like 'Uptime%';"

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 1485718 |
| Created_tmp_tables      | 1505629 |
+-------------------------+---------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 1523  |
| Uptime_since_flush_status | 1523  |
+---------------------------+-------+

如您所見，幾乎所有的臨時表都是在磁碟上創建的（~98%）。或者更確切地說是用於提高性能的 RAM 磁碟。

my.cnf 中的配置並沒有真正改變。最多一些選項已重命名為全長名稱。

調整tmp_table_size、max_heap_table_size、join_buffer_size和sort_buffer_size似乎沒有任何區別。

所以我現在的問題是什麼可能導致這種行為？

我已經閱讀了升級說明，搜尋了這個網站並且通常用Google搜尋了這個問題，但沒有想出任何對我有幫助的東西。

如果我忘記了一些非常需要的資訊，請告訴我。

非常感謝任何幫助。

事實證明，在 MySQL 5.6.8 中 query_cache_type 的預設值更改為 OFF。在此之前，預設值為 ON。

見https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_type

發生這種情況的原因有很多，很難指出一個具體原因——這可能是因為查詢優化器的更改、查詢統計資訊錯誤、預設值從版本更改為版本的參數、複製格式的更改，您正在處理的數據類型，行格式的更改等。

這裡的關鍵點是確定查詢執行的差異：如果在升級之前就已經確定了，那就太好了，但是如果您說主伺服器沒有顯示該行為，那可能就足夠了。您需要發現哪些查詢正在創建這些臨時表。當您使用 Percona Server 5.6時，有 2 個主要工具：

• 使用Percona 上可用的擴展日誌記錄並使用pt-query-digest來辨識和總結有問題的查詢，或者
• 使用自 5.6 起可用的performance_schema

這兩種工具都可以用來辨識那些錯誤的查詢。之後，您將能夠使用標準工具分析每個查詢：EXPLAIN分析指標、處理程序統計資訊等。在某些情況下，您可能需要更改配置，在其他查詢、索引或結構本身您的數據庫（例如，blob 通常會導致問題，因為使用它們的臨時表不能在記憶體中）。

與上述所有內容無關，實際上可能有用的一件事是複制格式。如果您在使用的從屬設備上創建臨時表STATEMENT或MIXED複制格式（或您的表沒有主鍵）。基於行的複制，在某些情況下（當查詢不同時修改許多記錄時）可能有助於查詢延遲和避免從屬漂移，因此您至少應該考慮這一點。

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