track_planning causing performance regression

Tharakan, Robins <tharar@amazon.com>

From: "Tharakan, Robins" <tharar@amazon.com>
To: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2020-06-29T05:48:35Z
Lists: 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



Commits

  1. doc: Fix description about pg_stat_statements.track_planning.

  2. doc: Add note about possible performance overhead by enabling track_planning.

  3. Change default of pg_stat_statements.track_planning to off.