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

On Tuesday, July 23, 2013 12:02 AM Greg Smith wrote:
> The v3 patch applies perfectly here now.  Attached is a spreadsheet
> with test results from two platforms, a Mac laptop and a Linux server.
> I used systems with high disk speed because that seemed like a worst
> case for this improvement.  The actual improvement for shrinking WAL
> should be even better on a system with slower disks.

You are absolutely right.
To mimic it on our system, by configuring RAMFS for database, it shows similar results.
> There are enough problems with the "hundred tiny fields" results that I
> think this not quite ready for commit yet.  More comments on that
> below.
>   This seems close though, close enough that I am planning to follow up
> to see if the slow disk results are better.

Thanks for going extra mile to try for slower disks.

> Reviewing the wal-update-testsuite.sh test program, I think the only
> case missing that would be useful to add is "ten tiny fields, one
> changed".  I think that one is interesting to highlight because it's
> what benchmark programs like pgbench do very often.
> The GUC added by the program looks like this:
>
> postgres=# show wal_update_compression_ratio ;
>   wal_update_compression_ratio
> ------------------------------
>   25
>
> Is possible to add a setting here that disables the feature altogether?

Yes, it can be done in below 2 ways:
1. Provide a new configuration parameter (wal_update_compression) to turn on/off this feature.
2. At table level user can be given option to configure

>   That always makes it easier to consider a commit, knowing people can
> roll back the change if it makes performance worse.  That would make
> performance testing easier too.  wal-update-testsuit.sh takes as long
> as
> 13 minutes, it's long enough that I'd like the easier to automate
> comparison GUC disabling adds.  If that's not practical to do given the
> intrusiveness of the code, it's not really necessary.  I haven't looked
> at the change enough to be sure how hard this is.
>
> On the Mac, the only case that seems to have a slowdown now is "hundred
> tiny fields, half nulled".  It would be nice to understand just what is
> going on with that one.  I got some ugly results in "two short fields,
> no change" too, along with a couple of other weird results, but I think
> those were testing procedure issues that can be ignored.  The pgbench
> throttle work I did recently highlights that I can't really make a Mac
> quiet/consistent for benchmarking very well.  Note that I ran all of
> the Mac tests with assertions on, to try and catch platform specific
> bugs.
> The Linux ones used the default build parameters.
>
> On Linux "hundred tiny fields, half nulled" was also by far the worst
> performing one, with a >30% increase in duration despite the 14% drop
> in WAL.  Exactly what's going on there really needs to be investigated
> before this seems safe to commit.  All of the "hundred tiny fields"
> cases seem pretty bad on Linux, with the rest of them running about a
> 11% duration increase.

The main benefit of this patch is to reduce WAL for improving time in I/O,
But I think for faster I/O systems, the calculation to reduce WAL has overhead.
I will check how to optimize that calculation, but I think this feature should be consider
with configuration knob as it can improve many cases.

With Regards,
Amit Kapila.