public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v4 2/2] Add deform_counter to pg_stat_statements 12+ messages / 5 participants [nested] [flat]
* [PATCH v4 2/2] Add deform_counter to pg_stat_statements @ 2022-06-11 10:25 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Dmitrii Dolgov @ 2022-06-11 10:25 UTC (permalink / raw) Similar to other JIT counters, expose deform_counter via pg_stat_statements. Includes bumping its version to 1.11. --- contrib/pg_stat_statements/Makefile | 1 + contrib/pg_stat_statements/meson.build | 1 + .../pg_stat_statements--1.10--1.11.sql | 69 +++++++++++++++++++ .../pg_stat_statements/pg_stat_statements.c | 34 ++++++++- .../pg_stat_statements.control | 2 +- doc/src/sgml/pgstatstatements.sgml | 19 +++++ 6 files changed, 123 insertions(+), 3 deletions(-) create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index edc40c8bbf..8d787970e7 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -7,6 +7,7 @@ OBJS = \ EXTENSION = pg_stat_statements DATA = pg_stat_statements--1.4.sql \ + pg_stat_statements--1.10--1.11.sql \ pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \ pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \ pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \ diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build index 7537e1cf64..e224cd764c 100644 --- a/contrib/pg_stat_statements/meson.build +++ b/contrib/pg_stat_statements/meson.build @@ -21,6 +21,7 @@ contrib_targets += pg_stat_statements install_data( 'pg_stat_statements.control', 'pg_stat_statements--1.4.sql', + 'pg_stat_statements--1.10--1.11.sql', 'pg_stat_statements--1.9--1.10.sql', 'pg_stat_statements--1.8--1.9.sql', 'pg_stat_statements--1.7--1.8.sql', diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql b/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql new file mode 100644 index 0000000000..20bae80458 --- /dev/null +++ b/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql @@ -0,0 +1,69 @@ +/* contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.11'" to load this file. \quit + +/* First we have to remove them from the extension */ +ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements; +ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean); + +/* Then we can drop them */ +DROP VIEW pg_stat_statements; +DROP FUNCTION pg_stat_statements(boolean); + +/* Now redefine */ +CREATE FUNCTION pg_stat_statements(IN showtext boolean, + OUT userid oid, + OUT dbid oid, + OUT toplevel bool, + OUT queryid bigint, + OUT query text, + OUT plans int8, + OUT total_plan_time float8, + OUT min_plan_time float8, + OUT max_plan_time float8, + OUT mean_plan_time float8, + OUT stddev_plan_time float8, + OUT calls int8, + OUT total_exec_time float8, + OUT min_exec_time float8, + OUT max_exec_time float8, + OUT mean_exec_time float8, + OUT stddev_exec_time float8, + OUT rows int8, + OUT shared_blks_hit int8, + OUT shared_blks_read int8, + OUT shared_blks_dirtied int8, + OUT shared_blks_written int8, + OUT local_blks_hit int8, + OUT local_blks_read int8, + OUT local_blks_dirtied int8, + OUT local_blks_written int8, + OUT temp_blks_read int8, + OUT temp_blks_written int8, + OUT blk_read_time float8, + OUT blk_write_time float8, + OUT temp_blk_read_time float8, + OUT temp_blk_write_time float8, + OUT wal_records int8, + OUT wal_fpi int8, + OUT wal_bytes numeric, + OUT jit_functions int8, + OUT jit_generation_time float8, + OUT jit_inlining_count int8, + OUT jit_inlining_time float8, + OUT jit_optimization_count int8, + OUT jit_optimization_time float8, + OUT jit_emission_count int8, + OUT jit_emission_time float8, + OUT jit_deform_count int8, + OUT jit_deform_time float8 +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_stat_statements_1_11' +LANGUAGE C STRICT VOLATILE PARALLEL SAFE; + +CREATE VIEW pg_stat_statements AS + SELECT * FROM pg_stat_statements(true); + +GRANT SELECT ON pg_stat_statements TO PUBLIC; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 8567cc0ca2..f2650b691b 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -118,7 +118,8 @@ typedef enum pgssVersion PGSS_V1_3, PGSS_V1_8, PGSS_V1_9, - PGSS_V1_10 + PGSS_V1_10, + PGSS_V1_11 } pgssVersion; typedef enum pgssStoreKind @@ -193,6 +194,9 @@ typedef struct Counters double jit_generation_time; /* total time to generate jit code */ int64 jit_inlining_count; /* number of times inlining time has been * > 0 */ + double jit_deform_time; /* total time to deform tuples in jit code */ + int64 jit_deform_count; /* number of times deform time has been > 0 */ + double jit_inlining_time; /* total time to inline jit code */ int64 jit_optimization_count; /* number of times optimization time * has been > 0 */ @@ -313,6 +317,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_1_3); PG_FUNCTION_INFO_V1(pg_stat_statements_1_8); PG_FUNCTION_INFO_V1(pg_stat_statements_1_9); PG_FUNCTION_INFO_V1(pg_stat_statements_1_10); +PG_FUNCTION_INFO_V1(pg_stat_statements_1_11); PG_FUNCTION_INFO_V1(pg_stat_statements); PG_FUNCTION_INFO_V1(pg_stat_statements_info); @@ -1400,6 +1405,10 @@ pgss_store(const char *query, uint64 queryId, e->counters.jit_functions += jitusage->created_functions; e->counters.jit_generation_time += INSTR_TIME_GET_MILLISEC(jitusage->generation_counter); + if (INSTR_TIME_GET_MILLISEC(jitusage->deform_counter)) + e->counters.jit_deform_count++; + e->counters.jit_deform_time += INSTR_TIME_GET_MILLISEC(jitusage->deform_counter); + if (INSTR_TIME_GET_MILLISEC(jitusage->inlining_counter)) e->counters.jit_inlining_count++; e->counters.jit_inlining_time += INSTR_TIME_GET_MILLISEC(jitusage->inlining_counter); @@ -1462,7 +1471,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) #define PG_STAT_STATEMENTS_COLS_V1_8 32 #define PG_STAT_STATEMENTS_COLS_V1_9 33 #define PG_STAT_STATEMENTS_COLS_V1_10 43 -#define PG_STAT_STATEMENTS_COLS 43 /* maximum of above */ +#define PG_STAT_STATEMENTS_COLS_V1_11 45 +#define PG_STAT_STATEMENTS_COLS 45 /* maximum of above */ /* * Retrieve statement statistics. @@ -1474,6 +1484,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) * expected API version is identified by embedding it in the C name of the * function. Unfortunately we weren't bright enough to do that for 1.1. */ +Datum +pg_stat_statements_1_11(PG_FUNCTION_ARGS) +{ + bool showtext = PG_GETARG_BOOL(0); + + pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext); + + return (Datum) 0; +} + Datum pg_stat_statements_1_10(PG_FUNCTION_ARGS) { @@ -1604,6 +1624,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, if (api_version != PGSS_V1_10) elog(ERROR, "incorrect number of output arguments"); break; + case PG_STAT_STATEMENTS_COLS_V1_11: + if (api_version != PGSS_V1_11) + elog(ERROR, "incorrect number of output arguments"); + break; default: elog(ERROR, "incorrect number of output arguments"); } @@ -1836,6 +1860,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, values[i++] = Int64GetDatumFast(tmp.jit_emission_count); values[i++] = Float8GetDatumFast(tmp.jit_emission_time); } + if (api_version >= PGSS_V1_11) + { + values[i++] = Int64GetDatumFast(tmp.jit_deform_count); + values[i++] = Float8GetDatumFast(tmp.jit_deform_time); + } Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 : api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 : @@ -1844,6 +1873,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, api_version == PGSS_V1_8 ? PG_STAT_STATEMENTS_COLS_V1_8 : api_version == PGSS_V1_9 ? PG_STAT_STATEMENTS_COLS_V1_9 : api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 : + api_version == PGSS_V1_11 ? PG_STAT_STATEMENTS_COLS_V1_11 : -1 /* fail if you forget to update this assert */ )); tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control index 0747e48138..8a76106ec6 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.control +++ b/contrib/pg_stat_statements/pg_stat_statements.control @@ -1,5 +1,5 @@ # pg_stat_statements extension comment = 'track planning and execution statistics of all SQL statements executed' -default_version = '1.10' +default_version = '1.11' module_pathname = '$libdir/pg_stat_statements' relocatable = true diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index ea90365c7f..62b29c48b5 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -420,6 +420,25 @@ </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_deform_count</structfield> <type>bigint</type> + </para> + <para> + Total number of tuple deform functions JIT-compiled by the statement + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>jit_deform_time</structfield> <type>double precision</type> + </para> + <para> + Total time spent by the statement on JIT-compiling deform tuple + functions, in milliseconds + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>jit_inlining_count</structfield> <type>bigint</type> -- 2.32.0 --v42rkzjsygxtrtfu-- ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-03 16:19 Tomas Vondra <[email protected]> 0 siblings, 3 replies; 12+ messages in thread From: Tomas Vondra @ 2024-09-03 16:19 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> On 9/3/24 17:06, Robert Haas wrote: > On Mon, Sep 2, 2024 at 1:46 PM Tomas Vondra <[email protected]> wrote: >> The one argument to not tie this to max_locks_per_transaction is the >> vastly different "per element" memory requirements. If you add one entry >> to max_locks_per_transaction, that adds LOCK which is a whopping 152B. >> OTOH one fast-path entry is ~5B, give or take. That's a pretty big >> difference, and it if the locks fit into the shared lock table, but >> you'd like to allow more fast-path locks, having to increase >> max_locks_per_transaction is not great - pretty wastefull. >> >> OTOH I'd really hate to just add another GUC and hope the users will >> magically know how to set it correctly. That's pretty unlikely, IMO. I >> myself wouldn't know what a good value is, I think. >> >> But say we add a GUC and set it to -1 by default, in which case it just >> inherits the max_locks_per_transaction value. And then also provide some >> basic metric about this fast-path cache, so that people can tune this? > > All things being equal, I would prefer not to add another GUC for > this, but we might need it. > Agreed. > Doing some worst case math, suppose somebody has max_connections=1000 > (which is near the upper limit of what I'd consider a sane setting) > and max_locks_per_transaction=10000 (ditto). The product is 10 > million, so every 10 bytes of storage each a gigabyte of RAM. Chewing > up 15GB of RAM when you could have chewed up only 0.5GB certainly > isn't too great. On the other hand, those values are kind of pushing > the limits of what is actually sane. If you imagine > max_locks_per_transaction=2000 rather than > max_locks_per_connection=10000, then it's only 3GB and that's > hopefully not a lot on the hopefully-giant machine where you're > running this. > Yeah, although I don't quite follow the math. With 1000/10000 settings, why would that eat 15GB of RAM? I mean, that's 1.5GB, right? FWIW the actual cost is somewhat higher, because we seem to need ~400B for every lock (not just the 150B for the LOCK struct). At least based on a quick experiment. (Seems a bit high, right?). Anyway, I agree this might be acceptable. If your transactions use this many locks regularly, you probably need this setting anyway. If you only need this many locks occasionally (so that you can keep the locks/xact value low), it probably does not matter that much. And if you're running massively-partitioned table on a tiny box, well, I don't really think that's a particularly sane idea. So I think I'm OK with just tying this to max_locks_per_transaction. >> I think just knowing the "hit ratio" would be enough, i.e. counters for >> how often it fits into the fast-path array, and how often we had to >> promote it to the shared lock table would be enough, no? > > Yeah, probably. I mean, that won't tell you how big it needs to be, > but it will tell you whether it's big enough. > True, but that applies to all "cache hit ratio" metrics (like for our shared buffers). It'd be great to have something better, enough to tell you how large the cache needs to be. But we don't :-( > I wonder if we should be looking at further improvements in the lock > manager of some kind. For instance, imagine if we allocated storage > via DSM or DSA for cases where we need a really large number of Lock > entries. The downside of that is that we might run out of memory for > locks at runtime, which would perhaps suck, but you'd probably use > significantly less memory on average. Or, maybe we need an even bigger > rethink where we reconsider the idea that we take a separate lock for > every single partition instead of having some kind of hierarchy-aware > lock manager. I don't know. But this feels like very old, crufty tech. > There's probably something more state of the art that we could or > should be doing. > Perhaps. I agree we'll probably need something more radical soon, not just changes that aim to fix some rare exceptional case (which may be annoying, but not particularly harmful for the complete workload). For example, if we did what you propose, that might help when very few transactions need a lot of locks. I don't mind saving memory in that case, ofc. but is it a problem if those rare cases are a bit slower? Shouldn't we focus more on cases where many locks are common? Because people are simply going to use partitioning, a lot of indexes, etc? So yeah, I agree we probably need a more fundamental rethink. I don't think we can just keep optimizing the current approach, there's a limit of fast it can be. Whether it's not locking individual partitions, or not locking some indexes, ... I don't know. regards -- Tomas Vondra ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-04 09:29 Jakub Wartak <[email protected]> parent: Tomas Vondra <[email protected]> 2 siblings, 1 reply; 12+ messages in thread From: Jakub Wartak @ 2024-09-04 09:29 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> Hi Tomas! On Tue, Sep 3, 2024 at 6:20 PM Tomas Vondra <[email protected]> wrote: > > On 9/3/24 17:06, Robert Haas wrote: > > On Mon, Sep 2, 2024 at 1:46 PM Tomas Vondra <[email protected]> wrote: > >> The one argument to not tie this to max_locks_per_transaction is the > >> vastly different "per element" memory requirements. If you add one entry > >> to max_locks_per_transaction, that adds LOCK which is a whopping 152B. > >> OTOH one fast-path entry is ~5B, give or take. That's a pretty big > >> difference, and it if the locks fit into the shared lock table, but > >> you'd like to allow more fast-path locks, having to increase > >> max_locks_per_transaction is not great - pretty wastefull. > >> > >> OTOH I'd really hate to just add another GUC and hope the users will > >> magically know how to set it correctly. That's pretty unlikely, IMO. I > >> myself wouldn't know what a good value is, I think. > >> > >> But say we add a GUC and set it to -1 by default, in which case it just > >> inherits the max_locks_per_transaction value. And then also provide some > >> basic metric about this fast-path cache, so that people can tune this? > > > > All things being equal, I would prefer not to add another GUC for > > this, but we might need it. > > > > Agreed. > > [..] > > So I think I'm OK with just tying this to max_locks_per_transaction. If that matters then the SLRU configurability effort added 7 GUCs (with 3 scaling up based on shared_buffers) just to give high-end users some relief, so here 1 new shouldn't be that such a deal. We could add to the LWLock/lock_manager wait event docs to recommend just using known-to-be-good certain values from this $thread (or ask the user to benchmark it himself). > >> I think just knowing the "hit ratio" would be enough, i.e. counters for > >> how often it fits into the fast-path array, and how often we had to > >> promote it to the shared lock table would be enough, no? > > > > Yeah, probably. I mean, that won't tell you how big it needs to be, > > but it will tell you whether it's big enough. > > > > True, but that applies to all "cache hit ratio" metrics (like for our > shared buffers). It'd be great to have something better, enough to tell > you how large the cache needs to be. But we don't :-( My $0.02 cents: the originating case that triggered those patches, actually started with LWLock/lock_manager waits being the top#1. The operator can cross check (join) that with a group by pg_locks.fastpath (='f'), count(*). So, IMHO we have good observability in this case (rare thing to say!) > > I wonder if we should be looking at further improvements in the lock > > manager of some kind. [..] > > Perhaps. I agree we'll probably need something more radical soon, not > just changes that aim to fix some rare exceptional case (which may be > annoying, but not particularly harmful for the complete workload). > > For example, if we did what you propose, that might help when very few > transactions need a lot of locks. I don't mind saving memory in that > case, ofc. but is it a problem if those rare cases are a bit slower? > Shouldn't we focus more on cases where many locks are common? Because > people are simply going to use partitioning, a lot of indexes, etc? > > So yeah, I agree we probably need a more fundamental rethink. I don't > think we can just keep optimizing the current approach, there's a limit > of fast it can be. Please help me understand: so are You both discussing potential far future further improvements instead of this one ? My question is really about: is the patchset good enough or are you considering some other new effort instead? BTW some other random questions: Q1. I've been lurking into https://github.com/tvondra/pg-lock-scalability-results and those shouldn't be used anymore for further discussions, as they contained earlier patches (including 0003-Add-a-memory-pool-with-adaptive-rebalancing.patch) and they were replaced by benchmark data in this $thread, right? Q2. Earlier attempts did contain a mempool patch to get those nice numbers (or was that jemalloc or glibc tuning). So were those recent results in [1] collected with still 0003 or you have switched completely to glibc/jemalloc tuning? -J. [1] - https://www.postgresql.org/message-id/b8c43eda-0c3f-4cb4-809b-841fa5c40ada%40vondra.me ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-04 11:15 Tomas Vondra <[email protected]> parent: Jakub Wartak <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Tomas Vondra @ 2024-09-04 11:15 UTC (permalink / raw) To: Jakub Wartak <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> On 9/4/24 11:29, Jakub Wartak wrote: > Hi Tomas! > > On Tue, Sep 3, 2024 at 6:20 PM Tomas Vondra <[email protected]> wrote: >> >> On 9/3/24 17:06, Robert Haas wrote: >>> On Mon, Sep 2, 2024 at 1:46 PM Tomas Vondra <[email protected]> wrote: >>>> The one argument to not tie this to max_locks_per_transaction is the >>>> vastly different "per element" memory requirements. If you add one entry >>>> to max_locks_per_transaction, that adds LOCK which is a whopping 152B. >>>> OTOH one fast-path entry is ~5B, give or take. That's a pretty big >>>> difference, and it if the locks fit into the shared lock table, but >>>> you'd like to allow more fast-path locks, having to increase >>>> max_locks_per_transaction is not great - pretty wastefull. >>>> >>>> OTOH I'd really hate to just add another GUC and hope the users will >>>> magically know how to set it correctly. That's pretty unlikely, IMO. I >>>> myself wouldn't know what a good value is, I think. >>>> >>>> But say we add a GUC and set it to -1 by default, in which case it just >>>> inherits the max_locks_per_transaction value. And then also provide some >>>> basic metric about this fast-path cache, so that people can tune this? >>> >>> All things being equal, I would prefer not to add another GUC for >>> this, but we might need it. >>> >> >> Agreed. >> >> [..] >> >> So I think I'm OK with just tying this to max_locks_per_transaction. > > If that matters then the SLRU configurability effort added 7 GUCs > (with 3 scaling up based on shared_buffers) just to give high-end > users some relief, so here 1 new shouldn't be that such a deal. We > could add to the LWLock/lock_manager wait event docs to recommend just > using known-to-be-good certain values from this $thread (or ask the > user to benchmark it himself). > TBH I'm skeptical we'll be able to tune those GUCs. Maybe it was the right thing for the SLRU thread, I don't know - I haven't been following that very closely. But my impression is that we often add a GUC when we're not quite sure how to pick a good value. So we just shift the responsibility to someone else, who however also doesn't know. I'd very much prefer not to do that here. Of course, it's challenging because we can't easily resize these arrays, so even if we had some nice heuristics to calculate the "optimal" number of fast-path slots, what would we do with it ... >>>> I think just knowing the "hit ratio" would be enough, i.e. counters for >>>> how often it fits into the fast-path array, and how often we had to >>>> promote it to the shared lock table would be enough, no? >>> >>> Yeah, probably. I mean, that won't tell you how big it needs to be, >>> but it will tell you whether it's big enough. >>> >> >> True, but that applies to all "cache hit ratio" metrics (like for our >> shared buffers). It'd be great to have something better, enough to tell >> you how large the cache needs to be. But we don't :-( > > My $0.02 cents: the originating case that triggered those patches, > actually started with LWLock/lock_manager waits being the top#1. The > operator can cross check (join) that with a group by pg_locks.fastpath > (='f'), count(*). So, IMHO we have good observability in this case > (rare thing to say!) > That's a good point. So if you had to give some instructions to users what to measure / monitor, and how to adjust the GUC based on that, what would your instructions be? >>> I wonder if we should be looking at further improvements in the lock >>> manager of some kind. [..] >> >> Perhaps. I agree we'll probably need something more radical soon, not >> just changes that aim to fix some rare exceptional case (which may be >> annoying, but not particularly harmful for the complete workload). >> >> For example, if we did what you propose, that might help when very few >> transactions need a lot of locks. I don't mind saving memory in that >> case, ofc. but is it a problem if those rare cases are a bit slower? >> Shouldn't we focus more on cases where many locks are common? Because >> people are simply going to use partitioning, a lot of indexes, etc? >> >> So yeah, I agree we probably need a more fundamental rethink. I don't >> think we can just keep optimizing the current approach, there's a limit >> of fast it can be. > > Please help me understand: so are You both discussing potential far > future further improvements instead of this one ? My question is > really about: is the patchset good enough or are you considering some > other new effort instead? > I think it was mostly a brainstorming about alternative / additional improvements in locking. The proposed patch does not change the locking in any fundamental way, it merely optimizes one piece - we still acquire exactly the same set of locks, exactly the same way. AFAICS there's an agreement the current approach has limits, and with the growing number of partitions we're hitting them already. That may need rethinking the fundamental approach, but I think that should not block improvements to the current approach. Not to mention there's no proposal for such "fundamental rework" yet. > BTW some other random questions: > Q1. I've been lurking into > https://github.com/tvondra/pg-lock-scalability-results and those > shouldn't be used anymore for further discussions, as they contained > earlier patches (including > 0003-Add-a-memory-pool-with-adaptive-rebalancing.patch) and they were > replaced by benchmark data in this $thread, right? The github results are still valid, I've only shared them 3 days ago. It does test both the mempool and glibc tuning, to assess (and compare) the benefits of that, but why would that make it obsolete? By "results in this thread" I suppose you mean the couple numbers I shared on September 2? Those were just very limited benchmarks to asses if making the arrays variable-length (based on GUC) would make things slower. And it doesn't, so the "full" github results still apply. > Q2. Earlier attempts did contain a mempool patch to get those nice > numbers (or was that jemalloc or glibc tuning). So were those recent > results in [1] collected with still 0003 or you have switched > completely to glibc/jemalloc tuning? > The results pushed to github are all with glibc, and test four cases: a) mempool patch not applied, no glibc tuning b) mempool patch applied, no glibc tuning c) mempool patch not applied, glibc tuning d) mempool patch applied, glibc tuning These are the 4 "column groups" in some of the pivot tables, to allow comparing those cases. My interpretation of the results are 1) The mempool / glibc tuning have significant benefits, at least for some workloads (where the locking patch alone does help much). 2) There's very little difference between the mempool / glibc tuning. The mempool does seem to have a small advantage. 3) The mempool / glibc tuning is irrelevant for non-glibc systems (e.g. for FreeBSD which I think uses jemalloc or something like that). I think the mempool might be interesting and useful for other reasons (e.g. I initially wrote it to enforce a per-backend memory limit), but you can get mostly the same caching benefits by tuning the glibc parameters. So I'm focusing on the locking stuff. regards -- Tomas Vondra ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-04 14:25 Matthias van de Meent <[email protected]> parent: Tomas Vondra <[email protected]> 2 siblings, 1 reply; 12+ messages in thread From: Matthias van de Meent @ 2024-09-04 14:25 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> On Tue, 3 Sept 2024 at 18:20, Tomas Vondra <[email protected]> wrote: > FWIW the actual cost is somewhat higher, because we seem to need ~400B > for every lock (not just the 150B for the LOCK struct). We do indeed allocate two PROCLOCKs for every LOCK, and allocate those inside dynahash tables. That amounts to (152+2*64+3*16=) 328 bytes in dynahash elements, and (3 * 8-16) = 24-48 bytes for the dynahash buckets/segments, resulting in 352-376 bytes * NLOCKENTS() being used[^1]. Does that align with your usage numbers, or are they significantly larger? > At least based on a quick experiment. (Seems a bit high, right?). Yeah, that does seem high, thanks for nerd-sniping me. The 152 bytes of LOCK are mostly due to a combination of two MAX_LOCKMODES-sized int[]s that are used to keep track of the number of requested/granted locks of each level. As MAX_LOCKMODES = 10, these arrays use a total of 2*4*10=80 bytes, with the remaining 72 spent on tracking. MAX_BACKENDS sadly doesn't fit in int16, so we'll have to keep using int[]s, but that doesn't mean we can't improve this size: ISTM that MAX_LOCKMODES is 2 larger than it has to be: LOCKMODE=0 is NoLock, which is never used or counted in these shared structures, and the max lock mode supported by any of the supported lock methods is AccessExclusiveLock (8). We can thus reduce MAX_LOCKMODES to 8, reducing size of the LOCK struct by 16 bytes. If some struct- and field packing is OK, then we could further reduce the size of LOCK by an additional 8 bytes by resizing the LOCKMASK type from int to int16 (we only use the first MaxLockMode (8) + 1 bits), and then storing the grant/waitMask fields (now 4 bytes total) in the padding that's present at the end of the waitProcs struct. This would depend on dclist not writing in its padding space, but I couldn't find any user that did so, and most critically dclist_init doesn't scribble in the padding with memset. If these are both implemented, it would save 24 bytes, reducing the struct to 128 bytes. :) [^2] I also checked PROCLOCK: If it is worth further packing the struct, we should probably look at whether it's worth replacing the PGPROC* typed fields with ProcNumber -based ones, potentially in both PROCLOCK and PROCLOCKTAG. When combined with int16-typed LOCKMASKs, either one of these fields being replaced with ProcNumber would allow a reduction in size by one MAXALIGN quantum, reducing the struct to 56 bytes, the smallest I could get it to without ignoring type alignments. Further shmem savings can be achieved by reducing the "10% safety margin" added at the end of LockShmemSize, as I'm fairly sure the memory used in shared hashmaps doesn't exceed the estimated amount, and if it did then we should probably fix that part, rather than requesting that (up to) 10% overhead here. Alltogether that'd save 40 bytes/lock entry on size, and ~35 bytes/lock on "safety margin", for a saving of (up to) 19% of our current allocation. I'm not sure if these tricks would benefit with performance or even be a demerit, apart from smaller structs usually being better at fitting better in CPU caches. Kind regards, Matthias van de Meent Neon (https://neon.tech) [^1] NLOCKENTS() benefits from being a power of 2, or slightly below one, as it's rounded up to a power of 2 when dynahash decides its number of buckets to allocate. [^2] Sadly this 2-cachelines alignment is lost due to dynahash's HASHELEMENT prefix of elements. :( ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-04 15:32 Tomas Vondra <[email protected]> parent: Matthias van de Meent <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Tomas Vondra @ 2024-09-04 15:32 UTC (permalink / raw) To: Matthias van de Meent <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> On 9/4/24 16:25, Matthias van de Meent wrote: > On Tue, 3 Sept 2024 at 18:20, Tomas Vondra <[email protected]> wrote: >> FWIW the actual cost is somewhat higher, because we seem to need ~400B >> for every lock (not just the 150B for the LOCK struct). > > We do indeed allocate two PROCLOCKs for every LOCK, and allocate those > inside dynahash tables. That amounts to (152+2*64+3*16=) 328 bytes in > dynahash elements, and (3 * 8-16) = 24-48 bytes for the dynahash > buckets/segments, resulting in 352-376 bytes * NLOCKENTS() being > used[^1]. Does that align with your usage numbers, or are they > significantly larger? > I see more like ~470B per lock. If I patch CalculateShmemSize to log the shmem allocated, I get this: max_connections=100 max_locks_per_transaction=1000 => 194264001 max_connections=100 max_locks_per_transaction=2000 => 241756967 and (((241756967-194264001)/100/1000)) = 474 Could be alignment of structs or something, not sure. >> At least based on a quick experiment. (Seems a bit high, right?). > > Yeah, that does seem high, thanks for nerd-sniping me. > > The 152 bytes of LOCK are mostly due to a combination of two > MAX_LOCKMODES-sized int[]s that are used to keep track of the number > of requested/granted locks of each level. As MAX_LOCKMODES = 10, these > arrays use a total of 2*4*10=80 bytes, with the remaining 72 spent on > tracking. MAX_BACKENDS sadly doesn't fit in int16, so we'll have to > keep using int[]s, but that doesn't mean we can't improve this size: > > ISTM that MAX_LOCKMODES is 2 larger than it has to be: LOCKMODE=0 is > NoLock, which is never used or counted in these shared structures, and > the max lock mode supported by any of the supported lock methods is > AccessExclusiveLock (8). We can thus reduce MAX_LOCKMODES to 8, > reducing size of the LOCK struct by 16 bytes. > > If some struct- and field packing is OK, then we could further reduce > the size of LOCK by an additional 8 bytes by resizing the LOCKMASK > type from int to int16 (we only use the first MaxLockMode (8) + 1 > bits), and then storing the grant/waitMask fields (now 4 bytes total) > in the padding that's present at the end of the waitProcs struct. This > would depend on dclist not writing in its padding space, but I > couldn't find any user that did so, and most critically dclist_init > doesn't scribble in the padding with memset. > > If these are both implemented, it would save 24 bytes, reducing the > struct to 128 bytes. :) [^2] > > I also checked PROCLOCK: If it is worth further packing the struct, we > should probably look at whether it's worth replacing the PGPROC* typed > fields with ProcNumber -based ones, potentially in both PROCLOCK and > PROCLOCKTAG. When combined with int16-typed LOCKMASKs, either one of > these fields being replaced with ProcNumber would allow a reduction in > size by one MAXALIGN quantum, reducing the struct to 56 bytes, the > smallest I could get it to without ignoring type alignments. > > Further shmem savings can be achieved by reducing the "10% safety > margin" added at the end of LockShmemSize, as I'm fairly sure the > memory used in shared hashmaps doesn't exceed the estimated amount, > and if it did then we should probably fix that part, rather than > requesting that (up to) 10% overhead here. > > Alltogether that'd save 40 bytes/lock entry on size, and ~35 > bytes/lock on "safety margin", for a saving of (up to) 19% of our > current allocation. I'm not sure if these tricks would benefit with > performance or even be a demerit, apart from smaller structs usually > being better at fitting better in CPU caches. > Not sure either, but it seems worth exploring. If you do an experimental patch for the LOCK size reduction, I can get some numbers. I'm not sure about the safety margins. 10% sure seems like quite a bit of memory (it might not have in the past, but as the instances are growing, that probably changed). regards -- Tomas Vondra ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-05 16:25 Robert Haas <[email protected]> parent: Tomas Vondra <[email protected]> 2 siblings, 1 reply; 12+ messages in thread From: Robert Haas @ 2024-09-05 16:25 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> On Tue, Sep 3, 2024 at 12:19 PM Tomas Vondra <[email protected]> wrote: > > Doing some worst case math, suppose somebody has max_connections=1000 > > (which is near the upper limit of what I'd consider a sane setting) > > and max_locks_per_transaction=10000 (ditto). The product is 10 > > million, so every 10 bytes of storage each a gigabyte of RAM. Chewing > > up 15GB of RAM when you could have chewed up only 0.5GB certainly > > isn't too great. On the other hand, those values are kind of pushing > > the limits of what is actually sane. If you imagine > > max_locks_per_transaction=2000 rather than > > max_locks_per_connection=10000, then it's only 3GB and that's > > hopefully not a lot on the hopefully-giant machine where you're > > running this. > > Yeah, although I don't quite follow the math. With 1000/10000 settings, > why would that eat 15GB of RAM? I mean, that's 1.5GB, right? Oh, right. > FWIW the actual cost is somewhat higher, because we seem to need ~400B > for every lock (not just the 150B for the LOCK struct). At least based > on a quick experiment. (Seems a bit high, right?). Hmm, yes, that's unpleasant. > Perhaps. I agree we'll probably need something more radical soon, not > just changes that aim to fix some rare exceptional case (which may be > annoying, but not particularly harmful for the complete workload). > > For example, if we did what you propose, that might help when very few > transactions need a lot of locks. I don't mind saving memory in that > case, ofc. but is it a problem if those rare cases are a bit slower? > Shouldn't we focus more on cases where many locks are common? Because > people are simply going to use partitioning, a lot of indexes, etc? > > So yeah, I agree we probably need a more fundamental rethink. I don't > think we can just keep optimizing the current approach, there's a limit > of fast it can be. Whether it's not locking individual partitions, or > not locking some indexes, ... I don't know. I don't know, either. We don't have to decide right now; it's just something to keep in mind. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-05 17:21 Tomas Vondra <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Tomas Vondra @ 2024-09-05 17:21 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> Hi, Here's a bit more polished version of this patch series. I only propose 0001 and 0002 for eventual commit, the two other bits are just stuff to help with benchmarking etc. 0001 ---- increases the size of the arrays, but uses hard-coded number of groups (64, so 1024 locks) and leaves everything in PGPROC 0002 ---- Allocates that separately from PGPROC, and sets the number based on max_locks_per_transactions I think 0001 and 0002 should be in fairly good shape, IMO. There's a couple cosmetic things that bother me (e.g. the way it Asserts after each FAST_PATH_LOCK_REL_GROUP seems distracting). But other than that I think it's fine, so a review / opinions would be very welcome. 0003 ---- Adds a separate GUC to make benchmarking easier (without the impact of changing the size of the lock table). I think the agreement is to not have a new GUC, unless it turns out to be necessary in the future. So 0003 was just to make benchmarking a bit easier. 0004 ---- This was a quick attempt to track the fraction of fast-path locks, and adding the infrastructure is mostly mechanical thing. But it turns out it's not quite trivial to track why a lock did not use fast-path. It might have been because it wouldn't fit, or maybe it's not eligible, or maybe there's a stronger lock. It's not obvious how to count these to help with evaluating the number of fast-path slots. regards -- Tomas Vondra Attachments: [text/x-patch] v20240905-0001-Increase-the-number-of-fast-path-lock-slot.patch (14.3K, ../../[email protected]/2-v20240905-0001-Increase-the-number-of-fast-path-lock-slot.patch) download | inline diff: From 6877dfa7cd94c9f541689d9fe211bdcfaf8bbbdc Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Mon, 2 Sep 2024 00:55:13 +0200 Subject: [PATCH v20240905 1/4] Increase the number of fast-path lock slots The fast-path locking introduced in 9.2 allowed each backend to obtain up to 16 relation locks, provided the lock is not exclusive etc. If the backend needs to obtain more locks, it needs to put them into the lock table in shared memory, which is considerably more expensive. The limit of 16 entries was always rather low. We need to lock all relations - not just tables, but also indexes. And for planning we need to lock all relations that might be used by a query, not just those in the final plan. So it was common to use all the fast-path slots even with simple schemas and queries. But as partitioning gets more widely used, with an ever increasing number of partitions, this bottleneck is becoming easier to hit. Especially on large machines with enough memory to keep the queried data cached, and many cores to cause contention when accessing the shared lock table. This patch addresses that by increasing the number of fast-path slots from 16 to 1024, structuring it as a 16-way set associative cache. The cache is divided into groups of 16 slots, and each lock is mapped to exactly one of those groups (by hashing the OID). Entries in each group are processed by linear search etc. We could treat the whole array as a single hash table, but that would degrade as it gets full (the cache is in shared memory, so we can't resize it easily to keep the load factor low). It would probably also have worse locality, due to more random access. If a group is full, we can simply insert the new lock into the shared lock table. This is the same as for the original code with 16 slots. Of course, if this happens too often, that reduces the benefit. To map relids to groups we use trivial hash function of the form h(relid) = ((relid * P) mod N) where P is a hard-coded prime number, and N is the number of groups. This is fast and works quite well - the main purpose is to map relids to different groups, so that we don't get "hot groups" while the rest of the groups are almost empty. If the relids are already spread out, the hash function is unlikely to group them. If the relids are sequential (e.g. for tables created by a script), the multiplication will spread them around. Note: This hard-codes the number of groups to 64, which means 1024 fast-path locks. This shall be either configurable or even better adjusted based on some existing GUC. --- src/backend/storage/lmgr/lock.c | 148 +++++++++++++++++++++++++++----- src/include/storage/proc.h | 8 +- 2 files changed, 132 insertions(+), 24 deletions(-) diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index 83b99a98f08..f41e4a33f06 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -167,7 +167,7 @@ typedef struct TwoPhaseLockRecord * our locks to the primary lock table, but it can never be lower than the * real value, since only we can acquire locks on our own behalf. */ -static int FastPathLocalUseCount = 0; +static int FastPathLocalUseCounts[FP_LOCK_GROUPS_PER_BACKEND]; /* * Flag to indicate if the relation extension lock is held by this backend. @@ -184,23 +184,56 @@ static int FastPathLocalUseCount = 0; */ static bool IsRelationExtensionLockHeld PG_USED_FOR_ASSERTS_ONLY = false; +/* + * Macros to calculate the group and index for a relation. + * + * The formula is a simple hash function, designed to spread the OIDs a bit, + * so that even contiguous values end up in different groups. In most cases + * there will be gaps anyway, but the multiplication should help a bit. + * + * The selected value (49157) is a prime not too close to 2^k, and it's + * small enough to not cause overflows (in 64-bit). + * + * XXX Maybe it'd be easier / cheaper to just do this in 32-bits? If we + * did (rel % 100000) or something like that first, that'd be enough to + * not wrap around. But even if it wrapped, would that be a problem? + */ +#define FAST_PATH_LOCK_REL_GROUP(rel) (((uint64) (rel) * 49157) % FP_LOCK_GROUPS_PER_BACKEND) + +/* + * Given a lock index (into the per-backend array), calculated using the + * FP_LOCK_SLOT_INDEX macro, calculate group and index (within the group). + */ +#define FAST_PATH_LOCK_GROUP(index) \ + (AssertMacro(((index) >= 0) && ((index) < FP_LOCK_SLOTS_PER_BACKEND)), \ + ((index) / FP_LOCK_SLOTS_PER_GROUP)) +#define FAST_PATH_LOCK_INDEX(index) \ + (AssertMacro(((index) >= 0) && ((index) < FP_LOCK_SLOTS_PER_BACKEND)), \ + ((index) % FP_LOCK_SLOTS_PER_GROUP)) + +/* Calculate index in the whole per-backend array of lock slots. */ +#define FP_LOCK_SLOT_INDEX(group, index) \ + (AssertMacro(((group) >= 0) && ((group) < FP_LOCK_GROUPS_PER_BACKEND)), \ + AssertMacro(((index) >= 0) && ((index) < FP_LOCK_SLOTS_PER_GROUP)), \ + ((group) * FP_LOCK_SLOTS_PER_GROUP + (index))) + /* Macros for manipulating proc->fpLockBits */ #define FAST_PATH_BITS_PER_SLOT 3 #define FAST_PATH_LOCKNUMBER_OFFSET 1 #define FAST_PATH_MASK ((1 << FAST_PATH_BITS_PER_SLOT) - 1) #define FAST_PATH_GET_BITS(proc, n) \ - (((proc)->fpLockBits >> (FAST_PATH_BITS_PER_SLOT * n)) & FAST_PATH_MASK) + (((proc)->fpLockBits[(n)/16] >> (FAST_PATH_BITS_PER_SLOT * FAST_PATH_LOCK_INDEX(n))) & FAST_PATH_MASK) #define FAST_PATH_BIT_POSITION(n, l) \ (AssertMacro((l) >= FAST_PATH_LOCKNUMBER_OFFSET), \ AssertMacro((l) < FAST_PATH_BITS_PER_SLOT+FAST_PATH_LOCKNUMBER_OFFSET), \ AssertMacro((n) < FP_LOCK_SLOTS_PER_BACKEND), \ - ((l) - FAST_PATH_LOCKNUMBER_OFFSET + FAST_PATH_BITS_PER_SLOT * (n))) + ((l) - FAST_PATH_LOCKNUMBER_OFFSET + FAST_PATH_BITS_PER_SLOT * (FAST_PATH_LOCK_INDEX(n)))) #define FAST_PATH_SET_LOCKMODE(proc, n, l) \ - (proc)->fpLockBits |= UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l) + (proc)->fpLockBits[FAST_PATH_LOCK_GROUP(n)] |= UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l) #define FAST_PATH_CLEAR_LOCKMODE(proc, n, l) \ - (proc)->fpLockBits &= ~(UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l)) + (proc)->fpLockBits[FAST_PATH_LOCK_GROUP(n)] &= ~(UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l)) #define FAST_PATH_CHECK_LOCKMODE(proc, n, l) \ - ((proc)->fpLockBits & (UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l))) + ((proc)->fpLockBits[FAST_PATH_LOCK_GROUP(n)] & (UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l))) /* * The fast-path lock mechanism is concerned only with relation locks on @@ -926,7 +959,7 @@ LockAcquireExtended(const LOCKTAG *locktag, * for now we don't worry about that case either. */ if (EligibleForRelationFastPath(locktag, lockmode) && - FastPathLocalUseCount < FP_LOCK_SLOTS_PER_BACKEND) + FastPathLocalUseCounts[FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2)] < FP_LOCK_SLOTS_PER_GROUP) { uint32 fasthashcode = FastPathStrongLockHashPartition(hashcode); bool acquired; @@ -1970,6 +2003,7 @@ LockRelease(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock) PROCLOCK *proclock; LWLock *partitionLock; bool wakeupNeeded; + int group; if (lockmethodid <= 0 || lockmethodid >= lengthof(LockMethods)) elog(ERROR, "unrecognized lock method: %d", lockmethodid); @@ -2063,9 +2097,14 @@ LockRelease(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock) */ locallock->lockCleared = false; + /* Which FP group does the lock belong to? */ + group = FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2); + + Assert(group >= 0 && group < FP_LOCK_GROUPS_PER_BACKEND); + /* Attempt fast release of any lock eligible for the fast path. */ if (EligibleForRelationFastPath(locktag, lockmode) && - FastPathLocalUseCount > 0) + FastPathLocalUseCounts[group] > 0) { bool released; @@ -2633,12 +2672,26 @@ LockReassignOwner(LOCALLOCK *locallock, ResourceOwner parent) static bool FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode) { - uint32 f; uint32 unused_slot = FP_LOCK_SLOTS_PER_BACKEND; + uint32 i, + group; + + /* Which FP group does the lock belong to? */ + group = FAST_PATH_LOCK_REL_GROUP(relid); + + Assert(group < FP_LOCK_GROUPS_PER_BACKEND); /* Scan for existing entry for this relid, remembering empty slot. */ - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + for (i = 0; i < FP_LOCK_SLOTS_PER_GROUP; i++) { + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, i); + + /* must not overflow the array of all locks for a backend */ + Assert(f < FP_LOCK_SLOTS_PER_BACKEND); + if (FAST_PATH_GET_BITS(MyProc, f) == 0) unused_slot = f; else if (MyProc->fpRelId[f] == relid) @@ -2654,7 +2707,7 @@ FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode) { MyProc->fpRelId[unused_slot] = relid; FAST_PATH_SET_LOCKMODE(MyProc, unused_slot, lockmode); - ++FastPathLocalUseCount; + ++FastPathLocalUseCounts[group]; return true; } @@ -2670,12 +2723,26 @@ FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode) static bool FastPathUnGrantRelationLock(Oid relid, LOCKMODE lockmode) { - uint32 f; bool result = false; + uint32 i, + group; + + /* Which FP group does the lock belong to? */ + group = FAST_PATH_LOCK_REL_GROUP(relid); - FastPathLocalUseCount = 0; - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + Assert(group < FP_LOCK_GROUPS_PER_BACKEND); + + FastPathLocalUseCounts[group] = 0; + for (i = 0; i < FP_LOCK_SLOTS_PER_GROUP; i++) { + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, i); + + /* must not overflow the array of all locks for a backend */ + Assert(f < FP_LOCK_SLOTS_PER_BACKEND); + if (MyProc->fpRelId[f] == relid && FAST_PATH_CHECK_LOCKMODE(MyProc, f, lockmode)) { @@ -2685,7 +2752,7 @@ FastPathUnGrantRelationLock(Oid relid, LOCKMODE lockmode) /* we continue iterating so as to update FastPathLocalUseCount */ } if (FAST_PATH_GET_BITS(MyProc, f) != 0) - ++FastPathLocalUseCount; + ++FastPathLocalUseCounts[group]; } return result; } @@ -2714,7 +2781,8 @@ FastPathTransferRelationLocks(LockMethod lockMethodTable, const LOCKTAG *locktag for (i = 0; i < ProcGlobal->allProcCount; i++) { PGPROC *proc = &ProcGlobal->allProcs[i]; - uint32 f; + uint32 j, + group; LWLockAcquire(&proc->fpInfoLock, LW_EXCLUSIVE); @@ -2739,9 +2807,21 @@ FastPathTransferRelationLocks(LockMethod lockMethodTable, const LOCKTAG *locktag continue; } - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + /* Which FP group does the lock belong to? */ + group = FAST_PATH_LOCK_REL_GROUP(relid); + + Assert(group < FP_LOCK_GROUPS_PER_BACKEND); + + for (j = 0; j < FP_LOCK_SLOTS_PER_GROUP; j++) { uint32 lockmode; + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, j); + + /* must not overflow the array of all locks for a backend */ + Assert(f < FP_LOCK_SLOTS_PER_BACKEND); /* Look for an allocated slot matching the given relid. */ if (relid != proc->fpRelId[f] || FAST_PATH_GET_BITS(proc, f) == 0) @@ -2793,13 +2873,26 @@ FastPathGetRelationLockEntry(LOCALLOCK *locallock) PROCLOCK *proclock = NULL; LWLock *partitionLock = LockHashPartitionLock(locallock->hashcode); Oid relid = locktag->locktag_field2; - uint32 f; + uint32 i, + group; + + /* Which FP group does the lock belong to? */ + group = FAST_PATH_LOCK_REL_GROUP(relid); + + Assert(group < FP_LOCK_GROUPS_PER_BACKEND); LWLockAcquire(&MyProc->fpInfoLock, LW_EXCLUSIVE); - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + for (i = 0; i < FP_LOCK_SLOTS_PER_GROUP; i++) { uint32 lockmode; + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, i); + + /* must not overflow the array of all locks for a backend */ + Assert(f < FP_LOCK_SLOTS_PER_BACKEND); /* Look for an allocated slot matching the given relid. */ if (relid != MyProc->fpRelId[f] || FAST_PATH_GET_BITS(MyProc, f) == 0) @@ -2903,6 +2996,12 @@ GetLockConflicts(const LOCKTAG *locktag, LOCKMODE lockmode, int *countp) LWLock *partitionLock; int count = 0; int fast_count = 0; + uint32 group; + + /* Which FP group does the lock belong to? */ + group = FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2); + + Assert(group < FP_LOCK_GROUPS_PER_BACKEND); if (lockmethodid <= 0 || lockmethodid >= lengthof(LockMethods)) elog(ERROR, "unrecognized lock method: %d", lockmethodid); @@ -2957,7 +3056,7 @@ GetLockConflicts(const LOCKTAG *locktag, LOCKMODE lockmode, int *countp) for (i = 0; i < ProcGlobal->allProcCount; i++) { PGPROC *proc = &ProcGlobal->allProcs[i]; - uint32 f; + uint32 j; /* A backend never blocks itself */ if (proc == MyProc) @@ -2979,9 +3078,16 @@ GetLockConflicts(const LOCKTAG *locktag, LOCKMODE lockmode, int *countp) continue; } - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + for (j = 0; j < FP_LOCK_SLOTS_PER_GROUP; j++) { uint32 lockmask; + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, j); + + /* must not overflow the array of all locks for a backend */ + Assert(f < FP_LOCK_SLOTS_PER_BACKEND); /* Look for an allocated slot matching the given relid. */ if (relid != proc->fpRelId[f]) diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index deeb06c9e01..845058da9fa 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -83,8 +83,9 @@ struct XidCache * rather than the main lock table. This eases contention on the lock * manager LWLocks. See storage/lmgr/README for additional details. */ -#define FP_LOCK_SLOTS_PER_BACKEND 16 - +#define FP_LOCK_GROUPS_PER_BACKEND 64 +#define FP_LOCK_SLOTS_PER_GROUP 16 /* don't change */ +#define FP_LOCK_SLOTS_PER_BACKEND (FP_LOCK_SLOTS_PER_GROUP * FP_LOCK_GROUPS_PER_BACKEND) /* * Flags for PGPROC.delayChkptFlags * @@ -292,7 +293,8 @@ struct PGPROC /* Lock manager data, recording fast-path locks taken by this backend. */ LWLock fpInfoLock; /* protects per-backend fast-path state */ - uint64 fpLockBits; /* lock modes held for each fast-path slot */ + uint64 fpLockBits[FP_LOCK_GROUPS_PER_BACKEND]; /* lock modes held for + * each fast-path slot */ Oid fpRelId[FP_LOCK_SLOTS_PER_BACKEND]; /* slots for rel oids */ bool fpVXIDLock; /* are we holding a fast-path VXID lock? */ LocalTransactionId fpLocalTransactionId; /* lxid for fast-path VXID -- 2.46.0 [text/x-patch] v20240905-0002-Size-fast-path-slots-using-max_locks_per_t.patch (15.2K, ../../[email protected]/3-v20240905-0002-Size-fast-path-slots-using-max_locks_per_t.patch) download | inline diff: From 9eaa679b5adea3a842eb944927d77f3d447646fe Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Thu, 5 Sep 2024 18:14:09 +0200 Subject: [PATCH v20240905 2/4] Size fast-path slots using max_locks_per_transaction Instead of using a hard-coded value of 64 groups (1024 fast-path slots), determine the value based on max_locks_per_transaction GUC. This size is calculated startup, before allocating shared memory. The default value of max_locks_per_transaction value is 64, which means 4 fast-path groups by default. The max_locks_per_transaction GUC is the best information about how many locks to expect per backend, but it's main purpose is to size the shared lock table. It is often set to an average number of locks needed by a backend, while some backends may need substantially more locks. This means fast-path capacity calculated from max_locks_per_transaction may not be sufficient for those lock-hungry backends, forcing them to use the shared lock table. If that is a problem, the only solution is to increase the GUC, even if the capacity of the shared lock table was already sufficient. That is not free, because each lock in the shared lock table requires almost 500B. The assumption is this is not an issue. Either there are only few of those lock-intensive backends, in which case concurrency when accessing the shared lock table is not an issue. Or there are enough of them to actually need a higher max_locks_per_transaction value. It may turn out we actually need a separate GUC for fast-path locking, but let's not add one until we're sure that's actually the case. An alternative approach might be to size the fast-path arrays for a multiple of max_locks_per_transaction. The cost of adding a fast-path slot is much lower (only ~5B compared to ~500B for shared lock table), so this would be cheaper than increasing max_locks_per_transaction. But it's not clear what multiple of max_locks_per_transaction to use. --- src/backend/bootstrap/bootstrap.c | 2 ++ src/backend/postmaster/postmaster.c | 5 +++ src/backend/storage/lmgr/lock.c | 34 +++++++++++++++------ src/backend/storage/lmgr/proc.c | 47 +++++++++++++++++++++++++++++ src/backend/tcop/postgres.c | 3 ++ src/backend/utils/init/postinit.c | 34 +++++++++++++++++++++ src/include/miscadmin.h | 1 + src/include/storage/proc.h | 11 ++++--- 8 files changed, 123 insertions(+), 14 deletions(-) diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c index 7637581a184..ed59dfce893 100644 --- a/src/backend/bootstrap/bootstrap.c +++ b/src/backend/bootstrap/bootstrap.c @@ -309,6 +309,8 @@ BootstrapModeMain(int argc, char *argv[], bool check_only) InitializeMaxBackends(); + InitializeFastPathLocks(); + CreateSharedMemoryAndSemaphores(); /* diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c index 96bc1d1cfed..f4a16595d7f 100644 --- a/src/backend/postmaster/postmaster.c +++ b/src/backend/postmaster/postmaster.c @@ -903,6 +903,11 @@ PostmasterMain(int argc, char *argv[]) */ InitializeMaxBackends(); + /* + * Also calculate the size of the fast-path lock arrays in PGPROC. + */ + InitializeFastPathLocks(); + /* * Give preloaded libraries a chance to request additional shared memory. */ diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index f41e4a33f06..134cd8a6e34 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -166,8 +166,13 @@ typedef struct TwoPhaseLockRecord * might be higher than the real number if another backend has transferred * our locks to the primary lock table, but it can never be lower than the * real value, since only we can acquire locks on our own behalf. + * + * XXX Allocate a static array of the maximum size. We could have a pointer + * and then allocate just the right size to save a couple kB, but that does + * not seem worth the extra complexity of having to initialize it etc. This + * way it gets initialized automaticaly. */ -static int FastPathLocalUseCounts[FP_LOCK_GROUPS_PER_BACKEND]; +static int FastPathLocalUseCounts[FP_LOCK_GROUPS_PER_BACKEND_MAX]; /* * Flag to indicate if the relation extension lock is held by this backend. @@ -184,6 +189,17 @@ static int FastPathLocalUseCounts[FP_LOCK_GROUPS_PER_BACKEND]; */ static bool IsRelationExtensionLockHeld PG_USED_FOR_ASSERTS_ONLY = false; +/* + * Number of fast-path locks per backend - size of the arrays in PGPROC. + * This is set only once during start, before initializing shared memory, + * and remains constant after that. + * + * We set the limit based on max_locks_per_transaction GUC, because that's + * the best information about expected number of locks per backend we have. + * See InitializeFastPathLocks for details. + */ +int FastPathLockGroupsPerBackend = 0; + /* * Macros to calculate the group and index for a relation. * @@ -198,7 +214,7 @@ static bool IsRelationExtensionLockHeld PG_USED_FOR_ASSERTS_ONLY = false; * did (rel % 100000) or something like that first, that'd be enough to * not wrap around. But even if it wrapped, would that be a problem? */ -#define FAST_PATH_LOCK_REL_GROUP(rel) (((uint64) (rel) * 49157) % FP_LOCK_GROUPS_PER_BACKEND) +#define FAST_PATH_LOCK_REL_GROUP(rel) (((uint64) (rel) * 49157) % FastPathLockGroupsPerBackend) /* * Given a lock index (into the per-backend array), calculated using the @@ -213,7 +229,7 @@ static bool IsRelationExtensionLockHeld PG_USED_FOR_ASSERTS_ONLY = false; /* Calculate index in the whole per-backend array of lock slots. */ #define FP_LOCK_SLOT_INDEX(group, index) \ - (AssertMacro(((group) >= 0) && ((group) < FP_LOCK_GROUPS_PER_BACKEND)), \ + (AssertMacro(((group) >= 0) && ((group) < FastPathLockGroupsPerBackend)), \ AssertMacro(((index) >= 0) && ((index) < FP_LOCK_SLOTS_PER_GROUP)), \ ((group) * FP_LOCK_SLOTS_PER_GROUP + (index))) @@ -2100,7 +2116,7 @@ LockRelease(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock) /* Which FP group does the lock belong to? */ group = FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2); - Assert(group >= 0 && group < FP_LOCK_GROUPS_PER_BACKEND); + Assert(group >= 0 && group < FastPathLockGroupsPerBackend); /* Attempt fast release of any lock eligible for the fast path. */ if (EligibleForRelationFastPath(locktag, lockmode) && @@ -2679,7 +2695,7 @@ FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode) /* Which FP group does the lock belong to? */ group = FAST_PATH_LOCK_REL_GROUP(relid); - Assert(group < FP_LOCK_GROUPS_PER_BACKEND); + Assert(group < FastPathLockGroupsPerBackend); /* Scan for existing entry for this relid, remembering empty slot. */ for (i = 0; i < FP_LOCK_SLOTS_PER_GROUP; i++) @@ -2730,7 +2746,7 @@ FastPathUnGrantRelationLock(Oid relid, LOCKMODE lockmode) /* Which FP group does the lock belong to? */ group = FAST_PATH_LOCK_REL_GROUP(relid); - Assert(group < FP_LOCK_GROUPS_PER_BACKEND); + Assert(group < FastPathLockGroupsPerBackend); FastPathLocalUseCounts[group] = 0; for (i = 0; i < FP_LOCK_SLOTS_PER_GROUP; i++) @@ -2810,7 +2826,7 @@ FastPathTransferRelationLocks(LockMethod lockMethodTable, const LOCKTAG *locktag /* Which FP group does the lock belong to? */ group = FAST_PATH_LOCK_REL_GROUP(relid); - Assert(group < FP_LOCK_GROUPS_PER_BACKEND); + Assert(group < FastPathLockGroupsPerBackend); for (j = 0; j < FP_LOCK_SLOTS_PER_GROUP; j++) { @@ -2879,7 +2895,7 @@ FastPathGetRelationLockEntry(LOCALLOCK *locallock) /* Which FP group does the lock belong to? */ group = FAST_PATH_LOCK_REL_GROUP(relid); - Assert(group < FP_LOCK_GROUPS_PER_BACKEND); + Assert(group < FastPathLockGroupsPerBackend); LWLockAcquire(&MyProc->fpInfoLock, LW_EXCLUSIVE); @@ -3001,7 +3017,7 @@ GetLockConflicts(const LOCKTAG *locktag, LOCKMODE lockmode, int *countp) /* Which FP group does the lock belong to? */ group = FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2); - Assert(group < FP_LOCK_GROUPS_PER_BACKEND); + Assert(group < FastPathLockGroupsPerBackend); if (lockmethodid <= 0 || lockmethodid >= lengthof(LockMethods)) elog(ERROR, "unrecognized lock method: %d", lockmethodid); diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index ac66da8638f..a91b6f8a6c0 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -103,6 +103,8 @@ ProcGlobalShmemSize(void) Size size = 0; Size TotalProcs = add_size(MaxBackends, add_size(NUM_AUXILIARY_PROCS, max_prepared_xacts)); + Size fpLockBitsSize, + fpRelIdSize; /* ProcGlobal */ size = add_size(size, sizeof(PROC_HDR)); @@ -113,6 +115,18 @@ ProcGlobalShmemSize(void) size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->subxidStates))); size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->statusFlags))); + /* + * fast-path lock arrays + * + * XXX The explicit alignment may not be strictly necessary, as both + * values are already multiples of 8 bytes, which is what MAXALIGN does. + * But better to make that obvious. + */ + fpLockBitsSize = MAXALIGN(FastPathLockGroupsPerBackend * sizeof(uint64)); + fpRelIdSize = MAXALIGN(FastPathLockGroupsPerBackend * sizeof(Oid) * FP_LOCK_SLOTS_PER_GROUP); + + size = add_size(size, mul_size(TotalProcs, (fpLockBitsSize + fpRelIdSize))); + return size; } @@ -162,6 +176,10 @@ InitProcGlobal(void) j; bool found; uint32 TotalProcs = MaxBackends + NUM_AUXILIARY_PROCS + max_prepared_xacts; + char *fpPtr, + *fpEndPtr PG_USED_FOR_ASSERTS_ONLY; + Size fpLockBitsSize, + fpRelIdSize; /* Create the ProcGlobal shared structure */ ProcGlobal = (PROC_HDR *) @@ -211,12 +229,38 @@ InitProcGlobal(void) ProcGlobal->statusFlags = (uint8 *) ShmemAlloc(TotalProcs * sizeof(*ProcGlobal->statusFlags)); MemSet(ProcGlobal->statusFlags, 0, TotalProcs * sizeof(*ProcGlobal->statusFlags)); + /* + * Allocate arrays for fast-path locks. Those are variable-length, so + * can't be included in PGPROC. We allocate a separate piece of shared + * memory and then divide that between backends. + */ + fpLockBitsSize = MAXALIGN(FastPathLockGroupsPerBackend * sizeof(uint64)); + fpRelIdSize = MAXALIGN(FastPathLockGroupsPerBackend * sizeof(Oid) * FP_LOCK_SLOTS_PER_GROUP); + + fpPtr = ShmemAlloc(TotalProcs * (fpLockBitsSize + fpRelIdSize)); + MemSet(fpPtr, 0, TotalProcs * (fpLockBitsSize + fpRelIdSize)); + + /* For asserts checking we did not overflow. */ + fpEndPtr = fpPtr + (TotalProcs * (fpLockBitsSize + fpRelIdSize)); + for (i = 0; i < TotalProcs; i++) { PGPROC *proc = &procs[i]; /* Common initialization for all PGPROCs, regardless of type. */ + /* + * Set the fast-path lock arrays, and move the pointer. We interleave + * the two arrays, to keep at least some locality. + */ + proc->fpLockBits = (uint64 *) fpPtr; + fpPtr += fpLockBitsSize; + + proc->fpRelId = (Oid *) fpPtr; + fpPtr += fpRelIdSize; + + Assert(fpPtr <= fpEndPtr); + /* * Set up per-PGPROC semaphore, latch, and fpInfoLock. Prepared xact * dummy PGPROCs don't need these though - they're never associated @@ -278,6 +322,9 @@ InitProcGlobal(void) pg_atomic_init_u64(&(proc->waitStart), 0); } + /* We expect to consume exactly the expected amount of data. */ + Assert(fpPtr = fpEndPtr); + /* * Save pointers to the blocks of PGPROC structures reserved for auxiliary * processes and prepared transactions. diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 8bc6bea1135..f54ae00abca 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -4166,6 +4166,9 @@ PostgresSingleUserMain(int argc, char *argv[], /* Initialize MaxBackends */ InitializeMaxBackends(); + /* Initialize size of fast-path lock cache. */ + InitializeFastPathLocks(); + /* * Give preloaded libraries a chance to request additional shared memory. */ diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 3b50ce19a2c..1faf756c8d8 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -557,6 +557,40 @@ InitializeMaxBackends(void) MAX_BACKENDS))); } +/* + * Initialize the number of fast-path lock slots in PGPROC. + * + * This must be called after modules have had the chance to alter GUCs in + * shared_preload_libraries and before shared memory size is determined. + * + * The default max_locks_per_xact=64 means 4 groups by default. + * + * We allow anything between 1 and 1024 groups, with the usual power-of-2 + * logic. The 1 is the "old" value before allowing multiple groups, 1024 + * is an arbitrary limit (matching max_locks_per_xact = 16k). Values over + * 1024 are unlikely to be beneficial - we're likely to hit other + * bottlenecks long before that. + */ +void +InitializeFastPathLocks(void) +{ + Assert(FastPathLockGroupsPerBackend == 0); + + /* we need at least one group */ + FastPathLockGroupsPerBackend = 1; + + while (FastPathLockGroupsPerBackend < FP_LOCK_GROUPS_PER_BACKEND_MAX) + { + /* stop once we exceed max_locks_per_xact */ + if (FastPathLockGroupsPerBackend * FP_LOCK_SLOTS_PER_GROUP >= max_locks_per_xact) + break; + + FastPathLockGroupsPerBackend *= 2; + } + + Assert(FastPathLockGroupsPerBackend <= FP_LOCK_GROUPS_PER_BACKEND_MAX); +} + /* * Early initialization of a backend (either standalone or under postmaster). * This happens even before InitPostgres. diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h index 25348e71eb9..e26d108a470 100644 --- a/src/include/miscadmin.h +++ b/src/include/miscadmin.h @@ -475,6 +475,7 @@ extern PGDLLIMPORT ProcessingMode Mode; #define INIT_PG_OVERRIDE_ROLE_LOGIN 0x0004 extern void pg_split_opts(char **argv, int *argcp, const char *optstr); extern void InitializeMaxBackends(void); +extern void InitializeFastPathLocks(void); extern void InitPostgres(const char *in_dbname, Oid dboid, const char *username, Oid useroid, bits32 flags, diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 845058da9fa..0e55c166529 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -83,9 +83,11 @@ struct XidCache * rather than the main lock table. This eases contention on the lock * manager LWLocks. See storage/lmgr/README for additional details. */ -#define FP_LOCK_GROUPS_PER_BACKEND 64 +extern PGDLLIMPORT int FastPathLockGroupsPerBackend; +#define FP_LOCK_GROUPS_PER_BACKEND_MAX 1024 #define FP_LOCK_SLOTS_PER_GROUP 16 /* don't change */ -#define FP_LOCK_SLOTS_PER_BACKEND (FP_LOCK_SLOTS_PER_GROUP * FP_LOCK_GROUPS_PER_BACKEND) +#define FP_LOCK_SLOTS_PER_BACKEND (FP_LOCK_SLOTS_PER_GROUP * FastPathLockGroupsPerBackend) + /* * Flags for PGPROC.delayChkptFlags * @@ -293,9 +295,8 @@ struct PGPROC /* Lock manager data, recording fast-path locks taken by this backend. */ LWLock fpInfoLock; /* protects per-backend fast-path state */ - uint64 fpLockBits[FP_LOCK_GROUPS_PER_BACKEND]; /* lock modes held for - * each fast-path slot */ - Oid fpRelId[FP_LOCK_SLOTS_PER_BACKEND]; /* slots for rel oids */ + uint64 *fpLockBits; /* lock modes held for each fast-path slot */ + Oid *fpRelId; /* slots for rel oids */ bool fpVXIDLock; /* are we holding a fast-path VXID lock? */ LocalTransactionId fpLocalTransactionId; /* lxid for fast-path VXID * lock */ -- 2.46.0 [text/x-patch] v20240905-0003-separate-guc-to-allow-benchmarking.patch (4.7K, ../../[email protected]/4-v20240905-0003-separate-guc-to-allow-benchmarking.patch) download | inline diff: From d9f3deaa518a673e4dc8df1ff6e40f47c2637e5e Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Thu, 5 Sep 2024 16:52:26 +0200 Subject: [PATCH v20240905 3/4] separate guc to allow benchmarking --- src/backend/bootstrap/bootstrap.c | 2 -- src/backend/postmaster/postmaster.c | 5 ----- src/backend/tcop/postgres.c | 3 --- src/backend/utils/init/postinit.c | 34 ----------------------------- src/backend/utils/misc/guc_tables.c | 10 +++++++++ src/include/miscadmin.h | 1 - 6 files changed, 10 insertions(+), 45 deletions(-) diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c index ed59dfce893..7637581a184 100644 --- a/src/backend/bootstrap/bootstrap.c +++ b/src/backend/bootstrap/bootstrap.c @@ -309,8 +309,6 @@ BootstrapModeMain(int argc, char *argv[], bool check_only) InitializeMaxBackends(); - InitializeFastPathLocks(); - CreateSharedMemoryAndSemaphores(); /* diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c index f4a16595d7f..96bc1d1cfed 100644 --- a/src/backend/postmaster/postmaster.c +++ b/src/backend/postmaster/postmaster.c @@ -903,11 +903,6 @@ PostmasterMain(int argc, char *argv[]) */ InitializeMaxBackends(); - /* - * Also calculate the size of the fast-path lock arrays in PGPROC. - */ - InitializeFastPathLocks(); - /* * Give preloaded libraries a chance to request additional shared memory. */ diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index f54ae00abca..8bc6bea1135 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -4166,9 +4166,6 @@ PostgresSingleUserMain(int argc, char *argv[], /* Initialize MaxBackends */ InitializeMaxBackends(); - /* Initialize size of fast-path lock cache. */ - InitializeFastPathLocks(); - /* * Give preloaded libraries a chance to request additional shared memory. */ diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 1faf756c8d8..3b50ce19a2c 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -557,40 +557,6 @@ InitializeMaxBackends(void) MAX_BACKENDS))); } -/* - * Initialize the number of fast-path lock slots in PGPROC. - * - * This must be called after modules have had the chance to alter GUCs in - * shared_preload_libraries and before shared memory size is determined. - * - * The default max_locks_per_xact=64 means 4 groups by default. - * - * We allow anything between 1 and 1024 groups, with the usual power-of-2 - * logic. The 1 is the "old" value before allowing multiple groups, 1024 - * is an arbitrary limit (matching max_locks_per_xact = 16k). Values over - * 1024 are unlikely to be beneficial - we're likely to hit other - * bottlenecks long before that. - */ -void -InitializeFastPathLocks(void) -{ - Assert(FastPathLockGroupsPerBackend == 0); - - /* we need at least one group */ - FastPathLockGroupsPerBackend = 1; - - while (FastPathLockGroupsPerBackend < FP_LOCK_GROUPS_PER_BACKEND_MAX) - { - /* stop once we exceed max_locks_per_xact */ - if (FastPathLockGroupsPerBackend * FP_LOCK_SLOTS_PER_GROUP >= max_locks_per_xact) - break; - - FastPathLockGroupsPerBackend *= 2; - } - - Assert(FastPathLockGroupsPerBackend <= FP_LOCK_GROUPS_PER_BACKEND_MAX); -} - /* * Early initialization of a backend (either standalone or under postmaster). * This happens even before InitPostgres. diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 686309db58b..cef6341979f 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -2788,6 +2788,16 @@ struct config_int ConfigureNamesInt[] = NULL, NULL, NULL }, + { + {"fastpath_lock_groups", PGC_POSTMASTER, LOCK_MANAGEMENT, + gettext_noop("Sets the maximum number of locks per transaction."), + gettext_noop("number of groups in the fast-path lock array.") + }, + &FastPathLockGroupsPerBackend, + 1, 1, INT_MAX, + NULL, NULL, NULL + }, + { {"max_pred_locks_per_transaction", PGC_POSTMASTER, LOCK_MANAGEMENT, gettext_noop("Sets the maximum number of predicate locks per transaction."), diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h index e26d108a470..25348e71eb9 100644 --- a/src/include/miscadmin.h +++ b/src/include/miscadmin.h @@ -475,7 +475,6 @@ extern PGDLLIMPORT ProcessingMode Mode; #define INIT_PG_OVERRIDE_ROLE_LOGIN 0x0004 extern void pg_split_opts(char **argv, int *argcp, const char *optstr); extern void InitializeMaxBackends(void); -extern void InitializeFastPathLocks(void); extern void InitPostgres(const char *in_dbname, Oid dboid, const char *username, Oid useroid, bits32 flags, -- 2.46.0 [text/x-patch] v20240905-0004-lock-stats.patch (13.7K, ../../[email protected]/5-v20240905-0004-lock-stats.patch) download | inline diff: From 6fbe413d86ecb1dca6acf939ab06550290ec337b Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Tue, 3 Sep 2024 19:27:16 +0200 Subject: [PATCH v20240905 4/4] lock stats --- src/backend/catalog/system_views.sql | 6 + src/backend/storage/lmgr/lock.c | 18 +++ src/backend/utils/activity/Makefile | 1 + src/backend/utils/activity/pgstat.c | 19 +++ src/backend/utils/activity/pgstat_locks.c | 134 ++++++++++++++++++++++ src/backend/utils/adt/pgstatfuncs.c | 18 +++ src/include/catalog/pg_proc.dat | 13 +++ src/include/pgstat.h | 21 +++- src/include/utils/pgstat_internal.h | 22 ++++ 9 files changed, 251 insertions(+), 1 deletion(-) create mode 100644 src/backend/utils/activity/pgstat_locks.c diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 7fd5d256a18..f5aecf14365 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1134,6 +1134,12 @@ CREATE VIEW pg_stat_bgwriter AS pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; +CREATE VIEW pg_stat_locks AS + SELECT + pg_stat_get_fplocks_num_inserted() AS num_inserted, + pg_stat_get_fplocks_num_overflowed() AS num_overflowed, + pg_stat_get_fplocks_stat_reset_time() AS stats_reset; + CREATE VIEW pg_stat_checkpointer AS SELECT pg_stat_get_checkpointer_num_timed() AS num_timed, diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index 134cd8a6e34..ecaf64b614c 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -39,6 +39,7 @@ #include "access/xlogutils.h" #include "miscadmin.h" #include "pg_trace.h" +#include "pgstat.h" #include "storage/proc.h" #include "storage/procarray.h" #include "storage/sinvaladt.h" @@ -964,6 +965,23 @@ LockAcquireExtended(const LOCKTAG *locktag, log_lock = true; } + /* + * See if an eligible lock would fit into the fast path cache or not. + * This is not quite correct, for two reasons. Firstly, eligible locks + * may end up requiring a regular lock because of a strong lock being + * held by someone else. Secondly, the count can be a bit stale, if + * some other backend promoted some of our fast-path locks. + * + * XXX Worth counting non-eligible locks too? + */ + if (EligibleForRelationFastPath(locktag, lockmode)) + { + if (FastPathLocalUseCounts[FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2)] < FP_LOCK_SLOTS_PER_GROUP) + ++PendingFastPathLockStats.num_inserted; + else + ++PendingFastPathLockStats.num_overflowed; + } + /* * Attempt to take lock via fast path, if eligible. But if we remember * having filled up the fast path array, we don't attempt to make any diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile index b9fd66ea17c..4b595f304d0 100644 --- a/src/backend/utils/activity/Makefile +++ b/src/backend/utils/activity/Makefile @@ -25,6 +25,7 @@ OBJS = \ pgstat_database.o \ pgstat_function.o \ pgstat_io.o \ + pgstat_locks.o \ pgstat_relation.o \ pgstat_replslot.o \ pgstat_shmem.o \ diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 178b5ef65aa..39475c5915f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -81,6 +81,7 @@ * - pgstat_database.c * - pgstat_function.c * - pgstat_io.c + * - pgstat_locks.c * - pgstat_relation.c * - pgstat_replslot.c * - pgstat_slru.c @@ -446,6 +447,21 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .reset_all_cb = pgstat_wal_reset_all_cb, .snapshot_cb = pgstat_wal_snapshot_cb, }, + + [PGSTAT_KIND_FPLOCKS] = { + .name = "fp-locks", + + .fixed_amount = true, + + .snapshot_ctl_off = offsetof(PgStat_Snapshot, fplocks), + .shared_ctl_off = offsetof(PgStat_ShmemControl, fplocks), + .shared_data_off = offsetof(PgStatShared_FastPathLocks, stats), + .shared_data_len = sizeof(((PgStatShared_FastPathLocks *) 0)->stats), + + .init_shmem_cb = pgstat_fplocks_init_shmem_cb, + .reset_all_cb = pgstat_fplocks_reset_all_cb, + .snapshot_cb = pgstat_fplocks_snapshot_cb, + }, }; /* @@ -739,6 +755,9 @@ pgstat_report_stat(bool force) /* flush SLRU stats */ partial_flush |= pgstat_slru_flush(nowait); + /* flush lock stats */ + partial_flush |= pgstat_fplocks_flush(nowait); + last_flush = now; /* diff --git a/src/backend/utils/activity/pgstat_locks.c b/src/backend/utils/activity/pgstat_locks.c new file mode 100644 index 00000000000..99a5d5259da --- /dev/null +++ b/src/backend/utils/activity/pgstat_locks.c @@ -0,0 +1,134 @@ +/* ------------------------------------------------------------------------- + * + * pgstat_locks.c + * Implementation of locks statistics. + * + * This file contains the implementation of lock statistics. It is kept + * separate from pgstat.c to enforce the line between the statistics access / + * storage implementation and the details about individual types of + * statistics. + * + * Copyright (c) 2001-2024, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/backend/utils/activity/pgstat_locks.c + * ------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "utils/pgstat_internal.h" + + +PgStat_FastPathLockStats PendingFastPathLockStats = {0}; + + + +/* + * Do we have any locks to report? + */ +static bool +pgstat_have_pending_locks(void) +{ + return (PendingFastPathLockStats.num_inserted > 0) || + (PendingFastPathLockStats.num_overflowed > 0); +} + + +/* + * If nowait is true, this function returns true if the lock could not be + * acquired. Otherwise return false. + */ +bool +pgstat_fplocks_flush(bool nowait) +{ + PgStatShared_FastPathLocks *stats_shmem = &pgStatLocal.shmem->fplocks; + + Assert(IsUnderPostmaster || !IsPostmasterEnvironment); + Assert(pgStatLocal.shmem != NULL && + !pgStatLocal.shmem->is_shutdown); + + /* + * This function can be called even if nothing at all has happened. Avoid + * taking lock for nothing in that case. + */ + if (!pgstat_have_pending_locks()) + return false; + + if (!nowait) + LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE); + else if (!LWLockConditionalAcquire(&stats_shmem->lock, LW_EXCLUSIVE)) + return true; + +#define FPLOCKS_ACC(fld) stats_shmem->stats.fld += PendingFastPathLockStats.fld + FPLOCKS_ACC(num_inserted); + FPLOCKS_ACC(num_overflowed); +#undef FPLOCKS_ACC + + LWLockRelease(&stats_shmem->lock); + + /* + * Clear out the statistics buffer, so it can be re-used. + */ + MemSet(&PendingFastPathLockStats, 0, sizeof(PendingFastPathLockStats)); + + return false; +} + +/* + * Support function for the SQL-callable pgstat* functions. Returns + * a pointer to the fast-path lock statistics struct. + */ +PgStat_FastPathLockStats * +pgstat_fetch_stat_fplocks(void) +{ + pgstat_snapshot_fixed(PGSTAT_KIND_FPLOCKS); + + return &pgStatLocal.snapshot.fplocks; +} + +void +pgstat_fplocks_init_shmem_cb(void *stats) +{ + PgStatShared_FastPathLocks *stats_shmem = (PgStatShared_FastPathLocks *) stats; + + LWLockInitialize(&stats_shmem->lock, LWTRANCHE_PGSTATS_DATA); +} + +void +pgstat_fplocks_reset_all_cb(TimestampTz ts) +{ + PgStatShared_FastPathLocks *stats_shmem = &pgStatLocal.shmem->fplocks; + + /* see explanation above PgStatShared_FastPathLocks for the reset protocol */ + LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE); + pgstat_copy_changecounted_stats(&stats_shmem->reset_offset, + &stats_shmem->stats, + sizeof(stats_shmem->stats), + &stats_shmem->changecount); + stats_shmem->stats.stat_reset_timestamp = ts; + LWLockRelease(&stats_shmem->lock); +} + +void +pgstat_fplocks_snapshot_cb(void) +{ + PgStatShared_FastPathLocks *stats_shmem = &pgStatLocal.shmem->fplocks; + PgStat_FastPathLockStats *reset_offset = &stats_shmem->reset_offset; + PgStat_FastPathLockStats reset; + + pgstat_copy_changecounted_stats(&pgStatLocal.snapshot.fplocks, + &stats_shmem->stats, + sizeof(stats_shmem->stats), + &stats_shmem->changecount); + + LWLockAcquire(&stats_shmem->lock, LW_SHARED); + memcpy(&reset, reset_offset, sizeof(stats_shmem->stats)); + LWLockRelease(&stats_shmem->lock); + + /* compensate by reset offsets */ +#define FPLOCKS_COMP(fld) pgStatLocal.snapshot.fplocks.fld -= reset.fld; + FPLOCKS_COMP(num_inserted); + FPLOCKS_COMP(num_overflowed); +#undef FPLOCKS_COMP +} diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 97dc09ac0d9..dcd4957777d 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1261,6 +1261,24 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS) PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->buf_alloc); } +Datum +pg_stat_get_fplocks_num_inserted(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_stat_fplocks()->num_inserted); +} + +Datum +pg_stat_get_fplocks_num_overflowed(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_stat_fplocks()->num_overflowed); +} + +Datum +pg_stat_get_fplocks_stat_reset_time(PG_FUNCTION_ARGS) +{ + PG_RETURN_TIMESTAMPTZ(pgstat_fetch_stat_fplocks()->stat_reset_timestamp); +} + /* * When adding a new column to the pg_stat_io view, add a new enum value * here above IO_NUM_COLUMNS. diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index ff5436acacf..242aea463ae 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5986,6 +5986,19 @@ provolatile => 'v', prorettype => 'void', proargtypes => 'oid', prosrc => 'pg_stat_reset_subscription_stats' }, +{ oid => '6095', descr => 'statistics: number of acquired fast-path locks', + proname => 'pg_stat_get_fplocks_num_inserted', provolatile => 's', proparallel => 'r', + prorettype => 'int8', proargtypes => '', prosrc => 'pg_stat_get_fplocks_num_inserted' }, + +{ oid => '6096', descr => 'statistics: number of not acquired fast-path locks', + proname => 'pg_stat_get_fplocks_num_overflowed', provolatile => 's', proparallel => 'r', + prorettype => 'int8', proargtypes => '', prosrc => 'pg_stat_get_fplocks_num_overflowed' }, + +{ oid => '6097', descr => 'statistics: last reset for the fast-path locks', + proname => 'pg_stat_get_fplocks_stat_reset_time', provolatile => 's', + proparallel => 'r', prorettype => 'timestamptz', proargtypes => '', + prosrc => 'pg_stat_get_fplocks_stat_reset_time' }, + { oid => '3163', descr => 'current trigger depth', proname => 'pg_trigger_depth', provolatile => 's', proparallel => 'r', prorettype => 'int4', proargtypes => '', prosrc => 'pg_trigger_depth' }, diff --git a/src/include/pgstat.h b/src/include/pgstat.h index be2c91168a1..f66b189f8df 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -57,9 +57,10 @@ #define PGSTAT_KIND_IO 9 #define PGSTAT_KIND_SLRU 10 #define PGSTAT_KIND_WAL 11 +#define PGSTAT_KIND_FPLOCKS 12 #define PGSTAT_KIND_BUILTIN_MIN PGSTAT_KIND_DATABASE -#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_WAL +#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_FPLOCKS #define PGSTAT_KIND_BUILTIN_SIZE (PGSTAT_KIND_BUILTIN_MAX + 1) /* Custom stats kinds */ @@ -303,6 +304,13 @@ typedef struct PgStat_CheckpointerStats TimestampTz stat_reset_timestamp; } PgStat_CheckpointerStats; +typedef struct PgStat_FastPathLockStats +{ + PgStat_Counter num_inserted; + PgStat_Counter num_overflowed; + TimestampTz stat_reset_timestamp; +} PgStat_FastPathLockStats; + /* * Types related to counting IO operations @@ -538,6 +546,10 @@ extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void); extern void pgstat_report_bgwriter(void); extern PgStat_BgWriterStats *pgstat_fetch_stat_bgwriter(void); +/* + * Functions in pgstat_locks.c + */ +extern PgStat_FastPathLockStats *pgstat_fetch_stat_fplocks(void); /* * Functions in pgstat_checkpointer.c @@ -811,4 +823,11 @@ extern PGDLLIMPORT SessionEndType pgStatSessionEndCause; extern PGDLLIMPORT PgStat_PendingWalStats PendingWalStats; +/* + * Variables in pgstat_locks.c + */ + +/* updated directly by fast-path locking */ +extern PGDLLIMPORT PgStat_FastPathLockStats PendingFastPathLockStats; + #endif /* PGSTAT_H */ diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 25820cbf0a6..0627983846c 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -340,6 +340,15 @@ typedef struct PgStatShared_BgWriter PgStat_BgWriterStats reset_offset; } PgStatShared_BgWriter; +typedef struct PgStatShared_FastPathLocks +{ + /* lock protects ->reset_offset as well as stats->stat_reset_timestamp */ + LWLock lock; + uint32 changecount; + PgStat_FastPathLockStats stats; + PgStat_FastPathLockStats reset_offset; +} PgStatShared_FastPathLocks; + typedef struct PgStatShared_Checkpointer { /* lock protects ->reset_offset as well as stats->stat_reset_timestamp */ @@ -453,6 +462,7 @@ typedef struct PgStat_ShmemControl PgStatShared_IO io; PgStatShared_SLRU slru; PgStatShared_Wal wal; + PgStatShared_FastPathLocks fplocks; /* * Custom stats data with fixed-numbered objects, indexed by (PgStat_Kind @@ -487,6 +497,8 @@ typedef struct PgStat_Snapshot PgStat_WalStats wal; + PgStat_FastPathLockStats fplocks; + /* * Data in snapshot for custom fixed-numbered statistics, indexed by * (PgStat_Kind - PGSTAT_KIND_CUSTOM_MIN). Each entry is allocated in @@ -704,6 +716,16 @@ extern void pgstat_drop_transactional(PgStat_Kind kind, Oid dboid, Oid objoid); extern void pgstat_create_transactional(PgStat_Kind kind, Oid dboid, Oid objoid); +/* + * Functions in pgstat_locks.c + */ + +extern bool pgstat_fplocks_flush(bool); +extern void pgstat_fplocks_init_shmem_cb(void *stats); +extern void pgstat_fplocks_reset_all_cb(TimestampTz ts); +extern void pgstat_fplocks_snapshot_cb(void); + + /* * Variables in pgstat.c */ -- 2.46.0 ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-05 17:33 Tomas Vondra <[email protected]> parent: Tomas Vondra <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Tomas Vondra @ 2024-09-05 17:33 UTC (permalink / raw) To: Jakub Wartak <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> On 9/4/24 13:15, Tomas Vondra wrote: > On 9/4/24 11:29, Jakub Wartak wrote: >> Hi Tomas! >> >> ... >> >> My $0.02 cents: the originating case that triggered those patches, >> actually started with LWLock/lock_manager waits being the top#1. The >> operator can cross check (join) that with a group by pg_locks.fastpath >> (='f'), count(*). So, IMHO we have good observability in this case >> (rare thing to say!) >> > > That's a good point. So if you had to give some instructions to users > what to measure / monitor, and how to adjust the GUC based on that, what > would your instructions be? > After thinking about this a bit more, I'm actually wondering if this is source of information is sufficient. Firstly, it's just a snapshot of a single instance, and it's not quite trivial to get some summary for longer time period (people would have to sample it often enough, etc.). Doable, but much less convenient than the cumulative counters. But for the sampling, doesn't this produce skewed data? Imagine you have a workload with very short queries (which is when fast-path matters), so you're likely to see the backend while it's obtaining the locks. If the fast-path locks take much faster acquire (kinda the whole point), we're more likely to see the backend while it's obtaining the regular locks. Let's say the backend needs 1000 locks, and 500 of those fit into the fast-path array. We're likely to see the 500 fast-path locks already acquired, and a random fraction of the 500 non-fast-path locks. So in the end you'll se backends needing 500 fast-path locks and 250 regular locks. That doesn't seem terrible, but I guess the difference can be made even larger. regards -- Tomas Vondra ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-06 11:56 Jakub Wartak <[email protected]> parent: Tomas Vondra <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Jakub Wartak @ 2024-09-06 11:56 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> On Thu, Sep 5, 2024 at 7:33 PM Tomas Vondra <[email protected]> wrote: >>> My $0.02 cents: the originating case that triggered those patches, >>> actually started with LWLock/lock_manager waits being the top#1. The >>> operator can cross check (join) that with a group by pg_locks.fastpath >>> (='f'), count(*). So, IMHO we have good observability in this case >>> (rare thing to say!) >>> >> >> That's a good point. So if you had to give some instructions to users >> what to measure / monitor, and how to adjust the GUC based on that, what >> would your instructions be? >> > > After thinking about this a bit more, I'm actually wondering if this is > source of information is sufficient. Firstly, it's just a snapshot of a > single instance, and it's not quite trivial to get some summary for > longer time period (people would have to sample it often enough, etc.). > Doable, but much less convenient than the cumulative counters. OK, so answering previous question: Probably just monitor pg_stat_activty (group on wait events count(*)) with pg_locks with group by on per-pid and fastpath . Even simple observations with \watch 0.1 are good enough to confirm/deny the root-cause in practice even for short bursts while it is happening. While deploying monitoring for a longer time (with say sample of 1s), you sooner or later would get the __high water mark__ and possibly allow up to that many fastpaths as starting point as there are locks occuring for affected PIDs (or double the amount). > But for the sampling, doesn't this produce skewed data? Imagine you have > a workload with very short queries (which is when fast-path matters), so > you're likely to see the backend while it's obtaining the locks. If the > fast-path locks take much faster acquire (kinda the whole point), we're > more likely to see the backend while it's obtaining the regular locks. > > Let's say the backend needs 1000 locks, and 500 of those fit into the > fast-path array. We're likely to see the 500 fast-path locks already > acquired, and a random fraction of the 500 non-fast-path locks. So in > the end you'll se backends needing 500 fast-path locks and 250 regular > locks. That doesn't seem terrible, but I guess the difference can be > made even larger. ... it doesn't need to perfect data to act, right? We may just need information that it is happening (well we do). Maybe it's too pragmatic point of view, but wasting some bits of memory for this, but still being allowed to control it how much it allocates in the end -- is much better situation than today, without any control where we are wasting crazy CPU time on all those futex() syscalls and context switches Another angle is that if you see the SQL causing it, it is most often going to be attributed to partitioning and people ending up accessing way too many partitions (thousands) without proper partition pruning - sometimes it even triggers re-partitioning of the said tables. So maybe the realistic "fastpath sizing" should assume something that supports: a) usual number of tables in JOINs (just few of them are fine like today) -> ok b) interval 1 month partitions for let's say 5 years (12*5 = 60), joined to some other table like that gives like what, max 120? -> so if you have users doing SELECT * FROM such_table , they will already have set the max_locks_per_xact probably to something higher. c) HASH partitioning up to VCPU-that-are-in-the-wild count? (say 64 or 128? so it sounds same as above?) d) probably we should not care here at all if somebody wants daily partitioning across years with HASH (sub)partitions without partition pruning -> it has nothing to do with being "fast" anyway Judging from the current reports, people have configured max_locks_per_xact like this: ~75% have it at default (64), 10% has 1024, 5% has 128 and the rest (~10%) is having between 100..thousands, with extreme one-offs @ 25k (wild misconfiguration judging from the other specs). BTW: you probably need to register this $thread into CF for others to see too (it's not there?) -J. ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-09-12 21:40 Tomas Vondra <[email protected]> parent: Tomas Vondra <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Tomas Vondra @ 2024-09-12 21:40 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> Hi, I've spent quite a bit of time trying to identify cases where having more fast-path lock slots could be harmful, without any luck. I started with the EPYC machine I used for the earlier tests, but found nothing, except for a couple cases unrelated to this patch, because it affects even cases without the patch applied at all. More like random noise or maybe some issue with the VM (or differences to the VM used earlier). I pushed the results to githus [1] anyway, if anyone wants to look. So I switched to my smaller machines, and ran a simple test on master, with the hard-coded arrays, and with the arrays moves out of PGPROC (and sized per max_locks_per_transaction). I was looking for regressions, so I wanted to test a case that can't benefit from fast-path locking, while paying the costs. So I decided to do pgbench -S with 4 partitions, because that fits into the 16 slots we had before, and scale 1 to keep everything in memory. And then did a couple read-only runs, first with 64 locks/transaction (default), then with 1024 locks/transaction. Attached is a shell script I used to collect this - it creates and removes clusters, so be careful. Should be fairly obvious what it tests and how. The results for max_locks_per_transaction=64 look like this (the numbers are throughput): machine mode clients master built-in with-guc --------------------------------------------------------- i5 prepared 1 14970 14991 14981 4 51638 51615 51388 simple 1 14042 14136 14008 4 48705 48572 48457 ------------------------------------------------------ xeon prepared 1 13213 13330 13170 4 49280 49191 49263 16 151413 152268 151560 simple 1 12250 12291 12316 4 45910 46148 45843 16 141774 142165 142310 And compared to master machine mode clients built-in with-guc ------------------------------------------------- i5 prepared 1 100.14% 100.08% 4 99.95% 99.51% simple 1 100.67% 99.76% 4 99.73% 99.49% ---------------------------------------------- xeon prepared 1 100.89% 99.68% 4 99.82% 99.97% 16 100.56% 100.10% simple 1 100.34% 100.54% 4 100.52% 99.85% 16 100.28% 100.38% So, no difference whatsoever - it's +/- 0.5%, well within random noise. And with max_locks_per_transaction=1024 the story is exactly the same: machine mode clients master built-in with-guc --------------------------------------------------------- i5 prepared 1 15000 14928 14948 4 51498 51351 51504 simple 1 14124 14092 14065 4 48531 48517 48351 xeon prepared 1 13384 13325 13290 4 49257 49309 49345 16 151668 151940 152201 simple 1 12357 12351 12363 4 46039 46126 46201 16 141851 142402 142427 machine mode clients built-in with-guc ------------------------------------------------- i5 prepared 1 99.52% 99.65% 4 99.71% 100.01% simple 1 99.77% 99.58% 4 99.97% 99.63% xeon prepared 1 99.56% 99.30% 4 100.11% 100.18% 16 100.18% 100.35% simple 1 99.96% 100.05% 4 100.19% 100.35% 16 100.39% 100.41% with max_locks_per_transaction=1024, it's fair to expect the fast-path locking to be quite beneficial. Of course, it's possible the GUC is set this high because of some rare issue (say, to run pg_dump, which needs to lock everything). I did look at docs if anything needs updating, but I don't think so. The SGML docs only talk about fast-path locking at fairly high level, not about how many we have etc. Same for src/backend/storage/lmgr/README, which is focusing on the correctness of fast-path locking, and that's not changed by this patch. I also cleaned up (removed) some of the Asserts checking that we got a valid group / slot index. I don't think this really helped in practice, once I added asserts to the macros. Anyway, at this point I'm quite happy with this improvement. I didn't have any clear plan when to commit this, but I'm considering doing so sometime next week, unless someone objects or asks for some additional benchmarks etc. One thing I'm not quite sure about yet is whether to commit this as a single change, or the way the attached patches do that, with the first patch keeping the larger array in PGPROC and the second patch making it separate and sized on max_locks_per_transaction ... Opinions? regards [1] https://github.com/tvondra/pg-lock-scalability-results -- Tomas Vondra Attachments: [text/x-patch] v20240912-0001-Increase-the-number-of-fast-path-lock-slot.patch (13.9K, ../../[email protected]/2-v20240912-0001-Increase-the-number-of-fast-path-lock-slot.patch) download | inline diff: From 7ae67a162fdcb80746bed45260fa937fc025b08b Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Thu, 12 Sep 2024 23:09:41 +0200 Subject: [PATCH v20240912 1/2] Increase the number of fast-path lock slots The fast-path locking introduced in 9.2 allowed each backend to acquire up to 16 relation locks cheaply, provided the lock level allows that. If a backend needs to hold more locks, it has to insert them into the regular lock table in shared memory. This is considerably more expensive, and on many-core systems may be subject to contention. The limit of 16 entries was always rather low, even with simple queries and schemas with only a few tables. We have to lock all relations - not just tables, but also indexes, views, etc. Moreover, for planning we need to lock all relations that might be used in the plan, not just those that actually get used in the final plan. It only takes a couple tables with multiple indexes to need more than 16 locks. It was quite common to fill all fast-path slots. As partitioning gets used more widely, with more and more partitions, this limit is trivial to hit, with complex queries easily using hundreds or even thousands of locks. For workloads doing a lot of I/O this is not noticeable, but on large machines with enough RAM to keep the data in memory, the access to the shared lock table may be a serious issue. This patch improves this by increasing the number of fast-path slots from 16 to 1024. The slots remain in PGPROC, and are organized as an array of 16-slot groups (each group being effectively a clone of the original fast-path approach). Instead of accessing this as a big hash table with open addressing, we treat this as a 16-way set associative cache. Each relation (identified by a "relid" OID) is mapped to a particular 16-slot group by calculating a hash h(relid) = ((relid * P) mod N) where P is a hard-coded prime, and N is the number of groups. This is not a great hash function, but it works well enough - the main purpose is to prevent "hot groups" with runs of consecutive OIDs, which might fill some of the fast-path groups. The multiplication by P ensures that. If the OIDs are already spread out, the hash should not group them. The groups are processed by linear search. With only 16 entries this is cheap, and the groups have very good locality. Treating this as a simple hash table with open addressing would not be efficient, especially once the hash table is getting almost full. The usual solution is to grow the table, but for hash tables in shared memory that's not trivial. It would also have worse locality, due to more random access. Luckily, fast-path locking already has a simple solution to deal with a full hash table. The lock can be simply inserted into the shared lock table, just like before. Of course, if this happens too often, that reduces the benefit of fast-path locking. This patch hard-codes the number of groups to 64, which means 1024 fast-path locks. As all the information is still stored in PGPROC, this grows PGPROC by about 4.5kB (from ~840B to ~5kB). This is a trade off exchanging memory for cheaper locking. Ultimately, the number of fast-path slots should not be hard coded, but adjustable based on what the workload does, perhaps using a GUC. That however means it can't be stored in PGPROC directly. --- src/backend/storage/lmgr/lock.c | 118 ++++++++++++++++++++++++++------ src/include/storage/proc.h | 8 ++- 2 files changed, 102 insertions(+), 24 deletions(-) diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index 83b99a98f08..d053ae0c409 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -167,7 +167,7 @@ typedef struct TwoPhaseLockRecord * our locks to the primary lock table, but it can never be lower than the * real value, since only we can acquire locks on our own behalf. */ -static int FastPathLocalUseCount = 0; +static int FastPathLocalUseCounts[FP_LOCK_GROUPS_PER_BACKEND]; /* * Flag to indicate if the relation extension lock is held by this backend. @@ -184,23 +184,53 @@ static int FastPathLocalUseCount = 0; */ static bool IsRelationExtensionLockHeld PG_USED_FOR_ASSERTS_ONLY = false; +/* + * Macros to calculate the group and index for a relation. + * + * The formula is a simple hash function, designed to spread the OIDs a bit, + * so that even contiguous values end up in different groups. In most cases + * there will be gaps anyway, but the multiplication should help a bit. + * + * The selected value (49157) is a prime not too close to 2^k, and it's + * small enough to not cause overflows (in 64-bit). + */ +#define FAST_PATH_LOCK_REL_GROUP(rel) \ + (((uint64) (rel) * 49157) % FP_LOCK_GROUPS_PER_BACKEND) + +/* Calculate index in the whole per-backend array of lock slots. */ +#define FP_LOCK_SLOT_INDEX(group, index) \ + (AssertMacro(((group) >= 0) && ((group) < FP_LOCK_GROUPS_PER_BACKEND)), \ + AssertMacro(((index) >= 0) && ((index) < FP_LOCK_SLOTS_PER_GROUP)), \ + ((group) * FP_LOCK_SLOTS_PER_GROUP + (index))) + +/* + * Given a lock index (into the per-backend array), calculated using the + * FP_LOCK_SLOT_INDEX macro, calculate group and index (within the group). + */ +#define FAST_PATH_LOCK_GROUP(index) \ + (AssertMacro(((index) >= 0) && ((index) < FP_LOCK_SLOTS_PER_BACKEND)), \ + ((index) / FP_LOCK_SLOTS_PER_GROUP)) +#define FAST_PATH_LOCK_INDEX(index) \ + (AssertMacro(((index) >= 0) && ((index) < FP_LOCK_SLOTS_PER_BACKEND)), \ + ((index) % FP_LOCK_SLOTS_PER_GROUP)) + /* Macros for manipulating proc->fpLockBits */ #define FAST_PATH_BITS_PER_SLOT 3 #define FAST_PATH_LOCKNUMBER_OFFSET 1 #define FAST_PATH_MASK ((1 << FAST_PATH_BITS_PER_SLOT) - 1) #define FAST_PATH_GET_BITS(proc, n) \ - (((proc)->fpLockBits >> (FAST_PATH_BITS_PER_SLOT * n)) & FAST_PATH_MASK) + (((proc)->fpLockBits[(n)/16] >> (FAST_PATH_BITS_PER_SLOT * FAST_PATH_LOCK_INDEX(n))) & FAST_PATH_MASK) #define FAST_PATH_BIT_POSITION(n, l) \ (AssertMacro((l) >= FAST_PATH_LOCKNUMBER_OFFSET), \ AssertMacro((l) < FAST_PATH_BITS_PER_SLOT+FAST_PATH_LOCKNUMBER_OFFSET), \ AssertMacro((n) < FP_LOCK_SLOTS_PER_BACKEND), \ - ((l) - FAST_PATH_LOCKNUMBER_OFFSET + FAST_PATH_BITS_PER_SLOT * (n))) + ((l) - FAST_PATH_LOCKNUMBER_OFFSET + FAST_PATH_BITS_PER_SLOT * (FAST_PATH_LOCK_INDEX(n)))) #define FAST_PATH_SET_LOCKMODE(proc, n, l) \ - (proc)->fpLockBits |= UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l) + (proc)->fpLockBits[FAST_PATH_LOCK_GROUP(n)] |= UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l) #define FAST_PATH_CLEAR_LOCKMODE(proc, n, l) \ - (proc)->fpLockBits &= ~(UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l)) + (proc)->fpLockBits[FAST_PATH_LOCK_GROUP(n)] &= ~(UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l)) #define FAST_PATH_CHECK_LOCKMODE(proc, n, l) \ - ((proc)->fpLockBits & (UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l))) + ((proc)->fpLockBits[FAST_PATH_LOCK_GROUP(n)] & (UINT64CONST(1) << FAST_PATH_BIT_POSITION(n, l))) /* * The fast-path lock mechanism is concerned only with relation locks on @@ -926,7 +956,7 @@ LockAcquireExtended(const LOCKTAG *locktag, * for now we don't worry about that case either. */ if (EligibleForRelationFastPath(locktag, lockmode) && - FastPathLocalUseCount < FP_LOCK_SLOTS_PER_BACKEND) + FastPathLocalUseCounts[FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2)] < FP_LOCK_SLOTS_PER_GROUP) { uint32 fasthashcode = FastPathStrongLockHashPartition(hashcode); bool acquired; @@ -1970,6 +2000,7 @@ LockRelease(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock) PROCLOCK *proclock; LWLock *partitionLock; bool wakeupNeeded; + int group; if (lockmethodid <= 0 || lockmethodid >= lengthof(LockMethods)) elog(ERROR, "unrecognized lock method: %d", lockmethodid); @@ -2063,9 +2094,12 @@ LockRelease(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock) */ locallock->lockCleared = false; + /* fast-path group the lock belongs to */ + group = FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2); + /* Attempt fast release of any lock eligible for the fast path. */ if (EligibleForRelationFastPath(locktag, lockmode) && - FastPathLocalUseCount > 0) + FastPathLocalUseCounts[group] > 0) { bool released; @@ -2633,12 +2667,21 @@ LockReassignOwner(LOCALLOCK *locallock, ResourceOwner parent) static bool FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode) { - uint32 f; uint32 unused_slot = FP_LOCK_SLOTS_PER_BACKEND; + uint32 i, + group; + + /* fast-path group the lock belongs to */ + group = FAST_PATH_LOCK_REL_GROUP(relid); /* Scan for existing entry for this relid, remembering empty slot. */ - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + for (i = 0; i < FP_LOCK_SLOTS_PER_GROUP; i++) { + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, i); + if (FAST_PATH_GET_BITS(MyProc, f) == 0) unused_slot = f; else if (MyProc->fpRelId[f] == relid) @@ -2654,7 +2697,7 @@ FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode) { MyProc->fpRelId[unused_slot] = relid; FAST_PATH_SET_LOCKMODE(MyProc, unused_slot, lockmode); - ++FastPathLocalUseCount; + ++FastPathLocalUseCounts[group]; return true; } @@ -2670,12 +2713,21 @@ FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode) static bool FastPathUnGrantRelationLock(Oid relid, LOCKMODE lockmode) { - uint32 f; bool result = false; + uint32 i, + group; - FastPathLocalUseCount = 0; - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + /* fast-path group the lock belongs to */ + group = FAST_PATH_LOCK_REL_GROUP(relid); + + FastPathLocalUseCounts[group] = 0; + for (i = 0; i < FP_LOCK_SLOTS_PER_GROUP; i++) { + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, i); + if (MyProc->fpRelId[f] == relid && FAST_PATH_CHECK_LOCKMODE(MyProc, f, lockmode)) { @@ -2685,7 +2737,7 @@ FastPathUnGrantRelationLock(Oid relid, LOCKMODE lockmode) /* we continue iterating so as to update FastPathLocalUseCount */ } if (FAST_PATH_GET_BITS(MyProc, f) != 0) - ++FastPathLocalUseCount; + ++FastPathLocalUseCounts[group]; } return result; } @@ -2714,7 +2766,8 @@ FastPathTransferRelationLocks(LockMethod lockMethodTable, const LOCKTAG *locktag for (i = 0; i < ProcGlobal->allProcCount; i++) { PGPROC *proc = &ProcGlobal->allProcs[i]; - uint32 f; + uint32 j, + group; LWLockAcquire(&proc->fpInfoLock, LW_EXCLUSIVE); @@ -2739,9 +2792,16 @@ FastPathTransferRelationLocks(LockMethod lockMethodTable, const LOCKTAG *locktag continue; } - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + /* fast-path group the lock belongs to */ + group = FAST_PATH_LOCK_REL_GROUP(relid); + + for (j = 0; j < FP_LOCK_SLOTS_PER_GROUP; j++) { uint32 lockmode; + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, j); /* Look for an allocated slot matching the given relid. */ if (relid != proc->fpRelId[f] || FAST_PATH_GET_BITS(proc, f) == 0) @@ -2793,13 +2853,21 @@ FastPathGetRelationLockEntry(LOCALLOCK *locallock) PROCLOCK *proclock = NULL; LWLock *partitionLock = LockHashPartitionLock(locallock->hashcode); Oid relid = locktag->locktag_field2; - uint32 f; + uint32 i, + group; + + /* fast-path group the lock belongs to */ + group = FAST_PATH_LOCK_REL_GROUP(relid); LWLockAcquire(&MyProc->fpInfoLock, LW_EXCLUSIVE); - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + for (i = 0; i < FP_LOCK_SLOTS_PER_GROUP; i++) { uint32 lockmode; + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, i); /* Look for an allocated slot matching the given relid. */ if (relid != MyProc->fpRelId[f] || FAST_PATH_GET_BITS(MyProc, f) == 0) @@ -2903,6 +2971,10 @@ GetLockConflicts(const LOCKTAG *locktag, LOCKMODE lockmode, int *countp) LWLock *partitionLock; int count = 0; int fast_count = 0; + uint32 group; + + /* fast-path group the lock belongs to */ + group = FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2); if (lockmethodid <= 0 || lockmethodid >= lengthof(LockMethods)) elog(ERROR, "unrecognized lock method: %d", lockmethodid); @@ -2957,7 +3029,7 @@ GetLockConflicts(const LOCKTAG *locktag, LOCKMODE lockmode, int *countp) for (i = 0; i < ProcGlobal->allProcCount; i++) { PGPROC *proc = &ProcGlobal->allProcs[i]; - uint32 f; + uint32 j; /* A backend never blocks itself */ if (proc == MyProc) @@ -2979,9 +3051,13 @@ GetLockConflicts(const LOCKTAG *locktag, LOCKMODE lockmode, int *countp) continue; } - for (f = 0; f < FP_LOCK_SLOTS_PER_BACKEND; f++) + for (j = 0; j < FP_LOCK_SLOTS_PER_GROUP; j++) { uint32 lockmask; + uint32 f; + + /* index into the whole per-backend array */ + f = FP_LOCK_SLOT_INDEX(group, j); /* Look for an allocated slot matching the given relid. */ if (relid != proc->fpRelId[f]) diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index deeb06c9e01..845058da9fa 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -83,8 +83,9 @@ struct XidCache * rather than the main lock table. This eases contention on the lock * manager LWLocks. See storage/lmgr/README for additional details. */ -#define FP_LOCK_SLOTS_PER_BACKEND 16 - +#define FP_LOCK_GROUPS_PER_BACKEND 64 +#define FP_LOCK_SLOTS_PER_GROUP 16 /* don't change */ +#define FP_LOCK_SLOTS_PER_BACKEND (FP_LOCK_SLOTS_PER_GROUP * FP_LOCK_GROUPS_PER_BACKEND) /* * Flags for PGPROC.delayChkptFlags * @@ -292,7 +293,8 @@ struct PGPROC /* Lock manager data, recording fast-path locks taken by this backend. */ LWLock fpInfoLock; /* protects per-backend fast-path state */ - uint64 fpLockBits; /* lock modes held for each fast-path slot */ + uint64 fpLockBits[FP_LOCK_GROUPS_PER_BACKEND]; /* lock modes held for + * each fast-path slot */ Oid fpRelId[FP_LOCK_SLOTS_PER_BACKEND]; /* slots for rel oids */ bool fpVXIDLock; /* are we holding a fast-path VXID lock? */ LocalTransactionId fpLocalTransactionId; /* lxid for fast-path VXID -- 2.46.0 [text/x-patch] v20240912-0002-Set-fast-path-slots-using-max_locks_per_tr.patch (13.5K, ../../[email protected]/3-v20240912-0002-Set-fast-path-slots-using-max_locks_per_tr.patch) download | inline diff: From 1e3be15e39aadc58db4c9be86cfee64f0395dfd4 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Thu, 12 Sep 2024 23:09:50 +0200 Subject: [PATCH v20240912 2/2] Set fast-path slots using max_locks_per_transaction Instead of using a hard-coded value of 64 groups (1024 fast-path slots), determine the value based on max_locks_per_transaction GUC. This size is calculated at startup, before allocating shared memory. The default value of max_locks_per_transaction value is 64, which means 4 groups of fast-path locks. The purpose of the max_locks_per_transaction GUC is to size the shared lock table, but it's the best information about the expected number of locks available. It is often set to an average number of locks needed by a backend, but some backends may need substantially fewer/more locks. This means fast-path capacity calculated from max_locks_per_transaction may not be sufficient for some backends, forcing use of the shared lock table. The assumption is this is not a major issue - there can't be too many of such backends, otherwise the max_locks_per_transaction would need to be higher anyway (resolving the fast-path issue too). If that happens to be a problem, the only solution is to increase the GUC, even if the shared lock table had sufficient capacity. That is not free, because each lock in the shared lock table requires about 500B. With many backends this may be a substantial amount of memory, but then again - that should only happen on machines with plenty of memory. In the future we can consider a separate GUC for the number of fast-path slots, but let's try without one first. An alternative solution might be to size the fast-path arrays for a multiple of max_locks_per_transaction. The cost of adding a fast-path slot is much lower (only ~5B compared to ~500B per entry), so this would be cheaper than increasing max_locks_per_transaction. But it's not clear what multiple of max_locks_per_transaction to use. --- src/backend/bootstrap/bootstrap.c | 2 ++ src/backend/postmaster/postmaster.c | 5 +++ src/backend/storage/lmgr/lock.c | 28 +++++++++++++---- src/backend/storage/lmgr/proc.c | 47 +++++++++++++++++++++++++++++ src/backend/tcop/postgres.c | 3 ++ src/backend/utils/init/postinit.c | 34 +++++++++++++++++++++ src/include/miscadmin.h | 1 + src/include/storage/proc.h | 11 ++++--- 8 files changed, 120 insertions(+), 11 deletions(-) diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c index 7637581a184..ed59dfce893 100644 --- a/src/backend/bootstrap/bootstrap.c +++ b/src/backend/bootstrap/bootstrap.c @@ -309,6 +309,8 @@ BootstrapModeMain(int argc, char *argv[], bool check_only) InitializeMaxBackends(); + InitializeFastPathLocks(); + CreateSharedMemoryAndSemaphores(); /* diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c index 96bc1d1cfed..f4a16595d7f 100644 --- a/src/backend/postmaster/postmaster.c +++ b/src/backend/postmaster/postmaster.c @@ -903,6 +903,11 @@ PostmasterMain(int argc, char *argv[]) */ InitializeMaxBackends(); + /* + * Also calculate the size of the fast-path lock arrays in PGPROC. + */ + InitializeFastPathLocks(); + /* * Give preloaded libraries a chance to request additional shared memory. */ diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index d053ae0c409..505aa52668e 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -166,8 +166,13 @@ typedef struct TwoPhaseLockRecord * might be higher than the real number if another backend has transferred * our locks to the primary lock table, but it can never be lower than the * real value, since only we can acquire locks on our own behalf. + * + * XXX Allocate a static array of the maximum size. We could have a pointer + * and then allocate just the right size to save a couple kB, but that does + * not seem worth the extra complexity of having to initialize it etc. This + * way it gets initialized automaticaly. */ -static int FastPathLocalUseCounts[FP_LOCK_GROUPS_PER_BACKEND]; +static int FastPathLocalUseCounts[FP_LOCK_GROUPS_PER_BACKEND_MAX]; /* * Flag to indicate if the relation extension lock is held by this backend. @@ -184,6 +189,17 @@ static int FastPathLocalUseCounts[FP_LOCK_GROUPS_PER_BACKEND]; */ static bool IsRelationExtensionLockHeld PG_USED_FOR_ASSERTS_ONLY = false; +/* + * Number of fast-path locks per backend - size of the arrays in PGPROC. + * This is set only once during start, before initializing shared memory, + * and remains constant after that. + * + * We set the limit based on max_locks_per_transaction GUC, because that's + * the best information about expected number of locks per backend we have. + * See InitializeFastPathLocks for details. + */ +int FastPathLockGroupsPerBackend = 0; + /* * Macros to calculate the group and index for a relation. * @@ -195,11 +211,11 @@ static bool IsRelationExtensionLockHeld PG_USED_FOR_ASSERTS_ONLY = false; * small enough to not cause overflows (in 64-bit). */ #define FAST_PATH_LOCK_REL_GROUP(rel) \ - (((uint64) (rel) * 49157) % FP_LOCK_GROUPS_PER_BACKEND) + (((uint64) (rel) * 49157) % FastPathLockGroupsPerBackend) /* Calculate index in the whole per-backend array of lock slots. */ #define FP_LOCK_SLOT_INDEX(group, index) \ - (AssertMacro(((group) >= 0) && ((group) < FP_LOCK_GROUPS_PER_BACKEND)), \ + (AssertMacro(((group) >= 0) && ((group) < FastPathLockGroupsPerBackend)), \ AssertMacro(((index) >= 0) && ((index) < FP_LOCK_SLOTS_PER_GROUP)), \ ((group) * FP_LOCK_SLOTS_PER_GROUP + (index))) @@ -2973,9 +2989,6 @@ GetLockConflicts(const LOCKTAG *locktag, LOCKMODE lockmode, int *countp) int fast_count = 0; uint32 group; - /* fast-path group the lock belongs to */ - group = FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2); - if (lockmethodid <= 0 || lockmethodid >= lengthof(LockMethods)) elog(ERROR, "unrecognized lock method: %d", lockmethodid); lockMethodTable = LockMethods[lockmethodid]; @@ -3005,6 +3018,9 @@ GetLockConflicts(const LOCKTAG *locktag, LOCKMODE lockmode, int *countp) partitionLock = LockHashPartitionLock(hashcode); conflictMask = lockMethodTable->conflictTab[lockmode]; + /* fast-path group the lock belongs to */ + group = FAST_PATH_LOCK_REL_GROUP(locktag->locktag_field2); + /* * Fast path locks might not have been entered in the primary lock table. * If the lock we're dealing with could conflict with such a lock, we must diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index ac66da8638f..a91b6f8a6c0 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -103,6 +103,8 @@ ProcGlobalShmemSize(void) Size size = 0; Size TotalProcs = add_size(MaxBackends, add_size(NUM_AUXILIARY_PROCS, max_prepared_xacts)); + Size fpLockBitsSize, + fpRelIdSize; /* ProcGlobal */ size = add_size(size, sizeof(PROC_HDR)); @@ -113,6 +115,18 @@ ProcGlobalShmemSize(void) size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->subxidStates))); size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->statusFlags))); + /* + * fast-path lock arrays + * + * XXX The explicit alignment may not be strictly necessary, as both + * values are already multiples of 8 bytes, which is what MAXALIGN does. + * But better to make that obvious. + */ + fpLockBitsSize = MAXALIGN(FastPathLockGroupsPerBackend * sizeof(uint64)); + fpRelIdSize = MAXALIGN(FastPathLockGroupsPerBackend * sizeof(Oid) * FP_LOCK_SLOTS_PER_GROUP); + + size = add_size(size, mul_size(TotalProcs, (fpLockBitsSize + fpRelIdSize))); + return size; } @@ -162,6 +176,10 @@ InitProcGlobal(void) j; bool found; uint32 TotalProcs = MaxBackends + NUM_AUXILIARY_PROCS + max_prepared_xacts; + char *fpPtr, + *fpEndPtr PG_USED_FOR_ASSERTS_ONLY; + Size fpLockBitsSize, + fpRelIdSize; /* Create the ProcGlobal shared structure */ ProcGlobal = (PROC_HDR *) @@ -211,12 +229,38 @@ InitProcGlobal(void) ProcGlobal->statusFlags = (uint8 *) ShmemAlloc(TotalProcs * sizeof(*ProcGlobal->statusFlags)); MemSet(ProcGlobal->statusFlags, 0, TotalProcs * sizeof(*ProcGlobal->statusFlags)); + /* + * Allocate arrays for fast-path locks. Those are variable-length, so + * can't be included in PGPROC. We allocate a separate piece of shared + * memory and then divide that between backends. + */ + fpLockBitsSize = MAXALIGN(FastPathLockGroupsPerBackend * sizeof(uint64)); + fpRelIdSize = MAXALIGN(FastPathLockGroupsPerBackend * sizeof(Oid) * FP_LOCK_SLOTS_PER_GROUP); + + fpPtr = ShmemAlloc(TotalProcs * (fpLockBitsSize + fpRelIdSize)); + MemSet(fpPtr, 0, TotalProcs * (fpLockBitsSize + fpRelIdSize)); + + /* For asserts checking we did not overflow. */ + fpEndPtr = fpPtr + (TotalProcs * (fpLockBitsSize + fpRelIdSize)); + for (i = 0; i < TotalProcs; i++) { PGPROC *proc = &procs[i]; /* Common initialization for all PGPROCs, regardless of type. */ + /* + * Set the fast-path lock arrays, and move the pointer. We interleave + * the two arrays, to keep at least some locality. + */ + proc->fpLockBits = (uint64 *) fpPtr; + fpPtr += fpLockBitsSize; + + proc->fpRelId = (Oid *) fpPtr; + fpPtr += fpRelIdSize; + + Assert(fpPtr <= fpEndPtr); + /* * Set up per-PGPROC semaphore, latch, and fpInfoLock. Prepared xact * dummy PGPROCs don't need these though - they're never associated @@ -278,6 +322,9 @@ InitProcGlobal(void) pg_atomic_init_u64(&(proc->waitStart), 0); } + /* We expect to consume exactly the expected amount of data. */ + Assert(fpPtr = fpEndPtr); + /* * Save pointers to the blocks of PGPROC structures reserved for auxiliary * processes and prepared transactions. diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 8bc6bea1135..f54ae00abca 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -4166,6 +4166,9 @@ PostgresSingleUserMain(int argc, char *argv[], /* Initialize MaxBackends */ InitializeMaxBackends(); + /* Initialize size of fast-path lock cache. */ + InitializeFastPathLocks(); + /* * Give preloaded libraries a chance to request additional shared memory. */ diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 3b50ce19a2c..1faf756c8d8 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -557,6 +557,40 @@ InitializeMaxBackends(void) MAX_BACKENDS))); } +/* + * Initialize the number of fast-path lock slots in PGPROC. + * + * This must be called after modules have had the chance to alter GUCs in + * shared_preload_libraries and before shared memory size is determined. + * + * The default max_locks_per_xact=64 means 4 groups by default. + * + * We allow anything between 1 and 1024 groups, with the usual power-of-2 + * logic. The 1 is the "old" value before allowing multiple groups, 1024 + * is an arbitrary limit (matching max_locks_per_xact = 16k). Values over + * 1024 are unlikely to be beneficial - we're likely to hit other + * bottlenecks long before that. + */ +void +InitializeFastPathLocks(void) +{ + Assert(FastPathLockGroupsPerBackend == 0); + + /* we need at least one group */ + FastPathLockGroupsPerBackend = 1; + + while (FastPathLockGroupsPerBackend < FP_LOCK_GROUPS_PER_BACKEND_MAX) + { + /* stop once we exceed max_locks_per_xact */ + if (FastPathLockGroupsPerBackend * FP_LOCK_SLOTS_PER_GROUP >= max_locks_per_xact) + break; + + FastPathLockGroupsPerBackend *= 2; + } + + Assert(FastPathLockGroupsPerBackend <= FP_LOCK_GROUPS_PER_BACKEND_MAX); +} + /* * Early initialization of a backend (either standalone or under postmaster). * This happens even before InitPostgres. diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h index 25348e71eb9..e26d108a470 100644 --- a/src/include/miscadmin.h +++ b/src/include/miscadmin.h @@ -475,6 +475,7 @@ extern PGDLLIMPORT ProcessingMode Mode; #define INIT_PG_OVERRIDE_ROLE_LOGIN 0x0004 extern void pg_split_opts(char **argv, int *argcp, const char *optstr); extern void InitializeMaxBackends(void); +extern void InitializeFastPathLocks(void); extern void InitPostgres(const char *in_dbname, Oid dboid, const char *username, Oid useroid, bits32 flags, diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 845058da9fa..0e55c166529 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -83,9 +83,11 @@ struct XidCache * rather than the main lock table. This eases contention on the lock * manager LWLocks. See storage/lmgr/README for additional details. */ -#define FP_LOCK_GROUPS_PER_BACKEND 64 +extern PGDLLIMPORT int FastPathLockGroupsPerBackend; +#define FP_LOCK_GROUPS_PER_BACKEND_MAX 1024 #define FP_LOCK_SLOTS_PER_GROUP 16 /* don't change */ -#define FP_LOCK_SLOTS_PER_BACKEND (FP_LOCK_SLOTS_PER_GROUP * FP_LOCK_GROUPS_PER_BACKEND) +#define FP_LOCK_SLOTS_PER_BACKEND (FP_LOCK_SLOTS_PER_GROUP * FastPathLockGroupsPerBackend) + /* * Flags for PGPROC.delayChkptFlags * @@ -293,9 +295,8 @@ struct PGPROC /* Lock manager data, recording fast-path locks taken by this backend. */ LWLock fpInfoLock; /* protects per-backend fast-path state */ - uint64 fpLockBits[FP_LOCK_GROUPS_PER_BACKEND]; /* lock modes held for - * each fast-path slot */ - Oid fpRelId[FP_LOCK_SLOTS_PER_BACKEND]; /* slots for rel oids */ + uint64 *fpLockBits; /* lock modes held for each fast-path slot */ + Oid *fpRelId; /* slots for rel oids */ bool fpVXIDLock; /* are we holding a fast-path VXID lock? */ LocalTransactionId fpLocalTransactionId; /* lxid for fast-path VXID * lock */ -- 2.46.0 [text/csv] results-1024.csv (13.9K, ../../[email protected]/4-results-1024.csv) download | inline: i5 1 master simple 1 14080.9 14102.110047 i5 1 master simple 4 48395.0 48354.116503 i5 1 master prepared 1 14890.3 14934.195527 i5 1 master prepared 4 51537.3 51522.624839 i5 1 built-in simple 1 14148.3 14142.788300 i5 1 built-in simple 4 48612.6 48612.890117 i5 1 built-in prepared 1 15001.2 14972.190629 i5 1 built-in prepared 4 50884.6 50909.650566 i5 1 built-in-guc simple 1 14044.7 14030.903646 i5 1 built-in-guc simple 4 48315.2 48269.468605 i5 1 built-in-guc prepared 1 14906.4 14910.701579 i5 1 built-in-guc prepared 4 51756.4 51749.293514 i5 2 master simple 1 14022.7 14016.470398 i5 2 master simple 4 48389.0 48375.120104 i5 2 master prepared 1 15006.9 14987.175386 i5 2 master prepared 4 51115.6 51115.335676 i5 2 built-in simple 1 14089.5 14084.849778 i5 2 built-in simple 4 48414.4 48429.565585 i5 2 built-in prepared 1 14928.4 14953.955533 i5 2 built-in prepared 4 51482.3 51469.249951 i5 2 built-in-guc simple 1 14070.0 14026.562135 i5 2 built-in-guc simple 4 48436.1 48420.536506 i5 2 built-in-guc prepared 1 14744.7 14750.031143 i5 2 built-in-guc prepared 4 51234.2 51220.396822 i5 3 master simple 1 14096.8 14077.481886 i5 3 master simple 4 48563.7 48562.921258 i5 3 master prepared 1 14998.7 15008.616332 i5 3 master prepared 4 51424.7 51395.277647 i5 3 built-in simple 1 14172.6 14166.768129 i5 3 built-in simple 4 48605.0 48578.513934 i5 3 built-in prepared 1 15048.8 15034.991405 i5 3 built-in prepared 4 51867.0 51856.876985 i5 3 built-in-guc simple 1 14058.0 14053.123947 i5 3 built-in-guc simple 4 48538.0 48530.856327 i5 3 built-in-guc prepared 1 15010.9 15026.335263 i5 3 built-in-guc prepared 4 51982.5 51989.466475 i5 4 master simple 1 14154.6 14117.359676 i5 4 master simple 4 48570.4 48570.852168 i5 4 master prepared 1 14920.9 14939.788716 i5 4 master prepared 4 51588.4 51578.604824 i5 4 built-in simple 1 14107.9 14109.651452 i5 4 built-in simple 4 48398.6 48400.304251 i5 4 built-in prepared 1 14775.4 14782.665393 i5 4 built-in prepared 4 51495.7 51460.438668 i5 4 built-in-guc simple 1 14002.3 14006.983057 i5 4 built-in-guc simple 4 48477.9 48455.621417 i5 4 built-in-guc prepared 1 14943.6 14956.007087 i5 4 built-in-guc prepared 4 51511.5 51472.586470 i5 5 master simple 1 14230.4 14195.051584 i5 5 master simple 4 48833.8 48826.516769 i5 5 master prepared 1 14988.6 15018.695951 i5 5 master prepared 4 51575.7 51569.855544 i5 5 built-in simple 1 13911.2 13909.242643 i5 5 built-in simple 4 48353.5 48357.250760 i5 5 built-in prepared 1 14787.6 14804.359452 i5 5 built-in prepared 4 51174.1 51159.879369 i5 5 built-in-guc simple 1 14138.8 14143.359702 i5 5 built-in-guc simple 4 48617.0 48601.743282 i5 5 built-in-guc prepared 1 15032.7 15026.830519 i5 5 built-in-guc prepared 4 51741.4 51723.602854 i5 6 master simple 1 14136.2 14106.507155 i5 6 master simple 4 48527.8 48509.882057 i5 6 master prepared 1 14970.6 14977.933552 i5 6 master prepared 4 51645.4 51602.356924 i5 6 built-in simple 1 14103.5 14108.594121 i5 6 built-in simple 4 48716.1 48707.753065 i5 6 built-in prepared 1 15040.9 15014.295825 i5 6 built-in prepared 4 51698.6 51698.996492 i5 6 built-in-guc simple 1 14088.7 14070.366851 i5 6 built-in-guc simple 4 48127.8 48053.241889 i5 6 built-in-guc prepared 1 14981.7 14987.015459 i5 6 built-in-guc prepared 4 51262.3 51272.774438 i5 7 master simple 1 14196.7 14149.519287 i5 7 master simple 4 48386.5 48364.892203 i5 7 master prepared 1 14984.4 15007.463706 i5 7 master prepared 4 51649.9 51647.311500 i5 7 built-in simple 1 14041.2 14030.780130 i5 7 built-in simple 4 48499.4 48477.027169 i5 7 built-in prepared 1 14935.8 14965.537448 i5 7 built-in prepared 4 51331.5 51328.492693 i5 7 built-in-guc simple 1 14070.2 14051.170322 i5 7 built-in-guc simple 4 48248.0 48233.555328 i5 7 built-in-guc prepared 1 15002.2 14984.904159 i5 7 built-in-guc prepared 4 51228.2 51222.004856 i5 8 master simple 1 14105.6 14106.610241 i5 8 master simple 4 48475.7 48465.042264 i5 8 master prepared 1 14927.6 14924.996755 i5 8 master prepared 4 51116.9 51109.524419 i5 8 built-in simple 1 13984.8 13983.338245 i5 8 built-in simple 4 48051.5 48066.170106 i5 8 built-in prepared 1 14767.6 14728.470718 i5 8 built-in prepared 4 50731.6 50725.547013 i5 8 built-in-guc simple 1 14207.0 14172.414090 i5 8 built-in-guc simple 4 48234.4 48238.768602 i5 8 built-in-guc prepared 1 14951.6 14956.407597 i5 8 built-in-guc prepared 4 51819.0 51817.032787 i5 9 master simple 1 14128.9 14158.161197 i5 9 master simple 4 48435.9 48446.319800 i5 9 master prepared 1 15032.3 15070.132100 i5 9 master prepared 4 51724.5 51714.625798 i5 9 built-in simple 1 14096.4 14091.993716 i5 9 built-in simple 4 48806.9 48802.519490 i5 9 built-in prepared 1 14928.5 14951.378328 i5 9 built-in prepared 4 51580.5 51557.651618 i5 9 built-in-guc simple 1 14085.8 14095.357211 i5 9 built-in-guc simple 4 48644.8 48638.348194 i5 9 built-in-guc prepared 1 14909.6 14878.023280 i5 9 built-in-guc prepared 4 50991.1 50947.761509 i5 10 master simple 1 14194.6 14212.629724 i5 10 master simple 4 48848.2 48838.334267 i5 10 master prepared 1 15162.7 15134.028526 i5 10 master prepared 4 51710.8 51722.211996 i5 10 built-in simple 1 14321.5 14295.535844 i5 10 built-in simple 4 48725.4 48735.632100 i5 10 built-in prepared 1 15112.9 15075.799677 i5 10 built-in prepared 4 51336.6 51340.594478 i5 10 built-in-guc simple 1 14016.6 13995.114681 i5 10 built-in-guc simple 4 48085.0 48069.448283 i5 10 built-in-guc prepared 1 15020.8 15006.527436 i5 10 built-in-guc prepared 4 51654.6 51621.983563 xeon 1 master simple 1 12321.0 12113.686352 xeon 1 master simple 4 46061.8 46167.908916 xeon 1 master simple 16 141913.1 142484.077282 xeon 1 master prepared 1 13136.7 13376.467508 xeon 1 master prepared 4 48927.0 49125.699832 xeon 1 master prepared 16 149617.6 149890.942091 xeon 1 built-in simple 1 11698.6 11984.559439 xeon 1 built-in simple 4 46253.3 46405.814755 xeon 1 built-in simple 16 142108.7 142204.159251 xeon 1 built-in prepared 1 13262.6 13278.766170 xeon 1 built-in prepared 4 49373.4 49116.682926 xeon 1 built-in prepared 16 150975.1 150882.901026 xeon 1 built-in-guc simple 1 12411.3 12209.569675 xeon 1 built-in-guc simple 4 46362.8 46292.608241 xeon 1 built-in-guc simple 16 143097.6 143255.213414 xeon 1 built-in-guc prepared 1 12891.4 12904.830056 xeon 1 built-in-guc prepared 4 49037.5 49650.207298 xeon 1 built-in-guc prepared 16 151605.2 151766.492639 xeon 2 master simple 1 12594.0 12569.975147 xeon 2 master simple 4 46095.0 46334.372497 xeon 2 master simple 16 143110.6 143251.654967 xeon 2 master prepared 1 13593.0 13849.216433 xeon 2 master prepared 4 50241.3 50771.943481 xeon 2 master prepared 16 153719.2 153964.165214 xeon 2 built-in simple 1 12581.6 12362.760560 xeon 2 built-in simple 4 46688.0 46920.393032 xeon 2 built-in simple 16 143339.1 143608.465217 xeon 2 built-in prepared 1 13377.2 13614.311099 xeon 2 built-in prepared 4 50243.0 50331.113222 xeon 2 built-in prepared 16 154896.0 155194.083386 xeon 2 built-in-guc simple 1 12403.6 12432.490363 xeon 2 built-in-guc simple 4 45747.6 46349.661315 xeon 2 built-in-guc simple 16 143039.6 143113.625020 xeon 2 built-in-guc prepared 1 13220.9 13345.973115 xeon 2 built-in-guc prepared 4 48563.3 48829.965386 xeon 2 built-in-guc prepared 16 151429.5 151732.394825 xeon 3 master simple 1 12288.7 12298.476516 xeon 3 master simple 4 45880.7 45765.051800 xeon 3 master simple 16 141240.3 141242.056364 xeon 3 master prepared 1 15067.5 14237.524890 xeon 3 master prepared 4 48663.8 48875.792909 xeon 3 master prepared 16 151295.7 151493.587220 xeon 3 built-in simple 1 12428.7 12357.897364 xeon 3 built-in simple 4 46276.9 46283.919558 xeon 3 built-in simple 16 142198.6 142147.885070 xeon 3 built-in prepared 1 13145.0 13129.735118 xeon 3 built-in prepared 4 49243.3 49505.854684 xeon 3 built-in prepared 16 151158.4 151490.885867 xeon 3 built-in-guc simple 1 12876.9 12799.358713 xeon 3 built-in-guc simple 4 45800.3 45910.069255 xeon 3 built-in-guc simple 16 141606.1 141706.124079 xeon 3 built-in-guc prepared 1 13175.5 13159.314980 xeon 3 built-in-guc prepared 4 49724.0 49933.670965 xeon 3 built-in-guc prepared 16 152262.3 152330.391810 xeon 4 master simple 1 12342.7 12273.037282 xeon 4 master simple 4 45735.3 46278.850548 xeon 4 master simple 16 140322.0 140506.519265 xeon 4 master prepared 1 12992.6 12901.871888 xeon 4 master prepared 4 48357.9 48428.230404 xeon 4 master prepared 16 149629.4 149732.767753 xeon 4 built-in simple 1 12587.4 12567.639184 xeon 4 built-in simple 4 46691.9 46699.962096 xeon 4 built-in simple 16 142465.7 142756.256287 xeon 4 built-in prepared 1 13218.2 13461.258227 xeon 4 built-in prepared 4 48914.4 49140.509405 xeon 4 built-in prepared 16 151772.2 151967.354148 xeon 4 built-in-guc simple 1 12253.7 12252.431895 xeon 4 built-in-guc simple 4 44455.3 44672.086158 xeon 4 built-in-guc simple 16 139107.8 139458.570645 xeon 4 built-in-guc prepared 1 12556.8 12806.292236 xeon 4 built-in-guc prepared 4 48509.3 48610.813753 xeon 4 built-in-guc prepared 16 151890.3 152296.476006 xeon 5 master simple 1 12128.5 12170.079024 xeon 5 master simple 4 46143.2 46060.806034 xeon 5 master simple 16 140829.8 141020.431251 xeon 5 master prepared 1 13340.0 13521.018080 xeon 5 master prepared 4 51304.7 51024.242249 xeon 5 master prepared 16 151775.1 151975.264934 xeon 5 built-in simple 1 12477.3 12443.974140 xeon 5 built-in simple 4 45561.2 45457.103949 xeon 5 built-in simple 16 142424.3 142422.955525 xeon 5 built-in prepared 1 13243.0 13348.347912 xeon 5 built-in prepared 4 48858.5 48813.072588 xeon 5 built-in prepared 16 151425.6 151647.701569 xeon 5 built-in-guc simple 1 12437.4 12439.925856 xeon 5 built-in-guc simple 4 45998.8 46430.348219 xeon 5 built-in-guc simple 16 141725.0 141975.006134 xeon 5 built-in-guc prepared 1 13320.2 13693.676450 xeon 5 built-in-guc prepared 4 49468.9 49414.102662 xeon 5 built-in-guc prepared 16 152397.1 152783.559526 xeon 6 master simple 1 12299.8 12274.341755 xeon 6 master simple 4 45690.0 45755.526417 xeon 6 master simple 16 141695.4 141706.926812 xeon 6 master prepared 1 12858.6 13022.596983 xeon 6 master prepared 4 48825.9 48711.051510 xeon 6 master prepared 16 150762.6 151108.027398 xeon 6 built-in simple 1 12128.0 12077.033721 xeon 6 built-in simple 4 45378.6 45568.192478 xeon 6 built-in simple 16 141033.1 141343.963168 xeon 6 built-in prepared 1 12965.8 13414.635061 xeon 6 built-in prepared 4 48654.1 48712.591104 xeon 6 built-in prepared 16 150590.4 150797.051462 xeon 6 built-in-guc simple 1 12348.6 12351.488976 xeon 6 built-in-guc simple 4 46467.8 46387.163084 xeon 6 built-in-guc simple 16 143734.3 143821.513557 xeon 6 built-in-guc prepared 1 13138.0 13788.425471 xeon 6 built-in-guc prepared 4 50398.0 50608.933505 xeon 6 built-in-guc prepared 16 154082.7 153894.209871 xeon 7 master simple 1 12320.0 12799.854420 xeon 7 master simple 4 46040.3 46140.135695 xeon 7 master simple 16 142497.1 142876.561870 xeon 7 master prepared 1 13200.7 13187.011622 xeon 7 master prepared 4 49346.3 50063.218378 xeon 7 master prepared 16 152825.5 152808.682996 xeon 7 built-in simple 1 12213.6 11988.561946 xeon 7 built-in simple 4 45205.1 45191.915439 xeon 7 built-in simple 16 139702.3 139948.950278 xeon 7 built-in prepared 1 12735.5 12944.767576 xeon 7 built-in prepared 4 47940.1 48099.254923 xeon 7 built-in prepared 16 148478.7 148768.219475 xeon 7 built-in-guc simple 1 12479.3 12388.390629 xeon 7 built-in-guc simple 4 45417.9 46094.883898 xeon 7 built-in-guc simple 16 141538.1 141647.778772 xeon 7 built-in-guc prepared 1 12913.6 12959.254618 xeon 7 built-in-guc prepared 4 48440.0 48478.460796 xeon 7 built-in-guc prepared 16 151040.8 151367.118367 xeon 8 master simple 1 12063.3 12062.550554 xeon 8 master simple 4 45022.6 45375.751462 xeon 8 master simple 16 139378.0 139616.512389 xeon 8 master prepared 1 13022.5 13034.608037 xeon 8 master prepared 4 47756.4 48032.141669 xeon 8 master prepared 16 150649.4 150739.169508 xeon 8 built-in simple 1 12636.9 12582.355521 xeon 8 built-in simple 4 46476.0 46441.387849 xeon 8 built-in simple 16 144153.7 144359.367626 xeon 8 built-in prepared 1 13238.2 13394.503058 xeon 8 built-in prepared 4 49636.9 49557.493302 xeon 8 built-in prepared 16 153845.1 154128.451439 xeon 8 built-in-guc simple 1 12515.3 12517.217910 xeon 8 built-in-guc simple 4 47009.4 47126.697586 xeon 8 built-in-guc simple 16 143638.1 143847.444651 xeon 8 built-in-guc prepared 1 13445.2 13700.718829 xeon 8 built-in-guc prepared 4 50346.5 50134.059773 xeon 8 built-in-guc prepared 16 152488.5 152623.934892 xeon 9 master simple 1 12490.8 12454.969962 xeon 9 master simple 4 46260.5 46149.849459 xeon 9 master simple 16 142678.2 142717.472349 xeon 9 master prepared 1 13057.0 13435.252281 xeon 9 master prepared 4 49104.0 49233.865922 xeon 9 master prepared 16 152324.8 152363.495307 xeon 9 built-in simple 1 12547.5 12542.310980 xeon 9 built-in simple 4 46361.9 46449.867649 xeon 9 built-in simple 16 143568.7 143902.189886 xeon 9 built-in prepared 1 13486.4 13646.233568 xeon 9 built-in prepared 4 50489.4 51148.594980 xeon 9 built-in prepared 16 153587.8 154082.377096 xeon 9 built-in-guc simple 1 12200.7 12194.922957 xeon 9 built-in-guc simple 4 46444.3 46608.534778 xeon 9 built-in-guc simple 16 143248.9 143475.950349 xeon 9 built-in-guc prepared 1 13327.9 13536.522876 xeon 9 built-in-guc prepared 4 49420.3 49401.932784 xeon 9 built-in-guc prepared 16 151859.1 152166.819766 xeon 10 master simple 1 12611.0 12548.414937 xeon 10 master simple 4 46396.9 46359.341299 xeon 10 master simple 16 143053.6 143087.924347 xeon 10 master prepared 1 13099.8 13274.694777 xeon 10 master prepared 4 48372.9 48298.822079 xeon 10 master prepared 16 152667.6 152607.979638 xeon 10 built-in simple 1 12431.5 12603.897559 xeon 10 built-in simple 4 45702.3 45837.274823 xeon 10 built-in simple 16 141242.3 141321.486585 xeon 10 built-in prepared 1 13004.4 13017.309945 xeon 10 built-in prepared 4 48725.7 48660.610834 xeon 10 built-in prepared 16 150256.6 150440.615260 xeon 10 built-in-guc simple 1 12051.9 12046.154519 xeon 10 built-in-guc simple 4 45916.9 46139.911810 xeon 10 built-in-guc simple 16 141991.3 141968.515317 xeon 10 built-in-guc prepared 1 13003.6 13005.107633 xeon 10 built-in-guc prepared 4 48240.3 48390.738417 xeon 10 built-in-guc prepared 16 150663.0 151043.965473 [application/x-shellscript] run-lock-test.sh (1.4K, ../../[email protected]/5-run-lock-test.sh) download [text/csv] results-64.csv (13.9K, ../../[email protected]/6-results-64.csv) download | inline: i5 1 master simple 1 13945.4 13954.827492 i5 1 master simple 4 48711.9 48695.172011 i5 1 master prepared 1 14879.3 14881.711975 i5 1 master prepared 4 51852.1 51836.343646 i5 1 built-in simple 1 14096.7 14105.113451 i5 1 built-in simple 4 48051.5 48072.994414 i5 1 built-in prepared 1 15149.0 15109.503629 i5 1 built-in prepared 4 51799.7 51775.818023 i5 1 built-in-guc simple 1 14132.6 14099.290708 i5 1 built-in-guc simple 4 48341.8 48337.845499 i5 1 built-in-guc prepared 1 14991.3 14975.235724 i5 1 built-in-guc prepared 4 51385.2 51344.776611 i5 2 master simple 1 14058.6 14074.818176 i5 2 master simple 4 48833.5 48824.141424 i5 2 master prepared 1 15262.2 15235.351798 i5 2 master prepared 4 52125.2 52109.756349 i5 2 built-in simple 1 14115.5 14140.507070 i5 2 built-in simple 4 48600.6 48592.795749 i5 2 built-in prepared 1 14975.8 15005.122055 i5 2 built-in prepared 4 51855.1 51692.940208 i5 2 built-in-guc simple 1 14016.9 14005.032892 i5 2 built-in-guc simple 4 48132.1 48107.706786 i5 2 built-in-guc prepared 1 14806.7 14825.469178 i5 2 built-in-guc prepared 4 51070.6 51034.867739 i5 3 master simple 1 14011.1 13999.267172 i5 3 master simple 4 48052.7 48047.319336 i5 3 master prepared 1 14966.7 14952.474371 i5 3 master prepared 4 51242.4 51229.490027 i5 3 built-in simple 1 14110.7 14071.999292 i5 3 built-in simple 4 48247.2 48247.999507 i5 3 built-in prepared 1 14809.9 14810.312961 i5 3 built-in prepared 4 51448.7 51448.614480 i5 3 built-in-guc simple 1 14048.7 14031.975903 i5 3 built-in-guc simple 4 48604.6 48609.412321 i5 3 built-in-guc prepared 1 14945.1 14956.656108 i5 3 built-in-guc prepared 4 51254.7 51245.407622 i5 4 master simple 1 14142.2 14145.531400 i5 4 master simple 4 48863.5 48849.608202 i5 4 master prepared 1 15128.6 15116.696271 i5 4 master prepared 4 52158.8 52158.107937 i5 4 built-in simple 1 14062.3 14060.649800 i5 4 built-in simple 4 48637.5 48561.196135 i5 4 built-in prepared 1 14950.6 14976.974040 i5 4 built-in prepared 4 51651.1 51647.112204 i5 4 built-in-guc simple 1 14142.3 14130.775720 i5 4 built-in-guc simple 4 48436.2 48413.349946 i5 4 built-in-guc prepared 1 14996.1 14987.062213 i5 4 built-in-guc prepared 4 51418.0 51430.385529 i5 5 master simple 1 14162.4 14140.067055 i5 5 master simple 4 48716.0 48714.697662 i5 5 master prepared 1 14874.2 14893.955329 i5 5 master prepared 4 51642.8 51638.478382 i5 5 built-in simple 1 14327.9 14315.687315 i5 5 built-in simple 4 48575.2 48582.721717 i5 5 built-in prepared 1 15148.0 15126.616486 i5 5 built-in prepared 4 52061.0 52071.206161 i5 5 built-in-guc simple 1 13969.8 13935.066582 i5 5 built-in-guc simple 4 48706.3 48706.351805 i5 5 built-in-guc prepared 1 15022.9 15031.313980 i5 5 built-in-guc prepared 4 51507.2 51515.781386 i5 6 master simple 1 13993.3 13998.592314 i5 6 master simple 4 48385.4 48383.615463 i5 6 master prepared 1 14939.4 14949.108858 i5 6 master prepared 4 51351.5 51350.713986 i5 6 built-in simple 1 14189.5 14164.803513 i5 6 built-in simple 4 48431.3 48434.261393 i5 6 built-in prepared 1 15186.1 15153.718243 i5 6 built-in prepared 4 51595.1 51581.913299 i5 6 built-in-guc simple 1 13912.9 13921.266247 i5 6 built-in-guc simple 4 48354.3 48375.206008 i5 6 built-in-guc prepared 1 14868.7 14886.172519 i5 6 built-in-guc prepared 4 51305.5 51302.170437 i5 7 master simple 1 14189.8 14175.239368 i5 7 master simple 4 48911.2 48915.235044 i5 7 master prepared 1 15074.6 15073.194243 i5 7 master prepared 4 51887.9 51888.708951 i5 7 built-in simple 1 14133.7 14132.483737 i5 7 built-in simple 4 48609.2 48602.497604 i5 7 built-in prepared 1 14903.9 14908.544561 i5 7 built-in prepared 4 51121.5 50992.647588 i5 7 built-in-guc simple 1 13984.7 13984.830373 i5 7 built-in-guc simple 4 48190.8 48180.152544 i5 7 built-in-guc prepared 1 14815.0 14824.572366 i5 7 built-in-guc prepared 4 51322.2 51310.995523 i5 8 master simple 1 14130.0 14121.928395 i5 8 master simple 4 48255.4 48276.713939 i5 8 master prepared 1 14918.2 14956.383950 i5 8 master prepared 4 51352.3 51346.184069 i5 8 built-in simple 1 14247.1 14216.424742 i5 8 built-in simple 4 48584.3 48581.328213 i5 8 built-in prepared 1 14912.3 14910.866013 i5 8 built-in prepared 4 50929.8 50939.532619 i5 8 built-in-guc simple 1 14076.1 14099.811859 i5 8 built-in-guc simple 4 48720.0 48728.435473 i5 8 built-in-guc prepared 1 15038.2 15048.531254 i5 8 built-in-guc prepared 4 51851.7 51853.129676 i5 9 master simple 1 13837.0 13844.483616 i5 9 master simple 4 48834.0 48845.416664 i5 9 master prepared 1 14972.0 14983.084695 i5 9 master prepared 4 51628.7 51637.828840 i5 9 built-in simple 1 14337.5 14294.360567 i5 9 built-in simple 4 49205.3 49158.109018 i5 9 built-in prepared 1 15122.1 15108.879391 i5 9 built-in prepared 4 52111.8 52108.582691 i5 9 built-in-guc simple 1 14010.9 14003.806280 i5 9 built-in-guc simple 4 48584.5 48539.174916 i5 9 built-in-guc prepared 1 15048.3 15027.601602 i5 9 built-in-guc prepared 4 51848.5 51843.800690 i5 10 master simple 1 14005.4 14009.837132 i5 10 master simple 4 48524.4 48508.366950 i5 10 master prepared 1 15028.8 14992.065530 i5 10 master prepared 4 51142.5 51146.001485 i5 10 built-in simple 1 14010.7 14041.493640 i5 10 built-in simple 4 48587.0 48562.210561 i5 10 built-in prepared 1 14957.6 14930.933240 i5 10 built-in prepared 4 51520.2 51504.606674 i5 10 built-in-guc simple 1 14002.5 14011.410482 i5 10 built-in-guc simple 4 48503.6 48501.130182 i5 10 built-in-guc prepared 1 14974.9 14992.937718 i5 10 built-in-guc prepared 4 51514.8 51507.047113 xeon 1 master simple 1 12457.3 12421.290207 xeon 1 master simple 4 45750.1 45985.332307 xeon 1 master simple 16 142636.6 143058.591697 xeon 1 master prepared 1 13095.7 13121.200090 xeon 1 master prepared 4 48766.5 48738.113161 xeon 1 master prepared 16 151395.1 151532.467550 xeon 1 built-in simple 1 11781.0 11870.365029 xeon 1 built-in simple 4 46707.2 46638.688006 xeon 1 built-in simple 16 143884.4 143688.011971 xeon 1 built-in prepared 1 13314.1 13397.399643 xeon 1 built-in prepared 4 51437.1 51211.051221 xeon 1 built-in prepared 16 152932.6 153579.846747 xeon 1 built-in-guc simple 1 12457.7 12323.612835 xeon 1 built-in-guc simple 4 45892.0 45767.443306 xeon 1 built-in-guc simple 16 141964.1 142164.895920 xeon 1 built-in-guc prepared 1 13356.8 13483.316673 xeon 1 built-in-guc prepared 4 49003.6 49190.649757 xeon 1 built-in-guc prepared 16 151446.4 151689.764377 xeon 2 master simple 1 12302.8 12305.818670 xeon 2 master simple 4 46321.0 46278.719217 xeon 2 master simple 16 143090.9 143378.881540 xeon 2 master prepared 1 13294.1 13455.504052 xeon 2 master prepared 4 49689.9 50305.283336 xeon 2 master prepared 16 152136.7 152457.693553 xeon 2 built-in simple 1 12213.1 12194.735753 xeon 2 built-in simple 4 45806.6 45686.671251 xeon 2 built-in simple 16 141558.5 141896.047974 xeon 2 built-in prepared 1 13077.4 12887.912351 xeon 2 built-in prepared 4 48903.0 48768.000806 xeon 2 built-in prepared 16 150655.6 150946.858640 xeon 2 built-in-guc simple 1 12487.6 12468.641761 xeon 2 built-in-guc simple 4 46317.5 46255.072475 xeon 2 built-in-guc simple 16 143521.1 143673.901901 xeon 2 built-in-guc prepared 1 13045.2 13210.098505 xeon 2 built-in-guc prepared 4 49633.9 49490.136535 xeon 2 built-in-guc prepared 16 151411.6 151514.530714 xeon 3 master simple 1 12316.2 12100.448108 xeon 3 master simple 4 45929.8 45835.654872 xeon 3 master simple 16 141676.4 141600.016349 xeon 3 master prepared 1 13094.3 13186.001217 xeon 3 master prepared 4 49108.5 49101.261003 xeon 3 master prepared 16 149193.8 149416.838439 xeon 3 built-in simple 1 12528.8 12533.810905 xeon 3 built-in simple 4 47031.9 47245.958915 xeon 3 built-in simple 16 142400.9 142435.321930 xeon 3 built-in prepared 1 13162.6 13134.417088 xeon 3 built-in prepared 4 49602.9 49676.601691 xeon 3 built-in prepared 16 153723.5 153708.057905 xeon 3 built-in-guc simple 1 12129.7 12185.239942 xeon 3 built-in-guc simple 4 45800.9 45693.622135 xeon 3 built-in-guc simple 16 142509.6 142465.767079 xeon 3 built-in-guc prepared 1 13227.2 13607.218542 xeon 3 built-in-guc prepared 4 49028.5 49285.997471 xeon 3 built-in-guc prepared 16 151986.1 152478.424781 xeon 4 master simple 1 12333.8 12321.136181 xeon 4 master simple 4 45371.7 45336.617238 xeon 4 master simple 16 141676.7 141718.240437 xeon 4 master prepared 1 13314.6 13780.305538 xeon 4 master prepared 4 49087.5 49302.928072 xeon 4 master prepared 16 150996.5 151294.186193 xeon 4 built-in simple 1 12336.0 12347.926660 xeon 4 built-in simple 4 44687.6 44768.275156 xeon 4 built-in simple 16 140751.7 140824.416046 xeon 4 built-in prepared 1 15233.8 14543.205055 xeon 4 built-in prepared 4 48716.1 48493.310586 xeon 4 built-in prepared 16 150966.4 151106.085111 xeon 4 built-in-guc simple 1 11949.3 12113.777423 xeon 4 built-in-guc simple 4 46232.3 46653.438592 xeon 4 built-in-guc simple 16 144307.6 144449.248169 xeon 4 built-in-guc prepared 1 13352.0 13355.108998 xeon 4 built-in-guc prepared 4 49241.4 49871.496891 xeon 4 built-in-guc prepared 16 152192.8 152428.348687 xeon 5 master simple 1 12143.7 12137.206377 xeon 5 master simple 4 45646.7 45689.169637 xeon 5 master simple 16 140830.4 140964.059442 xeon 5 master prepared 1 13020.8 13061.404227 xeon 5 master prepared 4 48762.8 48880.741440 xeon 5 master prepared 16 148939.3 149180.893231 xeon 5 built-in simple 1 12308.7 12375.878776 xeon 5 built-in simple 4 48135.4 47906.733000 xeon 5 built-in simple 16 142287.0 142433.458336 xeon 5 built-in prepared 1 12651.6 12984.675023 xeon 5 built-in prepared 4 49673.3 49636.365588 xeon 5 built-in prepared 16 153120.1 153425.535499 xeon 5 built-in-guc simple 1 12167.2 12158.948171 xeon 5 built-in-guc simple 4 44843.9 44861.932622 xeon 5 built-in-guc simple 16 139334.8 139418.873059 xeon 5 built-in-guc prepared 1 12762.6 12840.172116 xeon 5 built-in-guc prepared 4 48052.8 47924.203840 xeon 5 built-in-guc prepared 16 149338.3 149466.926784 xeon 6 master simple 1 12241.1 12192.157982 xeon 6 master simple 4 45504.8 45693.083022 xeon 6 master simple 16 140357.7 140600.608013 xeon 6 master prepared 1 13116.9 13118.097681 xeon 6 master prepared 4 49025.9 49256.292629 xeon 6 master prepared 16 150550.5 150911.350990 xeon 6 built-in simple 1 12117.0 12108.009794 xeon 6 built-in simple 4 45597.7 45482.468376 xeon 6 built-in simple 16 141024.5 141171.196029 xeon 6 built-in prepared 1 13093.8 13136.873564 xeon 6 built-in prepared 4 48407.0 48527.049175 xeon 6 built-in prepared 16 148928.5 149220.865379 xeon 6 built-in-guc simple 1 12340.7 12327.756912 xeon 6 built-in-guc simple 4 45639.1 45732.258877 xeon 6 built-in-guc simple 16 142188.2 142427.113552 xeon 6 built-in-guc prepared 1 13074.3 13130.758414 xeon 6 built-in-guc prepared 4 48054.2 47823.130155 xeon 6 built-in-guc prepared 16 151366.0 151468.465086 xeon 7 master simple 1 12458.5 12232.251979 xeon 7 master simple 4 46191.9 46080.753175 xeon 7 master simple 16 141907.8 142220.544197 xeon 7 master prepared 1 13252.3 13220.871104 xeon 7 master prepared 4 49208.6 49182.524739 xeon 7 master prepared 16 150538.2 150778.213068 xeon 7 built-in simple 1 12324.4 12312.734561 xeon 7 built-in simple 4 45571.9 45875.664935 xeon 7 built-in simple 16 143000.3 142999.611306 xeon 7 built-in prepared 1 13399.6 13431.484674 xeon 7 built-in prepared 4 49044.2 48962.880915 xeon 7 built-in prepared 16 152351.2 152374.973553 xeon 7 built-in-guc simple 1 12399.3 12377.823017 xeon 7 built-in-guc simple 4 45771.1 45918.148541 xeon 7 built-in-guc simple 16 141652.0 141589.891651 xeon 7 built-in-guc prepared 1 12806.4 13021.277447 xeon 7 built-in-guc prepared 4 48281.9 48684.758960 xeon 7 built-in-guc prepared 16 149714.0 149723.274031 xeon 8 master simple 1 12294.3 12267.598950 xeon 8 master simple 4 46191.3 46342.282529 xeon 8 master simple 16 141555.7 141828.810606 xeon 8 master prepared 1 13254.4 13204.673608 xeon 8 master prepared 4 49142.2 49378.568856 xeon 8 master prepared 16 151609.9 151928.266387 xeon 8 built-in simple 1 12413.6 12418.544828 xeon 8 built-in simple 4 46309.4 46420.900468 xeon 8 built-in simple 16 143031.0 143077.940248 xeon 8 built-in prepared 1 13369.4 13774.173143 xeon 8 built-in prepared 4 49453.1 49808.404341 xeon 8 built-in prepared 16 152665.6 152766.604680 xeon 8 built-in-guc simple 1 12270.1 12300.554079 xeon 8 built-in-guc simple 4 44816.1 44896.275741 xeon 8 built-in-guc simple 16 141102.9 141277.727653 xeon 8 built-in-guc prepared 1 12853.7 13006.582353 xeon 8 built-in-guc prepared 4 49189.6 50183.986276 xeon 8 built-in-guc prepared 16 151390.5 151689.193537 xeon 9 master simple 1 12358.6 12148.798480 xeon 9 master simple 4 45615.5 45732.570486 xeon 9 master simple 16 140979.9 141155.846623 xeon 9 master prepared 1 13212.6 13295.050385 xeon 9 master prepared 4 49197.9 49359.670203 xeon 9 master prepared 16 151773.4 151696.645348 xeon 9 built-in simple 1 12387.7 12269.813490 xeon 9 built-in simple 4 45420.7 45559.355159 xeon 9 built-in simple 16 141837.4 141882.647996 xeon 9 built-in prepared 1 13218.3 13262.212145 xeon 9 built-in prepared 4 49217.0 49419.717080 xeon 9 built-in prepared 16 152184.0 152161.183441 xeon 9 built-in-guc simple 1 12614.1 12607.549642 xeon 9 built-in-guc simple 4 46348.9 46732.498833 xeon 9 built-in-guc simple 16 142539.9 142833.568163 xeon 9 built-in-guc prepared 1 13127.4 13224.211389 xeon 9 built-in-guc prepared 4 49220.9 49240.426636 xeon 9 built-in-guc prepared 16 151345.9 151606.285005 xeon 10 master simple 1 12446.2 12473.293384 xeon 10 master simple 4 46430.7 46283.409941 xeon 10 master simple 16 143257.9 143243.023035 xeon 10 master prepared 1 13327.4 13420.132901 xeon 10 master prepared 4 49578.8 49413.363053 xeon 10 master prepared 16 151985.1 152466.368236 xeon 10 built-in simple 1 11670.0 11894.832095 xeon 10 built-in simple 4 46441.1 46485.108513 xeon 10 built-in simple 16 141231.1 141444.233496 xeon 10 built-in prepared 1 13175.4 13404.120931 xeon 10 built-in prepared 4 48438.3 48750.527722 xeon 10 built-in prepared 16 151364.4 151808.579775 xeon 10 built-in-guc simple 1 12306.6 12308.366084 xeon 10 built-in-guc simple 4 46403.4 46508.962630 xeon 10 built-in-guc simple 16 142039.9 142192.078044 xeon 10 built-in-guc prepared 1 12561.5 12902.873643 xeon 10 built-in-guc prepared 4 49244.9 49350.436567 xeon 10 built-in-guc prepared 16 151447.2 151412.039990 ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: scalability bottlenecks with (many) partitions (and more) @ 2024-11-20 16:58 Matthias van de Meent <[email protected]> parent: Tomas Vondra <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Matthias van de Meent @ 2024-11-20 16:58 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]> On Wed, 4 Sept 2024 at 17:32, Tomas Vondra <[email protected]> wrote: > > On 9/4/24 16:25, Matthias van de Meent wrote: > > On Tue, 3 Sept 2024 at 18:20, Tomas Vondra <[email protected]> wrote: > >> FWIW the actual cost is somewhat higher, because we seem to need ~400B > >> for every lock (not just the 150B for the LOCK struct). > > > > We do indeed allocate two PROCLOCKs for every LOCK, and allocate those > > inside dynahash tables. That amounts to (152+2*64+3*16=) 328 bytes in > > dynahash elements, and (3 * 8-16) = 24-48 bytes for the dynahash > > buckets/segments, resulting in 352-376 bytes * NLOCKENTS() being > > used[^1]. Does that align with your usage numbers, or are they > > significantly larger? > > > > I see more like ~470B per lock. If I patch CalculateShmemSize to log the > shmem allocated, I get this: > > max_connections=100 max_locks_per_transaction=1000 => 194264001 > max_connections=100 max_locks_per_transaction=2000 => 241756967 > > and (((241756967-194264001)/100/1000)) = 474 > > Could be alignment of structs or something, not sure. NLOCKENTS is calculated based off of MaxBackends, which is the sum of MaxConnections + autovacuum_max_workers + 1 + max_worker_processes + max_wal_senders; which by default add 22 more slots. After adjusting for that, we get 388 bytes /lock, which is approximately in line with the calculation. > >> At least based on a quick experiment. (Seems a bit high, right?). > > > > Yeah, that does seem high, thanks for nerd-sniping me. [...] > > Alltogether that'd save 40 bytes/lock entry on size, and ~35 > > bytes/lock on "safety margin", for a saving of (up to) 19% of our > > current allocation. I'm not sure if these tricks would benefit with > > performance or even be a demerit, apart from smaller structs usually > > being better at fitting better in CPU caches. > > > > Not sure either, but it seems worth exploring. If you do an experimental > patch for the LOCK size reduction, I can get some numbers. It took me some time to get back to this, and a few hours to experiment, but here's that experimental patch. Attached 4 patches, which together reduce the size of the shared lock tables by about 34% on my 64-bit system. 1/4 implements the MAX_LOCKMODES changes to LOCK I mentioned before, saving 16 bytes. 2/4 packs the LOCK struct more tightly, for another 8 bytes saved. 3/4 reduces the PROCLOCK struct size by 8 bytes with a PGPROC* -> ProcNumber substitution, allowing packing with fields previously reduced in size in patch 2/4. 4/4 reduces the size fo the PROCLOCK table by limiting the average number of per-backend locks to max_locks_per_transaction (rather than the current 2*max_locks_per_transaction when getting locks that other backends also requested), and makes the shared lock tables fully pre-allocated. 1-3 together save 11% on the lock tables in 64-bit builds, and 4/4 saves another ~25%, for a total of ~34% on per-lockentry shared memory usage; from ~360 bytes to ~240 bytes. Note that this doesn't include the ~4.5 bytes added per PGPROC entry per mlpxid for fastpath locking; I've ignored those for now. Not implemented, but technically possible: the PROCLOCK table _could_ be further reduced in size by acknowledging that each of that struct is always stored after dynahash HASHELEMENTs, which have 4 bytes of padding on 64-bit systems. By changing PROCLOCKTAG's myProc to ProcNumber, one could pack that field into the padding of the hash element header, reducing the effective size of the hash table's entries by 8 bytes, and thus the total size of the tables by another few %. I don't think that trade-off is worth it though, given the complexity and trickery required to get that to work well. > I'm not sure about the safety margins. 10% sure seems like quite a bit > of memory (it might not have in the past, but as the instances are > growing, that probably changed). I have not yet touched this safety margin. Kind regards, Matthias van de Meent Neon (https://neon.tech) Attachments: [application/octet-stream] v0-0002-Reduce-size-of-LOCK-by-8-more-bytes.patch (13.4K, ../../CAEze2Wgbr_UcMQsj2sJ9bbXtuDs0b0RF=RmpqLPRzGCD=Fn_Mg@mail.gmail.com/2-v0-0002-Reduce-size-of-LOCK-by-8-more-bytes.patch) download | inline diff: From 98d69e37db722dde4cd4dceda1123f6fa0fb8d8b Mon Sep 17 00:00:00 2001 From: Matthias van de Meent <[email protected]> Date: Wed, 20 Nov 2024 03:46:16 +0100 Subject: [PATCH v0 2/4] Reduce size of LOCK by 8 more bytes LOCKMASK will only use bits [1..8], and thus always fits in uint16. By changing the type from int to uint16, and moving the grant/wait masks in LOCK to the padding space of dclist_head, we save 8 bytes on the struct when the binary is compiled for a 64-bit architecture. --- src/include/storage/lock.h | 18 +++++++++-- src/include/storage/lockdefs.h | 2 +- src/backend/storage/lmgr/deadlock.c | 12 ++++---- src/backend/storage/lmgr/lock.c | 48 ++++++++++++++--------------- src/backend/storage/lmgr/proc.c | 8 ++--- 5 files changed, 50 insertions(+), 38 deletions(-) diff --git a/src/include/storage/lock.h b/src/include/storage/lock.h index b2523bf79d..345ded934f 100644 --- a/src/include/storage/lock.h +++ b/src/include/storage/lock.h @@ -289,6 +289,13 @@ typedef struct LOCKTAG (locktag).locktag_type = LOCKTAG_APPLY_TRANSACTION, \ (locktag).locktag_lockmethodid = DEFAULT_LOCKMETHOD) +/* + * On 64-bit architectures there are 4 bytes of padding in dclist_head. We + * reuse those 4 padding bytes to store some values. + */ +#define SIZEOF_PACKED_DCLIST_HEAD \ + (offsetof(dclist_head, count) + sizeof(uint32)) + /* * Per-locked-object lock information: * @@ -313,10 +320,15 @@ typedef struct LOCK LOCKTAG tag; /* unique identifier of lockable object */ /* data */ - LOCKMASK grantMask; /* bitmask for lock types already granted */ - LOCKMASK waitMask; /* bitmask for lock types awaited */ dlist_head procLocks; /* list of PROCLOCK objects assoc. with lock */ - dclist_head waitProcs; /* list of PGPROC objects waiting on lock */ + union { + dclist_head waitProcs; /* list of PGPROC objects waiting on lock */ + struct { + char pad[SIZEOF_PACKED_DCLIST_HEAD]; + LOCKMASK grantMask; /* bitmask for lock types already granted */ + LOCKMASK waitMask; /* bitmask for lock types awaited */ + } masks; + } packed; int requested[MAX_LOCKMODES]; /* counts of requested locks */ int nRequested; /* total of requested[] array */ int granted[MAX_LOCKMODES]; /* counts of granted locks */ diff --git a/src/include/storage/lockdefs.h b/src/include/storage/lockdefs.h index 810b297edf..c75b98960b 100644 --- a/src/include/storage/lockdefs.h +++ b/src/include/storage/lockdefs.h @@ -22,7 +22,7 @@ * mask indicating a set of held or requested lock types (the bit 1<<mode * corresponds to a particular lock mode). */ -typedef int LOCKMASK; +typedef uint16 LOCKMASK; typedef int LOCKMODE; /* diff --git a/src/backend/storage/lmgr/deadlock.c b/src/backend/storage/lmgr/deadlock.c index fcb874d234..72ba141a53 100644 --- a/src/backend/storage/lmgr/deadlock.c +++ b/src/backend/storage/lmgr/deadlock.c @@ -248,7 +248,7 @@ DeadLockCheck(PGPROC *proc) LOCK *lock = waitOrders[i].lock; PGPROC **procs = waitOrders[i].procs; int nProcs = waitOrders[i].nProcs; - dclist_head *waitQueue = &lock->waitProcs; + dclist_head *waitQueue = &lock->packed.waitProcs; Assert(nProcs == dclist_count(waitQueue)); @@ -697,7 +697,7 @@ FindLockCycleRecurseMember(PGPROC *checkProc, dclist_head *waitQueue; /* Use the true lock wait queue order */ - waitQueue = &lock->waitProcs; + waitQueue = &lock->packed.waitProcs; /* * Find the last member of the lock group that is present in the wait @@ -813,8 +813,8 @@ ExpandConstraints(EDGE *constraints, /* No, so allocate a new list */ waitOrders[nWaitOrders].lock = lock; waitOrders[nWaitOrders].procs = waitOrderProcs + nWaitOrderProcs; - waitOrders[nWaitOrders].nProcs = dclist_count(&lock->waitProcs); - nWaitOrderProcs += dclist_count(&lock->waitProcs); + waitOrders[nWaitOrders].nProcs = dclist_count(&lock->packed.waitProcs); + nWaitOrderProcs += dclist_count(&lock->packed.waitProcs); Assert(nWaitOrderProcs <= MaxBackends); /* @@ -861,7 +861,7 @@ TopoSort(LOCK *lock, int nConstraints, PGPROC **ordering) /* output argument */ { - dclist_head *waitQueue = &lock->waitProcs; + dclist_head *waitQueue = &lock->packed.waitProcs; int queue_size = dclist_count(waitQueue); PGPROC *proc; int i, @@ -1049,7 +1049,7 @@ TopoSort(LOCK *lock, static void PrintLockQueue(LOCK *lock, const char *info) { - dclist_head *waitQueue = &lock->waitProcs; + dclist_head *waitQueue = &lock->packed.waitProcs; dlist_iter proc_iter; printf("%s lock %p queue ", info, lock); diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index 9bf6fbf976..65349b1196 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -373,14 +373,14 @@ LOCK_PRINT(const char *where, const LOCK *lock, LOCKMODE type) lock->tag.locktag_field1, lock->tag.locktag_field2, lock->tag.locktag_field3, lock->tag.locktag_field4, lock->tag.locktag_type, lock->tag.locktag_lockmethodid, - lock->grantMask, + lock->packed.masks.grantMask, lock->requested[0], lock->requested[1], lock->requested[2], lock->requested[3], lock->requested[4], lock->requested[5], lock->requested[6], lock->requested[7], lock->nRequested, lock->granted[0], lock->granted[1], lock->granted[2], lock->granted[3], lock->granted[4], lock->granted[5], lock->granted[6], lock->granted[7], lock->nGranted, - dclist_count(&lock->waitProcs), + dclist_count(&lock->packed.waitProcs), LockMethods[LOCK_LOCKMETHOD(*lock)]->lockModeNames[type]); } @@ -768,7 +768,7 @@ LockHasWaiters(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock) /* * Do the checking. */ - if ((lockMethodTable->conflictTab[lockmode] & lock->waitMask) != 0) + if ((lockMethodTable->conflictTab[lockmode] & lock->packed.masks.waitMask) != 0) hasWaiters = true; LWLockRelease(partitionLock); @@ -1085,7 +1085,7 @@ LockAcquireExtended(const LOCKTAG *locktag, * wait queue. Otherwise, check for conflict with already-held locks. * (That's last because most complex check.) */ - if (lockMethodTable->conflictTab[lockmode] & lock->waitMask) + if (lockMethodTable->conflictTab[lockmode] & lock->packed.masks.waitMask) found_conflict = true; else found_conflict = LockCheckConflicts(lockMethodTable, lockmode, @@ -1259,10 +1259,10 @@ SetupLockInTable(LockMethod lockMethodTable, PGPROC *proc, */ if (!found) { - lock->grantMask = 0; - lock->waitMask = 0; + lock->packed.masks.grantMask = 0; + lock->packed.masks.waitMask = 0; dlist_init(&lock->procLocks); - dclist_init(&lock->waitProcs); + dclist_init(&lock->packed.waitProcs); lock->nRequested = 0; lock->nGranted = 0; MemSet(lock->requested, 0, sizeof(int) * MAX_LOCKMODES); @@ -1344,7 +1344,7 @@ SetupLockInTable(LockMethod lockMethodTable, PGPROC *proc, else { PROCLOCK_PRINT("LockAcquire: found", proclock); - Assert((proclock->holdMask & ~lock->grantMask) == 0); + Assert((proclock->holdMask & ~lock->packed.masks.grantMask) == 0); #ifdef CHECK_DEADLOCK_RISK @@ -1497,12 +1497,12 @@ LockCheckConflicts(LockMethod lockMethodTable, * first check for global conflicts: If no locks conflict with my request, * then I get the lock. * - * Checking for conflict: lock->grantMask represents the types of + * Checking for conflict: lock->packed.masks.grantMask represents the types of * currently held locks. conflictTable[lockmode] has a bit set for each * type of lock that conflicts with request. Bitwise compare tells if * there is a conflict. */ - if (!(conflictMask & lock->grantMask)) + if (!(conflictMask & lock->packed.masks.grantMask)) { PROCLOCK_PRINT("LockCheckConflicts: no conflict", proclock); return false; @@ -1615,9 +1615,9 @@ GrantLock(LOCK *lock, PROCLOCK *proclock, LOCKMODE lockmode) lock->nGranted++; lock->granted[lockmode - 1]++; - lock->grantMask |= LOCKBIT_ON(lockmode); + lock->packed.masks.grantMask |= LOCKBIT_ON(lockmode); if (lock->granted[lockmode - 1] == lock->requested[lockmode - 1]) - lock->waitMask &= LOCKBIT_OFF(lockmode); + lock->packed.masks.waitMask &= LOCKBIT_OFF(lockmode); proclock->holdMask |= LOCKBIT_ON(lockmode); LOCK_PRINT("GrantLock", lock, lockmode); Assert((lock->nGranted > 0) && (lock->granted[lockmode - 1] > 0)); @@ -1655,7 +1655,7 @@ UnGrantLock(LOCK *lock, LOCKMODE lockmode, if (lock->granted[lockmode - 1] == 0) { /* change the conflict mask. No more of this lock type. */ - lock->grantMask &= LOCKBIT_OFF(lockmode); + lock->packed.masks.grantMask &= LOCKBIT_OFF(lockmode); } LOCK_PRINT("UnGrantLock: updated", lock, lockmode); @@ -1669,7 +1669,7 @@ UnGrantLock(LOCK *lock, LOCKMODE lockmode, * some waiter, who could now be awakened because he doesn't conflict with * his own locks. */ - if (lockMethodTable->conflictTab[lockmode] & lock->waitMask) + if (lockMethodTable->conflictTab[lockmode] & lock->packed.masks.waitMask) wakeupNeeded = true; /* @@ -1971,11 +1971,11 @@ RemoveFromWaitQueue(PGPROC *proc, uint32 hashcode) Assert(proc->waitStatus == PROC_WAIT_STATUS_WAITING); Assert(proc->links.next != NULL); Assert(waitLock); - Assert(!dclist_is_empty(&waitLock->waitProcs)); + Assert(!dclist_is_empty(&waitLock->packed.waitProcs)); Assert(0 < lockmethodid && lockmethodid < lengthof(LockMethods)); /* Remove proc from lock's wait queue */ - dclist_delete_from_thoroughly(&waitLock->waitProcs, &proc->links); + dclist_delete_from_thoroughly(&waitLock->packed.waitProcs, &proc->links); /* Undo increments of request counts by waiting process */ Assert(waitLock->nRequested > 0); @@ -1985,7 +1985,7 @@ RemoveFromWaitQueue(PGPROC *proc, uint32 hashcode) waitLock->requested[lockmode - 1]--; /* don't forget to clear waitMask bit if appropriate */ if (waitLock->granted[lockmode - 1] == waitLock->requested[lockmode - 1]) - waitLock->waitMask &= LOCKBIT_OFF(lockmode); + waitLock->packed.masks.waitMask &= LOCKBIT_OFF(lockmode); /* Clean up the proc's own state, and pass it the ok/fail signal */ proc->waitLock = NULL; @@ -2459,7 +2459,7 @@ LockReleaseAll(LOCKMETHODID lockmethodid, bool allLocks) Assert(lock->nRequested >= 0); Assert(lock->nGranted >= 0); Assert(lock->nGranted <= lock->nRequested); - Assert((proclock->holdMask & ~lock->grantMask) == 0); + Assert((proclock->holdMask & ~lock->packed.masks.grantMask) == 0); /* * Release the previously-marked lock modes @@ -3605,7 +3605,7 @@ PostPrepare_Locks(TransactionId xid) Assert(lock->nRequested >= 0); Assert(lock->nGranted >= 0); Assert(lock->nGranted <= lock->nRequested); - Assert((proclock->holdMask & ~lock->grantMask) == 0); + Assert((proclock->holdMask & ~lock->packed.masks.grantMask) == 0); /* Ignore it if nothing to release (must be a session lock) */ if (proclock->releaseMask == 0) @@ -4046,7 +4046,7 @@ GetSingleProcBlockerStatusData(PGPROC *blocked_proc, BlockedProcsData *data) } /* Enlarge waiter_pids[] if it's too small to hold all wait queue PIDs */ - waitQueue = &(theLock->waitProcs); + waitQueue = &(theLock->packed.waitProcs); queue_size = dclist_count(waitQueue); if (queue_size > data->maxpids - data->npids) @@ -4328,10 +4328,10 @@ lock_twophase_recover(TransactionId xid, uint16 info, */ if (!found) { - lock->grantMask = 0; - lock->waitMask = 0; + lock->packed.masks.grantMask = 0; + lock->packed.masks.waitMask = 0; dlist_init(&lock->procLocks); - dclist_init(&lock->waitProcs); + dclist_init(&lock->packed.waitProcs); lock->nRequested = 0; lock->nGranted = 0; MemSet(lock->requested, 0, sizeof(int) * MAX_LOCKMODES); @@ -4406,7 +4406,7 @@ lock_twophase_recover(TransactionId xid, uint16 info, else { PROCLOCK_PRINT("lock_twophase_recover: found", proclock); - Assert((proclock->holdMask & ~lock->grantMask) == 0); + Assert((proclock->holdMask & ~lock->packed.masks.grantMask) == 0); } /* diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index 720ef99ee8..a359c0be21 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -1088,7 +1088,7 @@ JoinWaitQueue(LOCALLOCK *locallock, LockMethod lockMethodTable, bool dontWait) PROCLOCK *proclock = locallock->proclock; uint32 hashcode = locallock->hashcode; LWLock *partitionLock PG_USED_FOR_ASSERTS_ONLY = LockHashPartitionLock(hashcode); - dclist_head *waitQueue = &lock->waitProcs; + dclist_head *waitQueue = &lock->packed.waitProcs; PGPROC *insert_before = NULL; LOCKMASK myProcHeldLocks; LOCKMASK myHeldLocks; @@ -1223,7 +1223,7 @@ JoinWaitQueue(LOCALLOCK *locallock, LockMethod lockMethodTable, bool dontWait) else dclist_push_tail(waitQueue, &MyProc->links); - lock->waitMask |= LOCKBIT_ON(lockmode); + lock->packed.masks.waitMask |= LOCKBIT_ON(lockmode); /* Set up wait information in PGPROC object, too */ MyProc->heldLocks = myProcHeldLocks; @@ -1708,7 +1708,7 @@ ProcWakeup(PGPROC *proc, ProcWaitStatus waitStatus) Assert(proc->waitStatus == PROC_WAIT_STATUS_WAITING); /* Remove process from wait queue */ - dclist_delete_from_thoroughly(&proc->waitLock->waitProcs, &proc->links); + dclist_delete_from_thoroughly(&proc->waitLock->packed.waitProcs, &proc->links); /* Clean up process' state and pass it the ok/fail signal */ proc->waitLock = NULL; @@ -1730,7 +1730,7 @@ ProcWakeup(PGPROC *proc, ProcWaitStatus waitStatus) void ProcLockWakeup(LockMethod lockMethodTable, LOCK *lock) { - dclist_head *waitQueue = &lock->waitProcs; + dclist_head *waitQueue = &lock->packed.waitProcs; LOCKMASK aheadRequests = 0; dlist_mutable_iter miter; -- 2.45.2 [application/octet-stream] v0-0001-Reduce-size-of-LOCK-by-16-bytes.patch (14.5K, ../../CAEze2Wgbr_UcMQsj2sJ9bbXtuDs0b0RF=RmpqLPRzGCD=Fn_Mg@mail.gmail.com/3-v0-0001-Reduce-size-of-LOCK-by-16-bytes.patch) download | inline diff: From 1abaf5c17ddb92a14bd20afc0e43ad3fc21a8475 Mon Sep 17 00:00:00 2001 From: Matthias van de Meent <[email protected]> Date: Wed, 20 Nov 2024 03:27:32 +0100 Subject: [PATCH v0 1/4] Reduce size of LOCK by 16 bytes We only ever need 8 lockmodes, rather than 10, as NoLock is never registered, and MaxLockmode is AccessExclusiveLock (8). Also adjust various locations where we assume MAX_LOCKMODES > MaxLockMode. --- src/include/storage/lock.h | 4 +- src/backend/access/common/relation.c | 6 +-- src/backend/access/index/indexam.c | 2 +- src/backend/storage/lmgr/README | 15 +++--- src/backend/storage/lmgr/lock.c | 76 ++++++++++++++++------------ src/backend/utils/adt/lockfuncs.c | 2 +- 6 files changed, 59 insertions(+), 46 deletions(-) diff --git a/src/include/storage/lock.h b/src/include/storage/lock.h index 787f3db06a..b2523bf79d 100644 --- a/src/include/storage/lock.h +++ b/src/include/storage/lock.h @@ -79,11 +79,13 @@ typedef struct (vxid_dst).localTransactionId = (proc).vxid.lxid) /* MAX_LOCKMODES cannot be larger than the # of bits in LOCKMASK */ -#define MAX_LOCKMODES 10 +#define MAX_LOCKMODES MaxLockMode #define LOCKBIT_ON(lockmode) (1 << (lockmode)) #define LOCKBIT_OFF(lockmode) (~(1 << (lockmode))) +#define LOCK_VALID_LOCKMODE(lockmode) \ + ((lockmode) > NoLock && (lockmode) <= MaxLockMode) /* * This data structure defines the locking semantics associated with a diff --git a/src/backend/access/common/relation.c b/src/backend/access/common/relation.c index d8a313a2c9..78e58d13ce 100644 --- a/src/backend/access/common/relation.c +++ b/src/backend/access/common/relation.c @@ -48,7 +48,7 @@ relation_open(Oid relationId, LOCKMODE lockmode) { Relation r; - Assert(lockmode >= NoLock && lockmode < MAX_LOCKMODES); + Assert(lockmode >= NoLock && lockmode <= MAX_LOCKMODES); /* Get the lock before trying to open the relcache entry */ if (lockmode != NoLock) @@ -89,7 +89,7 @@ try_relation_open(Oid relationId, LOCKMODE lockmode) { Relation r; - Assert(lockmode >= NoLock && lockmode < MAX_LOCKMODES); + Assert(lockmode >= NoLock && lockmode <= MAX_LOCKMODES); /* Get the lock first */ if (lockmode != NoLock) @@ -206,7 +206,7 @@ relation_close(Relation relation, LOCKMODE lockmode) { LockRelId relid = relation->rd_lockInfo.lockRelId; - Assert(lockmode >= NoLock && lockmode < MAX_LOCKMODES); + Assert(lockmode >= NoLock && lockmode <= MAX_LOCKMODES); /* The relcache does the real work... */ RelationClose(relation); diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c index 1859be614c..70b9ac120e 100644 --- a/src/backend/access/index/indexam.c +++ b/src/backend/access/index/indexam.c @@ -178,7 +178,7 @@ index_close(Relation relation, LOCKMODE lockmode) { LockRelId relid = relation->rd_lockInfo.lockRelId; - Assert(lockmode >= NoLock && lockmode < MAX_LOCKMODES); + Assert(lockmode >= NoLock && lockmode <= MAX_LOCKMODES); /* The relcache does the real work... */ RelationClose(relation); diff --git a/src/backend/storage/lmgr/README b/src/backend/storage/lmgr/README index 45de0fd2bd..f85a0d6020 100644 --- a/src/backend/storage/lmgr/README +++ b/src/backend/storage/lmgr/README @@ -105,7 +105,7 @@ grantMask - table) to determine if a new lock request will conflict with existing lock types held. Conflicts are determined by bitwise AND operations between the grantMask and the conflict table entry for the requested - lock type. Bit i of grantMask is 1 if and only if granted[i] > 0. + lock type. Bit i of grantMask is 1 if and only if granted[i - 1] > 0. waitMask - This bitmask shows the types of locks being waited for. Bit i of waitMask @@ -133,10 +133,10 @@ nRequested - only in the backend's LOCALLOCK structure.) requested - - Keeps a count of how many locks of each type have been attempted. Only - elements 1 through MAX_LOCKMODES-1 are used as they correspond to the lock - type defined constants. Summing the values of requested[] should come out - equal to nRequested. + Keeps a count of how many locks of each type have been attempted. + Elements 0 through MAX_LOCKMODES (inclusive) are used, and correspond to + lock type definded constants with value elem+1. Summing the values of + requested[] should come out equal to nRequested. nGranted - Keeps count of how many times this lock has been successfully acquired. @@ -145,9 +145,8 @@ nGranted - granted - Keeps count of how many locks of each type are currently held. Once again - only elements 1 through MAX_LOCKMODES-1 are used (0 is not). Also, like - requested[], summing the values of granted[] should total to the value - of nGranted. + only elements 0 through MAX_LOCKMODES are used. Also, like requested[], + summing the values of granted[] should total to the value of nGranted. We should always have 0 <= nGranted <= nRequested, and 0 <= granted[i] <= requested[i] for each i. When all the request counts diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index edc5020c6a..9bf6fbf976 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -367,19 +367,19 @@ LOCK_PRINT(const char *where, const LOCK *lock, LOCKMODE type) if (LOCK_DEBUG_ENABLED(&lock->tag)) elog(LOG, "%s: lock(%p) id(%u,%u,%u,%u,%u,%u) grantMask(%x) " - "req(%d,%d,%d,%d,%d,%d,%d)=%d " - "grant(%d,%d,%d,%d,%d,%d,%d)=%d wait(%d) type(%s)", + "req(%d,%d,%d,%d,%d,%d,%d,%d)=%d " + "grant(%d,%d,%d,%d,%d,%d,%d,%d)=%d wait(%d) type(%s)", where, lock, lock->tag.locktag_field1, lock->tag.locktag_field2, lock->tag.locktag_field3, lock->tag.locktag_field4, lock->tag.locktag_type, lock->tag.locktag_lockmethodid, lock->grantMask, - lock->requested[1], lock->requested[2], lock->requested[3], - lock->requested[4], lock->requested[5], lock->requested[6], - lock->requested[7], lock->nRequested, - lock->granted[1], lock->granted[2], lock->granted[3], - lock->granted[4], lock->granted[5], lock->granted[6], - lock->granted[7], lock->nGranted, + lock->requested[0], lock->requested[1], lock->requested[2], + lock->requested[3], lock->requested[4], lock->requested[5], + lock->requested[6], lock->requested[7], lock->nRequested, + lock->granted[0], lock->granted[1], lock->granted[2], + lock->granted[3], lock->granted[4], lock->granted[5], + lock->granted[6], lock->granted[7], lock->nGranted, dclist_count(&lock->waitProcs), LockMethods[LOCK_LOCKMETHOD(*lock)]->lockModeNames[type]); } @@ -704,6 +704,8 @@ LockHasWaiters(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock) if (lockmode <= 0 || lockmode > lockMethodTable->numLockModes) elog(ERROR, "unrecognized lock mode: %d", lockmode); + Assert(LOCK_VALID_LOCKMODE(lockmode)); + #ifdef LOCK_DEBUG if (LOCK_DEBUG_ENABLED(locktag)) elog(LOG, "LockHasWaiters: lock [%u,%u] %s", @@ -851,6 +853,8 @@ LockAcquireExtended(const LOCKTAG *locktag, if (lockmode <= 0 || lockmode > lockMethodTable->numLockModes) elog(ERROR, "unrecognized lock mode: %d", lockmode); + Assert(LOCK_VALID_LOCKMODE(lockmode)); + if (RecoveryInProgress() && !InRecovery && (locktag->locktag_type == LOCKTAG_OBJECT || locktag->locktag_type == LOCKTAG_RELATION) && @@ -1133,11 +1137,11 @@ LockAcquireExtended(const LOCKTAG *locktag, else PROCLOCK_PRINT("LockAcquire: did not join wait queue", proclock); lock->nRequested--; - lock->requested[lockmode]--; + lock->requested[lockmode - 1]--; LOCK_PRINT("LockAcquire: did not join wait queue", lock, lockmode); Assert((lock->nRequested > 0) && - (lock->requested[lockmode] >= 0)); + (lock->requested[lockmode - 1] >= 0)); Assert(lock->nGranted <= lock->nRequested); LWLockRelease(partitionLock); if (locallock->nLocks == 0) @@ -1237,6 +1241,7 @@ SetupLockInTable(LockMethod lockMethodTable, PGPROC *proc, PROCLOCKTAG proclocktag; uint32 proclock_hashcode; bool found; + Assert(LOCK_VALID_LOCKMODE(lockmode)); /* * Find or create a lock with this tag. @@ -1267,8 +1272,8 @@ SetupLockInTable(LockMethod lockMethodTable, PGPROC *proc, else { LOCK_PRINT("LockAcquire: found", lock, lockmode); - Assert((lock->nRequested >= 0) && (lock->requested[lockmode] >= 0)); - Assert((lock->nGranted >= 0) && (lock->granted[lockmode] >= 0)); + Assert((lock->nRequested >= 0) && (lock->requested[lockmode - 1] >= 0)); + Assert((lock->nGranted >= 0) && (lock->granted[lockmode - 1] >= 0)); Assert(lock->nGranted <= lock->nRequested); } @@ -1385,8 +1390,8 @@ SetupLockInTable(LockMethod lockMethodTable, PGPROC *proc, * The other counts don't increment till we get the lock. */ lock->nRequested++; - lock->requested[lockmode]++; - Assert((lock->nRequested > 0) && (lock->requested[lockmode] > 0)); + lock->requested[lockmode - 1]++; + Assert((lock->nRequested > 0) && (lock->requested[lockmode - 1] > 0)); /* * We shouldn't already hold the desired lock; else locallock table is @@ -1483,7 +1488,7 @@ LockCheckConflicts(LockMethod lockMethodTable, int numLockModes = lockMethodTable->numLockModes; LOCKMASK myLocks; int conflictMask = lockMethodTable->conflictTab[lockmode]; - int conflictsRemaining[MAX_LOCKMODES]; + int conflictsRemaining[MAX_LOCKMODES + 1]; int totalConflictsRemaining = 0; dlist_iter proclock_iter; int i; @@ -1516,7 +1521,7 @@ LockCheckConflicts(LockMethod lockMethodTable, conflictsRemaining[i] = 0; continue; } - conflictsRemaining[i] = lock->granted[i]; + conflictsRemaining[i] = lock->granted[i - 1]; if (myLocks & LOCKBIT_ON(i)) --conflictsRemaining[i]; totalConflictsRemaining += conflictsRemaining[i]; @@ -1606,14 +1611,16 @@ LockCheckConflicts(LockMethod lockMethodTable, void GrantLock(LOCK *lock, PROCLOCK *proclock, LOCKMODE lockmode) { + Assert(LOCK_VALID_LOCKMODE(lockmode)); + lock->nGranted++; - lock->granted[lockmode]++; + lock->granted[lockmode - 1]++; lock->grantMask |= LOCKBIT_ON(lockmode); - if (lock->granted[lockmode] == lock->requested[lockmode]) + if (lock->granted[lockmode - 1] == lock->requested[lockmode - 1]) lock->waitMask &= LOCKBIT_OFF(lockmode); proclock->holdMask |= LOCKBIT_ON(lockmode); LOCK_PRINT("GrantLock", lock, lockmode); - Assert((lock->nGranted > 0) && (lock->granted[lockmode] > 0)); + Assert((lock->nGranted > 0) && (lock->granted[lockmode - 1] > 0)); Assert(lock->nGranted <= lock->nRequested); } @@ -1631,20 +1638,21 @@ UnGrantLock(LOCK *lock, LOCKMODE lockmode, PROCLOCK *proclock, LockMethod lockMethodTable) { bool wakeupNeeded = false; + Assert(LOCK_VALID_LOCKMODE(lockmode)); - Assert((lock->nRequested > 0) && (lock->requested[lockmode] > 0)); - Assert((lock->nGranted > 0) && (lock->granted[lockmode] > 0)); + Assert((lock->nRequested > 0) && (lock->requested[lockmode - 1] > 0)); + Assert((lock->nGranted > 0) && (lock->granted[lockmode - 1] > 0)); Assert(lock->nGranted <= lock->nRequested); /* * fix the general lock stats */ lock->nRequested--; - lock->requested[lockmode]--; + lock->requested[lockmode - 1]--; lock->nGranted--; - lock->granted[lockmode]--; + lock->granted[lockmode - 1]--; - if (lock->granted[lockmode] == 0) + if (lock->granted[lockmode - 1] == 0) { /* change the conflict mask. No more of this lock type. */ lock->grantMask &= LOCKBIT_OFF(lockmode); @@ -1656,7 +1664,7 @@ UnGrantLock(LOCK *lock, LOCKMODE lockmode, * We need only run ProcLockWakeup if the released lock conflicts with at * least one of the lock types requested by waiter(s). Otherwise whatever * conflict made them wait must still exist. NOTE: before MVCC, we could - * skip wakeup if lock->granted[lockmode] was still positive. But that's + * skip wakeup if lock->granted[lockmode - 1] was still positive. But that's * not true anymore, because the remaining granted locks might belong to * some waiter, who could now be awakened because he doesn't conflict with * his own locks. @@ -1973,10 +1981,10 @@ RemoveFromWaitQueue(PGPROC *proc, uint32 hashcode) Assert(waitLock->nRequested > 0); Assert(waitLock->nRequested > proc->waitLock->nGranted); waitLock->nRequested--; - Assert(waitLock->requested[lockmode] > 0); - waitLock->requested[lockmode]--; + Assert(waitLock->requested[lockmode - 1] > 0); + waitLock->requested[lockmode - 1]--; /* don't forget to clear waitMask bit if appropriate */ - if (waitLock->granted[lockmode] == waitLock->requested[lockmode]) + if (waitLock->granted[lockmode - 1] == waitLock->requested[lockmode - 1]) waitLock->waitMask &= LOCKBIT_OFF(lockmode); /* Clean up the proc's own state, and pass it the ok/fail signal */ @@ -2025,6 +2033,8 @@ LockRelease(const LOCKTAG *locktag, LOCKMODE lockmode, bool sessionLock) if (lockmode <= 0 || lockmode > lockMethodTable->numLockModes) elog(ERROR, "unrecognized lock mode: %d", lockmode); + Assert(LOCK_VALID_LOCKMODE(lockmode)); + #ifdef LOCK_DEBUG if (LOCK_DEBUG_ENABLED(locktag)) elog(LOG, "LockRelease: lock [%u,%u] %s", @@ -4311,6 +4321,8 @@ lock_twophase_recover(TransactionId xid, uint16 info, errhint("You might need to increase \"%s\".", "max_locks_per_transaction"))); } + Assert(LOCK_VALID_LOCKMODE(lockmode)); + /* * if it's a new lock object, initialize it */ @@ -4329,8 +4341,8 @@ lock_twophase_recover(TransactionId xid, uint16 info, else { LOCK_PRINT("lock_twophase_recover: found", lock, lockmode); - Assert((lock->nRequested >= 0) && (lock->requested[lockmode] >= 0)); - Assert((lock->nGranted >= 0) && (lock->granted[lockmode] >= 0)); + Assert((lock->nRequested >= 0) && (lock->requested[lockmode - 1] >= 0)); + Assert((lock->nGranted >= 0) && (lock->granted[lockmode - 1] >= 0)); Assert(lock->nGranted <= lock->nRequested); } @@ -4402,8 +4414,8 @@ lock_twophase_recover(TransactionId xid, uint16 info, * requests, whether granted or waiting, so increment those immediately. */ lock->nRequested++; - lock->requested[lockmode]++; - Assert((lock->nRequested > 0) && (lock->requested[lockmode] > 0)); + lock->requested[lockmode - 1]++; + Assert((lock->nRequested > 0) && (lock->requested[lockmode - 1] > 0)); /* * We shouldn't already hold the desired lock. diff --git a/src/backend/utils/adt/lockfuncs.c b/src/backend/utils/adt/lockfuncs.c index e790f856ab..f21f348464 100644 --- a/src/backend/utils/adt/lockfuncs.c +++ b/src/backend/utils/adt/lockfuncs.c @@ -189,7 +189,7 @@ pg_lock_status(PG_FUNCTION_ARGS) granted = false; if (instance->holdMask) { - for (mode = 0; mode < MAX_LOCKMODES; mode++) + for (mode = 1; mode <= MAX_LOCKMODES; mode++) { if (instance->holdMask & LOCKBIT_ON(mode)) { -- 2.45.2 [application/octet-stream] v0-0003-Reduce-size-of-PROCLOCK-by-8-bytes-on-64-bit-syst.patch (4.8K, ../../CAEze2Wgbr_UcMQsj2sJ9bbXtuDs0b0RF=RmpqLPRzGCD=Fn_Mg@mail.gmail.com/4-v0-0003-Reduce-size-of-PROCLOCK-by-8-bytes-on-64-bit-syst.patch) download | inline diff: From f75f69379809f796bd8959d9d7e7d38277cacdd3 Mon Sep 17 00:00:00 2001 From: Matthias van de Meent <[email protected]> Date: Wed, 20 Nov 2024 04:07:28 +0100 Subject: [PATCH v0 3/4] Reduce size of PROCLOCK by 8 bytes on 64-bit systems --- src/include/storage/lock.h | 2 +- src/backend/storage/lmgr/lock.c | 16 +++++++++------- src/backend/storage/lmgr/proc.c | 5 ++++- 3 files changed, 14 insertions(+), 9 deletions(-) diff --git a/src/include/storage/lock.h b/src/include/storage/lock.h index 345ded934f..b1d4f18402 100644 --- a/src/include/storage/lock.h +++ b/src/include/storage/lock.h @@ -386,7 +386,7 @@ typedef struct PROCLOCK PROCLOCKTAG tag; /* unique identifier of proclock object */ /* data */ - PGPROC *groupLeader; /* proc's lock group leader, or proc itself */ + ProcNumber groupLeader; /* proc's lock group leader, or proc itself */ LOCKMASK holdMask; /* bitmask for lock types currently held */ LOCKMASK releaseMask; /* bitmask for lock types to be released */ dlist_node lockLink; /* list link in LOCK's list of proclocks */ diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index 65349b1196..15e1512e75 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -1321,6 +1321,7 @@ SetupLockInTable(LockMethod lockMethodTable, PGPROC *proc, if (!found) { uint32 partition = LockHashPartition(hashcode); + PGPROC *leaderProc; /* * It might seem unsafe to access proclock->groupLeader without a @@ -1332,8 +1333,9 @@ SetupLockInTable(LockMethod lockMethodTable, PGPROC *proc, * lock group leader without first releasing all of its locks (and in * particular the one we are currently transferring). */ - proclock->groupLeader = proc->lockGroupLeader != NULL ? + leaderProc = proc->lockGroupLeader != NULL ? proc->lockGroupLeader : proc; + proclock->groupLeader = GetNumberFromPGProc(leaderProc); proclock->holdMask = 0; proclock->releaseMask = 0; /* Add proclock to appropriate lists */ @@ -1535,7 +1537,7 @@ LockCheckConflicts(LockMethod lockMethodTable, } /* If no group locking, it's definitely a conflict. */ - if (proclock->groupLeader == MyProc && MyProc->lockGroupLeader == NULL) + if (proclock->groupLeader == MyProcNumber && MyProc->lockGroupLeader == NULL) { Assert(proclock->tag.myProc == MyProc); PROCLOCK_PRINT("LockCheckConflicts: conflicting (simple)", @@ -3640,8 +3642,8 @@ PostPrepare_Locks(TransactionId xid) * Update groupLeader pointer to point to the new proc. (We'd * better not be a member of somebody else's lock group!) */ - Assert(proclock->groupLeader == proclock->tag.myProc); - proclock->groupLeader = newproc; + Assert(proclock->groupLeader == GetNumberFromPGProc(proclock->tag.myProc)); + proclock->groupLeader = GetNumberFromPGProc(newproc); /* * Update the proclock. We should not find any existing entry for @@ -3864,7 +3866,7 @@ GetLockStatusData(void) instance->vxid.procNumber = proc->vxid.procNumber; instance->vxid.localTransactionId = proc->vxid.lxid; instance->pid = proc->pid; - instance->leaderPid = proclock->groupLeader->pid; + instance->leaderPid = GetPGProcByNumber(proclock->groupLeader)->pid; instance->fastpath = false; instance->waitStart = (TimestampTz) pg_atomic_read_u64(&proc->waitStart); @@ -4040,7 +4042,7 @@ GetSingleProcBlockerStatusData(PGPROC *blocked_proc, BlockedProcsData *data) instance->vxid.procNumber = proc->vxid.procNumber; instance->vxid.localTransactionId = proc->vxid.lxid; instance->pid = proc->pid; - instance->leaderPid = proclock->groupLeader->pid; + instance->leaderPid = GetPGProcByNumber(proclock->groupLeader)->pid; instance->fastpath = false; data->nlocks++; } @@ -4394,7 +4396,7 @@ lock_twophase_recover(TransactionId xid, uint16 info, if (!found) { Assert(proc->lockGroupLeader == NULL); - proclock->groupLeader = proc; + proclock->groupLeader = GetNumberFromPGProc(proc); proclock->holdMask = 0; proclock->releaseMask = 0; /* Add proclock to appropriate lists */ diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index a359c0be21..43e79e07e2 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -1119,6 +1119,9 @@ JoinWaitQueue(LOCALLOCK *locallock, LockMethod lockMethodTable, bool dontWait) if (leader != NULL) { dlist_iter iter; + ProcNumber leaderNo; + + leaderNo = GetNumberFromPGProc(leader); dlist_foreach(iter, &lock->procLocks) { @@ -1126,7 +1129,7 @@ JoinWaitQueue(LOCALLOCK *locallock, LockMethod lockMethodTable, bool dontWait) otherproclock = dlist_container(PROCLOCK, lockLink, iter.cur); - if (otherproclock->groupLeader == leader) + if (otherproclock->groupLeader == leaderNo) myHeldLocks |= otherproclock->holdMask; } } -- 2.45.2 [application/octet-stream] v0-0004-Reduce-PROCLOCK-hash-table-size.patch (3.1K, ../../CAEze2Wgbr_UcMQsj2sJ9bbXtuDs0b0RF=RmpqLPRzGCD=Fn_Mg@mail.gmail.com/5-v0-0004-Reduce-PROCLOCK-hash-table-size.patch) download | inline diff: From 118cb90d4273f6cf84b98a4f9c9f66325bd827e2 Mon Sep 17 00:00:00 2001 From: Matthias van de Meent <[email protected]> Date: Wed, 20 Nov 2024 17:31:26 +0100 Subject: [PATCH v0 4/4] Reduce PROCLOCK hash table size This reduces the memory usage of the heavyweight lock mechanism by 25%. Because the main LOCK table is already sized for max_locks_per_transaction for every backend, further allocation of more PROCLOCK entries doesn't make sense, as those would let the average number of locked objects per backend to increase past max_locks_per_transaction. --- src/backend/storage/lmgr/lock.c | 29 ++++++++++++++++++++--------- 1 file changed, 20 insertions(+), 9 deletions(-) diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index 15e1512e75..bd3e4d0f20 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -438,16 +438,18 @@ void LockManagerShmemInit(void) { HASHCTL info; - long init_table_size, - max_table_size; + long max_table_size; bool found; + Size allocated; + char *start; + char *end; + start = ShmemAllocNoError(0); /* * Compute init/max size to request for lock hashtables. Note these * calculations must agree with LockManagerShmemSize! */ max_table_size = NLOCKENTS(); - init_table_size = max_table_size / 2; /* * Allocate hash table for LOCK structs. This stores per-locked-object @@ -458,14 +460,17 @@ LockManagerShmemInit(void) info.num_partitions = NUM_LOCK_PARTITIONS; LockMethodLockHash = ShmemInitHash("LOCK hash", - init_table_size, + max_table_size, max_table_size, &info, HASH_ELEM | HASH_BLOBS | HASH_PARTITION); - /* Assume an average of 2 holders per lock */ - max_table_size *= 2; - init_table_size *= 2; + /* + * Assume every proc has max_locks_per_transaction locks. We don't + * need more PROCLOCK entries after that, because we can't acquire more + * locks after that. This is also more consistent with the advertised + * behaviour of max_locks_per_transaction. + */ /* * Allocate hash table for PROCLOCK structs. This stores @@ -477,7 +482,7 @@ LockManagerShmemInit(void) info.num_partitions = NUM_LOCK_PARTITIONS; LockMethodProcLockHash = ShmemInitHash("PROCLOCK hash", - init_table_size, + max_table_size, max_table_size, &info, HASH_ELEM | HASH_FUNCTION | HASH_PARTITION); @@ -490,6 +495,13 @@ LockManagerShmemInit(void) sizeof(FastPathStrongRelationLockData), &found); if (!found) SpinLockInit(&FastPathStrongRelationLocks->mutex); + end = ShmemAllocNoError(0); + + allocated = end - start; + elog(LOG, "Lock ShMem: Allocated %lu for NLOCKENTS=%ld (= %d mlpxid * (%d m_c + %d m_p_xids))", + (unsigned long) allocated, max_table_size, + max_locks_per_xact, MaxBackends, max_prepared_xacts + ); } /* @@ -3682,7 +3694,6 @@ LockManagerShmemSize(void) size = add_size(size, hash_estimate_size(max_table_size, sizeof(LOCK))); /* proclock hash table */ - max_table_size *= 2; size = add_size(size, hash_estimate_size(max_table_size, sizeof(PROCLOCK))); /* -- 2.45.2 ^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2024-11-20 16:58 UTC | newest] Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-06-11 10:25 [PATCH v4 2/2] Add deform_counter to pg_stat_statements Dmitrii Dolgov <[email protected]> 2024-09-03 16:19 Re: scalability bottlenecks with (many) partitions (and more) Tomas Vondra <[email protected]> 2024-09-04 09:29 ` Re: scalability bottlenecks with (many) partitions (and more) Jakub Wartak <[email protected]> 2024-09-04 11:15 ` Re: scalability bottlenecks with (many) partitions (and more) Tomas Vondra <[email protected]> 2024-09-05 17:33 ` Re: scalability bottlenecks with (many) partitions (and more) Tomas Vondra <[email protected]> 2024-09-06 11:56 ` Re: scalability bottlenecks with (many) partitions (and more) Jakub Wartak <[email protected]> 2024-09-04 14:25 ` Re: scalability bottlenecks with (many) partitions (and more) Matthias van de Meent <[email protected]> 2024-09-04 15:32 ` Re: scalability bottlenecks with (many) partitions (and more) Tomas Vondra <[email protected]> 2024-11-20 16:58 ` Re: scalability bottlenecks with (many) partitions (and more) Matthias van de Meent <[email protected]> 2024-09-05 16:25 ` Re: scalability bottlenecks with (many) partitions (and more) Robert Haas <[email protected]> 2024-09-05 17:21 ` Re: scalability bottlenecks with (many) partitions (and more) Tomas Vondra <[email protected]> 2024-09-12 21:40 ` Re: scalability bottlenecks with (many) partitions (and more) Tomas Vondra <[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