Content-Type: multipart/mixed; boundary="------------ItU0fiAY5tJpPes7DHCRBCKe"
Message-ID: <683c128a-5961-9b7b-ad68-0ea850e62363@enterprisedb.com>
Date: Wed, 22 Dec 2021 19:49:15 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.2.0
Subject: Re: sequences vs. synchronous replication
Content-Language: en-US
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <712cad46-a9c8-1389-aef8-faf0203c9be9@enterprisedb.com>
 <1452362.1639803168@sss.pgh.pa.us>
 <38a6a779-ee8d-de3c-fbd7-709285be88fa@enterprisedb.com>
 <7869761b-fdc7-6f7d-dc95-9ba8315a8ecf@enterprisedb.com>
 <1637476.1639862832@sss.pgh.pa.us>
 <8e24a470-b092-3c9f-0614-acd9df3f724f@enterprisedb.com>
 <f4dd81e9-fc9e-3b71-0a8a-4a5ae345331d@enterprisedb.com>
 <a5303964-717e-9cb0-52cf-7a4b7d16adc8@enterprisedb.com>
 <8231183d-1077-5394-1ffa-0c9d8c3905b0@enterprisedb.com>
 <2683973.1640048519@sss.pgh.pa.us>
 <d5d61510-eaec-469f-b9b2-0c37e28d3577@enterprisedb.com>
In-Reply-To: <d5d61510-eaec-469f-b9b2-0c37e28d3577@enterprisedb.com>
Archived-At: 
 <https://www.postgresql.org/message-id/683c128a-5961-9b7b-ad68-0ea850e62363%40enterprisedb.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------ItU0fiAY5tJpPes7DHCRBCKe
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


On 12/21/21 03:49, Tomas Vondra wrote:
> On 12/21/21 02:01, Tom Lane wrote:
>> Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
>>> OK, I did a quick test with two very simple benchmarks - simple select
>>> from a sequence, and 'pgbench -N' on scale 1. Benchmark was on current
>>> master, patched means SEQ_LOG_VALS was set to 1.
>>
>> But ... pgbench -N doesn't use sequences at all, does it?
>>
>> Probably inserts into a table with a serial column would constitute a
>> plausible real-world case.
>>
> 
> D'oh! For some reason I thought pgbench has a sequence on the history 
> table, but clearly I was mistaken. There's another thinko, because after 
> inspecting pg_waldump output I realized "SEQ_LOG_VALS 1" actually logs 
> only every 2nd increment. So it should be "SEQ_LOG_VALS 0".
> 
> So I repeated the test fixing SEQ_LOG_VALS, and doing the pgbench with a 
> table like this:
> 
>    create table test (a serial, b int);
> 
> and a script doing
> 
>    insert into test (b) values (1);
> 
> The results look like this:
> 
> 1) select nextval('s');
> 
>       clients          1         4
>      ------------------------------
>       master       39533    124998
>       patched       3748      9114
>      ------------------------------
>       diff          -91%      -93%
> 
> 
> 2) insert into test (b) values (1);
> 
>       clients          1         4
>      ------------------------------
>       master        3718      9188
>       patched       3698      9209
>      ------------------------------
>       diff            0%        0%
> 
> So the nextval() results are a bit worse, due to not caching 1/2 the 
> nextval calls. The -90% is roughly expected, due to generating about 32x 
> more WAL (and having to wait for commit).
> 
> But results for the more realistic insert workload are about the same as 
> before (i.e. no measurable difference). Also kinda expected, because 
> those transactions have to wait for WAL anyway.
> 

Attached is a patch tweaking WAL logging - in wal_level=minimal we do 
the same thing as now, in higher levels we log every sequence fetch.

After thinking about this a bit more, I think even the nextval workload 
is not such a big issue, because we can set cache for the sequences. 
Until now this had fairly limited impact, but it can significantly 
reduce the performance drop caused by WAL-logging every sequence fetch.

I've repeated the nextval test on a different machine (the one I used 
before is busy with something else), and the results look like this:

1) 1 client

     cache          1         32        128
     --------------------------------------
     master     13975      14425      19886
     patched      886       7900      18397
     --------------------------------------
     diff        -94%       -45%        -7%

4) 4 clients

     cache             1         32        128
     -----------------------------------------
     master         8338      12849      18248
     patched         331       8124      18983
     -----------------------------------------
     diff           -96%       -37%         4%

So I think this makes it acceptable / manageable. Of course, this means 
the values are much less monotonous (across backends), but I don't think 
we really promised that. And I doubt anyone is really using sequences 
like this (just nextval) in performance critical use cases.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--------------ItU0fiAY5tJpPes7DHCRBCKe
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-WAL-log-individual-sequence-fetches-20211222.patch"
Content-Disposition: attachment;
 filename="0001-WAL-log-individual-sequence-fetches-20211222.patch"
Content-Transfer-Encoding: base64

RnJvbSBlZWFhN2NiMzZjNjlhZjA0OGYwMzIxZTQ4ODM4NjRlYmUyNTQyNDI5IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBUb21hcyBWb25kcmEgPHRvbWFzLnZvbmRyYUBwb3N0
Z3Jlc3FsLm9yZz4KRGF0ZTogV2VkLCAyMiBEZWMgMjAyMSAwMzoxODo0NiArMDEwMApTdWJq
ZWN0OiBbUEFUQ0ggMS82XSBXQUwtbG9nIGluZGl2aWR1YWwgc2VxdWVuY2UgZmV0Y2hlcwoK
LS0tCiBzcmMvYmFja2VuZC9jb21tYW5kcy9zZXF1ZW5jZS5jIHwgMTggKysrKysrKysrKysr
KysrKy0tCiAxIGZpbGUgY2hhbmdlZCwgMTYgaW5zZXJ0aW9ucygrKSwgMiBkZWxldGlvbnMo
LSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9jb21tYW5kcy9zZXF1ZW5jZS5jIGIvc3Jj
L2JhY2tlbmQvY29tbWFuZHMvc2VxdWVuY2UuYwppbmRleCA3MmJmZGMwN2E0Li4wZjMwOWQw
YTRlIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9jb21tYW5kcy9zZXF1ZW5jZS5jCisrKyBi
L3NyYy9iYWNrZW5kL2NvbW1hbmRzL3NlcXVlbmNlLmMKQEAgLTUyLDYgKzUyLDkgQEAKICAq
IFdlIGRvbid0IHdhbnQgdG8gbG9nIGVhY2ggZmV0Y2hpbmcgb2YgYSB2YWx1ZSBmcm9tIGEg
c2VxdWVuY2UsCiAgKiBzbyB3ZSBwcmUtbG9nIGEgZmV3IGZldGNoZXMgaW4gYWR2YW5jZS4g
SW4gdGhlIGV2ZW50IG9mCiAgKiBjcmFzaCB3ZSBjYW4gbG9zZSAoc2tpcCBvdmVyKSBhcyBt
YW55IHZhbHVlcyBhcyB3ZSBwcmUtbG9nZ2VkLgorICoKKyAqIFdlIG9ubHkgcHJlLWxvZyBm
ZXRjaGVzIGluIHdhbF9sZXZlbD1taW5pbWFsLiBGb3IgaGlnaGVyIGxldmVscyB3ZQorICog
V0FMLWxvZyBldmVyeSBpbmRpdmlkdWFsIHNlcXVlbmNlIGluY3JlbWVudCwgYXMgaWYgdGhp
cyB3YXMgMC4KICAqLwogI2RlZmluZSBTRVFfTE9HX1ZBTFMJMzIKIApAQCAtNjY2LDExICs2
NjksMTggQEAgbmV4dHZhbF9pbnRlcm5hbChPaWQgcmVsaWQsIGJvb2wgY2hlY2tfcGVybWlz
c2lvbnMpCiAJICogV0FMIHJlY29yZCB0byBiZSB3cml0dGVuIGFueXdheSwgZWxzZSByZXBs
YXkgc3RhcnRpbmcgZnJvbSB0aGUKIAkgKiBjaGVja3BvaW50IHdvdWxkIGZhaWwgdG8gYWR2
YW5jZSB0aGUgc2VxdWVuY2UgcGFzdCB0aGUgbG9nZ2VkIHZhbHVlcy4KIAkgKiBJbiB0aGlz
IGNhc2Ugd2UgbWF5IGFzIHdlbGwgZmV0Y2ggZXh0cmEgdmFsdWVzLgorCSAqCisJICogV2Ug
b25seSBwcmUtbG9nIGZldGNoZXMgaW4gd2FsX2xldmVsPW1pbmltYWwuIEZvciBoaWdoZXIg
bGV2ZWxzIHdlCisJICogV0FMLWxvZyBldmVyeSBpbmRpdmlkdWFsIHNlcXVlbmNlIGluY3Jl
bWVudC4KIAkgKi8KIAlpZiAobG9nIDwgZmV0Y2ggfHwgIXNlcS0+aXNfY2FsbGVkKQogCXsK
IAkJLyogZm9yY2VkIGxvZyB0byBzYXRpc2Z5IGxvY2FsIGRlbWFuZCBmb3IgdmFsdWVzICov
Ci0JCWZldGNoID0gbG9nID0gZmV0Y2ggKyBTRVFfTE9HX1ZBTFM7CisJCWlmIChYTG9nSXNO
ZWVkZWQoKSkKKwkJCWZldGNoID0gbG9nID0gZmV0Y2g7CisJCWVsc2UKKwkJCWZldGNoID0g
bG9nID0gZmV0Y2ggKyBTRVFfTE9HX1ZBTFM7CisKIAkJbG9naXQgPSB0cnVlOwogCX0KIAll
bHNlCkBAIC02ODAsNyArNjkwLDExIEBAIG5leHR2YWxfaW50ZXJuYWwoT2lkIHJlbGlkLCBi
b29sIGNoZWNrX3Blcm1pc3Npb25zKQogCQlpZiAoUGFnZUdldExTTihwYWdlKSA8PSByZWRv
cHRyKQogCQl7CiAJCQkvKiBsYXN0IHVwZGF0ZSBvZiBzZXEgd2FzIGJlZm9yZSBjaGVja3Bv
aW50ICovCi0JCQlmZXRjaCA9IGxvZyA9IGZldGNoICsgU0VRX0xPR19WQUxTOworCQkJaWYg
KFhMb2dJc05lZWRlZCgpKQorCQkJCWZldGNoID0gbG9nID0gZmV0Y2g7CisJCQllbHNlCisJ
CQkJZmV0Y2ggPSBsb2cgPSBmZXRjaCArIFNFUV9MT0dfVkFMUzsKKwogCQkJbG9naXQgPSB0
cnVlOwogCQl9CiAJfQotLSAKMi4zMS4xCgo=
--------------ItU0fiAY5tJpPes7DHCRBCKe--