Beyond PostgreSQL – Erweiterungen und Tools für Monitoring und Performance-Analyse
Die ersten beiden Teilen dieser Artikelreihe lieferten Einblicke in die Erweiterbarkeit von PostgreSQL und beschrieben Erweiterungen und Tools für Administration und Allgemeines.
Im dritten Teil dieser Artikelserie rund um die Erweiterbarkeit von PostgreSQL steht der Bereich Monitoring und Performance-Analyse im Vordergrund. Erfahrungsgemäß ist besonders in diesem Segment der Bedarf an unterstützenden Werkzeugen sehr hoch. Die folgenden Beispiele sollen bei der Suche und Auswahl einer passenden Erweiterung oder Tools helfen. Es werden ausschließlich Open-Source-Beispiele gezeigt. Entsprechende Links zu den Quellen befinden sich am Ende des Artikels.
Kommerzielle Datenbanksysteme, wie z. B. Oracle mit dem "Diagnostic and Tuning Pack", bringen häufig sehr umfangreiche und nützliche Pakete mit Tools, Funktionen und Analyse-Möglichkeiten für die Analyse und Behebung von Performance-Problemen mit.
Bei PostgreSQL wird ein Datenbank-Administrator im Alltag genauso viel mit Performance-Problemen zu tun haben, wie es bei anderen Datenbanksystemen auch der Fall ist. Die Analyse solcher Probleme ausschließlich mit dem Lesen und Bewerten von Ausführungsplänen der SQL-Statements auszuführen ist häufig nicht der praktikabelste Weg. Die folgenden Tools und Erweiterungen bringen dem Administrator weitere Analyse-Möglichkeiten.
Extension pg_stat_statements
Die im contrib-Paket befindliche Erweiterung pg_stat_statements ist für viele PostgreSQL-Administratoren die Basis für alle Analysen von Performance-Problemen [1]. Es wird empfohlen, diese Erweiterung standardmäßig bei jedem Datenbank-Cluster mit zu installieren.
Ist die Extension im Datenbank-Cluster erst mal aktiviert, werden ab sofort für jede SQL-Abfrage Informationen über die Anzahl der Aufrufe, Laufzeiten, Speichernutzung, verwendete Datensätze, Planungszeiten, etc. gesammelt.
Insbesondere unter Berücksichtigung der Aufrufanzahl und den entsprechenden minimalen und maximalen Laufzeiten hat man so einen sehr schnellen Ansatzpunkt, was genau gerade im System langsam ist. Bei unpräzisen Fehlermeldungen hat man so überhaupt erst eine Chance, problematische Abfragen zu identifizieren und zu analysieren. Im Listing 1 befindet sich ein einfaches Beispiel einer Abfrage und Ausgabe der pg_stat_statements-View, die mit der Extension mitgeliefert wird.
Listing 1: Beispiel-Ausgabe von pg_stat_statements
# select
substring (query, 1, 50)as short_query,
round(total_exec_time)as total_exec_time, calls,
round(mean_exec_time)as mean_exec_time,
round(100 * total_exec_time /(selectsum(total_exec_time)from pg_stat_statements))as percentage
from
pg_stat_statements
order by percentage desc;
short_query | total_exec_time | calls | mean_exec_time | percentage
----------------------------------------------------+-----------------+-------+----------------+------------
UPDATE pgbench_branches SET bbalance = bbalance + | 7114 | 1500 | 5 | 63
UPDATE pgbench_tellers SET tbalance = tbalance + $ | 2506 | 1500 | 2 | 22
copy pgbench_accounts from stdin | 664 | 1 | 664 | 6
UPDATE pgbench_accounts SET abalance = abalance + | 194 | 1500 | 0 | 2
alter table pgbench_accounts add primary key (aid) | 193 | 1 | 193 | 2
vacuum analyze pgbench_accounts | 138 | 1 | 138 | 1
…Über weitere Parameter kann Einfluss darauf genommen werden, was genau gesammelt wird. Sollen zum Beispiel auch die Planungszeiten von Statements erfasst werden, muss dies aktiviert werden.
Mit einer Funktion pg_stat_statements_reset() kann man die gesammelten Informationen löschen. Dies kann neben wartungstechnischen Gründen auch sinnvoll sein, wenn zum Beispiel eine Analyse mit einem sauberen Stand durchgeführt werden soll und man gezielt nur einen bestimmten Zeitraum in den gesammelten Daten sehen möchte.
Die Extension pg_stat_statements ist nicht nur nützlich, sondern wird auch von diversen Tools aus dem Bereich Monitoring und Management vorausgesetzt. Diese Tools verwenden die gesammelten Daten für diverse Anzeigen oder Statistiken.
Extension pg_wait_sampling
Eine sinnvolle Ergänzung zu pg_stat_statements ist die Erweiterung pg_wait_sampling [2]. Die Betrachtung der reinen Laufzeiten von Queries ist der erste Ansatz bei der Analyse. Aber häufig ist die Information wichtig, wo am Ende die Zeit verbraucht wurde. Handelt es sich um reine Prozessor-Zeit, dann fährt man mit der Analyse auf Query-Ebene fort. Aber eventuell liegt das Problem doch woanders. Für diese Analyse hat PostgreSQL ähnlich wie zum Beispiel bei Oracle-Datenbanken auch so genannte Wait Events, die in der Laufzeit dokumentieren, was ein Prozess gerade macht oder besser gesagt, warum er es nicht macht und buchstäblich wartet. Diese Information ist erst einmal flüchtig und kann immer nur als Momentaufnahme betrachtet werden.
Auch hier geht die Extension pg_wait_sampling den Weg wie pg_stat_statements und sammelt diese Informationen, so dass diese im Nachgang zur Analyse verwendet werden können [3].
Diese Analyse findet mit Hilfe von mehreren Views statt. Zum Beispiel können mit der View pg_wait_sampling_history die kompletten gesammelten Daten aller Wait Events pro Prozess und Query mit dem jeweiligen Zeitstempel abgefragt werden. Diese Information kann dann mit sinnvollen Abfragen für weitere Auswertungen verwendet werden.
In der Praxis ist aber die View pg_wait_sampling_profile interessant, in der die Wait Events pro Prozess und in Kombination mit pg_stat_statements auch pro Query summiert aufgezeigt werden. Wurde beispielsweise ein Performance-Problem von einem speziellen Client gemeldet, kann so gezielt nach diesem Client-Prozess gesucht und die Wait-Event-Situation untersucht werden. Im Beispiel vom Listing 1 sieht man eine beispielhafte Ausgabe, wo beim Prozess mit der PID 3300169 eine auffällig gehäufte Anzahl von Buffer-File-Read- und Buffer-File-Write-Wait-Events gelistet ist. Bei diesem Beispiel hat sich herausgestellt, dass bei der Konfiguration des work_mem-Parameters etwas zu sparsam dimensioniert wurde. Eine Erhöhung würde dieses Problem lösen und die Situation wieder beschleunigen.
Listing 2: Beispiel-Ausgabe von pg_wait_sampling
postgres=# select
pid,
event_type,
event,countfrom
pg_wait_sampling_profile
where
pid = 3300169;
pid | event_type | event |count
--------+------------+----------------+-------------
3300169 | IO | DataFileRead | 63
3300169 | IO | BufFileWrite | 3620
3300169 | IO | BufFileRead | 1931
3300169 | IO | DataFileExtend | 21
3300169 | LWLock | WALWrite | 3
3300169 | IO | DataFileWrite | 1
3300169 | IO | WALWrite | 1
3300169 | IO | WALSync | 1
3300169 | LWLock | WALBufMapping | 1
3300169 | Client | ClientRead | 1
…Vergleichbar zu pg_stat_statements werden auch hier standardmäßig bis zu 5.000 Einträge gesammelt. Ältere Einträge fallen entsprechend mit der Zeit raus. Es gibt auch Konfigurationsmöglichkeiten über diese Menge, sowie weitere Einstellungen, wie beispielsweise die Sampling-Rate, in der pg_wait_sampling die Informationen sammelt. Der Standard ist 10 Millisekunden. Wenn dies nicht präzise genug ist, muss man hier kleinere Zeiten wählen, wobei typischerweise immer ein Auge auf die Gesamtperformance geworfen werden muss. Wenn zu viel gesammelt wird, kann dies natürlich auch ein System ausbremsen.
Mit pg_wait_sampling und pg_stat_statements hat man ein solides Grundgerüst, um die Performance-Situation bei PostgreSQL zu überprüfen. Es ist zu empfehlen, diese beiden Extensions standardmäßig zu installieren, um auch akut und präventiv bei Problemen untersuchen zu können, ohne dass die Situation reproduziert werden muss.
Extension pg_profile
Die Möglichkeiten für nachhaltige Analysen auszubauen und zu strukturieren hat die Extension pg_profile weitergedacht [4]. Wer beim Einsatz von Oracle-Datenbanken schon einmal mit Statspack oder dem Automatic Workload Repository (AWR) in Berührung gekommen ist, kennt damit bereits einen sehr guten Ansatz.
Ähnlich, wie bei Oracle AWR, arbeitet pg_profile mit einem Repository, in dem Snapshots von der Performance-Situation eines Datenbank-Clusters gesammelt werden. Hier wird explizit auch auf bekannte Extensions – wie zum Beispiel pg_stat_statements – gebaut. Eine Integration von pg_wait_sampling ist bisher nicht erfolgt, aber die Idee reift noch in den Gedanken der Entwickler.
Die Idee ist, basierend auf diesen zeitlichen Snapshots Reports zu erzeugen, die Situationen innerhalb von zwei Zeitpunkten darstellen. Diese Reports sind in diesem Fall übersichtliche HTML-Dateien mit Informationen zum Cluster bzw. zu den Datenbanken allgemein, dann über diverse Last-Statistiken und dann eine Auflistung von diversen Top-Listen der Queries, z. B. nach CPU-Zeit, nach Aufrufen, etc.
Diese Reports kann man auch differentiell ausführen und direkt zwei Zeiträume vergleichen [5]. So werden in einem Report zwei Zeiträume gleichzeitig dargestellt, so dass man sich ein Bild über die Unterschiede machen kann. Dies ist besonders nützlich bei der Gegenüberstellung einer zeitlich klar eingrenzbaren Problemsituation mit einer üblichen Betriebslast.
In Abb. 1 werden Ausschnitte eines solchen Reports abgebildet. Es ist ein Beispiel von der Webseite der Entwickler. Es ist empfehlenswert, sich diese Beispiele vorher anzuschauen. Die Links befinden sich am Ende des Artikels.
Tool pgtop
Als Nutzer von Unix/Linux-Systemen sollte der Befehl top zweifellos bekannt sein und bestimmt den Alltag zur Analyse der Systemlast. Daran orientiert, hat das Tool pgtop eine ähnliche Anzeige entwickelt, die aber im gleichen Stil nur die entsprechenden Prozesse und Lastdaten zu den PostgreSQL-Clustern anzeigt [6]. Zusätzlich erhält man noch weitere datenbank-spezifische Informationen zu I/O-Aktivitäten, Hitraten, etc. (s. Abb. 2).
Beim Aufruf muss der Datenbank-Cluster spezifiziert werden oder es werden die Standardwerte verwendet.
Tool pg_activity
Ähnlich wie pgtop zeigt pg_activity die aktuelle Prozess-Situation des spezifizierten Datenbank-Clusters an [7]. Der Unterschied hier ist, dass sich auf die Client-Prozesse beschränkt wird und diese dafür detaillierter dargestellt werden. So werden jeweils Angaben zu den Schreibraten, dem CPU- und Memory-Verbrauch, mehr Details zum Client-Prozess, etc. angezeigt. Die Anzeige ist auch hier in Echtzeit und zeigt nur die aktuelle Situation an.
Die Tools pg_top und pg_activtiy sind beide nützliche Werkzeuge für eine akute und temporäre Echtzeitüberwachung. Insbesondere wenn kein anderes Monitoring vorhanden ist oder aktuell nur die Kommandozeile verfügbar ist, kann so bei bestimmten Aktivitäten sinnvoll und äußerst schlank Abhilfe geschaffen werden.
Wenn man detailliert überwachen will, müssen die Tools auch unter der Haube von PostgreSQL schauen können.
Tool PGWATCH
Umfangreiches und nachhaltiges Monitoring ist bei größeren Datenbank-Umgebungen dennoch eine wichtige Voraussetzung. Es gibt auf dem Markt eine große Zahl an Monitoring-Lösungen und viele können auch grundlegende nützliche Dinge von PostgreSQL überwachen. Aber wenn man auch detailliert und unter Anbetracht von datenbank-performance-relevanten Aspekten überwachen will, müssen die Tools auch unter der Haube von PostgreSQL schauen können. Am Ende muss ersichtlich sein, was genau langsam ist und nicht einfach nur die Tatsache, dass etwas langsam ist.
Ein gutes Beispiel für ein geeignetes Monitoring-Tool speziell für PostgreSQL ist PGWATCH von Cybertec [8]. Es ist quelloffen und baut auf etablierte Plattformen, wie z. B. Grafana, auf. In Abb. 4 sieht man eine Beispiel-Ansicht des Standard-Dashboards. Man kann sich vor dort aus in die unterschiedlichen Bereiche navigieren und zeitlich genau untersuchen und überwachen. Die Quelle dieses Beispiels ist eine öffentlich nutzbare Demo vom Entwickler. Damit kann ein erster Eindruck über die Bedienung und die verschiedenen Ansichten gewonnen werden.
Insbesondere die leichte Integration durch Open Source und bekannte Grundlagen sowie der immense Detailgrad machen dieses Tool empfehlenswert für die Überwachung von größeren PostgreSQL-Umgebungen. Der Hauptentwickler Cybertec bietet dazu kommerziellen Support an.
Fazit
Gerade beim Thema Performance-Analyse ist es essenziell, dass die genannten Tools und Extensions zumindest in Erwägung gezogen werden. Ich würde sogar einen Schritt weitergehen und Extensions, wie z. B. pg_stat_statements und pg_wait_sampling standardmäßig mit installieren und aktivieren. Nur so hat man präventiv eine Chance, solche Probleme zu analysieren. Grundsätzlich ist hier auch die Auswahl an Tools und Extensions sehr umfangreich, so dass man das passende Werkzeug selbst aussuchen kann.
Im folgenden Artikel der Serie werden die Extensions und Tools zum Thema Backup, Recovery und Hochverfügbarkeit behandelt.
Demnächst:
Teil 4 - Erweiterungen und Tools für Backup, Recovery und Hochverfügbarkeit
Teil 5 - Erweiterungen und Tools für Security und Auditing
- Github: pg_stat_statements
- Github: pg_wait_sampling
- PostgreSQL: The Statistics Collector
- Github: pg_profile
- Github: Report Examples
- PostgreSQL top Project Home Page
- Github: pg_activity
- pgwatch
pgwatch Demo
Github: pgwatch2
