title: Mysql and High CPU IO Wait
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>I've checked CPU Wait and here what is shows:</p>
  
  <pre>
  ----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    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  
  </pre>
  
  <p>What could cause such a behavior? Are there any techniques to avoid this?</p>