public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected] <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Fix the description of GUC "max_locks_per_transaction" and "max_pred_locks_per_transaction" in guc_table.c
Date: Wed, 22 Feb 2023 12:40:07 +0000
Message-ID: <OSZPR01MB627839B978B5735773D864FD9EAA9@OSZPR01MB6278.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <20230222003716.GA8100@nathanxps13>
References: <OS3PR01MB6275BDD09C9B875C65FCC5AB9EA39@OS3PR01MB6275.jpnprd01.prod.outlook.com>
<20230222003716.GA8100@nathanxps13>
On Wed, Feb 22, 2023 at 8:37 AM Nathan Bossart <[email protected]> wrote:
> On Wed, Feb 15, 2023 at 08:16:43AM +0000, [email protected] wrote:
> > When I refer to the GUC "max_locks_per_transaction", I find that the
> description
> > of the shared lock table size in pg-doc[1] is inconsistent with the code
> > (guc_table.c). BTW, the GUC "max_predicate_locks_per_xact" has similar
> problems.
> >
> > I think the descriptions in pg-doc are correct.
> > - GUC "max_locks_per_transaction"
> > Please refer to the macro "NLOCKENTS" used to obtain max_table_size in the
> > function InitLocks.
> >
> > - GUC "max_predicate_locks_per_xact"
> > Please refer to the macro "NPREDICATELOCKTARGETENTS" used to obtain
> > max_table_size in the function InitPredicateLocks.
>
> The GUC description for max_locks_per_transaction was first added in
> b700a67 (July 2003). Neither the GUC description nor the documentation was
> updated when max_prepared_transactions was introduced in d0a8968 (July
> 2005). However, the documentation was later fixed via 78ef2d3 (August
> 2005). It looks like the GUC description for
> max_predicate_locks_per_transaction was wrong from the start. In dafaa3e
> (February 2011), the GUC description does not include
> max_prepared_transactions, but the documentation does.
>
> It's interesting that the documentation cites max_connections, as the
> tables are sized using MaxBackends, which includes more than just
> max_connections (e.g., autovacuum_max_workers, max_worker_processes,
> max_wal_senders). After some digging, I see that MaxBackends was the
> original variable used for max_connections (which was called max_backends
> until 648677c (July 2000)), and it wasn't until autovacuum_max_workers was
> introduced in e2a186b (April 2007) before max_connections got its own
> MaxConnections variable and started diverging from MaxBackends.
>
> So, even with your patch applied, I don't think the formulas are correct.
> I don't know if it's worth writing out the exact formula, though. It
> doesn't seem to be kept up-to-date, and I don't know if users would choose
> different values for max_locks_per_transaction if it _was_ updated.
> Perhaps max_connections is a good enough approximation of MaxBackends most
> of the time...
Thanks very much for your careful review.
Yes, you are right. I think the formulas in the v1 patch are all approximations.
I think the exact formula (see function InitializeMaxBackends) is:
```
max_locks_per_transaction * (max_connections + autovacuum_max_workers + 1 +
max_worker_processes + max_wal_senders +
max_prepared_transactions)
```
After some rethinking, I think users can easily get exact value according to
exact formula, and I think using accurate formula can help users adjust
max_locks_per_transaction or max_predicate_locks_per_transaction if needed. So,
I used the exact formulas in the attached v2 patch.
Regards,
Wang Wei
Attachments:
[application/octet-stream] v2-0001-Fix-the-description-of-shared-lock-table-size-and.patch (3.5K, ../OSZPR01MB627839B978B5735773D864FD9EAA9@OSZPR01MB6278.jpnprd01.prod.outlook.com/2-v2-0001-Fix-the-description-of-shared-lock-table-size-and.patch)
download | inline diff:
From d1e51b531a80eb80a6d8ae17a78577ee98d641a0 Mon Sep 17 00:00:00 2001
From: Wang Wei <[email protected]>
Date: Wed, 15 Feb 2023 15:25:59 +0800
Subject: [PATCH v2] Fix the description of shared lock table size and shared
predicate lock table size
---
doc/src/sgml/config.sgml | 6 ++++++
src/backend/utils/misc/guc_tables.c | 10 ++++++----
2 files changed, 12 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index ecd9aa73ef..d134988dfc 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10150,6 +10150,9 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
The shared lock table tracks locks on
<varname>max_locks_per_transaction</varname> * (<xref
linkend="guc-max-connections"/> + <xref
+ linkend="guc-autovacuum-max-workers"/> + 1 + <xref
+ linkend="guc-max-worker-processes"/> + <xref
+ linkend="guc-max-wal-senders"/> + <xref
linkend="guc-max-prepared-transactions"/>) objects (e.g., tables);
hence, no more than this many distinct objects can be locked at
any one time. This parameter controls the average number of object
@@ -10182,6 +10185,9 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
The shared predicate lock table tracks locks on
<varname>max_pred_locks_per_transaction</varname> * (<xref
linkend="guc-max-connections"/> + <xref
+ linkend="guc-autovacuum-max-workers"/> + 1 + <xref
+ linkend="guc-max-worker-processes"/> + <xref
+ linkend="guc-max-wal-senders"/> + <xref
linkend="guc-max-prepared-transactions"/>) objects (e.g., tables);
hence, no more than this many distinct objects can be locked at
any one time. This parameter controls the average number of object
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 1c0583fe26..1460ca2f8f 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2571,8 +2571,9 @@ struct config_int ConfigureNamesInt[] =
{"max_locks_per_transaction", PGC_POSTMASTER, LOCK_MANAGEMENT,
gettext_noop("Sets the maximum number of locks per transaction."),
gettext_noop("The shared lock table is sized on the assumption that "
- "at most max_locks_per_transaction * max_connections distinct "
- "objects will need to be locked at any one time.")
+ "at most max_locks_per_transaction * (max_connections + autovacuum_max_workers + 1 "
+ "+ max_worker_processes + max_wal_senders + max_prepared_transactions) "
+ "distinct objects will need to be locked at any one time.")
},
&max_locks_per_xact,
64, 10, INT_MAX,
@@ -2583,8 +2584,9 @@ struct config_int ConfigureNamesInt[] =
{"max_pred_locks_per_transaction", PGC_POSTMASTER, LOCK_MANAGEMENT,
gettext_noop("Sets the maximum number of predicate locks per transaction."),
gettext_noop("The shared predicate lock table is sized on the assumption that "
- "at most max_pred_locks_per_transaction * max_connections distinct "
- "objects will need to be locked at any one time.")
+ "at most max_pred_locks_per_transaction * (max_connections + autovacuum_max_workers + 1 "
+ "+ max_worker_processes + max_wal_senders + max_prepared_transactions) "
+ "distinct objects will need to be locked at any one time.")
},
&max_predicate_locks_per_xact,
64, 10, INT_MAX,
--
2.39.1.windows.1
view thread (8+ 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]
Subject: RE: Fix the description of GUC "max_locks_per_transaction" and "max_pred_locks_per_transaction" in guc_table.c
In-Reply-To: <OSZPR01MB627839B978B5735773D864FD9EAA9@OSZPR01MB6278.jpnprd01.prod.outlook.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