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(dot)kapila(at)huawei(dot)com> wrote:
>
> > Test results with original pgbench (synccommit off) on the latest
> patch:
> >
> >
> > -Patch- -tps(at)-c1- -WAL(at)-c1- -tps(at)-c2- -
> WAL(at)-c2-
> > Head 1459 1.40 GB 2491 1.70
> GB
> > WAL modification 1558 1.38 GB 2441 1.59
> GB
> >
> >
> > -Patch- -tps(at)-c4- -WAL(at)-c4- -tps(at)-c8- -
> WAL(at)-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(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.