The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

crazy mysql overload

Discussion in 'General Discussion' started by Secret Agent, Oct 11, 2004.

Thread Status:
Not open for further replies.
  1. Secret Agent

    Secret Agent Guest

    My server hit an overload of 140+. This is crazy. I checked the cpu / memory / mysql usage and showed this in red:

    mysql 141.25 24.30 0.0
    Top Process %CPU 99.9 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.domain.com.pid --skip-locking --port3306
    Top Process %CPU 99.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.domain.com.pid --skip-locking --port3306
    Top Process %CPU 97.4 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.domain.com.pid --skip-locking --port3306

    What is that stuff?
     
  2. Secret Agent

    Secret Agent Guest

    Can someone please help. Still happening right now and causing Exim to crash as well
     
  3. Secret Agent

    Secret Agent Guest

    These are the running MySQL processes as well:

    +------+----------------+-----------------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +------+----------------+-----------------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
    | 1069 | eximstats | localhost | eximstats | Sleep | 42 | | |
    | 2171 | eximstats | localhost | eximstats | Sleep | 369 | | |
    | 2306 | leechprotect | localhost | leechprotect | Sleep | 331 | | |
    | 3091 | studio | localhost:49923 | hosting | Sleep | 96 | | |
    | 3196 | antro_agbook1 | localhost | antro_agbook1 | Query | 96 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3253 | antro_agbook1 | localhost | antro_agbook1 | Query | 79 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3255 | antro_agbook1 | localhost | antro_agbook1 | Query | 78 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3275 | antro_agbook1 | localhost | antro_agbook1 | Query | 73 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3277 | antro_agbook1 | localhost | antro_agbook1 | Query | 73 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3286 | antro_agbook1 | localhost | antro_agbook1 | Query | 71 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3342 | antro_agbook1 | localhost | antro_agbook1 | Query | 55 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3360 | antimon_genel | localhost | antimon_vbulletin | Sleep | 46 | | |
    | 3379 | antro_agbook1 | localhost | antro_agbook1 | Query | 43 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3394 | antro_agbook1 | localhost | antro_agbook1 | Query | 39 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3395 | antro_agbook1 | localhost | antro_agbook1 | Query | 39 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3477 | kalistyl_admin | localhost | kalistyl_community | Sleep | 21 | | |
    | 3500 | antro_agbook1 | localhost | antro_agbook1 | Query | 13 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3512 | antro_agbook1 | localhost | antro_agbook1 | Query | 11 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3518 | antro_agbook1 | localhost | antro_agbook1 | Query | 10 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3519 | antro_agbook1 | localhost | antro_agbook1 | Query | 10 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 3554 | root | localhost | | Query | 0 | | show processlist |
    +------+----------------+-----------------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
     
  4. oderland

    oderland Well-Known Member
    PartnerNOC

    Joined:
    Dec 30, 2002
    Messages:
    103
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Kungsbacka, Sweden
    same here but we have overload on "Copying to tmp table" issue on one of our oscommerce shops

    Any clue?
     
  5. Leandro

    Leandro Well-Known Member

    Joined:
    Sep 23, 2003
    Messages:
    48
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Argentin
    cPanel Access Level:
    DataCenter Provider
    I have the same problem in two diferentt servers,

    Top Process %CPU 99.9 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/domain.net.pid --skip-locking
    Top Process %CPU 99.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/domain.net.pid --skip-locking
    Top Process %CPU 80.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/domain.net.pid --skip-locking

    Any fix ?? :confused:
     
    #5 Leandro, Jan 2, 2006
    Last edited: Jan 9, 2006
  6. positive

    positive Member

    Joined:
    Mar 29, 2003
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    same problem here?
    any solutions
     
  7. eger

    eger Well-Known Member

    Joined:
    Feb 28, 2003
    Messages:
    128
    Likes Received:
    0
    Trophy Points:
    16
    I am also having the same issue on one of my servers. A simple query is stuck on "Copying to tmp table" for about 10+ minutes while mysql is using 99% CPU.

    I found a big article on mysql.com about it. But there is no relevant fix anywhere in the posts as far as I can tell. Just a bunch more confirming the problem: http://forums.mysql.com/read.php?24,39066,39066#msg-39066
     
  8. MK Hosting

    MK Hosting Active Member

    Joined:
    Dec 5, 2005
    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    UK, Milton Keynes
    I am also now having this problem. Any Fix yet?
     
  9. eger

    eger Well-Known Member

    Joined:
    Feb 28, 2003
    Messages:
    128
    Likes Received:
    0
    Trophy Points:
    16
    I haven't found a fix for this. The only thing I could do was disable the advanced guestbook and contact a customer also having this problem in his mysql application.
     
  10. kship

    kship Member

    Joined:
    Oct 29, 2004
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    HUNTSVILLE, TEXAS
    advanced guestbook

    I removed all instances from hosts directories and completely uninstalled the advanced guestbook and the levels seemed to have dropped. The guestbook is something that should not be used!
     
  11. Manuel_accu

    Manuel_accu Well-Known Member

    Joined:
    Jun 19, 2005
    Messages:
    191
    Likes Received:
    0
    Trophy Points:
    16
    Yes, this type of issue I have faced

    You make have select or update query running with many distinct and join which is hanging while fetching data from your database. Either you can alter this query or kill that particular mysql processes.
     
  12. wardv

    wardv Member

    Joined:
    Aug 10, 2005
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    I think I've got a fix!

    For every agbook database, add an extra INDEX:

    ALTER TABLE `book_com` ADD INDEX `id` ( `id` )

    (for phpmyadmin users: go to the table boom_com, create an index with the name 'id' on the column 'id').

    When an extra INDEX is added, the MySQL Bug isnt triggered (tested using mysql 4.1.21).
     
Loading...
Thread Status:
Not open for further replies.

Share This Page