MySQL 5.7 内部臨時表

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

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行插入“名額圖上，發現了從1k行每秒激增到6K行的尖刺，但卻無法和其他活動或者現象連接配接起來，PMM監控圖形上也有同樣的反映。

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):

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

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.

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

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?

原來是與磁盤臨時表有關。在MySQL 5.7版本中，内部磁盤臨時表的預設引擎是InnoDB引擎，這就意味着當SELECT操作需要在磁盤上建立臨時表時（例如GROUP BY操作），就會使用到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.

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

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:

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

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:

（1）ibtmp1檔案預設儲存在InnoDB的資料目錄，原本MyISAM臨時表被放在MySQL的tmp目錄，如若像MyISAM一樣把臨時表檔案存儲在MySQL的tmp目錄，需要更改為

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

（2）臨時表空間和其他的表空間一樣都不會自動縮小其占用容量，可能會發生臨時表空間容量占滿磁盤，MySQL挂掉的情況，可以通過控制其最大的容量來解決：

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

（3）内部臨時InnoDB表同樣共享正常的InnoDB表的限制，如行或列的最大數量限制，超過最大值後，會傳回Row size too large” or “Too many columns”的錯誤，遇到此種情況，可以将預設臨時表引擎改回MyISAM

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.

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

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.

<b>Conclusion 結論</b>

内部InnoDB臨時表（可能僅僅因為是SELECT查詢導緻）被儲存在InnoDB的ibtmp檔案中，在大部分情況下，會加速臨時表或者查詢的速度，但是會影響到原本InnoDB記憶體的占用情況和原本臨時表處理的邏輯，如果在某種情況确實需要規避的話，可以嘗試将臨時表的引擎改回MyISAM：

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.

這個案例要求我們要對MySQL 5.7的特性要有所注意和了解。

原文釋出時間為：2018-03-29

本文作者：張銳志