For quick performance troubleshooting, here is a simple script that reads 150 samples from pg_stat_activity, every 100 milliseconds during 15 seconds, to sum the Active Session History per wait event and time buckets, like this:
I create the temporary tables to store the samples:
-- create temporary views and table to store samples
drop table if exists temp_ash;
drop view if exists temp_psa;
create temporary view temp_psa
as select now() as ts, * from pg_stat_activity;
create temporary table temp_ash
as select * from psa where false;
I gather the 150 samples using \watch in psql:
-- take samples every 100ms during 15s
insert into temp_ash
select * from temp_psa
\watch interval=0.1 count=150
-- ^^^ ^^^
-- frequency(s) samples
I query the samples, using \crosstabview in psql:
-- query and group into 5 buckets ( = every 3 seconds for 15s gathered)
select ev as "state wait_event_type : wait_event"
, bucket, to_char(count(*)/count(distinct ts),'99999D9') from (
select ev, ts, to_char(max(ts)over(partition by bucket),'HH24:MI:SS') bucket from (
select ts
, format('%14s %-30s %10s:%-15s',backend_type, state, wait_event_type,wait_event) ev
, ntile(5) over(order by ts) bucket from ash
-- ^
-- time buckets columns
) x ) x group by ev, bucket order by 3 desc,ev, bucket
\crosstabview
This gives the following result:
state wait_event_type : wait_event | 19:24:16 | 19:24:19 | 19:24:07 | 19:24:13 | 19:24:10
--------------------------------------------------------------------------+----------+----------+----------+----------+----------
client backend idle Client:ClientRead | 83.0 | 83.0 | 82.0 | 80.0 | 79.0
: | 4.0 | 4.0 | 4.0 | 4.0 | 4.0
client backend active IO:DataFileRead | 2.0 | 3.0 | 2.0 | 3.0 | 2.0
client backend active LWLock:buffer_mapping | 1.0 | 2.0 | | |
client backend active : | 1.0 | 1.0 | 1.0 | 1.0 | 1.0
client backend active LWLock:buffer_io | 1.0 | 1.0 | 1.0 | 1.0 | 1.0
client backend idle in transaction : | | 1.0 | | |
client backend idle in transaction (aborted) Client:ClientRead | | | 1.0 | 1.0 | 1.0
client backend idle in transaction Client:ClientRead | 1.0 | 1.0 | 1.0 | 1.0 | 1.0
(9 rows)
The Amazon Aurora documentation has a great description of PostgreSQL wait events. In the example above Client:ClientRead is the idle backend waiting from something to do (Oracle DBAs will recognize SQL*Net message from client) so I have mostly idle users here and a few ones are within a transaction. The numbers are the average number of session in this state during the time bucket.
Of course, you can gather samples multiple times, with different frequency, and change the number of columns (in ntile())
Top comments (0)