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.

tuning_primer says the following:

TABLE CACHE
Current table_open_cache = 812 tables
Current table_definition_cache = 256 tables
You have a total of 498 tables
You have 812 open tables.
Current table_cache hit rate is 1%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

I don't understand this. I only have 498 tables, however my.cnf shows table_cache = 812.

If I have only 498 tables, should I not DECREASE my table_cache value?

The table_definition_cache is set to its default of 256. Should this be increased alsO?

Show status:

|Open_table_definitions            | 498         |
|Open_tables                       | 812         |
|Opened_files                      | 291680      |
|Opened_table_definitions          | 0           |
|Opened_tables                     | 0           |

We are running everything with InnoDB. Any help on this would be appreciated.

share|improve this question
    
What version of MySQL are you using ??? Are you using innodb_file_per_table ??? –  RolandoMySQLDBA Sep 10 '12 at 23:06
    
as the title states, 5.1.63 - yes innodb_file_per_table –  Tom G Sep 11 '12 at 2:07
add comment

2 Answers

A single "open table" instance means different things in different storage engines, but you can have more "open tables" than you actually have... tables.

Tables that are "open" were used at some point since the server was restarted, yet may not have been used for days or hours ... but there's no point in MySQL going around looking for something to do, closing tables that haven't been used in a while, because it will eventually just have to open them again... unless, of course, we want to proactively avoid unnecessary consumption of system resources... and how aggressively it closes idle tables is the gist of the purpose of table_open_cache. Too small, and you end up opening and closing the same tables over and over. Too large, and you demand more file descriptors (and probably small amounts of additional memory) from your operating system than your workload justifies.

What you're seeing here with tuning-primer is partially explained by what we might loosely refer to as the observer effect -- changing what we're measuring, by the act of measuring.

The "tuning-primer" script does things to your server that can alter the subsequent values that the script uses to draw its conclusions... As just one example, it queries information_schema.TABLES while gathering InnoDB stats. What does that do? It opens tables. How many tables does it open? Well... how many have ya got? :) (It may not actually open all of them, depending on some under-the-hood details in the implementation_schema, but it at least opens all of your InnoDB tables).

Depending on the number of tables you have and the level of traffic on your server (such as how many tables were open when the script was started), this script can completely fill the open table cache and then incorrectly report that the cache is 100% used.

To see this, flush the tables on your server and then run only the "file" test from tuning-primer. Better still, flush the tables, wait a while, and then run the "file" test again.

Production Environment Disclaimer: FLUSH TABLES should be a harmless operation -- it "closes" all of the tables as soon as any running queries are done, and they will be immediately re-opened by the next query that needs them, and, if you're primarily InnoDB, this is generally almost instantaneous... but on a busy server or with a lot of MyISAM it could take a few seconds, during which your performance may dip slightly.

mysql> FLUSH TABLES;
shell> ./tuning-primer.sh file

For one of my servers, I get this:

You have 8 open tables.
The table_cache value seems to be fine

An hour later, running it again (without flushing the tables) I get this:

You have 40 open tables.
The table_cache value seems to be fine

But if I do this (with or without a FLUSH TABLES before it):

./tuning-primer.sh innodb

...and then this...

./tuning-primer.sh file

You have 512 open tables.
You should probably increase your table_cache

Not the same answer, and not a meaningful one either, because -- would you believe -- that's exactly the limitation imposed by my table_open_cache variable.

My final thought: the best "tuning strategy" from my experience is to tune as few parameters as you can, as seldom as you can, in MySQL. Aside from the obvious values like innodb_buffer_pool_size, most parameters are best left alone unless a specific reason can be identified why that particular parameter has a value that's not appropriate for your environment.

The developers of this script obviously put a lot of work into it, so it's not my intention to be critical of it. But just like any tool that tries to make informed decisions based on observing a complex system, its conclusions should not be taken as authoritative without corroboration and investigation.

share|improve this answer
add comment

SHOW STATUS is the same as SHOW SESSION STATUS, which shows some values just for your one connection.

SHOW GLOBAL STATUS is what you need for this discussion. Do that, then compute:

  • Opened_tables / Uptime -- If > 2, increase table_open_cache.
  • Opened_files / Uptime -- If > 2, increase table_open_cache.
  • Opened_table_definitions / Uptime -- If > 1, increase table_open_cache.

How much to increase it? Can't say. You need to experiment. But don't go too big.

How many tables are open is not as important as how often you need to reopen.

share|improve this answer
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.