Interesting 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).
In 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.
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...
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.
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.