Huge Amount of MySQL Threads Being Created (Only happens during hightraffic)

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. Here's a screenshot of the past week's mysql thread
activity.

http://xinil.net/images/mysql/localh...reads-week.png

Notice the random 200+ spikes?

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.

http://xinil.net/images/mysql/localh...eries-week.png

IO Wait also reaches 100%~ when the thread count jumps. So the server
load jumps to 40+, and apache dies as well. 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.

Does anyone have any ideas why this happens? As you can see, the
server is fine sometimes, but during large traffic hours, it randomly
spikes.

Any help is appreciated. Thanks!

Server specs:
Core 2 Duo E6550, 2gb ram

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

max_connections=500
safe-show-database

query-cache-type=1
query_cache_limit=2M
query-cache-size=32M

ft_min_word_len=2
concurrent_insert=2

myisam_sort_buffer_size=64M

key_buffer_size=384M
max_allowed_packet=16M

table_cache=1848

thread_cache_size=8
thread_concurrency=4

low_priority_updates=1
long_query_time=5
log-slow-queries=/home/mysql/slow-query
#log-queries-not-using-indexes

max_heap_table_size=300M
tmp_table_size=300M

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

wait_timeout = 120

Garrett 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. Here's a screenshot of the past week's mysql thread
> activity.
>
> http://xinil.net/images/mysql/localh...reads-week.png
>
> Notice the random 200+ spikes?
>
> 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.
>
> http://xinil.net/images/mysql/localh...eries-week.png
>
> IO Wait also reaches 100%~ when the thread count jumps. So the server
> load jumps to 40+, and apache dies as well. 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.
>
> Does anyone have any ideas why this happens? As you can see, the
> server is fine sometimes, but during large traffic hours, it randomly
> spikes.
>
> Any help is appreciated. Thanks!
>
> Server specs:
> Core 2 Duo E6550, 2gb ram
>
> my.cnf
>
> [mysqld]
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
>
> max_connections=500
> safe-show-database
>
> query-cache-type=1
> query_cache_limit=2M
> query-cache-size=32M
>
> ft_min_word_len=2
> concurrent_insert=2
>
> myisam_sort_buffer_size=64M
>
> key_buffer_size=384M
> max_allowed_packet=16M
>
> table_cache=1848
>
> thread_cache_size=8
> thread_concurrency=4
>
> low_priority_updates=1
> long_query_time=5
> log-slow-queries=/home/mysql/slow-query
> #log-queries-not-using-indexes
>
> max_heap_table_size=300M
> tmp_table_size=300M
>
> sort_buffer_size=4M
> read_buffer_size=4M
> join_buffer_size=2M
> read_rnd_buffer_size=4M
>
> wait_timeout = 120
>

The could be a lot of things. I suspect the most likely is locks. What
kind of tables are you using, and what requests are being made to MySQL
at the time? Anything unusual, like is an administrator updating
something at the time or a backup being run?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On May 19, 5:45 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Garrett 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. Here's a screenshot of the past week's mysql thread
> > activity.
>
> >http://xinil.net/images/mysql/localh...reads-week.png
>
> > Notice the random 200+ spikes?
>
> > 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.
>
> >http://xinil.net/images/mysql/localh...eries-week.png
>
> > IO Wait also reaches 100%~ when the thread count jumps. So the server
> > load jumps to 40+, and apache dies as well. 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.
>
> > Does anyone have any ideas why this happens? As you can see, the
> > server is fine sometimes, but during large traffic hours, it randomly
> > spikes.
>
> > Any help is appreciated. Thanks!
>
> > Server specs:
> > Core 2 Duo E6550, 2gb ram
>
> > my.cnf
>
> > [mysqld]
> > datadir=/var/lib/mysql
> > socket=/var/lib/mysql/mysql.sock
>
> > max_connections=500
> > safe-show-database
>
> > query-cache-type=1
> > query_cache_limit=2M
> > query-cache-size=32M
>
> > ft_min_word_len=2
> > concurrent_insert=2
>
> > myisam_sort_buffer_size=64M
>
> > key_buffer_size=384M
> > max_allowed_packet=16M
>
> > table_cache=1848
>
> > thread_cache_size=8
> > thread_concurrency=4
>
> > low_priority_updates=1
> > long_query_time=5
> > log-slow-queries=/home/mysql/slow-query
> > #log-queries-not-using-indexes
>
> > max_heap_table_size=300M
> > tmp_table_size=300M
>
> > sort_buffer_size=4M
> > read_buffer_size=4M
> > join_buffer_size=2M
> > read_rnd_buffer_size=4M
>
> > wait_timeout = 120
>
> The could be a lot of things. I suspect the most likely is locks. What
> kind of tables are you using, and what requests are being made to MySQL
> at the time? Anything unusual, like is an administrator updating
> something at the time or a backup being run?
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

All tables are in Myisam format. I'm the only administrator of the
site and there weren't any updates/back-ups being run.

Lock Wait ratio = 1 : 60

Server averages around 200 queries per sec. I don't notice any unusual
items or queries being run at the time of the issue. It seems to only
happen when there is high traffic on the site though (usually Weekends/
Monday).

Thanks for the help!

Garrett wrote:
> On May 19, 5:45 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Garrett 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. Here's a screenshot of the past week's mysql thread
>>> activity.
>>> http://xinil.net/images/mysql/localh...reads-week.png
>>> Notice the random 200+ spikes?
>>> 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.
>>> http://xinil.net/images/mysql/localh...eries-week.png
>>> IO Wait also reaches 100%~ when the thread count jumps. So the server
>>> load jumps to 40+, and apache dies as well. 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.
>>> Does anyone have any ideas why this happens? As you can see, the
>>> server is fine sometimes, but during large traffic hours, it randomly
>>> spikes.
>>> Any help is appreciated. Thanks!
>>> Server specs:
>>> Core 2 Duo E6550, 2gb ram
>>> my.cnf
>>> [mysqld]
>>> datadir=/var/lib/mysql
>>> socket=/var/lib/mysql/mysql.sock
>>> max_connections=500
>>> safe-show-database
>>> query-cache-type=1
>>> query_cache_limit=2M
>>> query-cache-size=32M
>>> ft_min_word_len=2
>>> concurrent_insert=2
>>> myisam_sort_buffer_size=64M
>>> key_buffer_size=384M
>>> max_allowed_packet=16M
>>> table_cache=1848
>>> thread_cache_size=8
>>> thread_concurrency=4
>>> low_priority_updates=1
>>> long_query_time=5
>>> log-slow-queries=/home/mysql/slow-query
>>> #log-queries-not-using-indexes
>>> max_heap_table_size=300M
>>> tmp_table_size=300M
>>> sort_buffer_size=4M
>>> read_buffer_size=4M
>>> join_buffer_size=2M
>>> read_rnd_buffer_size=4M
>>> wait_timeout = 120
>> The could be a lot of things. I suspect the most likely is locks. What
>> kind of tables are you using, and what requests are being made to MySQL
>> at the time? Anything unusual, like is an administrator updating
>> something at the time or a backup being run?
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> All tables are in Myisam format. I'm the only administrator of the
> site and there weren't any updates/back-ups being run.
>
> Lock Wait ratio = 1 : 60
>
> Server averages around 200 queries per sec. I don't notice any unusual
> items or queries being run at the time of the issue. It seems to only
> happen when there is high traffic on the site though (usually Weekends/
> Monday).
>
> Thanks for the help!
>
>
>

OK, unfortunately, MyISAM doesn't have much to help tell if, for
instance, a table is locked. But my initial guess is that something is
locking one or more tables, and others are backing up behind it. Not
necessarily the case, mind you. But it often is.

Have you tried enabling the slow query log and see what it shows? This
might help.

How big are your tables, and how many rows in them? Have you looked at
the tuning parameters - buffer sizes, etc.?


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On May 19, 7:09 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Garrett wrote:
> > On May 19, 5:45 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> Garrett 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. Here's a screenshot of the past week's mysql thread
> >>> activity.
> >>>http://xinil.net/images/mysql/localh...reads-week.png
> >>> Notice the random 200+ spikes?
> >>> 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.
> >>>http://xinil.net/images/mysql/localh...eries-week.png
> >>> IO Wait also reaches 100%~ when the thread count jumps. So the server
> >>> load jumps to 40+, and apache dies as well. 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.
> >>> Does anyone have any ideas why this happens? As you can see, the
> >>> server is fine sometimes, but during large traffic hours, it randomly
> >>> spikes.
> >>> Any help is appreciated. Thanks!
> >>> Server specs:
> >>> Core 2 Duo E6550, 2gb ram
> >>> my.cnf
> >>> [mysqld]
> >>> datadir=/var/lib/mysql
> >>> socket=/var/lib/mysql/mysql.sock
> >>> max_connections=500
> >>> safe-show-database
> >>> query-cache-type=1
> >>> query_cache_limit=2M
> >>> query-cache-size=32M
> >>> ft_min_word_len=2
> >>> concurrent_insert=2
> >>> myisam_sort_buffer_size=64M
> >>> key_buffer_size=384M
> >>> max_allowed_packet=16M
> >>> table_cache=1848
> >>> thread_cache_size=8
> >>> thread_concurrency=4
> >>> low_priority_updates=1
> >>> long_query_time=5
> >>> log-slow-queries=/home/mysql/slow-query
> >>> #log-queries-not-using-indexes
> >>> max_heap_table_size=300M
> >>> tmp_table_size=300M
> >>> sort_buffer_size=4M
> >>> read_buffer_size=4M
> >>> join_buffer_size=2M
> >>> read_rnd_buffer_size=4M
> >>> wait_timeout = 120
> >> The could be a lot of things. I suspect the most likely is locks. What
> >> kind of tables are you using, and what requests are being made to MySQL
> >> at the time? Anything unusual, like is an administrator updating
> >> something at the time or a backup being run?
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================
>
> > All tables are in Myisam format. I'm the only administrator of the
> > site and there weren't any updates/back-ups being run.
>
> > Lock Wait ratio = 1 : 60
>
> > Server averages around 200 queries per sec. I don't notice any unusual
> > items or queries being run at the time of the issue. It seems to only
> > happen when there is high traffic on the site though (usually Weekends/
> > Monday).
>
> > Thanks for the help!
>
> OK, unfortunately, MyISAM doesn't have much to help tell if, for
> instance, a table is locked. But my initial guess is that something is
> locking one or more tables, and others are backing up behind it. Not
> necessarily the case, mind you. But it often is.
>
> Have you tried enabling the slow query log and see what it shows? This
> might help.
>
> How big are your tables, and how many rows in them? Have you looked at
> the tuning parameters - buffer sizes, etc.?
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

I have a table with 3,500,000 rows (highly accessed/updated/inserted)
and another with 6,500,000 rows with high selects/inserts. Slow query
log only updates on random times with high traffic. It's never the
same query twice and the query it shows is always indexed correctly.

I've looked at numerous sites that provide information on tuning
my.cnf. I've tuned it to the best of my knowledge too with that
information.

Thanks.

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/

Garrett wrote:
> On May 19, 7:09 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Garrett wrote:
>>> On May 19, 5:45 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> Garrett 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. Here's a screenshot of the past week's mysql thread
>>>>> activity.
>>>>> http://xinil.net/images/mysql/localh...reads-week.png
>>>>> Notice the random 200+ spikes?
>>>>> 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.
>>>>> http://xinil.net/images/mysql/localh...eries-week.png
>>>>> IO Wait also reaches 100%~ when the thread count jumps. So the server
>>>>> load jumps to 40+, and apache dies as well. 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.
>>>>> Does anyone have any ideas why this happens? As you can see, the
>>>>> server is fine sometimes, but during large traffic hours, it randomly
>>>>> spikes.
>>>>> Any help is appreciated. Thanks!
>>>>> Server specs:
>>>>> Core 2 Duo E6550, 2gb ram
>>>>> my.cnf
>>>>> [mysqld]
>>>>> datadir=/var/lib/mysql
>>>>> socket=/var/lib/mysql/mysql.sock
>>>>> max_connections=500
>>>>> safe-show-database
>>>>> query-cache-type=1
>>>>> query_cache_limit=2M
>>>>> query-cache-size=32M
>>>>> ft_min_word_len=2
>>>>> concurrent_insert=2
>>>>> myisam_sort_buffer_size=64M
>>>>> key_buffer_size=384M
>>>>> max_allowed_packet=16M
>>>>> table_cache=1848
>>>>> thread_cache_size=8
>>>>> thread_concurrency=4
>>>>> low_priority_updates=1
>>>>> long_query_time=5
>>>>> log-slow-queries=/home/mysql/slow-query
>>>>> #log-queries-not-using-indexes
>>>>> max_heap_table_size=300M
>>>>> tmp_table_size=300M
>>>>> sort_buffer_size=4M
>>>>> read_buffer_size=4M
>>>>> join_buffer_size=2M
>>>>> read_rnd_buffer_size=4M
>>>>> wait_timeout = 120
>>>> The could be a lot of things. I suspect the most likely is locks. What
>>>> kind of tables are you using, and what requests are being made to MySQL
>>>> at the time? Anything unusual, like is an administrator updating
>>>> something at the time or a backup being run?
>>>> --
>>>> ==================
>>>> Remove the "x" from my email address
>>>> Jerry Stuckle
>>>> JDS Computer Training Corp.
>>>> jstuck...@attglobal.net
>>>> ==================
>>> All tables are in Myisam format. I'm the only administrator of the
>>> site and there weren't any updates/back-ups being run.
>>> Lock Wait ratio = 1 : 60
>>> Server averages around 200 queries per sec. I don't notice any unusual
>>> items or queries being run at the time of the issue. It seems to only
>>> happen when there is high traffic on the site though (usually Weekends/
>>> Monday).
>>> Thanks for the help!
>> OK, unfortunately, MyISAM doesn't have much to help tell if, for
>> instance, a table is locked. But my initial guess is that something is
>> locking one or more tables, and others are backing up behind it. Not
>> necessarily the case, mind you. But it often is.
>>
>> Have you tried enabling the slow query log and see what it shows? This
>> might help.
>>
>> How big are your tables, and how many rows in them? Have you looked at
>> the tuning parameters - buffer sizes, etc.?
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> I have a table with 3,500,000 rows (highly accessed/updated/inserted)
> and another with 6,500,000 rows with high selects/inserts. Slow query
> log only updates on random times with high traffic. It's never the
> same query twice and the query it shows is always indexed correctly.
>
> I've looked at numerous sites that provide information on tuning
> my.cnf. I've tuned it to the best of my knowledge too with that
> information.
>
> Thanks.

I see Axel has stepped in with some very good suggestions. I'd
recommend you follow them first to see where it gets you.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================