Terminating idle mysql connections

Question

I see a lot of connections are open and remain idle for a long time, say 5 minutes.

Is there any solution to terminate / close it from server without restarting the mysql service?

I am maintaining a legacy PHP system and can not close the connections those are established to execute the query.

Should I reduce the timeout values in my.cnf file those defaults to 8 hours?

# default 28800 seconds  interactive_timeout=60 wait_timeout=60 

Answer 1

Manual cleanup:

You can KILL the processid.

mysql> show full processlist; +---------+------------+-------------------+------+---------+-------+-------+-----------------------+ | Id      | User       | Host              | db   | Command | Time  | State | Info                  | +---------+------------+-------------------+------+---------+-------+-------+-----------------------+ | 1193777 | TestUser12 | 192.168.1.11:3775 | www  | Sleep   | 25946 |       | NULL                  | +---------+------------+-------------------+------+---------+-------+-------+-----------------------+  mysql> kill 1193777; 

But:

• the php application might report errors (or the webserver, check the error logs)
• don't fix what is not broken - if you're not short on connections, just leave them be.

Automatic cleaner service ;)

Or you configure your mysql-server by setting a shorter timeout on wait_timeout and interactive_timeout

mysql> show variables like "%timeout%"; +--------------------------+-------+ | Variable_name            | Value | +--------------------------+-------+ | connect_timeout          | 5     | | delayed_insert_timeout   | 300   | | innodb_lock_wait_timeout | 50    | | interactive_timeout      | 28800 | | net_read_timeout         | 30    | | net_write_timeout        | 60    | | slave_net_timeout        | 3600  | | table_lock_wait_timeout  | 50    | | wait_timeout             | 28800 | +--------------------------+-------+ 9 rows in set (0.00 sec) 

Set with:

set global wait_timeout=3; set global interactive_timeout=3; 

(and also set in your configuration file, for when your server restarts)

But you're treating the symptoms instead of the underlying cause - why are the connections open? If the PHP script finished, shouldn't they close? Make sure your webserver is not using connection pooling...