Hi Garrett,

Garrett <gyssler@gmail.com> wrote:

> I've been having this problem recently on my dedicated LAMP server. It
> pretty much only occurs during high traffic loads. MySQL, for whatever
> reason, randomly spawns a high number of threads and completely bogs
> down the server.

MySQL creates one thread per connection. Plus some (5-10, depends on
configuration) internal helper threads. So the spikes in MySQL threads
are caused by spikes in the number of open connections.

> And here's what the query report looks like. We can see that 'no'
> queries are being processed when the mysql thread count is high.

This could be, when all those connections are idle. But I doubt that.

> IO Wait also reaches 100%~ when the thread count jumps. So the server
> load jumps to 40+, and apache dies as well.

I think you miss cause and effect here. What you see, is a typical
congestion situation, caused by high load and bad configuration.

It can start with one slow query, lets say a query that does a table
scan on a big table. This will slowdown I/O for all other queries and
the average response time of MySQL will increase.

With a constant inflow of HTTP requests, this will cause Apache to
start new workers (because the old ones are still busy, waiting for
MySQL to answer). Those new Apache workers will create new connections
to MySQL, causing more MySQL threads.

All this creation of new processes (Apache) and threads (MySQL) puts
additional stress on cpu and memory, making the situation worse.
(probably some memory has to be freed for the new threads, that was
used by the buffer cache before - this means even more I/O)
In worst case, the operating system has to start swapping to make
memory available.

> If I do nothing, it takes
> anywhere from 1 to 3 hours for the server to 'fix' itself. If I
> perform a 'service mysqld restart' though, it fixes itself
> immediately.

You could as well stop Apache or cut the network to stop inflow of
requests. This all would allow the system to empty the work queues
and go back to normal. It's not a solution, though.

> Any help is appreciated. Thanks!

I run your my.cnf through http://forge.mysql.com/tools/tool.php?id=44

~ $mysql-memory-usage my.cnf
/usr/local/mysql/current/libexec/mysqld will use at most:
426.0 MB for global stuff
12.3 MB per thread
6.4 GB total (with 500 active threads)
2.8 GB total (with 200 active threads)
and additionally:
300.0 MB for each HEAP table
300.0 MB for each temporary table

Looks like you configured a lot of buffers too generously. Especially
the per-thread buffers are too big when you expect many connections.
That would be:

sort_buffer_size=4M
read_buffer_size=4M
join_buffer_size=2M
read_rnd_buffer_size=4M

If you leave those at the defaults, you come to

426.0 MB for global stuff
2.7 MB per thread
957.2 MB total (with 200 active threads)

which is more suitable for a 2GB server that hosts MySQL and Apache.


There are more general recipes to handle a situations like yours.
First of all you should try to identify the bottleneck. This is
probably the disk. Consider switching to a RAID-10 with 4..10
disks. You can also add more RAM to lessen I/O stress.
With mostly MyISAM tables you should leave at least 25% of the
memory "unused" - it will be used by the buffer cache. Remember:
MyISAM caches only indexes in memory. Access to MyISAM data files
relies on the operating system caches.

Another countermeasure is limitation of inflow. Probably you
configured Apache to allow too many workers. If you lower this
number, you will also need fewer MySQL threads. It could be wise
to to adjust minspare* and maxspare* Apache settings to keep many
workers in memory. This will reduce thread creation. Etc. pp.

There are many tuning possibilities in a LAMP stack. Lern it!


XL
--
Axel Schwenke, MySQL Senior Support Engineer, Sun Microsystems

MySQL User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/