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

Today, my MySQL encountered a not-so-strange mistake, "Too many connections". When I usually encounter this problem, I basically modify / etc/my.cnf

Max_connections parameter, and then restart the database. But...

The database on the production server cannot be restarted at will.

I have no choice but to find a way to release some useless connections manually.

Log in to the MySQL prompt and use the command data show processlist to get all the MySQL connections to this server:

Mysql > show processlist +- + | Id | User | Host | db | Command | Time | State | Info | +-+- -+-+ | 1180421 | ur | 202.103.96.68 NULL 49754 | test1 | Sleep | 1 | NULL | 1180427 | ur | 202.103.96.68 NULL 55079 | test2 | Sleep | 1 | NULL | 1180429 | ur | 202.103.96.68vill55187 | Testdba | Sleep | 0 | NULL | 1180431 | ur | 202.103.96.68 | testdba | Sleep | 0 | NULL | | 1180437 | ur | 202.103.96.68 | 32825 | test1 | Sleep | 1 | NULL | 1180469 | ur | 202.103.96.68 | 58073 | testdba | Sleep | 0 | | NULL | | 1180472 | ur | 83.136.93.131 Sleep | test2 | Sleep | 8 | NULL | | 1180475 | root | localhost | NULL | Query | 0 | NULL | show PROCESSLIST | +-| -+-- + 8 rows in set (0.00 sec) mysql >

Then you can see a list of MySQL data connections like the one above, and each will have a process ID number (in the first column of the table above). All we have to do is enter the following command:

Mysql > kill 1180421 * * query OK, 0 rows affected (0.00 sec) mysql >

1180421 of them are the process numbers you find in the process list and want to kill.

The reasons for this problem are:

The number of connections exceeds the value set by MySQL, which is related to both max_connections and wait_timeout. The higher the value of Wait_timeout, the longer the idle wait for connections, resulting in a larger number of current connections.

Solution:

Modify the MySQL parameter file my.cnf and set it to max_connections=1000,wait_timeout=5. If you do not have this item, you can add it by yourself, and the modification will restart the MySQL service. If this error is not reported frequently, you need to optimize the overall performance of the server.

Note:

To prevent the problem of not being able to log in when too many connections occurs, mysql manual has the following instructions:

Mysqld actually allows max_connections+1 clients to connect. The extra connection isreserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not tonormal users (who should not need it), an administrator can connect to theserver and use SHOW PROCESSLIST to diagnose problems even if the maximum number ofunprivileged clients are connected.

Therefore, only the SUPER permission of the root user must be granted, and all database connection accounts cannot be granted SUPER permission. The aforementioned failure to log in after reporting an error is due to the root users directly configured by our application

To sum up, the ultimate solution to the problem:

1. Modify the parameter file my.cnf to adjust the connection parameters

two。 Check the program code and close the links that are not closed in time.

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.