I've been struggling for quite some time with some MySQL performance issues. What's been really anoying is that they come and go. I started off with a Linode 96 running Debian, Apache, PHP and MySQL that were installed with all of their defaults using apt-get. Two particular websites that I'm running are made up of mostly PHP dynamically generated pages that draw from MySQL.

These sites ran well for a week or so and then MySQL would start to drag, and the load average would go up. My pages that would normally take 0-2 seconds to generate were now taking 30 or more seconds. At first I thought that this was due to increased hits and not enough memory but in fact the hit rate stayed fairly constant. I often found that just restarting MySQL and Apache would settle things down.

So to "fix" this I upgraded to a Linode 128 and then to a Linode 256. I'm still having the problem and it's worse now. Things were very smoother for about 10 days after moving to the Linode 256 and then early Friday morning MySQL became very slow again and hasn't let up. Unlike pervious times, where restarting MySQL or just waiting a few hours would fix things, nothing is helping and my MySQL based websites are unusable.

Anyone have any insights on what may be causing this? Does anyone else have any similar problems?

Thanks,

Geoff

When it's slow, what is the output of:

vmstat 1 (a few lines, look for swapping)

and

ssh [linodeusername]@host[yourhost#].linode.com io_status

-Chris

It's interesting that this problem started at almost exactly midnight Thursday night (Pacific time) and improved significantly at exactly 4 PM today. It's still taking 8-20 seconds for the page generation so there is still an issue.

io_status shows:
OK io_count=850802 io_rate=27 io_tokens=200000 token_refill=512 token_max=200000

vmstat 1 shows:

   procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 0  0  0   1068  12864  12044 135524   0   0    19    17  138    44  24  41  35
 1  0  0   1068  12728  12052 135652   0   0   132     0  242   101  26  35  38
 1  0  0   1068  12728  12052 135652   0   0     0     0  106    11  42  58   0
 1  0  0   1068  12728  12052 135652   0   0     0     0  105    11  35  65   0
 1  0  0   1068  12728  12052 135652   0   0     0     0  105    13  32  68   0
 1  0  0   1068  12724  12056 135652   0   0     0    91  195    14  33  67   0
 1  0  0   1068  12724  12056 135652   0   0     0     0  106    11  42  58   0
 0  0  0   1068  12712  12056 135664   0   0     0     0  107    25   2   6  92
 1  0  0   1068  12640  12056 135664   0   0     0     0  108    19   0   5  95
 0  0  0   1068  12452  12060 135664   0   0     0    89  198    38   0   0 100
 0  0  0   1068  12612  12064 135748   0   0    85    46  240    82   3   5  92
 1  0  0   1068  12544  12064 135824   0   0    60     0  177   157  11  30  60
 1  0  0   1068  12544  12064 135824   0   0     0     0  105    13  29  71   0
 1  0  0   1068  12544  12064 135824   0   0     0     0  103    12  25  75   0
 1  0  0   1068  12544  12064 135824   0   0     0     0  103    11  51  49   0
 1  0  0   1068  12540  12068 135824   0   0     0   130  234    16  40  60   0
 1  0  0   1068  12540  12068 135824   0   0     0     0  104    10  41  59   0
 0  0  0   1068  12540  12068 135824   0   0     0     0  107    25  18  20  62
 0  0  0   1068  12540  12068 135824   0   0     0     0  102    15   0   0 100
 0  0  0   1068  12540  12068 135824   0   0     0     0  102     9   0   0 100
 0  0  0   1068  12540  12068 135824   0   0     0    26  128    20   0   0 100
 0  0  0   1068  12540  12068 135824   0   0     0     0  102     9   0   0 100
 0  0  0   1068  12540  12068 135824   0   0     0     0  102    11   0   0 100

Also, CPU is running at close to 100% with 60%-75% in the system.