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.kapila@huawei.com> wrote:
> 
> > Update patch contains handling of below Comments
> 
> Thanks
> 
> 
> > Test results with modified pgbench (1800 record size) on the latest
> patch:
> >
> > -Patch-             -tps@-c1-     -WAL@-c1-      -tps@-c2-      -
> WAL@-c2-
> > Head                831           4.17 GB        1416           7.13
> GB
> > WAL modification    846           2.36 GB        1712           3.31
> GB
> >
> > -Patch-             -tps@-c4-     -WAL@-c4-      -tps@-c8-      -
> WAL@-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@-c1-     -WAL@-c1-      -tps@-c2-      -WAL@-c2-
Head                1459          1.40 GB        2491           1.70 GB
WAL modification    1558          1.38 GB        2441           1.59 GB


-Patch-             -tps@-c4-     -WAL@-c4-      -tps@-c8-      -WAL@-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@-c1-     -WAL@-c1-      -tps@-c2-      -WAL@-c2-
Head                146           0.45 GB        167            0.49 GB
WAL modification    144           0.44 GB        166            0.49 GB


-Patch-             -tps@-c4-     -WAL@-c4-      -tps@-c8-      -WAL@-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.