'UPDATE' query causing huge server loads. Help!

**kryotek** · 05-06-2005 04:43 PM

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.

**chirpy** · 05-06-2005 05:12 PM

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?