Re: Performance Improvement by reducing WAL for Update Operation

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.