Re: Performance Improvement by reducing WAL for Update Operation

On Thu, Jan 16, 2014 at 12:07 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> Okay, got your point.
> Another minor thing is that in latest patch which I have sent yesterday,
> I have modified it such that while formation of chunks if there is a data
> at end of string which doesn't have special pattern and is less than max
> chunk size, we also consider that as a chunk. The reason of doing this
> was that let us say if we have 104 bytes string which contains no special
> bit pattern, then it will just have one 64 byte chunk and will leave the
> remaining bytes, which might miss the chance of doing compression for
> that data.

I ran Heikki's test suit on latest master and latest master plus
pgrb_delta_encoding_v4.patch on a PPC64 machine, but the results
didn't look too good. The only tests where the WAL volume changed by
more than half a percent were the "one short and one long field, no
change" test, where it dropped by 17%, but at the expense of an
increase in duration of 38%; and the "hundred tiny fields, half
nulled" test, where it dropped by 2% without a change in runtime.
Unfortunately, some of the tests where WAL didn't change significantly
took a runtime hit - in particular, "hundred tiny fields, half
changed" slowed down by 10% and "hundred tiny fields, all changed" by
8%. I've attached the full results in OpenOffice format.

Profiling the "one short and one long field, no change" test turns up
the following:

51.38% postgres pgrb_delta_encode
23.58% postgres XLogInsert
2.54% postgres heap_update
1.09% postgres LWLockRelease
0.90% postgres LWLockAcquire
0.89% postgres palloc0
0.88% postgres log_heap_update
0.84% postgres HeapTupleSatisfiesMVCC
0.75% postgres ExecModifyTable
0.73% postgres hash_search_with_hash_value

Yipes. That's a lot more than I remember this costing before. And I
don't understand why I'm seeing such a large time hit on this test
where you actually saw a significant time *reduction*. One
possibility is that you may have been running with a default
checkpoint_segments value or one that's low enough to force
checkpointing activity during the test. I ran with
checkpoint_segments=300.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company