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 5.5 extremely high loads

Discussion in 'Workarounds and Optimization' started by webmentors, Oct 18, 2012.

  1. webmentors

    webmentors Member

    Joined:
    Dec 7, 2004
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    After migrating one account with a MySQL database, (MySQL 4 to 5.5), As soon as the site gets a hits, CPU loads get very (120 or more) causing the machine to completely lock up after a couple of mins. I've checked the table structure and it could use some optimization.

    I had to suspend this account for now as I am not able to run any sort of analytics long enough. Eventually CPU usage goes really high.

    Information on the server:
    4 GB RAM, 8 cores total (Dual Xeon)
    RedHat 6. 64 Bit system
    WHM 11.32.5 Release.
    top usage is normally 0.30%
    its a dedicated server, not a VPS.

    What do I get MySQL to play nice and not take down the whole server with it cant handle the queries from this application?

    More info at normal usage (when the problem database is not activated by the problem site).
    top - 11:05:42 up 11 days, 8:16, 4 users, load average: 0.29, 0.23, 0.19
    Tasks: 349 total, 1 running, 348 sleeping, 0 stopped, 0 zombie
    Cpu(s): 1.3%us, 0.6%sy, 0.0%ni, 98.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
    Mem: 3911780k total, 3776672k used, 135108k free, 303740k buffers
    Swap: 2104504k total, 169440k used, 1935064k free, 2605996k cached
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,482
    Likes Received:
    203
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    You don't mention anything about the site. What software is the site using, some sort of CMS with lots of addons possibly? That's where I'd start looking before questioning server configuration.
     
  3. webmentors

    webmentors Member

    Joined:
    Dec 7, 2004
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    Thanks for the suggestion.

    I took a look at the code of the site, its a simple ecommerce website. It doesnt use a CMS or have an addon. Just straight php/MySQL. I took one quick look at the templating engine, and in the SQL code, the table structures and queries look like they could be seriously improved. For example on every page hit, there is a LIKE query on a table thats has 6000+ rows, on a field that it isn't indexed. This query is then used to create dynamic functions.

    It it comes down to it, we can probably patch up the some of the code to optimize the queries.
     
  4. webmentors

    webmentors Member

    Joined:
    Dec 7, 2004
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    OK it looked like a MySQL issue but it wasn't. actually it was suPHP that was causing an excessive load. After using Apache Configuration setting in WHM, the load is down to an average 1.28.
     
Loading...

Share This Page