Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.1Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 37.3Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 217.0Kb
Man Pages (Zip) - 329.9Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Temporary Tablespace

14.4.12 Temporary Tablespace

The temporary tablespace is used for non-compressed InnoDB temporary tables and related objects. The innodb_temp_data_file_path configuration option defines a relative path for the temporary tablespace data file. If innodb_temp_data_file_path is not defined, a single auto-extending 12MB data file named ibtmp1 is created in the data directory. The temporary tablespace is recreated on each server start and receives a dynamically generated space ID, which helps avoid conflicts with existing space IDs. The temporary tablespace cannot reside on a raw device. Startup is refused if the temporary tablespace cannot be created.

The temporary tablespace is removed on normal shutdown or on an aborted initialization. The temporary tablespace is not removed when a crash occurs. In this case, the database administrator may remove the temporary tablespace manually or restart the server with the same configuration, which removes and recreates the temporary tablespace.

INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO provides metadata about active InnoDB temporary tables. With the exception of optimized internal temporary tables used by InnoDB, INNODB_TEMP_TABLE_INFO reports on all user and system-created temporary tables that are active within a given InnoDB instance.

Temporary Table Undo Logs

Temporary table undo logs are used for temporary tables and related objects. This type of undo log is not a redo log, as temporary tables are not recovered during crash recovery and do not require redo logs. Temporary table undo logs are, however, used for rollback while the server is running. This special type of non-redo undo log benefits performance by avoiding redo logging I/O for temporary tables and related objects. Temporary table undo logs reside in the temporary tablespace. 32 rollback segments are reserved for temporary table undo logs for transactions that modify temporary tables and related objects.

User Comments
Sign Up Login You must be logged in to post a comment.