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

A preface

DBA operation and maintenance is the process of filling the hole, others dig the hole, fill it by themselves; dig their own hole and fill it by themselves, even if they say too much, they will cry. All right, let's get to the point. I was busy doing the maintenance and renovation of the IDC interactive machine and upgrading the database server all night in the early hours of this morning. I needed to restart the server. After rebooting the OS and redeploying the supporting facilities around the database, there is nothing wrong with the system. The error log of the database has been appearing since 8: 00 in the morning. Don't ask why you are dealing with it now. (I didn't arrive at the company until after 11:00.)

2017-01-10 20:47:45 21194 [Warning] Too many connections secondary survey

Although the above alarm information is not serious, error log has been writing warnning information, which always needs to be solved. The usual situation in which a "Too many connections" error is encountered is that the current number of database connections exceeds the size set by the system max_connections,max_user_connections. From this aspect, the specific train of thought of investigation. Usually check

1 whether the database can be logged in, whether the login will report an error, but the login db is not abnormal.

2 check the size configuration of max_connections,max_user_connections

3 check the connection distribution of the database system, such as the following figure is obviously normal, the system configuration 4000 connections, a single user more than 300 connections, far less than the value set by the system.

SELECT substring_index (host,':', 1) AS host_name,state,count (*) FROM information_schema.processlist GROUP BY state,host_name

The analysis seems to have reached an impasse here. When the current number of connections < max_connections and the current number of connections < max_user_connections, there is a "[Warning] Too many connections", so I asked other DBA peers to expand their ideas, and they also said that there were differences and no other ideas.

The colleague who made the change in the early morning reported the current problems, and his prompt woke up the dreamer-We enabled sql-killer (similar to the pt-killer real-time monitoring system, where the sql that takes more than 1.02 seconds to execute will kill down) is to connect to the database through the management port.

What is the management port-use this parameter extra_port to specify a port number when MySQL starts (do not conflict with the normal database service port), and Percona Server listens for requests from that port. Enabling this parameter can solve the problem that when using the thread_pool feature, DBA cannot connect to DB through the normal port because all connection pooling worker is busy with slow querey or locked, so that DBA can maintain the database properly. Obviously, the original intention of using the management port is good, and it is also to avoid slow query blocking the database. Sql-killer can connect to db from the management port, and then kill produces a slow query session.

So we stop the sql-killer, and sure enough, the error message also stops. Check from the management port and found that the extra_max_connections changed to 1 after restart, resulting in the sql-killer request unable to connect, report "Too many connections". After knowing the reason, you can dynamically modify the parameters in the instance, modify the parameters in the configuration file, and then restart sql-killer, so that the problem is solved.

Three summaries

It took about 1.5 hours to solve this exception, but the reason is that I don't know enough about my system, and the database configuration file is not standardized, which leads to a series of follow-up problems. If there are no colleagues to remind me that there are tools to connect to the database through the management port, it will take me longer to troubleshoot the problem. In the future, I should sort out the standardized configuration of the database and unify it to all databases.

Conventional thinking solves conventional problems.

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.