Re: Performance Improvement by reducing WAL for Update Operation

On Mon, Mar 3, 2014 at 7:57 PM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> On 02/16/2014 01:51 PM, Amit Kapila wrote:
>>
>> On Wed, Feb 5, 2014 at 5:29 PM, Heikki Linnakangas
>> <hlinnakangas(at)vmware(dot)com> wrote:
>
> Thanks. I have to agree with Robert though that using the pglz encoding when
> we're just checking for a common prefix/suffix is a pretty crappy way of
> going about it [1].
>
> As the patch stands, it includes the NULL bitmap when checking for a common
> prefix. That's probably not a good idea, because it defeats the prefix
> detection in a the common case that you update a field from NULL to not-NULL
> or vice versa.
>
> Attached is a rewritten version, which does the prefix/suffix tests directly
> in heapam.c, and adds the prefix/suffix lengths directly as fields in the
> WAL record. If you could take one more look at this version, to check if
> I've missed anything.

I had verified the patch and found few minor points:
1.
+extern bool heap_delta_encode(TupleDesc tupleDesc, HeapTuple oldtup,
+ HeapTuple newtup, char *encdata, uint32 *enclen);
+extern void heap_delta_decode (char *encdata, uint32 enclen, HeapTuple oldtup,
+ HeapTuple newtup);

Declaration for above functions are not required now.

2.
+ * later, but will not cause any problem because this function is used only to
+ * identify whether EWT is required for update.
+ */
+bool
+XLogCheckBufferNeedsBackup(Buffer buffer)

Here, I think we can change the comment to avoid word
EWT (Encoded WAL tuple), as now we changed compression
mechanism and it's not used anywhere else.

One Question:
+ rdata[1].data = (char *) &xlrec;
Earlier it seems to store record hearder as first segment rdata[0],
whats the reason of changing it?

I have verified the patch by doing crash recovery for below scenario's
and it worked fine:
a. no change in old and new tuple
b. all changed in new tuple
c. half changed (update half of the values to NULLS) in new tuple
d. only prefix same in new tuple
e. only suffix same in new tuple
f. prefix-suffix same, other columns values changed in new tuple.

Performance Data
----------------------------

Non-Default settings

autovacuum = off
checkpoitnt_segments = 256
checkpoint_timeout =15min
full_page_writes = off

Unpatched

testname | wal_generated | duration
-----------------------------------------+---------------+------------------
one short and one long field, no change | 573506704 | 9.56587505340576
one short and one long field, no change | 575351216 | 9.97713398933411
one short and one long field, no change | 573501848 | 9.76377606391907
hundred tiny fields, all changed | 364894056 | 13.3053929805756
hundred tiny fields, all changed | 364891536 | 13.3533811569214
hundred tiny fields, all changed | 364889264 | 13.3041989803314
hundred tiny fields, half changed | 365411920 | 14.1831648349762
hundred tiny fields, half changed | 365918216 | 13.6393811702728
hundred tiny fields, half changed | 366456552 | 13.6420011520386
hundred tiny fields, half nulled | 300705288 | 12.8859741687775
hundred tiny fields, half nulled | 301665624 | 12.6988201141357
hundred tiny fields, half nulled | 300700504 | 13.3536100387573
9 short and 1 long, short changed | 396983080 | 8.83671307563782
9 short and 1 long, short changed | 396987976 | 9.23769211769104
9 short and 1 long, short changed | 396984080 | 9.45178604125977

wal-update-prefix-suffix-5.patch

testname | wal_generated | duration
-----------------------------------------+---------------+------------------
one short and one long field, no change | 156278832 | 6.69434094429016
one short and one long field, no change | 156277352 | 6.70855903625488
one short and one long field, no change | 156280040 | 6.70657396316528
hundred tiny fields, all changed | 364895152 | 13.6677348613739
hundred tiny fields, all changed | 364892256 | 12.7107839584351
hundred tiny fields, all changed | 364890424 | 13.7760601043701
hundred tiny fields, half changed | 365970360 | 13.1902158260345
hundred tiny fields, half changed | 364895120 | 13.5730090141296
hundred tiny fields, half changed | 367031168 | 13.7023210525513
hundred tiny fields, half nulled | 204418576 | 12.1997199058533
hundred tiny fields, half nulled | 204422880 | 11.4583330154419
hundred tiny fields, half nulled | 204417464 | 12.0228970050812
9 short and 1 long, short changed | 220466016 | 8.14843511581421
9 short and 1 long, short changed | 220471168 | 8.03712797164917
9 short and 1 long, short changed | 220464464 | 8.55907511711121
(15 rows)

Conclusion is that patch shows good WAL reduction and performance
improvement for favourable cases without CPU overhead for non-favourable
cases.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com