0

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!

  • What was the Uptime when those STATUS values were grabbed? (Or grab them again, plus Uptime.) – Rick James Jan 6 '17 at 1:19
  • It should not be calculated at all. Unless you are a MySQL developer, it's usually best to pretend that you've never heard of the open table cache, becauae it's a very delicate mechanism that will kill your performance if you try to optimize it. See this answer and this answer. As a DBA, I've never had a good reason to change this, but more than one server from "before my time" was immediately cured of poor performance by removing configured values for this, and reverting to defaults. – Michael - sqlbot Jan 7 '17 at 2:17
  • so, should i disable that metric? i would not want to receive notifications of a metric that doesn't give me "important" information... – kriegu Jan 13 '17 at 16:59
0

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.

  • The "per second" values are reasonably small; so, again, I am not worried about the numbers. Perhaps most of the 615 "misses" were due to the dump. – Rick James Jan 6 '17 at 17:54
  • Yes, i think so too.. so which would be the more accurate way to calculate tablecache_hitrate? – kriegu Jan 7 '17 at 0:07
  • Are you hoping to modify the Nagios plugin? – Rick James Jan 7 '17 at 4:06
  • I want to tune my DB correctly, i want to make sure that this value is being calculated properly so i can act accordingly... and yes, if we agree on a best way to calculate this value, i can make the comments and link to this question to the developer of the plugin... – kriegu Jan 13 '17 at 17:02
  • Since you seem to have a burst of table opens when you do the dump, you could increase table_open_cache until the problem goes away, or you could ignore it during the dump (since it is relatively harmless). Based on the numbers you have presented, I am not sure how to find the 'perfect' value for that setting. – Rick James Jan 13 '17 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.