Re: FN-FORUM: Help! Apache server being hammered and site keeps falling over!

date posted 1st November 2007 12:09

Anthony Cartmell wrote:
>
>> This takes time and money, though; this is more about quick speed
>> fixes, something which the config posted previously seems to have
>> helped with.
>
> My guess is a slow MySQL query: when I get these during development
> (such as with huge tables and forgetting an important join condition, so
> the results go silly) it effectively hangs the MySQL and apache
> processes, leading to excessive CPU and load. Re-starting MySQL kills
> the run-away query, and all returns to normal.

Just a quick caveat. If you restart mysql on debian, there is a default
setting which tells debian to check mysql. This can cause severe
problems on large or busy tables, as the process read/write locks the table.

We run 36 busy sites with replicated mysql databases and this caused
massive downtime for about half a day until we killed all the
debian-sys-maint queries.

look at /etc/mysql/debian-start.

Tom.
>
>> I'm guessing there's a fairly major memory leak somewhere in the
>> software, but I'm not sure where it is.
>
> You have plenty of spare memory and no swapping, so it's not a memory
> problem. It's a CPU usage problem: something is doing rather too much
> calculation work. That could be an inefficient PHP script or an
> inefficient MySQL query.
>
> mod_status will tell you which web pages are taking the CPU, which
> should help you narrow down the problem.
>
> And you do have indexes on all your foreign keys in MySQL, don't you?
> Miss one on a large table and your query can quickly go from taking a
> few milliseconds to dozens of seconds.
>
> Anthony