Date: Mon, 10 Jul 2023 16:28:51 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-performance@postgresql.org
Subject: Performance implications of 8K pread()s
Message-ID: <218fa2e0-bc58-e469-35dd-c5cb35906064@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:2K3NwORKL5U=;uSH4sNYTDJoisY8gYHrg6CnZWnJ
 14v3zuQ73pItawyvSvxMtGutvJ1+xxaQuVfzXXqoLtsMhSN+VcywvJtoqIzv1U0dRbcspYTDM
 S0/U4STeQbHdFfuemxfTafeYVQF7TwC5enet9VJCRwcIsVPcGwnjAd01cmk/zfF/cEtX8zolG
 p0x0bnojofvweYR/ewgr54DrMBisoszM9Gq9SQm69dPGa4dX62HKlEeVJxhthXhIMSQZbfpIW
 Axl3hVeBRnJWfLI/TeF6ni4odeyQdfF+jxSCvuo2a1LQtgiED6HnQc084WAQZkpyZURshxLOj
 xuXctkcJJcz2yd9ey3upUZQKafl0bZ8u8x5ZxQp4h1Fvyxph9VD6Qt4v/drpceAKkuxKrrYWw
 ysr06HG71gQ4IttRz2ir3gpDR+UtmKFyImodFHtSWjDIXw4un2mIKgSPJBZ1ZeZyUssBF+X99
 Xolu0n40UOSbOQX+r7RxeTSZeT+4UGWGWcgb7OJ3HIUc/hLu1PeRUd5EcHw4uKoBLJLE9ROOt
 Jt/wuVOAKYb3EEwo9TLHwJaf8kb+pqwCuuvMjh7pP9H++/6EZMrOBCiLL8cWLzlrTzzd/+Kjk
 ztyljYsVGHYUA/EYKPF9o04c32M+yp34vHsx7kM98Bd0cZ2hTu5UFLZ3qRpwp7fkZGVhmZ4mn
 WNYiqmWgUZ7YDInskuQ0uGfHE6lN1kZVP0Vu1I4TeQG2o2ZDdP1ZNDl1IuhAx0KtErsNgL/MR
 ysvCe9Qx/8B2Rb0h/rUMsQxuLa2yKJgyDEnGeyUHUgb0QwQWrlNIr5BF/rIbb35p3G9H3ivlx
 ATd+LSpjKD1gvq9TIdInbylK5PtitT2Kka9DQEHopdKHhIgIa21S4L5EKc35bNrlIsy0qn84Z
 jRUCkuM1WlYeHlxtqsU2VJZznXkiavWp4N6CehrLFvGAv/CaLAwSnaFLh
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/218fa2e0-bc58-e469-35dd-c5cb35906064%40gmx.net>
Precedence: bulk

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) =3D 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 a=
round
10x, the above rate corresponds to about 100 to 200 MB/s of data going int=
o 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=3D4156889.4 of=3D/dev/null bs=3D8k
      1073741824 bytes (1.1 GB, 1.0 GiB) copied, 6.18829 s, 174 MB/s

      # dd if=3D4156889.4 of=3D/dev/null bs=3D8k    # 2nd run, data is cac=
hed
      1073741824 bytes (1.1 GB, 1.0 GiB) copied, 0.287623 s, 3.7 GB/s

      # dd if=3D4156889.8 of=3D/dev/null bs=3D32k
      1073741824 bytes (1.1 GB, 1.0 GiB) copied, 1.02688 s, 1.0 GB/s

      # dd if=3D4156889.8 of=3D/dev/null bs=3D32k    # 2nd run, data is ca=
ched
      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=3D8k is 174MB/s (I see ~20MB/s on th=
e
    device), slow and similar to what Postgresql gave us above. With bs=3D=
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 how=
ever
evident that reading in bigger chunks would mitigate such setup inefficien=
cies.
On a system that reads are already optimal and the read rate remains the s=
ame,
then bigger block size would probably reduce the sys time postgresql consu=
mes
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 uni=
t be
tied to postgres' page size?

Regards,
Dimitris