I need some help understanding a few things.

We use Google Cloud SQL MySQL8.0 and had an incident today where the working theory is that the instance was under memory pressure and took longer than expected to process its queries.

When opening the instance view, we currently get a status notification from Google itself suggesting to increase our table_open_cache (which at the moment is the default=4000).

Now I want to increase the value, but am not really sure where to set it at. Google suggests to increase it whenever the amount of open tables is higher than table_open_cache. For us both values seem to always be 4000.

So my first question: SHOW OPEN TABLES and SHOW GLOBAL STATUS LIKE 'Open_tables' give me different values. First is around ~360, the latter is 4000. How can I explain the difference between these two values?

Second question: If open tables can be higher than tables_open_cache why does it look as if for our instance they are always the same?

EDIT: Third question: Is there a way for me to find out who is causing the opening of tables?

1 Answer 1

  • The configuration setting table_open_cache controls the number of entries in a cache of tables. Since it is a "cache": too high = wasting RAM (but not much), too low = extra overhead for some queries (but not much).
  • table_open_cache = 4000 is reasonable for most systems. How much RAM do you have? How many tables are in your databases?
  • SHOW GLOBAL STATUS LIKE 'Table_open_cache%'; provides information on the effectiveness of the cache. Knowing the Uptime is also useful. These settings are the best for judging the health of this cache. I which all caches had metrics like these.
  • Still, those Status values only tell you that the cache needs to be bigger or could be smaller, but not how much bigger/smaller.
  • SHOW OPEN TABLES WHERE In_use OR Name_locked; is probably more interesting than SHOW OPEN TABLES;. The former shows activity; the latter shows entries that may as well be bumped out of the cache. (If that query fails for your version, there is probably a way to get the info from information_schema or performance_schema.)
  • (Q2) If the cache is always full, perhaps something looked through all the tables -- a dump, a monitoring program, etc. This is not necessarily a problem since it is a cache.
  • (Q3) I don't remember whether there is a way to find out which process (by process id) has which tables open.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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