Aufgabe 05 - Performance Tests

Aufgabe 05 - Performance Tests

Performance Test 1 - Arbeitsspeicher

Bei vielen Abfragen in PostgreSQL müssen Daten sortiert oder Zwischenergebnisse zwischengespeichert werden – zum Beispiel bei ORDER BY, DISTINCT, GROUP BY oder bei Joins. Für diese Operationen verwendet PostgreSQL den Arbeitsspeicherbereich work_mem, der pro Sortierung oder Hash-Vorgang reserviert wird.

Ist work_mem zu klein, können die benötigten Daten nicht vollständig im RAM verarbeitet werden. PostgreSQL legt dann temporäre Dateien auf der Festplatte an, was die Ausführung deutlich verlangsamen kann.

In diesem Test sollst du erkunden, wie sich die Größe von work_mem auf die Sortiergeschwindigkeit und den Ressourcenverbrauch auswirkt.

Vorbereitung

1. Neue Testdatenbank anlegen

   Öffne pgAdmin und lege mit dem Query Tool eine neue Datenbank an:

   CREATE DATABASE perf_test;

   Anschließend aktualisiere die Baumansicht mit F5 oder rechter Mausklick + Refresh.

   Nun kannst du zur neuen Datenbank perf_test wechseln (Rechtsklick → „Connect“ oder Doppelklick).

2. Testtabelle erstellen

   Erstelle eine Tabelle test_table mit 3 Mio. Datensätzen wie folgt:

   DROP TABLE IF EXISTS test_table;
   CREATE TABLE test_table AS SELECT generate_series(1,3000000) AS id, random() AS val;

   Diese Tabelle enthält 3 000 000 Zufallswerte und eignet sich gut für die nun folgenden Sortiertests.

3. Parallelisierungen ausschalten

   Sorge dafür, dass die Datenbank nicht versucht, die Ausführung der Abfrage zu parallelisieren, da sonst die Messergebnisse nicht vergleichbar wären. Führe dazu folgende Befehle aus:

   SET max_parallel_workers_per_gather = 0;
   SET max_parallel_workers = 0;
   SET max_parallel_maintenance_workers = 0;

4. Versuchsdurchführung

   Führe nun die folgende Abfrage jeweils mit unterschiedlichem work_mem-Wert aus und trage die Ergebnisse in die Tabelle ein:

   SET work_mem = '<Wert>';
   EXPLAIN ANALYZE SELECT * FROM test_table ORDER BY val;

   Nr.	work_mem	Sort Method (aus EXPLAIN)	Disk Used	Execution Time (ms)	Beobachtungen
   1	4 MB (Standard)
   2	64 MB
   3	256 MB
   4	64 kB

   Hinweis:

   • „Sort Method: external merge“ bedeutet, dass PostgreSQL Daten auf die Festplatte ausgelagert hat.
   • „quicksort“ bedeutet, dass alles im Arbeitsspeicher sortiert wurde.

   Wiederhole die Messungen mehrmals.

   Sind die Ergebnisse reproduzierbar?

   Bestimme ungefähre Mittelwerte.

   ---

   Auswertung

   Beantworte die Fragen kurz:

   1. Ab welchem work_mem-Wert beginnt PostgreSQL, Daten auf die Festplatte auszulagern?
   2. Welche Einstellung führte zur besten Performance?
   3. Warum ist work_mem nicht einfach dauerhaft sehr groß sinnvoll?
   4. Was passiert, wenn viele Benutzer gleichzeitig Abfragen mit hohem work_mem durchführen?

   ---

   Hinweis:

   • Einstellungen via SET ... = '...'; wirken nur in der aktuellen Sitzung.
   • Nach dem Schließen des Query-Tabs gelten wieder die Standardwerte.

Performance Test 2 - Sicheres/unsicheres Commit

Jede Transaktion in PostgreSQL wird erst dann als „fertig“ bestätigt, wenn die Änderungen im sogenannten Write-Ahead Log (WAL) auf der Festplatte gespeichert wurden. Damit wird sichergestellt, dass z.B. bei einem Systemcrash keine Transaktion verlorengehen kann. Dieser Sicherheitsmechanismus ist sehr wichtig aber kostet Performance. Eigentlich sollte man das nie ausschalten, aber in manchen Spezialfällen (z.B. Massenimport) kann man diesen Mechanismus gezielt vorübergehend deaktivieren, um Performance zu gewinnen.

Der Parameter synchronous_commit steuert, ob PostgreSQL auf diesen Festplatten-Flush wartet (on) oder sofort bestätigt, auch wenn die Daten nur im RAM liegen (off).

Finde nun heraus, ob und wie sich diese Einstellung auf die Performance auswirkt.

Versuchsdurchführung

1. Erstelle eine Testtabelle:

   DROP TABLE IF EXISTS test_table2;
   CREATE TABLE test_table2 (id serial PRIMARY KEY, val text);

2. Zeige den aktuellen Modus an:

   SHOW synchronous_commit;

   Standardwert ist normalerweise on.

3. Aktiviere den sicheren Modus:

   SET synchronous_commit = on;

4. Führe nun den ersten Test aus:

   INSERT INTO test_table2 (val)
   SELECT md5(random()::text)
   FROM generate_series(1,1000000); # 1 Mio. Inserts

   Lies nach dem Ausführen im pgAdmin Query Tool unten in der Statusleiste die Laufzeit und notiere die Zeit.

5. Aktiviere den schnellen Modus:

   SET synchronous_commit = off;

6. Führe denselben Insert-Befehl erneut aus.

   Lies wieder die Laufzeit in der Statusleiste ab und notiere die Zeit.

Zu welchen Messergebnissen bist du gekommen?

Wiederhole die Messungen mehrmals, und bestimme ungefähre Mittelwerte.

Hinweis:

„In dieser Testumgebung wird der Zeitunterschied möglicherweise nur klein sein – wichtig ist das Verständnis des Sicherheitsunterschieds, nicht die genaue Millisekunden-Zahl.“

Abgabe

• Dokumentation mit:
  • Messergebnissen (Tabelle)
  • Antworten zu den Fragen inkl. Auffälligkeiten und Erkenntnissen