[Proposal] Add accumulated statistics for wait event

Hello hackers,

This proposal is about recording additional statisticsofwait events.

PostgreSQL statistics Issue

----------------------------------------

The pg_stat_activity view is very useful in analysis for performance issues.

But it is difficult to get information of wait events in detail,

when you need to deep dive into analysis of performance.

It is because pg_stat_activity just shows the current wait status of backend.

If PostgreSQL provides additional statistics about wait events,

it will be helpful in pinpointing the exact cause of throughput issue.

Proposal

----------------------------------------

Record additional statistics items per wait event for every backend.

- total elapsed time to wait

- max elapsed time to wait

- number of times being waited

I suggest storing the above statistics in the pgBackendStatus structure.

typedef struct PgBackendStatus

{

...

/*

* proc's wait_event additional information.

* each wait_events elapsed time count.

*/

TimestampTz st_wait_event_start_timestamp;

uint64 st_wait_event_total_elapsed[NUM_WAIT_EVENT];

uint64 st_wait_event_max_elapsed[NUM_WAIT_EVENT];

uint32 st_wait_event_counting[NUM_WAIT_EVENT];

}

PoC test

----------------------------------------

I wrote a prototype patch.

With this patch, you can get additional wait event stats via

the new procedure ‘pg_stat_get_wait_events()’.

You can test by following steps.

1. apply git patch

- patch -p0 wait_event_stat_patchfile.diff

2. make distclean

3. configure --with-wait-event-detail

4. make make install

5. start postgreSQL and execute psql

6. using pg_stat_get_wait_events(null) function

- input parameter is pid.

display example

postgres=# select * from pg_stat_get_wait_events(null) where counting 0;

pid | wait_event_type | wait_event | total_elapsed | max_elapsed | counting

-------+-----------------+-----------------------+---------------+-------------+----------

25291| LWLock | WALBufMappingLock |1359|376|6

25291| LWLock | WALWriteLock |679733|113803|8

25291| IO | BufFileRead |780|7|172

25291| IO | BufFileWrite |1247|19|171

25291| IO | DataFileExtend |44703|53|3395

25291| IO | DataFileImmediateSync |268798|72286|12

25291| IO | DataFileRead |91763|22149|30

25291| IO | WALSync |441139|60456|28

25291| IO | WALWrite |9567|637|737

24251| LWLock | WALBufMappingLock |1256|350|6

24251| LWLock | WALWriteLock |649140|153994|7

24251| IO | BufFileRead |620|9|172

24251| IO | BufFileWrite |1228|20|171

24251| IO | DataFileExtend |26884|51|3395

24251| IO | DataFileImmediateSync |208630|21067|12

24251| IO | DataFileRead |426278|17327|128

24251| IO | WALSync |307055|70853|24

24251| IO | WALWrite |17935|961|2720

(18rows)

etc. concept proposal

------------------------------------------

1. I allocated arrays for additional statistics per wait event.

Normally the backend doesn’t use all wait events.

So the size of memory used for recording statistics can be reduced

by allocating one hash list as memory pool for statistics of wait events.

2. This feature can be implemented as extension

if some hooks were provided in following functions,

- pgstat_report_wait_start

- Pgstat_report_wait_end

Feedback and suggestion will be very welcome.

Thanks!