Content-Type: multipart/mixed; boundary="------------pGfQwPsGTktcHskuCifEpPx6"
Message-ID: <2f91cf01-3ce6-45c6-aa9d-72e4953264df@abcSQL.com>
Date: Thu, 4 Jan 2024 00:23:43 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
From: Cedric Villemain <Cedric.Villemain+pgsql@abcSQL.com>
Subject: Change prefetch and read strategies to use range in pg_prewarm ...
 and raise a question about posix_fadvise WILLNEED
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Language: en-US
Organization: Data Bene
Archived-At: <https://www.postgresql.org/message-id/2f91cf01-3ce6-45c6-aa9d-72e4953264df%40abcSQL.com>
Precedence: bulk

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

Hi,

I wonder what you think of making pg_prewarm use recent addition on 
smgrprefetch and readv ?


In order to try, I did it anyway in the attached patches. They contain 
no doc update, but I will proceed if it is of interest.

In summary:

1. The first one adds a new check on parameters (checking last block is 
indeed not before first block).
Consequence is an ERROR is raised instead of silently doing nothing.

2. The second one does implement smgrprefetch with range and loops by 
default per segment to still have a check for interrupts.

3. The third one provides smgrreadv instead of smgrread,  by default on 
a range of 8 buffers. I am absolutely unsure that I used readv correctly...

Q: posix_fadvise may not work exactly the way you think it does, or does 
it ?


In details, and for the question:

It's not so obvious that the "feature" is really required or wanted, 
depending on what are the expectations from user point of view.

The kernel decides on what to do with posix_fadvise calls, and how we 
pass parameters does impact the decision.
With the current situation where prefetch is done step by step, block by 
block, they are very probably most of the time all loaded even if those 
from the beginning of the relation can be discarded at the end of the 
prefetch.

However,  if instead you provide a real range, or the magic len=0 to 
posix_fadvise, then blocks are "more" loaded according to effective vm 
pressure (which is not the case on the previous example).
As a result only a small part of the relation might be loaded, and this 
is probably not what end-users expect despite being probably a good 
choice (you can still free cache beforehand to help the kernel).

An example, below I'm using vm_relation_cachestat() which provides linux 
cachestat output, and vm_relation_fadvise() to unload cache, and 
pg_prewarm for the demo:

# clear cache: (nr_cache is the number of file system pages in cache, 
not postgres blocks)

```
postgres=# select block_start, block_count, nr_pages, nr_cache from 
vm_relation_cachestat('foo',range:=1024*32);
block_start | block_count | nr_pages | nr_cache
-------------+-------------+----------+----------
           0 |       32768 |    65536 |        0
       32768 |       32768 |    65536 |        0
       65536 |       32768 |    65536 |        0
       98304 |       32768 |    65536 |        0
      131072 |        1672 |     3344 |        0
```

# load full relation with pg_prewarm (patched)

```
postgres=# select pg_prewarm('foo','prefetch');
pg_prewarm
------------
     132744
(1 row)
```

# Checking results:

```
postgres=# select block_start, block_count, nr_pages, nr_cache from 
vm_relation_cachestat('foo',range:=1024*32);
block_start | block_count | nr_pages | nr_cache
-------------+-------------+----------+----------
           0 |       32768 |    65536 |      320
       32768 |       32768 |    65536 |        0
       65536 |       32768 |    65536 |        0
       98304 |       32768 |    65536 |        0
      131072 |        1672 |     3344 |      320  <-- segment 1

```

# Load block by block and check:

```
postgres=# select from generate_series(0, 132743) g(n), lateral 
pg_prewarm('foo','prefetch', 'main', n, n);
postgres=# select block_start, block_count, nr_pages, nr_cache from 
vm_relation_cachestat('foo',range:=1024*32);
block_start | block_count | nr_pages | nr_cache
-------------+-------------+----------+----------
           0 |       32768 |    65536 |    65536
       32768 |       32768 |    65536 |    65536
       65536 |       32768 |    65536 |    65536
       98304 |       32768 |    65536 |    65536
      131072 |        1672 |     3344 |     3344

```

The duration of the last example is also really significant: full 
relation is 0.3ms and block by block is 1550ms!
You might think it's because of generate_series or whatever, but I have 
the exact same behavior with pgfincore.
I can compare loading and unloading duration for similar "async" work, 
here each call is from block 0 with len of 132744 and a range of 1 block 
(i.e. posix_fadvise on 8kB at a time).
So they have exactly the same number of operations doing DONTNEED or 
WILLNEED, but distinct duration on the first "load":

```

postgres=# select * from 
vm_relation_fadvise('foo','main',0,132744,1,'POSIX_FADV_DONTNEED');
vm_relation_fadvise
---------------------

(1 row)

Time: 25.202 ms
postgres=# select * from 
vm_relation_fadvise('foo','main',0,132744,1,'POSIX_FADV_WILLNEED');
vm_relation_fadvise
---------------------

(1 row)

Time: 1523.636 ms (00:01.524) <----- not free !
postgres=# select * from 
vm_relation_fadvise('foo','main',0,132744,1,'POSIX_FADV_WILLNEED');
vm_relation_fadvise
---------------------

(1 row)

Time: 24.967 ms
```

Thank you for your time reading this longer than expected email.

Comments ?


---
Cédric Villemain +33 (0)6 20 30 22 52
https://Data-Bene.io
PostgreSQL Expertise, Support, Training, R&D

--------------pGfQwPsGTktcHskuCifEpPx6
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-Check-last-block-greater-or-equal-to-first-block-in-.patch"
Content-Disposition: attachment;
 filename*0="0001-Check-last-block-greater-or-equal-to-first-block-in-.pa";
 filename*1="tch"
Content-Transfer-Encoding: base64

RnJvbSBlZWIyNGFjZDc4MmYxZGMyYWZmZjgwZmNjZmNlYzQ0MjUyMWI5NjIyIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiA9P1VURi04P3E/Qz1DMz1BOWRyaWM9MjBWaWxsZW1h
aW4/PSA8Q2VkcmljLlZpbGxlbWFpbkBEYXRhLUJlbmUuaW8+CkRhdGU6IFdlZCwgMyBKYW4g
MjAyNCAxOTowMzoyMiArMDEwMApTdWJqZWN0OiBbUEFUQ0ggMS8zXSBDaGVjayBsYXN0IGJs
b2NrIGdyZWF0ZXIgb3IgZXF1YWwgdG8gZmlyc3QgYmxvY2sgaW4KIHBnX3ByZXdhcm0KClRo
aXMgcHJldmVudHMgdXNlbGVzcyBjYWxsIGFuZCBpbmZvcm0gdXNlcnMgb2YgdW5leHBlY3Rl
ZCB2YWx1ZSBhcwpwYXJhbWV0ZXIuCi0tLQogY29udHJpYi9wZ19wcmV3YXJtL3BnX3ByZXdh
cm0uYyB8IDUgKysrLS0KIDEgZmlsZSBjaGFuZ2VkLCAzIGluc2VydGlvbnMoKyksIDIgZGVs
ZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvY29udHJpYi9wZ19wcmV3YXJtL3BnX3ByZXdhcm0u
YyBiL2NvbnRyaWIvcGdfcHJld2FybS9wZ19wcmV3YXJtLmMKaW5kZXggMDFmYzJjOGFkOS4u
MWRlMWIzOTQ4MiAxMDA2NDQKLS0tIGEvY29udHJpYi9wZ19wcmV3YXJtL3BnX3ByZXdhcm0u
YworKysgYi9jb250cmliL3BnX3ByZXdhcm0vcGdfcHJld2FybS5jCkBAIC0xMzMsMTAgKzEz
MywxMSBAQCBwZ19wcmV3YXJtKFBHX0ZVTkNUSU9OX0FSR1MpCiAJZWxzZQogCXsKIAkJbGFz
dF9ibG9jayA9IFBHX0dFVEFSR19JTlQ2NCg0KTsKLQkJaWYgKGxhc3RfYmxvY2sgPCAwIHx8
IGxhc3RfYmxvY2sgPj0gbmJsb2NrcykKKwkJaWYgKGxhc3RfYmxvY2sgPCBmaXJzdF9ibG9j
ayB8fCBsYXN0X2Jsb2NrID49IG5ibG9ja3MpCiAJCQllcmVwb3J0KEVSUk9SLAogCQkJCQko
ZXJyY29kZShFUlJDT0RFX0lOVkFMSURfUEFSQU1FVEVSX1ZBTFVFKSwKLQkJCQkJIGVycm1z
ZygiZW5kaW5nIGJsb2NrIG51bWJlciBtdXN0IGJlIGJldHdlZW4gMCBhbmQgJWxsZCIsCisJ
CQkJCSBlcnJtc2coImVuZGluZyBibG9jayBudW1iZXIgbXVzdCBiZSBiZXR3ZWVuICVsbGQg
YW5kICVsbGQiLAorCQkJCQkJCShsb25nIGxvbmcpIChmaXJzdF9ibG9jayksCiAJCQkJCQkJ
KGxvbmcgbG9uZykgKG5ibG9ja3MgLSAxKSkpKTsKIAl9CiAKLS0gCjIuMzkuMgoK
--------------pGfQwPsGTktcHskuCifEpPx6
Content-Type: text/x-patch; charset=UTF-8;
 name="0002-Allow-pg_prewarm-to-use-new-smgrprefetch-range.patch"
Content-Disposition: attachment;
 filename*0="0002-Allow-pg_prewarm-to-use-new-smgrprefetch-range.patch"
Content-Transfer-Encoding: base64

RnJvbSA4ZmQyYTA2MTFhMTNjYTQwZmU3ZDRjMjhhMjcyNTdiYTBmN2UyYTdhIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiA9P1VURi04P3E/Qz1DMz1BOWRyaWM9MjBWaWxsZW1h
aW4/PSA8Q2VkcmljLlZpbGxlbWFpbkBEYXRhLUJlbmUuaW8+CkRhdGU6IFdlZCwgMyBKYW4g
MjAyNCAxOTowODozNyArMDEwMApTdWJqZWN0OiBbUEFUQ0ggMi8zXSBBbGxvdyBwZ19wcmV3
YXJtIHRvIHVzZSBuZXcgc21ncnByZWZldGNoIHJhbmdlCgpUaGlzIGNvbW1pdCByZXBsYWNl
cyBCdWZmZXJQcmVmZXRjaCB3aXRoIHNtZ3JwcmVmZXRjaCAod2l0aCByYW5nZSkuCkl0IHJl
ZHVjZXMgdGhlIG51bWJlciBvZiBpbnN0cnVjdGlvbnMgdG8gcHJlZmV0Y2ggZGF0YS4KCkkg
YWxzbyBkZWZpbmVkIGEgUFJFV0FSTV9QUkVGRVRDSF9SQU5HRSB3aGljaCBpcyBieSBkZWZh
dWx0IHRoZSBzaXplIG9mCmEgc2VnbWVudCBzbyB0aGVyZSBpcyBhIGNoYW5jZSB0byBpbnRl
cnJ1cHQgdGhlIHByZWZldGNoaW5nIChvbiB2ZXJ5CmxhcmdlIHRhYmxlIGl0IG1pZ2h0IGJl
IGRlc2lyYWJsZSkuCi0tLQogY29udHJpYi9wZ19wcmV3YXJtL3BnX3ByZXdhcm0uYyB8IDE4
ICsrKysrKysrKysrKysrKy0tLQogMSBmaWxlIGNoYW5nZWQsIDE1IGluc2VydGlvbnMoKyks
IDMgZGVsZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvY29udHJpYi9wZ19wcmV3YXJtL3BnX3By
ZXdhcm0uYyBiL2NvbnRyaWIvcGdfcHJld2FybS9wZ19wcmV3YXJtLmMKaW5kZXggMWRlMWIz
OTQ4Mi4uYWVhMDc5MjdkYiAxMDA2NDQKLS0tIGEvY29udHJpYi9wZ19wcmV3YXJtL3BnX3By
ZXdhcm0uYworKysgYi9jb250cmliL3BnX3ByZXdhcm0vcGdfcHJld2FybS5jCkBAIC0yNSw2
ICsyNSw4IEBACiAjaW5jbHVkZSAidXRpbHMvbHN5c2NhY2hlLmgiCiAjaW5jbHVkZSAidXRp
bHMvcmVsLmgiCiAKKyNkZWZpbmUgUFJFV0FSTV9QUkVGRVRDSF9SQU5HRQlSRUxTRUdfU0la
RQorCiBQR19NT0RVTEVfTUFHSUM7CiAKIFBHX0ZVTkNUSU9OX0lORk9fVjEocGdfcHJld2Fy
bSk7CkBAIC0xNTYsMTEgKzE1OCwyMSBAQCBwZ19wcmV3YXJtKFBHX0ZVTkNUSU9OX0FSR1Mp
CiAJCSAqIG5vIHByYWN0aWNhbCB3YXkgdG8gZG8gdGhhdCBhdCBwcmVzZW50IHdpdGhvdXQg
YSBncm9zcyBtb2R1bGFyaXR5CiAJCSAqIHZpb2xhdGlvbiwgc28gd2UganVzdCBkbyB0aGlz
LgogCQkgKi8KLQkJZm9yIChibG9jayA9IGZpcnN0X2Jsb2NrOyBibG9jayA8PSBsYXN0X2Js
b2NrOyArK2Jsb2NrKQorCQlmb3IgKGJsb2NrID0gZmlyc3RfYmxvY2s7IGJsb2NrIDw9IGxh
c3RfYmxvY2s7CisJCQkgYmxvY2sgKz0gUFJFV0FSTV9QUkVGRVRDSF9SQU5HRSkKIAkJewor
CQkJaW50IHNlZWsgPSBNaW4oUFJFV0FSTV9QUkVGRVRDSF9SQU5HRSwgKGxhc3RfYmxvY2sg
LSBibG9jayArIDEpKTsKKworCQkJLyoKKwkJCSAqIGlmIGhhbmRsaW5nIGEgbXVsdGktVEIg
cmVsYXRpb24sIHdlIG5lZWQgYSB3YXkgdG8gaW50ZXJydXB0IHRoZQorCQkJICogcHJlZmV0
Y2hpbmc6IHNtZ3JwcmVmZXRjaCAobWRwcmVmZXRjaCkgd2lsbCBsb29wIG9uIGFsbCBzZWdt
ZW50cworCQkJICogd2l0aG91dCBpbnRlcnJ1cHRpb24gc28gd2UgdXNlIGEgcmFuZ2UgYW5k
IGtlZXAgdGhlIGZvbGxvd2luZworCQkJICogQ0hFQ0sgaW4gcGxhY2UKKwkJCSAqLwogCQkJ
Q0hFQ0tfRk9SX0lOVEVSUlVQVFMoKTsKLQkJCVByZWZldGNoQnVmZmVyKHJlbCwgZm9ya051
bWJlciwgYmxvY2spOwotCQkJKytibG9ja3NfZG9uZTsKKworCQkJc21ncnByZWZldGNoKFJl
bGF0aW9uR2V0U21ncihyZWwpLCBmb3JrTnVtYmVyLCBibG9jaywgc2Vlayk7CisJCQlibG9j
a3NfZG9uZSArPSBzZWVrOwogCQl9CiAjZWxzZQogCQllcmVwb3J0KEVSUk9SLAotLSAKMi4z
OS4yCgo=
--------------pGfQwPsGTktcHskuCifEpPx6
Content-Type: text/x-patch; charset=UTF-8;
 name="0003-Allow-pg_prewarm-to-use-new-smgrreadv.patch"
Content-Disposition: attachment;
 filename="0003-Allow-pg_prewarm-to-use-new-smgrreadv.patch"
Content-Transfer-Encoding: base64

RnJvbSBjYjJmYTZmMDI0NWIyOGExZDNmODNhYzkwMWViMGQ4Njk4ZjIyZGQzIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiA9P1VURi04P3E/Qz1DMz1BOWRyaWM9MjBWaWxsZW1h
aW4/PSA8Q2VkcmljLlZpbGxlbWFpbkBEYXRhLUJlbmUuaW8+CkRhdGU6IFdlZCwgMyBKYW4g
MjAyNCAyMDoxNjozNyArMDEwMApTdWJqZWN0OiBbUEFUQ0ggMy8zXSBBbGxvdyBwZ19wcmV3
YXJtIHRvIHVzZSBuZXcgc21ncnJlYWR2CgpUaGlzIGNvbW1pdCByZXBsYWNlcyBzbWdycmVh
ZCB3aXRoIHNtZ3JyZWFkdiAod2l0aCByYW5nZSkuCgpJIGFsc28gZGVmaW5lZCBhIFBSRVdB
Uk1fUkVBRF9SQU5HRSwgOCBieSBkZWZhdWx0LCB3aGljaCBpcyB0aGUKbnVtYmVyIG9mIGJ1
ZmZlcnMgdG8gcmVhZCBpbiBvbmUgY2FsbC4KCkkganVzdCBkaXNjb3ZlcmVkIHBnX3JlYWR2
IHNvIEkgaG9wZSB0aGUgaW5pdGlhbGl6YXRpb24gSSB3cm90ZSBpcyBjb3JyZWN0LgoKSXQn
cyBub3QgcmVhbGx5IHN1cmUgdGhlcmUgaXMgYSBodWdlIHdpbiBiZWNhdXNlIGFsbCBibG9j
a3MgYXJlIHNlcXVlbnRpYWwKYW5kIHJlYWR2IHNob3VsZCBtZXJnZSB0aGVtIGJ1dCBub3Qg
dXNlIHZlY3Rvcml6YXRpb24gKGlmIEkgdW5kZXJzdG9vZApjb3JyZWN0bHkpLgotLS0KIGNv
bnRyaWIvcGdfcHJld2FybS9wZ19wcmV3YXJtLmMgfCAxNyArKysrKysrKysrKysrLS0tLQog
MSBmaWxlIGNoYW5nZWQsIDEzIGluc2VydGlvbnMoKyksIDQgZGVsZXRpb25zKC0pCgpkaWZm
IC0tZ2l0IGEvY29udHJpYi9wZ19wcmV3YXJtL3BnX3ByZXdhcm0uYyBiL2NvbnRyaWIvcGdf
cHJld2FybS9wZ19wcmV3YXJtLmMKaW5kZXggYWVhMDc5MjdkYi4uZTExNjk0ZDcwNyAxMDA2
NDQKLS0tIGEvY29udHJpYi9wZ19wcmV3YXJtL3BnX3ByZXdhcm0uYworKysgYi9jb250cmli
L3BnX3ByZXdhcm0vcGdfcHJld2FybS5jCkBAIC0yNiw2ICsyNiw3IEBACiAjaW5jbHVkZSAi
dXRpbHMvcmVsLmgiCiAKICNkZWZpbmUgUFJFV0FSTV9QUkVGRVRDSF9SQU5HRQlSRUxTRUdf
U0laRQorI2RlZmluZSBQUkVXQVJNX1JFQURfUkFOR0UJOAogCiBQR19NT0RVTEVfTUFHSUM7
CiAKQEAgLTM4LDcgKzM5LDcgQEAgdHlwZWRlZiBlbnVtCiAJUFJFV0FSTV9CVUZGRVIsCiB9
IFByZXdhcm1UeXBlOwogCi1zdGF0aWMgUEdJT0FsaWduZWRCbG9jayBibG9ja2J1ZmZlcjsK
K3N0YXRpYyBQR0lPQWxpZ25lZEJsb2NrIGJsb2NrYnVmZmVyc1tQUkVXQVJNX1JFQURfUkFO
R0VdOwogCiAvKgogICogcGdfcHJld2FybShyZWdjbGFzcywgbW9kZSB0ZXh0LCBmb3JrIHRl
eHQsCkBAIC0xODcsMTEgKzE4OCwxOSBAQCBwZ19wcmV3YXJtKFBHX0ZVTkNUSU9OX0FSR1Mp
CiAJCSAqIGJ1ZmZlcnMuICBUaGlzIGlzIG1vcmUgcG9ydGFibGUgdGhhbiBwcmVmZXRjaCBt
b2RlIChpdCB3b3JrcwogCQkgKiBldmVyeXdoZXJlKSBhbmQgaXMgc3luY2hyb25vdXMuCiAJ
CSAqLwotCQlmb3IgKGJsb2NrID0gZmlyc3RfYmxvY2s7IGJsb2NrIDw9IGxhc3RfYmxvY2s7
ICsrYmxvY2spCisJCWNoYXIJKmJ1ZmZlcnNbUFJFV0FSTV9SRUFEX1JBTkdFXTsKKwkJZm9y
IChpbnQgaT0wOyBpIDwgUFJFV0FSTV9SRUFEX1JBTkdFOyBpKyspCisJCQlidWZmZXJzW2ld
ID0gYmxvY2tidWZmZXJzW2ldLmRhdGE7CisJCWZvciAoYmxvY2sgPSBmaXJzdF9ibG9jazsg
YmxvY2sgPD0gbGFzdF9ibG9jazsKKwkJCSBibG9jayArPSBQUkVXQVJNX1JFQURfUkFOR0Up
CiAJCXsKKwkJCWludCBzZWVrID0gTWluKFBSRVdBUk1fUkVBRF9SQU5HRSwgKGxhc3RfYmxv
Y2sgLSBibG9jayArIDEpKTsKKwogCQkJQ0hFQ0tfRk9SX0lOVEVSUlVQVFMoKTsKLQkJCXNt
Z3JyZWFkKFJlbGF0aW9uR2V0U21ncihyZWwpLCBmb3JrTnVtYmVyLCBibG9jaywgYmxvY2ti
dWZmZXIuZGF0YSk7Ci0JCQkrK2Jsb2Nrc19kb25lOworCisJCQlzbWdycmVhZHYoUmVsYXRp
b25HZXRTbWdyKHJlbCksIGZvcmtOdW1iZXIsIGJsb2NrLCAodm9pZCAqKSBidWZmZXJzLAor
CQkJCQkgIHNlZWspOworCQkJYmxvY2tzX2RvbmUgKz0gc2VlazsKIAkJfQogCX0KIAllbHNl
IGlmIChwdHlwZSA9PSBQUkVXQVJNX0JVRkZFUikKLS0gCjIuMzkuMgoK

--------------pGfQwPsGTktcHskuCifEpPx6--