pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
BUG #16180: s_lock botteneck when load pg_stat_statements and save=off|on 2+ messages / 2 participants [nested] [flat]
* BUG #16180: s_lock botteneck when load pg_stat_statements and save=off|on
@ 2019-12-30 14:13 PG Bug reporting form <noreply@postgresql.org>
2019-12-30 14:34 ` Re: BUG #16180: s_lock botteneck when load pg_stat_statements and save=off|on Sergei Kornilov <sk@zsrv.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2019-12-30 14:13 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: digoal@126.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16180: s_lock botteneck when load pg_stat_statements and save=off|on
2019-12-30 14:13 BUG #16180: s_lock botteneck when load pg_stat_statements and save=off|on PG Bug reporting form <noreply@postgresql.org>
@ 2019-12-30 14:34 ` Sergei Kornilov <sk@zsrv.org>
0 siblings, 0 replies; 2+ messages in thread
From: Sergei Kornilov @ 2019-12-30 14:34 UTC (permalink / raw)
To: digoal@126.com <digoal@126.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hello
> when pg_stat_statements.save=off, i found it also call pgss_store
I think you need pg_stat_statements.track = none
Option save means "Save pg_stat_statements statistics across server shutdowns."
regards, Sergei
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-12-30 14:34 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-12-30 14:13 BUG #16180: s_lock botteneck when load pg_stat_statements and save=off|on PG Bug reporting form <noreply@postgresql.org> 2019-12-30 14:34 ` Sergei Kornilov <sk@zsrv.org>
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