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 "
which serves the same purpose but defaults to 400.
So when should you increase the value for
Run the following query:
show status like "open%tables"
this will produce (with different numbers) something similar to:
The variable "
Open_tables" shows you the number of tables currently
held in the table cache.
According to the MySQL documentation the variable "
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
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
These notes have been tested against MySQL 5.1, and may apply to other versions as well.
About the author: Brian Cryer 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.