Schedule - PGConf.EU 2019
Performance analysis at full power
Date: 2019-10-16
Time: 09:50–10:40
Room: Manzoni
Level: Intermediate
PostgreSQL is getting more and more mature, and used in bigger environments. But at the same time, it's becoming more and more complex to administer as new features and optimizations are added, and it's sometimes hard to diagnose performance issues. For example, access to operating system level metrics, query usage statistics, and various information about internal events (like waits or settings changes) used to be very hard to retrieve and analyze. Fortunately, new performance metrics and statistic views are also regularly added, either in the core or through third party extensions.
This talk will present several of these extensions, explaining what information they give access to, and how they can be used to dig into bottleneck you can experience in production. The extensions this presentation will focus on are:
- pg_stat_statements (statistics on query usage)
- pg_stat_kcache (statistics on Disk / CPU usage)
- pg_wait_sampling (statistics on wait events)
- pg_qualstats (statistics on WHERE/JOIN clauses utilization by queries)
- pg_track_settings (track configuration changes)
- hypopg (hypothetical indexes)
In addition, PoWA will be presented as an example of combining them and exploit their power to the full. This is a tool that regularly stores the metrics provided by those extensions in a space efficient format, and a web application that can represent all the gathered metrics on different dashboards with a drill-down approach, and which can also automatically analyze the data to suggest optimizations, such as missing indexes. But each extension, and others, can of course be used independently to do performance analysis.
Slides
The following slides have been made available for this session: