Re: Performance Improvement by reducing WAL for Update Operation

On 11 January 2013 12:30, Amit Kapila <amit(dot)kapila(at)huawei(dot)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(at)-c1- -WAL(at)-c1- -tps(at)-c2- -WAL(at)-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(at)-c4- -WAL(at)-c4- -tps(at)-c8- -WAL(at)-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