Details of MySQL performance optimization configuration parameters thread_cache and table_cache

1. THREAD_CACHE

MySQL inside in order to improve the performance of the client request to create the connection process, provide a connection pool is Thread_Cache pool, put idle connection thread in the pool, not immediate destruction. The benefits of this is that when a new request, mysql will not immediately go to create a connection threads, but first go to find free connection thread Thread_Cache, if there are used directly, does not exist to create a new connection thread.

About Thread_Cache There are several important parameters in MySQL, briefly described as follows:

thread_cache_size

The maximum number of connection threads that can be stored in Thread_Cache. Thread_Cache is very effective in applications with short connections, because database connections and creation are very frequent in the application. In the long connection, although the improvement is not as obvious as in the short connection, the benefits are obvious. However, the bigger is not the better, but the waste of resources.

1G  - > 8

2G  - > 16

3G  - > 32

>3G  - > 64

If there are many short connections, they can be enlarged appropriately.

thread_stack

The amount of memory mysql allocates to each connection when it is created. This value of 1 is generally assumed to apply by default to most scenarios, and should not be touched unless necessary.

thread_handing

Use Thread_Cache to handle connections,5.1.19 added new features. There are two values to choose [no-threads | one-thread-per-connection] no-threads server using 1 thread, one-thread-per-connection The server USES 1 thread for each client request. As mentioned in the original manual, ES54en-ES55en was tried in Linux.

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)

From the above three commands, you can see that the server's thread_cache pool can hold up to 32 connection threads, with one thread per client ball. Allocate 192k memory space for each connected thread.

The server has a total of 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, the number of active state is 5, a total of 8689 connections have been created.

Thread_Cache_Hit=(Connections-Thread_created)/Connections*100%

The current server hit rate of Thread_cache is about 95.6%, which I am quite satisfied with, but I can see that thread_cache_size is a bit redundant. It would be more reasonable to change it to 16 or 8.

2. TABLE_CACHE(5.1.3 and later TABLE_OPEN_CACHE)

Since MySQL is a multi-threaded mechanism, to improve performance, each thread opens its own file description of the table it needs, rather than sharing what is already open.

In the myisam table engine, the descriptor for the data file (descriptor) is not Shared, but the descriptor for the index file is Shared by all threads.Innodb is related to the type of table space used, if the table space is Shared, there will be only one data file, and of course, the data file descriptor will be used less than the independent table space.

Personally feel a bit like php inside fopen open 1 connection, after the operation of the data, not immediately closed, but the cache, waiting for the next connection of this file request does not have to reopen the file, I do not know whether it is right, ha.

There is a paragraph in the manual about opening the table:

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 with HANDLER tbl_name OPEN statement, you will allocate a table specifically for that thread. The table is not Shared by other threads and is only closed when 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 not satisfied).

The recommended size in the mysql manual is table_cache=max_connections*n

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

There's a lot of doubt about this,table_cache is enough, check Opened_tables, if it's large or growing fast then you need to consider increasing table_cache.

Unused tables are closed and removed from the table cache under the following conditions:

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 thread.

When a table refresh operation occurs. Occurs when the FLUSH TABLES statement or the mysqladmin ES143en-ES144en or mysqladmin refresh commands are executed.

When the table cache is full, the server USES the following procedure to find 1 cache entry to use:

Currently unused tables are released in the order of least recent use.

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

If the cache is in a temporary expanded state and a table changes from being in use to not being in use, it is closed and released from the cache.

Several status values for table_cache:

table_cache: Number of tables opened by all threads. Increasing this value increases the number of file descriptors required by mysqld. The default value is 64.

open_tables: Number of tables currently open.

3. opened_tables: Number of cache misses, table_cache may be too small if opened_tables is large.

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.