Re: Simple queries hang on "statistics"

[Jay Paroline]

We have 32GB of RAM on the box and 26GB allocated to the buffer pool.

I'm not sure what you consider to be a lot of IO, so here's the results of running iostat for a little. I've already skipped the first output since that's not useful:

iostat -dx 10

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 41.84 0.50 11.41 3.20 426.03 1.60 213.01 36.03 0.01 0.55 0.50 0.59
sdb 0.00 158.56 132.03 305.31 3483.48 3710.91 1741.74 1855.46 16.45 0.88 2.00 1.27 55.50

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 13.27 0.00 2.89 0.00 129.34 0.00 64.67 44.69 0.00 0.41 0.34 0.10
sdb 0.00 89.62 153.99 295.61 4068.66 3081.84 2034.33 1540.92 15.90 0.99 2.19 1.30 58.53

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 15.90 0.00 3.60 0.00 156.00 0.00 78.00 43.33 0.00 0.72 0.53 0.19
sdb 0.00 76.30 150.40 288.30 4155.20 2916.80 2077.60 1458.40 16.12 0.86 1.95 1.46 63.93

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 5.61 0.00 6.21 0.00 94.49 0.00 47.25 15.23 0.01 1.48 0.03 0.02
sdb 0.00 86.69 190.99 275.18 5519.92 2894.89 2759.96 1447.45 18.05 1.20 2.56 1.92 89.40

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 14.49 0.00 3.70 0.00 145.45 0.00 72.73 39.35 0.00 0.35 0.24 0.09
sdb 0.00 209.89 178.42 304.70 5201.20 4116.68 2600.60 2058.34 19.29 1.27 2.63 1.58 76.52

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 3.70 0.00 0.40 0.00 32.80 0.00 16.40 82.00 0.00 0.25 0.25 0.01
sdb 0.00 65.70 203.60 308.30 5731.20 2992.00 2865.60 1496.00 17.04 1.28 2.50 1.78 91.14

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 16.50 0.00 5.60 0.00 176.80 0.00 88.40 31.57 0.00 0.70 0.27 0.15
sdb 0.00 313.60 162.40 308.50 4452.80 4976.80 2226.40 2488.40 20.02 1.16 2.47 1.48 69.67

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 15.10 0.00 3.50 0.00 148.80 0.00 74.40 42.51 0.00 0.63 0.49 0.17
sdb 0.00 102.00 159.00 290.90 4454.40 3143.20 2227.20 1571.60 16.89 0.99 2.20 1.55 69.59