Re: WAL format and API changes (9.5)

On Tue, Nov 11, 2014 at 2:15 PM, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
wrote:z
>
> On 11/11/2014 09:39 AM, Michael Paquier wrote:
>>
>> On Tue, Nov 11, 2014 at 4:29 AM, Heikki Linnakangas <
hlinnakangas(at)vmware(dot)com
>>>
>>> wrote:
>>
>>
>>> Attached is a new version. It's rebased on current git master, including
>>> BRIN. I've also fixed the laundry list of small things you reported, as
>>> well as a bunch of bugs I uncovered during my own testing.
>>
>>
>> This patch needs a small rebase, it has been broken by a590f266 that
fixed
>> WAL replay for brin indexes:
>> patching file src/backend/access/brin/brin_xlog.c
>> Hunk #2 FAILED at 42.
>> Hunk #3 FAILED at 91.
>> This will facilitate testing as well.
>
>
> Here's a rebased patch. No other changes.
>

I have done some performance testing of this patch using attached
script and data is as below:

Performance Data
----------------------------
IBM POWER-8 24 cores, 192 hardware threads
RAM = 492GB
checkpoint_segments=300
checkpoint_timeout =30min
full_page_writes = off

HEAD (commit - ae667f7) -

testname | wal_generated | duration
-----------------------------------------+---------------+------------------
two short fields, no change | 398510104 | 11.993057012558
two short fields, no change | 396984168 | 10.3508098125458
two short fields, no change | 396983624 | 10.4196150302887
two short fields, one changed | 437106016 | 10.650365114212
two short fields, one changed | 437102456 | 10.756795167923
two short fields, one changed | 437103000 | 10.6824090480804
two short fields, both changed | 438131520 | 11.1027519702911
two short fields, both changed | 437112248 | 11.0681071281433
two short fields, both changed | 437107168 | 11.0370399951935
one short and one long field, no change | 76044176 | 2.90529608726501
one short and one long field, no change | 76047432 | 2.86844515800476
one short and one long field, no change | 76042664 | 2.84641098976135
ten tiny fields, all changed | 478210904 | 13.1221458911896
ten tiny fields, all changed | 477244448 | 12.8017349243164
ten tiny fields, all changed | 477217832 | 12.9907081127167
hundred tiny fields, all changed | 180353400 | 6.03354096412659
hundred tiny fields, all changed | 180349536 | 5.99893379211426
hundred tiny fields, all changed | 180350064 | 6.00634717941284
hundred tiny fields, half changed | 180348480 | 6.03162407875061
hundred tiny fields, half changed | 180348440 | 6.08004903793335
hundred tiny fields, half changed | 180349056 | 6.03126788139343
hundred tiny fields, half nulled | 101369936 | 5.43424606323242
hundred tiny fields, half nulled | 101660160 | 5.06207084655762
hundred tiny fields, half nulled | 100119184 | 5.51814889907837
9 short and 1 long, short changed | 108142168 | 3.26260495185852
9 short and 1 long, short changed | 108138144 | 3.20250797271729
9 short and 1 long, short changed | 109761240 | 3.21728992462158
(27 rows)

Patch-

testname | wal_generated | duration
-----------------------------------------+---------------+------------------
two short fields, no change | 398692848 | 11.2538840770721
two short fields, no change | 396988648 | 11.4972231388092
two short fields, no change | 396988416 | 11.0026741027832
two short fields, one changed | 437104840 | 11.1911199092865
two short fields, one changed | 437103832 | 11.1138079166412
two short fields, one changed | 437101872 | 11.3141660690308
two short fields, both changed | 437133648 | 11.5836050510406
two short fields, both changed | 437106640 | 11.5675358772278
two short fields, both changed | 437104944 | 11.6147150993347
one short and one long field, no change | 77226464 | 3.01720094680786
one short and one long field, no change | 77645248 | 2.9660210609436
one short and one long field, no change | 76045256 | 3.03326010704041
ten tiny fields, all changed | 477396616 | 13.3963651657104
ten tiny fields, all changed | 477219840 | 13.3838939666748
ten tiny fields, all changed | 477223960 | 13.5736708641052
hundred tiny fields, all changed | 180742136 | 5.87386584281921
hundred tiny fields, all changed | 180398320 | 6.12072706222534
hundred tiny fields, all changed | 180354080 | 6.16246104240417
hundred tiny fields, half changed | 180351456 | 6.17317008972168
hundred tiny fields, half changed | 180348096 | 6.13790798187256
hundred tiny fields, half changed | 183369688 | 6.19142913818359
hundred tiny fields, half nulled | 100111888 | 5.55706000328064
hundred tiny fields, half nulled | 100111952 | 5.55145716667175
hundred tiny fields, half nulled | 100119424 | 5.53378510475159
9 short and 1 long, short changed | 116821904 | 3.42408394813538
9 short and 1 long, short changed | 116160920 | 3.37269878387451
9 short and 1 long, short changed | 116160552 | 3.40163683891296
(27 rows)

It seems to me that there is a regression of (4 ~ 8%) for small records,
refer two short fields tests.

2.
Compilation errors on windows

1>E:\WorkSpace\PostgreSQL\master\postgresql\src\include\access/nbtree.h(296):
error C2016: C requires that a struct or union has at least one member
1>E:\WorkSpace\PostgreSQL\master\postgresql\src\include\access/nbtree.h(303):
error C2016: C requires that a struct or union has at least one member
1> nbtree.c

They are due to following structure declarations.
typedef struct xl_btree_split_left
{
/*
* In the _L variants, next is the new item.
* (In the _R variants, the new item is one of the right page's tuples.)
*
* If level > 0, an IndexTuple representing the HIKEY of the left page
* follows. We don't need this on leaf pages, because it's the same as
* the leftmost key in
the new right page.
*/
} xl_btree_split_left;

typedef struct xl_btree_split_right
{
/*
* Last are the right page's tuples in the form used by _bt_restore_page.
*/
} xl_btree_split_right;

3.
Patch is failed to apply against Head:
patching file src/backend/access/gin/ginfast.c
Hunk #1 succeeded at 108 (offset 2 lines).
Hunk #2 FAILED at 215.
Hunk #3 succeeded at 276 (offset 3 lines).
Hunk #4 succeeded at 311 (offset 3 lines).
Hunk #5 succeeded at 324 (offset 3 lines).
Hunk #6 succeeded at 339 (offset 3 lines).
Hunk #7 succeeded at 348 (offset 3 lines).
Hunk #8 succeeded at 379 (offset 3 lines).
Hunk #9 succeeded at 396 (offset 3 lines).
Hunk #10 succeeded at 523 (offset 5 lines).
Hunk #11 succeeded at 550 (offset 5 lines).
Hunk #12 succeeded at 587 (offset 5 lines).
1 out of 12 hunks FAILED -- saving rejects to file
src/backend/access/gin/ginfast.c.rej

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