public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Jakub Wartak <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Andres Freund <[email protected]>
Subject: Re: scalability bottlenecks with (many) partitions (and more)
Date: Tue, 17 Sep 2024 22:16:04 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CA+TgmoabtPvTffKQiqKg9qOmyt-sYzjQW+TyFWf1CBN7M4hggQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CA+TgmoZ-40G7-WoLSsEH+7FqUzrP_0ndCOkewJ7ifAO6MVx0EA@mail.gmail.com>
<[email protected]>
<CA+TgmobCBse3LYj9pfh-ELbP0VnffQS7jP_vNTGA7-KZP0gqDw@mail.gmail.com>
<[email protected]>
<CA+TgmoamAOj5e4J7WcC9g10AskvVPk5D7JoAvWw8DFnpHMiSQA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAKZiRmze8L6FeAKNQhZkn4NmUH2WZZyQ46GUm-Xuky5qr5DUoA@mail.gmail.com>
<[email protected]>
I've spent the last couple days doing all kinds of experiments trying to
find regressions caused by the patch, but no success. Which is good.
Attached is a script that just does a simple pgbench on a tiny table,
with no or very few partitions. The idea is that this will will fit into
shared buffers (thus no I/O), and will fit into the 16 fast-path slots
we have now. It can't benefit from the patch - it can only get worse, if
having more fast-path slots hurts.
I ran this on my two machines, and in both cases the results are +/- 1%
from the master for all combinations of parameters (clients, mode,
number of partitions, ..). In most cases it's actually much closer,
particularly with the default max_locks_per_transaction value.
For higher values of the GUC, I think it's fine too - the differences
are perhaps a bit larger (~1.5%), but it's clearly hardware specific (i5
gets a bit faster, xeon a bit slower). And I'm pretty sure people who
increased that GUC value likely did that because of locking many rels,
and so will actually benefit from the increased fast-path capacity.
At this point I'm pretty happy and confident the patch is fine. Unless
someone objects, I'll get it committed after going over over it one more
time. I decided to commit that as as a single change - it would be weird
to have an intermediate state with larger arrays in PGPROC, when that's
not something we actually want.
I still haven't found any places in the docs that should mention this,
except for the bit about max_locks_per_transaction GUC. There's nothing
in SGML mentioning details of fast-path locking. I thought we have some
formula to calculate per-connection memory, but I think I confused that
with the shmmem formulas we had in "Managing Kernel Resources". But even
that no longer mentions max_connections in master.
regards
--
Tomas Vondra
Attachments:
[application/x-shellscript] lock-test.sh (1.5K, ../[email protected]/2-lock-test.sh)
download
[application/pdf] lock-test.pdf (13.9K, ../[email protected]/3-lock-test.pdf)
download
[application/vnd.oasis.opendocument.spreadsheet] lock-test.ods (96.3K, ../[email protected]/4-lock-test.ods)
download
view thread (34+ messages) latest in thread
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], [email protected], [email protected], [email protected]
Subject: Re: scalability bottlenecks with (many) partitions (and more)
In-Reply-To: <[email protected]>
* 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