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

11–20 of 29 posts

Re: The real cost of random I/O

#11

Post by pgaddict »

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

# sequential reads, 8K, direct IO, depth=1

fio --filename=/dev/md127 --direct=1 --rw=read --bs=8k --ioengine=io_uring --iodepth=1 --runtime=30 --numjobs=1 --time_based --group_reporting --name=random-1 --eta-newline=1 --readonly

-> read: IOPS=85.5k, BW=668MiB/s (700MB/s)(19.6GiB/30001msec)

With buffered I/O, random read stay at ~19k IOPS, while sequential reads get to ~1M IOPS (thanks to read-ahead, either at the OS level, or in the SSD).

So part of this is sequential reads benefiting from implicit "prefetching", which reduces the observed cost of a page. But for random I/O there's no such thing, and so it seems more expensive.

It's more complex (e.g. sequential reads allow issuing larger reads), of course.

Re: The real cost of random I/O

#12

Post by ozgrakkurt »

Would recommend adding the specific SSD model and also adding some fio benchmarks to show the real limits of the ssd.

SSDs can be connected to a machine through raid cards or some enclosures etc. etc. And all of this makes massive differences compared to a proper on-board PCIe connection.

Also obviously SSDs have very vastly different performance characteristics.

For example an ssd might look very good at writes until you keep writing for more than some amount without any breaks, and then it becomes super slow.

Re: The real cost of random I/O

#13

Post by ozgrakkurt »

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

NVMe's really do care about location when you hit some concurrency/size limit.

Manufacturers use many hacks like caching writes on disk etc. In my experience, it is rare to have an ssd that actualy behaves like it is expected to.

A solid way of measuring this is using fio with different configurations.

Re: The real cost of random I/O

#14

Post by pgaddict »

ozgrakkurt wrote: post #12

Would recommend adding the specific SSD model and also adding some fio benchmarks to show the real limits of the ssd. SSDs can be connected to a machine through raid cards or some enclosures etc. etc. And all of this makes massive differences compared to a proper on-board PCIe connection. Also obviously SSDs have very vastly different performance characteristics. For example an ssd might look very good at writes unti…

Good point, I should have included that (the linked pgsql-hackers thread have some of this information, at least).

I've observed exactly this behavior on a wide range of hardware / environments, it's not very specific to particular SSDs models (at least not for reads, which is what the blog post was measuring). That's why I showed results from three very different systems.

Some information for the two physical machines:

1) ryzen: Ryzen 9 9900X, RAID0 with 4x Samsung 990 PRO 1TB (in Asus Hyper M.2 Gen5 card)

2) xeon: E5-2699v4, WD Ultrastar DC SN640 960GB (U.3)

I don't know what exactly is backing the SSD storage on the Azure instance.

Re: The real cost of random I/O

#15

Post by re-thc »

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

> Is anyone able to explain why it's so much slower when solid state doesn't really care about the data location?

It does. Just differently.

E.g. a lot of SSDs nowadays cheap out and save money by using slower and poorer quality NAND + faster and high quality NAND cache. So random often misses the cache a lot more.

Re: The real cost of random I/O

#16

Post by nine_k »

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…

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

Re: The real cost of random I/O

#17

Post by vlovich123 »

Something tells me that the inclusion of an HDD into the data set would have altered the interpretation of the data. Given that it’s 30 for SSD and higher for remote disk, it sounds like the default of 4 is either wrong or the “what is the right value for SSD “ isn’t measured correctly

Re: The real cost of random I/O

#18

Post by pgaddict »

vlovich123 wrote: post #17

Something tells me that the inclusion of an HDD into the data set would have altered the interpretation of the data. Given that it’s 30 for SSD and higher for remote disk, it sounds like the default of 4 is either wrong or the “what is the right value for SSD “ isn’t measured correctly

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 hidden some of the random vs. sequential differences. But that's just a wild guess.

Re: The real cost of random I/O

#19

Post by LorenPechtel »

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

I suspect that this comes down the pattern we often see where cache hits dominate in scenarios that would on the surface look similar.

Re: The real cost of random I/O

#20

Post by vlovich123 »

pgaddict wrote: post #18
vlovich123 wrote: post #17

Something tells me that the inclusion of an HDD into the data set would have altered the interpretation of the data. Given that it’s 30 for SSD and higher for remote disk, it sounds like the default of 4 is either wrong or the “what is the right value for SSD “ isn’t measured correctly

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?
Post reply on HN