I've been reading on improving performance of my database, and ran into things called
Opened_tables . The MySQL doc told me the following:
The number of tables that have been opened. If
Opened_tablesis big, your
table_open_cachevalue is probably too small.
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 |
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.
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?