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.