MySQL table_cache and Opened_tables

http://dba.stackexchange.com/questions/5232/mysql-table-cache-and-opened-tables

 

MySQL table_cache and Opened_tables

up vote 8 down vote favorite

4

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.

mysql database-tuning settings

share|improve this question

asked Aug 31 '11 at 6:47 Sam Brightman 16113

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. – RolandoMySQLDBA Aug 31 '11 at 12:28

feedback

3 Answers

active oldest votes

up vote 4 down vote

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.

share|improve this answer

answered Sep 30 '11 at 22:11 Morgan Tocker 1,12129

feedback

up vote 2 down vote

From one of the user comments on the table_cache documentation page:

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:

  SHOW GLOBAL STATUS LIKE '%tmp%';

• Don't increase the table_cache too high

  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.

share|improve this answer

answered Aug 31 '11 at 21:32 DTest 9,01631851

feedback

up vote 1 down vote

First, let's consider those status variables:

Open tables : The number of tables that are open.

Opened_tables : The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

Surprisingly, the answer to your question lies within the question itself.

The two variables would only make more sense if you throw one more status variable into the mix : Uptime (or Uptime_since_flush status for fresh averages after FLUSH STATUS).

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:

• netstat
• iostat
• server load
• Connection Count (as it approaches max_connections)
• Aborted clients
• Aborted connects
• Slow queries

UPDATE 2011-08-31 12:18 EDT

Please note why I also suggested using Uptime_since_flush_status instead of Uptime to get a fix Opened_tables pattern of growth for a given period.

For example, if you run FLUSH STATUS; every Monday at midnight, you can generate an OpenTableFactor:

SELECT*,(Open_tables * Uptime / Opened_Tables) OpenTableFactor FROM(SELECT variable_value Uptime FROM information_schema.global_status
WHERE variable_name ='Uptime_since_flush_status') up,(SELECT variable_value Open_tables FROM information_schema.global_status
WHERE variable_name ='Open_tables') opn,(SELECTIF(variable_value=0,1,variable_value) Opened_tables
FROM information_schema.global_status
WHERE variable_name ='Opened_tables') opnd;

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.

Here is a sample out from one of my employer's clients:

mysql>SELECT*,(Open_tables * Uptime / Opened_Tables) OpenTableFactor FROM(SELECT variable_value Uptime FROM information_sc    hema.global_status     WHERE variable_name ='Uptime_since_flush_status') up,(SELECT variable_value Open_tables FROM informat    ion_schema.global_status     WHERE variable_name ='Open_tables') opn,(SELECTIF(variable_value=0,1,variable_value) Opened_ta    bles     FROM information_schema.global_status     WHERE variable_name ='Opened_tables') opnd;+----------+-------------+---------------+-------------------+| Uptime   | Open_tables | Opened_tables | OpenTableFactor   |+----------+-------------+---------------+-------------------+|14385123|16326|30429078|7717.996519579068|+----------+-------------+---------------+-------------------+1rowinset(0.00 sec)

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:

• open_files_limit
• innodb_open_files
• table_open_cache

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.

share|improve this answer

edited Feb 15 '12 at 20:45

answered Aug 31 '11 at 12:11 RolandoMySQLDBA 34.7k52394

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 Brightman Aug 31 '11 at 15:18