Hope this patch will be usefull/
On Mon, Sep 18, 2023 at 5:47 PM Sergey Sergey <[email protected]> wrote:
> While replaying our production workload we have found Postgres spending a
> lot of time inside TimescaleDB planner. The planner itself need an
> information about whether a table involved is a TimescaleDB hypertable. So
> planner need an access to TimescaleDB internal metainformation tables. This
> planner access become extremely slow when you have a lot of tables involved
> and you are beyond fast-path lock limit
>
> This humble example makes only 2330 tps on REL_15_STABLE but 27041tps on
> patched version with 64 slots for fast-path locks.
>
> \set bid random(1,1000)
>
> BEGIN;
> select bbalance from pgbench_branches where bid = :bid
> UNION
> select bbalance from pgbench_branches2 where bid = :bid
> UNION
> select bbalance from pgbench_branches3 where bid = :bid
> UNION
> select bbalance from pgbench_branches4 where bid = :bid
> UNION
> select bbalance from pgbench_branches5 where bid = :bid
> UNION
> select bbalance from pgbench_branches6 where bid = :bid
> UNION
> select bbalance from pgbench_branches7 where bid = :bid
> UNION
> select bbalance from pgbench_branches8 where bid = :bid
> UNION
> select bbalance from pgbench_branches9 where bid = :bid
> UNION
> select bbalance from pgbench_branches10 where bid = :bid
> UNION
> select bbalance from pgbench_branches11 where bid = :bid
> UNION
> select bbalance from pgbench_branches12 where bid = :bid
> UNION
> select bbalance from pgbench_branches13 where bid = :bid
> UNION
> select bbalance from pgbench_branches14 where bid = :bid
> UNION
> select bbalance from pgbench_branches15 where bid = :bid
> UNION
> select bbalance from pgbench_branches16 where bid = :bid
> UNION
> select bbalance from pgbench_branches17 where bid = :bid
> UNION
> select bbalance from pgbench_branches18 where bid = :bid
> UNION
> select bbalance from pgbench_branches19 where bid = :bid
> UNION
> select bbalance from pgbench_branches20 where bid = :bid;
> END;
>
> First i try to make the number of fast-path locks as a GUC parameter. But
> it implies a lot of changes with PGPROC structure. Next I implement it as a
> compile-time parameter.
>
diff --git a/home/nikor/tmp/postgres/configure.ac b/configure.ac
index c216ac4447..558e17273c 100644
--- a/home/nikor/tmp/postgres/configure.ac
+++ b/configure.ac
@@ -283,6 +283,23 @@ AC_DEFINE_UNQUOTED([BLCKSZ], ${BLCKSZ}, [
Changing BLCKSZ requires an initdb.
])
+#
+# Fastlock size
+#
+AC_MSG_CHECKING([for fastlock size])
+PGAC_ARG_REQ(with, fastlocksize, [FASTLOCKSIZE], [set fastlock array size [16]],
+ [fastlocksize=$withval],
+ [fastlocksize=16])
+AC_MSG_RESULT([${fastlocksize}])
+
+AC_DEFINE_UNQUOTED([FP_LOCK_SLOTS_PER_BACKEND], ${fastlocksize}, [
+ Size of a fastlock slots. Large values can help when transaction
+ touch a lot of objects.
+
+ Changes requires restart.
+
+])
+
#
# Relation segment size
#
diff --git a/home/nikor/tmp/postgres/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index 86fc5576af..81e0d5f839 100644
--- a/home/nikor/tmp/postgres/src/backend/storage/lmgr/lock.c
+++ b/src/backend/storage/lmgr/lock.c
@@ -191,18 +191,18 @@ static bool IsRelationExtensionLockHeld PG_USED_FOR_ASSERTS_ONLY = false;
#define FAST_PATH_LOCKNUMBER_OFFSET 1
#define FAST_PATH_MASK ((1 << FAST_PATH_BITS_PER_SLOT) - 1)
#define FAST_PATH_GET_BITS(proc, n) \
- (((proc)->fpLockBits >> (FAST_PATH_BITS_PER_SLOT * n)) & FAST_PATH_MASK)
+ ((proc)->fpLockBits[n] & FAST_PATH_MASK)
#define FAST_PATH_BIT_POSITION(n, l) \
(AssertMacro((l) >= FAST_PATH_LOCKNUMBER_OFFSET), \
AssertMacro((l) < FAST_PATH_BITS_PER_SLOT+FAST_PATH_LOCKNUMBER_OFFSET), \
AssertMacro((n) < FP_LOCK_SLOTS_PER_BACKEND), \
- ((l) - FAST_PATH_LOCKNUMBER_OFFSET + FAST_PATH_BITS_PER_SLOT * (n)))
+ ((l) - FAST_PATH_LOCKNUMBER_OFFSET ))
#define FAST_PATH_SET_LOCKMODE(proc, n, l) \
- (proc)->fpLockBits |= UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l)
+ (proc)->fpLockBits[n] |= UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l)
#define FAST_PATH_CLEAR_LOCKMODE(proc, n, l) \
- (proc)->fpLockBits &= ~(UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l))
+ (proc)->fpLockBits[n] &= ~(UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l))
#define FAST_PATH_CHECK_LOCKMODE(proc, n, l) \
- ((proc)->fpLockBits & (UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l)))
+ ((proc)->fpLockBits[n] & (UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l)))
/*
* The fast-path lock mechanism is concerned only with relation locks on
diff --git a/home/nikor/tmp/postgres/src/include/pg_config.h.in b/src/include/pg_config.h.in
index d09e9f9a1c..c8fc4f9e3f 100644
--- a/home/nikor/tmp/postgres/src/include/pg_config.h.in
+++ b/src/include/pg_config.h.in
@@ -62,6 +62,14 @@
# define gettimeofday(a,b) gettimeofday(a)
#endif
+/* Size of a fastlock array.
+ We allow a small number of "weak" relation locks (AccessShareLock,
+ RowShareLock, RowExclusiveLock) to be recorded in the PGPROC structure
+ rather than the main lock table. This eases contention on the lock
+ manager LWLocks. See storage/lmgr/README for additional details.
+*/
+#undef FP_LOCK_SLOTS_PER_BACKEND
+
/* Define to 1 if you have the `append_history' function. */
#undef HAVE_APPEND_HISTORY
diff --git a/home/nikor/tmp/postgres/src/include/storage/proc.h b/src/include/storage/proc.h
index 2579e619eb..5303675a3a 100644
--- a/home/nikor/tmp/postgres/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -74,14 +74,6 @@ struct XidCache
*/
#define PROC_XMIN_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC)
-/*
- * We allow a small number of "weak" relation locks (AccessShareLock,
- * RowShareLock, RowExclusiveLock) to be recorded in the PGPROC structure
- * rather than the main lock table. This eases contention on the lock
- * manager LWLocks. See storage/lmgr/README for additional details.
- */
-#define FP_LOCK_SLOTS_PER_BACKEND 16
-
/*
* An invalid pgprocno. Must be larger than the maximum number of PGPROC
* structures we could possibly have. See comments for MAX_BACKENDS.
@@ -282,7 +274,7 @@ struct PGPROC
/* Lock manager data, recording fast-path locks taken by this backend. */
LWLock fpInfoLock; /* protects per-backend fast-path state */
- uint64 fpLockBits; /* lock modes held for each fast-path slot */
+ uint8 fpLockBits[FP_LOCK_SLOTS_PER_BACKEND]; /* lock modes held for each fast-path slot */
Oid fpRelId[FP_LOCK_SLOTS_PER_BACKEND]; /* slots for rel oids */
bool fpVXIDLock; /* are we holding a fast-path VXID lock? */
LocalTransactionId fpLocalTransactionId; /* lxid for fast-path VXID
diff --git a/home/nikor/tmp/postgres/configure b/./configure
index 57607d79df..58228f42fa 100755
--- a/home/nikor/tmp/postgres/configure
+++ b/./configure
@@ -806,6 +806,7 @@ infodir
docdir
oldincludedir
includedir
+runstatedir
localstatedir
sharedstatedir
sysconfdir
@@ -845,6 +846,7 @@ enable_coverage
enable_dtrace
enable_tap_tests
with_blocksize
+with_fastlocksize
with_segsize
with_wal_blocksize
with_CC
@@ -950,6 +952,7 @@ datadir='${datarootdir}'
sysconfdir='${prefix}/etc'
sharedstatedir='${prefix}/com'
localstatedir='${prefix}/var'
+runstatedir='${localstatedir}/run'
includedir='${prefix}/include'
oldincludedir='/usr/include'
docdir='${datarootdir}/doc/${PACKAGE_TARNAME}'
@@ -1202,6 +1205,15 @@ do
| -silent | --silent | --silen | --sile | --sil)
silent=yes ;;
+ -runstatedir | --runstatedir | --runstatedi | --runstated \
+ | --runstate | --runstat | --runsta | --runst | --runs \
+ | --run | --ru | --r)
+ ac_prev=runstatedir ;;
+ -runstatedir=* | --runstatedir=* | --runstatedi=* | --runstated=* \
+ | --runstate=* | --runstat=* | --runsta=* | --runst=* | --runs=* \
+ | --run=* | --ru=* | --r=*)
+ runstatedir=$ac_optarg ;;
+
-sbindir | --sbindir | --sbindi | --sbind | --sbin | --sbi | --sb)
ac_prev=sbindir ;;
-sbindir=* | --sbindir=* | --sbindi=* | --sbind=* | --sbin=* \
@@ -1339,7 +1351,7 @@ fi
for ac_var in exec_prefix prefix bindir sbindir libexecdir datarootdir \
datadir sysconfdir sharedstatedir localstatedir includedir \
oldincludedir docdir infodir htmldir dvidir pdfdir psdir \
- libdir localedir mandir
+ libdir localedir mandir runstatedir
do
eval ac_val=\$$ac_var
# Remove trailing slashes.
@@ -1492,6 +1504,7 @@ Fine tuning of the installation directories:
--sysconfdir=DIR read-only single-machine data [PREFIX/etc]
--sharedstatedir=DIR modifiable architecture-independent data [PREFIX/com]
--localstatedir=DIR modifiable single-machine data [PREFIX/var]
+ --runstatedir=DIR modifiable per-process data [LOCALSTATEDIR/run]
--libdir=DIR object code libraries [EPREFIX/lib]
--includedir=DIR C header files [PREFIX/include]
--oldincludedir=DIR C header files for non-gcc [/usr/include]
@@ -1555,6 +1568,8 @@ Optional Packages:
--with-pgport=PORTNUM set default port number [5432]
--with-blocksize=BLOCKSIZE
set table block size in kB [8]
+ --with-fastlocksize=FASTLOCKSIZE
+ set fastlock array size [16]
--with-segsize=SEGSIZE set table segment size in GB [1]
--with-wal-blocksize=BLOCKSIZE
set WAL block size in kB [8]
@@ -3735,6 +3750,43 @@ cat >>confdefs.h <<_ACEOF
_ACEOF
+#
+# Fastlock size
+#
+{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for fastlock size" >&5
+$as_echo_n "checking for fastlock size... " >&6; }
+
+
+
+# Check whether --with-fastlocksize was given.
+if test "${with_fastlocksize+set}" = set; then :
+ withval=$with_fastlocksize;
+ case $withval in
+ yes)
+ as_fn_error $? "argument required for --with-fastlocksize option" "$LINENO" 5
+ ;;
+ no)
+ as_fn_error $? "argument required for --with-fastlocksize option" "$LINENO" 5
+ ;;
+ *)
+ fastlocksize=$withval
+ ;;
+ esac
+
+else
+ fastlocksize=16
+fi
+
+
+{ $as_echo "$as_me:${as_lineno-$LINENO}: result: ${fastlocksize}" >&5
+$as_echo "${fastlocksize}" >&6; }
+
+
+cat >>confdefs.h <<_ACEOF
+#define FP_LOCK_SLOTS_PER_BACKEND ${fastlocksize}
+_ACEOF
+
+
#
# Relation segment size
#
@@ -15780,7 +15832,7 @@ else
We can't simply define LARGE_OFF_T to be 9223372036854775807,
since some C++ compilers masquerading as C compilers
incorrectly reject 9223372036854775807. */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
&& LARGE_OFF_T % 2147483647 == 1)
? 1 : -1];
@@ -15826,7 +15878,7 @@ else
We can't simply define LARGE_OFF_T to be 9223372036854775807,
since some C++ compilers masquerading as C compilers
incorrectly reject 9223372036854775807. */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
&& LARGE_OFF_T % 2147483647 == 1)
? 1 : -1];
@@ -15850,7 +15902,7 @@ rm -f core conftest.err conftest.$ac_objext conftest.$ac_ext
We can't simply define LARGE_OFF_T to be 9223372036854775807,
since some C++ compilers masquerading as C compilers
incorrectly reject 9223372036854775807. */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
&& LARGE_OFF_T % 2147483647 == 1)
? 1 : -1];
@@ -15895,7 +15947,7 @@ else
We can't simply define LARGE_OFF_T to be 9223372036854775807,
since some C++ compilers masquerading as C compilers
incorrectly reject 9223372036854775807. */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
&& LARGE_OFF_T % 2147483647 == 1)
? 1 : -1];
@@ -15919,7 +15971,7 @@ rm -f core conftest.err conftest.$ac_objext conftest.$ac_ext
We can't simply define LARGE_OFF_T to be 9223372036854775807,
since some C++ compilers masquerading as C compilers
incorrectly reject 9223372036854775807. */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
&& LARGE_OFF_T % 2147483647 == 1)
? 1 : -1];
