Date: Wed, 12 Jul 2023 20:50:20 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Thomas Munro <thomas.munro@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Performance implications of 8K pread()s
In-Reply-To: 
 <CA+hUKG+Zr8FBi3pojzU3x4k5XyenHhZ_mdWQ7pKwGeFT2+Tq+Q@mail.gmail.com>
Message-ID: <b676deee-9938-d11b-c9d9-2373f04baa45@gmx.net>
References: <218fa2e0-bc58-e469-35dd-c5cb35906064@gmx.net>
 <CA+hUKG+Zr8FBi3pojzU3x4k5XyenHhZ_mdWQ7pKwGeFT2+Tq+Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-982451855-1689187821=:6218"
UI-OutboundReport: notjunk:1;M01:P0:Lf3XtCiwMCM=;Gq+r3goNqPWWONhL17iby8MpLrB
 uik0dRVwzf2LptNnq3VOzmecs7EETlgiXKescTAxCCbWVB96JEZE2nAl5M0gwbGWyRT8ul9TO
 12L5IuL3ZpPgoRuMEe9URxyxV170eijZF0xaaRu/l7r1UIjjdAx9HlbaAfI3KgNOMg5cBM9Ev
 dJR1Ykv2GnePFZC8GY3HVcG8JwGaQeMK2xM9u0OTztVc8OheqzxXDkmG4h1Fzw6JbO1CBt3Xf
 KsefSzzYFYq5atZb6P33Q6m3n1UJXtQpkPwNSGx5f1pr3vQ28dzyJdnFxAA8dIAaB+HOE8Tw1
 XQWeu4IKfrNEttuQyTUKQ3akiq3reBxNAzySLFIhyQpFiuawWC7D1oLL+XyA60VBQ+jYd6ZJ7
 RzMAVBU3RkU3MqqqJ87iVbjclsPLrunH/bFMT/wmGPXhSQtxudLNgtZrMI7TwWDDaPbAaWIjX
 JSsitEQHIVQfLvrzgnD22ndB+cQsC8/FddZCOMUHtl2bSLWz1WYeBA2pNRAeJAtpT1wodw/7x
 UYUGabs4GQ/isOZ/FjgKLk9uRL+zjF09yzgYzPOOcP8cXKIoSWAMEx5rR8Seo/heqygReO6+8
 GfkV8JPrdvf+s9mPRvdjkuKsJsaulRN2SuH3gOeduAyxHCPpL4cvc277yj8OTnhVFCtkXkSCw
 cMY3+eeiYQtcFHtMe7KAvTZjbWkmnKLvVkiF7uJwYo2DV3ylnxuAnPB3ehO3cDGyf7CNkBWdu
 NDveAJdN6S0iZEdK7jrE6IkNmHqXxJ1TMjbLqmPVSEaccntEL65pbzUHSOv8YjKCgdr6+pqqd
 mRzfPM3eaOwiCRudingZDOC2C5m0uinTxssYE0EQwXbzP+qZwVBWzsc1/Iv56GQVv5kbk5qpx
 /a+vX/7ho7K7zCZB1pyLMsZtFL/bxZkVVKOPJxbkpUrerKFJJIxqAoH3L
Archived-At: 
 <https://www.postgresql.org/message-id/b676deee-9938-d11b-c9d9-2373f04baa45%40gmx.net>
Precedence: bulk

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

--0-982451855-1689187821=:6218
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Hello and thanks for the feedback!

On Wed, 12 Jul 2023, Thomas Munro wrote:

> On Wed, Jul 12, 2023 at 1:11=E2=80=AFAM Dimitrios Apostolou <jimis@gmx.n=
et> wrote:
>> Note that I suspect my setup being related, (btrfs compression behaving
>> suboptimally) since the raw device can give me up to 1GB/s rate. It is =
however
>> evident that reading in bigger chunks would mitigate such setup ineffic=
iencies.
>> On a system that reads are already optimal and the read rate remains th=
e same,
>> then bigger block size would probably reduce the sys time postgresql co=
nsumes
>> because of the fewer system calls.
>
> I don't know about btrfs but maybe it can be tuned to prefetch
> sequential reads better...

I tried a lot to tweak the kernel's block layer read-ahead and to change
different I/O schedulers, but it made no difference. I'm now convinced
that the problem manifests specially on compressed btrfs: the filesystem
doesn't do any read-ahed (pre-fetch) so no I/O requests "merge" on the
block layer.

Iostat gives an interesting insight in the above measurements.  For both
postgres doing sequential scan and for dd with bs=3D8k, the kernel block
layer does not appear to merge the I/O requests. `iostat -x` shows 16
sectors average read request size, 0 merged requests, and very high
reads/s IOPS number.

The dd commands with bs=3D32k block size show fewer IOPS on `iostat -x` bu=
t
higher speed(!), larger average block size and high number of merged
requests.

Example output for some random second out of dd bs=3D8k:

     Device            r/s     rMB/s   rrqm/s  %rrqm r_await rareq-sz
     sdc           1313.00     20.93     2.00   0.15    0.53    16.32

with dd bs=3D32k:

     Device            r/s     rMB/s   rrqm/s  %rrqm r_await rareq-sz
     sdc            290.00     76.44  4528.00  93.98    1.71   269.92

On the same filesystem, doing dd bs=3D8k reads from a file that has not be=
en
compressed by the filesystem I get 1GB/s device read throughput!

I sent this feedback to the btrfs list, but got no feedback yet:

https://www.spinics.net/lists/linux-btrfs/msg137200.html

>
>> 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?
>
> It is hard to implement.  But people are working on it.  One of the
> problems is that the 8KB blocks that we want to read data into aren't
> necessarily contiguous so you can't just do bigger pread() calls
> without solving a lot more problems first.

This kind of overhaul is good, but goes much deeper. Same with async I/O
of course. But what I have in mind should be much simpler (add grains
of salt since I don't know postgres internals :-)

+ A process wants to read a block from a file
+ Postgres' buffer cache layer (shared_buffers?) looks it up in the cache,
   if not found it passes the request down to
+ postgres' block layer; it submits an I/O request for 32KB that include
   the 8K block requested; it returns the 32K block to
+ postgres' buffer cache layer; it stores all 4 blocks read from the disk
   into the buffer cache, and returns only the 1 block requested.

The danger here is that in random non-contiguous 8K reads, the buffer
cache gets satsurated by 4x the amount of data because of 32K reads, and
75% of that data is useless, but may still evict useful data. The answer
is that is should be marked as unused then (by putting it in front of the
cache's LRU for example) so that those unused read-ahead pages are re-used
for upcoming read-ahead, without evicting too much useful pages.

> The project at
> https://wiki.postgresql.org/wiki/AIO aims to deal with the
> "clustering" you seek plus the "gathering" required for non-contiguous
> buffers by allowing multiple block-sized reads to be prepared and
> collected on a pending list up to some size that triggers merging and
> submission to the operating system at a sensible rate, so we can build
> something like a single large preadv() call.  In the current
> prototype, if io_method=3Dworker then that becomes a literal preadv()
> call running in a background "io worker" process, but it could also be
> OS-specific stuff (io_uring, ...) that starts an asynchronous IO
> depending on settings.  If you take that branch and run your test you
> should see 128KB-sized preadv() calls.
>

Interesting and kind of sad that the last update on the wiki page is from
2021. What is the latest prototype? I'm not sure I'm up to the task of
putting my database to the test. ;-)


Thanks and regards,
Dimitris

--0-982451855-1689187821=:6218--