InnoDB Temporary Tablespaces

MariaDB starting with 10.2

The use of the temporary tablespaces in InnoDB was introduced in MariaDB 10.2. In earlier versions, temporary tablespaces exist as part of the InnoDB system tablespace or were file-per-table depending on the configuration of the innodb_file_per_table system variable.

When the user creates a temporary table using the CREATE TEMPORARY TABLE statement and the engine is set as InnoDB, MariaDB creates a temporary tablespace file. When the table is not compressed, MariaDB writes to a shared temporary tablespace as defined by the innodb_temp_data_file_path system variable. When compressed, the temporary table is written to a dedicated temporary tablespace for that table. MariaDB deletes temporary tablespaces when the server shuts down gracefully and is recreated when it starts again. It cannot be placed on a raw device.

Internal temporary tablespaces, (that is, temporary tables that cannot be kept in memory) use either Aria or MyISAM, depending on the aria_used_for_temp_tables system variable. You can set the default storage engine for user-created temporary tables using the default_tmp_storage_engine system variable.

Sizing Temporary Tablespaces

In order to size temporary tablespaces, use the innodb_temp_data_file_path system variable. Its syntax is the same as the innodb_data_file_path system variable. That is, a file name, size and option. By default, it writes a 12MB autoextending file to ibtmp1 in the data directory.

To see the current size of the temporary tablespace from MariaDB, query the Information Schema:

SELECT FILE_NAME AS "File Name"
   INITIAL_SIZE AS "Initial Size",
   DATA_FREE AS "Free Space",
   TOTAL_EXTENTS * EXTENT_SIZE AS "Total Size",
   MAXIMUM_SIZE AS "Max"
FROM information_Schema.FILES
WHERE TABLESPACE_NAME = "innodb_temporary";

+-----------+--------------+------------+------------+------+
| File Name | Initial Size | Free Space | Total Size | Max  |
+-----------+--------------+------------+------------+------+
| ./ibtmp1  |     12582912 |     621456 |   12592912 | NULL |
+-----------+--------------+------------+------------+------+

To increase the size of the temporary tablespace, set a new value with a larger size on the innodb_temp_data_file_path system variable. You provide additional paths to spread the usage out between multiple tablespace files. Setting the last file to autoextend ensures that you won't run out of space.

[mysqld]
...
innodb_temp_data_file_path=ibtmp1:32M;ibtmp2:32M:autoextend

Unlike normal tablespaces, temporary tablespaces are deleted when you stop MariaDB. To shrink temporary tablespaces to their minimum sizes, restart the server.

Comments

Comments loading...