Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
1  
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. –  James 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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