Subject: Performance implications of 8K pread()s

From: Dimitrios Apostolou <[email-masked]>
Date: Mon, 10 Jul 2023 16:28:51 +0200 (CEST)

Hello list,

I have noticed that the performance during a SELECT COUNT(*) command is
much slower than what the device can provide. Parallel workers improve the
situation but for simplicity's sake, I disable parallelism for my
measurements here by setting max_parallel_workers_per_gather to 0.

Strace'ing the postgresql process shows that all reads happen in offset'ed 8KB
blocks using pread():

   pread64(172, ..., 8192, 437370880) = 8192

The read rate I see on the device is only 10-20 MB/s. My case is special
though, as this is on a zstd-compressed btrfs filesystem, on a very fast
(1GB/s) direct attached storage system. Given the decompression ratio is around
10x, the above rate corresponds to about 100 to 200 MB/s of data going into the
postgres process.

Can the 8K block size cause slowdown? Here are my observations:

+ Reading a 1GB postgres file using dd (which uses read() internally) in
    8K and 32K chunks:

      # dd if=4156889.4 of=/dev/null bs=8k
      1073741824 bytes (1.1 GB, 1.0 GiB) copied, 6.18829 s, 174 MB/s

      # dd if=4156889.4 of=/dev/null bs=8k    # 2nd run, data is cached
      1073741824 bytes (1.1 GB, 1.0 GiB) copied, 0.287623 s, 3.7 GB/s

      # dd if=4156889.8 of=/dev/null bs=32k
      1073741824 bytes (1.1 GB, 1.0 GiB) copied, 1.02688 s, 1.0 GB/s

      # dd if=4156889.8 of=/dev/null bs=32k    # 2nd run, data is cached
      1073741824 bytes (1.1 GB, 1.0 GiB) copied, 0.264049 s, 4.1 GB/s

    The rates displayed are after decompression (the fs does it
    transparently) and the results have been verified with multiple runs.

    Notice that the read rate with bs=8k is 174MB/s (I see ~20MB/s on the
    device), slow and similar to what Postgresql gave us above. With bs=32k
    the rate increases to 1GB/s (I see ~80MB/s on the device, but the time
    is very short to register properly).

   The cached reads are fast in both cases.

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 inefficiencies.
On a system that reads are already optimal and the read rate remains the same,
then bigger block size would probably reduce the sys time postgresql consumes
because of the fewer system calls.

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?

Regards,
Dimitris

---

From: Thomas Munro <[email-masked]>
Date: Wed, 12 Jul 2023 05:12:45 +1200

> because of the fewer system calls.

I don't know about btrfs but maybe it can be tuned to prefetch
sequential reads better...

> 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.  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=worker 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.

---

From: Thomas Munro <[email-masked]>
Date: Wed, 12 Jul 2023 05:22:42 +1200

On Wed, Jul 12, 2023 at 5:12 AM Thomas Munro <[email-masked]> wrote:
> "gathering"

(Oops, for reads, that's "scattering".  As in scatter/gather I/O but I
picked the wrong one...).

---

From: Dimitrios Apostolou <[email-masked]>
Date: Wed, 12 Jul 2023 20:50:20 +0200 (CEST)

Hello and thanks for the feedback!

> 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=8k, 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=32k block size show fewer IOPS on `iostat -x` but
higher speed(!), larger average block size and high number of merged
requests.

Example output for some random second out of dd bs=8k:

     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=32k:

     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=8k reads from a file that has not been
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

> 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

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

---

From: Thomas Munro <[email-masked]>
Date: Mon, 17 Jul 2023 10:32:14 +1200

> putting my database to the test. ;-)

It works pretty well,  certainly well enough to try out, and work is
happening.  I'll try to update the wiki with some more up-to-date
information soon.  Basically, compare these two slides (you could also
look at slide 11, which is the most most people are probably
interested in, but then you can't really see what's going on with
system call-level tools):

https://speakerdeck.com/macdice/aio-and-dio-for-postgresql-on-freebsd?slide=7
https://speakerdeck.com/macdice/aio-and-dio-for-postgresql-on-freebsd?slide=9

Not only are the IOs converted into 128KB preadv() calls, they are
issued concurrently and ahead of time while your backend is chewing on
the last lot of pages.  So even if your file system completely fails
at prefetching, we'd have a fighting chance at getting closer to
device/line speed.  That's basically what you have to do to support
direct I/O, where there is no system-provided prefetching.

---

From: Dimitrios Apostolou <[email-masked]>
Date: Mon, 17 Jul 2023 16:42:31 +0200 (CEST)

Thanks, it sounds promising! Are the changes in the 16 branch already,
i.e. is it enough to fetch sources for 16-beta2? If
so do I just configure --with-liburing (I'm on linux) and run with
io_method=io_uring? Else, if I use the io_method=worker what is a sensible
amount of worker threads? Should I also set all the flags for direct I/O?
(io_data_direct=on io_wal_direct=on).

---

From: Andres Freund <[email-masked]>
Date: Mon, 17 Jul 2023 08:34:32 -0700

Hi,

On 2023-07-17 16:42:31 +0200, Dimitrios Apostolou wrote:
> i.e. is it enough to fetch sources for 16-beta2?

No, this is in a separate branch.

https://github.com/anarazel/postgres/tree/aio

> If so do I just configure --with-liburing (I'm on linux) and run with
> io_method=io_uring?

It's probably worth trying out both io_uring and worker. I've not looked at
performance on btrfs. I know that some of the optimized paths for io_uring
(being able to perform filesystem IO without doing so synchronously in an
in-kernel thread) require filesystem cooperation, and I do not know how much
attention btrfs has received for that.

> Else, if I use the io_method=worker what is a sensible amount of worker
> threads?

Depends on your workload :/. If you just want to measure whether it fixes your
single-threaded query execution issue, the default should be just fine.

> Should I also set all the flags for direct I/O?  (io_data_direct=on
> io_wal_direct=on).

FWIW, I just pushed a rebased version to the aio branch, and there the config
for direct io is
io_direct = 'data, wal, wal_init'
(or a subset thereof).

---

From: Thomas Munro <[email-masked]>
Date: Fri, 12 Apr 2024 17:45:52 +1200

> 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!

---

From: Dimitrios Apostolou <[email-masked]>
Date: Fri, 12 Apr 2024 13:12:55 +0200

Exciting! Since I still have the same performance issues on compressed btrfs, I'm looking forward to testing the patches, probably when a 17 Beta is out and I can find binaries on my platform (OpenSUSE). It looks like it will make a huge difference.

Thank you for persisting and getting this through.

Dimitris

---

From: Dimitrios Apostolou <[email-masked]>
Date: Fri, 13 Jun 2025 14:15:17 +0200 (CEST)

> 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