PGCon2019 - 3.4
PGCon 2019
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Adam Wołk |
|
Mariusz Zaborski |
| Schedule | |
|---|---|
| Day | Talks - Day 2 - 2019-05-31 |
| Room | DMS 1120 |
| Start time | 10:00 |
| Duration | 00:45 |
| Info | |
| ID | 1308 |
| Event type | Lecture |
| Track | Case Studies |
| Language used for presentation | English |
| Feedback | |
|---|---|
|
Did you attend this event? Give Feedback |
Let's (D)Trace Postgres
tracing the madness
There are situations where an existing database mechanism doesn't provide sufficient methods to determine the exact work being performed.
An example of such situation is GIN index rebuilds as a result of the fastupdate mechanism and slow computation of query plans.
Due to wrongly selected systems timers. With the help of DTrace, we will take a look on the Postgres internals and we show how a system administrator can clearly identify the root cause of a hanging insert and the slow computation of the query plans.
DTrace is a dynamic tracing framework created by Sun Microsystem and available in FreeBSD, NetBSD, and MacOS. Dtrace is a powerful tool which allows software developers and administrators measure performance and trace their applications using advanced scripting language - D.
Before entering the beautiful world of Postgres during this talk we will discuses the basics of it.
Gin in Posrgres stands for Generalized Inverted Index and should be considered as a genie, not a drink. Generalized means that the index does not know which operation it accelerates.
Having to do multiple searches/insertions when the same key appears in multiple heaps becomes costly during bulk insertions hence a fast update feature has been developed that maintains a pending list of entries delaying the index rebuilds in time.
Usually, the pending list is merged with the main btree by the vacuum process but it can also happen in other situations. Currently, the PostgreSQL database lacks a mechanism allowing a DBA to know when exactly this situation occurs and how long it took. This can lead to seemingly hanging INSERT and a DBA scratching his head not knowing what causes the stall.
Using DTrace we can show exactly when this rebuild happens, how long it took and how a DBA can script this in D expanding his toolbox for the future - all without rebuilding the database.