MySQL Table Cache

mysql-table-cache

MySQL Table Cache
The table cache is used to stores objects that represent tables.Each object in the cache holds associated table’s parsed .frm Each object in the cache contains the associated table’s parsed .frm file, plus other data.Exactly what else is in the object depends on the table’s storage engine.

For MyISAM, it holds the table data and/or index file descriptors.The table cache can help you reuse resources.For instance, when a query requests access to a MyISAM table, MySQL might be able to give it a file descriptor from the cached object. Although this does avoid the cost of opening a file descriptor, that’s not as expensive as you might think. Opening and closing file descriptors are very fast on local storage; the server should be able to do it a million times a second easily.The real benefit of the table cache is for MyISAM tables, where it lets the server avoid modifying the MyISAM file headers to mark a table.The table cache’s design is one of the areas where the separation between the server and the storage engines is not completely clean, for historical reasons.

The table cache is a little less important for InnoDB, because InnoDB doesn’t rely on it for as many
purposes (such as holding file descriptors; it has its own version of a table cache for this purpose ). However, even InnoDB benefits from caching the parsed .frm files.

The table cache is separated into two parts:
a. A cache of open tables
b. A table definition cache (configured via the table_open_cache and table_definition
_cache variables).

Thus, the table definitions (the parsed .frm files) are separated from the other resources, such as file descriptors.Opened tables are still per-thread, per-table-used, but the table definitions are global and can be shared among all connections efficiently.You can generally set table_definition_cache high enough to cache all your table definitions.Unless you have tens of thousands of tables, this is likely to be the easiest approach.If the Opened_tables status variable is large or increasing, the table cache might not be large enough, and you can consider increasing the table_cache system variable (or table_open_cache, in MySQL 5.1).However, note that this counter increases when you create and drop temporary tables, so if you do that a lot, you’ll never get the counter to stop increasing.One downside to making the table cache very large is that it might cause longer shutdown times when your server has a lot of MyISAM tables, because the key blocks have to be flushed and the tables have to be marked as no longer open. It can also make FLUSH TABLES WITH READ LOCK take a long time to complete, for the same reason.More seriously, the algorithms that check the table cache aren’t very efficient; more on this later.

If you get errors indicating that MySQL can’t open any more files, you might need to increase the number of files MySQL is allowed to keep open. You can do this with the open_files_limit server variable in your my.cnf file.

The table caches don’t really use much memory, and it can be beneficial when it conserve resources. Although creating a new thread and opening a new table aren’t really expensive compared to other things MySQL might do, the overhead can add up. Caching threads and tables can sometimes improve efficiency.

Leave a Comment

Your email address will not be published. Required fields are marked *

Optimization WordPress Plugins & Solutions by W3 EDGE