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:

Variable_name	Value
Open_tables	96
Opened_tables	1

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.