MySQL exceeds maximum number of connections resolution

encountered MySQL exceeded the maximum number of connections, I believe many people first reaction is to check the MySQL process, see if there is no slow query, of course, this practice is completely correct!
but most of the time the real problem isn't here.
today you have the same problem, blindly view the MySQL process and slow query log, no results.
later the eldest brother raised a bit, check the nginx log, found that there are one or two access execution time is longer, and then use the top command to see the server load, surprised, incredibly high!
finally found that there is a Web splitter host hangs, resulting in several additional web host load increased, resulting in php-fpm execution efficiency.
So what does this have to do with MySQL? The reason is simple, because PHP execution time is too long, the MySQL connection is not released, it will lead to excessive number of connections.
The final conclusion: in fact, a lot of times, the root cause of a problem is not so direct presentation, need to track themselves.
The eldest brother has a very practical words: Check the log (MySQL, PHP, Nginx, etc.) before encountering a problem

===============================================================

ways to troubleshoot too many connections

When a user receives a link number alarm, it means that the number of connections is about to reach the upper limit of the instance. if the number of connections to the instance exceeds the number of connections specified by the instance, a new connection cannot be created, which can affect the user's business ;

Mysql connection is usually a request to occupy a connection, if the request (Update,insert,delete,select) has not been completed for a long time, it will cause the accumulation of connections, quickly consumed the database of the number of connections, This time the technical support staff will log in to the database to sort, to see exactly those SQL occupies the connection;

Troubleshooting steps:

1. View the instance configuration:

You can log in to the RDS console "details and configuration" to view the number of instances rated links, we assume a maximum of 1500 links

2. View the current number of connections:

1) You can log in to the RDS console "performance monitoring" to see the current number of links in your instance.

2) or Login database query the current connection, you can use the synchronization account or user's business account login database, execute show processlist;

[Email protected] ~]# mysql-uroot-h127.0.0.1-p3020-e "Show Processlist" |wc-l

1262

You can see that the instance already has 1262 connections

3. Troubleshoot what actions are taking up these connections:

[Email protected] ~]# myql-uroot-h127.0.0.1-p3018-e "show full Processlist" >/tmp/1.log

[Email protected] # More/tmp/1.log

615083 my_db 223.4.49.212:54115 my_db Query sending data

INSERT into Tmp_orders_modify (OID, Tid, seller_id, status , Gmt_create, gmt_modified)

SELECT oid, Tid, seller_id, status gmt_create, gmt_modified

From Sys_info.orders WHERE

Gmt_modified < Name_const (' v_last ', _binary ' 2012-12-24 10:33:00 ' COLLATE ' binary ') an

D gmt_modified >= name_const (' V_curr ', _binary ' 2012-12-24 10:32:00 ' COLLATE ' binary ')

621564 my_db 223.4.49.212:46596 my_db Query 3890 sorting result

INSERT INTO Tmp_trades (SID, D, H, TC, TM, TP, IC, NEW_TP, OLD_TP)

Select a.seller_id as SID,

..............

From Orders_1 as a where seller_id =1 and is_detail = ' 1 '

and created < Date_format (' 2012-12-24 10:35:00 ', '%y-%m-%d%h:00:00 ')

and Gmt_create < Date_format (' 2012-12-24 10:40:00 ', '%y-%m-%d%h:%i:00 ')

and Gmt_create >= date_format (' 2012-12-24 10:35:00 ', '%y-%m-%d%h:%i:00 ')

Group by D, H

ORDER BY D

... the other SQL is omitted from this point in the same place.

4, analysis of the reasons for connection occupancy:

You can see the database has a long time no completion of SQL, has been occupied by the connection is not released, and the application of the request has been constantly pouring into the database, this time the database connection is used up quickly, so this time need to troubleshoot why these SQL for a long time did not finish, Whether the index is not created well or that SQL execution is time-consuming.

First sql:

INSERT into Tmp_orders_modify (OID, Tid, seller_id, status , Gmt_create, gmt_modified)

SELECT oid, Tid, seller_id, status gmt_create, gmt_modified

From Sys_info.orders WHERE

Gmt_modified < Name_const (' v_last ', _binary ' 2012-12-24 10:33:00 ' COLLATE ' binary ') an

D gmt_modified >= name_const (' V_curr ', _binary ' 2012-12-24 10:32:00 ' COLLATE ' binary ')

Is that the user pulls the order from the Sys_info database to the one in his own business library, but does not have a gmt_modified index on the Orders table, resulting in a full table scan; (More detailed troubleshooting methods can be consulted: why my RDS is slow);

Second sql:

See this SQL is in the sorting sort, why cause SQL long time sorting, usually the result set is too large to sort, resulting in sorting can not be completed in memory, the need to sort on disk, resulting in performance degradation, the solution is to reduce the ordered result set, The usual means is to use the order of the index, eliminate the sorting, or set up the appropriate index to reduce the result set; we can see that the second SQL sort field is very complex, but we can see that the query time range is very short, only 5 minutes of time interval, this time can be in Gmt_ Create an index to filter out most of the records:

Alter Tale order_1 Add index ind_order_gmt_create (gmt_create);

(The user has a table of orders, about 50 tables need to add the index of the Gmt_create field);

5, after the optimization of the above two steps, the user instance returns to normal: IO situation and connection situation, you can log in the RDS console again to view the number of connections.

MySQL exceeds maximum number of connections resolution