Understanding MySQL 5.7 Shared Temporary Tablespace and innodb_temp_data_file_path

Shared Temporary Tablespace

MySQL 5.7.1 introduced a shared temporary tablespace that separates temporary tables from the system tablespace (ibdata). It stores non‑compressed InnoDB temporary tables, related objects, and rollback segments. The space does not participate in crash recovery, so no redo log is written.

The default size is 12 MB, stored in ibtmp1 under innodb_data_home_dir (normally the datadir). The file is removed when the server stops and recreated on start.

You can change the file name and size with the new parameter innodb_temp_data_file_path, e.g.:

innodb_temp_data_file_path = temp_tablespace:64M:autoextend

This creates a temp_tablespace file of 64 MB that auto‑extends.

InnoDB Temporary Table Statistics Optimization

Prior to 5.7, temporary tables were invisible to SHOW TABLES and information_schema.TABLES. Starting with 5.7, the INNODB_TEMP_TABLE_INFO table provides metadata for temporary tables.

+----------+---------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME          | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+---------------+--------+-------+----------------------+---------------+
| 68       | #sql2b79_35_0 | 4      | 37    | FALSE                | FALSE         |
+----------+---------------+--------+-------+----------------------+---------------+

Field meanings:

TABLE_ID : internal table identifier.

NAME : temporary table name (e.g., #sql2b79_35_0).

N_COLS : number of visible columns plus three hidden InnoDB columns.

SPACE : temporary tablespace ID, non‑zero and changes after a restart.

PER_TABLE_TABLESPACE : TRUE if the table has its own .ibd file; FALSE if it uses the shared tablespace.

IS_COMPRESSED : TRUE if the table is created with ROW_FORMAT=COMPRESSED.

innodb_tmpdir

Available from MySQL 5.7.11, innodb_tmpdir overrides tmpdir for temporary files created during online DDL operations. It does not affect regular temporary tables.

Comparison with tmpdir

Both tmpdir and the shared temporary tablespace store temporary data that is deleted on server shutdown. However, tmpdir is the default location for files created by CREATE TEMPORARY TABLE, while the shared tablespace ( ibtmp1) holds data for non‑compressed temporary tables.

Practical Test

Two temporary tables are created:

CREATE TEMPORARY TABLE compress_table (id int, name char(255)) ROW_FORMAT=COMPRESSED;
CREATE TEMPORARY TABLE uncompress_table (id int, name char(255));

After inserting 50,000 rows into each table, the storage locations differ:

Compressed table data appears as an .ibd file under /tmp (e.g., #sqlb48_3_0.ibd).

Uncompressed table data resides in ibtmp1 within the datadir.

root@localhost> INSERT INTO compress_table SELECT id, name FROM a LIMIT 50000;
-- file size grows to ~11 MB in /tmp
root@localhost> INSERT INTO uncompress_table SELECT id, name FROM a LIMIT 50000;
-- ibtmp1 grows to ~80 MB in the datadir

The test confirms that compressed temporary tables use files in tmpdir, while non‑compressed ones use the shared temporary tablespace.

slave_load_tmpdir

The slave_load_tmpdir parameter (not new in 5.7) defaults to the value of tmpdir. It stores temporary files created on a replica when processing LOAD DATA INFILE statements replicated from the master. These files are removed after the load completes, but if the replica restarts before removal, replication can fail. It is advisable to point this directory to reliable storage when such statements are used.

Potential Issues

From MySQL 5.7.6 onward, page sizes of 32 KB or 64 KB are supported, but ROW_FORMAT=COMPRESSED works only with the default 16 KB page size. To ensure compression works, innodb_file_format must be set to Barracuda.

Overall, the shared temporary tablespace provides a cleaner separation of temporary data, reduces ibdata growth, and offers configurable sizing, but administrators must be aware of the interaction with tmpdir, compression limits, and replication‑related temporary files.