4 ms·

This is a good point, what the article really should be discussing is the tradeoff between sequential throughput and random disk access, which is especially imp

This is a good point, what the article really should be discussing is the tradeoff between sequential throughput and random disk access, which is especially important on HDDs, for those unfortunate enough to still be working with things that spin :)

wfn 11y ago
True - that's what I had to battle with as well - disk seek cost vs. read speeds - Postgres does collect stats on that IIRC (in special tables), but might still need to advise it on that. And do lots of EXPLAIN ANALYZE (incl. BUFFERS sometimes) to check what happens. Re. buffers etc., I also had this nagging idea that the way Postgres sorts sets sometimes takes into account cache misses (i.e. it may use sort algo which compares elements that are not that far away - but not sure on this - also, perhaps this is to be expected anyway).
- atomic77 11y ago
  AFAICT pgsql controls this with the random_page_cost and seq_page_cost variables [1], which are relative and seem to default to 4 and 1 respectively. It doesn't look like these are automatically adjusted though based on the hardware. Some searching and I found some blog posts suggesting lowering them both to closer to one on SSDs [2]. Indeed, the documentation says you can set them both to 1 if you are sure that the database will be fully cached in RAM. [1] http://www.postgresql.org/docs/current/static/runtime-config-query.html http://www.postgresql.org/docs/current/static/runtime-config... [2] http://www.cybertec.at/2013/01/better-postgresql-performance-on-ssds/ http://www.cybertec.at/2013/01/better-postgresql-performance...
  - wfn 11y ago
    Aha, yeah I recall reading suggestions akin to those for SSDs. Nice! There's also `ALTER TABLE SET STATISTICS` and default_statistics_target config parameter (for supposedly smart query planning on the fly?) (though would have to re-read to recall how these things work - off to bed soon): http://www.postgresql.org/docs/current/static/runtime-config-query.html http://www.postgresql.org/docs/current/static/runtime-config... (edit ah you included the same link - lots of good stuff here - ahh I like Postgres documentation)
- atomic77 11y ago
  Actually, further to your point, the documentation states this explanation of the default for random/seq_page_cost: "The default value can be thought of as modeling random access as 40 times slower than sequential, while expecting 90% of random reads to be cached If you believe a 90% cache rate is an incorrect assumption for your workload, you can increase random_page_cost to better reflect the true cost of random storage reads. Correspondingly, if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, decreasing random_page_cost can be appropriate. Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for random_page_cost." In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment.
  - wfn 11y ago
    > In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment. Fair enough. Though I think they have in mind cache misses in the sense of page faults here? - i.e. "it's not in memory - have to read from disk". (i.e. cache as in Postgres internal cache (+ OS filesystem cache, actually/IIRC). The variable name `random_page_cost` seems to suggest this (load a memory page into memory - from disk). Whereas I had in mind cache as in CPU cache[1] - where sort operation happens which does lots of cache misses (between CPU cache and RAM) - and needs to read from rest of RAM (i.e. random memory access is not uniformly random). It's a nice rabbit hole.. [1]: https://en.wikipedia.org/wiki/CPU_cache https://en.wikipedia.org/wiki/CPU_cache
  - mvc 11y ago
    > If you believe a 90% cache rate is an incorrect assumption for your workload For people wondering as I did about how you might monitor for this, it looks like the data you need is in pg_statio_all_indexes

wfn 11y ago

True - that's what I had to battle with as well - disk seek cost vs. read speeds - Postgres does collect stats on that IIRC (in special tables), but might still need to advise it on that. And do lots of EXPLAIN ANALYZE (incl. BUFFERS sometimes) to check what happens. Re. buffers etc., I also had this nagging idea that the way Postgres sorts sets sometimes takes into account cache misses (i.e. it may use sort algo which compares elements that are not that far away - but not sure on this - also, perhaps this is to be expected anyway).

atomic77 11y ago
AFAICT pgsql controls this with the random_page_cost and seq_page_cost variables [1], which are relative and seem to default to 4 and 1 respectively. It doesn't look like these are automatically adjusted though based on the hardware. Some searching and I found some blog posts suggesting lowering them both to closer to one on SSDs [2]. Indeed, the documentation says you can set them both to 1 if you are sure that the database will be fully cached in RAM. [1] http://www.postgresql.org/docs/current/static/runtime-config-query.html http://www.postgresql.org/docs/current/static/runtime-config... [2] http://www.cybertec.at/2013/01/better-postgresql-performance-on-ssds/ http://www.cybertec.at/2013/01/better-postgresql-performance...
- wfn 11y ago
  Aha, yeah I recall reading suggestions akin to those for SSDs. Nice! There's also `ALTER TABLE SET STATISTICS` and default_statistics_target config parameter (for supposedly smart query planning on the fly?) (though would have to re-read to recall how these things work - off to bed soon): http://www.postgresql.org/docs/current/static/runtime-config-query.html http://www.postgresql.org/docs/current/static/runtime-config... (edit ah you included the same link - lots of good stuff here - ahh I like Postgres documentation)
atomic77 11y ago
Actually, further to your point, the documentation states this explanation of the default for random/seq_page_cost: "The default value can be thought of as modeling random access as 40 times slower than sequential, while expecting 90% of random reads to be cached If you believe a 90% cache rate is an incorrect assumption for your workload, you can increase random_page_cost to better reflect the true cost of random storage reads. Correspondingly, if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, decreasing random_page_cost can be appropriate. Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for random_page_cost." In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment.
- wfn 11y ago
  > In other words, cache misses are taken into account to the extent that the model above is reflective of your workload and hw environment. Fair enough. Though I think they have in mind cache misses in the sense of page faults here? - i.e. "it's not in memory - have to read from disk". (i.e. cache as in Postgres internal cache (+ OS filesystem cache, actually/IIRC). The variable name `random_page_cost` seems to suggest this (load a memory page into memory - from disk). Whereas I had in mind cache as in CPU cache[1] - where sort operation happens which does lots of cache misses (between CPU cache and RAM) - and needs to read from rest of RAM (i.e. random memory access is not uniformly random). It's a nice rabbit hole.. [1]: https://en.wikipedia.org/wiki/CPU_cache https://en.wikipedia.org/wiki/CPU_cache
- mvc 11y ago
  > If you believe a 90% cache rate is an incorrect assumption for your workload For people wondering as I did about how you might monitor for this, it looks like the data you need is in pg_statio_all_indexes