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.
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):
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.
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 (e.g., 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:
- 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=../../../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
- 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.
- 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.
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 global internal_tmp_disk_storage_engine=MYISAM
What’s the point in §../../../tmp/ibtmp1§ when you can do §/tmp/ibtmp1§ ? If you put too many §../§ you get access denied or something else for illegal path, for instance.
As Alex said, its because its a *relative* path only.
Double facepalm.
How do you do this in MariaDB? The solution does not work with MariaDB
MariaDB uses Aria for temporary tables, and this behaviour cannot be changed.
Thanks for highlighting something that’s easy to overlook, appreciated !
In my current company we have 4 separate replication chains with different workloads and configurations. In all cases, using MyISAM with mmap for temporary tables is faster and sensibly reduces io_wait. I would advice users to test both options with their particular case.
Also, in our particular case, unexpected growth of ibtmp forced us to restart MySQL.
While the InnoDB in MariaDB 10.2 is based on MySQL 5.7, the code for dealing with internal temporary tables was never enabled, and it was intentionally removed by me before the 10.2 GA release.
I believe that MyISAM (or in the case of MariaDB, Aria) are a better match for the query executor than InnoDB. InnoDB does not support hash indexes, and records are not deleted directly, but instead delete-marked.
Regarding the temp file path, one can avoid confusing relative paths by setting innodb_data_home_dir to an empty string.
https://dev.mysql.com/doc/refman/5.7/en/innodb-init-startup-configuration.html