Aufgabe 05 - Performance Tests

Aufgabe 05 - Performance Tests

Worum geht es?

In dieser Übung wird experimentell gemessen, wie zwei Konfigurationsparameter die Performance beeinflussen: work_mem (Speicher für Sortierungen) und synchronous_commit (Warten auf das Write-Ahead-Log) (siehe Kapitel 3 - Performance). Im Expertenteil werden die Ergebnisse mit EXPLAIN und einer Dauerhaftigkeits-Abwägung erklärt.

Was Sie dafür brauchen

• Ein PostgreSQL-Server und pgAdmin oder psql.
• Kapitel 3 - Performance sowie der WAL-Abschnitt aus Kapitel 6 - Physische Organisation.

Welche Kompetenzen Sie erwerben und zeigen

• Sie führen reproduzierbare Messreihen durch und deuten EXPLAIN ANALYZE.
• Sie erkennen, wann PostgreSQL Sortierungen auf die Platte auslagert.
• Sie messen den Effekt von synchronous_commit auf Schreiblasten.
• Sie begründen die Abwägung zwischen Geschwindigkeit und Dauerhaftigkeit.

Pädagogische Einordnung

• Reproduktion: die Testumgebung aufbauen (Teil A).
• Reorganisation und Transfer: Messreihen durchführen und auswerten (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: Pläne lesen und Abwägungen begründen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Teil A - Testumgebung vorbereiten

1. Eine Datenbank perf_test anlegen und zu ihr wechseln.

2. Eine Testtabelle mit 3 Mio. Zufallswerten erstellen:

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

3. Die Parallelisierung ausschalten, damit die Messungen vergleichbar sind:

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

Teil B - work_mem-Messreihe

1. Die folgende Abfrage mit verschiedenen work_mem-Werten ausführen und je Zeile Sortiermethode, Disk Used und Ausführungszeit notieren:

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

   Nr.	work_mem	Sort Method	Disk Used	Zeit (ms)
   1	4 MB (Standard)
   2	64 MB
   3	256 MB
   4	64 kB

   external merge bedeutet Auslagerung auf die Platte, quicksort bedeutet Sortierung im RAM. Die Messungen mehrmals wiederholen und Mittelwerte bilden.

2. Auswerten: Ab welchem work_mem beginnt das Auslagern? Welche Einstellung war am schnellsten? Warum ist ein dauerhaft sehr großes work_mem nicht sinnvoll? Was passiert, wenn viele Nutzer gleichzeitig Abfragen mit hohem work_mem fahren?

Teil C - synchronous_commit

1. Eine Testtabelle anlegen und den aktuellen Modus anzeigen:

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

2. Mit SET synchronous_commit = on; eine Million Zeilen einfügen und die Zeit notieren:

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

3. Mit SET synchronous_commit = off; denselben Insert wiederholen und die Zeit vergleichen. Mehrmals messen.

Teil D - Expertenteil: Pläne lesen und Abwägungen begründen

1. Umschlagpunkt bestimmen: Aus der Messreihe von Teil B den Punkt bestimmen, ab dem EXPLAIN ANALYZE von external merge auf quicksort wechselt. Mit EXPLAIN (ANALYZE, BUFFERS) zeigen, wie viele temporäre Blöcke bei zu kleinem work_mem geschrieben werden, und in zwei bis drei Sätzen erklären, warum die Platte hier der Flaschenhals ist.
2. Mehrbenutzer-Rechnung: work_mem gilt pro Sortier- oder Hash-Vorgang und pro Sitzung. Rechnen Sie überschlägig aus, wie viel Speicher im schlimmsten Fall belegt wird, wenn 50 Verbindungen gleichzeitig eine Abfrage mit zwei Sortierungen und work_mem = 256 MB fahren. Was folgt daraus für die Konfiguration eines Servers?
3. Dauerhaftigkeit abwägen: Erklären Sie an einem konkreten Crash-Szenario (Stromausfall unmittelbar nach dem Commit), was bei synchronous_commit = off verloren gehen kann und was nicht (Konsistenz bleibt, die letzten Transaktionen können fehlen). Nennen Sie einen Fall, in dem off vertretbar ist (etwa ein wiederholbarer Massenimport), und einen, in dem es niemals erlaubt wäre (etwa Zahlungsverkehr).

Wissenscheck

1. Wozu dient work_mem, und was passiert, wenn er zu klein ist?
2. Woran erkennt man in EXPLAIN ANALYZE, dass PostgreSQL auf die Platte ausgelagert hat?
3. Warum ist ein dauerhaft sehr großes work_mem riskant?
4. Was steuert synchronous_commit, und was ist der Preis von off?
5. Warum bleibt die Datenbank bei synchronous_commit = off konsistent, obwohl Transaktionen verloren gehen können?

Abgabe

• Eine Dokumentation mit den Messtabellen, den Antworten zu den Auswertungsfragen und der Abwägung aus Teil D.

---

HTL Villach, 2025-2026,
https://www.htl-villach.at