Mysql – How MySQL Table cache hitrate should be calculated

MySQL

I have a MySQL 5.7.15 instance which is monitored by nagios with check_mysql_health plugin.

One of the metrics monitored is tablecache-hitrate which is triggering a critical alert. (hitrate ~50%)

My current table_open_cache value is the default of 2K.

My current GLOBAL stats are:

| Max_used_connections                          | 20      |
| Opened_tables                                 | 5278    |
| Open_tables                                   | 2000    |
| Table_open_cache_hits                         | 803395  |
| Table_open_cache_misses                       | 5278    |
| Table_open_cache_overflows                    | 3271    |

The UPTIME at the time of those status were:

Uptime: 20 days 22 hours 30 min 45 sec

My Current tables by storage engine:

+-----------------------+-----------+
|  engine               | count(*)  |
+-----------------------+-----------+
|    InnoDB             |   105     |
|    MEMORY             |   51      |
|    CSV                |   2       |
|    MyISAM             |   11      |
|    PERFORMANCE_SCHEMA |   87      |
|    NULL               |  115      |
+-----------------------+-----------+

That's 256 tables and 115 views. 371 items.

I have a cronjob that runs a whole mysqldump every saturday. I take a status variables on friday and on sunday. The values of last friday and monday are:

Friday:

| Max_used_connections                          | 20        |
| Open_tables                                   | 2000      |
| Opened_tables                                 | 4309      |
| Table_open_cache_hits                         | 513027    |
| Table_open_cache_misses                       | 4309      |

Monday:

| Max_used_connections                          | 20       |
| Open_tables                                   | 2000     |
| Opened_tables                                 | 4924     |
| Table_open_cache_hits                         | 699751   |
| Table_open_cache_misses                       | 4924     |

I've seen a couple of ways to calculate tablecache_hitrate:

  1. Open_tables / Opened_tables
  2. Table cache hit rate = table_open_cache*100/Opened_tables

With those approaches the table_cache_hitrate will lower as the uptime increases, because of new tables created, mysqldump the whole database (opening all tables), etc…

Q1. Does mysqldump bypass table_open_cache? Or it uses already cached tables?

So i think that they aren't a reliable way to calculate tablecache_hitrate.

I guess for MySQL 5.6.6+, it can be calculated based on Table_open_cache_hits and Table_open_cache_misses.

Q2. Is this correct? Or what would be the most accurate way to calculate table_open_cache hitrate?

Regards!

Best Answer

I could be that the plugin is looking at deltas, and the dump had to open all the tables at least once, thereby running through the cache and getting a burst of "misses".

The "hits" are quite high relative to the other values, so I don't see a real 'problem'. But you could increase table_open_cache and table_open_cache_instances.

What was Max_used_connections? I guess that number was rather high, and the many connections were opening lots of tables, thereby reaching up to (and past) 2000. Later, when the dump came along, many of the little-used tables had been bumped out of cache, hence a low hit rate.

Suggest you grab those GLOBAL STATUS readings before and after a dump. This may (or may not) confirm my guesses.

Related Question