【MySQL】【翻譯】MySQL Internal Temporary Tables in MySQL 5.7（MySQL 5.7 內(nèi)部臨時(shí)表）

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

積石山保安族東鄉(xiāng)族ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書(shū)未來(lái)市場(chǎng)廣闊！成為創(chuàng)新互聯(lián)公司的ssl證書(shū)銷售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：13518219792（備注：SSL證書(shū)合作）期待與您的合作！

翻譯:張銳志

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.

本文中研究了在沒(méi)有寫查詢的情況下，InnoDB行插入?yún)s因內(nèi)部臨時(shí)表的問(wèn)題發(fā)生性能尖刺的情形。

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

事情發(fā)生在我研究一個(gè)客戶的案例時(shí)，在”InnoDB行插入“指標(biāo)圖上，發(fā)現(xiàn)了從1k行每秒激增到6K行每秒的尖刺，但卻無(wú)法和其他活動(dòng)或者現(xiàn)象連接起來(lái)，PMM監(jiān)控圖形上也有同樣的反映。

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.

其他例如句柄和接口的圖形都沒(méi)有顯示同樣的尖刺，在無(wú)法開(kāi)啟general log的情況下，我們嘗試檢查了所有的日志，performance_schema，觸發(fā)器，存儲(chǔ)過(guò)程，預(yù)編譯語(yǔ)句，甚至包括binlog后發(fā)現(xiàn)沒(méi)有任何單個(gè)的寫查詢語(yǔ)句可以導(dǎo)致每秒插入飆升到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?

在最后才發(fā)現(xiàn)，行插入飆升一定和DML有關(guān)的這種想法是錯(cuò)誤的，出乎意料的是，尖刺是由于SELECT查詢導(dǎo)致的，但為何SELECT查詢會(huì)導(dǎo)致大量的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.

原來(lái)是與磁盤臨時(shí)表有關(guān)。在MySQL 5.7版本中，內(nèi)部磁盤臨時(shí)表的默認(rèn)引擎是InnoDB引擎，這就意味著當(dāng)SELECT操作需要在磁盤上創(chuàng)建臨時(shí)表時(shí)（例如GROUP BY操作），就會(huì)使用到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曾經(jīng)寫過(guò)一篇關(guān)于5.7 InnoDB原生表性能的文章,InnoDB的內(nèi)部臨時(shí)表的操作并不會(huì)記錄在redo和undo中，一般情況下相比原本MyISAM引擎的臨時(shí)表性能更好點(diǎn)，但是仍需注意一下幾點(diǎn)：

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存儲(chǔ)臨時(shí)表的位置，原本InnoDB臨時(shí)表被存儲(chǔ)在ibtmp1表空間中，可能遇到以下的問(wèn)題：

   • 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文件默認(rèn)保存在InnoDB的數(shù)據(jù)目錄，原本MyISAM臨時(shí)表被放在MySQL的tmp目錄，如若像MyISAM一樣把臨時(shí)表文件存儲(chǔ)在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

     臨時(shí)表空間和其他的表空間一樣都不會(huì)自動(dòng)縮小其占用容量，可能會(huì)發(fā)生臨時(shí)表空間容量占滿磁盤，MySQL掛掉的情況，可以通過(guò)控制其大的容量來(lái)解決：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.

     內(nèi)部臨時(shí)InnoDB表同樣共享常規(guī)的InnoDB表的限制，如行或列的大數(shù)量限制，超過(guò)大值后，會(huì)返回Row size too large” or “Too many columns”的錯(cuò)誤，遇到此種情況，可以將默認(rèn)臨時(shí)表引擎改回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.

   當(dāng)所有的臨時(shí)表都改成InnoDB引擎后，會(huì)增加引擎的負(fù)載，影響到其他的查詢。例如：當(dāng)所有的表都放入buffer_pool中，且臨時(shí)表都不是InnoDB引擎，那么不會(huì)對(duì)InnoDB的內(nèi)存占用造成任何影響，但是臨時(shí)表改成InnoDB引擎后，會(huì)和普通InnoDB表一樣占用InnoDB_buffer_pool的空間，而且可能因?yàn)榕R時(shí)表空間占用過(guò)大擠出真正的熱數(shù)據(jù)，讓某些高頻查詢變慢

Conclusion 結(jié)論

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

內(nèi)部InnoDB臨時(shí)表（可能僅僅因?yàn)槭荢ELECT查詢導(dǎo)致）被保存在InnoDB的ibtmp文件中，在大部分情況下，會(huì)加速臨時(shí)表或者查詢的速度，但是會(huì)影響到原本InnoDB內(nèi)存的占用情況和原本臨時(shí)表處理的邏輯，如果在某種情況確實(shí)需要規(guī)避的話，可以嘗試將臨時(shí)表的引擎改回MyISAM。set global internal_tmp_disk_storage_engine=MYISAM 。這個(gè)案例要求我們要對(duì)MySQL 5.7的特性要有所注意和了解。

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。

分享題目：【MySQL】【翻譯】MySQL5.7的內(nèi)部臨時(shí)表新特性-創(chuàng)新互聯(lián)
文章路徑：http://m.newbst.com/article0/ccjgoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站制作、網(wǎng)頁(yè)設(shè)計(jì)公司、外貿(mào)建站、網(wǎng)站制作、響應(yīng)式網(wǎng)站、域名注冊(cè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)