MySQL eventually brings system to a halt

[Adam]

Hi all,

a site I run has been getting busier during peak hours and I've
noticed at times the mysql server will bring the entire server to a
standstill.

I have tweaked the mysql server settings and my application so that
the database is running smooth and fast and for 95% of the time,
queries come back fast, the server doesn't get loaded badly (even
during peak hours).

At some point (I'm guessing when a big/slow query runs), the queries
start to build up - queries that ran fast before now take longer and
subsequent queries take even longer until the mysql server is maxed
and the entire server is running extremely slow.

A restart of apache or mysql will clear the lagging query and
everything is back to running smooth again. I have slow queries being
logged however *every query* is logged once the server starts to
crash.

Is there any way I can ensure no 1 query/thread will hog the system
resources? I would prefer 1 user be told mysql has broken than 100
users.

I'm running MySQL 4.0.18 off a VPS on Fedora Core Rel 1 (apache 2.0.48
using php 4.3.4).

Cheers,

Adam