| From: | Jakub Wartak <jakub(dot)wartak(at)enterprisedb(dot)com> |
|---|---|
| To: | Joao Rito <joaovictor(dot)rito(at)gmail(dot)com> |
| Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: pg_stat_io_histogram |
| Date: | 2026-11-17 13:53:23 |
| Message-ID: | CAKZiRmxSwmk9wY5O9XmhSeOSOTg6qTnz0EF+wwg241n8dkEn-A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Thu, Nov 12, 2026 at 5:12 PM Joao Rito <joaovictor(dot)rito(at)gmail(dot)com> wrote:
>
> Hi Jakub,
Hi Joao, thanks for the review! :)
> Functional test [..]
> - Latencies look plausible to me. For example, about two thirds (4160 of
> about 6300) of the client backend relation/normal reads were in the 8-32
> us buckets. For checkpointer fsyncs, 31 of 54 were in 0.5-2 ms, with a
> tail up to about 100 ms (152 ms total in pg_stat_io).
I wanted to give lighthing talk about this on PgConfEU, talk was not selected
to demo all of this, but in short You can try to use [1] to simualate aribrary
IO latencylike this to simulate some real problems and using iostat -dxm 1,
you'll be able to verify those latencies. Try to some cold / hot reads
(echo 1 to drop_caches + pg_buffercache_evict_all()) and you should see much
more realitic IO latency values both in view and iostat. Now having 20ms IO
(due to that dmsetup "20" there) latency and getting ~80..~93ms in pgbench is
because of mix of read/writes/flushes and also waiting on LWLock for inflight
WAL flush for concurrent transaction(s) (assuming some concurrency like above).
Of course you can use this technique to introduce real spikes of IO latencies
too just to simulating spikes as happens in real world (SAN failovers, crappy
hardware) and then just look at the view to see what it gives you.
> - With io_method=sync (autovacuum off, io_combine_limit=128kB, quiesced
> server, one REPEATABLE READ snapshot), the histogram total equals the
> pg_stat_io operation count for the relation-object read, write, extend and
> fsync rows I compared. The WAL rows had no histogram samples, presumably
> because track_wal_io_timing was at its default (off), and writeback
> differs (see below).
Yup, track_wal_io_timing is necessary to get WAL IO latencies.
> One thing I would like to ask about
> - With io_method=worker, the histogram sample count for reads is higher than
> the read count in pg_stat_io. In one run: vacuum reads 516 in pg_stat_io
> against 1021 in the histogram; client backend bulkread 615 against 738;
> background worker bulkread 575 against 659. My first run with the default
> settings showed the same pattern (vacuum 516 against 931). With
> io_method=sync the numbers were equal (516/516, 683/683, 678/678). Client
> backend normal-context reads were nearly equal (549 against 551).
> - I did not look for the cause. A guess is that some AIO reads are timed
> more than once, or timed in a different place than they are counted, but
> this is only a guess. Is it intended that one histogram sample is not
> always one operation counted in pg_stat_io?
Yes, good fining! There is some discrepancy between io_method=worker|sync when
looking at the pg_stat_io.reads vs pg_stat_io_histogram number of buckets hit.
The patch docs today hide under the following snippet:
+ The view shows measured perceived I/O latency by the backend, not
the kernel or device
+ one. This is important distinction when troubleshooting, as the
I/O latency observed by
+ the backend might get affected by:
[..]
+ <para>With AIO, it might include time to service other IOs
from the queue. That will often inflate IO latency
Perhaps we should extend it to cover this, but I'm just don't know how to
compress the next AIO behaviour and it's implementation into something short/
easily understandable by the user in just few sentences. So I'll start with
simple demo: another realiable technique to slowdown syscalls is to use
modern strace's injection with delay, e.g. given
* io_max_workers=1,
* max_parallel_workers_per_gather=0
* autovacuum=off
* IO timings enabled
you can do this:
strace -s -0 -fp <ioworkerpid|clientbackend> --inject
preadv,pread64:delay_exit=1000 #us
and run this:
select pg_buffercache_evict_all();
select pg_stat_reset_shared('io');
select count(*) from t2;
select pg_stat_force_next_flush();
select context,reads,read_bytes,read_time from pg_stat_io where
reads > 0 and backend_type = 'client backend';
select bucket_latency_us, bucket_count from pg_stat_io_histogram where
bucket_count > 0 and backend_type = 'client backend';
with sync io_method one gets:
context | reads | read_bytes | read_time
---------+-------+------------+-----------
normal | 329 | 37068800 | 366.162
bucket_latency_us | bucket_count
-------------------+--------------
[512,1024) | 7
[1024,2048) | 322
So above all is simple, because the client backend executes the I/O and just
measures it and there's sync among those views. But with that strace attached
to simulate latency and with single ioworker/worker method mode you'll get:
context | reads | read_bytes | read_time
---------+-------+------------+-----------
normal | 284 | 36700160 | 286.38
bucket_latency_us | bucket_count
-------------------+--------------
[0,8) | 284
[512,1024) | 254
[1024,2048) | 30
You'll get that thing only when AIO kicks in to that worker (e.g. with temp
table you won't get that behaviour, happens on fore pure seqscan and depends
if the optimizer choose path that has AIO read streams impl.), but note the
two things here related to Your's question:
a. the worker count is 2x in pg_stat_io_histogram
b. there's bimodal distribution (more on this later)
I am by no means AIO dev, so someone please correct me if I misunderstood
anything, but I'll try to explain best I can the pg_statio infra today, worker
mode and the way it's doing it's I/O timings:
- there's no pread() timing inside I/O workers, probably due to efficency and
avoiding double timing of I/O (it's already done in the client), see e.g.
recent [2] post where Andres shoot down such idea
- because of the above, it has certain repercussion such as pg_stat_io
.backend_type='io worker' returns zero rows
- in normal client backend, we first add the submission time
(smgrstartreadv()) and enqueue the IO (putting on shared mem/submission
queue) + send notification using signal to the IO worker, this is gonna be
ultra-fast almost always and likely hit with one entry the [0,8)ns bucket
(that's why you see more reads than in pg_stat_io.reads). The
pgstat_count_io_op_time(... cnt=1) is used for this.
- then we measure time the client backend *waited* for the I/O once it stopped
doing other stuff (see WaitReadBuffers()->pgaio_wref_wait()). The
pgstat_count_io_op_time(... cnt=0) is used there, but this time we add *wait
time* (so total added cnt is 1+0 , but _two_ buckets might be hit as we just
'++' the specific buckets). Still the wait time probably might be not the
exact full I/O timing(!) matching e.g. iostat/BPF mesaurements, because it
measures since the backend needed to access that buffer and not since
submitting then request for it. So that 2nd timing how much client backend
waited, usually t's going to still show most of the I/O latency (CPUs are
fast, while I/O has higher latency), but it also could happen the
client backend didn't even had to wait at all (imagine some fast fetch from
VFS cache, hitting again [0,8)ns bucket). Read-aheads,
effective_io_concurrency and stuff like that makes it even more probably to
be taken from VFS cache.
- when the IO worker queue would get full, pgaio_worker_submit() still can
fallback to sync execution even if one thinks he has workers doing their
job, so the view is kinda expected to return mix of values
I think users usually will be after long-tail IO latency outliers rather than
those lowend values, but I still wanted to track those two edge cases I still
would
like to cover with this:
a) obviously common issues with IO hw/path: if the real I/O is slow we would
want to catch it (and we'll do, client backend is gonna wait for it)
a) but also if the entry to the submission queue is stuck/slow, we would want
to catch it (and we'll do, it's goinng to be counted). This can be stopped
e.g. with kill -STOP ioworker and with -CONT a few secs later.
Another idea would be to having additional column for showing whether it was
bucket for submission or just for waiting on the I/O (pg_stat_io_histogram
could get additional bool aio_submission column perhaps?), but stil that would
put even more memory demand (so I'm open any feedback).
> - Related: pgstat_count_io_op_time() adds one sample per call, while
> pg_stat_io adds `cnt`. For writeback the histogram total was 18 against
> 565 operations (checkpointer, relation/normal). I did not check what `cnt`
> the writeback caller passes, so this may be intended, but the
> documentation could say what one sample means.
Good one, I think what you have in mind is IssuePendingWritebacks(). It then
goes to smgr_writeback()-> mdwriteback() -> .. ->
sync_file_range(SYNC_FILE_RANGE_WRITE)+posix_fadvise() to forget the cache,
but both those things executed in loop with multiple fd/block ranges should be
super fast (probably one could get stall), today we just track multiple such
calls of sync_file_range() as one (due to how IssuePendingWritebacks()) is
written maybe the should code instead of doing blind bucket++ like this:
PendingIOStats.pending_hist_time_buckets[offset][bucket_index]++;
we should do:
* if cnt==0, then bucket++ (to cover AIO wait, obligatory, as today)
* if cnt==1, then bucket++ (to cover 99% of callsites, as today)
* if cnt>1, then bucket += cnt (to cover single callsite for writeback)
If you or anybody have some thoughts (especially should we add that another
column), please let me know. Of course more review is more than welcome :)
-J.
[1] -
dd if=/dev/zero of=/tmp/disk1.img bs=1M count=1000
mkfs.ext4 -j /tmp/disk1.img
losetup -f # find free
DEV=/dev/loop6 # use it here(from above)
sudo losetup $DEV /tmp/disk1.img
sudo dmsetup create slow --table "0 $(sudo blockdev --getsz $DEV) linear $DEV 0"
sudo mount -t ext4 /dev/mapper/slow /tmp/slow
sudo chown -R user /tmp/slow
rmdir /tmp/slow/lost+found/
/usr/pgsql20/bin/initdb -D /tmp/slow
echo "track_io_timing = on" >> /tmp/slow/postgresql.auto.conf
echo "track_wal_io_timing = on" >> /tmp/slow/postgresql.auto.conf
/usr/pgsql20/bin/pg_ctl -D /tmp/slow -l ~/logfile start
/usr/pgsql20/bin/pgbench -i -s 4 -h /tmp postgres
sudo dmsetup suspend slow
# give 20ms for all IO types (read, write, but flush too)
sudo dmsetup reload slow --table "0 $(sudo blockdev --getsz $DEV)
delay $DEV 0 20"
sudo dmsetup resume slow
/usr/pgsql20/bin/pgbench -c 4 -j 4 -P 1 -T 120 -h /tmp postgres # ~42
RW TPS (~93ms avg per tx)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Sami Imseih | 2026-11-17 14:08:37 | Re: pgstat: Flush some statistics within running transactions, take 2 |
| Previous Message | Nitin Motiani | 2026-11-17 13:02:02 | Re: ALTER COLUMN SET EXPRESSION on partitions not work in case of constraint dependencies |