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.

MySQL abnormal operations

Discussion in 'Database Discussions' started by iso99, Jul 2, 2014.

  1. iso99

    iso99 Well-Known Member

    Joined:
    Jan 5, 2011
    Messages:
    89
    Likes Received:
    2
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Since the past month, my MySQL server is behaving abnormally. For some time, it'll just use 100% CPU for 2-3 days doubling my normal load average. Despite my optimization efforts, this still happens occasionally.

    For most of the time, it behaves normally with my load average playing from 0.2 to about 1.4. Then it will sudden use a lot of CPU power and my load average will begin to rise from 2 to ~4.

    There's about 300 sites on the server, using Litespeed. When the high CPU problem occurs, I don't find any suspicious processes. I perform daily maldet, weekly ClamAV scans and monthly rootkit scans, no problem with them.

    But this is really odd.

    One thing I suspect is the slow queries, however, the same sites perform the same activities why would CPU usage shoot up? (check attachment below)

    I'm really bothered by this issue, any help is appreciated.
     

    Attached Files:

  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You may also want to run the following command the next time this happens:

    Code:
    mysqladmin processlist;
    It might help you narrow down any particular databases that are utilizing the additional CPU.

    Thank you.
     
  3. iso99

    iso99 Well-Known Member

    Joined:
    Jan 5, 2011
    Messages:
    89
    Likes Received:
    2
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Hello,

    I think I've hit the query_cache inefficiency issue. For the past weeks, I use >=256M for query_cache experiments and I always see MyISAM lock conflicts and high memory and the issue I mentioned above.

    I went back to the regular < 128M configuration and for the past 2 days, it's sailing smoothly. No high CPU nor high memory consumption. I have not done any changes to InnoDB settings, so I'm blaming it to query cache.

    Please enlighten me on this one, is query size alloted per mysql connection?
     
Loading...

Share This Page