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.

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.

I like this question because it is a thought-provoking question. This gets a +1 for reminding MySQL developers to take fuller advantage of the status variables to measure DB Server Health.
–
RolandoMySQLDBAAug 31 '11 at 12:28

You should be comparing Open_tables agsinst (Opened_tables / Uptime). If Open_tables climbs above (Opened_tables / Uptime), now you have cause for concern and should keep an eye open for things like the following:

This open table factor amounts to the number that represent the number of open tables at any given moment against the average number of opened tables throughtout a given period. With a FLUSH HOSTS; every week/day/host, that average is against the week/day/hour.

This client normally maintains about 7745 OpenTableFactor at max. If OpenTableFactor drops suddenly (even if a little), it could indicate lower traffic patterns, high aborted conenctions, and so forth. If OpenTableFactor never changes (even if a little), it could present you with an opportunity to change these settings:

Once adjusted, the OpenTableFactor may change constantly or hit another ceiling or plateau. Thus, using different units within the status variables becomes vital for this kind of tuning.

UPDATE 2011-08-31 12:42 EDT

The SQL query I ran for the OpenTableFactor does not works for MySQL 5.0 and back. If you are using MySQL Administrator or MONyog, you could customize a graph using the formula in the query and monitor. MONyog collects the history using SQLLite for later historical graphing. This can be done for any version of MySQL.

Some good suggestions, but I don't think you want to compare two things with different units any more than you want to compare a cumulative value to a current one. And the issue of if this just measures misses remains.
–
Sam BrightmanAug 31 '11 at 15:18

Opened_tables is a status variable that keeps a running tally of the number of additional file descriptors that have been allocated for opening tables at times when the available file descriptors in table_cache have been depleted. ...

Meaning that it is incremented when you go over your table_cache value. So the way I normally check this is to compare opened_tables with uptime, but the key here is to take it over a set interval (once per minute over ten minutes, for instance). If it's increasing it may be an indication you need to increase your table_cache.

A couple caveats to mention:

Another comment in that documentation above: "The status variable 'Opened_tables' will also be incremented by 2 each time you create a temporary table." So if your queries are requiring many temporary tables, this could be the cause of a rapid increase in opened_tables. You can see your temporary table usage using the following query:

The reason for such behaviour is that, if you have large no. of tables with complicated queries joining several tables and multiple connections running those complicated queries, you might end up using all your file descriptors’ cache (table_cache) in that case MySQL uses an algorithm to find the least recently used descriptor, closes it, and replaces it with a new descriptor.

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.