Thread_cache and table_cache for MySQL performance optimization configuration parameters

I. THREAD_CACHE

To improve the performance of the connection process created by client requests, MySQL provides a connection pool, namely the Thread_Cache pool, which stores idle connection threads in the connection pool instead of destroying them immediately. the advantage is that when there is another new request, mysql will not immediately create a connection thread, but will first go to Thread_Cache to find idle connection threads. If so, it will be used directly, A new connection thread is created if it does not exist.

Thread_Cache has several important parameters in MySQL, which are described as follows:

Thread_cache_size

The maximum number of connection threads in Thread_Cache. thread_Cache is very effective in applications with transient connections, because the connection and creation of databases in applications are very frequent. If Thread_Cache is not used, the resources consumed are very considerable! Although the improvement brought by persistent connections is not as obvious as short connections, the benefits are obvious. but it is not as big as the better, but a waste of resources. This is generally considered to be related to the physical memory, as shown below:
Copy codeThe Code is as follows:
1G-> 8
2G-> 16
3G-> 32
> 3G-> 64

You can increase the number of transient connections.

Thread_stack

The memory allocated to mysql when a connection is created. This value is generally considered to be applicable to most scenarios by default, unless necessary.

Thread_handing

Use Thread_Cache to process connections. New features added in 5.1.19. there are two optional values: [no-threads | one-thread-per-connection]. You should have guessed points. Haha, the no-threads server uses one thread, the one-thread-per-connection server uses one thread for each client request. as mentioned in the original manual, no-threads has been downgraded for trial use in Linux.
Copy codeThe Code is 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)
The preceding three commands show that the thread_cache pool of the server can store up to 32 connection threads, use one thread for each client ball, and allocate KB of memory space for each connection thread.

Server has a total of 199156 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, A total of 8689 connections were created. it is clear that short connections are the main reasons. the thread_cache hit rate can be calculated. The formula is:

Copy codeThe Code is as follows:
Thread_Cache_Hit = (Connections-Thread_created)/Connections * 100%

I am satisfied with the result that the Thread_cache hit rate of the current server is about 95.6%, but it can be seen that thread_cache_size is a little redundant and it is more reasonable to change it to 16 or 8.

Ii. TABLE_CACHE(5.1.3 and later versions, also known as TABLE_OPEN_CACHE)

Because MySQL is a multi-threaded mechanism, in order to improve performance, each thread opens the file descriptor of the desired table independently, instead of sharing opened files. the processing methods for different storage engines are certainly different.

In the myisam Table engine, the data file descriptor (descriptor) is not shared, but the index file descriptor is shared by all threads. innodb is related to the use of table space types. If it is a shared table space, it is actually a data file. Of course, the data file descriptor occupied will be less than the independent table space.

I personally feel a bit like fopen in php opens a connection. After the data is operated, it is not immediately closed, but cached. When the next request to connect the file does not have to re-open the file, I don't know, right.

The manual provides a description of opening a table:
Copy codeThe Code is as follows:
A MyISAM table is opened for each concurrent access. this means the table needs to be 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 an entry in the table cache. the first open of any MyISAM table takes two file descriptors: one for the data file and one for the index file. each additional use of 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 using the HANDLER tbl_name OPEN statement, a special table will be allocated to the thread. This table is not shared by other threads. It is closed only when the thread calls HANDLER tbl_name CLOSE or the thread ends. After the table is closed, it is pulled back to the table cache (if the cache is not satisfied ).

The recommended mysql manual size is table_cache = max_connections * n.

N indicates the maximum number of tables in the query statement. Additional file descriptors must be reserved for temporary tables and files.

This data has been raised a lot of questions, and table_cache is enough. Check the Opened_tables value. If this value is large or increases rapidly, you have to consider increasing the value of table_cache.

Under the following conditions, unused tables will be disabled and removed from the table cache:

When the cache is full and a thread tries to open a table 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 thread.

When the table is refreshed. This happens when you execute the flush tables statement, mysqladmin flush-tables, or mysqladmin refresh command.

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

Unused tables are released in the order of least recent use.

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

If the cache is in a temporary extended state and a table changes from in use to out of use, it is disabled and released from the cache.

Several Status values about table_cache:

1. table_cache: 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: Number of opened tables.

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 number of cached. frm files. This variable was added in MySQL 5.1.3.

5. Opened_table_definitions: The number of. frm files that have been cached. This variable was added in MySQL 5.1.24.