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.

'UPDATE' query causing huge server loads. Help!

Discussion in 'General Discussion' started by kryotek, May 6, 2005.

  1. kryotek

    kryotek Registered

    Joined:
    May 3, 2005
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Hello, I'm new here and I have a problem. I have a website that gets over 425 million visits per day and it currently in the top 1,000 most popular websites on the net right now. I don't want to mention the site name because I don't want to take the subject off of what I'm asking about.

    We are only on ONE server but it has been doing good up until now. We have over 150,000 users and here is the server specs:

    4 Xeon 2.4 GHZ Processors
    2 gigs ram
    2 80 gig hdd (one dedicated to mysql)

    I recently recoded one of our main member pages that gets thousands of hits per minute. Each time it loads, it updates a few things in the database rather than inserting a few things into the database. We had to re-design our mysql structure so we could save the space and it just updates things on members accounts rather than inserting new things.

    Ever since I made this change, the server has been crashing because of the server load. It is always above 30 and when it peaks at about 70-80, it all crashes.

    I've fine tuned httpd.conf and my.cnf to the max and it is still crashing. When I last looked, apache was processing about 800 processes per second. Is there ANYTHING I can do to possibly optimize the update query or should we just fork out the money and cluster a few more servers?

    Btw, yes.. all of the mysql fields are indexed.
     
  2. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
    Have you checked your swapfile usage? Load causing servers to crash are usually as a result of memory thrashing and if your swapfile usage is about just a few K then that could be the cause - i.e. more memory may be required. For what you're doing, 2GB seems rather low to me.

    You don't mention your OS, but if you're running Linux, have you also checked that you do not have the laus rpm installed?
     
  3. kryotek

    kryotek Registered

    Joined:
    May 3, 2005
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    My swap is at 1 gig and it has used 0. I've been able to keep my memory usage below 30%.
     
  4. challii

    challii Well-Known Member

    Joined:
    Feb 3, 2004
    Messages:
    98
    Likes Received:
    0
    Trophy Points:
    6
    To be honest I think you are probably in iowait hell if you are using ide for the databases. I would upgrade to SCSI asap.
     
  5. kryotek

    kryotek Registered

    Joined:
    May 3, 2005
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Both hdd's on are scsi. Any other suggestions?
     
  6. brentp

    brentp Well-Known Member

    Joined:
    Mar 11, 2004
    Messages:
    324
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Ayr, North Queensland, Australia
    Setup a server cluster.

    Regards,
    Brent
     
Loading...

Share This Page