Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The real cost of random I/O

vondra.me

Post reply on HN
Print view View original

21–29 of 29 posts

Re: The real cost of random I/O

#21

Post by magicalhippo »

touisteur wrote: post #7
jasonhansel wrote: post #6

It'd be interesting to see an RDBMS that actually dynamically measures the performance characteristics of the drive it's running on (by occasionally running small "fio"-like benchmarks, or by inferring them from scan execution times).

Genuinely curious: where would one expect the drive performance to fluctuate? Wear ? Lack of TRIM ? Some form of timely GC process on disk firmware ? Fragmentation or compaction of some sort ? Maybe weird shenanigans with RAID setups with disks from different vendors and batches ? Embarking right now on a long-term embedded storage project and wondering what people actually monitor (apart from SMART and latency/throu…

A lot of databases are served over SAN, where the underlying performance can depend on contestion, as well as less frequent events such as faulty or new hardware.

Re: The real cost of random I/O

#22

Post by i_think_so »

pgaddict wrote: post #11
dwedge wrote: post #9

Is anyone able to explain why it's so much slower when solid state doesn't really care about the data location? Is this simply a quirk of postgres where the index scan requires two reads (unless I'm mistaken) while with mysql the primary key index is the data. I'd be curious to see comparisons here with mysql and also sequential/random read straight from disk

There probably is some additional inefficiency when reading pages randomly (compared to sequential reads), but most of the difference is at the storage level. That is, SSDs can handle a lot of random I/O, but it's nowhere close to sequential reads. For example, I have a RAID0 with 4 SSDs (Samsung 990 PRO, so consumer, but quite good for reads). And this is what fio says: # random reads, 8K, direct IO, depth=1 fio --f…

This is why I love this site. Thank you for sharing your data, not just opinion.

Re: The real cost of random I/O

#23

Post by menaerus »

pgaddict wrote: post #11
dwedge wrote: post #9

Is anyone able to explain why it's so much slower when solid state doesn't really care about the data location? Is this simply a quirk of postgres where the index scan requires two reads (unless I'm mistaken) while with mysql the primary key index is the data. I'd be curious to see comparisons here with mysql and also sequential/random read straight from disk

There probably is some additional inefficiency when reading pages randomly (compared to sequential reads), but most of the difference is at the storage level. That is, SSDs can handle a lot of random I/O, but it's nowhere close to sequential reads. For example, I have a RAID0 with 4 SSDs (Samsung 990 PRO, so consumer, but quite good for reads). And this is what fio says: # random reads, 8K, direct IO, depth=1 fio --f…

> fio --filename=device name --direct=1 --rw=randread --bs=4k --ioengine=libaio --iodepth=256 --runtime=120 --numjobs=4 --time_based --group_reporting --name=iops-test-job --eta-newline=1 --readonly

> -> read: IOPS=19.1k, BW=149MiB/s (156MB/s)(4473MiB/30001msec)

Isn't this too low? On my non-RAID configuration I get almost 2GB/s with the exact same command. Samsung 980 PRO 1TB.

Re: The real cost of random I/O

#24

Post by pgaddict »

menaerus wrote: post #23
pgaddict wrote: post #11

Earlier quoted context omitted.

There probably is some additional inefficiency when reading pages randomly (compared to sequential reads), but most of the difference is at the storage level. That is, SSDs can handle a lot of random I/O, but it's nowhere close to sequential reads. For example, I have a RAID0 with 4 SSDs (Samsung 990 PRO, so consumer, but quite good for reads). And this is what fio says: # random reads, 8K, direct IO, depth=1 fio --f…

> fio --filename=device name --direct=1 --rw=randread --bs=4k --ioengine=libaio --iodepth=256 --runtime=120 --numjobs=4 --time_based --group_reporting --name=iops-test-job --eta-newline=1 --readonly > -> read: IOPS=19.1k, BW=149MiB/s (156MB/s)(4473MiB/30001msec) Isn't this too low? On my non-RAID configuration I get almost 2GB/s with the exact same command. Samsung 980 PRO 1TB.

Damn, I copied the wrong command. I wanted to copy this one:

fio --filename=/dev/md127 --direct=1 --rw=randread --bs=8k --ioengine=io_uring --iodepth=1 --runtime=120 --numjobs=1 --time_based --group_reporting --name=iops-test-job --eta-newline=1 --readonly

i.e. with iodepth=1 and numjobs=1. Because this is what "mimics" index scan (without prefetch) on cold data. More or less.

The command I posted earlier does ~10GB/s on my RAID, which matches your data.

Re: The real cost of random I/O

#25

Post by pgaddict »

vlovich123 wrote: post #20
pgaddict wrote: post #18

Earlier quoted context omitted.

Good idea. It's an interesting historical question - when we picked 4.0 as the default ~25 years ago, how close was is to the calculated value? I was asking that myself. Unfortunately I don't have a machine with traditional HDD in my homelab anymore, but I'll see if I can run the test somewhere. I wouldn't be all that surprised if this was (partially) due to Postgres being less optimized back then, which might have h…

But also if it was calculated 25 years ago, was it the same metric you’re using today?

To the best of my knowledge, yes. Unfortunately the details of how it was calculated in ~2000 seem to be lost, but the person who did that described he did it like this. It's possible we forgot some important details, of course, but the intent was to use the same formula. Which is why I carefully described and published the scripts, so that other engineers can point out thinkos and suggest changes.

Re: The real cost of random I/O

#26

Post by touisteur »

magicalhippo wrote: post #21
touisteur wrote: post #7

Earlier quoted context omitted.

Genuinely curious: where would one expect the drive performance to fluctuate? Wear ? Lack of TRIM ? Some form of timely GC process on disk firmware ? Fragmentation or compaction of some sort ? Maybe weird shenanigans with RAID setups with disks from different vendors and batches ? Embarking right now on a long-term embedded storage project and wondering what people actually monitor (apart from SMART and latency/throu…

A lot of databases are served over SAN, where the underlying performance can depend on contestion, as well as less frequent events such as faulty or new hardware.

Oh thanks. Forgot about this case.

Re: The real cost of random I/O

#27

Post by touisteur »

nine_k wrote: post #16
touisteur wrote: post #7

Earlier quoted context omitted.

Genuinely curious: where would one expect the drive performance to fluctuate? Wear ? Lack of TRIM ? Some form of timely GC process on disk firmware ? Fragmentation or compaction of some sort ? Maybe weird shenanigans with RAID setups with disks from different vendors and batches ? Embarking right now on a long-term embedded storage project and wondering what people actually monitor (apart from SMART and latency/throu…

It could be regular, like SQL "analyze table", it could be one-off. The point is that it would be an automatic tool.

I'm not harping on the usefulness. More like trying to understand what would be the merit or use-case of frequent or permanent monitoring of this. Are there know failure/degradation modes from the storage HW or the filesystem, or database problems that would be detected by running this continuously. Sibling answer talks about SAN, which makes sense. Wondering what the other use-cases are here.

Otherwise, yes, of course,using it as calibration after any HW change would be interesting.

Re: The real cost of random I/O

#28

Post by menaerus »

pgaddict wrote: post #24
menaerus wrote: post #23

Earlier quoted context omitted.

> fio --filename=device name --direct=1 --rw=randread --bs=4k --ioengine=libaio --iodepth=256 --runtime=120 --numjobs=4 --time_based --group_reporting --name=iops-test-job --eta-newline=1 --readonly > -> read: IOPS=19.1k, BW=149MiB/s (156MB/s)(4473MiB/30001msec) Isn't this too low? On my non-RAID configuration I get almost 2GB/s with the exact same command. Samsung 980 PRO 1TB.

Damn, I copied the wrong command. I wanted to copy this one: fio --filename=/dev/md127 --direct=1 --rw=randread --bs=8k --ioengine=io_uring --iodepth=1 --runtime=120 --numjobs=1 --time_based --group_reporting --name=iops-test-job --eta-newline=1 --readonly i.e. with iodepth=1 and numjobs=1. Because this is what "mimics" index scan (without prefetch) on cold data. More or less. The command I posted earlier does ~10GB/…

> Because this is what "mimics" index scan (without prefetch) on cold data. More or less.

This is an interesting observation but does it really mimic the index scan? This would be essentially a worst case scenario. Submitting IO requests one by one would be a very inefficient way to handle scans, no?

Re: The real cost of random I/O

#29

Post by pgaddict »

menaerus wrote: post #28
pgaddict wrote: post #24

Earlier quoted context omitted.

Damn, I copied the wrong command. I wanted to copy this one: fio --filename=/dev/md127 --direct=1 --rw=randread --bs=8k --ioengine=io_uring --iodepth=1 --runtime=120 --numjobs=1 --time_based --group_reporting --name=iops-test-job --eta-newline=1 --readonly i.e. with iodepth=1 and numjobs=1. Because this is what "mimics" index scan (without prefetch) on cold data. More or less. The command I posted earlier does ~10GB/…

> Because this is what "mimics" index scan (without prefetch) on cold data. More or less. This is an interesting observation but does it really mimic the index scan? This would be essentially a worst case scenario. Submitting IO requests one by one would be a very inefficient way to handle scans, no?

True. Unfortunately it's what index scans in Postgres do right now - it's the last "major" scan type not supporting some sort of prefetch (posix_fadvise or AIO). We're working on it, hopefully it'll get into PG19.
Post reply on HN