public inbox for [email protected]
help / color / mirror / Atom feedPerformance implications of 8K pread()s
10+ messages / 3 participants
[nested] [flat]
* Performance implications of 8K pread()s
@ 2023-07-10 14:28 Dimitrios Apostolou <[email protected]>
2023-07-11 17:12 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
2024-04-12 05:45 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Dimitrios Apostolou @ 2023-07-10 14:28 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance implications of 8K pread()s
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
@ 2023-07-11 17:12 ` Thomas Munro <[email protected]>
2023-07-11 17:22 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
2023-07-12 18:50 ` Re: Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
1 sibling, 2 replies; 10+ messages in thread
From: Thomas Munro @ 2023-07-11 17:12 UTC (permalink / raw)
To: Dimitrios Apostolou <[email protected]>; +Cc: pgsql-performance
On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou <[email protected]> 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 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.
I don't know about btrfs but maybe it can be tuned to prefetch
sequential reads better...
> 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. 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance implications of 8K pread()s
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2023-07-11 17:12 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
@ 2023-07-11 17:22 ` Thomas Munro <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Thomas Munro @ 2023-07-11 17:22 UTC (permalink / raw)
To: Dimitrios Apostolou <[email protected]>; +Cc: pgsql-performance
On Wed, Jul 12, 2023 at 5:12 AM Thomas Munro <[email protected]> wrote:
> "gathering"
(Oops, for reads, that's "scattering". As in scatter/gather I/O but I
picked the wrong one...).
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance implications of 8K pread()s
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2023-07-11 17:12 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
@ 2023-07-12 18:50 ` Dimitrios Apostolou <[email protected]>
2023-07-16 22:32 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Dimitrios Apostolou @ 2023-07-12 18:50 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: pgsql-performance
Hello and thanks for the feedback!
On Wed, 12 Jul 2023, Thomas Munro wrote:
> On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou <[email protected]> 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 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.
>
> 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=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
>
>> 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=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.
>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance implications of 8K pread()s
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2023-07-11 17:12 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
2023-07-12 18:50 ` Re: Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
@ 2023-07-16 22:32 ` Thomas Munro <[email protected]>
2023-07-17 14:42 ` Re: Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Thomas Munro @ 2023-07-16 22:32 UTC (permalink / raw)
To: Dimitrios Apostolou <[email protected]>; +Cc: pgsql-performance
On Thu, Jul 13, 2023 at 6:50 AM Dimitrios Apostolou <[email protected]> wrote:
> 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. ;-)
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance implications of 8K pread()s
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2023-07-11 17:12 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
2023-07-12 18:50 ` Re: Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2023-07-16 22:32 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
@ 2023-07-17 14:42 ` Dimitrios Apostolou <[email protected]>
2023-07-17 15:34 ` Re: Performance implications of 8K pread()s Andres Freund <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Dimitrios Apostolou @ 2023-07-17 14:42 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: pgsql-performance
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).
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance implications of 8K pread()s
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2023-07-11 17:12 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
2023-07-12 18:50 ` Re: Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2023-07-16 22:32 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
2023-07-17 14:42 ` Re: Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
@ 2023-07-17 15:34 ` Andres Freund <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Andres Freund @ 2023-07-17 15:34 UTC (permalink / raw)
To: Dimitrios Apostolou <[email protected]>; +Cc: Thomas Munro <[email protected]>; pgsql-performance
Hi,
On 2023-07-17 16:42:31 +0200, Dimitrios Apostolou wrote:
> Thanks, it sounds promising! Are the changes in the 16 branch already,
> 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).
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance implications of 8K pread()s
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
@ 2024-04-12 05:45 ` Thomas Munro <[email protected]>
2024-04-12 11:12 ` Re: Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2025-06-13 12:15 ` Re: Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
1 sibling, 2 replies; 10+ messages in thread
From: Thomas Munro @ 2024-04-12 05:45 UTC (permalink / raw)
To: Dimitrios Apostolou <[email protected]>; +Cc: pgsql-performance
On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou <[email protected]> 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!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance implications of 8K pread()s
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2024-04-12 05:45 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
@ 2024-04-12 11:12 ` Dimitrios Apostolou <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Dimitrios Apostolou @ 2024-04-12 11:12 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: pgsql-performance
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
On 12 April 2024 07:45:52 CEST, Thomas Munro <[email protected]> wrote:
>On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou <[email protected]> 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!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance implications of 8K pread()s
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2024-04-12 05:45 ` Re: Performance implications of 8K pread()s Thomas Munro <[email protected]>
@ 2025-06-13 12:15 ` Dimitrios Apostolou <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Dimitrios Apostolou @ 2025-06-13 12:15 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: pgsql-performance
On Fri, 12 Apr 2024, Thomas Munro wrote:
> On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou <[email protected]> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-06-13 12:15 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-10 14:28 Performance implications of 8K pread()s Dimitrios Apostolou <[email protected]>
2023-07-11 17:12 ` Thomas Munro <[email protected]>
2023-07-11 17:22 ` Thomas Munro <[email protected]>
2023-07-12 18:50 ` Dimitrios Apostolou <[email protected]>
2023-07-16 22:32 ` Thomas Munro <[email protected]>
2023-07-17 14:42 ` Dimitrios Apostolou <[email protected]>
2023-07-17 15:34 ` Andres Freund <[email protected]>
2024-04-12 05:45 ` Thomas Munro <[email protected]>
2024-04-12 11:12 ` Dimitrios Apostolou <[email protected]>
2025-06-13 12:15 ` Dimitrios Apostolou <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox