HN

Comments (15)

• Menu

  dwedge
  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
• 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
• Menu

  sgarland
  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).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...
• Menu

  jasonhansel
  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).
• Menu

  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.
• Menu

  Normal_gaussian
  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.
• nimbus-hn-test
  [dead]