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

On Mon, Mar 3, 2014 at 7:57 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 02/16/2014 01:51 PM, Amit Kapila wrote:
>>
>> On Wed, Feb 5, 2014 at 5:29 PM, Heikki Linnakangas
>> <hlinnakangas@vmware.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.56587505340576one short and one long field, no change |     575351216 | 9.97713398933411one short and
onelong field, no change |     573501848 | 9.76377606391907hundred tiny fields, all changed        |     364894056 |
13.3053929805756hundredtiny fields, all changed        |     364891536 | 13.3533811569214hundred tiny fields, all
changed       |     364889264 | 13.3041989803314hundred tiny fields, half changed       |     365411920 |
14.1831648349762hundredtiny fields, half changed       |     365918216 | 13.6393811702728hundred tiny fields, half
changed      |     366456552 | 13.6420011520386hundred tiny fields, half nulled        |     300705288 |
12.8859741687775hundredtiny fields, half nulled        |     301665624 | 12.6988201141357hundred tiny fields, half
nulled       |     300700504 | 13.35361003875739 short and 1 long, short changed       |     396983080 |
8.836713075637829short and 1 long, short changed       |     396987976 | 9.237692117691049 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.69434094429016one short and one long field, no change |     156277352 | 6.70855903625488one short and
onelong field, no change |     156280040 | 6.70657396316528hundred tiny fields, all changed        |     364895152 |
13.6677348613739hundredtiny fields, all changed        |     364892256 | 12.7107839584351hundred tiny fields, all
changed       |     364890424 | 13.7760601043701hundred tiny fields, half changed       |     365970360 |
13.1902158260345hundredtiny fields, half changed       |     364895120 | 13.5730090141296hundred tiny fields, half
changed      |     367031168 | 13.7023210525513hundred tiny fields, half nulled        |     204418576 |
12.1997199058533hundredtiny fields, half nulled        |     204422880 | 11.4583330154419hundred tiny fields, half
nulled       |     204417464 | 12.02289700508129 short and 1 long, short changed       |     220466016 |
8.148435115814219short and 1 long, short changed       |     220471168 | 8.037127971649179 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