Thread_cache and table_cache for MySQL Performance Optimization configuration parameters _ MySQL

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:
The code is as follows:
1 GB-> 8
2 GB-> 16
3G-> 32
> 3 GB-> 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.
The 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 % ';
+---+ ――