Shulou(Shulou.com)05/31 Report--

MYSQL CPU part of the single core full will affect the establishment of database connection efficiency, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Problem description:

Cpu is instantly full, the foreground application reports a too many connection error, the mysql ERROR log reports [Warning] Too many connections, and the operating system log reports: kernel: TCP: time wait bucket table overflow.

The resources of the host are as follows:

The overall resources of 1.CPU are not high, but the utilization rate of single core is 100%.

2.CPU system interruption and context switching indicators rose suddenly at the same time.

3. The disk IO where the database is located is almost full.

Database situation:

1. The monitoring and acquisition program can not connect to the database and can not collect the database index, indicating that the database connection suddenly surged in a short period of time, and there is no gradual upward trend.

two。 There is no slow log during the failure.

3. Grab the BINLOG, and no new transaction is generated after the fault starts.

4. The daily number of connections is basically less than 500. in case of failure, it reaches the threshold of 3000, and the number of connections reaches the threshold is a result. The problem is to find out why some connections are not released.

5. When logging on to the database, it is found that a large number of sessions are in unauthenticated user state.

Business situation:

1. It is the peak of business, but there is no obvious sudden increase in business.

two。 Jdbc connection pool is used in business applications. If the connection is abnormal, the connection will be retried constantly.

Train of thought:

During the failure, a colleague performed 16 parallel pigz operations, which is also due to the abnormal resources of the above host cpu and IO. It is suspected that this exception affects the connection of the database. It is mainly the abnormal request of CPU that affects the influx of database connection requests.

My judgment is based on:

1. The result is that the number of connections is full in an instant, because most of the connections in front of HANG live.

two。 Then the connection may be at that stage of hang:

1) there is no slow query, which means it is not a slow sql problem

2) binlog does not have a large number of very long transactions, and even no transactions are generated during the failure. Explain the problem of non-long transaction

3) the connection to sleep has not been released? First of all, the program has not made any changes, the connection can not be disconnected may be a local exception to the database, but if so, I think the connection will not be able to connect properly at the beginning. So the connection should be disconnected normally.

4) so I think connecting to hang is in the connection stage. Coupled with the fact that a large number of sessions will be in the unauthenticated user state before, this stage should be the stage when the client establishes a tcp connection with the database, and then authenticates and allocates threads. If there is a problem with the database obtaining CPU resources, does it mean that the connection has been slowed down, or even HANG resides?

I did an experiment and found that when CPU usage persists at 100%, a large number of connections are initiated, and the demand for CPU during database connections is relatively large.

You can also see more unauthenticated user sessions at this time!

It is still not possible to determine the root cause:

Because CPU is only partially full, you need to further understand how LINUX CPU scheduling works.

Whether system interrupts and context switching can be explained by a large number of system call request exceptions.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.