Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

For mysql, I have the following tables:

[--] Data in MyISAM tables: 110M (Tables: 254)
[--] Data in InnoDB tables: 32M (Tables: 134)
[--] Data in MEMORY tables: 0B (Tables: 3)

but

[!!] Table cache hit rate: 19% (400 open / 2K opened)

I put 400 as the table cache because I have less than 400 tables. How does this work and what should I do?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Refer to the MySQL page on Open and Closed Tables

MySQL is multi-threaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session.

table_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache size of at least 200 × N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.

You can determine whether your table cache is too small by checking the mysqld status variable Opened_tables, which indicates the number of table-opening operations since the server started:

SHOW GLOBAL STATUS LIKE 'Opened_tables';

If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, you should increase the table cache size.

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.