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>
Cc: David Steele <david(at)pgmasters(dot)net>, a(dot)lubennikova(at)postgrespro(dot)ru, Michael Paquier <michael(at)paquier(dot)xyz>, Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Reduce amount of WAL generated by CREATE INDEX for gist, gin and sp-gist
Date: 2019-04-02 05:58:37
Message-ID: e8371fe3-aefe-aa91-e87f-1602f9984fed@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 25/03/2019 15:21, Heikki Linnakangas wrote:
> I had another quick look.
>
> I still think using the "generic xlog AM" for this is a wrong level of
> abstraction, and we should use the XLOG_FPI records for this directly.
> We can extend XLOG_FPI so that it can store multiple pages in a single
> record, if it doesn't already handle it.
>
> Another counter-point to using the generic xlog record is that you're
> currently doing unnecessary two memcpy's of all pages in the index, in
> GenericXLogRegisterBuffer() and GenericXLogFinish(). That's not free.
>
> I guess the generic_log_relation() function can stay where it is, but it
> should use XLogRegisterBuffer() and XLogInsert() directly.

Patch set v.3 uses XLOG_FPI records directly.

As a benchmark I use the script (test.sql in attachment) which show WAL
size increment during index build. In the table below you can see the
influence of the patch on WAL growth.

Results
=======
AM | master | patch |
GIN | 347 MB | 66 MB |
GiST | 157 MB | 43 MB |
SP-GiST | 119 MB | 38 MB |

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

Attachment Content-Type Size
v3_0001-Relation-into-WAL-function.patch text/x-patch 2.8 KB
v3_0002-GIN-Optimal-WAL-Usage.patch text/x-patch 10.2 KB
v3_0003-GiST-Optimal-WAL-Usage.patch text/x-patch 11.0 KB
v3_0004-SP-GiST-Optimal-WAL-Usage.patch text/x-patch 6.3 KB
test.sql application/sql 2.2 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Haribabu Kommi 2019-04-02 06:11:07 Re: Pluggable Storage - Andres's take
Previous Message Amit Langote 2019-04-02 05:50:59 Re: speeding up planning with partitions