How to Terminate Idle MySQL Connections

Problem Scenario:

Databases can accumulate a significant number of idle connections that remain open for extended periods. This can lead to resource consumption and performance issues.

Manual Cleanup:

One approach is manual termination of idle processes. To identify open connections, use the SHOW FULL PROCESSLIST command. You can then kill the process with KILL [process_id].

However, this method has limitations:

• It may cause errors in PHP applications or webservers.
• It does not address the underlying cause of idle connections.

Automatic Cleaner Service:

A more comprehensive solution is to configure the MySQL server with shorter timeouts for idle connections. Edit the my.cnf file and adjust the following parameters:

wait_timeout=3
interactive_timeout=3

With these settings, MySQL will automatically terminate idle connections after 3 seconds.

Considerations:

While this approach is effective, it's important to investigate the root cause of idle connections. Issues like PHP connection pooling can contribute to this problem and should be addressed.