Re: Final background writer cleanup for 8.3 - Mailing list pgsql-hackers

>>> On Sun, Aug 26, 2007 at  7:35 PM, in message
<46D1D601.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:
>>>> On Sun, Aug 26, 2007 at  4:16 PM, in message
> <Pine.GSO.4.64.0708261637030.3811@westnet.com>, Greg Smith
> <gsmith@gregsmith.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
pageswas 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
theworst 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@ATHENA:~> vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----r  b   swpd   free   buff  cache   si
so   bi    bo   in    cs us sy id wa2  3     20 402248      0 10538028    0    0     0     1    1     2 21  4 55 192  4
   20 403116      0 10538028    0    0  5180   384 2233  9599 24  5 50 213  6     20 402868      0 10532888    0    0
4844  512 2841 14054 44  6 31 197 10     20 397908      0 10534944    0    0  6768   465 2674 11995 40  6 26 284 15
20398016      0 10534944    0    0  3344  4703 2297 10578 34  7 13 460 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 473 10     20 408176      0 10530832    0
  0 11380   553 3907 15463 67 13  5 154  4     20 405572      0 10535972    0    0  8708   981 2904 12051 26  7 34 331
5    20 403588      0 10535972    0    0  5924   464 2589 12194 26  5 45 234  7     20 410780      0 10529804    0    0
6284  1163 2674 11830 33  8 35 243 13     20 402596      0 10526720    0    0  2424  6598 2441 10332 40  7 11 427 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 499 17     20 404580      0 10527748    0
  0  7156  3560 3185 13203 55 11  3 321 11     20 406192      0 10531860    0    0  5112  3647 2758 11362 31  6 26 373
13    20 404464      0 10531860    0    0  4856  3426 2342 11077 24  5 35 362 13     20 403968      0 10530832    0
0 5308  4634 2762 15778 34  7 22 364 12     20 403472      0 10534944    0    0  2996  3766 2090  9331 20  4 34 420  5
  20 412648      0 10522608    0    0  2364  5187 1816  5194 18  5 56 224 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 307  7     20 402340      0 10530832    0
  0  7600   712 3511 16486 45  8 20 264  9     20 403704      0 10531860    0    0  7708   830 3133 16164 43 11 22 245
6    20 408416      0 10529804    0    0  6900   814 2703 10806 31  7 39 248  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 195  6     20 403580      0 10533916    0
  0  6724   623 2905 11937 37  7 34 223  7     20 400728      0 10529804    0    0  6924   712 2746 12085 35  7 37 210
7    20 408664      0 10526720    0    0  6536   344 2562 10555 27  6 44 245  1     20 407796      0 10527748    0    0
4628  1000 2653 13092 41  7 37 157  9     20 400480      0 10529804    0    0  3364   744 2326 11198 35  7 40 183  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 271  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
tellingPostgreSQL 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
wewere bombarded with user complaints. 
-Kevin