Performance-Analyse einer PostgreSQL-Datenbank mit pg_stat_statements
Bei einer Webanwendung mit einer PostgreSQL-Datenbank stießen wir auf Performance-Probleme. Hier beschreibe ich, wie man Performance-Probleme in PostgreSQL mittels der Erweiterung pg_stat_statements analysieren kann. Da die Erweiterung nicht auf der Produktivdatenbank installiert ist, zeige ich, wie man eine lokale Testumgebung aufsetzt.
Überblick der Vorgehensweise
- Backup der Produktivdatenbank erstellen
- Lokale Testdatenbank mit Docker aufsetzen
- pg_stat_statements installieren und konfigurieren
- Performance-Analyse durchführen
Vorbereitung
Zuerst prüfen wir die Version der Datenbank, um die passende Docker-Umgebung zu wählen:
SELECT version(); -- PostgreSQL 13.4
Backup erstellen
Das Backup erfolgt mittels Docker, um eine konsistente Umgebung zu gewährleisten. Hier das Script backup.sh:
targetfile=backup_`date +%Y-%m-%d`.sql docker run --rm \ --network=host \ -e PGPASSWORD=PASSWORD \ postgres:13.4-bullseye \ pg_dump -h localhost -U myuser myschema > $targetfile gzip $targetfile
Die Backup-Datei wird auf den lokalen Rechner übertragen.
Lokale Testdatenbank aufsetzen
Zuerst entfernen wir eine eventuell vorhandene alte Testinstanz und starten dann einen neuen Container:
docker stop mydb-performancetest && docker rm mydb-performancetest docker run -d \ --name mydb-performancetest \ -e POSTGRES_USER=myuser \ -e POSTGRES_PASSWORD=PASSWORD \ -e POSTGRES_DB=myuser \ -p 6432:5432 \ postgres:13.4-bullseye
Verwendet wird Port 6432, um Konflikte mit einer möglicherweise laufenden lokalen PostgreSQL-Instanz zu vermeiden.
Daten importieren
Das Backup wird dann in die lokale Datenbank importiert, über das Script import.sh:
#!/bin/bash # Check if arguments were passed if [ $# -eq 0 ]; then echo "Error: No arguments provided" echo "Usage: $0 <filename>" exit 1 fi # Check if file exists if [ ! -f "$1" ]; then echo "Error: $1 is not a valid file." exit 1 fi echo "File found: $1" importfile=$(readlink -f "$1") server=localhost port=6432 username=myuser password=PASSWORD databasename=myuser echo psql -h $server -U $username -d $databasename -f $importfile docker run --rm \ --network=host \ -v $importfile:/script.sql \ -e PGPASSWORD=$password \ postgres:13.4-bullseye \ psql -h $server -p $port -U $username -d $databasename -f script.sql
pg_stat_statements installieren
Um pg_stat_statements zu aktivieren, müssen wir die PostgreSQL-Konfiguration anpassen. Dazu bearbeiten wir die postgresql.conf. Da in dem Postgres Container kein Editor enthalten ist, ex- und importieren wir die Datei, um sie bearbeiten zu können:
# Export & bearbeiten: docker cp mydb-performancetest:/var/lib/postgresql/data/postgresql.conf . ## shared_preload_libraries = 'pg_stat_statements' in einem Editor hinzufügen # Re-Import & Neustart: docker cp postgresql.conf mydb-performancetest:/var/lib/postgresql/data/postgresql.conf docker restart mydb-performancetest
Danach aktivieren wir die Erweiterung in der Datenbank:
CREATE EXTENSION pg_stat_statements;
Performance-Analyse
Nach dem Ausführen der kritischen Anwendungsfälle können wir die problematischen Queries identifizieren:
SELECT
query,
calls,
total_exec_time,
min_exec_time,
max_exec_time,
mean_exec_time,
rows,
(total_exec_time / calls) AS avg_time_per_call
FROM
pg_stat_statements
ORDER BY
total_exec_time DESC
LIMIT 10;Beispiel-Ergebnisse
Hier ein anonymisiertes Beispiel der Ausgabe:
query | calls | total_exec_time | min_exec_time | max_exec_time | mean_exec_time | rows | avg_time_per_call ----------------------------------+-------+----------------+---------------+---------------+----------------+-------+------------------- SELECT * FROM large_table WHERE... | 1500 | 350000.23 | 150.32 | 890.45 | 233.33 | 15000 | 233.33 UPDATE complex_table SET... | 500 | 180000.45 | 250.12 | 750.89 | 360.00 | 500 | 360.00
Die Ergebnisse zeigen:
- Anzahl der Aufrufe (calls)
- Gesamtausführungszeit in ms (total_exec_time)
- Minimale und maximale Ausführungszeit (min/max_exec_time)
- Durchschnittliche Ausführungszeit (mean_exec_time)
- Anzahl der betroffenen Zeilen (rows)
Besonders interessant sind Queries mit:
- Hoher Gesamtausführungszeit
- Hoher durchschnittlicher Ausführungszeit
- Großer Differenz zwischen minimaler und maximaler Ausführungszeit
- Unerwartet hoher Anzahl von Aufrufen
Reset
SELECT pg_stat_statements_reset();
Fazit
Mit dieser Methode können wir Performance-Probleme systematisch analysieren, ohne die Produktivumgebung zu beeinflussen.
Die Ergebnisse aus pg_stat_statements geben uns wichtige Hinweise, welche Queries optimiert werden sollten.
