0

I've been reading on improving performance of my database, and ran into things called table-open-cache and Opened_tables . The MySQL doc told me the following:

Opened_tables

The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

Source

What do they mean by big? How much big is big enough to increase cache? Okay, so I'm checking, how many tables are opened and seeing this:

 ---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Opened_tables | 711353 |

I'm increasing table-open-cache to 32768 (in fact, it was only 2000 before) and opened_tables is still growing.

An hour later, opened_tables is almost 750000.

And 20 hours later, it is 754000, so definitely the growth slowed down significantly, but still going.

Also, number of Open tables in mysqladmin status seems to have stopped increasing, or almost stopped. Well, it still increments by 1 every 5 minutes or so.

Currently mysqladmin status tells me this:

Opens: 754575  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912
Opens: 754578  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912
Opens: 754585  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912
Opens: 754589  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912

I've called it 4 times consecutively, so as you can see that Open tables is not growing, but Opens is growing.

How can it happen, if Open tables is still far away from table_opened_cache value which I set to 32768 about 21 hours ago?

Could you advice anything on tweaking this value and explain the results I got by this moment?

I have plenty of RAM for my purpose, and increasing table cache to 32k made server use 400MB more memory, but I still have 14GB free of 16.

What conclusions should I make at this point?

  • 'flush tables' closes all the tables and they have to be reopened. What does stopping the flush tables achieve? – danblack Apr 3 at 11:21
  • @dan how do I do that? Do you mean that flushing occurs without my intervention? – nicael Apr 3 at 12:38
  • 1
    @nicael You could A) SHOW GLOBAL STATUS LIKE '%open%'; for Beginning anchor list to be posted and B) SHOW GLOBAL STATUS LIKE '%flush%'; for Beginning flush list. An hour later do the same A) SHOW GLOBAL STATUS LIKE '%open%'; and post the AFTER 1 HOUR counts for us to see the activity counts and B) SHOW GLOBAL STATUS LIKE '%flush%'; for the AFTER 1 HOUR flush counts. And we will all see what is going on with this part of your server. Welcome to dba.stackexchange.com – Wilson Hauck Apr 3 at 19:56
  • @Wilson Thank you! I'll do it soon – nicael Apr 13 at 14:01
1

Dived Opened_tables by Uptime to get tables opened per second. But even that is not very useful.

Much more useful are the 3 values of SHOW GLOBAL STATUS LIKE 'table_open_cache%'; More than a few misses or overflows per second is worth noting.

There are many other metrics; provide the data that Wilson mentioned; see can dig further.

Only one FLUSH TABLES since boot up? No big deal.

mysqladmin status does not provide much useful info.

Be aware that the values in STATUS have different flavors, has many flavors:

  • Counters -- monotonically increasing since startup. In most cases, dividing by Uptime is appropriate. Eg: Opened_tables; Com_%.
  • High-water-marks -- eg: Max_used_connections
  • Current values -- eg: Threads_running (usually 1, namely the query that is fetching the values)
| improve this answer | |

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.