For the past few days we've been having a problem where MySQL slows down to a crawl and does not process any queries, they just build up according to the processlist. Nothing has changed code wise, a new text field was added to one of the tables recently but that is it. The only way to get MySQL back under control is to restart it, it goes back to normal operations after a restart. Load average goes over 60 when this happens, back to ~1 after MySQL is restarted.

During one of these slow downs I copied all the queries that were in the processlist and after restarting myself I ran each one, they all executed just fine under a second. All these queries have been running fine for weeks/months, it just started to happen few days ago.

This is a private server, we only host our own sites and no one else has access.


Box Specs:

Red Hat Linux Enterprise 4
Cpanel
2 x Dual Core Xenons
2G ram
2 x 10k SCSI in RAID1


MySQL 5.0.51a
DB size: 3.9 Gigs
All tables are MyISAM
Indexes are fine as far as I see


Here are some stats during the time it all goes crazy, yes high I/O


top - 09:23:29 up 3 days, 21:19, 4 users, load average: 62.88, 43.70, 28.22
Tasks: 389 total, 1 running, 386 sleeping, 1 stopped, 1 zombie
Cpu(s): 0.7% us, 1.9% sy, 0.0% ni, 35.1% id, 62.3% wa, 0.1% hi, 0.0% si
Mem: 2074640k total, 2060276k used, 14364k free, 2096k buffers
Swap: 1052248k total, 523692k used, 528556k free, 361412k cached


free
total used free shared buffers cached
Mem: 2074640 2058668 15972 0 2192 382912
-/+ buffers/cache: 1673564 401076
Swap: 1052248 587104 465144


vmstats

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
2 47 593000 14604 2876 373724 3 2 212 114 10 6 5 3 84 8
1 45 593000 16524 2808 369548 160 0 13147 659 2063 5832 1 3 4 92
0 86 593000 15948 2740 369124 4 0 13722 19 2085 5905 1 2 7 90
0 54 593000 16140 2708 371104 196 0 13509 37 2131 5513 1 1 0 98
1 44 593000 16332 2652 371104 0 0 17693 9 2328 6449 1 2 22 75
0 51 593000 17252 2696 368488 416 0 11631 188 2059 5745 2 2 26 71
0 48 592996 15972 2636 371920 0 0 15172 352 2192 5950 1 2 36 61
0 73 592996 16100 2668 372568 76 0 14344 7 2318 6810 1 2 24 72
0 71 592996 15700 2636 373112 0 0 15125 1 2201 6787 1 2 0 98
0 52 592996 15700 2676 371288 156 0 11924 209 2082 6235 2 2 1 95
0 50 592996 14420 2696 373724 32 0 18474 16 2242 6840 2 2 33 63
2 45 592996 15836 2676 373292 80 0 14353 364 2034 6124 1 2 22 76
1 47 592996 16028 2620 373216 24 0 17309 40 2184 6568 0 1 43 55
1 43 592996 14300 2592 374764 64 0 14724 33 2159 5790 1 2 52 45
0 47 592996 16796 2620 371304 212 0 16112 154 2061 6154 0 2 8 90
1 39 592996 25620 2716 373864 112 0 10517 8 1865 5253 3 2 13 82
1 38 592992 24460 2864 383400 200 0 10081 340 2018 5211 2 2 4 92
0 36 592992 16204 2968 395472 304 0 12297 76 2003 5139 0 1 50 48
0 36 592992 16844 3020 393396 0 0 12806 8 1876 5163 2 1 19 78
0 34 592992 16772 2992 392016 4 0 11871 656 1963 5520 1 1 14 84
1 35 592992 16340 3020 392828 32 0 10620 24 2054 5590 3 2 17 78
0 31 592992 16148 3024 391072 188 0 12775 77 2034 5340 2 1 25 72
0 33 592984 16980 3052 388796 156 0 11060 305 1876 4846 0 1 53 45
0 32 592984 17620 3084 389056 100 0 10774 589 1898 4696 1 4 44 51
0 30 592984 16020 3040 390724 32 0 13897 16 2115 5407 0 5 62 33
0 33 592984 16724 3032 389716 24 0 11373 693 2016 5621 1 3 56 41
0 31 592984 16724 3056 390016 84 0 8472 339 1781 4858 0 1 50 49
0 31 592984 16388 2960 390156 32 0 12019 28 2053 5588 0 1 56 43
0 31 592984 16516 2944 389940 20 0 15838 200 2053 5587 0 1 33 65
0 31 592980 16708 2836 391876 8 0 13551 40 1949 5358 1 2 39 59
0 34 592980 16580 2696 390988 4 0 15038 1 2222 5739 1 2 49 48
1 30 592980 17100 2676 389720 4 0 15062 159 2098 5204 1 2 58 39


mysqld process:

22840 mysql 16 0 5 48:23.42 16.6 552m 337m 4032 S mysqld


my.cnf

[mysqld]
skip-locking
skip-innodb
query_cache_limit=2M
query_cache_size=128M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=300
connect_timeout=10
thread_cache_size=80
key_buffer_size=256M
join_buffer_size = 1M
max_allowed_packet=1M
read_rnd_buffer_size = 8M
table_cache=512
record_buffer=1M
sort_buffer_size=4M
read_buffer_size=2M
max_connect_errors=10
myisam_sort_buffer_size=64M
log-bin
server-id=1
log-slow-queries = /var/log/mysql_slow.log
log_error = /var/log/mysql_error.log
long_query_time = 2
log_queries_not_using_indexes = 0
tmp_table_size =64M
ft_min_word_len = 3
thread_concurrency = 8

[mysqld_safe]
open_files_limit=8192

[mysql.server]
user=mysql

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
wait_timeout=300





I'm out of ideas at this point, I checked everything I could think of and I don't see anything out of the ordinary that pops out. Right now the only thing left is to move the database to a dedicated box.


Any info/help would be appreciated

I checked our SCSI controller, Dell PERC 5/i, and OpenManage says the firmware version is out of date. Not sure if that could have anything to do with this but that's the only thing that showed as far as the controller goes.

Did you recently install any scripts or something?

If something you are doing is causing high enough IO wait, it can cause the CPU to stall, which would prevent queries from being processes. You might want to check on what is causing such high disk IO times... how does the IO, and CPU wait times look in "vmstat 1"?

edit:
Just noticed you already pasted a vmstat, unfortunately the thread destroyed the spacing, so it is kind of difficult to tell. But Since your CPU wait time is fairly high(usually waiting on Disk IO to finish), I would suggest looking at what could be consuming a lot of Disk IO.

How are your DNS timings?