Re: Improving connection scalability: GetSnapshotData() - Mailing list pgsql-hackers

Hi,

On 2020-04-06 06:39:59 -0700, Andres Freund wrote:
> These benchmarks are on my workstation. The larger VM I used in the last
> round wasn't currently available.

One way to reproduce the problem at smaller connection counts / smaller
machines is to take more snapshots. Doesn't fully reproduce the problem,
because resetting ->xmin without xact overhead is part of the problem,
but it's helpful.

I use a volatile function that loops over a trivial statement. There's
probably an easier / more extreme way to reproduce the problem. But it's
good enough.

-- setup
CREATE OR REPLACE FUNCTION snapme(p_ret int, p_loop int) RETURNS int VOLATILE LANGUAGE plpgsql AS $$BEGIN FOR x in
1..p_loopLOOP EXECUTE 'SELECT 1';END LOOP; RETURN p_ret; END;$$;
 
-- statement executed in parallel
SELECT snapme(17, 10000);

before (all above 1.5%):
+   37.82%  postgres  postgres          [.] GetSnapshotData
+    6.26%  postgres  postgres          [.] AllocSetAlloc
+    3.77%  postgres  postgres          [.] base_yyparse
+    3.04%  postgres  postgres          [.] core_yylex
+    1.94%  postgres  postgres          [.] grouping_planner
+    1.83%  postgres  libc-2.30.so      [.] __strncpy_avx2
+    1.80%  postgres  postgres          [.] palloc
+    1.73%  postgres  libc-2.30.so      [.] __memset_avx2_unaligned_erms

after:
+    5.75%  postgres  postgres          [.] base_yyparse
+    4.37%  postgres  postgres          [.] palloc
+    4.29%  postgres  postgres          [.] AllocSetAlloc
+    3.75%  postgres  postgres          [.] expression_tree_walker.part.0
+    3.14%  postgres  postgres          [.] core_yylex
+    2.51%  postgres  postgres          [.] subquery_planner
+    2.48%  postgres  postgres          [.] CheckExprStillValid
+    2.45%  postgres  postgres          [.] check_stack_depth
+    2.42%  postgres  plpgsql.so        [.] exec_stmt
+    1.92%  postgres  libc-2.30.so      [.] __memset_avx2_unaligned_erms
+    1.91%  postgres  postgres          [.] query_tree_walker
+    1.88%  postgres  libc-2.30.so      [.] __GI_____strtoll_l_internal
+    1.86%  postgres  postgres          [.] _SPI_execute_plan
+    1.85%  postgres  postgres          [.] assign_query_collations_walker
+    1.84%  postgres  postgres          [.] remove_useless_results_recurse
+    1.83%  postgres  postgres          [.] grouping_planner
+    1.50%  postgres  postgres          [.] set_plan_refs


If I change the workload to be
BEGIN;
SELECT txid_current();
SELECT snapme(17, 1000);
COMMIT;


the difference reduces (because GetSnapshotData() only needs to look at
procs with xids, and xids are assigned for much longer), but still is
significant:

before (all above 1.5%):
+   35.89%  postgres  postgres            [.] GetSnapshotData
+    7.94%  postgres  postgres            [.] AllocSetAlloc
+    4.42%  postgres  postgres            [.] base_yyparse
+    3.62%  postgres  libc-2.30.so        [.] __memset_avx2_unaligned_erms
+    2.87%  postgres  postgres            [.] LWLockAcquire
+    2.76%  postgres  postgres            [.] core_yylex
+    2.30%  postgres  postgres            [.] expression_tree_walker.part.0
+    1.81%  postgres  postgres            [.] MemoryContextAllocZeroAligned
+    1.80%  postgres  postgres            [.] transformStmt
+    1.66%  postgres  postgres            [.] grouping_planner
+    1.64%  postgres  postgres            [.] subquery_planner

after:
+   24.59%  postgres  postgres          [.] GetSnapshotData
+    4.89%  postgres  postgres          [.] base_yyparse
+    4.59%  postgres  postgres          [.] AllocSetAlloc
+    3.00%  postgres  postgres          [.] LWLockAcquire
+    2.76%  postgres  postgres          [.] palloc
+    2.27%  postgres  postgres          [.] MemoryContextAllocZeroAligned
+    2.26%  postgres  postgres          [.] check_stack_depth
+    1.77%  postgres  postgres          [.] core_yylex

Greetings,

Andres Freund