Comments on: Temporary Tables in MySQL – Never Ending Story?

By: Rahul

Rahul — Tue, 13 Dec 2022 17:07:53 +0000

Following statement is partially correct.

shared ibtmp1 table space is no longer used for either temporary table type

As per the manual > The global temporary tablespace (ibtmp1) stores rollback segments for changes made to user-created temporary tables.

By: Ross

Ross — Tue, 30 Nov 2021 23:06:07 +0000

Good to see ever-relevant topics like this.

On a tangent from this post, can we check metrics for resource usage?

More than the “SHOW” status changes in created_tmp_tables, but also how much temp table usage hits mutex resources and other scale-related issues?

Reason for asking: We know (thanks to earlier Percona blog!) that INSERTs on PK-less/unique key-less tables force the use of the gen_clust_index, and in turn, use a global resource. Global resource as well for DROP/CREATE on temporary tables. Can this “hit” be measured across these MySQL versions? Percona flavor? MariaDB?

By: Marko Mäkelä

Marko Mäkelä — Tue, 23 Nov 2021 07:30:18 +0000

Some things could still be improved about temporary tables in MySQL. As far as I understand, in a workload that involves writes to both temporary and persistent InnoDB tables, current (or former!) pages of temporary tables may be unnecessarily written to the data files. Starting with MariaDB Server 10.5.9, unnecessary writes of freed pages (such as after DROP TEMPORARY TABLE) will be avoided. Starting with MariaDB 10.5.12, pages of temporary tables would only be written out if we run out of buffer pool (LRU eviction flushing), never as part of checkpoint flushing.