PGCon2009 - Final Release

PGCon 2009
The PostgreSQL Conference

Michael Glaesemann
Day Talks - first day - 2009-05-21
Room DMS 1150
Start time 11:30
Duration 01:00
ID 153
Event type Lecture
Track DBA
Language used for presentation English

Visualizing Postgres

Postgres provides a variety of metrics that are available via SQL queries. Much of this useful data is transient. By tracking these metrics over time, database, relation, and index trends can be easily visualized using simple graphs and charts, which in turn can be used to assess current server performance, assist in tuning, diagnose performance issues, and anticipate potential future issues. An application for such tracking and visualization will be presented.

Postgres database, relation, and index statistics are constantly updated by the statistics collector, providing useful data though Postgres system views and functions. Additional information is updated by the ANALYZE process. By collecting this data over time, database administrators can better quantify the behavior of their database installation. In OLTP environments that have loads which vary with time, this data can be particularly useful in noting correlations between overall system performance (such as CPU idle and IOWait) and specific database objects (e.g., heap hits, index utilization, and locks on particular relations).

The raw collected data can be unwieldy on its own given its volume and time-related nature. By taking advantage of a simple web framework and Javascript functionality, one can more easily view the data and quickly see relationships between the various database objects and system performance.

A simple example of a data-collecting and viewing application will be presented along with explanations of the various statistics gathered by Postgres and how they can be used to identify database server performance issues.