Skip to content

“Ich brauche mehr Details” – PostgreSQL Performance Reports mit pg_profile

Wer Oracle Datenbanken kennt, schätzt das integrierte Automatic Workload Repository (AWR) und die Reports die man damit erstellen kann. Etwas Vergleichbares existiert für PostgreSQL mit dem Namen pg_profile.

Ein Jeder DBA kennt das Problem, über Kurz oder Lang kommt es auf einer Datenbank zu Performance Problemen. Bei Oracle gehört es mit zu den Ersten Tätigkeiten, einen sogenannten AWR Report über einen Problemzeitraum zu erstellen. Schade, dass es für PostgreSQL nicht etwas vergleichbares direkt in die Datenbank integriertes Feature gibt. Aber mit der Erweiterung pg_profile (Github Repository) haben wir zumindest die Option, eine solche Funktionalität nachträglich in unsere PostgreSQL Datenbank zu integrieren.

Ähnlich wie AWR Reports bezieht sich pg_profile dabei auf sogenannte “Samples” (in AWR Snapshots gennant) und kann über ein Sample-Paar einen Report mit den Performance Metriken der Datenbank erstellen. Die Ausgabe kann in HTML erfolgen und ich somit gut lesbar. Für einen ersten Ausgangspunkt bei einer Performance Analyse, kommt ein solcher Report sehr gelegen.

Zu Beginn laden wir uns das Paket mit der aktuellen Version von pg_profile herunter und legen das Archiv auf unserem PostgreSQL Datenbanksystem ab. Das Archiv wird entpackt und in das extension Verzeichnis der PostgreSQL Software abgelegt. Dies erreichen wir mit folgendem Befehl (als root).

export PATH=/usr/pgsql-13/bin/:$PATH   #um pg_config in den Pfad aufzunehmen
tar xzf pg_profile-.tar.gz --directory $(pg_config --sharedir)/extension

Um ausreichend Daten sammeln zu können, muss die PostgreSQL Instanz mit ein paar Parametern konfiguriert werden. Diese sind in die postgresql.conf einzutragen und die Instanz muss im Anschluss neu gestartet werden.

track_activities = on #default
track_counts = on     #default
track_io_timing = on
track_functions = all

pg_profile benötigt für eine optimale Funktion die beiden Erweiterungen dblink und pg_stat_statements. Beide Erweiterungen sind im Paket postgresql<VERSION>-contrib enthalten. Sollte das nicht installiert sein, können wir es über den jeweiligen Paketmanager unter Linux installieren. Im Anschluss binden wir diese Erweiterungen in unsere Datenbank ein.

sudo yum install postgresql13-contrib

postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE EXTENSION pg_profile;

In meinem Fall musste ich nun noch die Erweiterung pg_stat_statements über den shared_preload_libraries Parameter in der postgresql.conf laden. Ein erneuter Neustart war notwendig.

shared_preload_libraries = 'pg_stat_statements'

Jetzt können wir pg_profile schon benutzen und uns Samples manuell über psql oder z.B. über die Crontab zeitgesteuert erzeugen lassen. Eine Regelmäßige Erstellung von Samples macht hier durchaus Sinn, denn man weiß ja im seltensten Fall genau, wann ein Performance Problem auftreten wird. Hier ein Beispiel in meiner Crontab (des postgres Benutzers) mit dem ich alle 15 Minuten ein Sample erzeugen lasse.

*/15 * * * *   psql -c 'SELECT take_sample()' > /dev/null 2>&1

Es empfiehlt sich, keine zu großen Zeiträume zwischen den Samples zu wählen. Der Grund dafür ist, dass eventuelle Problematische Statements über die Zeit “herausgeglättet” werden.

Ein manuell erstelltes Sample gibt mir folgende Ausgabe zurück.

postgres=# SELECT * from take_sample();
       take_sample
 (local,OK,00:00:00.31)
 (1 row)

Die Erweiterung hat einige Standard Einstellungen, die dafür sorgen, dass nicht unendlich lange Samples aufbewahrt werden. Im Standard werden 7 Tage an Samples vorgehalten. Dies kann durch den Parameter pg_profile.max_sample_age in der postgresql.conf gesteuert werden. Um sich nun die vorhandenen Samples anzeigen zu lassen, können wir folgenden Befehl nutzen.

postgres=# SELECT * from show_samples();
  sample |      sample_time       | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
 --------+------------------------+-----------------+---------------+----------------+-----------------
       3 | 2021-07-05 22:16:28+02 | t               |               |                |
       4 | 2021-07-05 22:24:38+02 | t               |               |                |

Wollen wir uns nun einen Report über zwei Samples im HTML Format erstellen, nutzen wir folgenden Befehl.

psql -qtc "SELECT get_report(1,2)" --output /tmp/report_postgres_1_2.html

Und fertig ist der Report. Hier ein Screenshot aus einem Report über zwei Samples, zwischen denen ich einen kleinen Stresstest mit pg_bench gefahren habe.

Für die Interpretation des Reports und der Metriken empfiehlt es sich, die Dokumentation von pg_profile zu studieren. pg_profile bietet hier noch einiges mehr. So kann ich auch Samples von entfernten Datenbanken erstellen und auf einer zentralen Datenbank ablegen usw. Auch hier hilft die Dokumentation weiter.

Philip

Leave a Reply

Your email address will not be published. Required fields are marked *