Kill idle connection in MariaDB / MySQL

Idle connection remove to avoid max connection reached in MySQL

Manually Clean the idle Connection in MariaDB/MYSQL:

  1. Login with root user:
  2. Check the session is in sleep state and get kill command for release the sessions:
select count(*) from information_schema.processlist where Command='Sleep';

select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep';

concat('KILL ',id,';')|
----------------------+
KILL 10;              |
KILL 9;               |

3. Execute the command to Kill the session got from upper query output.

KILL 10; 

Automatic Clean the idle Connection in MariaDB/MySQL:

Interactive operation: this means opening the MySQL client on your local computer and doing various SQL operations on the command prompt.
INTERACTIVE_TIMEOUT is used to automatically clean the interactive connection in MariaDB/MySQL. The number of seconds the server waits for activity on an interactive connection before closing it

Non-interactive: means calls from the program. Example Tomcat Web service call to the database server through JDBC to connect etc.
WAIT_TIMEOUT is used to automatically clean the idle connection in MariaDB/MySQL.
The number of seconds the server waits for activity on a connection before closing it.

Note: Default time for both is 28800 seconds i.e. 8 hours

Check the Variables Values:

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

Variable_name           |Value|
------------------------+-----+
wait_timeout            |28800|

SHOW GLOBAL VARIABLES LIKE '%interactive_timeout%'

Variable_name      |Value|
-------------------+-----+
interactive_timeout|28800|

Change the time limit to 30 minutes for idle connection timeout: The value used in both variables is in Seconds i.e. 30 min – 1800 seconds or 1 hour – 3600 seconds.

SET GLOBAL wait_timeout=1800;

SET GLOBAL interactive_timeout=1800;

You can also change to the Default value:

SET GLOBAL wait_timeout=DEFAULT;

SET GLOBAL interactive_timeout=DEFAULT;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.