【MySQL】【翻譯】 MySQL 5.7 的內部臨時表新特性

【MySQL】【翻譯】MySQL Internal Temporary Tables in MySQL 5.7（MySQL 5.7 內部臨時表）

Alexander Rubin | December 4, 2017 | Posted In: Insight for DBAs, MySQL, Percona Monitoring and Management

翻譯:張銳志

In this blog post, I investigate a case of spiking InnoDB Rows inserted in the absence of a write query, and find internal temporary tables to be the culprit.

本文中研究了在沒有寫查詢的情況下，InnoDB行插入卻因內部臨時表的問題發生性能尖刺的情形。

Recently I was investigating an interesting case for a customer. We could see the regular spikes on a graph depicting “InnoDB rows inserted” metric (jumping from 1K/sec to 6K/sec), however we were not able to correlate those spikes with other activity. The innodb_row_inserted graph (picture from PMM demo) looked similar to this (but on a much larger scale):

事情發生在我研究一個客戶的案例時，在”InnoDB行插入“指標圖上，發現了從1k行每秒激增到6K行每秒的尖刺，但卻無法和其他活動或者現象連接起來，PMM監控圖形上也有同樣的反映。

Other graphs (Com*, Handler*) did not show any spikes like that. I’ve examined the logs (we were not able to enable general log or change the threshold of the slow log), performance_schema, triggers, stored procedures, prepared statements and even reviewed the binary logs. However, I was not able to find any single *write* query which could have caused the spike to 6K rows inserted.

其他例如句柄和接口的圖形都沒有顯示同樣的尖刺，在無法開啟general log的情況下，我們嘗試檢查了所有的日志，performance_schema，觸發器，存儲過程，預編譯語句，甚至包括binlog后發現沒有任何單個的寫查詢語句可以導致每秒插入飆升到6K行。

Finally, I figured out that I was focusing on the wrong queries. I was trying to correlate the spikes on the InnoDB Rows inserted graph to the DML queries (writes). However, the spike was caused by SELECT queries! But why would SELECT queries cause the massive InnoDB insert operation? How is this even possible?

在最后才發現，行插入飆升一定和DML有關的這種想法是錯誤的，出乎意料的是，尖刺是由于SELECT查詢導致的，但為何SELECT查詢會導致大量的InnoDB行插入操作呢？

It turned out that this is related to temporary tables on disk. In MySQL 5.7 the default setting for internal_tmp_disk_storage_engine is set for InnoDB. That means that if the SELECT needs to create a temporary table on disk (e.g., for GROUP BY) it will use the InnoDB storage engine.

原來是與磁盤臨時表有關。在MySQL 5.7版本中，內部磁盤臨時表的默認引擎是InnoDB引擎，這就意味著當SELECT操作需要在磁盤上創建臨時表時（例如GROUP BY操作），就會使用到InnoDB引擎。

Is that bad? Not necessarily. Krunal Bauskar published a blog post originally about the InnoDB Intrinsic Tables performance in MySQL 5.7. The InnoDB internal temporary tables are not redo/undo logged. So in general performance is better. However, here is what we need to watch out for:

但這種尖刺就一定意味著性能的下降嗎？Krunal Bauskar曾經寫過一篇關于5.7 InnoDB原生表性能的文章,InnoDB的內部臨時表的操作并不會記錄在redo和undo中，一般情況下相比原本MyISAM引擎的臨時表性能更好點，但是仍需注意一下幾點：

1. Change of the place where MySQL stores temporary tables. InnoDB temporary tables are stored in ibtmp1 tablespace file. There are a number of challenges with that:

   更改MySQL存儲臨時表的位置，原本InnoDB臨時表被存儲在ibtmp1表空間中，可能遇到以下的問題：

   • Location of the ibtmp1 file. By default it is located inside the innodb datadir. Originally MyISAM temporary tables were stored in tmpdir. We can configure the size of the file, but the location is always relative to InnoDB datadir, so to move it to tmpdir we need something like this: innodb_temp_data_file_path=../../../tmp/ibtmp1:12M:autoextend

     ibtmp1文件默認保存在InnoDB的數據目錄，原本MyISAM臨時表被放在MySQL的tmp目錄，如若像MyISAM一樣把臨時表文件存儲在MySQL的tmp目錄，需要更改為innodb_temp_data_file_path=../../../tmp/ibtmp1:12M:autoextend

   • Like other tablespaces it never shrinks back (though it is truncated on restart). The huge temporary table can fill the disk and hang MySQL (bug opened). One way to fix that is to set the maximum size of ibtmp1 file: innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G

     臨時表空間和其他的表空間一樣都不會自動縮小其占用容量，可能會發生臨時表空間容量占滿磁盤，MySQL掛掉的情況，可以通過控制其最大的容量來解決：innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G

   • Like other InnoDB tables it has all the InnoDB limitations, i.e., InnoDB row or column limits. If it exceeds these, it will return “Row size too large” or “Too many columns” errors. The workaround is to set internal_tmp_disk_storage_engine to MYISAM.

     內部臨時InnoDB表同樣共享常規的InnoDB表的限制，如行或列的最大數量限制，超過最大值后，會返回Row size too large” or “Too many columns”的錯誤，遇到此種情況，可以將默認臨時表引擎改回MyISAM

2. When all temp tables go to InnoDB, it may increase the total engine load as well as affect other queries. For example, if originally all datasets fit into buffer_pool and temporary tables were created outside of the InnoDB, it will not affect the* InnoDB* memory footprint. Now, if a huge temporary table is created as an InnoDB table it will use innodb_buffer_pool and may “evict” the existing pages so that other queries may perform slower.

   當所有的臨時表都改成InnoDB引擎后，會增加引擎的負載，影響到其他的查詢。例如：當所有的表都放入buffer_pool中，且臨時表都不是InnoDB引擎，那么不會對InnoDB的內存占用造成任何影響，但是臨時表改成InnoDB引擎后，會和普通InnoDB表一樣占用InnoDB_buffer_pool的空間，而且可能因為臨時表空間占用過大擠出真正的熱數據，讓某些高頻查詢變慢

Conclusion 結論

Beware of the new change in MySQL 5.7, the internal temporary tables (those that are created for selects when a temporary table is needed) are stored in InnoDB ibtmp file. In most cases this is faster. However, it can change the original behavior. If needed, you can switch the creation of internal temp tables back to MyISAM: set globalinternal_tmp_disk_storage_engine=MYISAM

內部InnoDB臨時表（可能僅僅因為是SELECT查詢導致）被保存在InnoDB的ibtmp文件中，在大部分情況下，會加速臨時表或者查詢的速度，但是會影響到原本InnoDB內存的占用情況和原本臨時表處理的邏輯，如果在某種情況確實需要規避的話，可以嘗試將臨時表的引擎改回MyISAM。set global internal_tmp_disk_storage_engine=MYISAM 。這個案例要求我們要對MySQL 5.7的特性要有所注意和了解。