Mysql Question posted in
The official MySQL documentation can be found here.

MySQL table_open_cache miss 100% on query spikes

We encountered a situation where we had multiple connections opened to our DB, which seems to cause a high rate of open table cache misses.

I would expect that in this kind of situation we see 100% cache utilisation, but the opposite happened, hit rate was reduced to 0%.

When query rate went down we saw that cache usage increased back to 100%

Do you have any idea what might cuase that?

Tags:

2

Answers


  1. 0

    Take a look at these values

    innodb open files = 10000 or higher.
table_open_cache = 20000
table_definition_cache = 10000

    You can get the current values ​​like this

    SHOW VARIABLES LIKE 'innodb_open_files';
SHOW VARIABLES LIKE 'table_open_cache';
SHOW VARIABLES LIKE 'table_definition_cache';

    And so you can check whether tables are opened again and again. Info: Flush, dump, etc, also flush the cache

    SHOW STATUS LIKE 'Open_tables';
SHOW STATUS LIKE 'Opened_tables';

    For very large DBs you should also check whether the OS (linux) can do this. Otherwise you’ll have to change it

    Login or Signup to reply.
  2. 0

    If you’re experiencing a high rate of open table cache misses with multiple connections to a database, you can try these steps:

    Increase the table_open_cache value
    If the number of opened tables is increasing rapidly, you can try increasing the table_open_cache value. Make sure your operating system can handle the number of open file descriptors required by the table_open_cache setting.

    Check for unused tables
    If the cache contains more than table_open_cache entries and a table in the cache is no longer being used by any threads, MySQL will close and remove it from the table cache.

    Check for table-flushing operations
    If a table-flushing operation occurs, MySQL will close and remove unused tables from the table cache. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search