From c63172d60f242ad3581c83723a5b315bbe547a0e Mon Sep 17 00:00:00 2001 From: Kevin Grittner Date: Fri, 7 Apr 2017 21:38:05 -0500 Subject: [PATCH] Add GUCs for predicate lock promotion thresholds. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Defaults match the fixed behavior of prior releases, but now DBAs have better options to tune serializable workloads. It might be nice to be able to set this per relation, but that part will need to wait for another release. Author: Dagfinn Ilmari Mannsåker --- doc/src/sgml/config.sgml | 36 ++++++++++++++ doc/src/sgml/mvcc.sgml | 4 +- src/backend/storage/lmgr/predicate.c | 48 ++++++++++++------- src/backend/utils/misc/guc.c | 22 +++++++++ src/backend/utils/misc/postgresql.conf.sample | 4 ++ src/include/storage/predicate.h | 2 + 6 files changed, 98 insertions(+), 18 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index ac339fb566..744c5e8f37 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7337,7 +7337,43 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' tables in a single serializable transaction. This parameter can only be set at server start. + + + + + max_pred_locks_per_relation (integer) + + max_pred_locks_per_relation configuration parameter + + + + + This controls how many pages or tuples of a single relation can be + predicate-locked before the lock is promoted to covering the whole + relation. Values greater than or equal to zero mean an absolute + limit, while negative values + mean divided by + the absolute value of this setting. The default is -2, which keeps + the behaviour from previous versions of PostgreSQL. + This parameter can only be set in the postgresql.conf + file or on the server command line. + + + + + max_pred_locks_per_page (integer) + + max_pred_locks_per_page configuration parameter + + + + + This controls how many rows on a single page can be predicate-locked + before the lock is promoted to covering the whole page. The default + is 2. This parameter can only be set in + the postgresql.conf file or on the server command line. + diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index 7aa32932fa..dda0170886 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -765,7 +765,9 @@ ERROR: could not serialize access due to read/write dependencies among transact locks into a single relation-level predicate lock because the predicate lock table is short of memory, an increase in the rate of serialization failures may occur. You can avoid this by increasing - . + , + , and/or + . diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c index 7aa719d612..10bac71e94 100644 --- a/src/backend/storage/lmgr/predicate.c +++ b/src/backend/storage/lmgr/predicate.c @@ -352,8 +352,15 @@ static OldSerXidControl oldSerXidControl; static SERIALIZABLEXACT *OldCommittedSxact; -/* This configuration variable is used to set the predicate lock table size */ +/* + * These configuration variables are used to set the predicate lock table size + * and to control promotion of predicate locks to coarser granularity in an + * attempt to degrade performance (mostly as false positive serialization + * failure) gracefully in the face of memory pressurel + */ int max_predicate_locks_per_xact; /* set by guc.c */ +int max_predicate_locks_per_relation; /* set by guc.c */ +int max_predicate_locks_per_page; /* set by guc.c */ /* * This provides a list of objects in order to track transactions @@ -437,7 +444,7 @@ static void RestoreScratchTarget(bool lockheld); static void RemoveTargetIfNoLongerUsed(PREDICATELOCKTARGET *target, uint32 targettaghash); static void DeleteChildTargetLocks(const PREDICATELOCKTARGETTAG *newtargettag); -static int PredicateLockPromotionThreshold(const PREDICATELOCKTARGETTAG *tag); +static int MaxPredicateChildLocks(const PREDICATELOCKTARGETTAG *tag); static bool CheckAndPromotePredicateLockRequest(const PREDICATELOCKTARGETTAG *reqtag); static void DecrementParentLocks(const PREDICATELOCKTARGETTAG *targettag); static void CreatePredicateLock(const PREDICATELOCKTARGETTAG *targettag, @@ -2118,28 +2125,35 @@ DeleteChildTargetLocks(const PREDICATELOCKTARGETTAG *newtargettag) } /* - * Returns the promotion threshold for a given predicate lock - * target. This is the number of descendant locks required to promote - * to the specified tag. Note that the threshold includes non-direct - * descendants, e.g. both tuples and pages for a relation lock. + * Returns the promotion limit for a given predicate lock target. This is the + * max number of descendant locks allowed before promoting to the specified + * tag. Note that the limit includes non-direct descendants (e.g., both tuples + * and pages for a relation lock). + * + * Currently the default limit is 2 for a page lock, and half of the value of + * max_pred_locks_per_transaction - 1 for a relation lock, to match behavior + * of earlier releases when upgrading. * - * TODO SSI: We should do something more intelligent about what the - * thresholds are, either making it proportional to the number of - * tuples in a page & pages in a relation, or at least making it a - * GUC. Currently the threshold is 3 for a page lock, and - * max_pred_locks_per_transaction/2 for a relation lock, chosen - * entirely arbitrarily (and without benchmarking). + * TODO SSI: We should probably add additional GUCs to allow a maximum ratio + * of page and tuple locks based on the pages in a relation, and the maximum + * ratio of tuple locks to tuples in a page. This would provide more + * generally "balanced" allocation of locks to where they are most useful, + * while still allowing the absolute numbers to prevent one relation from + * tying up all predicate lock resources. */ static int -PredicateLockPromotionThreshold(const PREDICATELOCKTARGETTAG *tag) +MaxPredicateChildLocks(const PREDICATELOCKTARGETTAG *tag) { switch (GET_PREDICATELOCKTARGETTAG_TYPE(*tag)) { case PREDLOCKTAG_RELATION: - return max_predicate_locks_per_xact / 2; + return max_predicate_locks_per_relation < 0 + ? (max_predicate_locks_per_xact + / (-max_predicate_locks_per_relation)) - 1 + : max_predicate_locks_per_relation; case PREDLOCKTAG_PAGE: - return 3; + return max_predicate_locks_per_page; case PREDLOCKTAG_TUPLE: @@ -2194,8 +2208,8 @@ CheckAndPromotePredicateLockRequest(const PREDICATELOCKTARGETTAG *reqtag) else parentlock->childLocks++; - if (parentlock->childLocks >= - PredicateLockPromotionThreshold(&targettag)) + if (parentlock->childLocks > + MaxPredicateChildLocks(&targettag)) { /* * We should promote to this parent lock. Continue to check its diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index a57b175b2d..19d258d033 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -2199,6 +2199,28 @@ static struct config_int ConfigureNamesInt[] = NULL, NULL, NULL }, + { + {"max_pred_locks_per_relation", PGC_SIGHUP, LOCK_MANAGEMENT, + gettext_noop("Sets the maximum number of predicate-locked pages and tuples per relation."), + gettext_noop("If more than this total of pages and tuples in the same relation are locked " + "by a connection, those locks are replaced by a relation level lock.") + }, + &max_predicate_locks_per_relation, + -2, INT_MIN, INT_MAX, + NULL, NULL, NULL + }, + + { + {"max_pred_locks_per_page", PGC_SIGHUP, LOCK_MANAGEMENT, + gettext_noop("Sets the maximum number of predicate-locked tuples per page."), + gettext_noop("If more than this number of tuples on the same page are locked " + "by a connection, those locks are replaced by a page level lock.") + }, + &max_predicate_locks_per_page, + 2, 0, INT_MAX, + NULL, NULL, NULL + }, + { {"authentication_timeout", PGC_SIGHUP, CONN_AUTH_SECURITY, gettext_noop("Sets the maximum allowed time to complete client authentication."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 8a93bdcb3f..512be0a92e 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -593,6 +593,10 @@ # (change requires restart) #max_pred_locks_per_transaction = 64 # min 10 # (change requires restart) +#max_pred_locks_per_relation = -2 # negative values mean + # (max_pred_locks_per_transaction + # / -max_pred_locks_per_relation) - 1 +#max_pred_locks_per_page = 2 # min 0 #------------------------------------------------------------------------------ diff --git a/src/include/storage/predicate.h b/src/include/storage/predicate.h index f996d8e818..8f9ea29917 100644 --- a/src/include/storage/predicate.h +++ b/src/include/storage/predicate.h @@ -22,6 +22,8 @@ * GUC variables */ extern int max_predicate_locks_per_xact; +extern int max_predicate_locks_per_relation; +extern int max_predicate_locks_per_page; /* Number of SLRU buffers to use for predicate locking */ -- 2.40.0