public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Sabino Mullane <[email protected]>
To: Dagfinn Ilmari Mannsåker <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Changing the default random_page_cost value
Date: Mon, 30 Sep 2024 10:05:29 -0400
Message-ID: <CAKAnmmKxOq8i4JCfc6AzThuetLPenGaOQu25m0EeOhanaQdhLw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAKAnmmK_nSPYr53LobUwQD59a-8U9GEC3XGJ43oaTYJq5nAOkw@mail.gmail.com>
<[email protected]>
On Fri, Sep 27, 2024 at 12:03 PM Dagfinn Ilmari Mannsåker <[email protected]>
wrote:
> It might also be worth mentioning cloudy block storage (e.g. AWS' EBS),
> which is typically backed by SSDs, but has extra network latency.
>
That seems a little too in the weeds for me, but wording suggestions are
welcome. To get things moving forward, I made a doc patch which changes a
few things, namely:
* Mentions the distinction between ssd and hdd right up front.
* Moves the tablespace talk to the very end, as tablespace use is getting
rarer (again, thanks in part to ssds)
* Mentions the capability to set per-database and per-role since we mention
per-tablespace.
* Removes a lot of the talk of caches and justifications for the 4.0
setting. While those are interesting, I've been tuning this parameter for
many years and never really cared about the "90% cache rate". The proof is
in the pudding: rpc is the canonical "try it and see" parameter. Tweak.
Test. Repeat.
Cheers,
Greg
Attachments:
[application/octet-stream] 0001-Lower-random_page_cost-default-to-1.2-and-update-docs-about-it.patch (3.9K, ../CAKAnmmKxOq8i4JCfc6AzThuetLPenGaOQu25m0EeOhanaQdhLw@mail.gmail.com/3-0001-Lower-random_page_cost-default-to-1.2-and-update-docs-about-it.patch)
download | inline diff:
From 50f4800e3501a84c0d41a5e29cf384bf465f230d Mon Sep 17 00:00:00 2001
From: Greg Sabino Mullane <[email protected]>
Date: Mon, 30 Sep 2024 09:52:28 -0400
Subject: [PATCH] Lower random_page_cost default to 1.2 and update docs about
it.
---
doc/src/sgml/config.sgml | 35 ++++++-------------
src/backend/utils/misc/postgresql.conf.sample | 2 +-
2 files changed, 12 insertions(+), 25 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0aec11f443..e901a71613 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5728,14 +5728,15 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
<listitem>
<para>
Sets the planner's estimate of the cost of a
- non-sequentially-fetched disk page. The default is 4.0.
- This value can be overridden for tables and indexes in a particular
- tablespace by setting the tablespace parameter of the same name
- (see <xref linkend="sql-altertablespace"/>).
- </para>
+ non-sequentially-fetched disk page. The default is <literal>1.2</literal>.
+ Note that this value assumes use of a solid-state drive, which
+ has a low random read cost relative to sequential scans. If you
+ are a using a hard disk drive, raising the value to <literal>3</literal>
+ or <literal>4</literal> may give you better performance.
+ </para>
<para>
- Reducing this value relative to <varname>seq_page_cost</varname>
+ Reducing <varname>random_page_cost</varname> relative to <varname>seq_page_cost</varname>
will cause the system to prefer index scans; raising it will
make index scans look relatively more expensive. You can raise
or lower both values together to change the importance of disk I/O
@@ -5744,24 +5745,10 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
</para>
<para>
- 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.
- </para>
-
- <para>
- 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,
- e.g., <literal>1.1</literal>.
+ This value can be overridden for tables and indexes in a particular
+ tablespace by setting the tablespace parameter of the same name
+ (see <xref linkend="sql-altertablespace"/>). It can also be set
+ per-database and per-role, as needed.
</para>
<tip>
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 667e0dc40a..424df4a868 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -417,7 +417,7 @@
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
-#random_page_cost = 4.0 # same scale as above
+#random_page_cost = 1.2 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
--
2.30.2
view thread (6+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Changing the default random_page_cost value
In-Reply-To: <CAKAnmmKxOq8i4JCfc6AzThuetLPenGaOQu25m0EeOhanaQdhLw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox