Shulou(Shulou.com)06/01 Report--

MySQL Internal Temporary Tables in MySQL 5.7

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

Translated by: Zhang Ruizhi

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.

In this article, we examined the performance spike of InnoDB row inserts due to internal temporary table problems without writing queries.

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

It happened when I was studying a customer case and found spikes on the "InnoDB row insertion" metric graph that surged from 1k rows per second to 6K rows per second, but could not be connected to other activities or phenomena, and the same reflection was seen on the PMM monitoring graph.

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.

Other graphs such as handles and interfaces did not show the same spikes, and without opening the general log, we tried to check all logs, performance_schemas, triggers, stored procedures, precompiled statements, and even binlogs and found that no single write query statement could cause inserts to spike to 6K lines per second.

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?

Finally, it turns out that the idea that row insert spikes must be DML related is wrong. Unexpectedly, spikes are caused by SELECT queries, but why do SELECT queries cause a lot of InnoDB row inserts?

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.

It is related to disk temporary tables. In MySQL version 5.7, the default engine for internal disk temporary tables is the InnoDB engine, which means that when SELECT operations require temporary tables to be created on disk (for example, GROUP BY operations), the InnoDB engine is used.

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:

But does this spike necessarily mean a drop in performance? Krunal Bauskar once wrote an article about the performance of 5.7 InnoDB native tables. The operation of InnoDB internal temporary tables is not recorded in redo and undo. In general, the performance of temporary tables is better than that of the original MyISAM engine, but there are still a few points to note:

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:

Change the location where MySQL stores temporary tables. Originally, InnoDB temporary tables are stored in the ibtmp1 table space. You may encounter the following problems:

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

The ibtmp1 file is stored in the data directory of InnoDB by default. The MyISAM temporary table is originally stored in the tmp directory of MySQL. If the temporary table file is stored in the tmp directory of MySQL like MyISAM, it needs to be changed to 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

Temporary tablespaces, like other tablespaces, do not automatically shrink their capacity. Temporary tablespaces may fill up the disk and MySQL hangs. You can solve this problem by controlling their maximum capacity: 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.

Internal temporary InnoDB tables also share the limitations of regular InnoDB tables, such as the maximum number of rows or columns. If the maximum number is exceeded, an error of Row size too large" or "Too many columns"will be returned. In this case, the default temporary table engine can be changed back to MyISAM.

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.

When all temporary tables are changed to the InnoDB engine, the load on the engine will increase, affecting other queries. For example, when all tables are placed in buffer_pool and temporary tables are not InnoDB engines, the memory footprint of InnoDB will not be affected. However, after temporary tables are changed to InnoDB engines, they will occupy the space of InnoDB_buffer_pool like ordinary InnoDB tables, and some high-frequency queries may be slowed down because the temporary table space is too large to squeeze out real hot data.

Conclusion 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

Internal InnoDB temporary tables (probably only because of SELECT queries) are saved in the ibtmp file of InnoDB. In most cases, this will speed up the temporary tables or queries, but it will affect the original InnoDB memory consumption and the original temporary table processing logic. If you really need to avoid it in some cases, you can try to change the temporary table engine back to MyISAM. set global internal_tmp_disk_storage_engine=MYISAM 。This example requires us to pay attention to and understand MySQL 5.7 features.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.