Re: Performance Improvement by reducing WAL for Update Operation

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?


>  -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), 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.
>



-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services