Examples of handling excessive MySQL connections

Examples of handling excessive MySQL connections

I. Problem Description

Today, I suddenly received an error: 503 Service Temporarily Unavailable. After query, it is found that a user has a large number of connections and the database connection has been fully occupied. Solution: Immediately kill the blocked process, and then expand the max_connections parameter.

Ii. Handling Methods

1. query the connection status

Root @ localhost> show processlist;

......
1001 rows in set (0.00 sec)
Root @ localhost> show variables like '% proces % ';
Empty set (0.00 sec)

2. Check Parameters

Root @ localhost> show global status like 'max _ used_connections ';

+ ---------------------- + ------- +
| Variable_name | Value |
+ ---------------------- + ------- +
| Max_used_connections | 1001 |
+ ---------------------- + ------- +
1 row in set (0.00 sec)

3. generate a process killing script using commands

Root @ localhost> select concat ('Kill ', id,'; ') from information_schema.processlist where user = 'Sam 'into outfile'/tmp/a.txt
The script content is as follows:

+ ------------------------ +

| Concat ('Kill ', id,'; ') |
+ ------------------------ +
| KILL 31964612; |
| KILL 31964609; |
| KILL 31964611; |
......
| KILL 31966619; |
| KILL 31966620; |
+ ------------------------ +
991 rows in set (0.02 sec)
Root @ localhost>

4. Execute the KILL script generated above.

Root @ localhost> source/tmp/a.txt

Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
......
 
5. Check the connection status and restore to normal.

Root @ localhost> show processlist;

6. Modify the Max_used_connections parameter (Note: Remember to modify the my. cnf file, which will remain valid after the next restart)

Mysql> set GLOBAL max_connections = 2000;

Query OK, 0 rows affected (0.00 sec)

Mysql> show variables like '% max_connections % ';
+ ----------------- + ------- +
| Variable_name | Value |
+ ----------------- + ------- +
| Max_connections | 2000 |
+ ----------------- + ------- +
1 row in set (0.00 sec)

Iii. Summary
MySQL Parameter Learning max_connections, a parameter that controls the number of connections. There must be some problems behind this issue. Do not just tune up parameters. After analyzing the statements, the security department was finally positioned as conducting a security test, resulting in problems.

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/150985.htm