In my.cnf I have:

table_cache            = 524288
open_files_limit        = 65535

Both are at max allowed value for mysql config. Both are less than max open file limit:

# cat /proc/sys/fs/file-max

MySQL Variable Status:

| Variable_name            | Value  |
| Open_files               | 193    |
| Open_streams             | 0      |
| Open_table_definitions   | 594    |
| Open_tables              | 802    |
| Opened_files             | 537248 |
| Opened_table_definitions | 4895   |
| Opened_tables            | 9174   |
7 rows in set (0.00 sec)

Server has 32GB RAM. Mostly free!

Still, when I run mysqltuner script:

It says:

[!!] Table cache hit rate: 13% (853 open / 6K opened)

Any reason table_cache hit rate is poor?


One thing, here, is that you should be using this form, instead:

mysql> show global status like '%open%';

Some of these counters are global and some of them are session, so not using the GLOBAL keyword gives you a split set of numbers (especially the Opened_table* values).

The problem with tuning scripts is they can't possibly take into account all of the factors that need to be taken into account when deciding whether values are in a sane range... for example, if you use FLUSH TABLES, your Opened_files and Opened_tables counters will immediately increment because all of the tables that got flushed are re-opened as soon as they're accessed again... which, of course, means nothing at all negative.

Using mysqldump for backups will usually issue a FLUSH TABLES or FLUSH TABLES WITH READ LOCK at the beginning of the backup process, which means if you had been running daily backups and had a server uptime of even a few days, you could easily see a very poor "table_cache hit rate" and, once again, it doesn't mean anything.

"Table_cache hit rate" is not actually a value from MySQL. It's a calculation from two other values. All they are doing in mysqltuner is dividing open_tables by opened_tables (how many are open now, compared with how many have ever been opened).

badprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}% (".hr_num($mystat{'Open_tables'})." open / ".hr_num($mystat{'Opened_tables'})." opened)\n";

So if you observe those two values over time and don't see Opened_tables rapidly incrementing except perhaps during the period after a backup when traffic picks up, then you don't have a problem.

This looks like a false alarm to me.

  • 3
    Upvoted for the explanation and discouraging the tuning scripts. Another issue is that the table_cache doesn't scale up to anywhere near 500k: mysqlperformanceblog.com/2009/11/16/… The way some parts of that tuning script are written, it will tell you to increase some of these settings forever. Jun 19 '13 at 22:11
  • @Michael Thanks for answer. Yes, we have mysqldump running daily. Apart from that I take mysqldump whenever doing major changes to some sites. There are more than 10 apps on server coded in different languages and its highly likely that any of them could be doing FLUSH TABLES. I think its better to ignore this and other warnings from mysqltuner. Mysqltuner also asked me to set query_cache_size and join_buffer_size in GBs which many articles did not recommend.
    – rahul286
    Jun 20 '13 at 11:12
  • Just noticed that mysqltuner is suggesting me to set table_cache (> 524288) which is wrong. 524288 is max value mysql allows as per - dev.mysql.com/doc/refman/5.5/en/…
    – rahul286
    Jun 20 '13 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.