I had this patch for a while where one can get listing of both session and global temporary tables across all sessions. It really helped lot of times to understand the bottlenecks of some of the temporary table issues as MySQL never exposed them in the form of SHOW TABLES.
I also added a new status variable called ‘Created_tmp_heap_to_disk_tables‘, which keeps track of how many memory based temp tables are re-created back to disk based.
The patch is now ported to newer MySQL versions, both 5.0 and 5.1; and it works great on most of the platforms that I tested (Mac, Linux and Windows)
It introduces two new INFORMATION_SCHEMA tables, TEMPORARY_TABLES and GLOBAL_TEMPORARY_TABLES along with supporting regular SHOW syntax
|
1 2 3 |
SHOW [SESSION/GLOBAL] TEMPORARY TABLES [FROM db] |
Some examples of how it works at present
Session Temp Tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> show session temporary tables; +----+------+-------+--------+--------------+ | Id | Db | Table | Engine | Name | +----+------+-------+--------+--------------+ | 1 | test | t2 | MEMORY | #sql29da_1_3 | | 1 | test | t1 | MyISAM | #sql29da_1_2 | +----+------+-------+--------+--------------+ 2 rows in set (0.00 sec) mysql> show temporary tables; +----+------+-------+--------+--------------+ | Id | Db | Table | Engine | Name | +----+------+-------+--------+--------------+ | 1 | test | t2 | MEMORY | #sql29da_1_3 | | 1 | test | t1 | MyISAM | #sql29da_1_2 | +----+------+-------+--------+--------------+ 2 rows in set (0.00 sec) mysql> select * from information_schema.temporary_tables; +------------+----------+------------+--------+--------------+ | SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME | +------------+----------+------------+--------+--------------+ | 1 | test | t2 | MEMORY | #sql29da_1_3 | | 1 | test | t1 | MyISAM | #sql29da_1_2 | +------------+----------+------------+--------+--------------+ 2 rows in set (0.00 sec) |
Global Temp Tables Across All Sessions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> select * from information_schema.global_temporary_tables; +------------+----------+------------+--------+--------------+ | SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME | +------------+----------+------------+--------+--------------+ | 6 | test | t3 | MyISAM | #sql29da_6_0 | | 5 | test | t2 | MEMORY | #sql29da_5_3 | | 5 | test | t1 | MyISAM | #sql29da_5_2 | | 4 | venu | v1 | InnoDB | #sql29da_4_0 | +------------+----------+------------+--------+--------------+ 4 rows in set (0.00 sec) mysql> show global temporary tables; +----+------+-------+--------+--------------+ | Id | Db | Table | Engine | Name | +----+------+-------+--------+--------------+ | 6 | test | t3 | MyISAM | #sql29da_6_0 | | 5 | test | t2 | MEMORY | #sql29da_5_3 | | 5 | test | t1 | MyISAM | #sql29da_5_2 | | 4 | venu | v1 | InnoDB | #sql29da_4_0 | +----+------+-------+--------+--------------+ 4 rows in set (0.00 sec) |
- Id: Session ID
- Db: Database Name
- Engine: Engine Type
- Name: Internal Name, how its stored in the file system
Will publish the patch once am done with porting internal temp tables listing by introducing two new columns Table_Type and Info where Table_Type will indicate if its internal or external and Info will have initial 1024 bytes of the query that caused the internal temp table.
Nice, I’d love to see this functionality added! 🙂
We have http://forge.mysql.com/worklog/task.php?id=648 and http://bugs.mysql.com/bug.php?id=20001 that are around for this already – could you update the bug with your patch as well (under the SCA)?
Have you considered adding the table sizes as well?
Nice indeed!
Is there any record for *when* the temptable was created?
Nice work! Another vote for table size.
Thanks Mark, Shlomi and Baron. Yes, there will be a support for SHOW TEMPORARY TABLE STATUS, that will have all the requested info. The task 648 will add temp tables to existing features, initially I thought of doing that, but can break lot of apps if its not properly migrated by them.. so added own extension of syntax, so that its clean and does not break anything
+1 for table sizes
Nice work!
Submitting this patch to MariaDB for inclusion?
Monty has been reviewing and adding quite a few community contributions.
Yes, thanks Arjen. It will be sent to Maria as I have a SCA with them only 🙂
That’s a nice enhancement. Good job! Count me in for the table_size.
Venu,
If you can add table_size, we are ready to include it into percona builds.
[…] SHOW TEMPORARY TABLES I had this patch for a while where one can get listing of both session and global temporary tables across all sessions. It really helped lot of times to understand the bottlenecks of some of the temporary table issues as MySQL never exposed them in the form of SHOW TABLES. […]
[…] meant for desmonstration of different internal keys ]. This is a patch that I might be using for SHOW TEMPORARY TABLES when internal tables are included in the second version. The first version of the patch is already […]
[…] SHOW TEMPORARY FILES, patch from Venu Anuganti […]
I use xtradb 11.2
SELECT * FROM GLOBAL_TEMPORARY_TABLES G;
and
SELECT * FROM TEMPORARY_TABLES T;
having not data
how too setting?
ths
localhost:venu> select * from information_schema.global_temporary_tables;
+————+————–+————+——–+————–+————+—————-+————-+————–+———————+———————+
| SESSION_ID | TABLE_SCHEMA | TABLE_NAME | ENGINE | NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | INDEX_LENGTH | CREATE_TIME | UPDATE_TIME |
+————+————–+————+——–+————–+————+—————-+————-+————–+———————+———————+
| 2 | venu | temp2 | MyISAM | #sql1d14_2_0 | 1 | 7 | 7 | 1024 | 2010-08-26 21:34:26 | 2010-08-26 21:34:36 |
| 1 | test | temp1 | MyISAM | #sql1d14_1_0 | 2 | 7 | 14 | 1024 | 2010-08-26 21:33:57 | 2010-08-26 21:34:05 |
+————+————–+————+——–+————–+————+—————-+————-+————–+———————+———————+
2 rows in set (0.00 sec)
localhost:venu> select * from information_schema.temporary_tables;
+————+————–+————+——–+————–+————+—————-+————-+————–+———————+———————+
| SESSION_ID | TABLE_SCHEMA | TABLE_NAME | ENGINE | NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | INDEX_LENGTH | CREATE_TIME | UPDATE_TIME |
+————+————–+————+——–+————–+————+—————-+————-+————–+———————+———————+
| 2 | venu | temp2 | MyISAM | #sql1d14_2_0 | 1 | 7 | 7 | 1024 | 2010-08-26 21:34:26 | 2010-08-26 21:34:36 |
| 1 | test | temp1 | MyISAM | #sql1d14_1_0 | 2 | 7 | 14 | 1024 | 2010-08-26 21:33:57 | 2010-08-26 21:34:05 |
+————+————–+————+——–+————–+————+—————-+————-+————–+———————+———————+
2 rows in set (0.00 sec)
[…] At Scale”, where he will share some of his latest research. We have Venu to thank for the SHOW TEMPORARY TABLES feature which has made its way into to Percona Server, and for detailed investigative blog posts […]
[…] At Scale”, where he will share some of his latest research. We have Venu to thank for the SHOW TEMPORARY TABLES feature which has made its way into to Percona Server, and for detailed investigative blog posts […]
[…] At Scale”, where he will share some of his latest research. We have Venu to thank for the SHOW TEMPORARY TABLES feature which has made its way into to Percona Server, and for detailed investigative blog posts […]
Hi,
it seems you’ve done a great job and I really would like to try it.
Where can I find the “patch”? Is it an UDF dll or so file?
Did you add the table_size?
Thanks in advance
Toby, its part of Percona builds.