Re: Performance Improvement by reducing WAL for Update Operation

On Wednesday, January 09, 2013 4:57 PM Simon Riggs wrote:
> On 9 January 2013 08:05, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
>
> > Update patch contains handling of below Comments
>
> Thanks
>
>
> > Test results with modified pgbench (1800 record size) on the latest
> patch:
> >
> > -Patch- -tps(at)-c1- -WAL(at)-c1- -tps(at)-c2- -
> WAL(at)-c2-
> > Head 831 4.17 GB 1416 7.13
> GB
> > WAL modification 846 2.36 GB 1712 3.31
> GB
> >
> > -Patch- -tps(at)-c4- -WAL(at)-c4- -tps(at)-c8- -
> WAL(at)-c8-
> > Head 2196 11.01 GB 2758 13.88
> GB
> > WAL modification 3295 5.87 GB 5472 9.02
> GB
>
> And test results on normal pgbench?

configuration:

shared_buffers = 4GB
wal_buffers = 16MB
checkpoint_segments = 256
checkpoint_interval = 15min
autovacuum = off
server_encoding = SQL_ASCII
client_encoding = UTF8
lc_collate = C
lc_ctype = C

init:

pgbench -s 75 -i -F 80

run:

pgbench -T 600

Test results with original pgbench (synccommit off) on the latest patch:

-Patch- -tps(at)-c1- -WAL(at)-c1- -tps(at)-c2- -WAL(at)-c2-
Head 1459 1.40 GB 2491 1.70 GB
WAL modification 1558 1.38 GB 2441 1.59 GB

-Patch- -tps(at)-c4- -WAL(at)-c4- -tps(at)-c8- -WAL(at)-c8-
Head 5139 2.49 GB 10651 4.72 GB
WAL modification 5224 2.28 GB 11329 3.96 GB

Test results with original pgbench (synccommit on) on the latest patch:

-Patch- -tps(at)-c1- -WAL(at)-c1- -tps(at)-c2- -WAL(at)-c2-
Head 146 0.45 GB 167 0.49 GB
WAL modification 144 0.44 GB 166 0.49 GB

-Patch- -tps(at)-c4- -WAL(at)-c4- -tps(at)-c8- -WAL(at)-c8-
Head 325 0.77 GB 603 1.03 GB
WAL modification 321 0.76 GB 604 1.01 GB

The results are similar as noted by Kyotaro-San. The WAL size is reduced
even for original pgbench.
There is slight performance dip in some of the cases for original pgbench.

With Regards,
Amit Kapila.