One command, which few people realize exists is SHOW OPEN TABLES – it allows you to examine what tables do you have open right now:
mysql> show open tables from test;
| Database | Table | In_use | Name_locked |
| test | a | 3 | 0 |
1 row in set (0.00 sec)
This command lists all non-temporary tables in the table-cache, showing each of them only once (even if table is opened more than ones)
In_use show how many threads are currently using this table, meaning have it locked or waiting on the table lock for this table to lock it.
Name_locked shows whenever the name for this table is locked. It is used for DROP or RENAME TABLE, so you would very rarely see this field to contain anything else than 0.
Besides just figuring out what tables are in the table_cache this command is rather helpful to understand if there is activity on the given table. Just run “FLUSH TABLES mytable” and examine open tables later – if you see this table in table cache again chances are it is being used.
Note however if you’re starting MySQL Command line client without “-A” option it opens all tables in the active database to allow tab completion which can screw results.
Another use for this command is pre-flush implementation (as part of your backup routine) – instead of running FLUSH TABLES on ALL tables one by one you can run SHOW OPEN TABLES and flush only open tables, when run it again and see how many tables are open and in use and if FLUSH TABLES WITH READ LOCK can be run or not.
Unfortunately this command does not really help to answer the very common question you may have during table lock troubleshooting – who is holding lock for this table ?
I would much rather see all entries in the table_cache used, not grouped by the table, with thread_id using the table set (0 if table is not in use), lock_type READ/WRITE/READ_LOCAL etc as well as whenever the thread is looking for lock right now.
It also deserves to be converted to INFORMATION_SCHEMA table – so it would be easily to operate it using SQL commands.
Another thing which would be handly is LRU position for the given table (so you can see what tables are candidates for replacement) and the timestamp when this table was locked (or lock wait started) – MySQL anyway initializes the timer so it would not be much overhead to store that time together in the table cache. This could allow to understand table locks much better.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.