Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

My sql problem ? No reason to MySQL eat 100% CPU

Discussion in 'Workarounds and Optimization' started by konrath, Aug 30, 2010.

  1. konrath

    konrath Well-Known Member

    Joined:
    May 3, 2005
    Messages:
    367
    Likes Received:
    0
    Trophy Points:
    166
    Location:
    Brasil
    Hello

    the mysql is eating the processors

    ( CPU ) >>> 102.4 1.5 1:41.15 mysqld

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    23330 mysql 16 0 737m 88m 4460 S 102.4 1.5 1:41.15 mysqld
    5995 tomcat 27 12 197m 54m 6292 S 0.7 0.9 0:10.92 jsvc
    11 root 5 -10 0 0 0 S 0.3 0.0 0:06.54 events/1
    11882 named 20 0 79784 25m 2324 S 0.3 0.4 1:32.59 named
    18908 nobody 19 4 151m 78m 3488 S 0.3 1.3 0:00.16 httpd
    1 root 16 0 1888 632 536 S 0.0 0.0 0:16.90 init


    ---------------------------

    mytop show nothing.


    Id User Host/IP DB Time Cmd Query or State
    -- ---- ------- -- ---- --- ----------
    21 root localhost test 0 Query show full processlist

    ---------------------------

    Pid Owner Priority Cpu % Mem % Command
    23330 mysql 0 71.4 1.6 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.vipreseller7.net.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

    ---------------------------


    No reason to MySQL eat 100% CPU

    This happens at 3 o'clock in the morning. No site overloading the server.

    Any suggestions?



    Thank you
    Konrath
     
    #1 konrath, Aug 30, 2010
    Last edited: Aug 30, 2010
  2. konrath

    konrath Well-Known Member

    Joined:
    May 3, 2005
    Messages:
    367
    Likes Received:
    0
    Trophy Points:
    166
    Location:
    Brasil
    Hello

    I found the problem. The innodb is eating the processor.

    How to fix this problem ? I want keep innodb enable.

    Thank you
    Konrath
     
    #2 konrath, Aug 30, 2010
    Last edited: Aug 31, 2010
  3. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    320
    Likes Received:
    3
    Trophy Points:
    68
    Sounds like values are set to high on some parameters.

    Install mysql tuner via SSH by:

    Once done run the mysql tuner by:

    My advice is run it once, Change settings, Then restart mysql - Then leave it for a around 4 days then run it again, And eventually you will get the most stable and best performance from your mysql.
     
  4. konrath

    konrath Well-Known Member

    Joined:
    May 3, 2005
    Messages:
    367
    Likes Received:
    0
    Trophy Points:
    166
    Location:
    Brasil
    Hello GaryT

    The problem continues. I will execute your recomendation.

    When the innodb are activated, the mysql eat 100% of CPU.

    Thank you
    Konrath
     
    #4 konrath, Aug 31, 2010
    Last edited: Sep 7, 2010
  5. hostnex

    hostnex Well-Known Member

    Joined:
    May 2, 2008
    Messages:
    77
    Likes Received:
    1
    Trophy Points:
    58
    Location:
    Islamabad, Pakistan, Pakistan
    cPanel Access Level:
    Root Administrator
    We are having the same kind of issue where MySQL service stop responding and take 100 % CPU usage during cpanel nightly backups. Any Solutions ?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. konrath

    konrath Well-Known Member

    Joined:
    May 3, 2005
    Messages:
    367
    Likes Received:
    0
    Trophy Points:
    166
    Location:
    Brasil

    Hello Hostnex

    I have this problem with or without backup running. The problem is with inoodb.

    When inoodb is running the MYSQL eat 100% of CPU.

    The inoodb file has 5GB. ( 5194645504 Sep 7 15:34 ibdata1 )

    See

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    7898 mysql 21 6 1100m 574m 5076 S 103.8 9.4 185:45.47 mysqld



    Thank you
    Konrath
     
    #6 konrath, Sep 7, 2010
    Last edited: Sep 7, 2010
  7. B12Org

    B12Org Well-Known Member

    Joined:
    Jul 15, 2003
    Messages:
    692
    Likes Received:
    1
    Trophy Points:
    168
    Location:
    Seattle Washington
    cPanel Access Level:
    Root Administrator
    In my experience this has been caused by queries against innodb tables that are kind or large or changing often that do not have indexes and thus require a full table scan for every operation.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  8. konrath

    konrath Well-Known Member

    Joined:
    May 3, 2005
    Messages:
    367
    Likes Received:
    0
    Trophy Points:
    166
    Location:
    Brasil

    Hello B12ORG

    How to enable indexes ?

    Thank you
    Konrath
     
  9. B12Org

    B12Org Well-Known Member

    Joined:
    Jul 15, 2003
    Messages:
    692
    Likes Received:
    1
    Trophy Points:
    168
    Location:
    Seattle Washington
    cPanel Access Level:
    Root Administrator
    Please see mysql documentation on indexes - it will explain better than I can.
    MySQL :: MySQL 5.0 Reference Manual :: 7.5.3 How MySQL Uses Indexes

     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  10. hostnex

    hostnex Well-Known Member

    Joined:
    May 2, 2008
    Messages:
    77
    Likes Received:
    1
    Trophy Points:
    58
    Location:
    Islamabad, Pakistan, Pakistan
    cPanel Access Level:
    Root Administrator
    We moved to the new server and now everything working fine :)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice