Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc63172d

Browse files
committed
Add GUCs for predicate lock promotion thresholds.
Defaults match the fixed behavior of prior releases, but now DBAshave better options to tune serializable workloads.It might be nice to be able to set this per relation, but that partwill need to wait for another release.Author: Dagfinn Ilmari Mannsåker
1 parent9c7f522 commitc63172d

File tree

6 files changed

+98
-18
lines changed

6 files changed

+98
-18
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7337,7 +7337,43 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
73377337
tables in a single serializable transaction. This parameter can
73387338
only be set at server start.
73397339
</para>
7340+
</listitem>
7341+
</varlistentry>
7342+
7343+
<varlistentry id="guc-max-pred-locks-per-relation" xreflabel="max_pred_locks_per_relation">
7344+
<term><varname>max_pred_locks_per_relation</varname> (<type>integer</type>)
7345+
<indexterm>
7346+
<primary><varname>max_pred_locks_per_relation</> configuration parameter</primary>
7347+
</indexterm>
7348+
</term>
7349+
<listitem>
7350+
<para>
7351+
This controls how many pages or tuples of a single relation can be
7352+
predicate-locked before the lock is promoted to covering the whole
7353+
relation. Values greater than or equal to zero mean an absolute
7354+
limit, while negative values
7355+
mean <xref linkend="guc-max-pred-locks-per-transaction"> divided by
7356+
the absolute value of this setting. The default is -2, which keeps
7357+
the behaviour from previous versions of <productname>PostgreSQL</>.
7358+
This parameter can only be set in the <filename>postgresql.conf</>
7359+
file or on the server command line.
7360+
</para>
7361+
</listitem>
7362+
</varlistentry>
73407363

7364+
<varlistentry id="guc-max-pred-locks-per-page" xreflabel="max_pred_locks_per_page">
7365+
<term><varname>max_pred_locks_per_page</varname> (<type>integer</type>)
7366+
<indexterm>
7367+
<primary><varname>max_pred_locks_per_page</> configuration parameter</primary>
7368+
</indexterm>
7369+
</term>
7370+
<listitem>
7371+
<para>
7372+
This controls how many rows on a single page can be predicate-locked
7373+
before the lock is promoted to covering the whole page. The default
7374+
is 2. This parameter can only be set in
7375+
the <filename>postgresql.conf</> file or on the server command line.
7376+
</para>
73417377
</listitem>
73427378
</varlistentry>
73437379

‎doc/src/sgml/mvcc.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -765,7 +765,9 @@ ERROR: could not serialize access due to read/write dependencies among transact
765765
locks into a single relation-level predicate lock because the predicate
766766
lock table is short of memory, an increase in the rate of serialization
767767
failures may occur. You can avoid this by increasing
768-
<xref linkend="guc-max-pred-locks-per-transaction">.
768+
<xref linkend="guc-max-pred-locks-per-transaction">,
769+
<xref linkend="guc-max-pred-locks-per-relation">, and/or
770+
<xref linkend="guc-max-pred-locks-per-page">.
769771
</para>
770772
</listitem>
771773
<listitem>

‎src/backend/storage/lmgr/predicate.c

Lines changed: 31 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -352,8 +352,15 @@ static OldSerXidControl oldSerXidControl;
352352
staticSERIALIZABLEXACT*OldCommittedSxact;
353353

354354

355-
/* This configuration variable is used to set the predicate lock table size */
355+
/*
356+
* These configuration variables are used to set the predicate lock table size
357+
* and to control promotion of predicate locks to coarser granularity in an
358+
* attempt to degrade performance (mostly as false positive serialization
359+
* failure) gracefully in the face of memory pressurel
360+
*/
356361
intmax_predicate_locks_per_xact;/* set by guc.c */
362+
intmax_predicate_locks_per_relation;/* set by guc.c */
363+
intmax_predicate_locks_per_page;/* set by guc.c */
357364

358365
/*
359366
* This provides a list of objects in order to track transactions
@@ -437,7 +444,7 @@ static void RestoreScratchTarget(bool lockheld);
437444
staticvoidRemoveTargetIfNoLongerUsed(PREDICATELOCKTARGET*target,
438445
uint32targettaghash);
439446
staticvoidDeleteChildTargetLocks(constPREDICATELOCKTARGETTAG*newtargettag);
440-
staticintPredicateLockPromotionThreshold(constPREDICATELOCKTARGETTAG*tag);
447+
staticintMaxPredicateChildLocks(constPREDICATELOCKTARGETTAG*tag);
441448
staticboolCheckAndPromotePredicateLockRequest(constPREDICATELOCKTARGETTAG*reqtag);
442449
staticvoidDecrementParentLocks(constPREDICATELOCKTARGETTAG*targettag);
443450
staticvoidCreatePredicateLock(constPREDICATELOCKTARGETTAG*targettag,
@@ -2118,28 +2125,35 @@ DeleteChildTargetLocks(const PREDICATELOCKTARGETTAG *newtargettag)
21182125
}
21192126

21202127
/*
2121-
* Returns the promotion threshold for a given predicate lock
2122-
* target. This is the number of descendant locks required to promote
2123-
* to the specified tag. Note that the threshold includes non-direct
2124-
* descendants, e.g. both tuples and pages for a relation lock.
2128+
* Returns the promotion limit for a given predicate lock target. This is the
2129+
* max number of descendant locks allowed before promoting to the specified
2130+
* tag. Note that the limit includes non-direct descendants (e.g., both tuples
2131+
* and pages for a relation lock).
2132+
*
2133+
* Currently the default limit is 2 for a page lock, and half of the value of
2134+
* max_pred_locks_per_transaction - 1 for a relation lock, to match behavior
2135+
* of earlier releases when upgrading.
21252136
*
2126-
* TODO SSI: We shoulddo something more intelligent about what the
2127-
*thresholds are, either making it proportional to thenumber of
2128-
*tuples in a page & pages in arelation, or at least making it a
2129-
*GUC. Currently the threshold is 3 for a page lock, and
2130-
*max_pred_locks_per_transaction/2 for a relation lock, chosen
2131-
*entirely arbitrarily (and without benchmarking).
2137+
* TODO SSI: We shouldprobably add additional GUCs to allow a maximum ratio
2138+
*of page and tuple locks based on thepages in a relation, and the maximum
2139+
*ratio of tuple locks to tuples in apage. This would provide more
2140+
*generally "balanced" allocation of locks to where they are most useful,
2141+
*while still allowing the absolute numbers to prevent one relation from
2142+
*tying up all predicate lock resources.
21322143
*/
21332144
staticint
2134-
PredicateLockPromotionThreshold(constPREDICATELOCKTARGETTAG*tag)
2145+
MaxPredicateChildLocks(constPREDICATELOCKTARGETTAG*tag)
21352146
{
21362147
switch (GET_PREDICATELOCKTARGETTAG_TYPE(*tag))
21372148
{
21382149
casePREDLOCKTAG_RELATION:
2139-
returnmax_predicate_locks_per_xact /2;
2150+
returnmax_predicate_locks_per_relation<0
2151+
? (max_predicate_locks_per_xact
2152+
/ (-max_predicate_locks_per_relation))-1
2153+
:max_predicate_locks_per_relation;
21402154

21412155
casePREDLOCKTAG_PAGE:
2142-
return3;
2156+
returnmax_predicate_locks_per_page;
21432157

21442158
casePREDLOCKTAG_TUPLE:
21452159

@@ -2194,8 +2208,8 @@ CheckAndPromotePredicateLockRequest(const PREDICATELOCKTARGETTAG *reqtag)
21942208
else
21952209
parentlock->childLocks++;
21962210

2197-
if (parentlock->childLocks >=
2198-
PredicateLockPromotionThreshold(&targettag))
2211+
if (parentlock->childLocks>
2212+
MaxPredicateChildLocks(&targettag))
21992213
{
22002214
/*
22012215
* We should promote to this parent lock. Continue to check its

‎src/backend/utils/misc/guc.c

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2199,6 +2199,28 @@ static struct config_int ConfigureNamesInt[] =
21992199
NULL,NULL,NULL
22002200
},
22012201

2202+
{
2203+
{"max_pred_locks_per_relation",PGC_SIGHUP,LOCK_MANAGEMENT,
2204+
gettext_noop("Sets the maximum number of predicate-locked pages and tuples per relation."),
2205+
gettext_noop("If more than this total of pages and tuples in the same relation are locked "
2206+
"by a connection, those locks are replaced by a relation level lock.")
2207+
},
2208+
&max_predicate_locks_per_relation,
2209+
-2,INT_MIN,INT_MAX,
2210+
NULL,NULL,NULL
2211+
},
2212+
2213+
{
2214+
{"max_pred_locks_per_page",PGC_SIGHUP,LOCK_MANAGEMENT,
2215+
gettext_noop("Sets the maximum number of predicate-locked tuples per page."),
2216+
gettext_noop("If more than this number of tuples on the same page are locked "
2217+
"by a connection, those locks are replaced by a page level lock.")
2218+
},
2219+
&max_predicate_locks_per_page,
2220+
2,0,INT_MAX,
2221+
NULL,NULL,NULL
2222+
},
2223+
22022224
{
22032225
{"authentication_timeout",PGC_SIGHUP,CONN_AUTH_SECURITY,
22042226
gettext_noop("Sets the maximum allowed time to complete client authentication."),

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -593,6 +593,10 @@
593593
# (change requires restart)
594594
#max_pred_locks_per_transaction = 64# min 10
595595
# (change requires restart)
596+
#max_pred_locks_per_relation = -2# negative values mean
597+
# (max_pred_locks_per_transaction
598+
# / -max_pred_locks_per_relation) - 1
599+
#max_pred_locks_per_page = 2 # min 0
596600

597601

598602
#------------------------------------------------------------------------------

‎src/include/storage/predicate.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,8 @@
2222
* GUC variables
2323
*/
2424
externintmax_predicate_locks_per_xact;
25+
externintmax_predicate_locks_per_relation;
26+
externintmax_predicate_locks_per_page;
2527

2628

2729
/* Number of SLRU buffers to use for predicate locking */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp