Re: Background writer configuration

Yesterday we recieved a new server 2xAMD64(2core x 2chips = 4 cores)
8GB RAM and RAID-1 (LSI megaraid)
I've maid some tests with pgbench (scaling 1000, database size ~ 16Gb)

First of all, I'd like to mention that it was strange to see that
the server performance degraded by 1-2% when we changed kernel/userland to x86_64
from default installed i386 userland/amd64 kernel. The operating system was Debian Linux,
filesystem ext3.

bg_writer_*_percent/maxpages setting did not dramatically increase performance,
but setting bg_writer_delay to values x10 original setting (2000-4000) increased
transaction rate by 4-7 times.
I've tried shared buffers 32768, 65536, performance was almost equal.

for all tests:
checkpoint_segments = 16
checkpoint_timeout = 900
shared_buffers=65536
wal_buffers=128:

bgwriter_delay = 200
bgwriter_lru_percent = 10.0
bgwriter_lru_maxpages = 100
bgwriter_all_percent = 5.0
bgwriter_all_maxpages = 50

result:
./pgbench -c 32 -t 500 -U postgres regression
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1000
number of clients: 32
number of transactions per client: 500
number of transactions actually processed: 16000/16000
tps = 112.740903 (including connections establishing)
tps = 112.814327 (excluding connections establishing)

(disk activity about 2-4mb/sec writing)

bgwriter_delay = 4000
bgwriter_lru_percent = 10.0
bgwriter_lru_maxpages = 100
bgwriter_all_percent = 5.0
bgwriter_all_maxpages = 50

result:
./pgbench -c 32 -t 500 -U postgres regression
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1000
number of clients: 32
number of transactions per client: 500
number of transactions actually processed: 16000/16000
tps = 508.637831 (including connections establishing)
tps = 510.107981 (excluding connections establishing)

(disk activity about 20-40 mb/sec writing)

Setting bgwriter_delay to higher values leads to slower postgresql shutdown time
(I see postgresql writer process writing to disk). Sometimes postgresql didn't
shutdown correctly (doesn't complete background writing ?).

I've found some settings with which system behaves strange:

./pgbench -c 32 -t 3000 -U postgres regression

vmstat 1:

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 25 528 14992 22884 7876736 0 0 457 383 77 83 1 0 94 5
0 7 632 14728 22892 7875780 0 88 4412 9456 1594 21623 9 5 8 78
1 19 796 16904 22928 7872712 0 16 3536 9053 1559 19717 9 4 12 75
0 4 872 14928 22936 7874208 0 36 3036 9092 1574 20874 9 4 2 85
0 24 912 16292 22964 7872068 0 44 3020 9316 1581 19922 9 4 9 78
0 1 912 17800 22980 7869876 0 0 2596 8700 1560 19926 9 4 4 84
4 23 996 18284 22996 7868292 32 0 3396 11048 1657 22802 11 5 3 81
0 22 960 14728 23020 7871448 52 0 3020 9648 1613 21641 9 4 5 82
0 28 1008 15440 23028 7869624 0 48 2992 10052 1608 21430 9 5 5 82
1 16 1088 17328 23044 7867196 0 0 2460 7884 1530 16536 8 3 9 79
0 23 1088 18440 23052 7865556 0 0 3256 10128 1635 22587 10 4 4 81
1 29 1076 14728 23076 7868604 0 0 2968 9860 1597 21518 10 5 7 79
1 24 1136 15952 23084 7866700 0 40 2696 8900 1560 19311 9 4 5 81
0 14 1208 17200 23112 7864736 0 16 2888 9508 1603 20634 10 4 6 80
0 21 1220 18520 23120 7862828 0 72 2816 9487 1572 19888 10 4 7 79
1 21 1220 14792 23144 7866000 0 0 2960 9536 1599 20331 9 5 5 81
1 24 1220 16392 23152 7864088 0 0 2860 8932 1583 19288 9 4 3 84
0 18 1276 18000 23168 7862048 0 0 2792 8592 1553 18843 9 4 9 78
1 17 1348 19144 23176 7860132 0 16 2840 9604 1583 20654 10 4 6 80
0 22 64 15112 23200 7864264 528 0 3280 8785 1582 19339 9 4 7 80
0 25 16 16008 23212 7862664 4 0 2764 8964 1605 18471 9 4 8 79
0 26 16 17544 23236 7860872 0 0 3008 9848 1590 20527 10 4 7 79
1 7 16 18704 23244 7858960 0 0 2756 8760 1564 19875 9 4 4 84
1 25 16 15120 23268 7861996 0 0 2768 8512 1550 18518 9 3 12 75
1 25 16 18076 23276 7859812 0 0 2484 8580 1536 18391 8 4 8 80
0 3 16 17832 23300 7862916 0 0 2888 8864 1586 21450 9 4 4 83
0 14 16 24280 23308 7866036 0 0 2816 9140 1537 20655 9 4 7 81
1 1 16 54452 23348 7867968 0 0 1808 6988 1440 14235 6 9 24 61
0 1 16 51988 23348 7868036 0 0 60 4180 1344 885 1 10 72 16
0 2 16 51988 23348 7868036 0 0 0 3560 1433 50 0 0 75 25
0 2 16 51988 23348 7868036 0 0 0 2848 1364 46 0 0 75 25
0 2 16 51988 23348 7868036 0 0 0 2560 1350 44 0 0 75 25
0 4 16 51996 23360 7868092 0 0 0 2603 1328 60 0 0 72 28
0 4 16 52060 23360 7868092 0 0 0 2304 1306 46 0 0 75 25
0 4 16 52140 23360 7868092 0 0 0 2080 1288 40 0 0 75 25
0 2 16 52140 23360 7868092 0 0 0 2552 1321 48 0 0 75 25
0 2 16 52220 23360 7868092 0 0 0 2560 1335 44 0 0 75 25
0 2 16 52220 23360 7868092 0 0 0 2560 1340 48 0 0 75 25
0 2 16 52284 23360 7868092 0 0 0 2560 1338 48 0 0 75 25
... continued

during the time with zero read io and write io about 2500 I see many hanging
postgresql processes executing UPDATE or COMMIT. This lasts for a minute or so,
after that I see the same IO which was during benchmark start.

What happens during this period?

On Thu, 16 Mar 2006 15:58:53 -0600
"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> >>> On Thu, Mar 16, 2006 at 12:15 pm, in message
> <20060316211523(dot)73343cee(dot)eugrid(at)fpm(dot)kubsu(dot)ru>, Evgeny Gridasov
> <eugrid(at)fpm(dot)kubsu(dot)ru> wrote:
> >
> > please, could you post other settings from your postgresql.conf?
>
> Everything in postgresql.conf which is not commented out:
>
> listen_addresses = '*' # what IP interface(s) to listen on;
> max_connections = 600 # note: increasing
> max_connections costs
> shared_buffers = 20000 # min 16 or max_connections*2,
> 8KB each
> work_mem = 10240 # min 64, size in KB
> max_fsm_pages = 1400000 # min max_fsm_relations*16, 6
> bytes each
> bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers
> scanned/round
> bgwriter_lru_maxpages = 200 # 0-1000 buffers max
> written/round
> bgwriter_all_percent = 10.0 # 0-100% of all buffers
> scanned/round
> bgwriter_all_maxpages = 600 # 0-1000 buffers max
> written/round
> full_page_writes = off # recover from partial page
> writes

--
Evgeny Gridasov
Software Engineer
I-Free, Russia