public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v39] postgres_fdw: Start using new libpq cancel APIs
10+ messages / 5 participants
[nested] [flat]
* [PATCH v39] postgres_fdw: Start using new libpq cancel APIs
@ 2024-03-18 18:37 Jelte Fennema-Nio <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Jelte Fennema-Nio @ 2024-03-18 18:37 UTC (permalink / raw)
Commit 61461a300c1c introduced new functions to libpq for cancelling
queries. This replaces the usage of the old ones in postgres_fdw.
Author: Jelte Fennema-Nio <[email protected]>
Discussion: https://postgr.es/m/CAGECzQT_VgOWWENUqvUV9xQmbaCyXjtRRAYO8W07oqashk_N+g@mail.gmail.com
---
contrib/postgres_fdw/connection.c | 108 +++++++++++++++---
.../postgres_fdw/expected/postgres_fdw.out | 15 +++
contrib/postgres_fdw/sql/postgres_fdw.sql | 7 ++
3 files changed, 113 insertions(+), 17 deletions(-)
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 4931ebf591..0c66eaa001 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -133,7 +133,7 @@ static void pgfdw_inval_callback(Datum arg, int cacheid, uint32 hashvalue);
static void pgfdw_reject_incomplete_xact_state_change(ConnCacheEntry *entry);
static void pgfdw_reset_xact_state(ConnCacheEntry *entry, bool toplevel);
static bool pgfdw_cancel_query(PGconn *conn);
-static bool pgfdw_cancel_query_begin(PGconn *conn);
+static bool pgfdw_cancel_query_begin(PGconn *conn, TimestampTz endtime);
static bool pgfdw_cancel_query_end(PGconn *conn, TimestampTz endtime,
bool consume_input);
static bool pgfdw_exec_cleanup_query(PGconn *conn, const char *query,
@@ -1315,36 +1315,106 @@ pgfdw_cancel_query(PGconn *conn)
endtime = TimestampTzPlusMilliseconds(GetCurrentTimestamp(),
CONNECTION_CLEANUP_TIMEOUT);
- if (!pgfdw_cancel_query_begin(conn))
+ if (!pgfdw_cancel_query_begin(conn, endtime))
return false;
return pgfdw_cancel_query_end(conn, endtime, false);
}
+/*
+ * Submit a cancel request to the given connection, waiting only until
+ * the given time.
+ *
+ * We sleep interruptibly until we receive confirmation that the cancel
+ * request has been accepted, and if it is, return true; if the timeout
+ * lapses without that, or the request fails for whatever reason, return
+ * false.
+ */
static bool
-pgfdw_cancel_query_begin(PGconn *conn)
+pgfdw_cancel_query_begin(PGconn *conn, TimestampTz endtime)
{
- PGcancel *cancel;
- char errbuf[256];
+ bool timed_out = false;
+ bool failed = false;
+ PGcancelConn *cancel_conn = PQcancelCreate(conn);
- /*
- * Issue cancel request. Unfortunately, there's no good way to limit the
- * amount of time that we might block inside PQgetCancel().
- */
- if ((cancel = PQgetCancel(conn)))
+ if (!PQcancelStart(cancel_conn))
{
- if (!PQcancel(cancel, errbuf, sizeof(errbuf)))
+ PG_TRY();
{
ereport(WARNING,
(errcode(ERRCODE_CONNECTION_FAILURE),
errmsg("could not send cancel request: %s",
- errbuf)));
- PQfreeCancel(cancel);
- return false;
+ pchomp(PQcancelErrorMessage(cancel_conn)))));
}
- PQfreeCancel(cancel);
+ PG_FINALLY();
+ {
+ PQcancelFinish(cancel_conn);
+ }
+ PG_END_TRY();
+ return false;
}
- return true;
+ /* In what follows, do not leak any PGcancelConn on an error. */
+ PG_TRY();
+ {
+ while (true)
+ {
+ PostgresPollingStatusType pollres = PQcancelPoll(cancel_conn);
+ TimestampTz now = GetCurrentTimestamp();
+ long cur_timeout;
+ int waitEvents = WL_LATCH_SET | WL_TIMEOUT | WL_EXIT_ON_PM_DEATH;
+
+ if (pollres == PGRES_POLLING_OK)
+ break;
+
+ /* If timeout has expired, give up, else get sleep time. */
+ cur_timeout = TimestampDifferenceMilliseconds(now, endtime);
+ if (cur_timeout <= 0)
+ {
+ timed_out = true;
+ failed = true;
+ goto exit;
+ }
+
+ switch (pollres)
+ {
+ case PGRES_POLLING_READING:
+ waitEvents |= WL_SOCKET_READABLE;
+ break;
+ case PGRES_POLLING_WRITING:
+ waitEvents |= WL_SOCKET_WRITEABLE;
+ break;
+ default:
+ failed = true;
+ goto exit;
+ }
+
+ /* Sleep until there's something to do */
+ WaitLatchOrSocket(MyLatch, waitEvents, PQcancelSocket(cancel_conn),
+ cur_timeout, PG_WAIT_EXTENSION);
+ ResetLatch(MyLatch);
+
+ CHECK_FOR_INTERRUPTS();
+ }
+exit:
+ if (failed)
+ {
+ if (timed_out)
+ ereport(WARNING,
+ (errmsg("could not cancel request due to timeout")));
+ else
+ ereport(WARNING,
+ (errcode(ERRCODE_CONNECTION_FAILURE),
+ errmsg("could not send cancel request: %s",
+ pchomp(PQcancelErrorMessage(cancel_conn)))));
+ }
+ }
+ PG_FINALLY();
+ {
+ PQcancelFinish(cancel_conn);
+ }
+ PG_END_TRY();
+
+ return !failed;
}
static bool
@@ -1685,7 +1755,11 @@ pgfdw_abort_cleanup_begin(ConnCacheEntry *entry, bool toplevel,
*/
if (PQtransactionStatus(entry->conn) == PQTRANS_ACTIVE)
{
- if (!pgfdw_cancel_query_begin(entry->conn))
+ TimestampTz endtime;
+
+ endtime = TimestampTzPlusMilliseconds(GetCurrentTimestamp(),
+ CONNECTION_CLEANUP_TIMEOUT);
+ if (!pgfdw_cancel_query_begin(entry->conn, endtime))
return false; /* Unable to cancel running query */
*cancel_requested = lappend(*cancel_requested, entry);
}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 58a603ac56..e03160bd97 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2739,6 +2739,21 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c
(10 rows)
ALTER VIEW v4 OWNER TO regress_view_owner;
+-- Make sure this big CROSS JOIN query is pushed down
+EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 CROSS JOIN ft2 CROSS JOIN ft4 CROSS JOIN ft5;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: (count(*))
+ Relations: Aggregate on ((((public.ft1) INNER JOIN (public.ft2)) INNER JOIN (public.ft4)) INNER JOIN (public.ft5))
+ Remote SQL: SELECT count(*) FROM ((("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) INNER JOIN "S 1"."T 3" r4 ON (TRUE)) INNER JOIN "S 1"."T 4" r6 ON (TRUE))
+(4 rows)
+
+-- Make sure query cancellation works
+SET statement_timeout = '10ms';
+select count(*) from ft1 CROSS JOIN ft2 CROSS JOIN ft4 CROSS JOIN ft5; -- this takes very long
+ERROR: canceling statement due to statement timeout
+RESET statement_timeout;
-- ====================================================================
-- Check that userid to use when querying the remote table is correctly
-- propagated into foreign rels present in subqueries under an UNION ALL
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e3d147de6d..2626e68cc6 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -737,6 +737,13 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
ALTER VIEW v4 OWNER TO regress_view_owner;
+-- Make sure this big CROSS JOIN query is pushed down
+EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 CROSS JOIN ft2 CROSS JOIN ft4 CROSS JOIN ft5;
+-- Make sure query cancellation works
+SET statement_timeout = '10ms';
+select count(*) from ft1 CROSS JOIN ft2 CROSS JOIN ft4 CROSS JOIN ft5; -- this takes very long
+RESET statement_timeout;
+
-- ====================================================================
-- Check that userid to use when querying the remote table is correctly
-- propagated into foreign rels present in subqueries under an UNION ALL
--
2.39.2
--hlk45oagubnrswfe--
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Changing the default random_page_cost value
@ 2024-10-14 21:15 Bruce Momjian <[email protected]>
2024-10-15 02:20 ` Re: Changing the default random_page_cost value David Rowley <[email protected]>
2024-10-25 00:01 ` Re: Changing the default random_page_cost value Greg Sabino Mullane <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Bruce Momjian @ 2024-10-14 21:15 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers
On Mon, Sep 30, 2024 at 10:05:29AM -0400, Greg Sabino Mullane wrote:
> 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.
I am not a fan of this patch. I don't see why _removing_ the magnetic
part helps because you then have no logic for any 1.2 was chosen. I
would put the magnetic in a separate paragraph perhaps, and recommend
4.0 for it. Also, per-tablespace makes sense because of physical media
differences, but what purpose would per-database and per-role serve?
Also, per-tablespace is not a connection-activated item like the other
two.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Changing the default random_page_cost value
2024-10-14 21:15 Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
@ 2024-10-15 02:20 ` David Rowley <[email protected]>
2024-10-15 02:38 ` Re: Changing the default random_page_cost value Tom Lane <[email protected]>
2024-10-25 00:13 ` Re: Changing the default random_page_cost value Greg Sabino Mullane <[email protected]>
1 sibling, 2 replies; 10+ messages in thread
From: David Rowley @ 2024-10-15 02:20 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>
On Tue, 15 Oct 2024 at 10:15, Bruce Momjian <[email protected]> wrote:
> I am not a fan of this patch. I don't see why _removing_ the magnetic
> part helps because you then have no logic for any 1.2 was chosen. I
> would put the magnetic in a separate paragraph perhaps, and recommend
> 4.0 for it. Also, per-tablespace makes sense because of physical media
> differences, but what purpose would per-database and per-role serve?
> Also, per-tablespace is not a connection-activated item like the other
> two.
Yeah, I think any effort to change the default value for this setting
would require some analysis to prove that the newly proposed default
is a more suitable setting than the current default. I mean, why 1.2
and not 1.1 or 1.3? Where's the evidence that 1.2 is the best value
for this?
I don't think just providing evidence that random read times are
closer to sequential read times on SSDs are closer than they are with
HDDs is going to be enough. What we want to know is if the planner
costs become more closely related to the execution time or not. From
my experience, random_page_cost really only has a loose grasp on
reality, so you might find that it's hard to prove this with any
degree of confidence (just have a look at how inconsiderate
index_pages_fetched() is to other queries running on the database, for
example).
I suggest first identifying all the locations that use
random_page_cost then coming up with some test cases that run queries
that exercise those locations in a way that does things like vary the
actual selectivity of some value to have the planner switch plans then
try varying the random_page_cost to show that the switchover point is
more correct with the new value than it is with the old value. It
would be nice to have this as a script so that other people could
easily run it on their hardware to ensure that random_page_cost we
choose as the new default is representative of the average hardware.
You'll likely need to do this with varying index sizes. I imagine to
properly test this so that we'd have any useful degree of confidence
that the new value is better than the old one would likely require a
benchmark that runs for several hours. At the upper end, you'd likely
want the data sizes to exceed the size of RAM. Another dimension that
the tests should likely explore is varying data locality.
David
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Changing the default random_page_cost value
2024-10-14 21:15 Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
2024-10-15 02:20 ` Re: Changing the default random_page_cost value David Rowley <[email protected]>
@ 2024-10-15 02:38 ` Tom Lane <[email protected]>
2024-10-15 03:03 ` Re: Changing the default random_page_cost value Tom Lane <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2024-10-15 02:38 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>
David Rowley <[email protected]> writes:
> Yeah, I think any effort to change the default value for this setting
> would require some analysis to prove that the newly proposed default
> is a more suitable setting than the current default. I mean, why 1.2
> and not 1.1 or 1.3? Where's the evidence that 1.2 is the best value
> for this?
Yeah, that's been my main concern about this proposal too.
I recall that when we settled on 4.0 as a good number for
spinning-rust drives, it came out of some experimentation that
I'd done that involved multiple-day-long tests. I don't recall any
more details than that sadly, but perhaps trawling the mailing list
archives would yield useful info. It looks like the 4.0 value came
in with b1577a7c7 of 2000-02-15, so late 1999/early 2000 would be the
time frame to look in.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Changing the default random_page_cost value
2024-10-14 21:15 Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
2024-10-15 02:20 ` Re: Changing the default random_page_cost value David Rowley <[email protected]>
2024-10-15 02:38 ` Re: Changing the default random_page_cost value Tom Lane <[email protected]>
@ 2024-10-15 03:03 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2024-10-15 03:03 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>
I wrote:
> I recall that when we settled on 4.0 as a good number for
> spinning-rust drives, it came out of some experimentation that
> I'd done that involved multiple-day-long tests. I don't recall any
> more details than that sadly, but perhaps trawling the mailing list
> archives would yield useful info. It looks like the 4.0 value came
> in with b1577a7c7 of 2000-02-15, so late 1999/early 2000 would be the
> time frame to look in.
I tried asking https://www.postgresql.org/search/ about
random_page_cost, and got nothing except search engine timeouts :-(.
However, some digging in my own local archives yielded
https://www.postgresql.org/message-id/flat/25387.948414692%40sss.pgh.pa.us
https://www.postgresql.org/message-id/flat/14601.949786166%40sss.pgh.pa.us
That confirms my recollection of multiple-day test runs, but doesn't
offer much more useful detail than that :-(. What I think I did
though was to create some large tables (much bigger than the RAM on
the machine I had) and actually measure the runtime of seq scans
versus full-table index scans, repeating plenty 'o times to try to
average out the noise. There was some talk in those threads of
reducing that to a publishable script, but it was never followed up
on.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Changing the default random_page_cost value
2024-10-14 21:15 Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
2024-10-15 02:20 ` Re: Changing the default random_page_cost value David Rowley <[email protected]>
@ 2024-10-25 00:13 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Greg Sabino Mullane @ 2024-10-25 00:13 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>
On Mon, Oct 14, 2024 at 10:20 PM David Rowley <[email protected]> wrote:
> Yeah, I think any effort to change the default value for this setting
> would require some analysis to prove that the newly proposed default
> is a more suitable setting than the current default. I mean, why 1.2 and
> not 1.1 or 1.3? Where's the evidence that 1.2 is the best value
> for this?
>
As I said, I was just throwing that 1.2 number out there. It felt right,
although perhaps a tad high (which seems right as we keep things very
conservative). I agree we should make a best effort to have an accurate,
defendable default. We all know (I hope) that 4.0 is wrong for SSDs.
> I don't think just providing evidence that random read times are closer to
> sequential read times on SSDs are closer than they are with
> HDDs is going to be enough.
...
> It would be nice to have this as a script so that other people could
> easily run it on their hardware to ensure that random_page_cost we
> choose as the new default is representative of the average hardware.
Heh, this is starting to feel like belling the cat (see
https://fablesofaesop.com/belling-the-cat.html)
Remember this is still just a default, and we should encourage people to
tweak it themselves based on their own workloads. I just want people to
start in the right neighborhood. I'll see about working on some more
research / generating a script, but help from others is more than welcome.
Cheers,
Greg
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Changing the default random_page_cost value
2024-10-14 21:15 Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
@ 2024-10-25 00:01 ` Greg Sabino Mullane <[email protected]>
2024-10-31 17:43 ` Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Greg Sabino Mullane @ 2024-10-25 00:01 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers
On Mon, Oct 14, 2024 at 5:15 PM Bruce Momjian <[email protected]> wrote:
> I am not a fan of this patch. I don't see why _removing_ the magnetic
> part helps because you then have no logic for any 1.2 was chosen.
Okay, but we have no documented logic on why 4.0 was chosen either. :)
I would put the magnetic in a separate paragraph perhaps, and recommend
> 4.0 for it.
Sounds doable. Even in the pre-SSD age I recall lowering this as a fairly
standard practice, but I'm fine with a recommendation of 4. Partly because
I doubt anyone will use it much.
Also, per-tablespace makes sense because of physical media
> differences, but what purpose would per-database and per-role serve?
> Also, per-tablespace is not a connection-activated item like the other
> two.
>
Good point, I withdraw that part.
Cheers,
Greg
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Changing the default random_page_cost value
2024-10-14 21:15 Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
2024-10-25 00:01 ` Re: Changing the default random_page_cost value Greg Sabino Mullane <[email protected]>
@ 2024-10-31 17:43 ` Bruce Momjian <[email protected]>
2024-10-31 17:53 ` Re: Changing the default random_page_cost value Tom Lane <[email protected]>
2024-11-01 13:54 ` Re: Changing the default random_page_cost value Greg Sabino Mullane <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Bruce Momjian @ 2024-10-31 17:43 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers
On Thu, Oct 24, 2024 at 08:01:11PM -0400, Greg Sabino Mullane wrote:
> On Mon, Oct 14, 2024 at 5:15 PM Bruce Momjian <[email protected]> wrote:
>
> I am not a fan of this patch. I don't see why _removing_ the magnetic
> part helps because you then have no logic for any 1.2 was chosen.
>
>
> Okay, but we have no documented logic on why 4.0 was chosen either. :)
Uh, we do, and it is in the docs:
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.
You surely saw this when you created the patch and removed the text.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Changing the default random_page_cost value
2024-10-14 21:15 Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
2024-10-25 00:01 ` Re: Changing the default random_page_cost value Greg Sabino Mullane <[email protected]>
2024-10-31 17:43 ` Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
@ 2024-10-31 17:53 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2024-10-31 17:53 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers
Bruce Momjian <[email protected]> writes:
> On Thu, Oct 24, 2024 at 08:01:11PM -0400, Greg Sabino Mullane wrote:
>> Okay, but we have no documented logic on why 4.0 was chosen either. :)
> Uh, we do, and it is in the docs:
> 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.
Meh. Reality is that that is somebody's long-after-the-fact apologia
for a number that was obtained by experimentation.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Changing the default random_page_cost value
2024-10-14 21:15 Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
2024-10-25 00:01 ` Re: Changing the default random_page_cost value Greg Sabino Mullane <[email protected]>
2024-10-31 17:43 ` Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
@ 2024-11-01 13:54 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Greg Sabino Mullane @ 2024-11-01 13:54 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers
On Thu, Oct 31, 2024 at 1:43 PM Bruce Momjian <[email protected]> wrote:
> > I am not a fan of this patch. I don't see why _removing_ the
> magnetic
> > part helps because you then have no logic for any 1.2 was chosen.
> >
> > Okay, but we have no documented logic on why 4.0 was chosen either. :)
>
> Uh, we do, and it is in the docs:
>
> 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.
>
> You surely saw this when you created the patch and removed the text.
>
Yes, I did, but there is no documentation to support those numbers. Is it
really 40 times slower? Which hard disks, in what year? Where did the 90%
come from, and is that really an accurate average for production systems
with multiple caching layers? I know Tom ran actual tests many years ago,
but at the end of the day, all of these numbers will vary wildly depending
on a host of factors, so we have to make some educated guesses.
I guess my point was that my 1.2 (based on many years of tuning many client
systems) seems no less imprecise than 4.0 (based on actual tests many years
ago, with hardware that has changed a lot), for a default value that people
should tune for their specific system anyway.
Maybe these new tests people are asking for in this thread to determine a
better default rpc for SSDs can also help us determine a better rpc for HDs
as well.
Cheers,
Greg
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2024-11-01 13:54 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-18 18:37 [PATCH v39] postgres_fdw: Start using new libpq cancel APIs Jelte Fennema-Nio <[email protected]>
2024-10-14 21:15 Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
2024-10-15 02:20 ` Re: Changing the default random_page_cost value David Rowley <[email protected]>
2024-10-15 02:38 ` Re: Changing the default random_page_cost value Tom Lane <[email protected]>
2024-10-15 03:03 ` Re: Changing the default random_page_cost value Tom Lane <[email protected]>
2024-10-25 00:13 ` Re: Changing the default random_page_cost value Greg Sabino Mullane <[email protected]>
2024-10-25 00:01 ` Re: Changing the default random_page_cost value Greg Sabino Mullane <[email protected]>
2024-10-31 17:43 ` Re: Changing the default random_page_cost value Bruce Momjian <[email protected]>
2024-10-31 17:53 ` Re: Changing the default random_page_cost value Tom Lane <[email protected]>
2024-11-01 13:54 ` Re: Changing the default random_page_cost value Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox