Re: Background writer configuration - Mailing list pgsql-performance

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.Grittner@wicourts.gov> wrote:

> >>> On Thu, Mar 16, 2006 at 12:15 pm, in message
> <20060316211523.73343cee.eugrid@fpm.kubsu.ru>, Evgeny Gridasov
> <eugrid@fpm.kubsu.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