public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v9 1/7] Row pattern recognition patch for raw parser.
12+ messages / 4 participants
[nested] [flat]
* [PATCH v9 1/7] Row pattern recognition patch for raw parser.
@ 2023-10-04 05:51 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Tatsuo Ishii @ 2023-10-04 05:51 UTC (permalink / raw)
---
src/backend/parser/gram.y | 222 ++++++++++++++++++++++++++++++--
src/include/nodes/parsenodes.h | 56 ++++++++
src/include/parser/kwlist.h | 8 ++
src/include/parser/parse_node.h | 1 +
4 files changed, 273 insertions(+), 14 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e56cbe77cb..730c51bc87 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -251,6 +251,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
DefElem *defelt;
SortBy *sortby;
WindowDef *windef;
+ RPCommonSyntax *rpcom;
+ RPSubsetItem *rpsubset;
JoinExpr *jexpr;
IndexElem *ielem;
StatsElem *selem;
@@ -278,6 +280,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
MergeWhenClause *mergewhen;
struct KeyActions *keyactions;
struct KeyAction *keyaction;
+ RPSkipTo skipto;
}
%type <node> stmt toplevel_stmt schema_stmt routine_body_stmt
@@ -453,8 +456,12 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TriggerTransitions TriggerReferencing
vacuum_relation_list opt_vacuum_relation_list
drop_option_list pub_obj_list
-
-%type <node> opt_routine_body
+ row_pattern_measure_list row_pattern_definition_list
+ opt_row_pattern_subset_clause
+ row_pattern_subset_list row_pattern_subset_rhs
+ row_pattern
+%type <rpsubset> row_pattern_subset_item
+%type <node> opt_routine_body row_pattern_term
%type <groupclause> group_clause
%type <list> group_by_list
%type <node> group_by_item empty_grouping_set rollup_clause cube_clause
@@ -551,6 +558,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <range> relation_expr_opt_alias
%type <node> tablesample_clause opt_repeatable_clause
%type <target> target_el set_target insert_column_item
+ row_pattern_measure_item row_pattern_definition
+%type <skipto> first_or_last
%type <str> generic_option_name
%type <node> generic_option_arg
@@ -633,6 +642,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> window_clause window_definition_list opt_partition_clause
%type <windef> window_definition over_clause window_specification
opt_frame_clause frame_extent frame_bound
+%type <rpcom> opt_row_pattern_common_syntax opt_row_pattern_skip_to
+%type <boolean> opt_row_pattern_initial_or_seek
+%type <list> opt_row_pattern_measures
%type <ival> opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
@@ -659,7 +671,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_object_constructor_null_clause_opt
json_array_constructor_null_clause_opt
-
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
* They must be listed first so that their numeric codes do not depend on
@@ -702,7 +713,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
- DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC
+ DEFERRABLE DEFERRED DEFINE DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC
DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P
DOUBLE_P DROP
@@ -718,7 +729,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
HANDLER HAVING HEADER_P HOLD HOUR_P
IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
- INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
+ INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIAL INITIALLY INLINE_P
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -731,7 +742,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED
- MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE METHOD
+ MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MEASURES MERGE METHOD
MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NFC NFD NFKC NFKD NO NONE
@@ -743,8 +754,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
- PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD
- PLACING PLANS POLICY
+ PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PAST
+ PATTERN_P PERMUTE PLACING PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -755,12 +766,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
- SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
+ SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SEEK SELECT
SEQUENCE SEQUENCES
+
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
- SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
+ SUBSCRIPTION SUBSET SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
@@ -853,6 +865,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
*/
%nonassoc UNBOUNDED /* ideally would have same precedence as IDENT */
%nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
+%nonassoc MEASURES AFTER INITIAL SEEK PATTERN_P
%left Op OPERATOR /* multi-character ops and user-defined operators */
%left '+' '-'
%left '*' '/' '%'
@@ -15892,7 +15905,8 @@ over_clause: OVER window_specification
;
window_specification: '(' opt_existing_window_name opt_partition_clause
- opt_sort_clause opt_frame_clause ')'
+ opt_sort_clause opt_row_pattern_measures opt_frame_clause
+ opt_row_pattern_common_syntax ')'
{
WindowDef *n = makeNode(WindowDef);
@@ -15900,10 +15914,12 @@ window_specification: '(' opt_existing_window_name opt_partition_clause
n->refname = $2;
n->partitionClause = $3;
n->orderClause = $4;
+ n->rowPatternMeasures = $5;
/* copy relevant fields of opt_frame_clause */
- n->frameOptions = $5->frameOptions;
- n->startOffset = $5->startOffset;
- n->endOffset = $5->endOffset;
+ n->frameOptions = $6->frameOptions;
+ n->startOffset = $6->startOffset;
+ n->endOffset = $6->endOffset;
+ n->rpCommonSyntax = $7;
n->location = @1;
$$ = n;
}
@@ -15927,6 +15943,31 @@ opt_partition_clause: PARTITION BY expr_list { $$ = $3; }
| /*EMPTY*/ { $$ = NIL; }
;
+/*
+ * ROW PATTERN_P MEASURES
+ */
+opt_row_pattern_measures: MEASURES row_pattern_measure_list { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
+row_pattern_measure_list:
+ row_pattern_measure_item
+ { $$ = list_make1($1); }
+ | row_pattern_measure_list ',' row_pattern_measure_item
+ { $$ = lappend($1, $3); }
+ ;
+
+row_pattern_measure_item:
+ a_expr AS ColLabel
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = $3;
+ $$->indirection = NIL;
+ $$->val = (Node *) $1;
+ $$->location = @1;
+ }
+ ;
+
/*
* For frame clauses, we return a WindowDef, but only some fields are used:
* frameOptions, startOffset, and endOffset.
@@ -16086,6 +16127,143 @@ opt_window_exclusion_clause:
| /*EMPTY*/ { $$ = 0; }
;
+opt_row_pattern_common_syntax:
+opt_row_pattern_skip_to opt_row_pattern_initial_or_seek
+ PATTERN_P '(' row_pattern ')'
+ opt_row_pattern_subset_clause
+ DEFINE row_pattern_definition_list
+ {
+ RPCommonSyntax *n = makeNode(RPCommonSyntax);
+ n->rpSkipTo = $1->rpSkipTo;
+ n->rpSkipVariable = $1->rpSkipVariable;
+ n->initial = $2;
+ n->rpPatterns = $5;
+ n->rpSubsetClause = $7;
+ n->rpDefs = $9;
+ $$ = n;
+ }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
+opt_row_pattern_skip_to:
+ AFTER MATCH SKIP TO NEXT ROW
+ {
+ RPCommonSyntax *n = makeNode(RPCommonSyntax);
+ n->rpSkipTo = ST_NEXT_ROW;
+ n->rpSkipVariable = NULL;
+ $$ = n;
+ }
+ | AFTER MATCH SKIP PAST LAST_P ROW
+ {
+ RPCommonSyntax *n = makeNode(RPCommonSyntax);
+ n->rpSkipTo = ST_PAST_LAST_ROW;
+ n->rpSkipVariable = NULL;
+ $$ = n;
+ }
+ | AFTER MATCH SKIP TO first_or_last ColId
+ {
+ RPCommonSyntax *n = makeNode(RPCommonSyntax);
+ n->rpSkipTo = $5;
+ n->rpSkipVariable = $6;
+ $$ = n;
+ }
+/*
+ | AFTER MATCH SKIP TO LAST_P ColId %prec LAST_P
+ {
+ RPCommonSyntax *n = makeNode(RPCommonSyntax);
+ n->rpSkipTo = ST_LAST_VARIABLE;
+ n->rpSkipVariable = $6;
+ $$ = n;
+ }
+ | AFTER MATCH SKIP TO ColId
+ {
+ RPCommonSyntax *n = makeNode(RPCommonSyntax);
+ n->rpSkipTo = ST_VARIABLE;
+ n->rpSkipVariable = $5;
+ $$ = n;
+ }
+*/
+ | /*EMPTY*/
+ {
+ RPCommonSyntax *n = makeNode(RPCommonSyntax);
+ /* temporary set default to ST_NEXT_ROW */
+ n->rpSkipTo = ST_PAST_LAST_ROW;
+ n->rpSkipVariable = NULL;
+ $$ = n;
+ }
+ ;
+
+first_or_last:
+ FIRST_P { $$ = ST_FIRST_VARIABLE; }
+ | LAST_P { $$ = ST_LAST_VARIABLE; }
+ ;
+
+opt_row_pattern_initial_or_seek:
+ INITIAL { $$ = true; }
+ | SEEK
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("SEEK is not supported"),
+ errhint("Use INITIAL."),
+ parser_errposition(@1)));
+ }
+ | /*EMPTY*/ { $$ = true; }
+ ;
+
+row_pattern:
+ row_pattern_term { $$ = list_make1($1); }
+ | row_pattern row_pattern_term { $$ = lappend($1, $2); }
+ ;
+
+row_pattern_term:
+ ColId { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "", (Node *)makeString($1), NULL, @1); }
+ | ColId '*' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "*", (Node *)makeString($1), NULL, @1); }
+ | ColId '+' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "+", (Node *)makeString($1), NULL, @1); }
+ | ColId '?' { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "?", (Node *)makeString($1), NULL, @1); }
+ ;
+
+opt_row_pattern_subset_clause:
+ SUBSET row_pattern_subset_list { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
+row_pattern_subset_list:
+ row_pattern_subset_item { $$ = list_make1($1); }
+ | row_pattern_subset_list ',' row_pattern_subset_item { $$ = lappend($1, $3); }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
+row_pattern_subset_item: ColId '=' '(' row_pattern_subset_rhs ')'
+ {
+ RPSubsetItem *n = makeNode(RPSubsetItem);
+ n->name = $1;
+ n->rhsVariable = $4;
+ $$ = n;
+ }
+ ;
+
+row_pattern_subset_rhs:
+ ColId { $$ = list_make1(makeStringConst($1, @1)); }
+ | row_pattern_subset_rhs ',' ColId { $$ = lappend($1, makeStringConst($3, @1)); }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
+row_pattern_definition_list:
+ row_pattern_definition { $$ = list_make1($1); }
+ | row_pattern_definition_list ',' row_pattern_definition { $$ = lappend($1, $3); }
+ ;
+
+row_pattern_definition:
+ ColId AS a_expr
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = $1;
+ $$->indirection = NIL;
+ $$->val = (Node *) $3;
+ $$->location = @1;
+ }
+ ;
/*
* Supporting nonterminals for expressions.
@@ -17181,6 +17359,7 @@ unreserved_keyword:
| INDEXES
| INHERIT
| INHERITS
+ | INITIAL
| INLINE_P
| INPUT_P
| INSENSITIVE
@@ -17208,6 +17387,7 @@ unreserved_keyword:
| MATCHED
| MATERIALIZED
| MAXVALUE
+ | MEASURES
| MERGE
| METHOD
| MINUTE_P
@@ -17250,6 +17430,9 @@ unreserved_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PAST
+ | PATTERN_P
+ | PERMUTE
| PLANS
| POLICY
| PRECEDING
@@ -17300,6 +17483,7 @@ unreserved_keyword:
| SEARCH
| SECOND_P
| SECURITY
+ | SEEK
| SEQUENCE
| SEQUENCES
| SERIALIZABLE
@@ -17325,6 +17509,7 @@ unreserved_keyword:
| STRICT_P
| STRIP_P
| SUBSCRIPTION
+ | SUBSET
| SUPPORT
| SYSID
| SYSTEM_P
@@ -17512,6 +17697,7 @@ reserved_keyword:
| CURRENT_USER
| DEFAULT
| DEFERRABLE
+ | DEFINE
| DESC
| DISTINCT
| DO
@@ -17674,6 +17860,7 @@ bare_label_keyword:
| DEFAULTS
| DEFERRABLE
| DEFERRED
+ | DEFINE
| DEFINER
| DELETE_P
| DELIMITER
@@ -17749,6 +17936,7 @@ bare_label_keyword:
| INDEXES
| INHERIT
| INHERITS
+ | INITIAL
| INITIALLY
| INLINE_P
| INNER_P
@@ -17798,6 +17986,7 @@ bare_label_keyword:
| MATCHED
| MATERIALIZED
| MAXVALUE
+ | MEASURES
| MERGE
| METHOD
| MINVALUE
@@ -17851,6 +18040,9 @@ bare_label_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PAST
+ | PATTERN_P
+ | PERMUTE
| PLACING
| PLANS
| POLICY
@@ -17907,6 +18099,7 @@ bare_label_keyword:
| SCROLL
| SEARCH
| SECURITY
+ | SEEK
| SELECT
| SEQUENCE
| SEQUENCES
@@ -17938,6 +18131,7 @@ bare_label_keyword:
| STRICT_P
| STRIP_P
| SUBSCRIPTION
+ | SUBSET
| SUBSTRING
| SUPPORT
| SYMMETRIC
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f637937cd2..31b04d6919 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -547,6 +547,44 @@ typedef struct SortBy
int location; /* operator location, or -1 if none/unknown */
} SortBy;
+/*
+ * AFTER MATCH row pattern skip to types in row pattern common syntax
+ */
+typedef enum RPSkipTo
+{
+ ST_NONE, /* AFTER MATCH omitted */
+ ST_NEXT_ROW, /* SKIP TO NEXT ROW */
+ ST_PAST_LAST_ROW, /* SKIP TO PAST LAST ROW */
+ ST_FIRST_VARIABLE, /* SKIP TO FIRST variable name */
+ ST_LAST_VARIABLE, /* SKIP TO LAST variable name */
+ ST_VARIABLE /* SKIP TO variable name */
+} RPSkipTo;
+
+/*
+ * Row Pattern SUBSET clause item
+ */
+typedef struct RPSubsetItem
+{
+ NodeTag type;
+ char *name; /* Row Pattern SUBSET clause variable name */
+ List *rhsVariable; /* Row Pattern SUBSET rhs variables (list of char *string) */
+} RPSubsetItem;
+
+/*
+ * RowPatternCommonSyntax - raw representation of row pattern common syntax
+ *
+ */
+typedef struct RPCommonSyntax
+{
+ NodeTag type;
+ RPSkipTo rpSkipTo; /* Row Pattern AFTER MATCH SKIP type */
+ char *rpSkipVariable; /* Row Pattern Skip To variable name, if any */
+ bool initial; /* true if <row pattern initial or seek> is initial */
+ List *rpPatterns; /* PATTERN variables (list of A_Expr) */
+ List *rpSubsetClause; /* row pattern subset clause (list of RPSubsetItem), if any */
+ List *rpDefs; /* row pattern definitions clause (list of ResTarget) */
+} RPCommonSyntax;
+
/*
* WindowDef - raw representation of WINDOW and OVER clauses
*
@@ -562,6 +600,8 @@ typedef struct WindowDef
char *refname; /* referenced window name, if any */
List *partitionClause; /* PARTITION BY expression list */
List *orderClause; /* ORDER BY (list of SortBy) */
+ List *rowPatternMeasures; /* row pattern measures (list of ResTarget) */
+ RPCommonSyntax *rpCommonSyntax; /* row pattern common syntax */
int frameOptions; /* frame_clause options, see below */
Node *startOffset; /* expression for starting bound, if any */
Node *endOffset; /* expression for ending bound, if any */
@@ -1483,6 +1523,11 @@ typedef struct GroupingSet
* the orderClause might or might not be copied (see copiedOrder); the framing
* options are never copied, per spec.
*
+ * "defineClause" is Row Pattern Recognition DEFINE clause (list of
+ * TargetEntry). TargetEntry.resname represents row pattern definition
+ * variable name. "patternVariable" and "patternRegexp" represents PATTERN
+ * clause.
+ *
* The information relevant for the query jumbling is the partition clause
* type and its bounds.
*/
@@ -1514,6 +1559,17 @@ typedef struct WindowClause
Index winref; /* ID referenced by window functions */
/* did we copy orderClause from refname? */
bool copiedOrder pg_node_attr(query_jumble_ignore);
+ /* Row Pattern AFTER MACH SKIP clause */
+ RPSkipTo rpSkipTo; /* Row Pattern Skip To type */
+ bool initial; /* true if <row pattern initial or seek> is initial */
+ /* Row Pattern DEFINE clause (list of TargetEntry) */
+ List *defineClause;
+ /* Row Pattern DEFINE variable initial names (list of String) */
+ List *defineInitial;
+ /* Row Pattern PATTERN variable name (list of String) */
+ List *patternVariable;
+ /* Row Pattern PATTERN regular expression quantifier ('+' or ''. list of String) */
+ List *patternRegexp;
} WindowClause;
/*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 5984dcfa4b..2804333b53 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -128,6 +128,7 @@ PG_KEYWORD("default", DEFAULT, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("defaults", DEFAULTS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("deferrable", DEFERRABLE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("deferred", DEFERRED, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("define", DEFINE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("definer", DEFINER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("delete", DELETE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("delimiter", DELIMITER, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -212,6 +213,7 @@ PG_KEYWORD("index", INDEX, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("indexes", INDEXES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("inherit", INHERIT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("inherits", INHERITS, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("initial", INITIAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("initially", INITIALLY, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("inline", INLINE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("inner", INNER_P, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
@@ -265,6 +267,7 @@ PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("matched", MATCHED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("measures", MEASURES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("merge", MERGE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("method", METHOD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD, AS_LABEL)
@@ -326,6 +329,9 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("past", PAST, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("pattern", PATTERN_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("permute", PERMUTE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -385,6 +391,7 @@ PG_KEYWORD("scroll", SCROLL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("search", SEARCH, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("second", SECOND_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("seek", SEEK, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("select", SELECT, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -416,6 +423,7 @@ PG_KEYWORD("stored", STORED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("strict", STRICT_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("strip", STRIP_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("subscription", SUBSCRIPTION, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("subset", SUBSET, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("substring", SUBSTRING, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("support", SUPPORT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("symmetric", SYMMETRIC, RESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f589112d5e..6640090910 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -51,6 +51,7 @@ typedef enum ParseExprKind
EXPR_KIND_WINDOW_FRAME_RANGE, /* window frame clause with RANGE */
EXPR_KIND_WINDOW_FRAME_ROWS, /* window frame clause with ROWS */
EXPR_KIND_WINDOW_FRAME_GROUPS, /* window frame clause with GROUPS */
+ EXPR_KIND_RPR_DEFINE, /* DEFINE */
EXPR_KIND_SELECT_TARGET, /* SELECT target list item */
EXPR_KIND_INSERT_TARGET, /* INSERT target list item */
EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */
--
2.25.1
----Next_Part(Wed_Oct__4_15_03_28_2023_821)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v9-0002-Row-pattern-recognition-patch-parse-analysis.patch"
^ permalink raw reply [nested|flat] 12+ messages in thread
* NUMA shared memory interleaving
@ 2025-04-16 09:14 Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
2025-06-26 11:36 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Jakub Wartak @ 2025-04-16 09:14 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; +Cc: Andres Freund <[email protected]>; Tomas Vondra <[email protected]>; Bertrand Drouvot <[email protected]>
Thanks to having pg_numa.c, we can now simply address problem#2 of
NUMA imbalance from [1] pages 11-14, by interleaving shm memory in
PG19 - patch attached. We do not need to call numa_set_localalloc() as
we only interleave shm segments, while local allocations stay the same
(well, "local" means relative to the CPU asking for private memory).
Below is result from legacy 4s32t64 Sandy Bridge EP box with low NUMA
(QPI) interconnect bandwidth to better illustrate the problem (it's
little edgecase, but some one may hit it):
Testcase:
small SB (here it was 4GB*) that fully fits NUMA hugepage zone as
this was tested with hugepages=on
$ cat seqconcurrscans.pgb
\set num (:client_id % 8) + 1
select sum(octet_length(filler)) from pgbench_accounts_:num;
/usr/local/pgsql/bin/pg_ctl -D /db/data -l logfile restart
/usr/local/pgsql/bin/psql -c "select
pg_prewarm('pgbench_accounts_'||s) from generate_series(1, 8) s;"
#load all using current policy
/usr/local/pgsql/bin/psql -c "select * from
pg_shmem_allocations_numa where name = 'Buffer Blocks';"
/usr/local/pgsql/bin/pgbench -c 64 -j 8 -P 1 -T 60 -f seqconcurrscans.pgb
on master and numa=off (default) and in previous versions:
name | numa_node | size
---------------+-----------+------------
Buffer Blocks | 0 | 0
Buffer Blocks | 1 | 0
Buffer Blocks | 2 | 4297064448
Buffer Blocks | 3 | 0
latency average = 1826.324 ms
latency stddev = 665.567 ms
tps = 34.708151 (without initial connection time)
on master and numa=on:
name | numa_node | size
---------------+-----------+------------
Buffer Blocks | 0 | 1073741824
Buffer Blocks | 1 | 1073741824
Buffer Blocks | 2 | 1075838976
Buffer Blocks | 3 | 1073741824
latency average = 1002.288 ms
latency stddev = 214.392 ms
tps = 63.344814 (without initial connection time)
Normal pgbench workloads tend to be not affected, as each backend
tends to touch just a small partition of shm (thanks to BAS
strategies). Some remaining questions are:
1. How to name this GUC (numa or numa_shm_interleave) ? I prefer the
first option, as we could potentially in future add more optimizations
behind that GUC.
2. Should we also interleave DSA/DSM for Parallel Query? (I'm not an
expert on DSA/DSM at all)
3. Should we fail to start if we numa=on on an unsupported platform?
* interesting tidbit to get reliable measurement: one needs to double
check that s_b (hugepage allocation) is smaller than per-NUMA zone
free hugepages (s_b fits static hugepage allocation within a single
zone). This shouldn't be a problem on 2 sockets (as most of the time
there, s_b is < 50% RAM anyway, well usually 26-30% with some stuff by
max_connections, it's higher than 25% but people usually sysctl
nr_hugepages=25%RAM) , but with >= 4 NUMA nodes (4 sockets or some
modern MCMs) kernel might start spilling the s_b (> 25%) to the other
NUMA node on it's own, so it's best to verify it using
pg_shmem_allocations_numa...
-J.
[1] - https://anarazel.de/talks/2024-10-23-pgconf-eu-numa-vs-postgresql/numa-vs-postgresql.pdf
Attachments:
[application/octet-stream] v1-0001-Add-capability-to-interleave-shared-memory-across.patch (6.4K, ../../CAKZiRmw6i1W1AwXxa-Asrn8wrVcVH3TO715g_MCoowTS9rkGyw@mail.gmail.com/2-v1-0001-Add-capability-to-interleave-shared-memory-across.patch)
download | inline diff:
From f7295a1c4cd07c393ced70ad0c8622efdb6ab26d Mon Sep 17 00:00:00 2001
From: Jakub Wartak <[email protected]>
Date: Wed, 16 Apr 2025 10:23:31 +0200
Subject: [PATCH v1] Add capability to interleave shared memory across multiple
NUMA nodes
Introduce new GUC numa=off(default)/on/auto that might be used to
enable interleaving shared memory. Until today, imbalances in shared memory
allocations on NUMA setups, may have caused non-deterministic performance
due to differences in latencies and bandwidths across interconnects ("remote"
access). This is only supported on Linux with libnuma.
Author: Jakub Wartak <[email protected]>
Inspired-by: Andres Freund <[email protected]>
Reviewed-by:
Discussion:
---
doc/src/sgml/config.sgml | 16 ++++++++++++++++
src/backend/port/sysv_shmem.c | 7 +++++++
src/backend/utils/misc/guc_tables.c | 18 ++++++++++++++++++
src/backend/utils/misc/postgresql.conf.sample | 2 ++
src/include/port/pg_numa.h | 1 +
src/include/storage/pg_shmem.h | 10 ++++++++++
src/port/pg_numa.c | 17 +++++++++++++++++
7 files changed, 71 insertions(+)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index c1674c22cb2..15397df71d6 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2313,6 +2313,22 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-numa" xreflabel="numa">
+ <term><varname>numa</varname> (<type>enum</type>)
+ <indexterm>
+ <primary><varname>numa</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies wheter to use NUMA interleaving policy for the shared memory
+ segment. Possible values are <literal>off</literal>, <literal>on</literal>
+ and <literal>auto</literal>. This parameter is only effective on Linux.
+ The default value is <literal>off</literal>. This parameter can only be set
+ at server start.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect2>
diff --git a/src/backend/port/sysv_shmem.c b/src/backend/port/sysv_shmem.c
index 197926d44f6..510b0e53638 100644
--- a/src/backend/port/sysv_shmem.c
+++ b/src/backend/port/sysv_shmem.c
@@ -29,6 +29,7 @@
#include "miscadmin.h"
#include "port/pg_bitutils.h"
+#include "port/pg_numa.h"
#include "portability/mem.h"
#include "storage/dsm.h"
#include "storage/fd.h"
@@ -663,6 +664,12 @@ CreateAnonymousSegment(Size *size)
allocsize) : 0));
}
+ if (numa == NUMA_ON || (numa == NUMA_AUTO && pg_numa_get_max_node() > 1))
+ {
+ elog(DEBUG1, "enabling NUMA shm interleaving");
+ pg_numa_interleave_memptr(ptr, allocsize);
+ }
+
*size = allocsize;
return ptr;
}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 60b12446a1c..e4c9491df78 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -491,6 +491,13 @@ static const struct config_enum_entry file_copy_method_options[] = {
{NULL, 0, false}
};
+static const struct config_enum_entry numa_options[] = {
+ {"off", NUMA_OFF, false},
+ {"on", NUMA_ON, false},
+ {"auto", NUMA_AUTO, false},
+ {NULL, 0, false}
+};
+
/*
* Options for enum values stored in other modules
*/
@@ -579,6 +586,7 @@ static int ssl_renegotiation_limit;
int huge_pages = HUGE_PAGES_TRY;
int huge_page_size;
int huge_pages_status = HUGE_PAGES_UNKNOWN;
+int numa = DEFAULT_NUMA;
/*
* These variables are all dummies that don't do anything, except in some
@@ -5418,6 +5426,16 @@ struct config_enum ConfigureNamesEnum[] =
NULL, assign_io_method, NULL
},
+ {
+ {"numa", PGC_POSTMASTER, RESOURCES_MEM,
+ gettext_noop("Whether to use NUMA interleaving for shared memory."),
+ NULL
+ },
+ &numa,
+ DEFAULT_NUMA, numa_options,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, NULL, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 34826d01380..f46b2d8de4d 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -137,6 +137,8 @@
# (change requires restart)
#huge_page_size = 0 # zero for system default
# (change requires restart)
+#numa = off # off, on, auto
+ # (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
diff --git a/src/include/port/pg_numa.h b/src/include/port/pg_numa.h
index 40f1d324dcf..129663de2e8 100644
--- a/src/include/port/pg_numa.h
+++ b/src/include/port/pg_numa.h
@@ -17,6 +17,7 @@
extern PGDLLIMPORT int pg_numa_init(void);
extern PGDLLIMPORT int pg_numa_query_pages(int pid, unsigned long count, void **pages, int *status);
extern PGDLLIMPORT int pg_numa_get_max_node(void);
+extern PGDLLIMPORT int pg_numa_interleave_memptr(void *ptr, size_t sz);
#ifdef USE_LIBNUMA
diff --git a/src/include/storage/pg_shmem.h b/src/include/storage/pg_shmem.h
index 5f7d4b83a60..1b09f7ec390 100644
--- a/src/include/storage/pg_shmem.h
+++ b/src/include/storage/pg_shmem.h
@@ -46,6 +46,7 @@ extern PGDLLIMPORT int shared_memory_type;
extern PGDLLIMPORT int huge_pages;
extern PGDLLIMPORT int huge_page_size;
extern PGDLLIMPORT int huge_pages_status;
+extern PGDLLIMPORT int numa;
/* Possible values for huge_pages and huge_pages_status */
typedef enum
@@ -64,6 +65,15 @@ typedef enum
SHMEM_TYPE_MMAP,
} PGShmemType;
+typedef enum
+{
+ NUMA_OFF,
+ NUMA_ON,
+ NUMA_AUTO,
+} NumaType;
+
+#define DEFAULT_NUMA NUMA_OFF
+
#ifndef WIN32
extern PGDLLIMPORT unsigned long UsedShmemSegID;
#else
diff --git a/src/port/pg_numa.c b/src/port/pg_numa.c
index 4b487a2a4e8..6ed0a5d2949 100644
--- a/src/port/pg_numa.c
+++ b/src/port/pg_numa.c
@@ -55,6 +55,17 @@ pg_numa_get_max_node(void)
return numa_max_node();
}
+int
+pg_numa_interleave_memptr(void *ptr, size_t sz)
+{
+ struct bitmask *nodemask = numa_allocate_nodemask();
+
+ numa_bitmask_setall(nodemask);
+ numa_interleave_memory(ptr, sz, nodemask);
+ numa_free_nodemask(nodemask);
+ return 0;
+}
+
#else
/* Empty wrappers */
@@ -77,4 +88,10 @@ pg_numa_get_max_node(void)
return 0;
}
+int
+pg_numa_interleave_memptr(void *ptr, size_t sz)
+{
+ return 0;
+}
+
#endif
--
2.39.5
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
@ 2025-04-18 17:43 ` Bertrand Drouvot <[email protected]>
2025-06-26 11:31 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Bertrand Drouvot @ 2025-04-18 17:43 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Jakub Wartak <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>; Tomas Vondra <[email protected]>
Hi,
On Wed, Apr 16, 2025 at 10:05:04AM -0400, Robert Haas wrote:
> On Wed, Apr 16, 2025 at 5:14 AM Jakub Wartak
> <[email protected]> wrote:
> > Normal pgbench workloads tend to be not affected, as each backend
> > tends to touch just a small partition of shm (thanks to BAS
> > strategies). Some remaining questions are:
> > 1. How to name this GUC (numa or numa_shm_interleave) ? I prefer the
> > first option, as we could potentially in future add more optimizations
> > behind that GUC.
>
> I wonder whether the GUC needs to support interleaving between a
> designated set of nodes rather than only being able to do all nodes.
> For example, suppose someone is pinning the processes to a certain set
> of NUMA nodes; perhaps then they wouldn't want to use memory from
> other nodes.
+1. That could be used for instances consolidation on the same host. One could
ensure that numa nodes are not shared across instances (cpu and memory resource
isolation per instance). Bonus point, adding Direct I/O into the game would
ensure that the OS page cache is not shared too.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
@ 2025-06-26 11:31 ` Jakub Wartak <[email protected]>
2025-06-27 16:41 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Jakub Wartak @ 2025-06-26 11:31 UTC (permalink / raw)
To: Bertrand Drouvot <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>; Tomas Vondra <[email protected]>
On Fri, Apr 18, 2025 at 7:43 PM Bertrand Drouvot
<[email protected]> wrote:
>
> Hi,
>
> On Wed, Apr 16, 2025 at 10:05:04AM -0400, Robert Haas wrote:
> > On Wed, Apr 16, 2025 at 5:14 AM Jakub Wartak
> > <[email protected]> wrote:
> > > Normal pgbench workloads tend to be not affected, as each backend
> > > tends to touch just a small partition of shm (thanks to BAS
> > > strategies). Some remaining questions are:
> > > 1. How to name this GUC (numa or numa_shm_interleave) ? I prefer the
> > > first option, as we could potentially in future add more optimizations
> > > behind that GUC.
> >
> > I wonder whether the GUC needs to support interleaving between a
> > designated set of nodes rather than only being able to do all nodes.
> > For example, suppose someone is pinning the processes to a certain set
> > of NUMA nodes; perhaps then they wouldn't want to use memory from
> > other nodes.
>
> +1. That could be used for instances consolidation on the same host. One could
> ensure that numa nodes are not shared across instances (cpu and memory resource
> isolation per instance). Bonus point, adding Direct I/O into the game would
> ensure that the OS page cache is not shared too.
Hi, the attached patch has two changes:
1. It adds more modes and supports this 'consolidation' and
'isolation' scenario from above. Doc in patch briefly explains the
merit.
2. it adds trivial NUMA for PQ
The original initial test expanded on the very same machine
(4s32c128t, QPI interconnect):
numa='off'
latency average = 1271.019 ms
latency stddev = 245.061 ms
tps = 49.683923 (without initial connection time)
explanation(pcm-memory): 3 sockets doing ~46MB/s on RAM (almost
idle), 1 socket doing ~17GB/s (fully saturated because s_b ended up in
this scenario only on NUMA node)
numa='all'
latency average = 702.622 ms
latency stddev = 13.259 ms
tps = 90.026526 (without initial connection time)
explanation(pcm-memory): this forced to interleave s_b across 4
NUMA nodes and each socket gets equal part of workload (9.2 - 10GB/s)
totalling ~37GB/s of memory bandwidth
This gives a boost: 90/49.6=1.8x. The values for memory bandwidth are
combined read+write.
NUMA impact on the Parallel Query:
----------------------------------
with:
with the most simplistic interleaving of s_b +
dynamic_shared_memory for PQ interleaved too :
max_worker_processes=max_parallel_workers=max_parallel_workers_per_gather=64
alter on 1 partition to force real 64 parallel seq scans
The query:
select sum(octet_length(filler)) from pgbench_accounts;
launched 64 effective parallel workes launched for 64 partitions each
of 400MB (25600MBs), All of that was fitting in the s_b (32GB), so
all fetched from s_b. All was hot, several first runs are not shown.
select sum(octet_length(filler)) from pgbench_accounts;
numa='off'
Time: 1108.178 ms (00:01.108)
Time: 1118.494 ms (00:01.118)
Time: 1104.491 ms (00:01.104)
Time: 1112.221 ms (00:01.112)
Time: 1105.501 ms (00:01.106)
avg: 1109 ms
-- not interleaved, more like appended:
postgres=# select * from pg_shmem_allocations_numa where name =
'Buffer Blocks';
name | numa_node | size
---------------+-----------+------------
Buffer Blocks | 0 | 9277800448
Buffer Blocks | 1 | 7044333568
Buffer Blocks | 2 | 9097445376
Buffer Blocks | 3 | 8942256128
numa='all'
Time: 1026.747 ms (00:01.027)
Time: 1024.087 ms (00:01.024)
Time: 1024.179 ms (00:01.024)
Time: 1037.026 ms (00:01.037)
avg: 1027 ms
postgres=# select * from pg_shmem_allocations_numa where name
= 'Buffer Blocks';
name | numa_node | size
---------------+-----------+------------
Buffer Blocks | 0 | 8589934592
Buffer Blocks | 1 | 8592031744
Buffer Blocks | 2 | 8589934592
Buffer Blocks | 3 | 8589934592
1109/1027=1.079x, not bad for such trivial change and the paper
referenced by Thomas also stated (`We can see an improvement by a
factor of more than three by just running
the non-NUMA-aware implementation on interleaved memor`), probably it
could be improved much further, but I'm not planning to work on this
more. So if anything:
- latency-wise: it would be best to place leader+all PQ workers close
to s_b, provided s_b fits NUMA shared/huge page memory there and you
won't need more CPU than there's on that NUMA node... (assuming e.g.
hosting 4 DBs on 4-sockets each on it's own, it would be best to pin
everything including shm, but PQ workers too)
- capacity/TPS-wise or s_b > NUMA: just interleave to maximize
bandwidth and get uniform CPU performance out of this
The patch supports e.g. numa='@1' which should fully isolate the
workload to just memory and CPUs on node #1.
As for the patch: I'm lost with our C headers policy :)
One of less obvious reasons (outside of better efficiency of
consolidation of multiple PostgreSQL cluster on single NUMA server),
why I've implemented '=' and '@' is that seems that CXL memory can be
attached as a CPU-less(!) NUMA node, thus Linux - depending on
sysctls/sysfs setup - could use it for automatic memory tiering and
the above provides configurable way to prevent allocation on such
(potential) systems - simply exclude such NUMA node via config for now
and we are covered I think. I have no access to real hardware, so I
haven't researched it further, but it looks like in the far future we
could probably indicate preferred NUMA memory nodes (think big s_b,
bigger than "CPU" RAM), and that kernel could transparently do NUMA
auto balancing/demotion for us (AKA Transparent Page Placement AKA
memory) or vice versa: use small s_b and do not use CXL node at all
and expect that VFS cache could be spilled there.
numa_weighted_interleave_memory() / MPOL_WEIGHTED_INTERLEAVE is not
yet supported in distros (although new libnuma has support for it), so
I have not included it in the patch, as it was too early.
BTW: DO NOT USE meson's --buildtype=debug as it somehow disables the
NUMA optimizations benefit, I've lost hours on it (probably -O0 is so
slow that it wasn't stressing interconnects enough). Default is
--buildtype=debugoptimized which is good. Also if testing performance,
check that HW that has proper realistic NUMA remote access distances
first, e.g. here my remote had remote access 2x or even 3x. Probably
this is worth only testing on multi-sockets which have really higher
latencies/throughput limitations, but reports from 1 socket MCMs CPUs
(with various Node-per-Socket BIOS settings) are welcome too.
kernel 6.14.7 was used with full isolation:
cpupower frequency-set --governor performance
cpupower idle-set -D0
echo 1 > /sys/devices/system/cpu/intel_pstate/no_turbo
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
max_connections = '10000'
huge_pages = 'on'
wal_level = 'minimal'
wal_buffers = '1024MB'
max_wal_senders = '0'
shared_buffers = '4 GB'
autovacuum = 'off'
max_parallel_workers_per_gather = '0'
numa = 'all'
#numa = 'off'
[1] - https://lwn.net/Articles/897536/
Attachments:
[application/octet-stream] v4-0001-Add-capability-to-interleave-shared-memory-across.patch (17.4K, ../../CAKZiRmy4r+CYFX+x7fAj9t4tjMJZnBY73phuBVM7pbEfxueiEg@mail.gmail.com/2-v4-0001-Add-capability-to-interleave-shared-memory-across.patch)
download | inline diff:
From da7fa0b8af9b75108bd4f0b50b25bdf1a2167473 Mon Sep 17 00:00:00 2001
From: Jakub Wartak <[email protected]>
Date: Tue, 24 Jun 2025 11:23:36 +0200
Subject: [PATCH v4] Add capability to interleave shared memory across multiple
NUMA nodes.
Introduce new GUC numa=off(default)/auto/all/../=../@.. that might be used to
enable interleaving of shared memory. Until today, imbalances in shared memory
allocations on NUMA setups, may have caused non-deterministic performance
due to differences in latencies and bandwidths across interconnects ("remote"
access).
When provided list of nodes, the default is to use interleave memory on
preferred NUMA nodes, but support for more strict modes: pinning memory or
pinning both memory and CPU to specific NUMA node(s) is handled using special
'=' and '@' prefixes.
This is only supported on Linux with libnuma.
Author: Jakub Wartak <[email protected]>
Inspired-by: Andres Freund <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/CAKZiRmw6i1W1AwXxa-Asrn8wrVcVH3TO715g_MCoowTS9rkGyw%40mail.gmail.com
---
doc/src/sgml/config.sgml | 26 ++++
src/backend/port/sysv_shmem.c | 22 ++++
src/backend/postmaster/postmaster.c | 17 +++
src/backend/storage/ipc/dsm_impl.c | 13 ++
src/backend/storage/ipc/shmem.c | 76 ++++++++++++
src/backend/utils/misc/guc_tables.c | 14 +++
src/backend/utils/misc/postgresql.conf.sample | 2 +
src/include/port/pg_numa.h | 13 ++
src/include/storage/pg_shmem.h | 19 +++
src/include/utils/guc_hooks.h | 2 +
src/port/pg_numa.c | 114 +++++++++++++++++-
11 files changed, 317 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index b265cc89c9d..0ab5c519624 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2329,6 +2329,32 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-numa" xreflabel="numa">
+ <term><varname>numa</varname> (<type>enum</type>)
+ <indexterm>
+ <primary><varname>numa</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies wheter to use NUMA interleaving policy for the shared memory
+ segment. Possible values are <literal>off</literal>,
+ <literal>all</literal> (interleaves shared memory across all available NUMA nodes),
+ <literal>auto</literal> (as previous, but only if number of available NUMA nodes is 2 or higher)
+ or <literal>[=@]comma-separated list of node numbers or node ranges</literal>
+
+ If comma-separated list of NUMA nodes is prefixed with <literal>=</literal> the memory allocations
+ are made strict to avoid spilling to other NUMA nodes.
+ If comma-separated list of NUMA nodes is prefixed with <literal>@</literal> the memory allocations
+ are made strict and also available CPUs are limited only to those of listed NUMA nodes.
+
+ This parameter is only effective on Linux. Parallel Query interleaving is
+ only supported with <literal>dynamic_shared_memory</literal>=<literal>posix</literal>
+ The default value is <literal>off</literal>. This parameter can only be
+ set at server start.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect2>
diff --git a/src/backend/port/sysv_shmem.c b/src/backend/port/sysv_shmem.c
index 197926d44f6..77af7c56ecd 100644
--- a/src/backend/port/sysv_shmem.c
+++ b/src/backend/port/sysv_shmem.c
@@ -29,6 +29,7 @@
#include "miscadmin.h"
#include "port/pg_bitutils.h"
+#include "port/pg_numa.h"
#include "portability/mem.h"
#include "storage/dsm.h"
#include "storage/fd.h"
@@ -663,6 +664,27 @@ CreateAnonymousSegment(Size *size)
allocsize) : 0));
}
+ if (numa->setting > NUMA_OFF)
+ {
+ /* In strict mode we want to ensure to not spill memory to another NUMA nodes */
+ int mem_bind_policy = numa->setting >= NUMA_STRICT_ONLY ? 1 : 0;
+
+ /* We do nothing in auto mode, if there is just one standard NUMA node */
+ if(numa->setting == NUMA_AUTO && pg_numa_get_max_node() <= 1) {
+ elog(DEBUG1, "no NUMA nodes found");
+ } else {
+ elog(LOG, "enabling NUMA shm interleaving");
+ pg_numa_interleave_memptr(ptr, allocsize, numa->nodes);
+
+ /* In NUMA_PREFERRED we can spill memory to other nodes, but not in STRICT modes */
+ pg_numa_set_bind_policy(mem_bind_policy);
+
+ /* We can also isolate CPUs to just isolated NUMA nodes */
+ if(numa->setting >= NUMA_STRICT_ONLY_AND_CPU_TOO)
+ pg_numa_bind(numa->nodes);
+ }
+ }
+
*size = allocsize;
return ptr;
}
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 490f7ce3664..bc9e3da8fa7 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -100,6 +100,7 @@
#include "pg_getopt.h"
#include "pgstat.h"
#include "port/pg_bswap.h"
+#include "port/pg_numa.h"
#include "postmaster/autovacuum.h"
#include "postmaster/bgworker_internals.h"
#include "postmaster/pgarch.h"
@@ -113,6 +114,7 @@
#include "storage/fd.h"
#include "storage/io_worker.h"
#include "storage/ipc.h"
+#include "storage/pg_shmem.h"
#include "storage/pmsignal.h"
#include "storage/proc.h"
#include "tcop/backend_startup.h"
@@ -453,6 +455,7 @@ static void StartSysLogger(void);
static void StartAutovacuumWorker(void);
static bool StartBackgroundWorker(RegisteredBgWorker *rw);
static void InitPostmasterDeathWatchHandle(void);
+static void InitNuma(void);
#ifdef WIN32
#define WNOHANG 0 /* ignored, so any integer value will do */
@@ -993,6 +996,9 @@ PostmasterMain(int argc, char *argv[])
ExitPostmaster(0);
}
+ /* Initialize libnuma if necessary */
+ InitNuma();
+
/*
* Set up shared memory and semaphores.
*
@@ -4616,3 +4622,14 @@ InitPostmasterDeathWatchHandle(void)
GetLastError())));
#endif /* WIN32 */
}
+
+
+static void
+InitNuma(void)
+{
+ if(numa->setting > NUMA_OFF) {
+ if (pg_numa_init() == -1)
+ elog(ERROR, "libnuma initialization failed or NUMA is not supported on this platform");
+ }
+ return;
+}
diff --git a/src/backend/storage/ipc/dsm_impl.c b/src/backend/storage/ipc/dsm_impl.c
index 6bf8ab5bb5b..46dcef48394 100644
--- a/src/backend/storage/ipc/dsm_impl.c
+++ b/src/backend/storage/ipc/dsm_impl.c
@@ -64,8 +64,10 @@
#include "pgstat.h"
#include "portability/mem.h"
#include "postmaster/postmaster.h"
+#include "port/pg_numa.h"
#include "storage/dsm_impl.h"
#include "storage/fd.h"
+#include "storage/pg_shmem.h"
#include "utils/guc.h"
#include "utils/memutils.h"
@@ -334,6 +336,13 @@ dsm_impl_posix(dsm_op op, dsm_handle handle, Size request_size,
}
*mapped_address = address;
*mapped_size = request_size;
+
+ /* We interleave memory only at creation time. */
+ if (op == DSM_OP_CREATE && numa->setting > NUMA_OFF) {
+ elog(DEBUG1, "interleaving shm mem @ %p size=%zu", *mapped_address, *mapped_size);
+ pg_numa_interleave_memptr(*mapped_address, *mapped_size, numa->nodes);
+ }
+
close(fd);
ReleaseExternalFD();
@@ -588,6 +597,8 @@ dsm_impl_sysv(dsm_op op, dsm_handle handle, Size request_size,
*mapped_address = address;
*mapped_size = request_size;
+ /* As dynamic_shared_memory=sysv is a bit legacy, we do not peform NUMA interleave here */
+
return true;
}
#endif
@@ -937,6 +948,8 @@ dsm_impl_mmap(dsm_op op, dsm_handle handle, Size request_size,
*mapped_address = address;
*mapped_size = request_size;
+ /* As dynamic_shared_memory=mmap is a bit legacy, we do not peform NUMA interleave here */
+
if (CloseTransientFile(fd) != 0)
{
ereport(elevel,
diff --git a/src/backend/storage/ipc/shmem.c b/src/backend/storage/ipc/shmem.c
index c9ae3b45b76..bac84492e79 100644
--- a/src/backend/storage/ipc/shmem.c
+++ b/src/backend/storage/ipc/shmem.c
@@ -74,6 +74,10 @@
#include "storage/shmem.h"
#include "storage/spin.h"
#include "utils/builtins.h"
+#include "utils/guc.h"
+#include "utils/guc_hooks.h"
+#include <ctype.h>
+#include <numa.h>
static void *ShmemAllocRaw(Size size, Size *allocated_size);
@@ -765,3 +769,75 @@ pg_numa_available(PG_FUNCTION_ARGS)
{
PG_RETURN_BOOL(pg_numa_init() != -1);
}
+
+bool
+check_numa(char **newval, void **extra, GucSource source)
+{
+ bool result = true;
+ NumaConfigData *n;
+ char *rawstring = *newval;
+
+ n = (NumaConfigData *) guc_malloc(LOG, sizeof(NumaConfigData));
+#ifndef USE_LIBNUMA
+ n->setting = NUMA_OFF;
+
+ if (!(strcmp(rawstring, "") == 0 || strcmp(rawstring, "off") == 0)) {
+
+ GUC_check_errdetail("\"%s\" is not supported on this platform.",
+ "numa");
+ result = false;
+ }
+#else
+
+ /* in case of just listing NUMA nodes it's list of preferred ones */
+ n->setting = NUMA_PREFERRED;
+
+ if (strcmp(rawstring, "") == 0)
+ n->setting = DEFAULT_NUMA;
+ else if (pg_strcasecmp(rawstring, "off") == 0)
+ n->setting = NUMA_OFF;
+ else if (pg_strcasecmp(rawstring, "all") == 0) {
+ n->setting = NUMA_ALL;
+ n->nodes = numa_all_nodes_ptr;
+ } else if (pg_strcasecmp(rawstring, "auto") == 0) {
+ n->setting = NUMA_AUTO;
+ n->nodes = numa_all_nodes_ptr;
+ } else if (isdigit(rawstring[0]))
+ n->setting = NUMA_PREFERRED;
+ else if (rawstring[0] == '=')
+ n->setting = NUMA_STRICT_ONLY;
+ else if (rawstring[0] == '@')
+ n->setting = NUMA_STRICT_ONLY_AND_CPU_TOO;
+ else {
+ GUC_check_errdetail("Invalid option \"%s\".", rawstring);
+ guc_free(n);
+ return false;
+ }
+
+ if(n->setting >= NUMA_PREFERRED) {
+ char *s = rawstring;
+
+ /* skip first character */
+ if(n->setting >= NUMA_STRICT_ONLY)
+ s++;
+
+ n->nodes = pg_numa_parse_nodestring(s);
+ if(n->nodes == 0) {
+ GUC_check_errdetail("Invalid list syntax in parameter \"%s\".",
+ "numa");
+ guc_free(n);
+ return false;
+ }
+ }
+
+#endif
+
+ *extra = n;
+ return result;
+}
+
+void
+assign_numa(const char *newval, void *extra)
+{
+ numa = (NumaConfigData *) extra;
+}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index f04bfedb2fd..65b7ab7b5b0 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -491,6 +491,7 @@ static const struct config_enum_entry file_copy_method_options[] = {
{NULL, 0, false}
};
+
/*
* Options for enum values stored in other modules
*/
@@ -580,6 +581,8 @@ int huge_pages = HUGE_PAGES_TRY;
int huge_page_size;
int huge_pages_status = HUGE_PAGES_UNKNOWN;
+NumaConfigData *numa;
+
/*
* These variables are all dummies that don't do anything, except in some
* cases provide the value for SHOW to display. The real state is elsewhere
@@ -594,6 +597,7 @@ static char *server_version_string;
static int server_version_num;
static char *debug_io_direct_string;
static char *restrict_nonsystem_relation_kind_string;
+static char *numa_string;
#ifdef HAVE_SYSLOG
#define DEFAULT_SYSLOG_FACILITY LOG_LOCAL0
@@ -4984,6 +4988,16 @@ struct config_string ConfigureNamesString[] =
check_log_connections, assign_log_connections, NULL
},
+ {
+ {"numa", PGC_POSTMASTER, RESOURCES_MEM,
+ gettext_noop("Whether to enable NUMA optimizations."),
+ NULL
+ },
+ &numa_string,
+ "",
+ check_numa, assign_numa, NULL
+ },
+
/* End-of-list marker */
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 341f88adc87..d9e0c165a94 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -135,6 +135,8 @@
# (change requires restart)
#huge_page_size = 0 # zero for system default
# (change requires restart)
+#numa = off # off,all, auto, or comma list of NUMA nodes
+ # (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
diff --git a/src/include/port/pg_numa.h b/src/include/port/pg_numa.h
index 40f1d324dcf..567cef3c505 100644
--- a/src/include/port/pg_numa.h
+++ b/src/include/port/pg_numa.h
@@ -14,9 +14,19 @@
#ifndef PG_NUMA_H
#define PG_NUMA_H
+// JW: is this legal to be included here?
+#include <numa.h>
+#include <numaif.h>
+
+typedef struct bitmask pg_numa_bitmask_t;
+
extern PGDLLIMPORT int pg_numa_init(void);
extern PGDLLIMPORT int pg_numa_query_pages(int pid, unsigned long count, void **pages, int *status);
extern PGDLLIMPORT int pg_numa_get_max_node(void);
+extern PGDLLIMPORT int pg_numa_interleave_memptr(void *ptr, size_t sz, pg_numa_bitmask_t *mask);
+extern PGDLLIMPORT pg_numa_bitmask_t *pg_numa_parse_nodestring(const char *string);
+extern PGDLLIMPORT void pg_numa_set_bind_policy(int strict);
+extern PGDLLIMPORT void pg_numa_bind(pg_numa_bitmask_t *nodemask);
#ifdef USE_LIBNUMA
@@ -27,6 +37,9 @@ extern PGDLLIMPORT int pg_numa_get_max_node(void);
#define pg_numa_touch_mem_if_required(ro_volatile_var, ptr) \
ro_volatile_var = *(volatile uint64 *) ptr
+extern void numa_warn(int num, char *fmt,...) pg_attribute_printf(2, 3);
+extern void numa_error(char *where);
+
#else
#define pg_numa_touch_mem_if_required(ro_volatile_var, ptr) \
diff --git a/src/include/storage/pg_shmem.h b/src/include/storage/pg_shmem.h
index 5f7d4b83a60..0c95fc4cdd0 100644
--- a/src/include/storage/pg_shmem.h
+++ b/src/include/storage/pg_shmem.h
@@ -25,6 +25,7 @@
#define PG_SHMEM_H
#include "storage/dsm_impl.h"
+#include "port/pg_numa.h"
typedef struct PGShmemHeader /* standard header for all Postgres shmem */
{
@@ -41,11 +42,17 @@ typedef struct PGShmemHeader /* standard header for all Postgres shmem */
#endif
} PGShmemHeader;
+typedef struct NumaConfigData {
+ int setting;
+ pg_numa_bitmask_t *nodes;
+} NumaConfigData;
+
/* GUC variables */
extern PGDLLIMPORT int shared_memory_type;
extern PGDLLIMPORT int huge_pages;
extern PGDLLIMPORT int huge_page_size;
extern PGDLLIMPORT int huge_pages_status;
+extern PGDLLIMPORT NumaConfigData *numa;
/* Possible values for huge_pages and huge_pages_status */
typedef enum
@@ -64,6 +71,18 @@ typedef enum
SHMEM_TYPE_MMAP,
} PGShmemType;
+typedef enum
+{
+ NUMA_OFF,
+ NUMA_ALL,
+ NUMA_AUTO,
+ NUMA_PREFERRED,
+ NUMA_STRICT_ONLY,
+ NUMA_STRICT_ONLY_AND_CPU_TOO,
+} NumaType;
+
+#define DEFAULT_NUMA NUMA_OFF
+
#ifndef WIN32
extern PGDLLIMPORT unsigned long UsedShmemSegID;
#else
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index 799fa7ace68..854a7dd02b4 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -94,6 +94,8 @@ extern bool check_multixact_member_buffers(int *newval, void **extra,
extern bool check_multixact_offset_buffers(int *newval, void **extra,
GucSource source);
extern bool check_notify_buffers(int *newval, void **extra, GucSource source);
+extern bool check_numa(char **newval, void **extra, GucSource source);
+extern void assign_numa(const char *newval, void *extra);
extern bool check_primary_slot_name(char **newval, void **extra,
GucSource source);
extern bool check_random_seed(double *newval, void **extra, GucSource source);
diff --git a/src/port/pg_numa.c b/src/port/pg_numa.c
index 4b487a2a4e8..6956f33ef44 100644
--- a/src/port/pg_numa.c
+++ b/src/port/pg_numa.c
@@ -13,10 +13,17 @@
*-------------------------------------------------------------------------
*/
-#include "c.h"
+//JW:is this legal to replace "c.h" with below:
+#ifndef FRONTEND
+#include "postgres.h"
+#else
+#include "postgres_fe.h"
+#endif
+
#include <unistd.h>
#include "port/pg_numa.h"
+#include "common/string.h"
/*
* At this point we provide support only for Linux thanks to libnuma, but in
@@ -55,6 +62,87 @@ pg_numa_get_max_node(void)
return numa_max_node();
}
+int
+pg_numa_interleave_memptr(void *ptr, size_t sz, pg_numa_bitmask_t *mask)
+{
+ numa_interleave_memory(ptr, sz, mask);
+ return 0;
+}
+
+pg_numa_bitmask_t *
+pg_numa_parse_nodestring(const char *string)
+{
+ return numa_parse_nodestring(string);
+}
+
+void
+pg_numa_set_bind_policy(int strict)
+{
+ numa_set_bind_policy(strict);
+}
+
+void
+pg_numa_bind(pg_numa_bitmask_t *nodemask)
+{
+ numa_bind(nodemask);
+}
+
+#ifndef FRONTEND
+/*
+ * The standard libnuma built-in code can be seen here:
+ * https://github.com/numactl/numactl/blob/master/libnuma.c
+ *
+ */
+void
+numa_warn(int num, char *fmt,...)
+{
+ va_list ap;
+ int olde = errno;
+ int needed;
+ StringInfoData msg;
+
+ initStringInfo(&msg);
+
+ va_start(ap, fmt);
+ needed = appendStringInfoVA(&msg, fmt, ap);
+ va_end(ap);
+ if (needed > 0)
+ {
+ enlargeStringInfo(&msg, needed);
+ va_start(ap, fmt);
+ appendStringInfoVA(&msg, fmt, ap);
+ va_end(ap);
+ }
+
+ /* chomp last newline character */
+ pg_strip_crlf(msg.data);
+
+ ereport(WARNING,
+ (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION),
+ errmsg_internal("libnuma: %s", msg.data)));
+
+ pfree(msg.data);
+
+ errno = olde;
+}
+
+void
+numa_error(char *where)
+{
+ int olde = errno;
+
+ /* chomp last newline character */
+ pg_strip_crlf(where);
+
+ /*
+ * XXX: for now we issue just WARNING, but long-term that might depend on
+ * numa_set_strict() here.
+ */
+ elog(WARNING, "libnuma: %s", where);
+ errno = olde;
+}
+#endif /* FRONTEND */
+
#else
/* Empty wrappers */
@@ -77,4 +165,28 @@ pg_numa_get_max_node(void)
return 0;
}
+int
+pg_numa_interleave_memptr(void *ptr, size_t sz, pg_numa_bitmask_t *mask)
+{
+ return 0;
+}
+
+pg_numa_bitmask_t *
+pg_numa_parse_nodestring(const char *string)
+{
+ return NULL;
+}
+
+void
+pg_numa_set_bind_policy(int strict)
+{
+ return;
+}
+
+void
+pg_numa_bind(pg_numa_bitmask_t *nodemask)
+{
+ return;
+}
+
#endif
--
2.39.5
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
2025-06-26 11:31 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
@ 2025-06-27 16:41 ` Tomas Vondra <[email protected]>
2025-06-30 10:55 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Tomas Vondra @ 2025-06-27 16:41 UTC (permalink / raw)
To: Jakub Wartak <[email protected]>; Bertrand Drouvot <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>
Hi,
I agree we should improve the behavior on NUMA systems. But I'm not sure
this patch does far enough, or perhaps the approach seems a bit too
blunt, ignoring some interesting stuff.
AFAICS the patch essentially does the same thing as
numactl --interleave=all
except that it only does that to shared memory, not to process private
memory (as if we called numa_set_localalloc). Which means it has some of
the problems people observe with --interleave=all.
In particular, this practically guarantees that (with 4K memory pages),
each buffer hits multiple NUMA nodes. Because with the first half will
do to node N, while the second half goes to node (N+1).
That doesn't seem great. It's likely better than a misbalanced system
with everything allocated on a single NUMA node, but I don't see how it
could be better than "balanced" properly warmed up system where the
buffers are not split like this.
But OK, admittedly this only happens for 4K memory pages, and serious
systems with a lot of memory are likely to use huge pages, which makes
this less of an issue (only the buffers crossing the page boundaries
might get split).
My bigger comment however is that the approach focuses on balancing the
nodes (i.e. ensuring each node gets a fair share of shared memory), and
is entirely oblivious to the internal structure of the shared memory.
* It interleaves the shared segment, but it has many pieces - shared
buffers are the largest but not the only one. Does it make sense to
interleave all the other pieces?
* Some of the pieces are tightly related. For example, we talk about
shared buffers as if it was one big array, but it actually is two arrays
- blocks and descriptors. Even if buffers don't get split between nodes
(thanks to huge pages), there's no guarantee the descriptor for the
buffer does not end on a different node.
* In fact, the descriptors are so much smaller that blocks that it's
practically guaranteed all descriptors will end up on a single node.
I could probably come up with a couple more similar items, but I think
you get the idea. I do think making Postgres NUMA-aware will require
figuring out how to distribute (or not distribute) different parts of
the shared memory, and do that explicitly. And do that in a way that
allows us to do other stuff in NUMA-aware way, e.g. have a separate
freelists and clocksweep for each NUMA node, etc.
That's something numa_interleave_memory simply can't do for us, and I
suppose it might also have other downsides on large instances. I mean,
doesn't it have to create a separate mapping for each memory page?
Wouldn't that be a bit inefficient/costly for big instances?
Of course, I'm not saying all this as a random passerby - I've been
working on a similar patch for a while, based on Andres' experimental
NUMA branch. It's far from complete/perfect, more of a PoC quality, but
I hope to share it on the mailing list sometime soon.
FWIW while I think the patch doesn't go far enough, there's one area
where I think it probably goes way too far - configurability. I agree
it's reasonable to allow running on a subset of nodes, e.g. to split the
system between multiple instances etc. But do we need to configure that
from Postgres? Aren't people likely to already use something like
containers or k8 anyway? I think we should just try to inherit this from
the environment, i.e. determine which nodes we're allowed to run, and
use that. Maybe we'll find we need to be smarter, but I think we caan
leave that for later.
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
2025-06-26 11:31 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-27 16:41 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
@ 2025-06-30 10:55 ` Jakub Wartak <[email protected]>
2025-06-30 12:02 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-30 19:23 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Jakub Wartak @ 2025-06-30 10:55 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>
Hi Tomas!
On Fri, Jun 27, 2025 at 6:41 PM Tomas Vondra <[email protected]> wrote:
> I agree we should improve the behavior on NUMA systems. But I'm not sure
> this patch does far enough, or perhaps the approach seems a bit too
> blunt, ignoring some interesting stuff.
>
> AFAICS the patch essentially does the same thing as
>
> numactl --interleave=all
>
> except that it only does that to shared memory, not to process private
> memory (as if we called numa_set_localalloc). Which means it has some of
> the problems people observe with --interleave=all.
>
> In particular, this practically guarantees that (with 4K memory pages),
> each buffer hits multiple NUMA nodes. Because with the first half will
> do to node N, while the second half goes to node (N+1).
>
> That doesn't seem great. It's likely better than a misbalanced system
> with everything allocated on a single NUMA node, but I don't see how it
> could be better than "balanced" properly warmed up system where the
> buffers are not split like this.
>
> But OK, admittedly this only happens for 4K memory pages, and serious
> systems with a lot of memory are likely to use huge pages, which makes
> this less of an issue (only the buffers crossing the page boundaries
> might get split).
>
>
> My bigger comment however is that the approach focuses on balancing the
> nodes (i.e. ensuring each node gets a fair share of shared memory), and
> is entirely oblivious to the internal structure of the shared memory.
>
> * It interleaves the shared segment, but it has many pieces - shared
> buffers are the largest but not the only one. Does it make sense to
> interleave all the other pieces?
>
> * Some of the pieces are tightly related. For example, we talk about
> shared buffers as if it was one big array, but it actually is two arrays
> - blocks and descriptors. Even if buffers don't get split between nodes
> (thanks to huge pages), there's no guarantee the descriptor for the
> buffer does not end on a different node.
>
> * In fact, the descriptors are so much smaller that blocks that it's
> practically guaranteed all descriptors will end up on a single node.
>
>
> I could probably come up with a couple more similar items, but I think
> you get the idea. I do think making Postgres NUMA-aware will require
> figuring out how to distribute (or not distribute) different parts of
> the shared memory, and do that explicitly. And do that in a way that
> allows us to do other stuff in NUMA-aware way, e.g. have a separate
> freelists and clocksweep for each NUMA node, etc.
I do understand what you mean, but I'm *NOT* stating here that it
makes PG fully "NUMA-aware". I actually try to avoid doing so with
each sentence. This is only about the imbalance problem specifically.
I think we could build those follow-up optimizations as separate
patches in this or follow-up threads. If we would do it all in one
giant 0001 (without split) the very first question would be to
quantify the impact of each of those optimizations (for which we would
probably need more GUCs?). Here I'm just showing that the very first
baby step - interleaving - helps avoid interconnect saturation in some
cases too.
Anyway, even putting the fact that local mallocs() would be
interleaved, adjusting systemd startup scripts to just include
`numactl --interleave=all` sounds like some dirty hack not like proper
UX.
Also please note that:
* I do not have lot of time to dedicate towards it, yet I was kind of
always interested in researching that and wondering why we couldn't it
for such long time, therefore the previous observability work and now
$subject (note it is not claiming to be full blown NUMA awareness,
just some basic NUMA interleave as first [well, second?] step).
* I've raised this question in the first post "How to name this GUC
(numa or numa_shm_interleave) ?" I still have no idea, but `numa`,
simply looks better, and we could just add way more stuff to it over
time (in PG19 or future versions?). Does that sound good?
> That's something numa_interleave_memory simply can't do for us, and I
> suppose it might also have other downsides on large instances. I mean,
> doesn't it have to create a separate mapping for each memory page?
> Wouldn't that be a bit inefficient/costly for big instances?
No? Or what kind of mapping do you have in mind? I think our shared
memory on the kernel side is just a single VMA (contiguous memory
region), on which technically we execute mbind() (libnuma is just a
wrapper around it). I have not observed any kind of regressions,
actually quite the opposite. Not sure what you also mean by 'big
instances' (AFAIK 1-2TB shared_buffers might even fail to start).
> Of course, I'm not saying all this as a random passerby - I've been
> working on a similar patch for a while, based on Andres' experimental
> NUMA branch. It's far from complete/perfect, more of a PoC quality, but
> I hope to share it on the mailing list sometime soon.
Cool, I didn't know Andres's branch was public till now, I know he
referenced multiple issues in presentation (and hackathon!), but I
wanted to divide it and try to get something in at least partially,
step by step, to have at least something. I think we should
collaborate (not a lot of people interested in this?) and I can try to
offer my limited help if you attack those more advanced problems. I
think we could improve this by properly ensuring that by
over(allocating)/spreading/padding certain special regions (e.g.
better distribute ProcArray, but what about cache hits?) - we get more
juice, or do you want to start from scratch and re-design/re-think all
shm allocations case by case?
> FWIW while I think the patch doesn't go far enough, there's one area
> where I think it probably goes way too far - configurability. I agree
> it's reasonable to allow running on a subset of nodes, e.g. to split the
> system between multiple instances etc. But do we need to configure that
> from Postgres? Aren't people likely to already use something like
> containers or k8 anyway?
> I think we should just try to inherit this from
> the environment, i.e. determine which nodes we're allowed to run, and
> use that. Maybe we'll find we need to be smarter, but I think we caan
> leave that for later.
That's what "numa=all" is all about (take whatever is there in the
OS/namespace), but I do not know a better way than just let's say
numa_get_mems_allowed() being altered somehow by namespace/cgroups. I
think if one runs on k8/containers then it's quite limited/small
deployment and he wouldn't benefit from this at all (I struggle to
imagine the point of k8 pod using 2+ sockets), quite contrary: my
experience indicates that the biggest deployments are usually almost
baremetal? And it's way easier to get consistent results. Anyway as
You say, let's leave it for later. PG currently often is not CPU-aware
(i.e. is not even adjusting sizing of certain structs based on CPU
count), so making it NUMA-aware or cgroup/namespace-aware sounds
already like taking 2-3 steps ahead into future [I think we had
discussion at least one in LWLock partitionmanager /
FP_LOCK_SLOTS_PER_BACKEND where I've proposed to size certain
structures based on $VCPUs or I am misremembering this]
-J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
2025-06-26 11:31 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-27 16:41 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-06-30 10:55 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
@ 2025-06-30 12:02 ` Jakub Wartak <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Jakub Wartak @ 2025-06-30 12:02 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>
On Mon, Jun 30, 2025 at 12:55 PM Jakub Wartak
<[email protected]> wrote:
[..]
> > FWIW while I think the patch doesn't go far enough, there's one area
> > where I think it probably goes way too far - configurability. I agree
> > it's reasonable to allow running on a subset of nodes, e.g. to split the
> > system between multiple instances etc. But do we need to configure that
> > from Postgres? Aren't people likely to already use something like
> > containers or k8 anyway?
> > I think we should just try to inherit this from
> > the environment, i.e. determine which nodes we're allowed to run, and
> > use that. Maybe we'll find we need to be smarter, but I think we caan
> > leave that for later.
>
> That's what "numa=all" is all about (take whatever is there in the
> OS/namespace)
My error, that should be: that's what "numa=AUTO" is all about (..)
-J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
2025-06-26 11:31 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-27 16:41 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-06-30 10:55 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
@ 2025-06-30 19:23 ` Tomas Vondra <[email protected]>
2025-07-01 09:04 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Tomas Vondra @ 2025-06-30 19:23 UTC (permalink / raw)
To: Jakub Wartak <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>
On 6/30/25 12:55, Jakub Wartak wrote:
> Hi Tomas!
>
> On Fri, Jun 27, 2025 at 6:41 PM Tomas Vondra <[email protected]> wrote:
>
>> I agree we should improve the behavior on NUMA systems. But I'm not sure
>> this patch does far enough, or perhaps the approach seems a bit too
>> blunt, ignoring some interesting stuff.
>>
>> AFAICS the patch essentially does the same thing as
>>
>> numactl --interleave=all
>>
>> except that it only does that to shared memory, not to process private
>> memory (as if we called numa_set_localalloc). Which means it has some of
>> the problems people observe with --interleave=all.
>>
>> In particular, this practically guarantees that (with 4K memory pages),
>> each buffer hits multiple NUMA nodes. Because with the first half will
>> do to node N, while the second half goes to node (N+1).
>>
>> That doesn't seem great. It's likely better than a misbalanced system
>> with everything allocated on a single NUMA node, but I don't see how it
>> could be better than "balanced" properly warmed up system where the
>> buffers are not split like this.
>>
>> But OK, admittedly this only happens for 4K memory pages, and serious
>> systems with a lot of memory are likely to use huge pages, which makes
>> this less of an issue (only the buffers crossing the page boundaries
>> might get split).
>>
>>
>> My bigger comment however is that the approach focuses on balancing the
>> nodes (i.e. ensuring each node gets a fair share of shared memory), and
>> is entirely oblivious to the internal structure of the shared memory.
>>
>> * It interleaves the shared segment, but it has many pieces - shared
>> buffers are the largest but not the only one. Does it make sense to
>> interleave all the other pieces?
>>
>> * Some of the pieces are tightly related. For example, we talk about
>> shared buffers as if it was one big array, but it actually is two arrays
>> - blocks and descriptors. Even if buffers don't get split between nodes
>> (thanks to huge pages), there's no guarantee the descriptor for the
>> buffer does not end on a different node.
>>
>> * In fact, the descriptors are so much smaller that blocks that it's
>> practically guaranteed all descriptors will end up on a single node.
>>
>>
>> I could probably come up with a couple more similar items, but I think
>> you get the idea. I do think making Postgres NUMA-aware will require
>> figuring out how to distribute (or not distribute) different parts of
>> the shared memory, and do that explicitly. And do that in a way that
>> allows us to do other stuff in NUMA-aware way, e.g. have a separate
>> freelists and clocksweep for each NUMA node, etc.
>
> I do understand what you mean, but I'm *NOT* stating here that it
> makes PG fully "NUMA-aware". I actually try to avoid doing so with
> each sentence. This is only about the imbalance problem specifically.
> I think we could build those follow-up optimizations as separate
> patches in this or follow-up threads. If we would do it all in one
> giant 0001 (without split) the very first question would be to
> quantify the impact of each of those optimizations (for which we would
> probably need more GUCs?). Here I'm just showing that the very first
> baby step - interleaving - helps avoid interconnect saturation in some
> cases too.
>
> Anyway, even putting the fact that local mallocs() would be
> interleaved, adjusting systemd startup scripts to just include
> `numactl --interleave=all` sounds like some dirty hack not like proper
> UX.
>
I wasn't suggesting to do "numactl --interleave=all". My argument was
simply that doing numa_interleave_memory() has most of the same issues,
because it's oblivious to what's stored in the shared memory. Sure, the
fact that local memory is not interleaved too is an improvement.
But I just don't see how this could be 0001, followed by some later
improvements. ISTM the improvements would have to largely undo 0001
first, and it would be nontrivial if an optimization needs to do that
only for some part of the shared memory.
> Also please note that:
> * I do not have lot of time to dedicate towards it, yet I was kind of
> always interested in researching that and wondering why we couldn't it
> for such long time, therefore the previous observability work and now
> $subject (note it is not claiming to be full blown NUMA awareness,
> just some basic NUMA interleave as first [well, second?] step).
Sorry, I appreciate the time you spent working on these features. It
wasn't my intention to dunk on your patch. I'm afraid this is an example
of how reactions on -hackers are often focused on pointing out issues. I
apologize for that, I should have realized it earlier.
I certainly agree it'd be good to improve the NUMA support, otherwise I
wouldn't be messing with Andres' PoC patches myself.
> * I've raised this question in the first post "How to name this GUC
> (numa or numa_shm_interleave) ?" I still have no idea, but `numa`,
> simply looks better, and we could just add way more stuff to it over
> time (in PG19 or future versions?). Does that sound good?
>
I'm not sure. In my WIP patch I have a bunch of numa_ GUCs, for
different parts of the shared memory. But that's mostly for development,
to allow easy experimentation. I don't have a clear idea what UX should
look like.
>> That's something numa_interleave_memory simply can't do for us, and I
>> suppose it might also have other downsides on large instances. I mean,
>> doesn't it have to create a separate mapping for each memory page?
>> Wouldn't that be a bit inefficient/costly for big instances?
>
> No? Or what kind of mapping do you have in mind? I think our shared
> memory on the kernel side is just a single VMA (contiguous memory
> region), on which technically we execute mbind() (libnuma is just a
> wrapper around it). I have not observed any kind of regressions,
> actually quite the opposite. Not sure what you also mean by 'big
> instances' (AFAIK 1-2TB shared_buffers might even fail to start).
>
Something as simple as giving a contiguous chunk of to each NUMA node.
Essentially 1/nodes goes to the first NUMA node, and so on. I haven't
looked into the details of how NUMA interleaving works, but from the
discussions I had about it, I understood it might be expensive. Not
sure, maybe that's wrong.
But the other reason for a simpler mapping is that it seems useful to be
able to easily calculate which NUMA node a buffer belongs to. Because
then you can do NUMA-aware freelists, clocksweep, etc.
>> Of course, I'm not saying all this as a random passerby - I've been
>> working on a similar patch for a while, based on Andres' experimental
>> NUMA branch. It's far from complete/perfect, more of a PoC quality, but
>> I hope to share it on the mailing list sometime soon.
>
> Cool, I didn't know Andres's branch was public till now, I know he
> referenced multiple issues in presentation (and hackathon!), but I
> wanted to divide it and try to get something in at least partially,
> step by step, to have at least something. I think we should
> collaborate (not a lot of people interested in this?) and I can try to
> offer my limited help if you attack those more advanced problems. I
> think we could improve this by properly ensuring that by
> over(allocating)/spreading/padding certain special regions (e.g.
> better distribute ProcArray, but what about cache hits?) - we get more
> juice, or do you want to start from scratch and re-design/re-think all
> shm allocations case by case?
>
+1 to collaboration, absolutely. I was actually planning to ping you
once I have something workable. I hope I'll be able to polish the WIP
patches a little bit and post them sometime this week.
>> FWIW while I think the patch doesn't go far enough, there's one area
>> where I think it probably goes way too far - configurability. I agree
>> it's reasonable to allow running on a subset of nodes, e.g. to split the
>> system between multiple instances etc. But do we need to configure that
>> from Postgres? Aren't people likely to already use something like
>> containers or k8 anyway?
>> I think we should just try to inherit this from
>> the environment, i.e. determine which nodes we're allowed to run, and
>> use that. Maybe we'll find we need to be smarter, but I think we caan
>> leave that for later.
>
> That's what "numa=all" is all about (take whatever is there in the
> OS/namespace), but I do not know a better way than just let's say
> numa_get_mems_allowed() being altered somehow by namespace/cgroups. I
> think if one runs on k8/containers then it's quite limited/small
> deployment and he wouldn't benefit from this at all (I struggle to
> imagine the point of k8 pod using 2+ sockets), quite contrary: my
> experience indicates that the biggest deployments are usually almost
> baremetal? And it's way easier to get consistent results. Anyway as
> You say, let's leave it for later. PG currently often is not CPU-aware
> (i.e. is not even adjusting sizing of certain structs based on CPU
> count), so making it NUMA-aware or cgroup/namespace-aware sounds
> already like taking 2-3 steps ahead into future [I think we had
> discussion at least one in LWLock partitionmanager /
> FP_LOCK_SLOTS_PER_BACKEND where I've proposed to size certain
> structures based on $VCPUs or I am misremembering this]
>
+1 to leave this for later, we can worry about this once we have it
working with the basic whole-system NUMA setups. I hope people doing
some of this would give us feedback what config they actually need.
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
2025-06-26 11:31 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-27 16:41 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-06-30 10:55 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-30 19:23 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
@ 2025-07-01 09:04 ` Jakub Wartak <[email protected]>
2025-07-01 14:02 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Jakub Wartak @ 2025-07-01 09:04 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>
On Mon, Jun 30, 2025 at 9:23 PM Tomas Vondra <[email protected]> wrote:
>
> I wasn't suggesting to do "numactl --interleave=all". My argument was
> simply that doing numa_interleave_memory() has most of the same issues,
> because it's oblivious to what's stored in the shared memory. Sure, the
> fact that local memory is not interleaved too is an improvement.
... and that's enough for me to start this ;)
> But I just don't see how this could be 0001, followed by some later
> improvements. ISTM the improvements would have to largely undo 0001
> first, and it would be nontrivial if an optimization needs to do that
> only for some part of the shared memory.
OK, maybe I'll back-off a bit to see Your ideas first. It seems you
are thinking about having multiple separate shared memory segments.
> I certainly agree it'd be good to improve the NUMA support, otherwise I
> wouldn't be messing with Andres' PoC patches myself.
Yup, cool, let's stick to that.
> > * I've raised this question in the first post "How to name this GUC
> > (numa or numa_shm_interleave) ?" I still have no idea, but `numa`,
> > simply looks better, and we could just add way more stuff to it over
> > time (in PG19 or future versions?). Does that sound good?
> >
>
> I'm not sure. In my WIP patch I have a bunch of numa_ GUCs, for
> different parts of the shared memory. But that's mostly for development,
> to allow easy experimentation.
[..]
> I don't have a clear idea what UX should look like.
Later (after research/experiments), I could still imagine sticking to
one big `numa` switch like it's today in v4-0001, but maybe with
additional 1-2 more `really_advanced_numa=stuff` (but not lots of
them, I would imagine e.g. that NUMA for analytics could be different
setup that NUMA for OLTP -- AKA do we want to optimize for
interconnect bandwidth or latency?).
> >> That's something numa_interleave_memory simply can't do for us, and I
> >> suppose it might also have other downsides on large instances. I mean,
> >> doesn't it have to create a separate mapping for each memory page?
> >> Wouldn't that be a bit inefficient/costly for big instances?
> >
> > No? Or what kind of mapping do you have in mind? I think our shared
> > memory on the kernel side is just a single VMA (contiguous memory
> > region), on which technically we execute mbind() (libnuma is just a
> > wrapper around it). I have not observed any kind of regressions,
> > actually quite the opposite. Not sure what you also mean by 'big
> > instances' (AFAIK 1-2TB shared_buffers might even fail to start).
> >
>
> Something as simple as giving a contiguous chunk of to each NUMA node.
That would actually be multiple separate VMAs/shared memory regions
(main, and specific ones for in case of NUMA - per structure) and
potentially - speculating here - slower fork()?
Related, the only complaint about memory allocated via
mmap(MAP_SHARED|MAP_HUGETLB) with NUMA, I have so far is that if the
per-zone HP free memory is too small (especially with HP=on), it
starts to spill over to the others nodes without interleaving and
without notification, you may have the same problem here unless it is
strict allocation.
> Essentially 1/nodes goes to the first NUMA node, and so on. I haven't
> looked into the details of how NUMA interleaving works, but from the
> discussions I had about it, I understood it might be expensive. Not
> sure, maybe that's wrong.
I would really like to hear the argument how NUMA interleaving is
expensive on the kernel side. It's literally bandwidth over latency. I
can buy the argument that e.g. by having dedicated mmap(MAP_SHARED)
for ProcArray[] (assuming we are HP=on/2MB), and by having a smaller
page size just for this stuct (sizeof() =~ 832b? so let's assume even
wasting 4kB per entry), could better enable kernel's NUMA
autobalancing to better relocate those necessary pages closer to the
active processes (warning: I'm making lots of assumptions, haven't
really checked memory access patterns for this struct). No idea how
bad it would be on the CPU caches too, though by making it so big here
in this theoretical context. But the easy counter argument also could
be: smaller page size = no HP available --> potentially making it
__swap-able__ and potentially causing worse dTLB hit-rates? ... and we
are just discussing a single shared memory entry and there are 73 :)
My take is that by doing it is - as an opt-in - basic interleaving is
safe, proven and and gives *more* predictable latency that without it
(of course as You mention we could do better with some allocation for
specific structures, but how do You know where CPU scheduler puts
stuff?) I think we would need to limit ourselves to just optimizing
the most crucial (hot) stuff, like ProcArray[], but probably doesn't
make sense for investigating structures like multixacts/substractions
in this attempt.
E.g. I could even imagine that we could boost standby's NUMA-awareness
too, just by putting most used memory (eg.g. XLOG) to the same NODE
that is used by startup/recovery and walreciever (by CPU pinning), not
sure is it worth the effort though in this attempt and the problem
would be: what to do with those low-level/optimized allocations after
pg_promote() to primary? In theory this quickly escalates to calling
interleave on that struct again, so maybe let's put it aside.
To sum up, my problem is that optimization possibilities are quite
endless, so we need to settle on something realistic, right?
> But the other reason for a simpler mapping is that it seems useful to be
> able to easily calculate which NUMA node a buffer belongs to. Because
> then you can do NUMA-aware freelists, clocksweep, etc.
Yay, sounds pretty advanced!
> +1 to collaboration, absolutely. I was actually planning to ping you
> once I have something workable. I hope I'll be able to polish the WIP
> patches a little bit and post them sometime this week.
Cool.
-J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
2025-06-26 11:31 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-27 16:41 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-06-30 10:55 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-30 19:23 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-07-01 09:04 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
@ 2025-07-01 14:02 ` Tomas Vondra <[email protected]>
2025-07-01 19:09 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Tomas Vondra @ 2025-07-01 14:02 UTC (permalink / raw)
To: Jakub Wartak <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>
On 7/1/25 11:04, Jakub Wartak wrote:
> On Mon, Jun 30, 2025 at 9:23 PM Tomas Vondra <[email protected]> wrote:
>>
>> I wasn't suggesting to do "numactl --interleave=all". My argument was
>> simply that doing numa_interleave_memory() has most of the same issues,
>> because it's oblivious to what's stored in the shared memory. Sure, the
>> fact that local memory is not interleaved too is an improvement.
>
> ... and that's enough for me to start this ;)
>
>> But I just don't see how this could be 0001, followed by some later
>> improvements. ISTM the improvements would have to largely undo 0001
>> first, and it would be nontrivial if an optimization needs to do that
>> only for some part of the shared memory.
>
> OK, maybe I'll back-off a bit to see Your ideas first. It seems you
> are thinking about having multiple separate shared memory segments.
>
>> I certainly agree it'd be good to improve the NUMA support, otherwise I
>> wouldn't be messing with Andres' PoC patches myself.
>
> Yup, cool, let's stick to that.
>
>>> * I've raised this question in the first post "How to name this GUC
>>> (numa or numa_shm_interleave) ?" I still have no idea, but `numa`,
>>> simply looks better, and we could just add way more stuff to it over
>>> time (in PG19 or future versions?). Does that sound good?
>>>
>>
>> I'm not sure. In my WIP patch I have a bunch of numa_ GUCs, for
>> different parts of the shared memory. But that's mostly for development,
>> to allow easy experimentation.
> [..]
>> I don't have a clear idea what UX should look like.
>
> Later (after research/experiments), I could still imagine sticking to
> one big `numa` switch like it's today in v4-0001, but maybe with
> additional 1-2 more `really_advanced_numa=stuff` (but not lots of
> them, I would imagine e.g. that NUMA for analytics could be different
> setup that NUMA for OLTP -- AKA do we want to optimize for
> interconnect bandwidth or latency?).
>
Maybe. I have no clear idea yet, but I'd like to keep the number of new
GUCs as low as possible.
>>>> That's something numa_interleave_memory simply can't do for us, and I
>>>> suppose it might also have other downsides on large instances. I mean,
>>>> doesn't it have to create a separate mapping for each memory page?
>>>> Wouldn't that be a bit inefficient/costly for big instances?
>>>
>>> No? Or what kind of mapping do you have in mind? I think our shared
>>> memory on the kernel side is just a single VMA (contiguous memory
>>> region), on which technically we execute mbind() (libnuma is just a
>>> wrapper around it). I have not observed any kind of regressions,
>>> actually quite the opposite. Not sure what you also mean by 'big
>>> instances' (AFAIK 1-2TB shared_buffers might even fail to start).
>>>
>>
>> Something as simple as giving a contiguous chunk of to each NUMA node.
>
> That would actually be multiple separate VMAs/shared memory regions
> (main, and specific ones for in case of NUMA - per structure) and
> potentially - speculating here - slower fork()?
>
I may be confused about what you mean by VMA, but it certainly does not
require creating separate shared memory segments. Interleaving also does
not require that. You can move a certain range of memory to a particular
NUMA node, and that's it.
We may end up with separate shared memory segments for different parts
of the shared memory (instead of having a single segment like now), e.g.
to support dynamic shared_buffers resizing. But even with that we'd have
a shared memory segment for each part, split between NUMA nodes.
Well, we'd probably want separate segments, because for some parts it's
not great to have 2MB pages, because it's too coarse. And you can only
use huge pages for the whole segment.
> Related, the only complaint about memory allocated via
> mmap(MAP_SHARED|MAP_HUGETLB) with NUMA, I have so far is that if the
> per-zone HP free memory is too small (especially with HP=on), it
> starts to spill over to the others nodes without interleaving and
> without notification, you may have the same problem here unless it is
> strict allocation.
>
>> Essentially 1/nodes goes to the first NUMA node, and so on. I haven't
>> looked into the details of how NUMA interleaving works, but from the
>> discussions I had about it, I understood it might be expensive. Not
>> sure, maybe that's wrong.
>
> I would really like to hear the argument how NUMA interleaving is
> expensive on the kernel side. It's literally bandwidth over latency. I
> can buy the argument that e.g. by having dedicated mmap(MAP_SHARED)
> for ProcArray[] (assuming we are HP=on/2MB), and by having a smaller
> page size just for this stuct (sizeof() =~ 832b? so let's assume even
> wasting 4kB per entry), could better enable kernel's NUMA
> autobalancing to better relocate those necessary pages closer to the
> active processes (warning: I'm making lots of assumptions, haven't
> really checked memory access patterns for this struct). No idea how
> bad it would be on the CPU caches too, though by making it so big here
> in this theoretical context. But the easy counter argument also could
> be: smaller page size = no HP available --> potentially making it
> __swap-able__ and potentially causing worse dTLB hit-rates? ... and we
> are just discussing a single shared memory entry and there are 73 :)
>
I admit I don't recall the exact details of why exactly interleaving
would be expensive on the kernel side. I've been told by smarter people
that might be the case, but I don't remember the exact explanation. And
maybe it isn't measurably more expensive ...
I've been focusing on the aspect that it makes certain things more
difficult, or even impossible ...
> My take is that by doing it is - as an opt-in - basic interleaving is
> safe, proven and and gives *more* predictable latency that without it
> (of course as You mention we could do better with some allocation for
> specific structures, but how do You know where CPU scheduler puts
> stuff?) I think we would need to limit ourselves to just optimizing
> the most crucial (hot) stuff, like ProcArray[], but probably doesn't
> make sense for investigating structures like multixacts/substractions
> in this attempt.
>
My argument is that if we allocate the structs "well" then we can do
something smart later, like pick a PGPROC placed on the current NUMA
node (at connection time), and possibly even pin it to that NUMA node so
that it doesn't get migrated.
This means not just the PROPROC itself, but also stuff like fast-path
locking arrays (which are stored separately). And interleaving could
easily place them on a different NUMA node.
> E.g. I could even imagine that we could boost standby's NUMA-awareness
> too, just by putting most used memory (eg.g. XLOG) to the same NODE
> that is used by startup/recovery and walreciever (by CPU pinning), not
> sure is it worth the effort though in this attempt and the problem
> would be: what to do with those low-level/optimized allocations after
> pg_promote() to primary? In theory this quickly escalates to calling
> interleave on that struct again, so maybe let's put it aside.
>
> To sum up, my problem is that optimization possibilities are quite
> endless, so we need to settle on something realistic, right?
>
Perhaps. But maybe we should explore the possibilities first, before
just settling out on something at the very beginning. To make an
informed decision we need to know what the costs/benefits are, and we
need to understand what the "advanced" solution might look like, so that
we don't pick a design that makes that impossible.
>> But the other reason for a simpler mapping is that it seems useful to be
>> able to easily calculate which NUMA node a buffer belongs to. Because
>> then you can do NUMA-aware freelists, clocksweep, etc.
>
> Yay, sounds pretty advanced!
>
>> +1 to collaboration, absolutely. I was actually planning to ping you
>> once I have something workable. I hope I'll be able to polish the WIP
>> patches a little bit and post them sometime this week.
>
> Cool.
>
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
2025-06-26 11:31 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-27 16:41 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-06-30 10:55 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-30 19:23 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-07-01 09:04 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-07-01 14:02 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
@ 2025-07-01 19:09 ` Tomas Vondra <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Tomas Vondra @ 2025-07-01 19:09 UTC (permalink / raw)
To: Jakub Wartak <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>
Hi Jakub,
FYI I've posted my experimental NUMA patch series here:
https://www.postgresql.org/message-id/099b9433-2855-4f1b-b421-d078a5d82017%40vondra.me
I've considered posting it to this thread, but it seemed sufficiently
different to start a new thread.
regards
--
Tomas Vondra
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NUMA shared memory interleaving
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
@ 2025-06-26 11:36 ` Jakub Wartak <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Jakub Wartak @ 2025-06-26 11:36 UTC (permalink / raw)
To: Bertrand Drouvot <[email protected]>; +Cc: Thomas Munro <[email protected]>; PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>; Tomas Vondra <[email protected]>
On Fri, Apr 18, 2025 at 7:48 PM Bertrand Drouvot
<[email protected]> wrote:
>
> Hi,
>
> On Thu, Apr 17, 2025 at 01:58:44AM +1200, Thomas Munro wrote:
> > On Wed, Apr 16, 2025 at 9:14 PM Jakub Wartak
> > <[email protected]> wrote:
> > > 2. Should we also interleave DSA/DSM for Parallel Query? (I'm not an
> > > expert on DSA/DSM at all)
> >
> > I have no answers but I have speculated for years about a very
> > specific case (without any idea where to begin due to lack of ... I
> > guess all this sort of stuff): in ExecParallelHashJoinNewBatch(),
> > workers split up and try to work on different batches on their own to
> > minimise contention, and when that's not possible (more workers than
> > batches, or finishing their existing work at different times and going
> > to help others), they just proceed in round-robin order. A beginner
> > thought is: if you're going to help someone working on a hash table,
> > it would surely be best to have the CPUs and all the data on the same
> > NUMA node. During loading, cache line ping pong would be cheaper, and
> > during probing, it *might* be easier to tune explicit memory prefetch
> > timing that way as it would look more like a single node system with a
> > fixed latency, IDK (I've shared patches for prefetching before that
> > showed pretty decent speedups, and the lack of that feature is
> > probably a bigger problem than any of this stuff, who knows...).
> > Another beginner thought is that the DSA allocator is a source of
> > contention during loading: the dumbest problem is that the chunks are
> > just too small, but it might also be interesting to look into per-node
> > pools. Or something. IDK, just some thoughts...
>
> I'm also thinking that could be beneficial for parallel workers. I think the
> ideal scenario would be to have the parallel workers spread across numa nodes and
> accessing their "local" memory first (and help with "remote" memory access if
> there is still more work to do "remotely").
Hi Bertrand, I've played with CPU pinning of PQ workers (via adjusting
postmaster pin), but I've got quite opposite results - please see
attached, especially "lat"ency against how the CPUs were assigned VS
NUMA/s_b when it was not interleaved. Not that I intend to spend a lot
of time researching PQ vs NUMA , but I've included interleaving of PQ
shm segments too in the v4 patch in the subthread nearby. Those
attached results here, were made some time ago with v1 of the patch
where PQ shm segment was not interleaved.
If anything it would be to hear if there are any sensible
production-like scenarios/workloads when dynamic_shared_memory should
be set to sysv or mmap (instead of default posix) ? Asking for Linux
only, I couldn't imagine anything (?)
-J.
On legacy 4s/NUMA simple simplest query to `select sum(aid) from pgbench_accounts_1 (single partition, 400MB)` with pgbench -c 1 client and small s_b 4GB just taking single NUMA node, gets this:
Finalize Aggregate (cost=72150.70..72150.71 rows=1 width=8) (actual time=252.817..254.409 rows=1.00 loops=1)
Output: sum(aid)
Buffers: shared hit=54798
-> Gather (cost=72150.49..72150.69 rows=2 width=8) (actual time=252.319..254.381 rows=3.00 loops=1)
Output: (PARTIAL sum(aid))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=54798
-> Partial Aggregate (cost=71150.49..71150.49 rows=1 width=8) (actual time=247.262..247.264 rows=1.00 loops=3)
Output: PARTIAL sum(aid)
Buffers: shared hit=54798
Worker 0: actual time=245.116..245.118 rows=1.00 loops=1
Buffers: shared hit=19185
Worker 1: actual time=244.797..244.800 rows=1.00 loops=1
Buffers: shared hit=19021
-> Parallel Seq Scan on public.pgbench_accounts_1 (cost=0.00..67879.99 rows=1308199 width=4) (actual time=0.011..136.895 rows=1041666.67 loop
s=3)
Output: aid, bid, abalance, filler
Buffers: shared hit=54798
Worker 0: actual time=0.010..133.393 rows=1094053.00 loops=1
Buffers: shared hit=19185
Worker 1: actual time=0.009..134.281 rows=1084465.00 loops=1
Buffers: shared hit=19021
Observe "lat"ency metric , it primary depends on the where the process are placed:
no PQ, default behavoir:
progress: 57.0 s, 2.0 tps, lat 515.312 ms stddev 0.053, 0 failed
progress: 58.0 s, 2.0 tps, lat 609.009 ms stddev 92.653, 0 failed
progress: 59.0 s, 2.0 tps, lat 515.414 ms stddev 0.423, 0 failed
progress: 60.0 s, 2.0 tps, lat 515.530 ms stddev 0.138, 0 failed
progress: 61.0 s, 2.0 tps, lat 515.370 ms stddev 0.181, 0 failed
progress: 62.0 s, 2.0 tps, lat 515.396 ms stddev 0.351, 0 failed
progress: 63.0 s, 1.0 tps, lat 515.484 ms stddev NaN, 0 failed
progress: 64.0 s, 2.0 tps, lat 515.428 ms stddev 0.035, 0 failed
progress: 65.0 s, 2.0 tps, lat 515.357 ms stddev 0.089, 0 failed
progress: 66.0 s, 2.0 tps, lat 608.885 ms stddev 92.633, 0 failed
progress: 67.0 s, 2.0 tps, lat 515.828 ms stddev 0.501, 0 failed
progress: 68.0 s, 1.0 tps, lat 603.824 ms stddev NaN, 0 failed
no PQ, manual pinning (above it flunctuates a lot due to migrations):
progress: 318.0 s, 3.0 tps, lat 321.714 ms stddev 0.089, 0 failed <-- bind backend to CPU on the same NUMA node, gets 1.4GB/s DRAM read on that whole socket/OS
progress: 319.0 s, 3.0 tps, lat 321.630 ms stddev 0.254, 0 failed
progress: 320.0 s, 3.0 tps, lat 321.655 ms stddev 0.131, 0 failed
progress: 321.0 s, 3.0 tps, lat 321.555 ms stddev 0.132, 0 failed
progress: 322.0 s, 3.0 tps, lat 322.027 ms stddev 0.964, 0 failed
progress: 323.0 s, 2.0 tps, lat 426.245 ms stddev 49.599, 0 failed
progress: 324.0 s, 2.0 tps, lat 519.873 ms stddev 0.160, 0 failed <-- bind query to CPU on different NUMA node (hits interconnect), gets only ~0.8GB/s read on that socket with s_b
progress: 325.0 s, 2.0 tps, lat 519.854 ms stddev 0.488, 0 failed
progress: 326.0 s, 2.0 tps, lat 519.604 ms stddev 0.285, 0 failed
progress: 327.0 s, 2.0 tps, lat 519.626 ms stddev 0.190, 0 failed
progress: 328.0 s, 2.0 tps, lat 520.172 ms stddev 0.064, 0 failed
progress: 329.0 s, 3.0 tps, lat 409.836 ms stddev 41.466, 0 failed
progress: 330.0 s, 3.0 tps, lat 325.236 ms stddev 0.248, 0 failed <-- bind backend to CPU on the same NUMA node again
progress: 331.0 s, 3.0 tps, lat 325.190 ms stddev 0.261, 0 failed
progress: 332.0 s, 3.0 tps, lat 325.485 ms stddev 0.751, 0 failed
progress: 333.0 s, 3.0 tps, lat 325.026 ms stddev 0.065, 0 failed
520/325 ms = 1.6x; IPC jumps from 1.23 to ~2.00 just due to switch from remote to local DRAM memory access (latency)
no PQ and numa=on (s_b interleaved), STABLE (low stddev), notice how optimal was ~321ms vs interleaved gets ~463ms
yes it is wrose (321->463ms), but we have uniform latency, instead of high variance depending on how scheduler assigns CPU:
progress: 10.0 s, 2.0 tps, lat 463.378 ms stddev 0.181, 0 failed
progress: 11.0 s, 2.0 tps, lat 463.422 ms stddev 0.016, 0 failed
progress: 12.0 s, 2.0 tps, lat 463.121 ms stddev 0.052, 0 failed
progress: 13.0 s, 2.0 tps, lat 463.198 ms stddev 0.148, 0 failed
progress: 14.0 s, 3.0 tps, lat 463.823 ms stddev 0.854, 0 failed
progress: 15.0 s, 2.0 tps, lat 462.937 ms stddev 0.199, 0 failed
ok let's enable PQ:
2 workers and leader participating, default:
progress: 30.0 s, 7.0 tps, lat 134.692 ms stddev 4.644, 0 failed
progress: 31.0 s, 8.0 tps, lat 129.489 ms stddev 1.510, 0 failed
progress: 32.0 s, 7.0 tps, lat 130.506 ms stddev 3.393, 0 failed
progress: 33.0 s, 8.0 tps, lat 133.785 ms stddev 1.921, 0 failed
progress: 34.0 s, 7.0 tps, lat 134.561 ms stddev 6.965, 0 failed
progress: 35.0 s, 8.0 tps, lat 135.438 ms stddev 5.286, 0 failed
progress: 36.0 s, 7.0 tps, lat 141.760 ms stddev 15.878, 0 failed
progress: 37.0 s, 7.0 tps, lat 136.749 ms stddev 5.937, 0 failed
progress: 38.0 s, 7.0 tps, lat 138.258 ms stddev 8.666, 0 failed
progress: 39.0 s, 7.0 tps, lat 140.263 ms stddev 8.834, 0 failed
2 workers and leader participating and postmaster CPU affinity (therefore any new pq workers) set to only CPUs from nodes having s_b + CPU-pin the leader there too:
progress: 342.0 s, 9.0 tps, lat 117.306 ms stddev 0.152, 0 failed
progress: 343.0 s, 8.0 tps, lat 117.265 ms stddev 0.216, 0 failed
progress: 344.0 s, 9.0 tps, lat 117.216 ms stddev 0.221, 0 failed
progress: 345.0 s, 9.0 tps, lat 117.233 ms stddev 0.155, 0 failed
postmaster's CPU affinity (so for 2 PQ workers) being forced to remote NUMA node:
progress: 346.0 s, 6.0 tps, lat 142.502 ms stddev 12.621, 0 failed
progress: 347.0 s, 6.0 tps, lat 168.971 ms stddev 20.585, 0 failed
progress: 348.0 s, 7.0 tps, lat 157.017 ms stddev 9.333, 0 failed
progress: 349.0 s, 6.0 tps, lat 157.810 ms stddev 15.124, 0 failed
progress: 350.0 s, 7.0 tps, lat 150.623 ms stddev 1.246, 0 failed
progress: 351.0 s, 6.0 tps, lat 156.901 ms stddev 15.012, 0 failed
2 PQ workers on the local NUMA node to s_b + leader process forced to remote NUMA node:
progress: 554.0 s, 7.0 tps, lat 130.552 ms stddev 0.256, 0 failed
progress: 555.0 s, 8.0 tps, lat 130.488 ms stddev 0.144, 0 failed
progress: 556.0 s, 8.0 tps, lat 130.795 ms stddev 0.171, 0 failed
progress: 557.0 s, 7.0 tps, lat 130.759 ms stddev 0.250, 0 failed
progress: 558.0 s, 8.0 tps, lat 130.601 ms stddev 0.217, 0 failed
progress: 559.0 s, 8.0 tps, lat 130.606 ms stddev 0.151, 0 failed
progress: 560.0 s, 7.0 tps, lat 130.503 ms stddev 0.217, 0 failed
2 PQ workers on remote NUMA node to s_b + leader process on local
progress: 627.0 s, 6.0 tps, lat 150.555 ms stddev 0.147, 0 failed
progress: 628.0 s, 7.0 tps, lat 150.675 ms stddev 0.484, 0 failed
progress: 629.0 s, 7.0 tps, lat 150.617 ms stddev 0.324, 0 failed
progress: 630.0 s, 6.0 tps, lat 150.410 ms stddev 0.087, 0 failed
progress: 631.0 s, 7.0 tps, lat 150.493 ms stddev 0.150, 0 failed
progress: 632.0 s, 7.0 tps, lat 150.566 ms stddev 0.095, 0 failed
With PQ (2 workers and leader participating) with numa=on (interleaved s_b), CPU affinity default, stddev flunctuates 2-23ms
progress: 39.0 s, 6.0 tps, lat 175.806 ms stddev 10.968, 0 failed
progress: 40.0 s, 6.0 tps, lat 172.568 ms stddev 10.002, 0 failed
progress: 41.0 s, 5.0 tps, lat 175.535 ms stddev 9.343, 0 failed
progress: 42.0 s, 6.0 tps, lat 170.761 ms stddev 6.165, 0 failed
progress: 43.0 s, 6.0 tps, lat 164.163 ms stddev 1.575, 0 failed
progress: 44.0 s, 6.0 tps, lat 175.584 ms stddev 13.247, 0 failed
progress: 45.0 s, 6.0 tps, lat 162.442 ms stddev 0.175, 0 failed
but we have 4x (number of sockets) more CPU power available at the same latency/bandwidth.
So if anything:
- latency-wise: it would be best to place leader+all PQ workers close to s_b, provided s_b fits NUMA shared/huge page memory there and you won't need more CPU than there's on that NUMA node... (assuming e.g. hosting 4 DBs on 4-sockets each on it's own, it would be best to pin everything including shm, but PQ workers too)
- capacity/TPS-wise or s_b > NUMA: just interleave to maximize bandwidth and get uniform CPU performance out of this
Attachments:
[text/plain] NUMA_pq_cpu_pinning_results.txt (8.2K, ../../CAKZiRmxYMPbQ4WiyJWh=Vuw_Ny+hLGH9_9FaacKRJvzZ-smm+w@mail.gmail.com/2-NUMA_pq_cpu_pinning_results.txt)
download | inline:
On legacy 4s/NUMA simple simplest query to `select sum(aid) from pgbench_accounts_1 (single partition, 400MB)` with pgbench -c 1 client and small s_b 4GB just taking single NUMA node, gets this:
Finalize Aggregate (cost=72150.70..72150.71 rows=1 width=8) (actual time=252.817..254.409 rows=1.00 loops=1)
Output: sum(aid)
Buffers: shared hit=54798
-> Gather (cost=72150.49..72150.69 rows=2 width=8) (actual time=252.319..254.381 rows=3.00 loops=1)
Output: (PARTIAL sum(aid))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=54798
-> Partial Aggregate (cost=71150.49..71150.49 rows=1 width=8) (actual time=247.262..247.264 rows=1.00 loops=3)
Output: PARTIAL sum(aid)
Buffers: shared hit=54798
Worker 0: actual time=245.116..245.118 rows=1.00 loops=1
Buffers: shared hit=19185
Worker 1: actual time=244.797..244.800 rows=1.00 loops=1
Buffers: shared hit=19021
-> Parallel Seq Scan on public.pgbench_accounts_1 (cost=0.00..67879.99 rows=1308199 width=4) (actual time=0.011..136.895 rows=1041666.67 loop
s=3)
Output: aid, bid, abalance, filler
Buffers: shared hit=54798
Worker 0: actual time=0.010..133.393 rows=1094053.00 loops=1
Buffers: shared hit=19185
Worker 1: actual time=0.009..134.281 rows=1084465.00 loops=1
Buffers: shared hit=19021
Observe "lat"ency metric , it primary depends on the where the process are placed:
no PQ, default behavoir:
progress: 57.0 s, 2.0 tps, lat 515.312 ms stddev 0.053, 0 failed
progress: 58.0 s, 2.0 tps, lat 609.009 ms stddev 92.653, 0 failed
progress: 59.0 s, 2.0 tps, lat 515.414 ms stddev 0.423, 0 failed
progress: 60.0 s, 2.0 tps, lat 515.530 ms stddev 0.138, 0 failed
progress: 61.0 s, 2.0 tps, lat 515.370 ms stddev 0.181, 0 failed
progress: 62.0 s, 2.0 tps, lat 515.396 ms stddev 0.351, 0 failed
progress: 63.0 s, 1.0 tps, lat 515.484 ms stddev NaN, 0 failed
progress: 64.0 s, 2.0 tps, lat 515.428 ms stddev 0.035, 0 failed
progress: 65.0 s, 2.0 tps, lat 515.357 ms stddev 0.089, 0 failed
progress: 66.0 s, 2.0 tps, lat 608.885 ms stddev 92.633, 0 failed
progress: 67.0 s, 2.0 tps, lat 515.828 ms stddev 0.501, 0 failed
progress: 68.0 s, 1.0 tps, lat 603.824 ms stddev NaN, 0 failed
no PQ, manual pinning (above it flunctuates a lot due to migrations):
progress: 318.0 s, 3.0 tps, lat 321.714 ms stddev 0.089, 0 failed <-- bind backend to CPU on the same NUMA node, gets 1.4GB/s DRAM read on that whole socket/OS
progress: 319.0 s, 3.0 tps, lat 321.630 ms stddev 0.254, 0 failed
progress: 320.0 s, 3.0 tps, lat 321.655 ms stddev 0.131, 0 failed
progress: 321.0 s, 3.0 tps, lat 321.555 ms stddev 0.132, 0 failed
progress: 322.0 s, 3.0 tps, lat 322.027 ms stddev 0.964, 0 failed
progress: 323.0 s, 2.0 tps, lat 426.245 ms stddev 49.599, 0 failed
progress: 324.0 s, 2.0 tps, lat 519.873 ms stddev 0.160, 0 failed <-- bind query to CPU on different NUMA node (hits interconnect), gets only ~0.8GB/s read on that socket with s_b
progress: 325.0 s, 2.0 tps, lat 519.854 ms stddev 0.488, 0 failed
progress: 326.0 s, 2.0 tps, lat 519.604 ms stddev 0.285, 0 failed
progress: 327.0 s, 2.0 tps, lat 519.626 ms stddev 0.190, 0 failed
progress: 328.0 s, 2.0 tps, lat 520.172 ms stddev 0.064, 0 failed
progress: 329.0 s, 3.0 tps, lat 409.836 ms stddev 41.466, 0 failed
progress: 330.0 s, 3.0 tps, lat 325.236 ms stddev 0.248, 0 failed <-- bind backend to CPU on the same NUMA node again
progress: 331.0 s, 3.0 tps, lat 325.190 ms stddev 0.261, 0 failed
progress: 332.0 s, 3.0 tps, lat 325.485 ms stddev 0.751, 0 failed
progress: 333.0 s, 3.0 tps, lat 325.026 ms stddev 0.065, 0 failed
520/325 ms = 1.6x; IPC jumps from 1.23 to ~2.00 just due to switch from remote to local DRAM memory access (latency)
no PQ and numa=on (s_b interleaved), STABLE (low stddev), notice how optimal was ~321ms vs interleaved gets ~463ms
yes it is wrose (321->463ms), but we have uniform latency, instead of high variance depending on how scheduler assigns CPU:
progress: 10.0 s, 2.0 tps, lat 463.378 ms stddev 0.181, 0 failed
progress: 11.0 s, 2.0 tps, lat 463.422 ms stddev 0.016, 0 failed
progress: 12.0 s, 2.0 tps, lat 463.121 ms stddev 0.052, 0 failed
progress: 13.0 s, 2.0 tps, lat 463.198 ms stddev 0.148, 0 failed
progress: 14.0 s, 3.0 tps, lat 463.823 ms stddev 0.854, 0 failed
progress: 15.0 s, 2.0 tps, lat 462.937 ms stddev 0.199, 0 failed
ok let's enable PQ:
2 workers and leader participating, default:
progress: 30.0 s, 7.0 tps, lat 134.692 ms stddev 4.644, 0 failed
progress: 31.0 s, 8.0 tps, lat 129.489 ms stddev 1.510, 0 failed
progress: 32.0 s, 7.0 tps, lat 130.506 ms stddev 3.393, 0 failed
progress: 33.0 s, 8.0 tps, lat 133.785 ms stddev 1.921, 0 failed
progress: 34.0 s, 7.0 tps, lat 134.561 ms stddev 6.965, 0 failed
progress: 35.0 s, 8.0 tps, lat 135.438 ms stddev 5.286, 0 failed
progress: 36.0 s, 7.0 tps, lat 141.760 ms stddev 15.878, 0 failed
progress: 37.0 s, 7.0 tps, lat 136.749 ms stddev 5.937, 0 failed
progress: 38.0 s, 7.0 tps, lat 138.258 ms stddev 8.666, 0 failed
progress: 39.0 s, 7.0 tps, lat 140.263 ms stddev 8.834, 0 failed
2 workers and leader participating and postmaster CPU affinity (therefore any new pq workers) set to only CPUs from nodes having s_b + CPU-pin the leader there too:
progress: 342.0 s, 9.0 tps, lat 117.306 ms stddev 0.152, 0 failed
progress: 343.0 s, 8.0 tps, lat 117.265 ms stddev 0.216, 0 failed
progress: 344.0 s, 9.0 tps, lat 117.216 ms stddev 0.221, 0 failed
progress: 345.0 s, 9.0 tps, lat 117.233 ms stddev 0.155, 0 failed
postmaster's CPU affinity (so for 2 PQ workers) being forced to remote NUMA node:
progress: 346.0 s, 6.0 tps, lat 142.502 ms stddev 12.621, 0 failed
progress: 347.0 s, 6.0 tps, lat 168.971 ms stddev 20.585, 0 failed
progress: 348.0 s, 7.0 tps, lat 157.017 ms stddev 9.333, 0 failed
progress: 349.0 s, 6.0 tps, lat 157.810 ms stddev 15.124, 0 failed
progress: 350.0 s, 7.0 tps, lat 150.623 ms stddev 1.246, 0 failed
progress: 351.0 s, 6.0 tps, lat 156.901 ms stddev 15.012, 0 failed
2 PQ workers on the local NUMA node to s_b + leader process forced to remote NUMA node:
progress: 554.0 s, 7.0 tps, lat 130.552 ms stddev 0.256, 0 failed
progress: 555.0 s, 8.0 tps, lat 130.488 ms stddev 0.144, 0 failed
progress: 556.0 s, 8.0 tps, lat 130.795 ms stddev 0.171, 0 failed
progress: 557.0 s, 7.0 tps, lat 130.759 ms stddev 0.250, 0 failed
progress: 558.0 s, 8.0 tps, lat 130.601 ms stddev 0.217, 0 failed
progress: 559.0 s, 8.0 tps, lat 130.606 ms stddev 0.151, 0 failed
progress: 560.0 s, 7.0 tps, lat 130.503 ms stddev 0.217, 0 failed
2 PQ workers on remote NUMA node to s_b + leader process on local
progress: 627.0 s, 6.0 tps, lat 150.555 ms stddev 0.147, 0 failed
progress: 628.0 s, 7.0 tps, lat 150.675 ms stddev 0.484, 0 failed
progress: 629.0 s, 7.0 tps, lat 150.617 ms stddev 0.324, 0 failed
progress: 630.0 s, 6.0 tps, lat 150.410 ms stddev 0.087, 0 failed
progress: 631.0 s, 7.0 tps, lat 150.493 ms stddev 0.150, 0 failed
progress: 632.0 s, 7.0 tps, lat 150.566 ms stddev 0.095, 0 failed
With PQ (2 workers and leader participating) with numa=on (interleaved s_b), CPU affinity default, stddev flunctuates 2-23ms
progress: 39.0 s, 6.0 tps, lat 175.806 ms stddev 10.968, 0 failed
progress: 40.0 s, 6.0 tps, lat 172.568 ms stddev 10.002, 0 failed
progress: 41.0 s, 5.0 tps, lat 175.535 ms stddev 9.343, 0 failed
progress: 42.0 s, 6.0 tps, lat 170.761 ms stddev 6.165, 0 failed
progress: 43.0 s, 6.0 tps, lat 164.163 ms stddev 1.575, 0 failed
progress: 44.0 s, 6.0 tps, lat 175.584 ms stddev 13.247, 0 failed
progress: 45.0 s, 6.0 tps, lat 162.442 ms stddev 0.175, 0 failed
but we have 4x (number of sockets) more CPU power available at the same latency/bandwidth.
So if anything:
- latency-wise: it would be best to place leader+all PQ workers close to s_b, provided s_b fits NUMA shared/huge page memory there and you won't need more CPU than there's on that NUMA node... (assuming e.g. hosting 4 DBs on 4-sockets each on it's own, it would be best to pin everything including shm, but PQ workers too)
- capacity/TPS-wise or s_b > NUMA: just interleave to maximize bandwidth and get uniform CPU performance out of this
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-07-01 19:09 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-04 05:51 [PATCH v9 1/7] Row pattern recognition patch for raw parser. Tatsuo Ishii <[email protected]>
2025-04-16 09:14 NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-04-18 17:43 ` Re: NUMA shared memory interleaving Bertrand Drouvot <[email protected]>
2025-06-26 11:31 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-27 16:41 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-06-30 10:55 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-30 12:02 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-06-30 19:23 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-07-01 09:04 ` Re: NUMA shared memory interleaving Jakub Wartak <[email protected]>
2025-07-01 14:02 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-07-01 19:09 ` Re: NUMA shared memory interleaving Tomas Vondra <[email protected]>
2025-06-26 11:36 ` Re: NUMA shared memory interleaving Jakub Wartak <[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