6

I read this article about PostgreSQL performance on SSD:

https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0

These two configurations seem to be important random_page_cost vs seq_page_cost

Since both parameters need to match the particular hardware I wonder if it is possible to automatically detect the matching values?

Update

I have these steps on my mind:

  1. Script creates some dummy tables
  2. Scripts inserts data to the tables
  3. Script does some queries
  4. Script shows matching values for random_page_cost and seq_page_cost
  5. Human or an automated system takes these values and updates the config. This step is not part of the question.
  • 4
    For a SSD, I think it's safe to set random_page_cost to 1 as there is no difference in performance between random access and sequential access on a SSD if I'm not mistaken – a_horse_with_no_name Apr 20 '18 at 9:37
  • @a_horse_with_no_name two reads from different chips can be pipelined with an SSD, and if you write sequentially I believe the writes get balanced to the different chips. – Evan Carroll Apr 22 '18 at 5:31
  • random_page_cost and seq_page_cost only apply for reading data. I don't think they are used for any write activity. – a_horse_with_no_name Apr 22 '18 at 15:03
  • @a_horse_with_no_name I don't understand you comment. You say they are not used for writing. That's true. But why not create some tables do some benchmarking and finally show matching values? I updated the question. – guettli Apr 23 '18 at 9:27
7
+50

Since both parameters need to match the particular hardware I wonder if it is possible to automatically detect the matching values?

It's certainly possible to set the parameters automatically, but no one has submitted a patch to do it.

You need to know the sequential and non-sequential read speeds of the drive. There are a ton of ways to get this, but you may just as well use Google because it likely doesn't matter that much. For instance, a quick google search for the sequential and non-sequential read performance of the Samsung SSD 840 Pro (256GB) shows this from AnandTech, with

  • Random Read 101.4/mbps
  • Sequential Read 510.7/mbps

That's roughly a ratio of 1:5, so

SET random_page_cost = 5;
SET seq_page_cost = 1;

Warning, random_page_cost takes into account the cache,

Random access to mechanical disk storage is normally much more expensive than four times sequential access. However, a lower default is used (4.0) because the majority of random accesses to disk, such as indexed reads, are assumed to be in cache. 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.

I've shown my random_page_cost to be 5 times slower than sequential. It's still a wildcard how much of the random_page_cost is already cached. Alas, these values don't really matter much unless the Index scan and the Sequential were so close that you could reasonably pick the Sequential scan by accident. That's seldom the case. It's not uncommon for an Index to speed things up by thousands of times over.

For example, my cpu_index_tuple_cost is 0.005. AFAIK, that means scanning 1000 entries in the index is the same in the planner's eyes as going to the heap once to fetch a block.

  • @a_horse_with_no_name typo on the label but that's the right data. – Evan Carroll Apr 22 '18 at 15:09
  • 1
    Just comparing random vs sequential speeds for choosing the random_page_cost and taking that value directly may not be a good idea, right? Random access to mechanical disk storage is normally much more expensive than four times sequential access. However, a lower default is used (4.0) because the majority of random accesses to disk, such as indexed reads, are assumed to be in cache. 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. (emphasis mine) – tehnicaorg Aug 24 '18 at 8:13
  • Yep, that's a valid point! good catch. @tehnicaorg – Evan Carroll Aug 24 '18 at 8:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.