Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Internal Temporary Tables in MySQL 5.7

DZone's Guide to

Internal Temporary Tables in MySQL 5.7

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.

· Database Zone
Free Resource

Want to free your enterprise from the costs of provisioning servers? Learn about serverless architecture with Couchbase and cloud computing.

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 

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 that could have caused the spike to 6K rows inserted.

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?

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 (i.e. for GROUP BY) it will use the InnoDB storage engine.

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. 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:
    • 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.
    • 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 the ibtmp1 file: innodb_temp_data_file_path.
    • 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. 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 theInnoDB 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.

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 an 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 global internal_tmp_disk_storage_engine=MYISAM.

Learn how the world’s first Engagement Database can lower your costs and simplify deployments with Couchbase in the cloud.

Topics:
temporary tables ,innodb ,mysql ,database ,database performance

Published at DZone with permission of Alexander Rubin, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}