Re: Reduce amount of WAL generated by CREATE INDEX for gist, gin and sp-gist

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Reduce amount of WAL generated by CREATE INDEX for gist, gin and sp-gist
Date: 2018-07-31 04:35:56
Message-ID: 18f0b868-6888-27eb-22cc-982709c3d0f7@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

With the consent of Anastasia I will improving this patch further.
Attachment contains next version of the patch set.

11.07.2018 00:03, Heikki Linnakangas пишет:
> On 28/02/18 18:03, Anastasia Lubennikova wrote:

>> Implementation is based on generic_xlog.
>
> Why? I think we should just add a log_relation() function in
> xloginsert.c directly, alongside log_newpage_buffer().
>
I have some arguments to stay this functionality at generic_xlog module:
1. xloginsert.c functions work on low level of abstraction, use buffers
and pages.
2. Code size using generic_xlog service functions looks more compact and
safe.
> This makes the assumption that all the pages in these indexes used the
> standard page layout. I think that's a valid assumption, but needs at
> least a comment on that. And perhaps an Assert, to check that
> pd_lower/upper look sane.
Done
>
> As a further optimization, would it be a win to WAL-log multiple pages
> in each record?
In this version of the patch we use simple optimization: pack
XLR_NORMAL_MAX_BLOCK_ID blocks pieces into each WAL-record.
>
> This leaves the XLOG_*_CREATE_INDEX WAL record types unused, BTW.
>
Done
> - Heikki
>

Benchmarks:
-----------

Test: pgbench -f gin-WAL-test.sql -t 5:
---------------------------------------
master:
Latency average: 27696.299 ms
WAL size: 2.66 GB

patched
Latency average: 22812.103 ms
WAL size: 1.23 GB

Test: pgbench -f gist-WAL-test.sql -t 5:
----------------------------------------
master:
Latency average: 19928.284 ms
WAL size: 1.25 GB

patched
Latency average: 18175.064 ms
WAL size: 0.63 GB

Test: pgbench -f spgist-WAL-test.sql -t 5:
------------------------------------------
master:
Latency average: 11529.384 ms
WAL size: 1.07 GB

patched
Latency average: 9330.828 ms
WAL size: 0.6 GB

--
Andrey Lepikhov
Postgres Professional
https://postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
0001-Relation-into-WAL-function.patch text/x-patch 3.1 KB
0002-GIN-Optimal-WAL-Usage.patch text/x-patch 10.5 KB
0003-GIST-Optimal-WAL-Usage.patch text/x-patch 11.2 KB
0004-SPGIST-Optimal-WAL-Usage.patch text/x-patch 6.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2018-07-31 04:39:07 Re: Temporary tables prevent autovacuum, leading to XID wraparound
Previous Message Amit Kapila 2018-07-31 03:30:00 Re: Explain buffers wrong counter with parallel plans