pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: digoal@126.com
Subject: BUG #16180: s_lock botteneck when load pg_stat_statements and save=off|on
Date: Mon, 30 Dec 2019 14:13:17 +0000
Message-ID: <16180-c53ce0f4aaa85596@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16180
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 12.1
Operating system:   CentOS 7.7 x64
Description:        

i use postgresql 12.1 and test load or unload pg_stat_statements. 

when pg_stat_statements.save=off, i found it also call pgss_store

```
Samples: 217K of event 'cpu-clock', 4000 Hz, Event count (approx.):
22185764626 lost: 411555/7098659 drop: 446791/8362859                       
                                                 
  Children      Self  Shared Object           Symbol                        
                                                                            
                                        
-   55.76%    55.76%  [kernel]                [k] cpuidle_enter_state       
                                                                            
                                        
   - 7.97% 0xffffffff93a000d5                                               
                                                                            
                                        
      - 55.37% 0xffffffff93a59c97                                           
                                                                            
                                        
           0xffffffff93affc2a                                               
                                                                            
                                        
           0xffffffff93a37bce                                               
                                                                            
                                        
           0xffffffff93fbf9ae                                               
                                                                            
                                        
-   29.82%     0.25%  postgres                [.] PostgresMain              
                                                                            
                                        
   - 4.16% PostgresMain                                                     
                                                                            
                                        
      - 2.00% finish_xact_command                                           
                                                                            
                                        
         - 2.02% CommitTransactionCommand                                   
                                                                            
                                        
            - 2.44% CommitTransaction                                       
                                                                            
                                        
               - 1.90% PreCommit_Portals                                    
                                                                            
                                        
                  - 1.83% PortalDrop                                        
                                                                            
                                        
                     - 1.84% PortalCleanup                                  
                                                                            
                                        
                        - 2.18% pgss_ExecutorEnd                            
                                                                            
                                        
                           - 10.32% pgss_store                              
                                                                            
                                        
                              - 10.77% s_lock                               
                                                                            
                                        
                                   1.30% perform_spin_delay                 
                                                                            
                                        
      - 1.25% exec_execute_message (inlined)                                
                                                                            
                                        
-   13.87%     0.03%  postgres                [.] CommitTransactionCommand  
                                                                            
                                        
     2.44% CommitTransactionCommand                                         
                                                                            
                                        
      - CommitTransaction                                                   
                                                                            
                                        
         - 1.90% PreCommit_Portals                                          
                                                                            
                                        
            - 1.83% PortalDrop                                              
                                                                            
                                        
               - 1.84% PortalCleanup                                        
                                                                            
                                        
                  - 2.18% pgss_ExecutorEnd                                  
                                                                            
                                        
                     - 10.32% pgss_store                                    
                                                                            
                                        
                        - 10.77% s_lock                                     
                                                                            
                                        
                             1.30% perform_spin_delay                       
                                                                            
                                        
-   13.84%     0.04%  postgres                [.] CommitTransaction         
                                                                            
                                        
   - 2.40% CommitTransaction                                                
                                                                            
                                        
      - 1.90% PreCommit_Portals                                             
                                                                            
                                        
         - 1.83% PortalDrop                                                 
                                                                            
                                        
            - 1.84% PortalCleanup                                           
                                                                            
                                        
               - 2.18% pgss_ExecutorEnd                                     
                                                                            
                                        
                  - 10.32% pgss_store                                       
                                                                            
                                        
                     - 10.77% s_lock                                        
                                                                            
                                        
                          1.30% perform_spin_delay                          
                                                                            
                                        
-   13.58%     0.01%  postgres                [.] finish_xact_command       
                                                                            
                                        
     1.99% finish_xact_command                                              
                                                                            
                                        
-   12.63%     0.02%  postgres                [.] PreCommit_Portals         
                                                                            
                                        
   - 1.87% PreCommit_Portals                                                
                                                                            
                                        
      - 1.83% PortalDrop                                                    
                                                                            
                                        
         - 1.84% PortalCleanup   
```

and performance reduce very much.

load pg_stat_statements (104core machine):

```
pgbench -M prepared -n -r -P 1 -c 104 -j 104 -T 120 -S

transaction type: <builtin: select only>
scaling factor: 1000
query mode: prepared
number of clients: 104
number of threads: 104
duration: 120 s
number of transactions actually processed: 67468127
latency average = 0.185 ms
latency stddev = 0.348 ms
tps = 562224.744452 (including connections establishing)
tps = 562300.147713 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \set aid random(1, 100000 * :scale)
         0.185  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
```

unload pg_stat_statements:

```
transaction type: <builtin: select only>
scaling factor: 1000
query mode: prepared
number of clients: 104
number of threads: 104
duration: 120 s
number of transactions actually processed: 187563515
latency average = 0.066 ms
latency stddev = 0.014 ms
tps = 1562993.591525 (including connections establishing)
tps = 1563258.811725 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \set aid random(1, 100000 * :scale)
         0.065  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
```

why pg 12 load pg_stat_statements and pg_stat_statements.save set to off
will also call pgss_store.



view thread (2+ messages)  latest in thread

Message-ID: <16180-c53ce0f4aaa85596@postgresql.org>
Permalink:  ../16180-c53ce0f4aaa85596@postgresql.org/
Also on:    postgresql.org/message-id/16180-c53ce0f4aaa85596@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, digoal@126.com
  Subject: Re: BUG #16180: s_lock botteneck when load pg_stat_statements and save=off|on
  In-Reply-To: <16180-c53ce0f4aaa85596@postgresql.org>
  References: <16180-c53ce0f4aaa85596@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox