One command, which few people realize exists is SHOW OPEN TABLES – it allows you to examine what tables do you have open right now:

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.

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
AlexN

I also found it to be a useful instrument for optimizations. With a simple script that runs every minute
it is possible to get a real-time statistics of tables that are locked and thus determine candidates for
storage engine change or other optimizations. Somehow it is better than slow logs, because slow logs provides
information not in real time and only about long queries, however fast queries can also affect performance.

0
Peter Zaitsev

Right… This is why we make it possible for all queries to go to the slow query logs with Percona patches 🙂

0
Ovidiu

I see Opened_tables in phpMyAdmin with red. Any idea about how I can close the tables from php ?

thank you.

0
jerel

Hi,

thank you for this post.. do you know why the value of Open_tables

from show status like ‘%table%’;
and

mysql> show open tables;

are different ?

Thanks,
Jerel

0