Mysql – wrong with table_cache hit rate

cachemy.cnfMySQLoptimizationperformance

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
2097152

MySQL Variable Status:

mysql> SHOW GLOBAL STATUS LIKE 'open%';
+--------------------------+--------+
| 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?

Best Answer

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.

Related Question