PGCon2009 - Final Release
PGCon 2009
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Michael Glaesemann |
| Schedule | |
|---|---|
| Day | Talks - first day - 2009-05-21 |
| Room | DMS 1150 |
| Start time | 11:30 |
| Duration | 01:00 |
| Info | |
| 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.