Cry MySQL How to...

Performance tune the table cache

MySQL maintains a cache of open tables.

As far as I can make out, this is a cache of file descriptors for each database table. So if two separate threads run queries requiring access to the same table at the same time then it will require two file descriptors.

In MySQL 5.1 and earlier the number of tables which could be cached was specified by the system variable "table_cache". The default for this being 64. In MySQL 5.1.3 and later this was replaced by "table_open_cache" which serves the same purpose but defaults to 400.

So when should you increase the value for table_cache/table_open_cache?

Run the following query:

show status like "open%tables"

this will produce (with different numbers) something similar to:

Open_tables 96

The variable "Open_tables" shows you the number of tables currently held in the table cache.

According to the MySQL documentation the variable "Opened_tables" shows the number of tables which have been opened, but in practise this seems to hold the number of tables which have been opened outside of the table cache or because the table cache has been exhausted.

So if the number of Opened_tables is high or if it seems to be rapidly increasing (particularly during busy times) then you should increase the value for table_cache (for MySQL 5.1.2 and earlier) or table_open_cache (for MySQL 5.1.3 and later).

This means that given the figures above (given that Opened_Tables=1) that I don't need to increase the size of my table cache.

Be aware that there are normal activities which will cause opened_tables to increase. For example temporary tables may cause it to increment.

These notes have been tested against MySQL 5.1, and may apply to other versions as well.

About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.