We had a big increase in load, iowait, and disk i/o on a dedicated database host the other night.
Looking at the sar logs, the problem shows itself in a big increase in pgpgout/s, which I believe is postgres paging out parts of itself to disk?
02:15:01 AM pgpgin/s pgpgout/s fault/s majflt/s pgfree/s pgscank/s pgscand/s pgsteal/s %vmeff
02:25:01 AM 49.63 163.83 836.80 0.00 467.69 4.80 0.00 4.80 100.00
02:35:01 AM 44.85 230.29 1248.68 0.00 677.18 16.21 0.00 16.21 100.00
02:45:01 AM 47.67 190.82 1059.58 0.00 517.72 5.33 0.00 5.33 100.00
02:55:01 AM 59.00 175.58 986.87 0.00 514.08 18.13 0.00 18.13 100.00
03:05:01 AM 24.67 849.77 1382.60 0.00 1267.94 37.35 0.00 37.35 100.00
03:15:01 AM 28.67 1701.67 717.88 0.00 1231.48 0.00 0.00 0.00 0.00
03:25:02 AM 42.64 21342.02 4086.19 0.04 9701.70 415.92 0.00 414.44 99.64
03:35:01 AM 35.60 28290.69 4305.38 0.10 12906.73 623.89 0.00 615.85 98.71
03:45:01 AM 36.94 31119.30 3675.34 0.01 12456.54 527.55 0.00 521.61 98.87
03:55:01 AM 42.77 29020.72 3458.96 0.01 12165.57 557.57 0.00 553.10 99.20
Average: 41.25 11306.39 2175.59 0.02 5189.70 220.62 0.00 218.63 99.10
However, there isn't a corresponding increase in pages *in*, so if postgres is writing portions of itself out to disk, they can't be very important. And there's no swapping going on:
02:15:01 AM pswpin/s pswpout/s
02:25:01 AM 0.00 0.00
02:35:01 AM 0.00 0.00
02:45:01 AM 0.00 0.00
02:55:01 AM 0.00 0.00
03:05:01 AM 0.00 0.00
03:15:01 AM 0.00 0.00
03:25:02 AM 0.00 0.00
03:35:01 AM 0.00 0.00
03:45:01 AM 0.00 0.00
03:55:01 AM 0.00 0.00
Average: 0.00 0.00
Can anybody help me understand what these statistics are suggesting, what's actually going on on this box/in postgresql? What is it writing to disk, and why? Is it just writing out new/changed rows, or what?
Kevin M. Goess
Software Engineer
Berkeley Electronic Press
kgoess@xxxxxxxxxxx
510-665-1200 x179
www.bepress.com
bepress: sustainable scholarly publishing
