edit-icon download-icon

How to troubleshoot excessive connections

Last Updated: Jul 13, 2016
When users receive the alarm of connections, it means the connections are about to reach the upper limit of the instance. If the instance connections surpass the specified limit, users will not be able to create new connections and the business will be affected;
One MySQL connection is usually used by one request. If the request (update, insert, delete, select) is not finished for long, the following up connections will pile up and the connection quota will be quickly used up. In such cases, the technical support personnel need to log into the database, sort the requests and check which queries are occupying the quota;
Troubleshooting steps:
1. Check instance configurations:
Log into ApsaraDB for RDS console and view the instance connection quota in "Details and Configurations". We assume a maximum connection count of 1,500 is supported.
2. Check current connections:
1) Log into ApsaraDB for RDS console - "Performance Monitoring" to view the current connections of the instance.
2) Or log into the database to query the current connection. You can use the sync account or the user's business account to log into the database and run "show processlist";
[root@r41d05036.xy2.aliyun.com ~]# mysql -uroot -h127.0.0.1 -P3020 -e "show processlist"|wc -l
1262
We can see the instance has had 1,262 connections
3. Check which processes occupy the connections:
[root@r41d05036.xy2.aliyun.com ~]# myql -uroot -h127.0.0.1 -P3018 -e "show full processlist">/tmp/1.log
root@r14d11038.dg.aliyun.com # more /tmp/1.log
615083 my_db 223.4.49.212:54115 my_db Query 100 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
………………. Other queries are skipped here
4. Analyze the cause:
We can see there are unfinished queries after a long processing time in the database. They occupy and won't release the connections, while new application requests keep streaming in. The connection quota of the database was soon used up. At this point, we need to check why the queries are taking so long to process, whether it is because of the poor indexing, or the severe lag in query execution.
The first query:
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')
It is used by the user to pull order requests from sys_info database to their own business library. But due to the absence of gmt_modified index in the orders table, a full table scan is performed. (For more details about troubleshooting, refer to: Why does my ApsaraDB for RDS get slower?)
The second query:
We can see this query is sorting. But why the sorting take so long? Usually it is because the sorting result set is too big to be processed in the memory, and it has to be processed in the disk, leading to performance drop. The solution is to reduce the sorting result set. A common means is to clear the sorting by use of the ordered index, or establish a more appropriate index to reduce the result set. As we can see, the sorting fields of the second query are very complicated, but with a quite small time scope of only five minutes of intervals. So here we can create an index on gmt_create to filter out most of the results:
Alter tale order_1 add index ind_order_gmt_create(gmt_create);
(The user has split tables for orders, with more than 50 split tables to add the index of gmt_create fields to);
5. After the optimization in the previous two steps, the user's instance resumes normal operation: to view the IO and connections, you can log into ApsaraDB for RDS console again to view the connections.
Thank you! We've received your feedback.