Mysql performance Optimization configuration parameters Thread_cache and Table_cache detailed _mysql

First, Thread_cache

MySQL inside in order to improve the performance of the client request creation connection process, a connection pool is provided, which is the Thread_cache pool, where the idle connection thread is placed in the connection pool instead of being destroyed immediately. The advantage is that MySQL does not immediately create a connection when there is a new request Thread, but first go to the Thread_cache to find the idle connection thread, if there is a direct use, does not exist to create a new connection thread.

There are several important parameters about Thread_cache in MySQL, which are briefly described below:

Thread_cache_size

The maximum number of connection threads that are stored in the Thread_cache. In the application of short connection Thread_cache is very obvious, because in the application of database connection and creation is very frequent, if not using thread_cache so the resources consumed is very considerable! Although the improvements made in long connections are not as obvious as those for short connections, the benefits are obvious. But it's not the bigger, the better. The determination of this is generally considered to be related to physical memory, as follows:

Copy Code code as follows:

1g-> 8
2g-> 16
3g-> 32
>3G-> 64

If there are many short connections can be properly increased.

Thread_stack

When each connection is created, MySQL assigns its memory to it. This value is generally considered to be applied to most scenarios by default, unless it is necessary not to move it.

Thread_handing

Using Thread_cache to handle the connection, 5.1.19 added new features. There are two values to choose from [no-threads|one-thread-per-connection] see the literal meaning everyone should guess eight or nine points, hehe, no-threads The server uses a thread, and the One-thread-per-connection server uses one thread for each client request. The original manual mentions that No-threads was on Linux for trial.

Copy Code code as follows:

Mysql> Show variables like ' thread% ';
+ ——————-+ ————————— +
| variable_name | Value |
+ ——————-+ ————————— +
| Thread_cache_size | 32 |
| thread_handling | one-thread-per-connection |
| Thread_stack | 196608 |
+ ——————-+ ————————— +
3 rows in Set (0.01 sec)

Mysql> Show status like '%connections% ';
+ ———————-+--–+
| variable_name | Value |
+ ———————-+--–+
| Connections | 199156 |
| max_used_connections | 31 |
+ ———————-+--–+
2 rows in Set (0.00 sec)

Mysql> Show status like '%thread% ';
+ ———————— +--–+
| variable_name | Value |
+ ———————— +--–+
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| threads_cached | 3 |
| threads_connected | 6 |
| threads_created | 8689 |
| threads_running | 5 |
+ ———————— +--–+
6 rows in Set (0.00 sec)

With the above 3 commands, you can see that there are up to 32 connection threads in the Thread_cache pool of the server and one thread for each client ball. Allocates 192k of memory space for each attached thread.

The server has 199,156 connections, the maximum number of concurrent connections is 31, the current number of connections in the Thread_cashe pool is 3, the number of connections is 6, there are 5 active, and a total of 8,689 connections are created. It is obvious that this is mainly a short connection. Can calculate the Thread_ Cache hit rate, the formula is:

Copy Code code as follows:

thread_cache_hit= (connections-thread_created)/connections*100%

The current server has a thread_cache hit rate of about 95.6%. I am quite satisfied with the result. But it can be seen that thread_cache_size a bit redundant to 16 or 8 more reasonable.

Second, Table_cache (5.1.3 and later version also known as Table_open_cache)

Because MySQL is a multithreaded mechanism, to improve performance, each thread is open by itself to the file descriptor of the table it needs, rather than by sharing it. The methods for handling different storage engines are of course not the same.

In the MyISAM table engine, the data file descriptor (descriptor) is not shared, but the index file's descriptor is shared by all threads. InnoDB and the use of table space types, if it is shared tablespace so the actual data file, of course, the data file descriptor will be less than the independent table space.

Personal feeling a bit like PHP inside the fopen open a connection, after the operation of data, not immediately shut down, but the cache, waiting for the next connection to the file request will not have to reopen the file, do not know how to understand the right, ha.

There is a section on the manual that describes when you open a table:

Copy Code code as follows:

A MyISAM table is opened to each concurrent access. This means the table needs to is opened twice if two threads access the same table or if a thread accesses the table twice In the same query (for example, by joining the table to itself). Each concurrent open requires a entry in the table cache. The "a" of any MyISAM table takes two file Descriptors:one for the data file and one for the index file. Each additional with the table takes only one file, descriptor for the data file. The index file descriptor is shared among all threads.

If you are opening a table with the HANDLER tbl_name Open statement, a table will be allocated specifically for that thread. The table is not shared by other threads and is closed only if the thread calls handler Tbl_name close or when the thread terminates. After the table is closed, it is pulled back into the table cache (if the cache is dissatisfied).

The recommended size for the MySQL manual is: table_cache=max_connections*n

n represents the maximum number of tables in a query, and additional file descriptors need to be reserved for temporary tables and files.

This data has been questioned a lot, table_cache enough to check the Opened_tables value, if this value is large, or grow quickly then you have to consider to increase the table_cache.

In the following conditions, unused tables are closed and removed from the table cache:

When the cache is full and a thread attempts to open a table that is not in the cache.

When the cache contains more than Table_cache entries, and the tables in the cache are no longer used by any threads.

When a table refresh operation occurs. Occurs when the Flush tables statement is executed or when the mysqladmin flush-tables or mysqladmin refresh command is executed.

When a table is full, the server uses the following procedure to find a cache entry to use:

Tables that are not currently in use are freed with the least recent order of use.

If the cache is full and no tables can be freed, but a new table needs to be opened, the cache must be temporarily expanded.

If the cache is in a temporary extended state and a table becomes inactive from being used, it is closed and released from the cache.

Several status values about Table_cache:

1. Table_cache: The number of tables opened by all threads. Increasing this value can increase the number of file descriptors required by mysqld. The default value is 64.

2. Open_tables: The number of tables currently open.

3. Opened_tables:number of table cache misses, if Opened_tables is large, the Table_cache value may be too small.

4 open_table_definitions:the of cached. frm files. This variable is added in MySQL 5.1.3.

5 opened_table_definitions:the Number of frm files that have been cached. This variable is added in MySQL 5.1.24.