[Proposal] Add accumulated statistics for wait event

From: 임명규 <myungkyu(dot)lim(at)samsung(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Cc: 홍도형 <don(dot)hong(at)samsung(dot)com>, 손우성 <woosung(dot)sohn(at)samsung(dot)com>
Subject: [Proposal] Add accumulated statistics for wait event
Date: 2018-07-23 07:04:42
Message-ID: 2022793411.102298.1532329482584.JavaMail.jboss@ep1ml402
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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!











Attachment Content-Type Size
wait_event_stat_patchfile.diff application/octet-stream 19.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2018-07-23 07:06:44 Re: Memory leak with CALL to Procedure with COMMIT.
Previous Message Noah Misch 2018-07-23 07:00:54 Re: Non-portable shell code in pg_upgrade tap tests