MySQL thread cache thread_cache_size parameter optimization

Thread_cahe_size thread pool, threading cache. Used to cache idle threads so that they are not destroyed, and if idle threads in the thread cache need to reestablish a new connection, the cache in the thread pool is prioritized and the connection request can be answered very quickly.

For each connection, a thread is required to match it.

MariaDB [(None)]> show global status like '%thread% ';

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

| variable_name | Value |

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

| Delayed_insert_threads | 0 |

| Innodb_master_thread_1_second_loops | 4338823 |

| Innodb_master_thread_10_second_loops | 432031 |

| Innodb_master_thread_background_loops | 18974 |

| Innodb_master_thread_main_flush_loops | 18974 |

| Innodb_master_thread_sleeps | 4338819 |

| Performance_schema_thread_classes_lost | 0 |

| Performance_schema_thread_instances_lost | 0 |

| Slow_launch_threads | 0 |

| Threadpool_idle_threads | 0 |

| Threadpool_threads | 0 |

| threads_cached | 0 | Represents the current idle thread in the thread cache at this moment

| threads_connected | 11 | Represents the number of connections currently established

| threads_created | 11032160 | The number of threads created since the last service startup

| threads_running | 2 | Number of currently active (non-sleep) threads

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

Thread_cache_size is best set to be the same as threads_connected. However, Thread_cache_size is rarely set to be larger than 200.

Configuration:

Mysql>set Global thread_cache_size=30 Immediate effect

Or

Vim/etc/my.cnf

Thread_concurrency=30

Restart MySQL.

MySQL thread cache thread_cache_size parameter optimization