The real cost of random I/O
vondra.me
The real cost of random I/O
1–10 of 29 posts
Re: The real cost of random I/O
#2In some ways I found this a little surprising; however the final paragraph about real working data not being accessed as randomly as you think is my experience.
I'm curious about ways to live automatically tune this. You can use SET LOCAL to transaction scope a value, yet this would mean managing it completely in your application.
Re: The real cost of random I/O
#3[dead]
Re: The real cost of random I/O
#4Interesting post! IME, setting random_page_cost to 1.1 is more likely to produce good results overall, which is what the ending paragraphs of the post allude to. I’ve also seen situations where it makes the result significantly worse, but they’re relatively rare, and can usually be addressed with a better index.
In general, there are a dizzying number of parameters for both MySQL and Postgres (I assume Oracle and SQL Server as well, but I don’t have experience with them), and many of them can have surprising results. One such example for MySQL is innodb_io_capacity[_max]. The docs [0] say that you should set it to the number of IOPS your system is capable of, and that InnoDB will then use that to guide its background operations. As of version 8.4, the default value has been raised from 200 to 10000. Granted, I haven’t used 8.4 (or 9.x for that matter) in prod, but with 5.7 and 8.0, the advice from Percona [1], and what I’ve found with my own workloads, is to leave it alone - going higher can reduce performance by adding additional write loads (and, as the post points out, prematurely wear out SSDs if you’re running your own).
0: https://dev.mysql.com/doc/refman/8.0/en/innodb-configuring-i...
1: https://www.percona.com/blog/give-love-to-your-ssds-reduce-i...
Re: The real cost of random I/O
#5In some ways I found this a little surprising; however the final paragraph about real working data not being accessed as randomly as you think is my experience. I'm curious about ways to live automatically tune this. You can use SET LOCAL to transaction scope a value, yet this would mean managing it completely in your application.
I suppose if you had a proxying layer capable of query rewrites (ProxySQL can do so, and supports Postgres in newer versions), you could have it prepend the SET LOCAL commands to specific queries as needed. You could take this further, and have a sidecar that’s examining query performance (either by directly querying ProxySQL, or waiting for Prometheus stats), and performing adjustments as needed.
That’s actually an interesting idea, now that I think about it. You could have it running the queries as EXPLAIN in the background, then occasionally testing a change out with EXPLAIN ANALYZE before adjusting the settings to use for rewrites.
Re: The real cost of random I/O
#6It'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).
Re: The real cost of random I/O
#7It'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/throughput at app or db-level).
Re: The real cost of random I/O
#8Interesting post! IME, setting random_page_cost to 1.1 is more likely to produce good results overall, which is what the ending paragraphs of the post allude to. I’ve also seen situations where it makes the result significantly worse, but they’re relatively rare, and can usually be addressed with a better index. In general, there are a dizzying number of parameters for both MySQL and Postgres (I assume Oracle and SQL…
MSSQL keeps a lot of this stuff internal. This is why I prefer it for certain scenarios. Worrying about the tuning parameters can be overwhelming if you aren't pushing the instance to its limits.
Re: The real cost of random I/O
#9Is 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
Re: The real cost of random I/O
#10Is 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
you're right. there are a couple explanations that might have some merit looking at it from the device perspective. one is the the underlying block size is really large, so that looks like a very large cache line that a sequential scan will always hit. its also very likely that there are prefetchers running to try and hide the latency.