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

User · Answer

I don t see any problem  unless you are not managing them using a connection pool   If you use connection pool  these connections are re-used instead of initiating new connections  so basically  leaving open connections and re-use them it is less problematic than re-creating them each time

User · Answer

Manual cleanup  You can KILL the processid  mysql gt  show full processlist   --------- ------------ ------------------- ------ --------- ------- ------- -----------------------    Id        User         Host                db     Command   Time    State   Info                     --------- ------------ ------------------- ------ --------- ------- ------- -----------------------    1193777   TestUser12   192 168 1 11 3775   www    Sleep     25946           NULL                     --------- ------------ ------------------- ------ --------- ------- ------- -----------------------   mysql gt  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 gt  show variables like  quot  timeout  quot    -------------------------- -------    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

[mysql] Terminating idle mysql connections

Examples related to mysql

Examples related to timeout

Examples related to kill

Examples related to wait

Examples related to python-idle