Re: Performance Improvement by reducing WAL for Update Operation

On Sunday, March 03, 2013 8:19 PM Craig Ringer wrote:
> On 02/05/2013 11:53 PM, Amit Kapila wrote:
> >> Performance data for the patch is attached with this mail.
> >> Conclusions from the readings (these are same as my previous patch):
> >>
> >> 1. With orignal pgbench there is a max 7% WAL reduction with not
> much
> >> performance difference.
> >> 2. With 250 record pgbench there is a max wal reduction of 35% with
> not
> >> much performance difference.
> >> 3. With 500 and above record size in pgbench there is an improvement
> in
> >> the performance and wal reduction both.
> >>
> >> If the record size increases there is a gain in performance and wal
> >> size is reduced as well.
> >>
> >> Performance data for synchronous_commit = on is under progress, I
> shall
> >> post it once it is done.
> >> I am expecting it to be same as previous.
> > Please find the performance readings for synchronous_commit = on.
> >
> > Each run is taken for 20 min.
> >
> > Conclusions from the readings with synchronous commit on mode:
> >
> > 1. With orignal pgbench there is a max 2% WAL reduction with not much
> > performance difference.
> > 2. With 500 record pgbench there is a max wal reduction of 3% with
> not much
> > performance difference.
> > 3. With 1800 record size in pgbench there is both an improvement in
> the
> > performance (approx 3%) as well as wal reduction (44%).
> >
> > If the record size increases there is a very good reduction in WAL
> size.
>
> The stats look fairly sane. I'm a little concerned about the apparent
> trend of falling TPS in the patched vs original tests for the 1-client
> test as record size increases, but it's only 0.0%->0.2%->0.4%, and the
> 0.4% case made other config changes too. Nonetheless, it might be wise
> to check with really big records and see if the trend continues.

For bigger size (~2000) records, it goes into toast, for which we don't do
this optimization.
This optimization is mainly for medium size records.

With Regards,
Amit Kapila.