MySQL table_cache and Opened_tables

I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so this is not a valid comparison. The only caveat being that perhaps Opened_tables is only bumped on misses - although even then if the tables being opened per second is still small, it's probably not a problem for it to grow gradually.

If comparing Open_tables to Opened_tables is not valid, is there another way to get measured data for this?

This is on MySQL 5.0, but differences between version are welcome too.

The biggest reason to have a large table_cache is so that LOCK_open mutex is not hot. MySQL prior to 5.5 has a lot of contention when you are trying to open/close tables, so you want to restrict doing this as much as possible, i.e. have a large table cache.

So you don't care about any particular ratio of hits to misses (infact you should ignore ratios altogether - this blog post explains why). What you care about is the miss rate, because the more times this happens per second, the higher the chance that you will have contention (one thread has to wait for another thread to release the lock.)

How do you spot the miss rate? You fetch a few samples of Opened_Tables a few seconds apart during the busiest period of the day, and if there are increases in each sample, it's probably a good idea to see if you can bump up the table_cache.

Note: I very specifically do not recommend comparing to uptime.

StackExchange Database Administrators Q#5232, answer score: 7