Temporary tables¶

Only the temporary tables that were explicitly created with CREATE TEMPORARY TABLE or ALTER TABLE are shown, and not the ones created to process complex queries.

table INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLES¶ Version Info:

• 5.6.5-60.0 – Feature introduced

• SESSION_ID – MySQL connection id
• TABLE_SCHEMA – Schema in which the temporary table is created
• TABLE_NAME – Name of the temporary table
• ENGINE – Engine of the temporary table
• NAME – Internal name of the temporary table
• TABLE_ROWS – Number of rows of the temporary table
• AVG_ROW_LENGTH – Average row length of the temporary table
• DATA_LENGTH – Size of the data (Bytes)
• INDEX_LENGTH – Size of the indexes (Bytes)
• CREATE_TIME – Date and time of creation of the temporary table
• UPDATE_TIME – Date and time of the latest update of the temporary table

This table holds information on the temporary tables existing for all connections. You don’t need the SUPER privilege to query this table.

table INFORMATION_SCHEMA.TEMPORARY_TABLES¶ Version Info:

• 5.6.5-60.0 – Feature introduced

• SESSION_ID – MySQL connection id
• TABLE_SCHEMA – Schema in which the temporary table is created
• TABLE_NAME – Name of the temporary table
• ENGINE – Engine of the temporary table
• NAME – Internal name of the temporary table
• TABLE_ROWS – Number of rows of the temporary table
• AVG_ROW_LENGTH – Average row length of the temporary table
• DATA_LENGTH – Size of the data (Bytes)
• INDEX_LENGTH – Size of the indexes (Bytes)
• CREATE_TIME – Date and time of creation of the temporary table
• UPDATE_TIME – Date and time of the latest update of the temporary table

This table holds information on the temporary tables existing for the running connection.

Multiple Rollback Segments¶

Percona Server for MySQL, in addition to the upstream multiple rollback segment implementation, provides the additional Information Schema table: INFORMATION_SCHEMA.XTRADB_RSEG.

INFORMATION_SCHEMA Tables¶

This feature provides the following table:

table INFORMATION_SCHEMA.XTRADB_RSEG¶ Columns:

• rseg_id – rollback segment id
• space_id – space where the segment placed
• zip_size – compressed page size in bytes if compressed otherwise 0
• page_no – page number of the segment header
• max_size – max size in pages
• curr_size – current size in pages

This table shows information about all the rollback segments (the default segment and the extra segments).

Here is an example of output with innodb_extra_rsegments = 8

mysql> select * from information_schema.innodb_rseg;
+---------+----------+----------+---------+------------+-----------+
| rseg_id | space_id | zip_size | page_no | max_size   | curr_size |
+---------+----------+----------+---------+------------+-----------+
|       0 |        0 |        0 |       6 | 4294967294 |         1 |
|       1 |        0 |        0 |      13 | 4294967294 |         2 |
|       2 |        0 |        0 |      14 | 4294967294 |         1 |
|       3 |        0 |        0 |      15 | 4294967294 |         1 |
|       4 |        0 |        0 |      16 | 4294967294 |         1 |
|       5 |        0 |        0 |      17 | 4294967294 |         1 |
|       6 |        0 |        0 |      18 | 4294967294 |         1 |
|       7 |        0 |        0 |      19 | 4294967294 |         1 |
|       8 |        0 |        0 |      20 | 4294967294 |         1 |
+---------+----------+----------+---------+------------+-----------+
9 rows in set (0.00 sec)