MySQL Parameter Optimization-Table Cache

The Table Cache

--------------------------------------------------------------------------------
Table cache objects: tables

Each cached object contains the parsing result of the related table. frm file, and some other data is added.

To be accurate, the content of other data in an object depends on the storage engine of the table. For example:

MyISAM is the table data and index file descriptor.

Merge may be multiple file descriptors, because Merge tables can have many underlying tables.

--------------------------------------------------------------------------------

Table cache feature: Resource Reuse

For example:

When a query request accesses a MyISAM table, MySQL may be able to obtain the file descriptor from the cached object. Although this can avoid the overhead of opening a file descriptor, this overhead is not large. Opening and disabling file descriptors is fast in local storage, and the server can easily perform 1 million operations per second. For MyISAM tables, the real benefit of table caching is that the server can avoid modifying the MyISAM file header to mark the table as "in use ".

--------------------------------------------------------------------------------

Table cache --> InnoDB

The Design of table cache is the product of Incomplete separation between the server and the storage engine. It is a historical issue. Table cache is much less important to InnoDB, because InnoDB does not rely on it to do so many things (for example, InnoDB has its own table Cache version to hold file descriptors ). Even so, InnoDB can also benefit from cached parsed. frm files.

--------------------------------------------------------------------------------

Evolution of Table Cache Parameters

In MySQL 5.1 and later versions, table cache is separated into two parts:

One is to open the table cache ---> table_open_cache

One is the table-defined cache ---> table_definition_cache

The result is that the table definition (the result of parsing the. frm file) is separated from other resources, such as the table descriptor. The opened table is still used by every thread and table, but the table definition is global and can be effectively shared by all connections.

You can set table_definition_cache to be high enough to cache all table definitions. Unless there are tens of thousands of tables, this is probably the simplest method.

--------------------------------------------------------------------------------

Parameter introduction:

Variable Name Variable Scope Dynamic Variable

Table_open_cache Global Yes

Table_definition_cache Global Yes

The default value of table_definition_cache is 400. The value range is 400-524288.

--------------------------------------------------------------------------------

Determine whether the parameter needs to be adjusted

If the Opened_tables status variable is large or growing, it may be because the table cache is not large enough. You can manually add the table_cache system variable (or table_open_cache in MySQL 5.1 ). However, when creating and deleting temporary tables, pay attention to the growth of this counter. If you often need to create and delete temporary tables, this counter will continue to grow. Www.bkjia.com

Mysql> show status like 'opened _ files ';

+ --------------- + ------------ +

| Variable_name | Value |

+ --------------- + ------------ +

| Opened_files | 1170770489 |

+ --------------- + ------------ +

 

--------------------------------------------------------------------------------

Disadvantages of Table Cache setting too large

Disadvantages of setting the table cache too large: when many MyISAM tables exist on the server, shutdown may take a long time, because the index block must be refreshed before shutdown, all tables must be marked as not opened. For the same reason, the flush tables with read lock operation may take a long time.

--------------------------------------------------------------------------------

Table Cache summary:
The table cache actually uses a small amount of memory, but it can effectively save resources. Although opening a new table does not cost much compared to other MySQL operations, their overhead is accumulated. Therefore, cache tables can sometimes improve efficiency.

--------------------------------------------------------------------------------

Personal feeling:

For most online databases, the MyISAM storage engine should increase the value of table_open_cache accordingly.

For table_definition_cache, check the table capacity of the database.

 

I would like to explain that my personal feeling is purely my own point of view, and my understanding is for your reference only.