Re: Performance implications of 8K pread()s

On Fri, 12 Apr 2024, Thomas Munro wrote:

> On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou <jimis(at)gmx(dot)net> wrote:
>> So would it make sense for postgres to perform reads in bigger blocks? Is it
>> easy-ish to implement (where would one look for that)? Or must the I/O unit be
>> tied to postgres' page size?
>
> FYI as of last week we can do a little bit of that on the master branch:
>
> postgres=# select count(*) from t;
>
> preadv(46, ..., 8, 256237568) = 131072
> preadv(46, ..., 5, 256368640) = 131072
> preadv(46, ..., 8, 256499712) = 131072
> preadv(46, ..., 5, 256630784) = 131072
>
> postgres=# set io_combine_limit = '256k';
> postgres=# select count(*) from t;
>
> preadv(47, ..., 5, 613728256) = 262144
> preadv(47, ..., 5, 613990400) = 262144
> preadv(47, ..., 5, 614252544) = 262144
> preadv(47, ..., 5, 614514688) = 262144
>
> Here's hoping the commits implementing this stick, for the PostgreSQL
> 17 release. It's just the beginning though, we can only do this for
> full table scans so far (plus a couple of other obscure places).
> Hopefully in the coming year we'll get the "streaming I/O" mechanism
> that powers this hooked up to lots more places... index scans and
> other stuff. And writing. Then eventually pushing the I/O into the
> background. Your questions actually triggered us to talk about why we
> couldn't switch a few things around in our project and get the I/O
> combining piece done sooner. Thanks!

Better late than never, but I have verified that this makes quite a bit of
difference on Postgres 17, on compressed Btrfs. Sequential scans on
transparently compressed tables are now many times faster. Same good
experience (if not better) with Postgres 18 and method io_uring. Can't
directly compare the two though, since they run on different systems.
Thanks!

Dimitris