PGCon2017 - 20180510

PGCon 2017
The PostgreSQL Conference

Rahila Syed
Day Talks - Day 2 - 2017-05-26
Room DMS 1160
Start time 16:00
Duration 00:45
ID 1072
Event type Lecture
Track Hacking
Language used for presentation English

PostgreSQL Monitoring Enhancements

How long before a utility command finishes and what additions can be made to existing wait events?

Although PostgreSQL monitoring has been improved with infrastructure to measure progress of long running utility commands and wait events to identify bottlenecks in the system, it can be enhanced further. This presentation will discuss how the existing infrastructure in PostgreSQL 9.6 can be used to estimate progress of more utility commands and to detect the lag in database operations using additional wait events.

PostgreSQL 9.6 adds a generic progress reporting feature. It creates capabilities for a utility command to expose the target relation which it is working on and certain other counters to indicate its progress. This facility has been used by VACUUM command to report its progress to the user. The presentation will contain a brief explanation of the generic progress reporting feature. It will be followed by analysis of how it can be used in future by other utility commands like CREATE INDEX, REINDEX to report progress. It will involve details of how an estimation of progress can be made for such utility commands and how it can be tracked via this feature.

PostgreSQL 9.6 also adds infrastructure for wait events. Wait events can be used to report the reason for lag in backend process from code. Currently the wait events can be classified as heavyweight lock, lightweight lock, buffer pin, IPC, timeout etc. More wait events need to be added to provide details of the disk I/O and network events at which the backend is waiting . This presentation will give information about the existing and new wait events that can be added to provide detailed information on why the operations are slow in your database. It will list in detail different wait events that can be added to provide information about I/O and network activities of the backend.