How to optimize table_cache?

Question

I searched Google but couldn't find an exact answer. Some have this value at 10000 or even 20000. Others say that even 1000 is too much for 1gb of RAM. I'm Confused.

My number of opened_tables is growing. Mysqltuner says I should raise the table_cache value. I tried to raise it to 2K, then 3K, then 4K, then I made it 512 to see what happens. Here is the report:

Currently running supported MySQL version 5.0.67-community-nt-log
Operating on 32-bit architecture with less than 2GB RAM
Archive Engine Installed
Berkeley DB Engine Not Installed
Federated Engine Installed
InnoDB Engine Installed
ISAM Engine Not Installed
NDBCLUSTER Engine Not Installed
Data in InnoDB tables: 12M (Tables: 3)
Data in MEMORY tables: 380K (Tables: 2)
Data in MyISAM tables: 83M (Tables: 84)
Total fragmented tables: 16
All database users have passwords assigned
Up for: 16h 12m 55s (161K q [2.000 qps], 6K conn, TX: 281M, RX: 22M)
Reads / Writes: 69% / 31%
Total buffers: 128.0M global + 2.1M per thread (100 max threads)
Maximum possible memory usage: 334.3M (16% of installed RAM)
Slow queries: 1% (11/161K)
Highest usage of available connections: 10% (10/100)
Key buffer size / total MyISAM indexes: 32.0M/4.1M
Key buffer hit rate: 97% (849K cached / 20K reads)
Query cache efficiency: 28% (26K cached / 93K selects)
Query cache prunes per day: 0
Sorts requiring temporary tables: 1% (1 temp sorts / 21K sorts)
Temporary tables created on disk: 1% (2 on disk / 15K total)
Thread cache hit rate: 99% (10 created / 6K connections)
Table cache hit rate: 5% (139 open / 2K opened)
Open file limit used: 20% (232/1K)
Table locks acquired immediately: 99% (116K immediate / 116K locks)
InnoDB data size / buffer pool: 12.5M/32.0M
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increase table_cache gradually to avoid file descriptor limits
table_cache (> 512)
Scan Complete

mysql> show global STATUS LIKE 'open%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 222   |
| Open_streams  | 0     |
| Open_tables   | 127   |
| Opened_tables | 2335  |
+---------------+-------+
4 rows in set (0.00 sec)

However, opened_tables is still growing. Should I raise it again? Or is it not worth attention?

In addition, some people say that the problem is usually in creating a lot of temp tables. As we can see from the stats, it's not my case. What's the ratio between table_cache and RAM?

Answer 1

The reason you didn't find an answer is that there's not an answer. The appropriate value for table_cache doesn't correlate to an amount of system memory.

Note that table_cache was renamed table_open_cache in MySQL 5.1.3 and is referred to by the new name in newer versions of MySQL.

As I mentioned in my answer to this similar recent question, among the things that can increment opened_tables, some of them are both harmless and inevitable (such as making backups with mysqldump, which, in most cases, issues some variant of FLUSH TABLES, which increments the counters as each table is reopened after the flush, either for the purpose of backing up the table or by other client threads accessing the recently-flushed tables, which must naturally be re-opened).

What I didn't think to mention in that post is -- and I trust you will find some measure of amusement in this -- that the act of running mysqltuner against your server can, itself, increment the opened_tables counter, thus invalidating its own results.

A commenter on my answer referenced this article, where the author points out that tweaking table_cache worsens performance the more "optimally" you set it.

The bottom line is that this is a good example of a variable that is best left alone unless you have a specific reason to tweak it, and the output of a tuning script doesn't count. Tuning scripts are often well-intentioned but that is sometimes the best thing that can be said about them:

The problem with correlations that sometimes appear to be true is that people begin to believe they will always be true. Oracle DBAs abandoned ratio-based tuning years ago, and we wish MySQL DBAs would follow their lead.

We wish even more fervently that people wouldn’t write “tuning scripts” that codify these dangerous practices and teach them to thousands of people. This leads to our second suggestion of what not to do: don’t use tuning scripts! There are several very popular ones that you can find on the Internet. It’s probably best to ignore them.

We also suggest that you avoid the word “tuning,” which we’ve used liberally in the past few paragraphs. We favor “configuration” or “optimization” instead (as long as that’s what you’re actually doing; see Chapter 3). The word “tuning” conjures up images of an undisciplined novice who tweaks the server and sees what happens. We suggested in the previous section that this practice is best left to those who are researching server internals.

“Tuning” your server can be a stunning waste of time.

^{-- Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Locations 12173-12182). OReilly Media - A. Kindle Edition.}

As a side note, MySQL 5.0 is end of life. MySQL 5.0.67 was released over 4 years ago and a significant number of bugs were fixed in the 5.0 release series since then. If I had a 5.0.67 server, and it was working, then the last thing I would want to do would be to change anything about its configuration.

Answer 2

You are opening less than 1 table per hour. That is insignificant.

The metric that should have been looked at is Opened_tables / Uptime. That's "per second".

I suggest that anything under 2/sec is fine.

Back to the title question:

Consider increasing table_open_cache if

Opened_tables / Uptime > 2
Opened_table_definitions / Uptime > 1
Table_open_cache_misses / Uptime > 1
Table_open_cache_overflows / Uptime > 1
Open_tables = table_open_cache

If you can't raise it, check open_files_limit and the OS limit.

If your MySQL is new enough, set table_open_cache_instances to (perhaps) the number of cores you have.

Answer 3

It could be because your application might be causing MySQL to create tmp tables if Open_tables value is well below the table_open_cache value. Creating tmp tables also increments Opened_tables counter. I run Mysqltuner script too and notice the table cache hit rate is below 50%. I was wondering why it does not make use of available free cache space, yet the number keeps increasing. The same book that is referenced by Michael explains it. High Performance MySQL: Optimization, Backups, and Replication

Answer 4

It depends on whether it's Linux or Windows.

For Windows with default connection, the value cannot be greater than (2048-100)/2 = 978, due to open table x 2 + connection needs to be smaller than 2048. Your Windows event viewer will tell you if you ever mis-configure it.

On linux, the value can be much higher, due to it has much higher file descriptor limit. You can increase the limit by increasing ulimit.