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:
- Change of the place where MySQL stores temporary tables. InnoDB temporary tables are stored in
ibtmp1tablespace file. There are a number of challenges with that:
- Location of the
ibtmp1file. 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:
- 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
- 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
- Location of the
- 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_pooland 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_pooland may "evict" the existing pages so that other queries may perform slower.
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.