Idle connection remove to avoid max connection reached in MySQL
Manually Clean the idle Connection in MariaDB/MYSQL:
- Login with root user:
- 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.
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;