6 ms·

> Sequential isn’t Always the Worst Indeed! And sometimes it gets even more nuanced - when say the way the index(es) would be used would imply a lot of disk se

by wfn 11y ago

> Sequential isn’t Always the Worst Indeed! And sometimes it gets even more nuanced - when say the way the index(es) would be used would imply a lot of disk seeking (or page faults if not the whole of index(es) is/are in memory (you can "pre-warm" them into memory but if you have to do that you may have to go back to your schema and index design and re-assess it anyway)). Sometimes a bulldozer-like (mostly) (hopefully!) sequential disk read works better. The latter becomes tricky with say SSDs - you then may need to inform Postgres of differing costs for (e.g.) disk seeks (or memory reads vs. disk reads) so it can better decide which way to go.

merb 11y ago
However mostly it is. Consider the example with the ID's if he would have an index with all the fields he would need, example he would only need the id and the email he could create a index with them and the query would be faster, that would be a index only scan (https://wiki.postgresql.org/wiki/Index-only_scans https://wiki.postgresql.org/wiki/Index-only_scans)
- wfn 11y ago
  Sure, I guess with index-only scans the picture does shift a bit, right? And quite a few cases are reducible to those, so this can't be ignored.
atomic77 11y ago
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.
tominous 11y ago
It's also tricky with a COW filesystem like ZFS in which the order of data on-disk may not match the logical order of the file. So your database may decide to dispatch a bunch of sequential 1MB reads to the filesystem, only for the filesystem to translate those large sequential reads into many small random 8kB reads. It depends on how fragmented the file is, so you can start off with great performance which degrades significantly over time. Luckily SSDs have a much lower penalty for random reads compared to spinning disks, so in future this will be less of an issue. In the meantime all you can do is use a different filesystem, hide the problem with cache, or manually defragment your data files from time to time.
- pkaye 11y ago
  Actually SSD themselves implement COW filesystem since NAND cannot be written without erase step.
  - pmalynin 11y ago
    What about TRIM?
    - pkaye 11y ago
      TRIM is like the free() equivalent to malloc(). It releases a NAND page back to free state for other uses. It dones't require any NAND access or writes per-se but if persistent TRIM is needed (vs best effort,) it gets much more complicated.
  - tominous 11y ago
    That's true but not particularly relevant to database performance. As I mentioned, random reads on an SSD do not suffer from a head seek penalty so it doesn't matter how the blocks are allocated. On the other hand with spinning disk there is a huge difference in throughput for large sequential vs small random reads.

by wfn 11y ago

merb 11y ago

However mostly it is. Consider the example with the ID's if he would have an index with all the fields he would need, example he would only need the id and the email he could create a index with them and the query would be faster, that would be a index only scan (https://wiki.postgresql.org/wiki/Index-only_scans https://wiki.postgresql.org/wiki/Index-only_scans)

wfn 11y ago
Sure, I guess with index-only scans the picture does shift a bit, right? And quite a few cases are reducible to those, so this can't be ignored.

atomic77 11y ago

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.

tominous 11y ago

It's also tricky with a COW filesystem like ZFS in which the order of data on-disk may not match the logical order of the file. So your database may decide to dispatch a bunch of sequential 1MB reads to the filesystem, only for the filesystem to translate those large sequential reads into many small random 8kB reads. It depends on how fragmented the file is, so you can start off with great performance which degrades significantly over time. Luckily SSDs have a much lower penalty for random reads compared to spinning disks, so in future this will be less of an issue. In the meantime all you can do is use a different filesystem, hide the problem with cache, or manually defragment your data files from time to time.

pkaye 11y ago
Actually SSD themselves implement COW filesystem since NAND cannot be written without erase step.
- pmalynin 11y ago
  What about TRIM?
  - pkaye 11y ago
    TRIM is like the free() equivalent to malloc(). It releases a NAND page back to free state for other uses. It dones't require any NAND access or writes per-se but if persistent TRIM is needed (vs best effort,) it gets much more complicated.
- tominous 11y ago
  That's true but not particularly relevant to database performance. As I mentioned, random reads on an SSD do not suffer from a head seek penalty so it doesn't matter how the blocks are allocated. On the other hand with spinning disk there is a huge difference in throughput for large sequential vs small random reads.