Shulou(Shulou.com)06/01 Report--

Database version: percona-mysql 5.6.16

For a long time, programs will connect to the database and respond slowly, normally between a few to dozens of milliseconds, but occasionally hundreds of milliseconds

At first, because the developer reset and adjusted the program connection pool, it was always suspected that it was the connection pool problem, but the problem remained.

Because the version used is percona-mysql 5.6.16 and database connection pooling is used.

Thread Pool is divided into several group according to the parameter thread_pool_size, each group maintains the connections initiated by client, when the MySQL establishes the connection

MySQL takes the module to thread_pool_size according to the thread id of connection, and assigns the sql statement initiated by connection to the corresponding group. Maximum worker per group

The quantity is thread_pool_oversubscribe+1. If the maximum number of worker is still insufficient to handle the reply request, the connection waits on this group, resulting in an increase in the rt of the sql statement

So increase the thread_pool_oversubscribe parameter, but the problem remains.

Check the thread_cache_size. The memory size of the server is 64GB memory. Threadbare cachesize128

Every time a connection is established, a thread is needed to match it. This parameter is used to cache idle threads so that they are not destroyed. If there are free threads in the thread cache, if a new connection is established, MYSQL will quickly respond to connection requests.

Show status like'% thread%'

Variable_name Value

Threads_cached 0

Threads_connected 219

Threads_created 655068

Threads_running 48

Use show status to view the current mysql connection:

SHOW STATUS WHERE Variable_name LIKE'% Thread%'

Threads_cached: represents how many threads are free in the thread cache at the moment.

Threads_connected: represents the number of connections currently established, because a connection requires one thread, so it can also be regarded as the number of threads currently in use.

Threads_created: represents the number of threads that have been created since the most recent service startup.

Threads_running: represents the number of currently active (non-sleeping) threads. Does not represent the number of threads in use, sometimes the connection has been established, but the connection is in the sleep state, here the corresponding thread is also in the sleep state.

It is best to set thread_cache_size to be the same as threads_connected.

Show variables like'% thread%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_purge_threads | 1 | |

| | innodb_read_io_threads | 4 |

| | innodb_thread_concurrency | 0 | |

| | innodb_thread_sleep_delay | 10000 | |

| | innodb_write_io_threads | 4 |

| | max_delayed_threads | 20 |

| | max_insert_delayed_threads | 20 |

| | myisam_repair_threads | 1 | |

| | performance_schema_max_thread_classes | 50 | |

| | performance_schema_max_thread_instances |-1 |

| | pseudo_thread_id | 8735851 | |

| | thread_cache_size | 128 | |

| | thread_concurrency | 24 |

| | thread_handling | pool-of-threads |

| | thread_pool_high_prio_mode | transactions |

| | thread_pool_high_prio_tickets | 4294967295 | |

| | thread_pool_idle_timeout | 60 | |

| | thread_pool_max_threads | 100000 | |

| | thread_pool_oversubscribe | 40 | |

| | thread_pool_size | 12 | |

| | thread_pool_stall_limit | 500 | |

| | thread_stack | 262144 | |

| | thread_statistics | OFF |

+-+ +

It can be refreshed continuously. If Threads_cached = 0 and Threads_created continues to increase, then the current thread_cache_size value setting should be changed to about Threads_connected value.

Considering the physical memory 1G-> 8x 2G-> 16; 3G-> 32; > 3G-> 64, change the thread_cache_size to 512.

Note and translation:

Threads_cached: represents how many threads are free in the thread cache at the moment.

Threads_connected: represents the number of connections currently established, because a connection requires one thread, so it can also be regarded as the number of threads currently in use.

Threads_created: represents the number of threads that have been created since the most recent service startup.

Threads_running: represents the number of currently active (non-sleeping) threads. Does not represent the number of threads in use, sometimes the connection has been established, but the connection is in the sleep state, here the corresponding thread is also in the sleep state.

After modifying the thread_cache_size to 512, the response time of the retest program connecting to the database is extremely fast, and there is no longer the slow response of the program connecting to the database!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.