Low MySQL table cache load

Question

I am working on optimizing my site and databases, and I use mysqltuner.pl to help with this. I got almost all the correct value, except for the frequency of getting into the cache table, no matter how much I can increase it in my.cnf, I'm still at about 0% (284 open / 79 thousand open).

My problem is that I don’t quite understand what is affecting this, so I really don’t know what to look in my queries / database structure to fix this.

Answer 1

The cache must support copies of hot data. Hot data is data that is used a lot. If you cannot extract data from a specific cache, this means that the database needs to go to disk to get it.

- edit--

Sorry if the definition seemed a little unpleasant. a particular cache often spans a lot of objects, and this is database specific, you need to first find out what is cached by the table cache.

- change: some investigation -

Well, it seems (from the answer to this post) that Mysql uses table cache for the data structures used to represent the table. data structures also (by encapsulation or by duplicating table entries for each table) are a set of file descriptors open for data files in the file system. The MyIsam mechanism uses one for the table and one for each index; in addition, each active query element requires its own descriptors.

A file descriptor is the core used for an IO file; it represents the low-level context of a specific file that is being read or written.

I think that you are either misinterpreting the meaning or should be interpreted differently in this context. 284 is the number of active tables in the instance in which you took the picture, and the second value represents the number of times the table was received since the start of Mysql.

I would venture to suggest that you need to take a few snapshots of this reading and see if the first value (active fd in this instance) exceeds the capacity of your cache.

ps, the kernel typically has an upper limit on the number of file descriptors that will allow each process to open, so you may need to configure it if it is too small.

Answer 2

the table cache determines the number of concurrent file descriptors opened by MySQL. Thus, the speed of getting into the table cache will depend on how many tables you have relative to your limit, as well as on how often you re-refer to tables (bearing in mind that it takes into account not only one connection, but also simultaneous connections )

For example, if your limit is 100 and you have 101 tables, and you query each one in order, you will never get a single cache cache. On the other hand, if you only have 1 table, you should usually get close to a 100% result unless you run FLUSH TABLES a lot (as long as your table_cache is set higher than the number of typically concurrent connections).

So, to configure, you need to see how many different tables you can reference with one process / client, and then see how many simultaneous connections you usually can have.

Without any details, I can’t guess if your business is connected with too many concurrent connections or too many frequently referenced tables.