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

I received a notice from R & D in the morning that there were a large number of slow queries in the database, and sent the call time, the query time is basically more than 0.5s. Tell them to send the sql, open the database connection, execute the sql, and find that the sql execution is very fast, the execution time is less than 0.1s, and let them troubleshoot the network problems.

After a period of time, I was informed that there was nothing wrong with the network. I wondered if it was caused by a dns problem, and I used both local and remote connections to perform sql.

Time mysql-S mysql.sock-uxxx-p'xxx'-e "select 1"

Time mysql-h xx.xx.xx.xx-P xxxx-u xxx-p'xxx'-e "select 1"

It is found that the query is about the same, most of the query time is about 0.5s, a small part of the query time is less than 0.1s. Troubleshoot the dns problem. The colleague confirmed that there was a problem with the connection to the database.

By comparison, it is found that this is not the case in other instances. There is another instance on this physical machine. The access time is less than 0.1s, and only a few of them reach 0.5s. It is suspected that it is due to configuration parameters. Comparing the parameter file show global status;show global variables; of the two instances, no obvious exception is found. Temporarily deadlocked.

Because it is the connection problem of the database, the leader requires that it must be solved, and we think again, because the connection to the database is very fast to execute sql, but the execution of sql as above is very slow, which is suspected to be the problem of the thread pool of the database. Find information about the thread pool.

The client connects to the server thread and maps the two, and the corresponding relationship is not fixed. The server thread is responsible for executing the sql sent from the client.

The basic unit is a thread group, each containing one listening thread and (possibly multiple) execution threads, the former responsible for receiving sql from the client, and the latter for execution

When a new sql is received, if there is no other sql at this time, it will be executed by the listening thread immediately. During this period, the group is not listening temporarily. If the sql execution time is too long, the thread pool assigns a new listening thread to it; otherwise, it is queued to wait.

Mysql's thread pool (thread pool) has three modes by default: one-thread-per-connection,pool-of-threads, and no-thread

Thread_pool_size is the number of thread groups, default is the number of cpu cores, and Threads_running is the number of running threads. Check the thread of the current library:

Threads_running obviously exceeds the Thread_pool_ size value, because thread pool was originally designed to keep a certain number of threads in the "ACTIVE" state, which is achieved by controlling the number of thread group and the number of thread in the "ACTIVE" state within the thread group. The way to control the number of ACTIVE states within the thread group is to maximize the number of threads in the ACTIVE state of 1. It is obvious that there is a thread in the WAITING state in the current thread group. If a new thread is enabled and the thread is in the ACTIVE state, when the thread just changed from WAITING to ACTIVE state, there will be two threads in the "ACTIVE" state, which seems to be contrary to the original goal, but obviously can not keep the subsequent ready socket waiting, what should we do?

So a trade-off is needed at this point, which provides a way to enable a counter for a thread in the ACTIVE or WAITING state, mark the thread as stalled when the counter is exceeded, and then thread group creates a new thread or wakes up the thread of the sleep to process the new sokcet, which would be a good trade-off. The timeout is determined by the parameter thread_pool_stall_limit. The default is 500ms.

Finally found the reason why the query time is often at 0.5s! Exciting news. It's easy to find the reason. Mysql5.7 defaults to 6ms, production is 5.6, and defaults to 500ms. We can change it to 10ms, which forces the old sql to be marked as stalled faster, and then create a new thread to process the new connection.

Other information has also been checked on the Internet, it is said that the thread_pool_stall_limit parameter value of Ali's database is also 10ms, we tested again, the connection query is less than 0.1s, there is no ups and downs.

The next day R & D feedback, the previous half-hour slow query alarm disappeared. The weird slow inquiry is finally over.

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.