track_planning causing performance regression - Mailing list pgsql-hackers

Hi,

During fully-cached SELECT-only test using pgbench, Postgres v13Beta1 shows
~45% performance drop [2] at high DB connection counts (when compared with v12.3)

Disabling pg_stat_statements.track_planning (which is 'On' by default)
brings the TPS numbers up to v12.3 levels.

The inflection point (in this test-case) is 128 Connections, beyond which the
TPS numbers are consistently low. Looking at the mailing list [1], this issue
didn't surface earlier possibly since the regression is trivial at low connection counts.

It would be great if this could be optimized further, or track_planning
disabled (by default) so as to not trip users upgrading from v12 with pg_stat_statement
enabled (but otherwise not particularly interested in track_planning).

These are some details around the above test:

pgbench: scale - 100 / threads - 16
test-duration - 30s each
server - 96 vCPUs / 768GB - r5.24xl (AWS EC2 instance)
client - 72 vCPUs / 144GB - c5.18xl (AWS EC2 instance) (co-located with the DB server - Same AZ)
v12 - REL_12_STABLE (v12.3)
v13Beta1 - REL_13_STABLE (v13Beta1)
max_connections = 10000
shared_preload_libraries = 'pg_stat_statements'
shared_buffers 128MB


Reference:
1) https://www.postgresql.org/message-id/1554150919882-0.post%40n3.nabble.com

2) Fully-cached-select-only TPS drops >= 128 connections.

Conn      v12.3          v13Beta1        v13Beta1 (track_planning=off)
1         6,764          6,734            6,905
2         14,978         14,961           15,316
4         31,641         32,012           36,961
8         71,989         68,848           69,204
16        129,056        131,157          132,773
32        231,910        226,718          253,316
64        381,778        371,782          385,402
128       534,661  ====> 353,944          539,231
256       636,794  ====> 248,825          643,631
512       574,447  ====> 213,033          555,099
768       493,912  ====> 214,801          502,014
1024      484,993  ====> 222,492          490,716
1280      480,571  ====> 223,296          483,843
1536      475,030  ====> 228,137          477,153
1792      472,145  ====> 229,027          474,423
2048      471,385  ====> 228,665          470,238


3) perf - v13Beta1

-   88.38%     0.17%  postgres      postgres               [.] PostgresMain
   - 88.21% PostgresMain
      - 80.09% exec_simple_query
         - 25.34% pg_plan_queries
            - 25.28% pg_plan_query
               - 25.21% pgss_planner
                  - 14.36% pgss_store
                     + 13.54% s_lock
                  + 10.71% standard_planner
         + 18.29% PortalRun
         - 15.12% PortalDrop
            - 14.73% PortalCleanup
               - 13.78% pgss_ExecutorEnd
                  - 13.72% pgss_store
                     + 12.83% s_lock
                 0.72% standard_ExecutorEnd
         + 6.18% PortalStart
         + 4.86% pg_analyze_and_rewrite
         + 3.52% GetTransactionSnapshot
         + 2.56% pg_parse_query
         + 1.83% finish_xact_command
           0.51% start_xact_command
      + 3.93% pq_getbyte
      + 3.40% ReadyForQuery



4) perf - v12.3

v12.3
-   84.32%     0.21%  postgres      postgres               [.] PostgresMain
   - 84.11% PostgresMain
      - 72.56% exec_simple_query
         + 26.71% PortalRun
         - 15.33% pg_plan_queries
            - 15.29% pg_plan_query
               + 15.21% standard_planner
         + 7.81% PortalStart
         + 6.76% pg_analyze_and_rewrite
         + 4.37% GetTransactionSnapshot
         + 3.69% pg_parse_query
         - 2.96% PortalDrop
            - 2.42% PortalCleanup
               - 1.35% pgss_ExecutorEnd
                  - 1.22% pgss_store
                       0.57% s_lock
                 0.77% standard_ExecutorEnd
         + 2.16% finish_xact_command
         + 0.78% start_xact_command
         + 0.59% pg_rewrite_query
      + 5.67% pq_getbyte
      + 4.73% ReadyForQuery

-
robins