Re: Performance Improvement by reducing WAL for Update Operation

On Friday, January 11, 2013 4:28 PM Simon Riggs wrote:
> On 11 January 2013 10:40, Amit Kapila <amit.kapila@huawei.com> wrote:
> 
> > 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
> 
> > There is slight performance dip in some of the cases for original
> pgbench.
> 
> Is this just one run? Can we see 3 runs please?
 This average of 3 runs.
-Patch-               -tps@-c1-     -WAL@-c1-      -tps@-c2-      -WAL@-c2- Head-1                 1648          1.47
GB       2491           1.69 GB Head-2                 1538          1.43 GB        2529           1.72 GB Head-3
         1192          1.31 GB        2453           1.70 GB
 
 AvgHead                1459          1.40 GB        2491           1.70 GB
 WAL modification-1      1618          1.40 GB        2351           1.56
GB WAL modification-2      1623          1.40 GB        2411           1.59
GB WAL modification-3      1435          1.34 GB        2562           1.61
GB
 WAL modification-Avg    1558          1.38 GB        2441           1.59
GB


-Patch-               -tps@-c4-     -WAL@-c4-      -tps@-c8-      -WAL@-c8- Head-1                 5285          2.53
GB       11858           5.43
 
GB Head-2                 5105          2.47 GB        10724           4.98
GB Head-3                 5029          2.46 GB        9372            3.75
GB
 AvgHead                5139          2.49 GB        10651           4.72
GB
 WAL modification-1      5117          2.26 GB        12092           4.42
GB WAL modification-2      5142          2.26 GB        9965            3.48
GB WAL modification-3      5413          2.33 GB        11930           3.99
GB
 WAL modification-Avg    5224          2.28 GB        11329           3.96
GB 


> Can we investigate the performance dip at c=2? Please consider following points for this dip: 1. For synchronous
commit= off, there is always slight variation in data. 2. The size of WAL is reduced. 3. For small rows (128 bytes),
sometimesthe performance difference
 
created by this algorithm doesn't help much,     as the size is not reduced significantly and there is equivalent
overhead for delta compression.     We can put check that this optimization should be applied if row length
is greater than some     threshold(128 bytes, 200 bytes), but I feel as performance dip is not
much and WAL reduction gain is also      there, so it should be okay without any check as well.

With Regards,
Amit Kapila.