InnoDB Temporary Tablespaces

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.