>>> On Sun, Aug 26, 2007 at 7:35 PM, in message
<46D1D601(dot)EE98(dot)0025(dot)0(at)wicourts(dot)gov>, "Kevin Grittner"
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>>>> On Sun, Aug 26, 2007 at 4:16 PM, in message
> <Pine(dot)GSO(dot)4(dot)64(dot)0708261637030(dot)3811(at)westnet(dot)com>, Greg Smith
> <gsmith(at)gregsmith(dot)com> wrote:
> I'll try to get some more meaningful numbers tomorrow.
Well, I ran the query against the production web server 40 times, and the highest number I got for usagecount 5 dirty pages was in this sample:
usagecount | count | isdirty
------------+-------+---------
0 | 7358 | f
1 | 7428 | f
2 | 1938 | f
3 | 1311 | f
4 | 1066 | f
5 | 1097 | f
1 | 87 | t
2 | 62 | t
3 | 31 | t
4 | 11 | t
5 | 86 | t
| 5 |
(12 rows)
Most samples looked something like this:
usagecount | count | isdirty
------------+-------+---------
0 | 7981 | f
1 | 6584 | f
2 | 1975 | f
3 | 1063 | f
4 | 1366 | f
5 | 1294 | f
0 | 5 | t
1 | 83 | t
2 | 60 | t
3 | 19 | t
4 | 21 | t
5 | 28 | t
| 1 |
(13 rows)
The system can comfortably write out about 4,000 pages per second as long as the write cache doesn't get swamped, so in the worst case I caught it had 69 ms worth of work to do, if they were all physical writes (which, of course, is highly unlikely).
>From shortly afterwards, possibly of interest:
postgres(at)ATHENA:~> vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
2 3 20 402248 0 10538028 0 0 0 1 1 2 21 4 55 19
2 4 20 403116 0 10538028 0 0 5180 384 2233 9599 24 5 50 21
3 6 20 402868 0 10532888 0 0 4844 512 2841 14054 44 6 31 19
7 10 20 397908 0 10534944 0 0 6768 465 2674 11995 40 6 26 28
4 15 20 398016 0 10534944 0 0 3344 4703 2297 10578 34 7 13 46
0 22 20 405456 0 10534944 0 0 2464 4192 1785 6167 20 3 21 56
14 19 20 401852 0 10538028 0 0 3680 4704 2474 11779 29 5 12 54
17 13 20 401728 0 10532888 0 0 5504 1945 2554 21490 35 8 10 47
3 10 20 408176 0 10530832 0 0 11380 553 3907 15463 67 13 5 15
4 4 20 405572 0 10535972 0 0 8708 981 2904 12051 26 7 34 33
1 5 20 403588 0 10535972 0 0 5924 464 2589 12194 26 5 45 23
4 7 20 410780 0 10529804 0 0 6284 1163 2674 11830 33 8 35 24
3 13 20 402596 0 10526720 0 0 2424 6598 2441 10332 40 7 11 42
7 16 20 400736 0 10528776 0 0 3928 6784 2453 9852 26 6 26 42
19 14 20 405308 0 10524664 0 0 2272 4708 2208 8583 27 5 19 49
9 17 20 404580 0 10527748 0 0 7156 3560 3185 13203 55 11 3 32
1 11 20 406192 0 10531860 0 0 5112 3647 2758 11362 31 6 26 37
3 13 20 404464 0 10531860 0 0 4856 3426 2342 11077 24 5 35 36
2 13 20 403968 0 10530832 0 0 5308 4634 2762 15778 34 7 22 36
4 12 20 403472 0 10534944 0 0 2996 3766 2090 9331 20 4 34 42
0 5 20 412648 0 10522608 0 0 2364 5187 1816 5194 18 5 56 22
4 13 20 415376 0 10519524 0 0 2836 6172 1929 5075 25 6 26 43
27 16 20 413880 0 10522608 0 0 7892 2340 3325 19769 52 8 10 30
7 7 20 402340 0 10530832 0 0 7600 712 3511 16486 45 8 20 26
4 9 20 403704 0 10531860 0 0 7708 830 3133 16164 43 11 22 24
5 6 20 408416 0 10529804 0 0 6900 814 2703 10806 31 7 39 24
8 6 20 401844 0 10532888 0 0 6884 632 2993 13792 37 7 29 27
13 3 20 398868 0 10534944 0 0 7732 744 3443 14580 63 9 8 19
5 6 20 403580 0 10533916 0 0 6724 623 2905 11937 37 7 34 22
3 7 20 400728 0 10529804 0 0 6924 712 2746 12085 35 7 37 21
0 7 20 408664 0 10526720 0 0 6536 344 2562 10555 27 6 44 24
5 1 20 407796 0 10527748 0 0 4628 1000 2653 13092 41 7 37 15
7 9 20 400480 0 10529804 0 0 3364 744 2326 11198 35 7 40 18
3 4 20 406384 0 10531860 0 0 4044 904 2998 14055 60 9 16 14
18 5 20 397976 0 10525692 0 0 6000 671 3082 14058 55 10 15 20
11 6 20 410996 0 10528776 0 0 4828 3498 2768 13027 38 7 28 27
1 3 20 406416 0 10531860 0 0 4140 616 2496 11980 33 6 43 17
This box is a little beefier than the proposed test box, with 8 3 GHz Xeon MP CPUs and 12 GB of RAM. Other than telling PostgreSQL about the extra RAM in the effective cache size GUC, this box has the same postgresql.conf.
Other than cranking up the background writer settings this is the same box and configuration that stalled so badly that we were bombarded with user complaints.
-Kevin