Re: Performance Improvement by reducing WAL for Update Operation - Mailing list pgsql-hackers

From Amit Kapila
Subject Re: Performance Improvement by reducing WAL for Update Operation
Date
Msg-id 006c01cdf002$726bf150$5743d3f0$@kapila@huawei.com
Whole thread Raw
In response to Re: Performance Improvement by reducing WAL for Update Operation  (Simon Riggs <simon@2ndQuadrant.com>)
List pgsql-hackers
On Friday, January 11, 2013 6:18 PM Simon Riggs wrote:
> On 11 January 2013 12:30, Amit Kapila <amit.kapila@huawei.com> wrote:
> 
> >> Is this just one run? Can we see 3 runs please?
> >
> >   This average of 3 runs.
> 
> The results are so variable its almost impossible to draw any
> conclusions at all. I think if we did harder stats on those we'd get
> nothing.
> 
> Can you do something to bring that in? Or just do more tests to get a
> better view?

To be honest, I have tried this set of 3 readings 2 times and there is
similar fluctuation for sync commit =off
What I can do is early next week, 
a. I can run this test for 10 times to see the results.
b. run the tests for record length-256 instead of 128

However I think my results of sync commit = on is matching with Kyotaro-San.

Please suggest if you have anything in mind?

This is for sync mode= off, if see the result on sync mode= on, it is
comparatively consistent. 
I think for sync commit = off, there is always fluctuation in results. 
The sync mode= on, results are as below:
-Patch-             -tps@-c1-     -WAL@-c1-      -tps@-c2-      -WAL@-c2- Head-1              149          0.46 GB
 160           0.48
 
GB Head-2              145          0.45 GB        180           0.52
GB Head-3              144          0.45 GB        161           0.48
GB
 WAL modification-1    142          0.44 GB        161           0.48 GB WAL modification-2    146          1.45 GB
  162           0.48 GB WAL modification-3    144          1.44 GB        175           0.51 GB
 
-Patch-             -tps@-c4-     -WAL@-c4-      -tps@-c8-      -WAL@-c8- Head-1              325          0.77 GB
 602           1.03
 
GB Head-2              328          0.77 GB        606           1.03
GB Head-3              323          0.77 GB        603           1.03
GB
 WAL modification-1    324          0.76 GB        604           1.01 GB WAL modification-2    322          0.76 GB
  604           1.01 GB WAL modification-3    317          0.75 GB        604           1.01 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), sometimes the 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.
> >

With Regards,
Amit Kapila.




pgsql-hackers by date:

Previous
From: Simon Riggs
Date:
Subject: Re: Performance Improvement by reducing WAL for Update Operation
Next
From: Amit Kapila
Date:
Subject: Re: Performance Improvement by reducing WAL for Update Operation