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_loop LOOP 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
