Results 1 to 8 of 8

Hybrid View

  1. #1

    Question Too many mysql processes!!

    I find a large number of sleeping mysql processes and very high CPU usage.
    Does it mean there are bugs in my PHP scripts? How to kill those sleeping processes?

    By the way, can anybody tell me how to shutdown mysql service temporarily and start it a few minutes later?
    It'll be great if they can be done in WHM.

    Thanks!

  2. #2
    Join Date
    Oct 2002
    Location
    Canada
    Posts
    3,103
    Some settings that can be found in mysql config file (often /etc/my.cnf) that have an affect on the time process waits for scripts and shold have effect on amount of sleeping process you have.

    interactive_timeout=20
    wait_timeout=10
    connect_timeout=10


    you cannot stop mysql in whm as much as i know. You can just restart it.

    to shop it in shell:
    /etc/init.d/mysql stop
    and to start it
    /etc/init.d/mysql start

    chkservd daemon that comes with cpanel instalations might start it back up sooner then you want so you should check and edit /etc/chkserv.d/mysql to make sure that does not happen.

  3. #3
    great! many thanks to sasha!

    here is another question. when will a mysql connection be closed in php scripts? shall i do mysql_close everytime i connect to mysql database? if i'm not wrong, php will close mysql connection automatically at the end of scripts. so how come so many sleeping mysql processes?
    Last edited by wula; 05-25-2005 at 10:32 AM.

  4. #4
    Join Date
    Oct 2002
    Location
    Canada
    Posts
    3,103
    Originally posted by wula
    here is another question. when will a mysql connection be closed in php scripts? shall i do mysql_close everytime i connect to mysql database? if i'm not wrong, php will close mysql connection automatically at the end of scripts. so how come so many sleeping mysql processes?

    If you connect with mysql_pconnect() connection is persistent and will not close when script exits so you have to do it with mysql_close().

    If you connect with mysql_connect() the connection will die on its own when script exits so there is no need to mysql_close().

    Eather way, the short timeouts I posted above will ensure that even not closed persistent connections die quickly and do not stay around sleeping forever.

  5. #5
    Join Date
    May 2004
    Location
    Lansing, MI, USA
    Posts
    1,548
    Originally posted by sasha
    Some settings that can be found in mysql config file (often /etc/my.cnf) that have an affect on the time process waits for scripts and shold have effect on amount of sleeping process you have.

    interactive_timeout=20
    wait_timeout=10
    connect_timeout=10
    After a few hours MySQL 4.0 does not honor those settings. We have a script in place that automatically finds them and kills them, without relying on MySQL to do so
    Jacob - WebOnce Technologies - 30 Day 100% Satisfaction Guarantee - Over 5 Years Going Strong!
    Website Hosting, PHP4&5, RoR, MySQL 5.0, Reseller Hosting, Development, and Designs
    Powered By JAM - Professional Website Development - PHP, MySQL, JavaScript, AJAX - Projects Small & Large

  6. #6
    Join Date
    Oct 2002
    Location
    Canada
    Posts
    3,103
    Originally posted by WebOnce
    After a few hours MySQL 4.0 does not honor those settings. We have a script in place that automatically finds them and kills them, without relying on MySQL to do so
    Odd, just looked up on 3 servers with uptimes of 48 , 49 and 60 days and settings seem to be well repected. I looked in mysql "show processlist" and I can see connection sleeping and exiting after specified number of seconds. This is mysql 4.0.23

  7. #7
    Join Date
    May 2004
    Location
    Lansing, MI, USA
    Posts
    1,548
    Maybe they fixed it in .23... it's common on .22 though
    Jacob - WebOnce Technologies - 30 Day 100% Satisfaction Guarantee - Over 5 Years Going Strong!
    Website Hosting, PHP4&5, RoR, MySQL 5.0, Reseller Hosting, Development, and Designs
    Powered By JAM - Professional Website Development - PHP, MySQL, JavaScript, AJAX - Projects Small & Large

  8. #8
    Originally posted by sasha
    If you connect with mysql_pconnect() connection is persistent and will not close when script exits so you have to do it with mysql_close().

    If you connect with mysql_connect() the connection will die on its own when script exits so there is no need to mysql_close().

    Eather way, the short timeouts I posted above will ensure that even not closed persistent connections die quickly and do not stay around sleeping forever.
    Well, according to the manual on php.net, mysql_close will not close persistent connections.

    "Notes
    Note: mysql_close() will not close persistent links created by mysql_pconnect().
    "

    http://www.php.net/manual/en/function.mysql-close.php

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •