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.

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:
    16
    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:
    16
    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:
    321
    Likes Received:
    3
    Trophy Points:
    16
    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:
    16
    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:
    8
    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 ?
     
  6. konrath

    konrath Well-Known Member

    Joined:
    May 3, 2005
    Messages:
    367
    Likes Received:
    0
    Trophy Points:
    16
    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:
    18
    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.
     
  8. konrath

    konrath Well-Known Member

    Joined:
    May 3, 2005
    Messages:
    367
    Likes Received:
    0
    Trophy Points:
    16
    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:
    18
    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

     
  10. hostnex

    hostnex Well-Known Member

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

Share This Page