public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 07/10] add wal_compression_method: zstd
43+ messages / 24 participants
[nested] [flat]
* [PATCH 07/10] add wal_compression_method: zstd
@ 2021-03-12 20:43 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Justin Pryzby @ 2021-03-12 20:43 UTC (permalink / raw)
---
configure | 163 ++++++++++++++++++
configure.ac | 26 +++
doc/src/sgml/config.sgml | 2 +-
src/backend/access/transam/xlog.c | 3 +
src/backend/access/transam/xloginsert.c | 19 ++
src/backend/access/transam/xlogreader.c | 16 ++
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/include/access/xlog_internal.h | 1 +
src/include/pg_config.h.in | 3 +
src/tools/msvc/Solution.pm | 1 +
10 files changed, 234 insertions(+), 2 deletions(-)
diff --git a/configure b/configure
index 8d76be00c1..81e23418b2 100755
--- a/configure
+++ b/configure
@@ -699,6 +699,9 @@ with_gnu_ld
LD
LDFLAGS_SL
LDFLAGS_EX
+ZSTD_LIBS
+ZSTD_CFLAGS
+with_zstd
LZ4_LIBS
LZ4_CFLAGS
with_lz4
@@ -868,6 +871,7 @@ with_libxslt
with_system_tzdata
with_zlib
with_lz4
+with_zstd
with_gnu_ld
with_ssl
with_openssl
@@ -897,6 +901,8 @@ XML2_CFLAGS
XML2_LIBS
LZ4_CFLAGS
LZ4_LIBS
+ZSTD_CFLAGS
+ZSTD_LIBS
LDFLAGS_EX
LDFLAGS_SL
PERL
@@ -1576,6 +1582,7 @@ Optional Packages:
use system time zone data in DIR
--without-zlib do not use Zlib
--without-lz4 build without LZ4 support
+ --with-zstd build with Zstd compression library
--with-gnu-ld assume the C compiler uses GNU ld [default=no]
--with-ssl=LIB use LIB for SSL/TLS support (openssl)
--with-openssl obsolete spelling of --with-ssl=openssl
@@ -1605,6 +1612,8 @@ Some influential environment variables:
XML2_LIBS linker flags for XML2, overriding pkg-config
LZ4_CFLAGS C compiler flags for LZ4, overriding pkg-config
LZ4_LIBS linker flags for LZ4, overriding pkg-config
+ ZSTD_CFLAGS C compiler flags for ZSTD, overriding pkg-config
+ ZSTD_LIBS linker flags for ZSTD, overriding pkg-config
LDFLAGS_EX extra linker flags for linking executables only
LDFLAGS_SL extra linker flags for linking shared libraries only
PERL Perl program
@@ -8705,6 +8714,137 @@ fi
CFLAGS="$LZ4_CFLAGS $CFLAGS"
fi
+#
+# ZSTD
+#
+{ $as_echo "$as_me:${as_lineno-$LINENO}: checking whether to build with zstd support" >&5
+$as_echo_n "checking whether to build with zstd support... " >&6; }
+
+
+
+# Check whether --with-zstd was given.
+if test "${with_zstd+set}" = set; then :
+ withval=$with_zstd;
+ case $withval in
+ yes)
+
+$as_echo "#define USE_ZSTD 1" >>confdefs.h
+
+ ;;
+ no)
+ :
+ ;;
+ *)
+ as_fn_error $? "no argument expected for --with-zstd option" "$LINENO" 5
+ ;;
+ esac
+
+else
+ with_zstd=no
+
+fi
+
+
+{ $as_echo "$as_me:${as_lineno-$LINENO}: result: $with_zstd" >&5
+$as_echo "$with_zstd" >&6; }
+
+
+if test "$with_zstd" = yes; then
+
+pkg_failed=no
+{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for libzstd" >&5
+$as_echo_n "checking for libzstd... " >&6; }
+
+if test -n "$ZSTD_CFLAGS"; then
+ pkg_cv_ZSTD_CFLAGS="$ZSTD_CFLAGS"
+ elif test -n "$PKG_CONFIG"; then
+ if test -n "$PKG_CONFIG" && \
+ { { $as_echo "$as_me:${as_lineno-$LINENO}: \$PKG_CONFIG --exists --print-errors \"libzstd\""; } >&5
+ ($PKG_CONFIG --exists --print-errors "libzstd") 2>&5
+ ac_status=$?
+ $as_echo "$as_me:${as_lineno-$LINENO}: \$? = $ac_status" >&5
+ test $ac_status = 0; }; then
+ pkg_cv_ZSTD_CFLAGS=`$PKG_CONFIG --cflags "libzstd" 2>/dev/null`
+ test "x$?" != "x0" && pkg_failed=yes
+else
+ pkg_failed=yes
+fi
+ else
+ pkg_failed=untried
+fi
+if test -n "$ZSTD_LIBS"; then
+ pkg_cv_ZSTD_LIBS="$ZSTD_LIBS"
+ elif test -n "$PKG_CONFIG"; then
+ if test -n "$PKG_CONFIG" && \
+ { { $as_echo "$as_me:${as_lineno-$LINENO}: \$PKG_CONFIG --exists --print-errors \"libzstd\""; } >&5
+ ($PKG_CONFIG --exists --print-errors "libzstd") 2>&5
+ ac_status=$?
+ $as_echo "$as_me:${as_lineno-$LINENO}: \$? = $ac_status" >&5
+ test $ac_status = 0; }; then
+ pkg_cv_ZSTD_LIBS=`$PKG_CONFIG --libs "libzstd" 2>/dev/null`
+ test "x$?" != "x0" && pkg_failed=yes
+else
+ pkg_failed=yes
+fi
+ else
+ pkg_failed=untried
+fi
+
+
+
+if test $pkg_failed = yes; then
+ { $as_echo "$as_me:${as_lineno-$LINENO}: result: no" >&5
+$as_echo "no" >&6; }
+
+if $PKG_CONFIG --atleast-pkgconfig-version 0.20; then
+ _pkg_short_errors_supported=yes
+else
+ _pkg_short_errors_supported=no
+fi
+ if test $_pkg_short_errors_supported = yes; then
+ ZSTD_PKG_ERRORS=`$PKG_CONFIG --short-errors --print-errors --cflags --libs "libzstd" 2>&1`
+ else
+ ZSTD_PKG_ERRORS=`$PKG_CONFIG --print-errors --cflags --libs "libzstd" 2>&1`
+ fi
+ # Put the nasty error message in config.log where it belongs
+ echo "$ZSTD_PKG_ERRORS" >&5
+
+ as_fn_error $? "Package requirements (libzstd) were not met:
+
+$ZSTD_PKG_ERRORS
+
+Consider adjusting the PKG_CONFIG_PATH environment variable if you
+installed software in a non-standard prefix.
+
+Alternatively, you may set the environment variables ZSTD_CFLAGS
+and ZSTD_LIBS to avoid the need to call pkg-config.
+See the pkg-config man page for more details." "$LINENO" 5
+elif test $pkg_failed = untried; then
+ { $as_echo "$as_me:${as_lineno-$LINENO}: result: no" >&5
+$as_echo "no" >&6; }
+ { { $as_echo "$as_me:${as_lineno-$LINENO}: error: in \`$ac_pwd':" >&5
+$as_echo "$as_me: error: in \`$ac_pwd':" >&2;}
+as_fn_error $? "The pkg-config script could not be found or is too old. Make sure it
+is in your PATH or set the PKG_CONFIG environment variable to the full
+path to pkg-config.
+
+Alternatively, you may set the environment variables ZSTD_CFLAGS
+and ZSTD_LIBS to avoid the need to call pkg-config.
+See the pkg-config man page for more details.
+
+To get pkg-config, see <http://pkg-config.freedesktop.org/;.
+See \`config.log' for more details" "$LINENO" 5; }
+else
+ ZSTD_CFLAGS=$pkg_cv_ZSTD_CFLAGS
+ ZSTD_LIBS=$pkg_cv_ZSTD_LIBS
+ { $as_echo "$as_me:${as_lineno-$LINENO}: result: yes" >&5
+$as_echo "yes" >&6; }
+
+fi
+ LIBS="$ZSTD_LIBS $LIBS"
+ CFLAGS="$ZSTD_CFLAGS $CFLAGS"
+fi
+
#
# Assignments
#
@@ -13559,6 +13699,29 @@ done
CPPFLAGS=$ac_save_CPPFLAGS
fi
+if test "$with_zstd" = yes; then
+ ac_save_CPPFLAGS=$CPPFLAGS
+ CPPFLAGS="$ZSTD_CFLAGS $CPPFLAGS"
+
+ # Verify we have zstd's header files
+ for ac_header in zstd.h
+do :
+ ac_fn_c_check_header_mongrel "$LINENO" "zstd.h" "ac_cv_header_zstd_h" "$ac_includes_default"
+if test "x$ac_cv_header_zstd_h" = xyes; then :
+ cat >>confdefs.h <<_ACEOF
+#define HAVE_ZSTD_H 1
+_ACEOF
+
+else
+ as_fn_error $? "zstd.h header file is required for zstd" "$LINENO" 5
+fi
+
+done
+
+
+ CPPFLAGS=$ac_save_CPPFLAGS
+fi
+
if test "$with_gssapi" = yes ; then
for ac_header in gssapi/gssapi.h
do :
diff --git a/configure.ac b/configure.ac
index bfcdc88be0..d6f6349067 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1001,6 +1001,21 @@ if test "$with_lz4" = yes; then
CFLAGS="$LZ4_CFLAGS $CFLAGS"
fi
+#
+# ZSTD
+#
+AC_MSG_CHECKING([whether to build with zstd support])
+PGAC_ARG_BOOL(with, zstd, no, [build with Zstd compression library],
+ [AC_DEFINE([USE_ZSTD], 1, [Define to 1 to build with zstd support. (--with-zstd)])])
+AC_MSG_RESULT([$with_zstd])
+AC_SUBST(with_zstd)
+
+if test "$with_zstd" = yes; then
+ PKG_CHECK_MODULES(ZSTD, libzstd)
+ LIBS="$ZSTD_LIBS $LIBS"
+ CFLAGS="$ZSTD_CFLAGS $CFLAGS"
+fi
+
#
# Assignments
#
@@ -1436,6 +1451,17 @@ if test "$with_lz4" = yes; then
CPPFLAGS=$ac_save_CPPFLAGS
fi
+if test "$with_zstd" = yes; then
+ ac_save_CPPFLAGS=$CPPFLAGS
+ CPPFLAGS="$ZSTD_CFLAGS $CPPFLAGS"
+
+ # Verify we have zstd's header files
+ AC_CHECK_HEADERS(zstd.h, [],
+ [AC_MSG_ERROR([zstd.h header file is required for zstd])])
+
+ CPPFLAGS=$ac_save_CPPFLAGS
+fi
+
if test "$with_gssapi" = yes ; then
AC_CHECK_HEADERS(gssapi/gssapi.h, [],
[AC_CHECK_HEADERS(gssapi.h, [], [AC_MSG_ERROR([gssapi.h header file is required for GSSAPI])])])
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 257775c83b..94dd6ef3e9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3083,7 +3083,7 @@ include_dir 'conf.d'
This parameter selects the compression method used to compress WAL when
<varname>wal_compression</varname> is enabled.
The supported methods are pglz, zlib, and (if configured when
- <productname>PostgreSQL</productname> was built) lz4.
+ <productname>PostgreSQL</productname> was built) lz4 and zstd.
The default value is <literal>pglz</literal>.
Only superusers can change this setting.
</para>
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 3657f74de9..307eee6626 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -189,6 +189,9 @@ const struct config_enum_entry wal_compression_options[] = {
#endif
#ifdef USE_LZ4
{"lz4", WAL_COMPRESSION_LZ4, false},
+#endif
+#ifdef USE_ZSTD
+ {"zstd", WAL_COMPRESSION_ZSTD, false},
#endif
{NULL, 0, false}
};
diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c
index 9fe5c30236..5d1ae37dae 100644
--- a/src/backend/access/transam/xloginsert.c
+++ b/src/backend/access/transam/xloginsert.c
@@ -41,6 +41,10 @@
#include "lz4.h"
#endif
+#ifdef USE_ZSTD
+#include "zstd.h"
+#endif
+
/* Buffer size required to store a compressed version of backup block image */
#define PGLZ_MAX_BLCKSZ PGLZ_MAX_OUTPUT(BLCKSZ)
@@ -896,6 +900,21 @@ XLogCompressBackupBlock(char *page, uint16 hole_offset, uint16 hole_length,
break;
#endif
+#ifdef USE_ZSTD
+ case WAL_COMPRESSION_ZSTD:
+ len = ZSTD_compress(dest, PGLZ_MAX_BLCKSZ, source, orig_len, 1);
+ if (ZSTD_isError(len))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("failed compressing zstd: %s",
+ ZSTD_getErrorName(len))));
+ len = -1;
+ }
+
+ break;
+#endif
+
default:
/*
* It should be impossible to get here for unsupported algorithms,
diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c
index fa1e38a810..caa1031d63 100644
--- a/src/backend/access/transam/xlogreader.c
+++ b/src/backend/access/transam/xlogreader.c
@@ -41,6 +41,10 @@
#include "lz4.h"
#endif
+#ifdef USE_ZSTD
+#include "zstd.h"
+#endif
+
static void report_invalid_record(XLogReaderState *state, const char *fmt,...)
pg_attribute_printf(2, 3);
static bool allocate_recordbuf(XLogReaderState *state, uint32 reclength);
@@ -1564,6 +1568,8 @@ wal_compression_name(WalCompression compression)
return "zlib";
case WAL_COMPRESSION_LZ4:
return "lz4";
+ case WAL_COMPRESSION_ZSTD:
+ return "zstd";
default:
return "???";
}
@@ -1621,6 +1627,16 @@ RestoreBlockImage(XLogReaderState *record, uint8 block_id, char *page)
break;
#endif
+#ifdef USE_ZSTD
+ case WAL_COMPRESSION_ZSTD:
+ decomp_result = ZSTD_decompress(tmp.data, BLCKSZ-bkpb->hole_length,
+ ptr, bkpb->bimg_len);
+ // XXX: ZSTD_getErrorName
+ if (ZSTD_isError(decomp_result))
+ decomp_result = -1;
+ break;
+#endif
+
default:
report_invalid_record(record, "image at %X/%X is compressed with unsupported codec, block %d (%d/%s)",
(uint32) (record->ReadRecPtr >> 32),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 76f494cb9b..d372e2a817 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -213,7 +213,7 @@
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_compression = off # enable compression of full-page writes
-#wal_compression_method = pglz # pglz, zlib, lz4
+#wal_compression_method = pglz # pglz, zlib, lz4, zstd
#wal_log_hints = off # also do full page writes of non-critical updates
# (change requires restart)
#wal_init_zero = on # zero-fill new WAL files
diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h
index e70886b81c..48b16b6083 100644
--- a/src/include/access/xlog_internal.h
+++ b/src/include/access/xlog_internal.h
@@ -329,6 +329,7 @@ typedef enum WalCompression
WAL_COMPRESSION_PGLZ,
WAL_COMPRESSION_ZLIB,
WAL_COMPRESSION_LZ4,
+ WAL_COMPRESSION_ZSTD,
} WalCompression;
extern const char *wal_compression_name(WalCompression compression);
diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in
index 0a6422da4f..ad26393352 100644
--- a/src/include/pg_config.h.in
+++ b/src/include/pg_config.h.in
@@ -902,6 +902,9 @@
/* Define to 1 to build with LZ4 support (--with-lz4) */
#undef USE_LZ4
+/* Define to 1 if you have the `zstd' library (-lzstd). */
+#undef USE_ZSTD
+
/* Define to select named POSIX semaphores. */
#undef USE_NAMED_POSIX_SEMAPHORES
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index 14605371bb..fff0212087 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -486,6 +486,7 @@ sub GenerateFiles
USE_LIBXML => undef,
USE_LIBXSLT => undef,
USE_LZ4 => undef,
+ USE_ZSTD => $self->{options}->{zstd} ? 1 : undef,
USE_LDAP => $self->{options}->{ldap} ? 1 : undef,
USE_LLVM => undef,
USE_NAMED_POSIX_SEMAPHORES => undef,
--
2.17.0
--XsQoSWH+UP9D9v3l
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="0008-Default-to-zstd.patch"
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
@ 2023-06-05 15:18 Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 18:51 ` Re: Let's make PostgreSQL multi-threaded Andrew Dunstan <[email protected]>
2023-06-05 21:07 ` Re: Let's make PostgreSQL multi-threaded Jonah H. Harris <[email protected]>
2023-06-08 17:07 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
0 siblings, 4 replies; 43+ messages in thread
From: Tom Lane @ 2023-06-05 15:18 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: pgsql-hackers
Heikki Linnakangas <[email protected]> writes:
> I spoke with some folks at PGCon about making PostgreSQL multi-threaded,
> so that the whole server runs in a single process, with multiple
> threads. It has been discussed many times in the past, last thread on
> pgsql-hackers was back in 2017 when Konstantin made some experiments [0].
> I feel that there is now pretty strong consensus that it would be a good
> thing, more so than before. Lots of work to get there, and lots of
> details to be hashed out, but no objections to the idea at a high level.
> The purpose of this email is to make that silent consensus explicit. If
> you have objections to switching from the current multi-process
> architecture to a single-process, multi-threaded architecture, please
> speak up.
For the record, I think this will be a disaster. There is far too much
code that will get broken, largely silently, and much of it is not
under our control.
regards, tom lane
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
@ 2023-06-05 15:33 ` Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 19:20 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
3 siblings, 2 replies; 43+ messages in thread
From: Heikki Linnakangas @ 2023-06-05 15:33 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On 05/06/2023 11:18, Tom Lane wrote:
> Heikki Linnakangas <[email protected]> writes:
>> I spoke with some folks at PGCon about making PostgreSQL multi-threaded,
>> so that the whole server runs in a single process, with multiple
>> threads. It has been discussed many times in the past, last thread on
>> pgsql-hackers was back in 2017 when Konstantin made some experiments [0].
>
>> I feel that there is now pretty strong consensus that it would be a good
>> thing, more so than before. Lots of work to get there, and lots of
>> details to be hashed out, but no objections to the idea at a high level.
>
>> The purpose of this email is to make that silent consensus explicit. If
>> you have objections to switching from the current multi-process
>> architecture to a single-process, multi-threaded architecture, please
>> speak up.
>
> For the record, I think this will be a disaster. There is far too much
> code that will get broken, largely silently, and much of it is not
> under our control.
Noted. Other large projects have gone through this transition. It's not
easy, but it's a lot easier now than it was 10 years ago. The platform
and compiler support is there now, all libraries have thread-safe
interfaces, etc.
I don't expect you or others to buy into any particular code change at
this point, or to contribute time into it. Just to accept that it's a
worthwhile goal. If the implementation turns out to be a disaster, then
it won't be accepted, of course. But I'm optimistic.
--
Heikki Linnakangas
Neon (https://neon.tech)
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
@ 2023-06-05 17:40 ` Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Jonathan S. Katz @ 2023-06-05 17:40 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; +Cc: pgsql-hackers
On 6/5/23 11:33 AM, Heikki Linnakangas wrote:
> On 05/06/2023 11:18, Tom Lane wrote:
>> Heikki Linnakangas <[email protected]> writes:
>>> I spoke with some folks at PGCon about making PostgreSQL multi-threaded,
>>> so that the whole server runs in a single process, with multiple
>>> threads. It has been discussed many times in the past, last thread on
>>> pgsql-hackers was back in 2017 when Konstantin made some experiments
>>> [0].
>>
>>> I feel that there is now pretty strong consensus that it would be a good
>>> thing, more so than before. Lots of work to get there, and lots of
>>> details to be hashed out, but no objections to the idea at a high level.
>>
>>> The purpose of this email is to make that silent consensus explicit. If
>>> you have objections to switching from the current multi-process
>>> architecture to a single-process, multi-threaded architecture, please
>>> speak up.
>>
>> For the record, I think this will be a disaster. There is far too much
>> code that will get broken, largely silently, and much of it is not
>> under our control.
>
> Noted. Other large projects have gone through this transition. It's not
> easy, but it's a lot easier now than it was 10 years ago. The platform
> and compiler support is there now, all libraries have thread-safe
> interfaces, etc.
>
> I don't expect you or others to buy into any particular code change at
> this point, or to contribute time into it. Just to accept that it's a
> worthwhile goal. If the implementation turns out to be a disaster, then
> it won't be accepted, of course. But I'm optimistic.
I don't have enough expertise in this area to comment on if it'd be a
"disaster" or not. My zoomed out observations are two-fold:
1. It seems like there's a lack of consensus on which of processes vs.
threads yield the best performance benefit, and from talking to folks
with greater expertise than me, this can vary between workloads. I
believe one DB even gives uses a choice if they want to run in processes
vs. threads.
2. While I wouldn't want to necessarily discourage a moonshot effort, I
would ask if developer time could be better spent on tackling some of
the other problems around vertical scalability? Per some PGCon
discussions, there's still room for improvement in how PostgreSQL can
best utilize resources available very large "commodity" machines (a
448-core / 24TB RAM instance comes to mind).
I'm purposely giving a nonanswer on whether it's a worthwhile goal, but
rather I'd be curious where it could stack up against some other efforts
to continue to help PostgreSQL improve performance and handle very large
workloads.
Thanks,
Jonathan
Attachments:
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
@ 2023-06-07 21:37 ` Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 14:08 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
2023-07-27 13:27 ` Re: Let's make PostgreSQL multi-threaded David Geier <[email protected]>
0 siblings, 3 replies; 43+ messages in thread
From: Andres Freund @ 2023-06-07 21:37 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; +Cc: Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
Hi,
On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote:
> 2. While I wouldn't want to necessarily discourage a moonshot effort, I
> would ask if developer time could be better spent on tackling some of the
> other problems around vertical scalability? Per some PGCon discussions,
> there's still room for improvement in how PostgreSQL can best utilize
> resources available very large "commodity" machines (a 448-core / 24TB RAM
> instance comes to mind).
I think we're starting to hit quite a few limits related to the process model,
particularly on bigger machines. The overhead of cross-process context
switches is inherently higher than switching between threads in the same
process - and my suspicion is that that overhead will continue to
increase. Once you have a significant number of connections we end up spending
a *lot* of time in TLB misses, and that's inherent to the process model,
because you can't share the TLB across processes.
The amount of duplicated code we have to deal with due to to the process model
is quite substantial. We have local memory, statically allocated shared memory
and dynamically allocated shared memory variants for some things. And that's
just going to continue.
> I'm purposely giving a nonanswer on whether it's a worthwhile goal, but
> rather I'd be curious where it could stack up against some other efforts to
> continue to help PostgreSQL improve performance and handle very large
> workloads.
There's plenty of things we can do before, but in the end I think tackling the
issues you mention and moving to threads are quite tightly linked.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
@ 2023-06-08 09:54 ` Hannu Krosing <[email protected]>
2023-06-08 10:15 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2 siblings, 2 replies; 43+ messages in thread
From: Hannu Krosing @ 2023-06-08 09:54 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Wed, Jun 7, 2023 at 11:37 PM Andres Freund <[email protected]> wrote:
>
> Hi,
>
> On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote:
> > 2. While I wouldn't want to necessarily discourage a moonshot effort, I
> > would ask if developer time could be better spent on tackling some of the
> > other problems around vertical scalability? Per some PGCon discussions,
> > there's still room for improvement in how PostgreSQL can best utilize
> > resources available very large "commodity" machines (a 448-core / 24TB RAM
> > instance comes to mind).
>
> I think we're starting to hit quite a few limits related to the process model,
> particularly on bigger machines. The overhead of cross-process context
> switches is inherently higher than switching between threads in the same
> process - and my suspicion is that that overhead will continue to
> increase. Once you have a significant number of connections we end up spending
> a *lot* of time in TLB misses, and that's inherent to the process model,
> because you can't share the TLB across processes.
This part was touched in the "AMA with a Linux Kernale Hacker"
Unconference session where he mentioned that the had proposed a
'mshare' syscall for this.
So maybe a more fruitful way to fixing the perceived issues with
process model is to push for small changes in Linux to overcome these
avoiding a wholesale rewrite ?
>
>
> The amount of duplicated code we have to deal with due to to the process model
> is quite substantial. We have local memory, statically allocated shared memory
> and dynamically allocated shared memory variants for some things. And that's
> just going to continue.
Maybe we can already remove the distinction between static and dynamic
shared memory ?
Though I already heard some complaints at the conference discussions
that having the dynamic version available has made some developers
sloppy in using it resulting in wastefulness.
>
>
> > I'm purposely giving a nonanswer on whether it's a worthwhile goal, but
> > rather I'd be curious where it could stack up against some other efforts to
> > continue to help PostgreSQL improve performance and handle very large
> > workloads.
>
> There's plenty of things we can do before, but in the end I think tackling the
> issues you mention and moving to threads are quite tightly linked.
Still we should be focusing our attention at solving the issues and
not at "moving to threads" and hoping this will fix the issues by
itself.
Cheers
Hannu
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
@ 2023-06-08 10:15 ` Hannu Krosing <[email protected]>
2023-06-08 16:00 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Hannu Krosing @ 2023-06-08 10:15 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Thu, Jun 8, 2023 at 11:54 AM Hannu Krosing <[email protected]> wrote:
>
> On Wed, Jun 7, 2023 at 11:37 PM Andres Freund <[email protected]> wrote:
> >
> > Hi,
> >
> > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote:
> > > 2. While I wouldn't want to necessarily discourage a moonshot effort, I
> > > would ask if developer time could be better spent on tackling some of the
> > > other problems around vertical scalability? Per some PGCon discussions,
> > > there's still room for improvement in how PostgreSQL can best utilize
> > > resources available very large "commodity" machines (a 448-core / 24TB RAM
> > > instance comes to mind).
> >
> > I think we're starting to hit quite a few limits related to the process model,
> > particularly on bigger machines. The overhead of cross-process context
> > switches is inherently higher than switching between threads in the same
> > process - and my suspicion is that that overhead will continue to
> > increase. Once you have a significant number of connections we end up spending
> > a *lot* of time in TLB misses, and that's inherent to the process model,
> > because you can't share the TLB across processes.
>
>
> This part was touched in the "AMA with a Linux Kernale Hacker"
> Unconference session where he mentioned that the had proposed a
> 'mshare' syscall for this.
Also, the *static* huge pages already let you solve this problem now
by sharing the page tables
Cheers
Hannu
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 10:15 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
@ 2023-06-08 16:00 ` Andres Freund <[email protected]>
2023-06-08 19:34 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Andres Freund @ 2023-06-08 16:00 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
Hi,
On 2023-06-08 12:15:58 +0200, Hannu Krosing wrote:
> On Thu, Jun 8, 2023 at 11:54 AM Hannu Krosing <[email protected]> wrote:
> >
> > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund <[email protected]> wrote:
> > >
> > > Hi,
> > >
> > > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote:
> > > > 2. While I wouldn't want to necessarily discourage a moonshot effort, I
> > > > would ask if developer time could be better spent on tackling some of the
> > > > other problems around vertical scalability? Per some PGCon discussions,
> > > > there's still room for improvement in how PostgreSQL can best utilize
> > > > resources available very large "commodity" machines (a 448-core / 24TB RAM
> > > > instance comes to mind).
> > >
> > > I think we're starting to hit quite a few limits related to the process model,
> > > particularly on bigger machines. The overhead of cross-process context
> > > switches is inherently higher than switching between threads in the same
> > > process - and my suspicion is that that overhead will continue to
> > > increase. Once you have a significant number of connections we end up spending
> > > a *lot* of time in TLB misses, and that's inherent to the process model,
> > > because you can't share the TLB across processes.
> >
> >
> > This part was touched in the "AMA with a Linux Kernale Hacker"
> > Unconference session where he mentioned that the had proposed a
> > 'mshare' syscall for this.
As-is that'd just lead to sharing page table, not the TLB. I don't think you
currently do sharing of the TLB for parts of your address space on x86
hardware. It's possible that something like that gets added to future
hardware, but ...
> Also, the *static* huge pages already let you solve this problem now
> by sharing the page tables
You don't share the page tables with huge pages on linux.
- Andres
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 10:15 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 16:00 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
@ 2023-06-08 19:34 ` Thomas Munro <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Thomas Munro @ 2023-06-08 19:34 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Hannu Krosing <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Fri, Jun 9, 2023 at 4:00 AM Andres Freund <[email protected]> wrote:
> On 2023-06-08 12:15:58 +0200, Hannu Krosing wrote:
> > > This part was touched in the "AMA with a Linux Kernale Hacker"
> > > Unconference session where he mentioned that the had proposed a
> > > 'mshare' syscall for this.
>
> As-is that'd just lead to sharing page table, not the TLB. I don't think you
> currently do sharing of the TLB for parts of your address space on x86
> hardware. It's possible that something like that gets added to future
> hardware, but ...
I wasn't in Mathew Wilcox's unconference in Ottawa but I found an
older article on LWN:
https://lwn.net/Articles/895217/
For what it's worth, FreeBSD hackers have studied this topic too (and
it's been done in Android and no doubt other systems before):
https://www.cs.rochester.edu/u/sandhya/papers/ispass19.pdf
I've shared that paper on this list before in the context of
super/huge pages and their benefits (to executable code, and to the
buffer pool), but a second topic in that paper is the idea of a shared
page table: "We find that sharing PTPs across different processes can
reduce execution cycles by as much as 6.9%. Moreover, the combined
effects of using superpages to map the main executable and sharing
PTPs for the small shared libraries can reduce execution cycles up to
18.2%." And that's just part of it, because those guys are more
interested in shared code/libraries and such so that's probably not
even getting to the stuff like buffer pool and DSMs that we might tend
to think of first.
I'm pretty sure PostgreSQL (along with another fork-based RDBMSs
mentioned in this thread) must be one of the worst offenders for page
table bloat, simply because we can have a lot of processes and touch a
lot of memory.
I'm no expert in this stuff, but it seems to be that with shared page
table schemes you can avoid wasting huge amounts of RAM on duplicated
page table entries (pages * processes), and with huge/super pages you
can reduce the number of pages, but AFAIK you still can't escape the
TLB shootdown cost, which is all-or-nothing (PCID level at best). The
only way to avoid TLB shootdowns on context switches is to have
*exactly the same memory map*. Or, as Robert succinctly shouted,
"THREADS".
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
@ 2023-06-08 12:15 ` Matthias van de Meent <[email protected]>
2023-06-08 12:44 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Matthias van de Meent @ 2023-06-08 12:15 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Andres Freund <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Thu, 8 Jun 2023 at 11:54, Hannu Krosing <[email protected]> wrote:
>
> On Wed, Jun 7, 2023 at 11:37 PM Andres Freund <[email protected]> wrote:
> >
> > Hi,
> >
> > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote:
> > > 2. While I wouldn't want to necessarily discourage a moonshot effort, I
> > > would ask if developer time could be better spent on tackling some of the
> > > other problems around vertical scalability? Per some PGCon discussions,
> > > there's still room for improvement in how PostgreSQL can best utilize
> > > resources available very large "commodity" machines (a 448-core / 24TB RAM
> > > instance comes to mind).
> >
> > I think we're starting to hit quite a few limits related to the process model,
> > particularly on bigger machines. The overhead of cross-process context
> > switches is inherently higher than switching between threads in the same
> > process - and my suspicion is that that overhead will continue to
> > increase. Once you have a significant number of connections we end up spending
> > a *lot* of time in TLB misses, and that's inherent to the process model,
> > because you can't share the TLB across processes.
>
>
> This part was touched in the "AMA with a Linux Kernale Hacker"
> Unconference session where he mentioned that the had proposed a
> 'mshare' syscall for this.
>
> So maybe a more fruitful way to fixing the perceived issues with
> process model is to push for small changes in Linux to overcome these
> avoiding a wholesale rewrite ?
We support not just Linux, but also Windows and several (?) BSDs. I'm
not against pushing Linux to make things easier for us, but Linux is
an open source project, too, where someone need to put in time to get
the shiny things that you want. And I'd rather see our time spent in
PostgreSQL, as Linux is only used by a part of our user base.
> > The amount of duplicated code we have to deal with due to to the process model
> > is quite substantial. We have local memory, statically allocated shared memory
> > and dynamically allocated shared memory variants for some things. And that's
> > just going to continue.
>
> Maybe we can already remove the distinction between static and dynamic
> shared memory ?
That sounds like a bad idea, dynamic shared memory is more expensive
to maintain than our static shared memory systems, not in the least
because DSM is not guaranteed to share the same addresses in each
process' address space.
> Though I already heard some complaints at the conference discussions
> that having the dynamic version available has made some developers
> sloppy in using it resulting in wastefulness.
Do you know any examples of this wastefulness?
> > > I'm purposely giving a nonanswer on whether it's a worthwhile goal, but
> > > rather I'd be curious where it could stack up against some other efforts to
> > > continue to help PostgreSQL improve performance and handle very large
> > > workloads.
> >
> > There's plenty of things we can do before, but in the end I think tackling the
> > issues you mention and moving to threads are quite tightly linked.
>
> Still we should be focusing our attention at solving the issues and
> not at "moving to threads" and hoping this will fix the issues by
> itself.
I suspect that it is much easier to solve some of the issues when
working in a shared address space.
E.g. resizing shared_buffers is difficult right now due to the use of
a static allocation of shared memory, but if we had access to a single
shared address space, it'd be easier to do any cleanup necessary for
dynamically increasing/decreasing its size.
Same with parallel workers - if we have a shared address space, the
workers can pass any sized objects around without being required to
move the tuples through DSM and waiting for the leader process to
empty that buffer when it gets full.
Sure, most of that is probably possible with DSM as well, it's just
that I see a lot more issues that you need to take care of when you
don't have a shared address space (such as the pointer translation we
do in dsa_get_address).
Kind regards,
Matthias van de Meent
Neon, Inc.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
@ 2023-06-08 12:44 ` Hannu Krosing <[email protected]>
2023-06-08 14:56 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
2023-06-08 15:08 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 16:05 ` Re: Let's make PostgreSQL multi-threaded Greg Sabino Mullane <[email protected]>
0 siblings, 3 replies; 43+ messages in thread
From: Hannu Krosing @ 2023-06-08 12:44 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: Andres Freund <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Thu, Jun 8, 2023 at 2:15 PM Matthias van de Meent
<[email protected]> wrote:
>
> On Thu, 8 Jun 2023 at 11:54, Hannu Krosing <[email protected]> wrote:
> >
> > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund <[email protected]> wrote:
> > >
> > > Hi,
> > >
> > > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote:
> > > > 2. While I wouldn't want to necessarily discourage a moonshot effort, I
> > > > would ask if developer time could be better spent on tackling some of the
> > > > other problems around vertical scalability? Per some PGCon discussions,
> > > > there's still room for improvement in how PostgreSQL can best utilize
> > > > resources available very large "commodity" machines (a 448-core / 24TB RAM
> > > > instance comes to mind).
> > >
> > > I think we're starting to hit quite a few limits related to the process model,
> > > particularly on bigger machines. The overhead of cross-process context
> > > switches is inherently higher than switching between threads in the same
> > > process - and my suspicion is that that overhead will continue to
> > > increase. Once you have a significant number of connections we end up spending
> > > a *lot* of time in TLB misses, and that's inherent to the process model,
> > > because you can't share the TLB across processes.
> >
> >
> > This part was touched in the "AMA with a Linux Kernale Hacker"
> > Unconference session where he mentioned that the had proposed a
> > 'mshare' syscall for this.
> >
> > So maybe a more fruitful way to fixing the perceived issues with
> > process model is to push for small changes in Linux to overcome these
> > avoiding a wholesale rewrite ?
>
> We support not just Linux, but also Windows and several (?) BSDs. I'm
> not against pushing Linux to make things easier for us, but Linux is
> an open source project, too, where someone need to put in time to get
> the shiny things that you want. And I'd rather see our time spent in
> PostgreSQL, as Linux is only used by a part of our user base.
Do we have any statistics for the distribution of our user base ?
My gut feeling says that for performance-critical use the non-Linux is
in low single digits at best.
My fascination for OpenSource started with realisation that instead of
workarounds you can actually fix the problem at source. So if the
specific problem is that TLB is not shared then the proper fix is
making it shared instead of rewriting everything else to get around
it. None of us is limited to writing code in PostgreSQL only. If the
easiest and more generix fix can be done in Linux then so be it.
It is also possible that Windows and *BSD already have a similar feature.
>
> > > The amount of duplicated code we have to deal with due to to the process model
> > > is quite substantial. We have local memory, statically allocated shared memory
> > > and dynamically allocated shared memory variants for some things. And that's
> > > just going to continue.
> >
> > Maybe we can already remove the distinction between static and dynamic
> > shared memory ?
>
> That sounds like a bad idea, dynamic shared memory is more expensive
> to maintain than our static shared memory systems, not in the least
> because DSM is not guaranteed to share the same addresses in each
> process' address space.
Then this too needs to be fixed
>
> > Though I already heard some complaints at the conference discussions
> > that having the dynamic version available has made some developers
> > sloppy in using it resulting in wastefulness.
>
> Do you know any examples of this wastefulness?
No. Just somebody mentioned it in a hallway conversation and the rest
of the developers present mumbled approvingly :)
> > > > I'm purposely giving a nonanswer on whether it's a worthwhile goal, but
> > > > rather I'd be curious where it could stack up against some other efforts to
> > > > continue to help PostgreSQL improve performance and handle very large
> > > > workloads.
> > >
> > > There's plenty of things we can do before, but in the end I think tackling the
> > > issues you mention and moving to threads are quite tightly linked.
> >
> > Still we should be focusing our attention at solving the issues and
> > not at "moving to threads" and hoping this will fix the issues by
> > itself.
>
> I suspect that it is much easier to solve some of the issues when
> working in a shared address space.
Probably. But it would come at the cost of needing to change a lot of
other parts of PostgreSQL.
I am not against making code cleaner for potential threaded model
support. I am just a bit sceptical about the actual switch being easy,
or doable in the next 10-15 years.
> E.g. resizing shared_buffers is difficult right now due to the use of
> a static allocation of shared memory, but if we had access to a single
> shared address space, it'd be easier to do any cleanup necessary for
> dynamically increasing/decreasing its size.
This again could be done with shared memory mapping + dynamic shared memory.
> Same with parallel workers - if we have a shared address space, the
> workers can pass any sized objects around without being required to
> move the tuples through DSM and waiting for the leader process to
> empty that buffer when it gets full.
Larger shared memory :)
Same for shared plan cache and shared schema cache.
> Sure, most of that is probably possible with DSM as well, it's just
> that I see a lot more issues that you need to take care of when you
> don't have a shared address space (such as the pointer translation we
> do in dsa_get_address).
All of the above seem to point to the need of a single thing - having
an option for shared memory mappings .
So let's focus on fixing things with minimal required change.
And this would not have an adverse affect on systems that can not
share mapping, they just won't become faster. And thay are all welcome
to add the option for shared mappings too if they see enough value in
it.
It could sound like the same thing as threaded model, but should need
much less changes and likely no changes for most out-of-tree
extensions
---
Cheers
Hannu
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 12:44 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
@ 2023-06-08 14:56 ` Robert Haas <[email protected]>
2023-06-08 15:02 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2 siblings, 1 reply; 43+ messages in thread
From: Robert Haas @ 2023-06-08 14:56 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Matthias van de Meent <[email protected]>; Andres Freund <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing <[email protected]> wrote:
> > That sounds like a bad idea, dynamic shared memory is more expensive
> > to maintain than our static shared memory systems, not in the least
> > because DSM is not guaranteed to share the same addresses in each
> > process' address space.
>
> Then this too needs to be fixed
Honestly, I'm struggling to respond to this non-sarcastically. I mean,
I was the one who implemented DSM. Do you think it works the way that
it works because I considered doing something smart and decided to do
something dumb instead?
Suppose you have two PostgreSQL backends A and B. If we're not running
on Windows, each of these was forked from the postmaster, so things
like the text and data segments and the main shared memory segment are
going to be mapped at the same address in both processes, because they
inherit those mappings from the postmaster. However, additional things
can get mapped into the address space of either process later. This
can happen in a variety of ways. For instance, a shared library can
get loaded into one process and not the other. Or it can get loaded
into both processes but at different addresses - keep in mind that
it's the OS, not PostgreSQL, that decides what address to use when
loading a shared library. Or, if one process allocates a bunch of
memory, then new address space will have to be mapped into that
process to handle those memory allocations and, again, it is the OS
that decides where to put those mappings. So over time the memory
mappings of these two processes can diverge arbitrarily. That means
that if the same DSM has to be mapped into both processes, there is no
guarantee that it can be placed at the same address in both processes.
The address that gets used in one process might not be available in
the other process.
It's worth pointing out here that there are no portable primitives
available for a process to examine what memory segments are mapped
into its address space. I think it's probably possible on every OS,
but it works differently on different ones. Linux exposes such details
through /proc, for example, but macOS doesn't have /proc. So if we're
using standard, portable primitives, we can't even TRY to put the DSM
at the same address in every process that maps it. But even if we used
non-portable primitives to examine what's mapped into the address
space of every process, it wouldn't solve the problem. Suppose 4
processes want to share a DSM, so they all run around and use
non-portable OS-specific interfaces to figure out where there's a free
chunk of address space large enough to accommodate that DSM and they
all map it there. Hooray! But then say a fifth process comes along and
it ALSO wants to map that DSM, but in that fifth process the address
space that was available in the other four processes has already been
used by something else. Well, now we're screwed.
The fact that DSM is expensive and awkward to use isn't a defect in
the implementation of DSM. It's a consequence of the fact that the
address space mappings in one PostgreSQL backend can be almost
arbitrarily different from the address space mappings in another
PostgreSQL backend. If only there were some kind of OS feature
available that would allow us to set things up so that all of the
PostgreSQL backends shared the same address space mappings!
Oh, right, there is: THREADS.
The fact that we don't use threads is the reason why DSM sucks and has
to suck. In fact it's the reason why DSM has to exist at all. Saying
"fix DSM instead of using threads" is roughly in the same category as
saying "if the peasants are revolting because they have no bread, then
let them eat cake." Both statements evince a complete failure to
understand the actual source of the problem.
With apologies for my grumpiness,
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 12:44 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 14:56 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
@ 2023-06-08 15:02 ` Hannu Krosing <[email protected]>
2023-06-08 15:56 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
2023-06-08 18:41 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
0 siblings, 2 replies; 43+ messages in thread
From: Hannu Krosing @ 2023-06-08 15:02 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Matthias van de Meent <[email protected]>; Andres Freund <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Thu, Jun 8, 2023 at 4:56 PM Robert Haas <[email protected]> wrote:
>
> On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing <[email protected]> wrote:
> > > That sounds like a bad idea, dynamic shared memory is more expensive
> > > to maintain than our static shared memory systems, not in the least
> > > because DSM is not guaranteed to share the same addresses in each
> > > process' address space.
> >
> > Then this too needs to be fixed
>
> Honestly, I'm struggling to respond to this non-sarcastically. I mean,
> I was the one who implemented DSM. Do you think it works the way that
> it works because I considered doing something smart and decided to do
> something dumb instead?
No, I meant that this needs to be fixed at OS level, by being able to
use the same mapping.
We should not shy away from asking the OS people for adding the useful
features still missing.
It was mentioned in the Unconference Kernel Hacker AMA talk and said
kernel hacker works for Oracle, andf they also seemed to be needing
this :)
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 12:44 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 14:56 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
2023-06-08 15:02 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
@ 2023-06-08 15:56 ` Robert Haas <[email protected]>
2023-06-08 18:54 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Robert Haas @ 2023-06-08 15:56 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Matthias van de Meent <[email protected]>; Andres Freund <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Thu, Jun 8, 2023 at 11:02 AM Hannu Krosing <[email protected]> wrote:
> No, I meant that this needs to be fixed at OS level, by being able to
> use the same mapping.
>
> We should not shy away from asking the OS people for adding the useful
> features still missing.
>
> It was mentioned in the Unconference Kernel Hacker AMA talk and said
> kernel hacker works for Oracle, andf they also seemed to be needing
> this :)
Fair enough, but we aspire to work on a bunch of different operating
systems. To make use of an OS facility, we need something that works
on at least Linux, Windows, macOS, and a few different BSD flavors.
It's not as if when the PostgreSQL project asks for a new operating
system facility everyone springs into action to provide it
immediately. And even if they did, and even if they all released an
implementation of whatever we requested next year, it would still be
at least five, more realistically ten, years before systems with those
facilities were ubiquitous. And unless we have truly obscene amounts
of clout in the OS community, it's likely that all of those different
operating systems would implement different things to meet the stated
need, and then we'd have to have a complex bunch of platform-dependent
code in order to keep working on all of those systems.
To me, this is a road to nowhere. I have no problem at all with us
expressing our needs to the OS community, but realistically, any
PostgreSQL feature that depends on an OS feature less than twenty
years old is going to have to be optional, which means that if we want
to do anything about sharing address space mappings in the next few
years, it's going to need to be based on threads.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 12:44 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 14:56 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
2023-06-08 15:02 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 15:56 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
@ 2023-06-08 18:54 ` Andres Freund <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Andres Freund @ 2023-06-08 18:54 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Hannu Krosing <[email protected]>; Matthias van de Meent <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
Hi,
On 2023-06-08 11:56:13 -0400, Robert Haas wrote:
> On Thu, Jun 8, 2023 at 11:02 AM Hannu Krosing <[email protected]> wrote:
> > No, I meant that this needs to be fixed at OS level, by being able to
> > use the same mapping.
> >
> > We should not shy away from asking the OS people for adding the useful
> > features still missing.
> >
> > It was mentioned in the Unconference Kernel Hacker AMA talk and said
> > kernel hacker works for Oracle, andf they also seemed to be needing
> > this :)
>
> Fair enough, but we aspire to work on a bunch of different operating
> systems. To make use of an OS facility, we need something that works
> on at least Linux, Windows, macOS, and a few different BSD flavors.
> It's not as if when the PostgreSQL project asks for a new operating
> system facility everyone springs into action to provide it
> immediately. And even if they did, and even if they all released an
> implementation of whatever we requested next year, it would still be
> at least five, more realistically ten, years before systems with those
> facilities were ubiquitous.
I'm less concerned about this aspect - most won't have upgraded to a version
of postgres that benefit from threaded postgres in a similar timeframe. And if
the benefits are large enough, people will move. But:
> And unless we have truly obscene amounts of clout in the OS community, it's
> likely that all of those different operating systems would implement
> different things to meet the stated need, and then we'd have to have a
> complex bunch of platform-dependent code in order to keep working on all of
> those systems.
And even more likely, they just won't do anything, because it's a model that
large parts of the industry have decided isn't going anywhere. It'd be one
thing if we had 5 kernel devs that we could deploy to work on this, but we
don't. So we have to convince kernel devs employed by others that somehow this
is an urgent enough thing that they should work on it. The likely, imo
justified, answer is just going to be: Fix your architecture, then we can
talk.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 12:44 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 14:56 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
2023-06-08 15:02 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
@ 2023-06-08 18:41 ` Andres Freund <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Andres Freund @ 2023-06-08 18:41 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Robert Haas <[email protected]>; Matthias van de Meent <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
Hi,
On 2023-06-08 17:02:08 +0200, Hannu Krosing wrote:
> On Thu, Jun 8, 2023 at 4:56 PM Robert Haas <[email protected]> wrote:
> >
> > On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing <[email protected]> wrote:
> > > > That sounds like a bad idea, dynamic shared memory is more expensive
> > > > to maintain than our static shared memory systems, not in the least
> > > > because DSM is not guaranteed to share the same addresses in each
> > > > process' address space.
> > >
> > > Then this too needs to be fixed
> >
> > Honestly, I'm struggling to respond to this non-sarcastically. I mean,
> > I was the one who implemented DSM. Do you think it works the way that
> > it works because I considered doing something smart and decided to do
> > something dumb instead?
>
> No, I meant that this needs to be fixed at OS level, by being able to
> use the same mapping.
>
> We should not shy away from asking the OS people for adding the useful
> features still missing.
There's a large part of this that is about hardware, not software. And
honestly, for most of the problems the answer is to just use threads. Adding
complexity to operating systems to make odd architectures like postgres'
better is a pretty dubious proposition.
I don't think we have even remotely enough influence on CPU design to make
e.g. *partial* TLB sharing across processes a thing.
> It was mentioned in the Unconference Kernel Hacker AMA talk and said
> kernel hacker works for Oracle, andf they also seemed to be needing
> this :)
The proposals around that don't really help us all that much. Sharing the page
table will be a bit more efficient, but it won't really change anything
dramatically. From what I understand they are primarily interested in
changing properties of a memory mapping across multiple processes, e.g. making
some memory executable and have that reflected in all processes. I don't think
this will help us much.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 12:44 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
@ 2023-06-08 15:08 ` Matthias van de Meent <[email protected]>
2 siblings, 0 replies; 43+ messages in thread
From: Matthias van de Meent @ 2023-06-08 15:08 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Andres Freund <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Thu, 8 Jun 2023 at 14:44, Hannu Krosing <[email protected]> wrote:
>
> On Thu, Jun 8, 2023 at 2:15 PM Matthias van de Meent
> <[email protected]> wrote:
> >
> > On Thu, 8 Jun 2023 at 11:54, Hannu Krosing <[email protected]> wrote:
> > >
> > > This part was touched in the "AMA with a Linux Kernale Hacker"
> > > Unconference session where he mentioned that the had proposed a
> > > 'mshare' syscall for this.
> > >
> > > So maybe a more fruitful way to fixing the perceived issues with
> > > process model is to push for small changes in Linux to overcome these
> > > avoiding a wholesale rewrite ?
> >
> > We support not just Linux, but also Windows and several (?) BSDs. I'm
> > not against pushing Linux to make things easier for us, but Linux is
> > an open source project, too, where someone need to put in time to get
> > the shiny things that you want. And I'd rather see our time spent in
> > PostgreSQL, as Linux is only used by a part of our user base.
>
> Do we have any statistics for the distribution of our user base ?
>
> My gut feeling says that for performance-critical use the non-Linux is
> in low single digits at best.
>
> My fascination for OpenSource started with realisation that instead of
> workarounds you can actually fix the problem at source. So if the
> specific problem is that TLB is not shared then the proper fix is
> making it shared instead of rewriting everything else to get around
> it. None of us is limited to writing code in PostgreSQL only. If the
> easiest and more generix fix can be done in Linux then so be it.
TLB is a CPU hardware facility, not something that the OS can decide
to share between processes. While sharing (some) OS memory management
facilities across threads might be possible (as you mention, that
mshare syscall would be an example), that doesn't solve the issue of
the hardware not supporting sharing TLB entries across processes. We'd
use less kernel memory for memory management, but the CPU would still
stall on TLB misses every time we switch processes on the CPU (unless
we somehow were able to use non-process-namespaced TLB entries, which
would make our processes not meaningfully different from threads
w.r.t. address space).
> > >
> > > Maybe we can already remove the distinction between static and dynamic
> > > shared memory ?
> >
> > That sounds like a bad idea, dynamic shared memory is more expensive
> > to maintain than our static shared memory systems, not in the least
> > because DSM is not guaranteed to share the same addresses in each
> > process' address space.
>
> Then this too needs to be fixed
That needs kernel facilities in all (most?) supported OSes, and I
think that's much more work than moving to threads:
Allocations from the kernel are arbitrarily random across the
available address space, so a DSM segment that is allocated in one
backend might overlap with unshared allocations of a different
backend, making those backends have conflicting memory address spaces.
The only way to make that work is to have a shared memory addressing
space, but some backends just not having the allocation mapped into
their local address space; which seems only slightly more isolated
than threads and much more effort to maintain.
> > > Though I already heard some complaints at the conference discussions
> > > that having the dynamic version available has made some developers
> > > sloppy in using it resulting in wastefulness.
> >
> > Do you know any examples of this wastefulness?
>
> No. Just somebody mentioned it in a hallway conversation and the rest
> of the developers present mumbled approvingly :)
The only "wastefulness" that I know of in our use of DSM is the queue,
and that's by design: We need to move data from a backend's private
memory to memory that's accessible to other backends; i.e. shared
memory. You can't do that without copying or exposing your private
memory.
> > > Still we should be focusing our attention at solving the issues and
> > > not at "moving to threads" and hoping this will fix the issues by
> > > itself.
> >
> > I suspect that it is much easier to solve some of the issues when
> > working in a shared address space.
>
> Probably. But it would come at the cost of needing to change a lot of
> other parts of PostgreSQL.
>
> I am not against making code cleaner for potential threaded model
> support. I am just a bit sceptical about the actual switch being easy,
> or doable in the next 10-15 years.
PostgreSQL only has a support cycle of 5 years. 5 years after the last
release of un-threaded PostgreSQL we could drop support for "legacy"
extension models that don't support threading.
> > E.g. resizing shared_buffers is difficult right now due to the use of
> > a static allocation of shared memory, but if we had access to a single
> > shared address space, it'd be easier to do any cleanup necessary for
> > dynamically increasing/decreasing its size.
>
> This again could be done with shared memory mapping + dynamic shared memory.
Yes, but as I said, that's much more difficult than lock and/or atomic
operations on shared-between-backends static variables, because if
these variables aren't in shared memory you need to pass the messages
to update the variables to all backends.
> > Same with parallel workers - if we have a shared address space, the
> > workers can pass any sized objects around without being required to
> > move the tuples through DSM and waiting for the leader process to
> > empty that buffer when it gets full.
>
> Larger shared memory :)
>
> Same for shared plan cache and shared schema cache.
Shared memory in processes is not free, if only because the TLB gets
saturated much faster.
> > Sure, most of that is probably possible with DSM as well, it's just
> > that I see a lot more issues that you need to take care of when you
> > don't have a shared address space (such as the pointer translation we
> > do in dsa_get_address).
>
> All of the above seem to point to the need of a single thing - having
> an option for shared memory mappings .
>
> So let's focus on fixing things with minimal required change.
That seems logical, but not all kernels support dynamic shared memory
mappings. And, as for your suggested solution, I couldn't find much
info on this mshare syscall (or its successor mmap/VM_SHARED_PT), nor
on whether it would actually fix the TLB issue.
> And this would not have an adverse affect on systems that can not
> share mapping, they just won't become faster. And thay are all welcome
> to add the option for shared mappings too if they see enough value in
> it.
>
> It could sound like the same thing as threaded model, but should need
> much less changes and likely no changes for most out-of-tree
> extensions
We can't expect the kernel to fix everything for us - that's what we
build PostgreSQL for. Where possible, we do want to rely on OS
primitives, but I'm not sure that it would be easy to share memory
address mappings across backends, for reasons including the above
("That needs kernel facilities in all [...] more effort to maintain").
Kind regards,
Matthias van de Meent
Neon, Inc.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 12:44 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
@ 2023-06-08 16:05 ` Greg Sabino Mullane <[email protected]>
2 siblings, 0 replies; 43+ messages in thread
From: Greg Sabino Mullane @ 2023-06-08 16:05 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: pgsql-hackers
On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing <[email protected]> wrote:
> Do we have any statistics for the distribution of our user base ?
>
> My gut feeling says that for performance-critical use the non-Linux is
> in low single digits at best.
>
Stats are probably not possible, but based on years of consulting, as well
as watching places like SO, Slack, IRC, etc. over the years, IMO that's a
very accurate gut feeling. I'd hazard 1% or less for non-Linux systems.
Cheers,
Greg
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
@ 2023-06-08 14:08 ` Robert Haas <[email protected]>
2 siblings, 0 replies; 43+ messages in thread
From: Robert Haas @ 2023-06-08 14:08 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Wed, Jun 7, 2023 at 5:37 PM Andres Freund <[email protected]> wrote:
> I think we're starting to hit quite a few limits related to the process model,
> particularly on bigger machines. The overhead of cross-process context
> switches is inherently higher than switching between threads in the same
> process - and my suspicion is that that overhead will continue to
> increase. Once you have a significant number of connections we end up spending
> a *lot* of time in TLB misses, and that's inherent to the process model,
> because you can't share the TLB across processes.
This is a very good point.
Our default posture on this mailing list is to try to maximize use of
OS facilities rather than reimplementing things - well and good. But
if a user writes a query with FOO JOIN BAR ON FOO.X = BAR.X OR FOO.Y =
BAR.Y and then complains that the resulting query plan sucks, we don't
slink off in embarrassment: we tell the user that there's not really
any fast plan for that query and that if they write queries like that
they have to live with the consequences. But the same thing applies
here. To the extent that context switching between more processes is
more expensive than context switching between threads for
hardware-related reasons, that's not something that the OS can fix for
us. If we choose to do the expensive thing then we pay the overhead.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
@ 2023-07-27 13:27 ` David Geier <[email protected]>
2023-08-11 12:05 ` Re: Let's make PostgreSQL multi-threaded Merlin Moncure <[email protected]>
2 siblings, 1 reply; 43+ messages in thread
From: David Geier @ 2023-07-27 13:27 UTC (permalink / raw)
To: Andres Freund <[email protected]>; Jonathan S. Katz <[email protected]>; +Cc: Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
Hi,
On 6/7/23 23:37, Andres Freund wrote:
> I think we're starting to hit quite a few limits related to the process model,
> particularly on bigger machines. The overhead of cross-process context
> switches is inherently higher than switching between threads in the same
> process - and my suspicion is that that overhead will continue to
> increase. Once you have a significant number of connections we end up spending
> a *lot* of time in TLB misses, and that's inherent to the process model,
> because you can't share the TLB across processes.
Another problem I haven't seen mentioned yet is the excessive kernel
memory usage because every process has its own set of page table entries
(PTEs). Without huge pages the amount of wasted memory can be huge if
shared buffers are big.
For example with 256 GiB of used shared buffers a single process needs
about 256 MiB for the PTEs (for simplicity I ignored the tree structure
of the page tables and just took the number of 4k pages times 4 bytes
per PTE). With 512 connections, which is not uncommon for machines with
many cores, a total of 128 GiB of memory is just spent on page tables.
We used non-transparent huge pages to work around this limitation but
they come with plenty of provisioning challenges, especially in cloud
infrastructures where different services run next to each other on the
same server. Transparent huge pages have unpredictable performance
disadvantages. Also if some backends only use shared buffers sparsely,
memory is wasted for the remaining, unused range inside the huge page.
--
David Geier
(ServiceNow)
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-07-27 13:27 ` Re: Let's make PostgreSQL multi-threaded David Geier <[email protected]>
@ 2023-08-11 12:05 ` Merlin Moncure <[email protected]>
2023-08-25 12:01 ` Re: Let's make PostgreSQL multi-threaded David Geier <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Merlin Moncure @ 2023-08-11 12:05 UTC (permalink / raw)
To: David Geier <[email protected]>; +Cc: Andres Freund <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Thu, Jul 27, 2023 at 8:28 AM David Geier <[email protected]> wrote:
> Hi,
>
> On 6/7/23 23:37, Andres Freund wrote:
> > I think we're starting to hit quite a few limits related to the process
> model,
> > particularly on bigger machines. The overhead of cross-process context
> > switches is inherently higher than switching between threads in the same
> > process - and my suspicion is that that overhead will continue to
> > increase. Once you have a significant number of connections we end up
> spending
> > a *lot* of time in TLB misses, and that's inherent to the process model,
> > because you can't share the TLB across processes.
>
> Another problem I haven't seen mentioned yet is the excessive kernel
> memory usage because every process has its own set of page table entries
> (PTEs). Without huge pages the amount of wasted memory can be huge if
> shared buffers are big.
Hm, noted this upthread, but asking again, does this
help/benefit interactions with the operating system make oom kill
situations less likely? These things are the bane of my existence, and
I'm having a hard time finding a solution that prevents them other than
running pgbouncer and lowering max_connections, which adds complexity. I
suspect I'm not the only one dealing with this. What's really scary about
these situations is they come without warning. Here's a pretty typical
example per sar -r.
kbmemfree kbmemused %memused kbbuffers kbcached kbcommit
%commit kbactive kbinact kbdirty
14:20:02 461612 15803476 97.16 0 11120280 12346980
60.35 10017820 4806356 220
14:30:01 378244 15886844 97.67 0 11239012 12296276
60.10 10003540 4909180 240
14:40:01 308632 15956456 98.10 0 11329516 12295892
60.10 10015044 4981784 200
14:50:01 458956 15806132 97.18 0 11383484 12101652
59.15 9853612 5019916 112
15:00:01 10592736 5672352 34.87 0 4446852 8378324
40.95 1602532 3473020 264 <-- reboot!
15:10:01 9151160 7113928 43.74 0 5298184 8968316
43.83 2714936 3725092 124
15:20:01 8629464 7635624 46.94 0 6016936 8777028
42.90 2881044 4102888 148
15:30:01 8467884 7797204 47.94 0 6285856 8653908
42.30 2830572 4323292 436
15:40:02 8077480 8187608 50.34 0 6828240 8482972
41.46 2885416 4671620 320
15:50:01 7683504 8581584 52.76 0 7226132 8511932
41.60 2998752 4958880 308
16:00:01 7239068 9026020 55.49 0 7649948 8496764
41.53 3032140 5358388 232
16:10:01 7030208 9234880 56.78 0 7899512 8461588
41.36 3108692 5492296 216
Triggering query was heavy (maybe even runaway), server load was minimal
otherwise:
CPU %user %nice %system %iowait %steal
%idle
14:30:01 all 9.55 0.00 0.63 0.02 0.00
89.81
14:40:01 all 9.95 0.00 0.69 0.02 0.00
89.33
14:50:01 all 10.22 0.00 0.83 0.02 0.00
88.93
15:00:01 all 10.62 0.00 1.63 0.76 0.00
86.99
15:10:01 all 8.55 0.00 0.72 0.12 0.00
90.61
The conjecture here is that lots of idle connections make the server appear
to have less memory available than it looks, and sudden transient demands
can cause it to destabilize.
Just throwing it out there, if it can be shown to help it may be supportive
of moving forward with something like this, either instead of, or along
with, O_DIRECT or other internalized database memory management
strategies. Lowering context switches, faster page access etc are of
course nice would not be a game changer for the workloads we see which are
pretty varied (OLTP, analytics) although we don't extremely high
transaction rates.
merlin
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-07-27 13:27 ` Re: Let's make PostgreSQL multi-threaded David Geier <[email protected]>
2023-08-11 12:05 ` Re: Let's make PostgreSQL multi-threaded Merlin Moncure <[email protected]>
@ 2023-08-25 12:01 ` David Geier <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: David Geier @ 2023-08-25 12:01 UTC (permalink / raw)
To: Merlin Moncure <[email protected]>; +Cc: Andres Freund <[email protected]>; Jonathan S. Katz <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
Hi,
On 8/11/23 14:05, Merlin Moncure wrote:
> On Thu, Jul 27, 2023 at 8:28 AM David Geier <[email protected]> wrote:
>
> Hi,
>
> On 6/7/23 23:37, Andres Freund wrote:
> > I think we're starting to hit quite a few limits related to the
> process model,
> > particularly on bigger machines. The overhead of cross-process
> context
> > switches is inherently higher than switching between threads in
> the same
> > process - and my suspicion is that that overhead will continue to
> > increase. Once you have a significant number of connections we
> end up spending
> > a *lot* of time in TLB misses, and that's inherent to the
> process model,
> > because you can't share the TLB across processes.
>
> Another problem I haven't seen mentioned yet is the excessive kernel
> memory usage because every process has its own set of page table
> entries
> (PTEs). Without huge pages the amount of wasted memory can be huge if
> shared buffers are big.
>
>
> Hm, noted this upthread, but asking again, does this
> help/benefit interactions with the operating system make oom kill
> situations less likely? These things are the bane of my existence,
> and I'm having a hard time finding a solution that prevents them other
> than running pgbouncer and lowering max_connections, which adds
> complexity. I suspect I'm not the only one dealing with this.
> What's really scary about these situations is they come without
> warning. Here's a pretty typical example per sar -r.
>
> The conjecture here is that lots of idle connections make the server
> appear to have less memory available than it looks, and sudden
> transient demands can cause it to destabilize.
It does in the sense that your server will have more memory available in
case you have many long living connections around. Every connection has
less kernel memory overhead if you will. Of course even then a runaway
query will be able to invoke the OOM killer. The unfortunate thing with
the OOM killer is that, in my experience, it often kills the
checkpointer. That's because the checkpointer will touch all of shared
buffers over time which makes it likely to get selected by the OOM
killer. Have you tried disabling memory overcommit?
--
David Geier
(ServiceNow)
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
@ 2023-06-07 19:20 ` Tomas Vondra <[email protected]>
2023-06-07 21:39 ` Re: Let's make PostgreSQL multi-threaded Thomas Kellerer <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Tomas Vondra @ 2023-06-07 19:20 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>; +Cc: pgsql-hackers
On 6/5/23 17:33, Heikki Linnakangas wrote:
> On 05/06/2023 11:18, Tom Lane wrote:
>> Heikki Linnakangas <[email protected]> writes:
>>> I spoke with some folks at PGCon about making PostgreSQL multi-threaded,
>>> so that the whole server runs in a single process, with multiple
>>> threads. It has been discussed many times in the past, last thread on
>>> pgsql-hackers was back in 2017 when Konstantin made some experiments
>>> [0].
>>
>>> I feel that there is now pretty strong consensus that it would be a good
>>> thing, more so than before. Lots of work to get there, and lots of
>>> details to be hashed out, but no objections to the idea at a high level.
>>
>>> The purpose of this email is to make that silent consensus explicit. If
>>> you have objections to switching from the current multi-process
>>> architecture to a single-process, multi-threaded architecture, please
>>> speak up.
>>
>> For the record, I think this will be a disaster. There is far too much
>> code that will get broken, largely silently, and much of it is not
>> under our control.
>
> Noted. Other large projects have gone through this transition. It's not
> easy, but it's a lot easier now than it was 10 years ago. The platform
> and compiler support is there now, all libraries have thread-safe
> interfaces, etc.
>
Is the platform support really there for all platforms we want/intend to
support? I have no problem believing that for modern Linux/BSD systems,
but what about the older stuff we currently support.
Also, which other projects did this transition? Is there something we
could learn from them? Were they restricted to much smaller list of
platforms?
> I don't expect you or others to buy into any particular code change at
> this point, or to contribute time into it. Just to accept that it's a
> worthwhile goal. If the implementation turns out to be a disaster, then
> it won't be accepted, of course. But I'm optimistic.
>
I personally am not opposed to the effort in principle, but how do you
even evaluate cost and benefits for a transition like this? I have no
idea how to quantify the costs/benefits for this as a single change.
I've seen some benchmarks in the past, but it's hard to say which of
these improvements are possible only with threads, and what would be
doable with less invasive changes with the process model.
IMHO the only way to move this forward is to divide this into smaller
changes, each of which gives us some benefit we'd want anyway. For
example, this thread already mentioned improving handling of many
connections. AFAICS that requires isolating "session state", which seems
useful even without a full switch to threads as it makes connection
pooling simpler. It should be easier to get a buy-in for these changes,
while introducing abstractions simplifying the switch to threads.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-07 19:20 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
@ 2023-06-07 21:39 ` Thomas Kellerer <[email protected]>
2023-06-07 22:37 ` Re: Let's make PostgreSQL multi-threaded Jeremy Schneider <[email protected]>
2023-06-08 09:56 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
0 siblings, 2 replies; 43+ messages in thread
From: Thomas Kellerer @ 2023-06-07 21:39 UTC (permalink / raw)
To: [email protected]
Tomas Vondra schrieb am 07.06.2023 um 21:20:
> Also, which other projects did this transition? Is there something we
> could learn from them? Were they restricted to much smaller list of
> platforms?
Firebird did this a while ago if I'm not mistaken.
Not open source, but Oracle was historically multi-threaded on Windows and multi-process on all other platforms.
I _think_ starting with 19c you can optionally run it multi-threaded on Linux as well.
But I doubt, they are willing to share any insights ;)
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-07 19:20 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
2023-06-07 21:39 ` Re: Let's make PostgreSQL multi-threaded Thomas Kellerer <[email protected]>
@ 2023-06-07 22:37 ` Jeremy Schneider <[email protected]>
2023-06-07 23:37 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
2023-06-08 05:55 ` Re: Let's make PostgreSQL multi-threaded Drouvot, Bertrand <[email protected]>
1 sibling, 2 replies; 43+ messages in thread
From: Jeremy Schneider @ 2023-06-07 22:37 UTC (permalink / raw)
To: [email protected]; +Cc: Thomas Kellerer <[email protected]>; Tomas Vondra <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>
On 6/7/23 2:39 PM, Thomas Kellerer wrote:
> Tomas Vondra schrieb am 07.06.2023 um 21:20:
>> Also, which other projects did this transition? Is there something we
>> could learn from them? Were they restricted to much smaller list of
>> platforms?
>
> Not open source, but Oracle was historically multi-threaded on Windows
> and multi-process on all other platforms.
> I _think_ starting with 19c you can optionally run it multi-threaded on
> Linux as well.
Looks like it actually became publicly available in 12c. AFAICT Oracle
supports both modes today, with a config parameter to switch between them.
This is a very interesting case study.
Concepts Manual:
https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/process-architecture.html#GUID-4...
Reference:
https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/THREADED_EXECUTION.html#GUID-7A6...
List of Oracle process types, which ones can run as threads and which
ones always run as processes:
https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/background-processes.html#GUID-8...
Looks like they have four processes that will never run in threads:
* dbwriter (writes dirty blocks in background)
* process monitor (cleanup after process crash to avoid full server
restarts) <jealous>
* process spawner (like postmaster)
* time keeper process
Per Tim Hall's oracle-base, it seems that plenty of people are sticking
with the process model, and that one use case for threads was:
"consolidating lots of instances onto a single server without using the
multitennant option. Without the multithreaded model, the number of OS
processes could get very high."
https://oracle-base.com/articles/12c/multithreaded-model-using-threaded_execution_12cr1
I did google search for "oracle threaded_execution" and browsed a bit;
didn't see anything that seems earth shattering so far.
Ludovico Caldara and Martin Bach published blogs when it was first
released, which just introduced but didn't test or hammer on it. The
feature has existed for 10 years now and I don't see any blog posts
saying that "everyone should use this because it doubles your
performance" or anything like that. I think if there were really
significant performance gains then there would be many interesting blog
posts on the internet by now from the independent Oracle professional
community - I know many of these people.
In fact, there's an interesting blog by Kamil Stawiarski from 2015 where
he actually observed one case of /slower/ performance with threads. That
blog post ends with: "So I raise the question: why and when use threaded
execution? If ever?"
https://blog.ora-600.pl/2015/12/17/oracle-12c-internals-of-threaded-execution/
I'm not sure if he ever got an answer
-Jeremy
--
http://about.me/jeremy_schneider
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-07 19:20 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
2023-06-07 21:39 ` Re: Let's make PostgreSQL multi-threaded Thomas Kellerer <[email protected]>
2023-06-07 22:37 ` Re: Let's make PostgreSQL multi-threaded Jeremy Schneider <[email protected]>
@ 2023-06-07 23:37 ` Thomas Munro <[email protected]>
2023-06-08 10:37 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
2023-06-10 00:23 ` Re: Let's make PostgreSQL multi-threaded Bruce Momjian <[email protected]>
1 sibling, 2 replies; 43+ messages in thread
From: Thomas Munro @ 2023-06-07 23:37 UTC (permalink / raw)
To: Jeremy Schneider <[email protected]>; +Cc: [email protected]; Thomas Kellerer <[email protected]>; Tomas Vondra <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>
On Thu, Jun 8, 2023 at 10:37 AM Jeremy Schneider
<[email protected]> wrote:
> On 6/7/23 2:39 PM, Thomas Kellerer wrote:
> > Tomas Vondra schrieb am 07.06.2023 um 21:20:
> >> Also, which other projects did this transition? Is there something we
> >> could learn from them? Were they restricted to much smaller list of
> >> platforms?
> >
> > Not open source, but Oracle was historically multi-threaded on Windows
> > and multi-process on all other platforms.
> > I _think_ starting with 19c you can optionally run it multi-threaded on
> > Linux as well.
> Looks like it actually became publicly available in 12c. AFAICT Oracle
> supports both modes today, with a config parameter to switch between them.
It's old, but this describes the 4 main models and which well known
RDBMSes use them in section 2.3:
https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf
TL;DR DB2 is the winner, it can do process-per-connection,
thread-per-connection, process-pool or thread-pool.
I understand this thread to be about thread-per-connection (= backend,
session, socket) for now.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-07 19:20 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
2023-06-07 21:39 ` Re: Let's make PostgreSQL multi-threaded Thomas Kellerer <[email protected]>
2023-06-07 22:37 ` Re: Let's make PostgreSQL multi-threaded Jeremy Schneider <[email protected]>
2023-06-07 23:37 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
@ 2023-06-08 10:37 ` Tomas Vondra <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Tomas Vondra @ 2023-06-08 10:37 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; Jeremy Schneider <[email protected]>; +Cc: [email protected]; Thomas Kellerer <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>
On 6/8/23 01:37, Thomas Munro wrote:
> On Thu, Jun 8, 2023 at 10:37 AM Jeremy Schneider
> <[email protected]> wrote:
>> On 6/7/23 2:39 PM, Thomas Kellerer wrote:
>>> Tomas Vondra schrieb am 07.06.2023 um 21:20:
>>>> Also, which other projects did this transition? Is there something we
>>>> could learn from them? Were they restricted to much smaller list of
>>>> platforms?
>>>
>>> Not open source, but Oracle was historically multi-threaded on Windows
>>> and multi-process on all other platforms.
>>> I _think_ starting with 19c you can optionally run it multi-threaded on
>>> Linux as well.
>> Looks like it actually became publicly available in 12c. AFAICT Oracle
>> supports both modes today, with a config parameter to switch between them.
>
> It's old, but this describes the 4 main models and which well known
> RDBMSes use them in section 2.3:
>
> https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf
>
> TL;DR DB2 is the winner, it can do process-per-connection,
> thread-per-connection, process-pool or thread-pool.
>
I think the basic architectures are known, especially from the user
perspective. I'm more interested in challenges the projects faced while
moving from one architecture to the other, or how / why they support
more than just one, etc.
In [1] Heikki argued that:
I don't think this is worth it, unless we plan to eventually remove
the multi-process mode. ... As long as you need to also support
processes, you need to code to the lowest common denominator and
don't really get the benefits.
But these projects clearly support multiple architectures, and have no
intention to ditch some of them. So how did they do that? Surely they
think there are benefits.
One option would be to just have separate code paths for processes and
threads, but the effort required to maintain and improve that would be
deadly. So the only feasible option seems to be they managed to abstract
the subsystems enough for the "regular" code to not care about model.
[1]
https://www.postgresql.org/message-id/[email protected]
> I understand this thread to be about thread-per-connection (= backend,
> session, socket) for now.
Maybe, although people also proposed to switch the parallel query to
threads (so that'd be multiple threads per session). But I don't think
it really matters, the concerns are mostly about moving from one
architecture to another and/or supporting both.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-07 19:20 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
2023-06-07 21:39 ` Re: Let's make PostgreSQL multi-threaded Thomas Kellerer <[email protected]>
2023-06-07 22:37 ` Re: Let's make PostgreSQL multi-threaded Jeremy Schneider <[email protected]>
2023-06-07 23:37 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
@ 2023-06-10 00:23 ` Bruce Momjian <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Bruce Momjian @ 2023-06-10 00:23 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Jeremy Schneider <[email protected]>; [email protected]; Thomas Kellerer <[email protected]>; Tomas Vondra <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>
On Thu, Jun 8, 2023 at 11:37:00AM +1200, Thomas Munro wrote:
> It's old, but this describes the 4 main models and which well known
> RDBMSes use them in section 2.3:
>
> https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf
>
> TL;DR DB2 is the winner, it can do process-per-connection,
> thread-per-connection, process-pool or thread-pool.
>
> I understand this thread to be about thread-per-connection (= backend,
> session, socket) for now.
I am quite confused that few people seem to care about which model,
processes or threads, is better for Oracle, and how having both methods
available can be a reasonable solution to maintain. Someone suggested
they abstracted the differences so the maintenance burden was minor, but
that seems very hard to me.
Did these vendors start with processes, add threads, and then find that
threads had downsides so they had to keep both?
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-07 19:20 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
2023-06-07 21:39 ` Re: Let's make PostgreSQL multi-threaded Thomas Kellerer <[email protected]>
2023-06-07 22:37 ` Re: Let's make PostgreSQL multi-threaded Jeremy Schneider <[email protected]>
@ 2023-06-08 05:55 ` Drouvot, Bertrand <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Drouvot, Bertrand @ 2023-06-08 05:55 UTC (permalink / raw)
To: Jeremy Schneider <[email protected]>; [email protected]; +Cc: Thomas Kellerer <[email protected]>; Tomas Vondra <[email protected]>; Heikki Linnakangas <[email protected]>; Tom Lane <[email protected]>
Hi,
On 6/8/23 12:37 AM, Jeremy Schneider wrote:
> On 6/7/23 2:39 PM, Thomas Kellerer wrote:
>> Tomas Vondra schrieb am 07.06.2023 um 21:20:
>
> I did google search for "oracle threaded_execution" and browsed a bit;
> didn't see anything that seems earth shattering so far.
FWIW, I recall Karl Arao's wiki page: https://karlarao.github.io/karlaraowiki/#%2212c%20threaded_execution%22
where some performance and memory consumption studies have been done.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-07 19:20 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
2023-06-07 21:39 ` Re: Let's make PostgreSQL multi-threaded Thomas Kellerer <[email protected]>
@ 2023-06-08 09:56 ` Hannu Krosing <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Hannu Krosing @ 2023-06-08 09:56 UTC (permalink / raw)
To: Thomas Kellerer <[email protected]>; +Cc: [email protected]
I think I remember that in the early days of development somebody did
send a patch-set for making PostgreSQL threaded on Solaris.
I don't remember why this did not catch on.
On Wed, Jun 7, 2023 at 11:40 PM Thomas Kellerer <[email protected]> wrote:
>
> Tomas Vondra schrieb am 07.06.2023 um 21:20:
> > Also, which other projects did this transition? Is there something we
> > could learn from them? Were they restricted to much smaller list of
> > platforms?
>
> Firebird did this a while ago if I'm not mistaken.
>
> Not open source, but Oracle was historically multi-threaded on Windows and multi-process on all other platforms.
> I _think_ starting with 19c you can optionally run it multi-threaded on Linux as well.
>
> But I doubt, they are willing to share any insights ;)
>
>
>
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
@ 2023-06-05 18:51 ` Andrew Dunstan <[email protected]>
2023-06-05 20:08 ` Re: Let's make PostgreSQL multi-threaded Joe Conway <[email protected]>
2023-06-06 22:26 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
3 siblings, 2 replies; 43+ messages in thread
From: Andrew Dunstan @ 2023-06-05 18:51 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Heikki Linnakangas <[email protected]>; +Cc: pgsql-hackers
On 2023-06-05 Mo 11:18, Tom Lane wrote:
> Heikki Linnakangas<[email protected]> writes:
>> I spoke with some folks at PGCon about making PostgreSQL multi-threaded,
>> so that the whole server runs in a single process, with multiple
>> threads. It has been discussed many times in the past, last thread on
>> pgsql-hackers was back in 2017 when Konstantin made some experiments [0].
>> I feel that there is now pretty strong consensus that it would be a good
>> thing, more so than before. Lots of work to get there, and lots of
>> details to be hashed out, but no objections to the idea at a high level.
>> The purpose of this email is to make that silent consensus explicit. If
>> you have objections to switching from the current multi-process
>> architecture to a single-process, multi-threaded architecture, please
>> speak up.
> For the record, I think this will be a disaster. There is far too much
> code that will get broken, largely silently, and much of it is not
> under our control.
>
>
If we were starting out today we would probably choose a threaded
implementation. But moving to threaded now seems to me like a
multi-year-multi-person project with the prospect of years to come
chasing bugs and the prospect of fairly modest advantages. The risk to
reward doesn't look great.
That's my initial reaction. I could be convinced otherwise.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 18:51 ` Re: Let's make PostgreSQL multi-threaded Andrew Dunstan <[email protected]>
@ 2023-06-05 20:08 ` Joe Conway <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Joe Conway @ 2023-06-05 20:08 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; Tom Lane <[email protected]>; Heikki Linnakangas <[email protected]>; +Cc: pgsql-hackers
On 6/5/23 14:51, Andrew Dunstan wrote:
>
> On 2023-06-05 Mo 11:18, Tom Lane wrote:
>> Heikki Linnakangas<[email protected]> writes:
>>> I spoke with some folks at PGCon about making PostgreSQL multi-threaded,
>>> so that the whole server runs in a single process, with multiple
>>> threads. It has been discussed many times in the past, last thread on
>>> pgsql-hackers was back in 2017 when Konstantin made some experiments [0].
>>> I feel that there is now pretty strong consensus that it would be a good
>>> thing, more so than before. Lots of work to get there, and lots of
>>> details to be hashed out, but no objections to the idea at a high level.
>>> The purpose of this email is to make that silent consensus explicit. If
>>> you have objections to switching from the current multi-process
>>> architecture to a single-process, multi-threaded architecture, please
>>> speak up.
>> For the record, I think this will be a disaster. There is far too much
>> code that will get broken, largely silently, and much of it is not
>> under our control.
>
> If we were starting out today we would probably choose a threaded
> implementation. But moving to threaded now seems to me like a
> multi-year-multi-person project with the prospect of years to come
> chasing bugs and the prospect of fairly modest advantages. The risk to
> reward doesn't look great.
>
> That's my initial reaction. I could be convinced otherwise.
I read through the thread thus far, and Andrew's response is the one
that best aligns with my reaction.
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 18:51 ` Re: Let's make PostgreSQL multi-threaded Andrew Dunstan <[email protected]>
@ 2023-06-06 22:26 ` Thomas Munro <[email protected]>
2023-06-08 17:02 ` Re: Let's make PostgreSQL multi-threaded Ilya Anfimov <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Thomas Munro @ 2023-06-06 22:26 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Tom Lane <[email protected]>; Heikki Linnakangas <[email protected]>; pgsql-hackers
On Tue, Jun 6, 2023 at 6:52 AM Andrew Dunstan <[email protected]> wrote:
> If we were starting out today we would probably choose a threaded implementation. But moving to threaded now seems to me like a multi-year-multi-person project with the prospect of years to come chasing bugs and the prospect of fairly modest advantages. The risk to reward doesn't look great.
>
> That's my initial reaction. I could be convinced otherwise.
Here is one thing I often think about when contemplating threads.
Take a look at dsa.c. It calls itself a shared memory allocator, but
really it has two jobs, the second being to provide software emulation
of virtual memory. That’s behind dshash.c and now the stats system,
and various parts of the parallel executor code. It’s slow and
complicated, and far from the state of the art. I wrote that code
(building on allocator code from Robert) with the expectation that it
was a transitional solution to unblock a bunch of projects. I always
expected that we'd eventually be deleting it. When I explain that
subsystem to people who are not steeped in the lore of PostgreSQL, it
sounds completely absurd. I mean, ... it is, right? My point is
that we’re doing pretty unreasonable and inefficient contortions to
develop new features -- we're not just happily chugging along without
threads at no cost.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 18:51 ` Re: Let's make PostgreSQL multi-threaded Andrew Dunstan <[email protected]>
2023-06-06 22:26 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
@ 2023-06-08 17:02 ` Ilya Anfimov <[email protected]>
2023-06-08 19:10 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Ilya Anfimov @ 2023-06-08 17:02 UTC (permalink / raw)
To: [email protected]; pgsql-hackers
On Wed, Jun 07, 2023 at 10:26:07AM +1200, Thomas Munro wrote:
> On Tue, Jun 6, 2023 at 6:52???AM Andrew Dunstan <[email protected]> wrote:
> > If we were starting out today we would probably choose a threaded implementation. But moving to threaded now seems to me like a multi-year-multi-person project with the prospect of years to come chasing bugs and the prospect of fairly modest advantages. The risk to reward doesn't look great.
> >
> > That's my initial reaction. I could be convinced otherwise.
>
> Here is one thing I often think about when contemplating threads.
> Take a look at dsa.c. It calls itself a shared memory allocator, but
> really it has two jobs, the second being to provide software emulation
> of virtual memory. That???s behind dshash.c and now the stats system,
> and various parts of the parallel executor code. It???s slow and
> complicated, and far from the state of the art. I wrote that code
> (building on allocator code from Robert) with the expectation that it
> was a transitional solution to unblock a bunch of projects. I always
> expected that we'd eventually be deleting it. When I explain that
> subsystem to people who are not steeped in the lore of PostgreSQL, it
> sounds completely absurd. I mean, ... it is, right? My point is
Isn't all the memory operations would require nearly the same
shared memory allocators if someone switches to a threaded imple-
mentation?
> that we???re doing pretty unreasonable and inefficient contortions to
> develop new features -- we're not just happily chugging along without
> threads at no cost.
>
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 18:51 ` Re: Let's make PostgreSQL multi-threaded Andrew Dunstan <[email protected]>
2023-06-06 22:26 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
2023-06-08 17:02 ` Re: Let's make PostgreSQL multi-threaded Ilya Anfimov <[email protected]>
@ 2023-06-08 19:10 ` Thomas Munro <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Thomas Munro @ 2023-06-08 19:10 UTC (permalink / raw)
To: Ilya Anfimov <[email protected]>; [email protected]; pgsql-hackers
On Fri, Jun 9, 2023 at 5:02 AM Ilya Anfimov <[email protected]> wrote:
> Isn't all the memory operations would require nearly the same
> shared memory allocators if someone switches to a threaded imple-
> mentation?
It's true that we'd need concurrency-aware MemoryContext
implementations (details can be debated), but we wouldn't need that
address translation layer, which adds a measurable cost at every
access.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
@ 2023-06-05 21:07 ` Jonah H. Harris <[email protected]>
2023-06-06 00:27 ` Re: Let's make PostgreSQL multi-threaded Jeremy Schneider <[email protected]>
2023-06-06 12:06 ` Re: Let's make PostgreSQL multi-threaded Konstantin Knizhnik <[email protected]>
3 siblings, 2 replies; 43+ messages in thread
From: Jonah H. Harris @ 2023-06-05 21:07 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Heikki Linnakangas <[email protected]>; pgsql-hackers
On Mon, Jun 5, 2023 at 8:18 AM Tom Lane <[email protected]> wrote:
> For the record, I think this will be a disaster. There is far too much
> code that will get broken, largely silently, and much of it is not
> under our control.
>
While I've long been in favor of a multi-threaded implementation, now in my
old age, I tend to agree with Tom. I'd be interested in Konstantin's
thoughts (and PostgresPro's experience) of multi-threaded vs. internal
pooling with the current process-based model. I recall looking at and
playing with Konstantin's implementations of both, which were impressive.
Yes, the latter doesn't solve the same issues, but many real-world ones
where multi-threaded is argued. Personally, I think there would be not only
a significant amount of time spent dealing with in-the-field stability
regressions before a multi-threaded implementation matures, but it would
also increase the learning curve for anyone trying to start with internals
development.
--
Jonah H. Harris
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 21:07 ` Re: Let's make PostgreSQL multi-threaded Jonah H. Harris <[email protected]>
@ 2023-06-06 00:27 ` Jeremy Schneider <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Jeremy Schneider @ 2023-06-06 00:27 UTC (permalink / raw)
To: Jonah H. Harris <[email protected]>; Tom Lane <[email protected]>; +Cc: Heikki Linnakangas <[email protected]>; pgsql-hackers
On 6/5/23 2:07 PM, Jonah H. Harris wrote:
> On Mon, Jun 5, 2023 at 8:18 AM Tom Lane <[email protected]
> <mailto:[email protected]>> wrote:
>
> For the record, I think this will be a disaster. There is far too much
> code that will get broken, largely silently, and much of it is not
> under our control.
>
>
> While I've long been in favor of a multi-threaded implementation, now in
> my old age, I tend to agree with Tom. I'd be interested in Konstantin's
> thoughts (and PostgresPro's experience) of multi-threaded vs. internal
> pooling with the current process-based model. I recall looking at and
> playing with Konstantin's implementations of both, which were
> impressive. Yes, the latter doesn't solve the same issues, but many
> real-world ones where multi-threaded is argued. Personally, I think
> there would be not only a significant amount of time spent dealing with
> in-the-field stability regressions before a multi-threaded
> implementation matures, but it would also increase the learning curve
> for anyone trying to start with internals development.
To me, processes feel just a little easier to observe and inspect, a
little easier to debug, and a little easier to reason about. Tooling
does exist for threads - but operating systems track more things at a
process level and I like having the full arsenal of unix process-based
tooling at my disposal.
Even simple things, like being able to see at a glance from "ps" or
"top" output which process is the bgwriter or the checkpointer, and
being able to attach gdb only on that process without pausing the whole
system. Or to a single backend.
A thread model certainly has advantages but I do feel that some useful
things might be lost here.
And for the record, just within the past few weeks I saw a small mistake
in some C code which smashed the stack of another thread in the same
process space. It manifested as unpredictable periodic random SIGSEGV
and SIGBUS with core dumps that were useless gibberish, and it was
rather difficult to root cause.
But one interesting outcome of that incident was learning from my
colleague Josh that apparently SUSv2 and C99 contradict each other: when
snprintf() is called with size=0 then SUSv2 stipulates an unspecified
return value less than 1, while C99 allows str to be NULL in this case,
and gives the return value (as always) as the number of characters that
would have been written in case the output string has been large enough.
So long story short... I think the robustness angle on the process model
shouldn't be underestimated either.
-Jeremy
--
http://about.me/jeremy_schneider
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 21:07 ` Re: Let's make PostgreSQL multi-threaded Jonah H. Harris <[email protected]>
@ 2023-06-06 12:06 ` Konstantin Knizhnik <[email protected]>
2023-06-06 15:48 ` Re: Let's make PostgreSQL multi-threaded [email protected]
1 sibling, 1 reply; 43+ messages in thread
From: Konstantin Knizhnik @ 2023-06-06 12:06 UTC (permalink / raw)
To: [email protected]
On 06.06.2023 12:07 AM, Jonah H. Harris wrote:
> On Mon, Jun 5, 2023 at 8:18 AM Tom Lane <[email protected]> wrote:
>
> For the record, I think this will be a disaster. There is far too
> much
> code that will get broken, largely silently, and much of it is not
> under our control.
>
>
> While I've long been in favor of a multi-threaded implementation, now
> in my old age, I tend to agree with Tom. I'd be interested in
> Konstantin's thoughts (and PostgresPro's experience) of multi-threaded
> vs. internal pooling with the current process-based model. I recall
> looking at and playing with Konstantin's implementations of both,
> which were impressive. Yes, the latter doesn't solve the same issues,
> but many real-world ones where multi-threaded is argued. Personally, I
> think there would be not only a significant amount of time spent
> dealing with in-the-field stability regressions before a
> multi-threaded implementation matures, but it would also increase the
> learning curve for anyone trying to start with internals development.
>
> --
> Jonah H. Harris
>
Let me share my experience with porting Postgres to threads (by the way
- repository is still alive -
https://github.com/postgrespro/postgresql.pthreads
<https://github.com/postgrespro/postgresql.pthreads;
but I have not keep it in sync with recent versions of Postgres).
1. Solving the problem with static variables was not so difficult as I
expected - thanks to TLS and its support in modern compilers.
So the only thing we should do is to add some special modified to
variable declaration:
-static int MyLockNo = 0;
-static bool holdingAllLocks = false;
+static session_local int MyLockNo = 0;
+static session_local bool holdingAllLocks = false;
But there are about 2k such variables which storage class has to be changed.
This is one of the reasons why I do not agree with the proposal to
define some session context, place all session specific variables in
such context and pass it everywhere. It will be very inconvenient to
maintain structure with 2k fields and adding new field to this struct
each time you need some non-local variable. Even i it can be hide in
some macros like DEF_SESSION_VAR(type, name).
Also it requires changing of all Postgres code working with this
variables, not just declarations.
So patch will be 100x times more and almost any line of Postgres code
has to be changed.
And I do not see any reasons for it except portability and avoid
dependecy on compiler.
Implementation of TLS is quite efficient (at least at x86) - there is
special register pointing to TLS area, so access TLS variable is not
more expensive than static variable.
2. Performance improvement from switching to threads was not so large
(~10%). But please notice that I have not changed ny Postgres sync
primitives.
(But still not sure that using for example pthead_rwlock instead of our
own LWLock will cause some gains in performance)
3. Multithreading significantly simplify concurrent query execution and
interaction between workers.
Right now with dynamic shared memory stuff we can support work with
varying size data in shared memory but
in mutithreaded program it can be done much easier.
4. Multuthreaded model opens a way for fixing many existed Postgres
problems: lack of shared catalog and prepared statements cache, changing
page pool size (shared buffers) in runtime, ...
5. During this porting I had most of troubles with the following
components: GUCs, signals, handling errors and file descriptor cache.
File descriptor cache really becomes bottleneck because now all backends
and competing for file descriptors which number is usually limited by
1024 (without editing system configuration). Protecting it with mutex
cause significant degrade of performance. So I have to maintain
thread-local cache.
6. It is not clear how to support external extensions.
7. It will be hard to support non-multithreaded PL languages (like
python), but for example support of Java will be more natural and efficient.
I do not think that development of multithreaded application is more
complex or requires large "learning curve".
When you deal with parallel execution you should be careful in any case.
The advantage of process model is that there is much clear distinction
between shared and private variables.
Concerning debugging and profiling - it is more convenient with
multithreading in some cases and less convenient in other.
But programmers are living with threads for more than 30 years so now
most tools are supporting threads at least not worse than processes.
And for many developers now working with threads is more natural and
convenient.
OOM and local backend memory consumption seems to be one of the main
challenges for multithreadig model:
right now some queries can cause high consumption of memory. work_mem is
just a hint and real memory consumption can be much higher.
Even if it doesn't cause OOM, still not all of the allocated memory is
returned to OS after query completion and increase memory fragmentation.
Right now restart of single backend suffering from memory fragmentation
eliminates this problem. But if will be impossible for multhreaded Postgres.
So? as I see from this thread, most of authoritative members of Postgres
community are still very pessimistic (or conservative:)
about making Postgres multi-threaded. And it is really huge work which
will cause significant code discrepancy. It significantly complicates
backpatching and support of external extension. It can not be done
without support and approval by most of committers. This is why this
work was stalled in PgPro.
My personal opinion is that Postgres almost reaches its "limit of
evolution" or is close to it.
Making some major changes such as multithreading, undo log, columnar
store with vector executor
requires so much changes and cause so many conflicts with existed code
that it will be easier to develop new system from scratch
rather than trying to plugin new approach in old architecture. May be I
wrong. It can be my personal fault that I was not able to bring
multithread Postgres, builtin connection pooler, vectorized executor,
libpq compression and other my PRs to commit.
I have a filling that it is not possible to merge in mainstream
something non-trivial, affecting Postgres core without interest and help
of several
committers. Fro the other hand presence of such Postgres forks as
TimescaleDB, OrioleDB, GreenPlum demonstrates that Postgres still has
high potential for extension.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 21:07 ` Re: Let's make PostgreSQL multi-threaded Jonah H. Harris <[email protected]>
2023-06-06 12:06 ` Re: Let's make PostgreSQL multi-threaded Konstantin Knizhnik <[email protected]>
@ 2023-06-06 15:48 ` [email protected]
2023-06-06 16:24 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: [email protected] @ 2023-06-06 15:48 UTC (permalink / raw)
To: Konstantin Knizhnik <[email protected]>; +Cc: [email protected]
On 2023-06-06 08:06, Konstantin Knizhnik wrote:
> 7. It will be hard to support non-multithreaded PL languages (like
> python), but for example support of Java will be more natural and
> efficient.
To this I say ...
Hmm.
Surely, the current situation with a JVM in each backend process
(that calls for one) has been often seen as heavier than desirable.
At the same time, I am not sure how manageable one giant process
with one giant JVM instance would prove to be, either.
It is somewhat nice to be able to tweak JVM settings in a session
and see what happens, without disrupting other sessions. There may
also exist cases for different JVM settings in per-user or per-
database GUCs.
Like Python with the GIL, it is documented for JNI_CreateJavaVM
that "Creation of multiple VMs in a single process is not
supported."[1]
And the devs of Java, in their immeasurable wisdom, have announced
a "JDK Enhancement Proposal" (that's just what these things are
called, don't blame Orwell), JEP 411[2][3], in which all of the
Security Manager features that PL/Java relies on for bounds on
'trusted' behavior are deprecated for eventual removal with no
functional replacement. I'd be even more leery of using one big
shared JVM for everybody's work after that happens.
Might the work toward allowing a run-time choice between a
process or threaded model also make possible some
intermediate models as well? A backend process for
connections to a particular database, or with particular
authentication credentials? Go through the authentication
handshake and then sendfd the connected socket to the
appropriate process. (Has every supported platform got
something like sendfd?)
That way, there could be some flexibility to arrange how many
distinct backends (and, for Java purposes, how many JVMs) get
fired up, and have each sharing sessions that have something in
common.
Or, would that just require all the complexity of both
approaches to synchronization, with no sufficient benefit?
Regards,
-Chap
[1]
https://docs.oracle.com/en/java/javase/17/docs/specs/jni/invocation.html#jni_createjavavm
[2]
https://blogs.apache.org/netbeans/entry/jep-411-deprecate-the-security1
[3] https://github.com/tada/pljava/wiki/JEP-411
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 21:07 ` Re: Let's make PostgreSQL multi-threaded Jonah H. Harris <[email protected]>
2023-06-06 12:06 ` Re: Let's make PostgreSQL multi-threaded Konstantin Knizhnik <[email protected]>
2023-06-06 15:48 ` Re: Let's make PostgreSQL multi-threaded [email protected]
@ 2023-06-06 16:24 ` Heikki Linnakangas <[email protected]>
2023-06-06 17:00 ` Re: Let's make PostgreSQL multi-threaded [email protected]
0 siblings, 1 reply; 43+ messages in thread
From: Heikki Linnakangas @ 2023-06-06 16:24 UTC (permalink / raw)
To: [email protected]; Konstantin Knizhnik <[email protected]>; +Cc: [email protected]
On 06/06/2023 11:48, [email protected] wrote:
> And the devs of Java, in their immeasurable wisdom, have announced
> a "JDK Enhancement Proposal" (that's just what these things are
> called, don't blame Orwell), JEP 411[2][3], in which all of the
> Security Manager features that PL/Java relies on for bounds on
> 'trusted' behavior are deprecated for eventual removal with no
> functional replacement. I'd be even more leery of using one big
> shared JVM for everybody's work after that happens.
Ouch.
> Might the work toward allowing a run-time choice between a
> process or threaded model also make possible some
> intermediate models as well? A backend process for
> connections to a particular database, or with particular
> authentication credentials? Go through the authentication
> handshake and then sendfd the connected socket to the
> appropriate process. (Has every supported platform got
> something like sendfd?)
I'm afraid having multiple processes and JVMs doesn't help that. If you
can escape the one JVM in one backend process, it's game over. Backend
processes are not a security barrier, and you have the same problems
with the current multi-process architecture, too.
https://github.com/greenplum-db/plcontainer is one approach. It launches
a separate process for the PL, separate from the backend process, and
sandboxes that.
--
Heikki Linnakangas
Neon (https://neon.tech)
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 21:07 ` Re: Let's make PostgreSQL multi-threaded Jonah H. Harris <[email protected]>
2023-06-06 12:06 ` Re: Let's make PostgreSQL multi-threaded Konstantin Knizhnik <[email protected]>
2023-06-06 15:48 ` Re: Let's make PostgreSQL multi-threaded [email protected]
2023-06-06 16:24 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
@ 2023-06-06 17:00 ` [email protected]
0 siblings, 0 replies; 43+ messages in thread
From: [email protected] @ 2023-06-06 17:00 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Konstantin Knizhnik <[email protected]>; [email protected]
On 2023-06-06 12:24, Heikki Linnakangas wrote:
> I'm afraid having multiple processes and JVMs doesn't help that.
> If you can escape the one JVM in one backend process, it's game over.
So there's escape and there's escape, right? Java still prioritizes
(and has, in fact, strengthened) barriers against breaking module
encapsulation, or getting access to arbitrary native memory or code.
The features that have been deprecated, to eventually go away, are
the ones that offer fine-grained control over operations that there
are Java APIs for. Eventually it won't be as easy as it is now to say
"ok, your function gets to open these files or these sockets but
not those ones."
Even for those things, there may yet be solutions. There are Java
APIs for virtualizing the view of the file system, for example. It's
yet to be seen how things will shake out. Configuration may get
trickier, and there may be some incentive to to include, say,
sepgsql in the picture.
Sure, even access to a file API can be game over, depending on
what file you open, but that's already the risk for every PL
with an 'untrusted' flavor.
Regards,
-Chap
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
@ 2023-06-08 17:07 ` Hannu Krosing <[email protected]>
2023-06-08 19:59 ` Re: Let's make PostgreSQL multi-threaded Dave Cramer <[email protected]>
3 siblings, 1 reply; 43+ messages in thread
From: Hannu Krosing @ 2023-06-08 17:07 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Heikki Linnakangas <[email protected]>; pgsql-hackers
I discovered this thread from a Twitter post "PostgreSQL will finally
be rewritten in Rust" :)
On Mon, Jun 5, 2023 at 5:18 PM Tom Lane <[email protected]> wrote:
>
> Heikki Linnakangas <[email protected]> writes:
> > I spoke with some folks at PGCon about making PostgreSQL multi-threaded,
> > so that the whole server runs in a single process, with multiple
> > threads. It has been discussed many times in the past, last thread on
> > pgsql-hackers was back in 2017 when Konstantin made some experiments [0].
>
> > I feel that there is now pretty strong consensus that it would be a good
> > thing, more so than before. Lots of work to get there, and lots of
> > details to be hashed out, but no objections to the idea at a high level.
>
> > The purpose of this email is to make that silent consensus explicit. If
> > you have objections to switching from the current multi-process
> > architecture to a single-process, multi-threaded architecture, please
> > speak up.
>
> For the record, I think this will be a disaster. There is far too much
> code that will get broken, largely silently, and much of it is not
> under our control.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Let's make PostgreSQL multi-threaded
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-08 17:07 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
@ 2023-06-08 19:59 ` Dave Cramer <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Dave Cramer @ 2023-06-08 19:59 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Tom Lane <[email protected]>; Heikki Linnakangas <[email protected]>; pgsql-hackers
On Thu, 8 Jun 2023 at 13:08, Hannu Krosing <[email protected]> wrote:
> I discovered this thread from a Twitter post "PostgreSQL will finally
> be rewritten in Rust" :)
>
By the time we got around to finishing this, there would be a better
language to write it in.
Dave
^ permalink raw reply [nested|flat] 43+ messages in thread
end of thread, other threads:[~2023-08-25 12:01 UTC | newest]
Thread overview: 43+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-12 20:43 [PATCH 07/10] add wal_compression_method: zstd Justin Pryzby <[email protected]>
2023-06-05 15:18 Re: Let's make PostgreSQL multi-threaded Tom Lane <[email protected]>
2023-06-05 15:33 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-05 17:40 ` Re: Let's make PostgreSQL multi-threaded Jonathan S. Katz <[email protected]>
2023-06-07 21:37 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 09:54 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 10:15 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 16:00 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 19:34 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
2023-06-08 12:15 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 12:44 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 14:56 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
2023-06-08 15:02 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 15:56 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
2023-06-08 18:54 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 18:41 ` Re: Let's make PostgreSQL multi-threaded Andres Freund <[email protected]>
2023-06-08 15:08 ` Re: Let's make PostgreSQL multi-threaded Matthias van de Meent <[email protected]>
2023-06-08 16:05 ` Re: Let's make PostgreSQL multi-threaded Greg Sabino Mullane <[email protected]>
2023-06-08 14:08 ` Re: Let's make PostgreSQL multi-threaded Robert Haas <[email protected]>
2023-07-27 13:27 ` Re: Let's make PostgreSQL multi-threaded David Geier <[email protected]>
2023-08-11 12:05 ` Re: Let's make PostgreSQL multi-threaded Merlin Moncure <[email protected]>
2023-08-25 12:01 ` Re: Let's make PostgreSQL multi-threaded David Geier <[email protected]>
2023-06-07 19:20 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
2023-06-07 21:39 ` Re: Let's make PostgreSQL multi-threaded Thomas Kellerer <[email protected]>
2023-06-07 22:37 ` Re: Let's make PostgreSQL multi-threaded Jeremy Schneider <[email protected]>
2023-06-07 23:37 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
2023-06-08 10:37 ` Re: Let's make PostgreSQL multi-threaded Tomas Vondra <[email protected]>
2023-06-10 00:23 ` Re: Let's make PostgreSQL multi-threaded Bruce Momjian <[email protected]>
2023-06-08 05:55 ` Re: Let's make PostgreSQL multi-threaded Drouvot, Bertrand <[email protected]>
2023-06-08 09:56 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-05 18:51 ` Re: Let's make PostgreSQL multi-threaded Andrew Dunstan <[email protected]>
2023-06-05 20:08 ` Re: Let's make PostgreSQL multi-threaded Joe Conway <[email protected]>
2023-06-06 22:26 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
2023-06-08 17:02 ` Re: Let's make PostgreSQL multi-threaded Ilya Anfimov <[email protected]>
2023-06-08 19:10 ` Re: Let's make PostgreSQL multi-threaded Thomas Munro <[email protected]>
2023-06-05 21:07 ` Re: Let's make PostgreSQL multi-threaded Jonah H. Harris <[email protected]>
2023-06-06 00:27 ` Re: Let's make PostgreSQL multi-threaded Jeremy Schneider <[email protected]>
2023-06-06 12:06 ` Re: Let's make PostgreSQL multi-threaded Konstantin Knizhnik <[email protected]>
2023-06-06 15:48 ` Re: Let's make PostgreSQL multi-threaded [email protected]
2023-06-06 16:24 ` Re: Let's make PostgreSQL multi-threaded Heikki Linnakangas <[email protected]>
2023-06-06 17:00 ` Re: Let's make PostgreSQL multi-threaded [email protected]
2023-06-08 17:07 ` Re: Let's make PostgreSQL multi-threaded Hannu Krosing <[email protected]>
2023-06-08 19:59 ` Re: Let's make PostgreSQL multi-threaded Dave Cramer <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox