Mysql and High CPU IO Wait

I've the following problem. I'm running a MySQL server 5.1.37 on Ubuntu 9.10 x86 on Amazon. For data store I use EBS volume formatted for ext3.

From time to time the following problem occurs. MySQL start processing about queries 10~20 queries and processing of these takes more than 300sec (These SQL are using filesort). During that time no other transaction could be executed.

I've checked CPU Wait and here what is shows:

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
24   9  66   0   0   0|   0    76k| 258k  989k|   0     0 |5970  3014 
 23   1  75   0   0   1|4096B   28k| 229k 1536k|   0     0 |3249  2308 
 19   6  74   0   0   0|4096B  316k| 209k  609k|   0     0 |4943  2542 
 19  17  62   0   0   2|4096B   36k| 230k  718k|   0     0 |5482  2520 
 21  19  57   2   0   2|  16k  800k| 271k  860k|   0     0 |6549  2923 
 23  27  44   5   0   1| 480k   40k| 288k  979k|   0     0 |4140  2682 
 12   0  86   1   0   0| 256k   48k| 237k  771k|   0     0 |3404  2627 
 22   1  75   0   0   1|8192B   60k| 285k  908k|   0     0 |4009  2786 
 54  21  19   3   0   2|4096B 3384k| 287k 1556k|   0     0 |3962  2284 
 49  24  24   1   0   2|4096B  928k| 285k 2795k|   0     0 |3257  2005 
 61  19  17   2   0   2|8192B   36k| 215k  577k|   0     0 |3246  1922 
 40  49   8   0   0   3|   0开发者_如何学Python    40k| 312k  905k|   0     0 |3282  1732 
 56  23  20   1   0   1|4096B  188k| 247k  897k|   0     0 |3102  2238 
 39  19  27  16   0   0|4096B   77M| 265k  819k|   0     0 |5147  3075 
 35  35  12  16   0   1|4096B   56M| 259k 1052k|   0     0 |4656  2739 
 36  27   8  28   0   1|4096B   59M| 259k 1139k|   0     0 |5549  2821 
 27  13  36  23   0   1|4096B   64M| 251k 1218k|   0     0 |4207  2540
usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
 26   4  13  57   0   1|4096B   66M| 275k  681k|   0     0 |5205  3291 
 22   6  27  43   0   1|4096B   52M| 237k  684k|   0     0 |4906  2602 
 14   3  24  58   0   0|4096B   46M| 278k 1058k|   0     0 |6448  3687 
 19   3  34  43   0   2|  32k   51M| 233k  685k|   0     0 |5006  2652 
 27   3   9  61   0   1|4096B   51M| 294k  800k|   0     0 |4428  2384 
 17   3  30  50   0   1|4096B   42M| 243k  699k|   0     0 |5334  2830 
 40  18   0  42   0   0|   0    89M| 247k  840k|   0     0 |4698  2977 
 31  18  11  39   0   2|4096B   42M| 238k 1269k|   0     0 |4270  2474 
 17   3  13  66   0   0|4096B   49M| 260k  773k|   0     0 |5153  3100 
 21   2  14  62   0   1|8192B   46M| 269k  948k|   0     0 |6762  3581 
 24   2  35  39   0   0|4096B   39M| 256k  777k|   0     0 |5313  2761 
 15   2  10  72   0   1|4096B   49M| 237k  797k|   0     0 |5312  3018 
 19   4  22  55   0   0|8192B   47M| 307k 1034k|   0     0 |5508  3278 
 41   3  15  40   0   1|8192B   47M| 293k  727k|   0     0 |5630  3303 
 16   2  26  54   0   1|4096B   56M| 282k 1750k|   0     0 |5016  2781 
 17   3  12  67   0   2|8192B   43M| 238k  824k|   0     0 |5751  3147 
 14  11  50  24   0   1|4096B   39M| 247k 1105k|   0     0 |4454  2389 
 41   3  20  35   0   1|   0    58M| 152k  481k|   0     0 |4009  2958 
 52   2   4  41   0   1|4096B   59M| 211k  621k|   0     0 |5449  2846 
 31   2   0  66   0   1|   0    52M| 255k 1476k|   0     0 |5167  2693 
 36   2  24  36   0   2|  12k   49M| 311k  888k|   0     0 |4537  2563 
 47   7   2  43   0   2|4096B   50M| 231k  750k|   0     0 |4083  2165 
 40   4   6  50   0   0|4096B   86M| 211k  819k|   0     0 |4768  2875 
 29   5   2  65   0   0|   0    79M| 180k  580k|   0     0 |4271  4461 
 40   3   0  57   0   0|4096B   58M| 238k 1489k|   0     0 |4366  4480 
 27   8  26  38   0   1|4096B   33M| 301k  984k|   0     0 |4439  2838 
 11   2   9  78   0   1|4096B   24M| 230k  646k|   0     0 |4894  4504 
 10   3  14  72   0   0|4096B   21M| 183k  549k|   0     0 |4066  3952 
 14   3  27  57   0   0|   0    64M| 147k  339k|   0     0 |3479  2860 
 10   2  19  69   0   0|4096B   51M| 112k  452k|   0     0 |2847  2300 
  9   4  18  69   0   0|4096B   37M| 131k  443k|   0     0 |2923  2004 
  4   2  49  45   0   0|4096B   31M|  97k  230k|   0     0 |2163  1545 
  1   2  73  24   0   0|   0    33M|  49k  130k|   0     0 |1425   824 
  1   0  71  28   0   0|   0    26M|  36k   86k|   0     0 |1426   910 
  0   0  55  45   0   0|   0    32M|  32k  148k|   0     0 |1334   695 
  4   0  64  32   0   0|   0    39M|  14k   39k|   0     0 |1262   406 
  0   2  38  60   0   0|   0    44M|  13k   44k|   0     0 |1136   382 
  1   1  82  16   0   0|   0    47M|  25k   70k|   0     0 |1228   584 
  1   3  69  27   0   0|4096B   46M|  23k   60k|   0     0 |1576   599 
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
  3   1  70  27   0   0|4096B   43M|  22k   54k|   0     0 |1065   574 
  1   1  33  65   0   0|   0    46M|6124B   17k|   0     0 |1190   345 
  1   1  49  50   0   0|   0    47M|  11k   22k|   0     0 |1258   444 
  2  11  23  64   0   0|  56k   58M|9749B   47k|   0     0 |1143   379 
  1   1  64  34   0   0|   0    51M| 198B 5914B|   0     0 |1048   234 
  0   1  63  36   0   0|   0    58M| 662B 1278B|   0     0 | 976   454 
  1   0  81  18   0   0|   0    50M| 426B 6022B|   0     0 |1304   600 
  0   1  70  29   0   0|   0    43M| 132B 1868B|   0     0 |1150   210 
  1   1  79  19   0   0|   0    51M| 198B 5914B|   0     0 | 986   246 
  1   2  30  66   0   0|   0    54M| 246B  420B|   0     0 |1150   288 
  1   0  49  50   0   0|   0    55M| 659B 6752B|   0     0 |1038   280 
  1   2  37  60   0   0|   0    47M|  66B  354B|   0     0 |1191   227 
  0   0  80  19   0   0|   0    43M| 561B 6044B|   0     0 |1129   256 
  5  13  44  38   0   0|   0    49M|1558B   19k|   0     0 |1225   243 
  3   6  48  42   0   0|   0    52M| 705B 6022B|   0     0 | 948   327  

What could cause such a behavior? Are there any techniques to avoid this?

---

You're showing a high amount of IO_WAIT status on the CPU (65%). It's possible that you're just pulling too much out of the disks. Try running iostat and seeing what the disk activity is like (namely transactions per second).

However, you mention 10 to 20 queries. Are these queries doing any writing at all? Are they using a transaction? If the answer to either is yes, then you're locking because of the transaction lock in MySQL. If that's the case, your problem is that you need to either figure a way to remove the transaction, or make the queries much more efficient.

A good test would be to create another database on the server. Then run your queries and try to query against the different database. If it works, it's due to transaction locks. If it doesn't, it's likely the disk or some other leak from the VM...

---

The biggest suspect here is the performance of the EBS volumes, the CPUs may be waiting for I/Os to complete. The next question is what is causing the I/O requests.

This question might be better answered on ServerFault.

http://www.mysqlperformanceblog.com/2011/02/21/death-match-ebs-versus-ssd-price-performance-and-qos/

---

The IO performance of EBS is poor (I recently benchmarked EBS on a Small instance as being half as fast as my laptop's hard drive). However, you can improve it significantly by striping multiple EBS volumes into a software RAID configuration.

http://alestic.com/2009/06/ec2-ebs-raid

---

EBS comes with lot of its own limitations, if you are running your instance in the US Region, its better you switch to optimized EBS to make the IO faster. Even I was managing a self managed Mysql but later switched to RDS, which gives a lot better performance then EBS.