Speicherplatz für PostgreSQL-Tabellen richtig nutzen

Alles ein Abwasch

Obwohl immer wieder Datensätze gelöscht werden, wächst die Datenbank unaufhörlich? Das ist kein Mysterium, sondern liegt daran, dass PostgreSQL freigegebenen Speicherplatz nicht automatisch wiederverwendet. Stattdessen obliegt es dem Anwender, für ein effizientes Speichermanagement zu sorgen.
Mit E-Mail-Diensten muss sich jeder Administrator früher oder später einmal beschäftigen. Das zur CeBIT erscheinende ADMIN 02/2012 gibt dazu Praxis-Tipps und ... (mehr)

Würde man in einer Datenbank immer nur nacheinander lesen oder schreiben, gäbe es das Problem nicht, und frei gewordener Speicher ließe sich umgehend wieder neu einsetzen. Doch so strikt sequenziell möchte niemand arbeiten, denn das würde unnötig Zeit kosten Zeit, die paralleles Lesen und Schreiben spart. Die Technik dafür heißt MVCC, Multi Version Concurrency Control. Allerdings kann es damit passieren, dass eine Partei einen Datensatz lesen will, den eine andere im Begriff ist zu ändern. Was soll die Datenbank dann tun?

PostgreSQL behilft sich damit, dass es solange die alten Werte zurückgibt, bis der schreibende Anwender die Transaktion committed hat und die Änderungen endgültig gespeichert wurden. Noch komplexer wird das Ganze, wenn mehrere Anwender zeitgleich auf derselben Zeile schreiben. Die Konsequenz aus alldem ist jedenfalls, dass die Datenbank den Speicherplatz gelöschter oder geänderter Datensätze nicht sofort überschreiben darf, sondern die alte Version mindestens bis zum Commit aufbewahren muss.

Nach dem Commit sind die alten Versionen – man spricht jetzt von toten Tupeln – nutzlos. Die Datenbank muss sie aber noch explizit recyclen. Solange das nicht erledigt ist, muss sie für neue Daten neuen Speicherplatz auf der Festplatte belegen.

Die Beispiel-Kneipe

Das Verhalten der Datenbank lässt sich gut mit dem Prozedere an einem Tresen vergleichen. Zurückkommende Gläser, egal ob ausgetrunken oder nicht, gleichen den toten Tupeln. Bevor sie wiederverwendet werden können, müssen sie gespült werden. Je länger mit dem Spülen gewartet wird, desto mehr Gläser braucht der Umlauf. Das Spülen übernimmt bei PostgreSQL der Befehl VACUUM (kommt von staubsaugen).

Das soll ein konkretes Beispiel demonstrieren. Dazu ist eine Tabelle »bier« nötig, die zunächst 9000 Datensätze enthalten soll:

CREATE TABLE bier(bestellnummer integer, inhalt text);
INSERT INTO bier(bestellnummer, inhalt) VALUES(generate_series(1,9000), 'Bier');

Der Wirt hat Kellner, die den Gästen das Bestellte an den Tisch bringen. Übertragen auf PostgreSQL ist der Wirt das Datenbanksystem, und die Angestellten sind die User, die SQL-Befehle wie INSERT, UPDATE oder DELETE ausführen. Der Chef des Wirtes ist der Administrator. Der Administrator kann ohne Probleme selbst einsehen, wie viele Bestellungen die Gäste aufgegeben haben:

SELECT count(*) as bestelltFROM bier;bestellt
-----------
 9000

Bierkonsum analysiert

Der Administrator möchte aber vom Wirt mehr wissen: Wie viele Paletten an Biergläsern wurden dafür in Umlauf gebracht? Um diese Frage zu beantworten, muss sich der Wirt erst einen Überblick verschaffen. Den Überblick verschafft sich PostgreSQL mit dem SQL-Befehl »ANALYZE« (funktioniert auch in britischer Schreibweise: »ANALYSE« ). »ANALYZE« sorgt dafür, dass die Statistiken auf den aktuellen Stand gebracht werden. Ohne einen Überblick zu haben, nimmt PostgreSQL an, es sei noch gar nichts passiert (Listing 1).

Listing 1

Vor dem Start

01 SELECT relname, reltuples, relpages,
02 pg_size_pretty(relpages * 8 * 1024) AS size,
03 CASE WHEN relpages = 0 THEN 0
04 ELSE floor(reltuples / relpages)
05 END AS avg
06 FROM pg_class
07 WHERE relname = 'bier';
08
09  relname | reltuples | relpages |  size   | avg
10 ---------+-----------+----------+---------+-----
11  bier    |         0 |        0 | 0 bytes |   0

Wird »ANALYZE« ohne weitere Angaben gestartet, wertet es alle Tabellen der Datenbank aus. Will man es nur für eine Tabelle oder gar nur für bestimmte Spalten einer Tabelle ausführen, dann sind der Tabellenname und gegebenenfalls die Spaltennamen mit anzugeben: »ANALYZE bier;« Jetzt hat der Wirt den Überblick (Listing 2).

Listing 2

Überblick

01 SELECT relname, reltuples, relpages,
02 pg_size_pretty(relpages * 8 * 1024) AS size,
03 CASE WHEN relpages = 0 THEN 0
04 ELSE floor(reltuples / relpages)
05 END AS avg
06 FROM pg_class
07 WHERE relname = 'bier';
08
09  relname | reltuples | relpages |  size  | avg
10 ---------+-----------+----------+--------+-----
11  bier    |      9000 |       45 | 360 kB | 200

PostgreSQL nutzt zur Speicherung 8 KByte große Pages. Wie viele Datensätze in eine solche Page passen, hängt von der Art der Tabelle und den verwendeten Datentypen ab. Werden variable Datentypen (etwa »VARCHAR« oder »TEXT« ) benutzt, dann kann die Anzahl der Datensätze pro Page variieren. Es gibt auch Sondermechanismen (»TOAST« ) für den Fall, dass ein einzelner Datensatz größer als 8 KByte ist. Darauf wird dieser Artikel aber nicht weiter eingehen.

Im Beispiel lässt sich eine Page als eine Palette Gläser vorstellen, die in Umlauf gebracht wurde. Der Wirt kann damit antworten, dass Gläser von 45 Paletten in Umlauf sind. Die Spalte »relpages« gibt die Anzahl der im Umlauf befindlichen Pages an. Um das Rechnen zu erleichtern, kennt PostgreSQL die Funktion »pg_pretty_size(Byte-Anzahl)« . Ein Kilobyte sind 1024 Byte. Da Pages immer 8 KByte groß sind, multipliziert die Funktion die Spalte »relpages« mit 8 mal 1024. Da der Wert der Spalte »reltuple« mit der tatsächlichen Anzahl der Datensätze in der Tabelle übereinstimmt, kann man davon ausgehen, dass die Statistiken auf dem aktuellen Stand sind. Ein Datensatz ist ein Tupel.

Es könnte nützlich sein, auch die durchschnittliche Anzahl an Datensätzen pro Page zu beobachten. Die Funktion »floor(Wert)« rundet ein Ergebnis immer ab. Da PostgreSQL zu Recht streikt, wenn durch null geteilt werden soll, fängt »case« diesen Fall ab (Listing 3).

Listing 3

Vor Aktualisierung und …

01 SELECT relname, reltuples, relpages,
02 pg_size_pretty(relpages * 8 * 1024) AS size,
03 CASE WHEN relpages = 0 THEN 0
04 ELSE floor(reltuples / relpages)
05 END AS avg
06 FROM pg_class
07 WHERE relname = 'bier';
08
09  relname | reltuples | relpages |  size  | avg
10 ---------+-----------+----------+--------+-----
11  bier    |      9000 |       45 | 360 kB | 200

Die ersten 5000 Gläser Bier sind getrunken und die Bestellung abgehakt. Sie werden aus der Datenbank gelöscht:

DELETE FROM bier WHERE bestellnummer<=5000;

Wie viele laufende Bestellungen gibt es jetzt noch?

SELECT count(*) as bestellt FROM bier;
 bestellt
-----------
 4000

Jetzt befragt der Anwender wieder die Statistiken (Listing 3).

Der Vergleich mit dem Ergebnis von »reltuple« und »count(*)« lässt sofort erkennen, dass die Statistiken noch nicht aktualisiert sind. Das holt Listing 4 nach.

Listing 4

…nach Aktualisierung der Statistik

01 ANALYZE bier;
02
03 SELECT relname, reltuples, relpages,
04 pg_size_pretty(relpages * 8 * 1024) AS size,
05 CASE WHEN relpages = 0 THEN 0
06 ELSE floor(reltuples / relpages)
07 END AS avg
08 FROM pg_class
09 WHERE relname = 'bier';
10
11  relname | reltuples | relpages |  size  | avg
12 ---------+-----------+----------+--------+-----
13  bier    |      4000 |       45 | 360 kB |  88

Die Tabelle ist nicht kleiner geworden. Verglichen mit dem vorherigen Ergebnis kann man darauf schließen, dass es eine Menge toter Tupel geben muss – eine Menge schmutziger Gläser.

Ohne »VACUUM« (ohne Spülen der Gläser) gehen nochmals 1000 Bestellungen ein, wie Listing 5 zeigt.

Listing 5

Weitere Bestellungen

01 INSERT INTO bier(bestellnummer, inhalt)
02 VALUES(generate_series(9001, 10000), 'Bier');
03
04 ANALYZE bier;
05
06 SELECT relname, reltuples, relpages,
07 pg_size_pretty(relpages * 8 * 1024) AS size,
08 CASE WHEN relpages = 0 THEN 0
09 ELSE floor(reltuples / relpages)
10 END AS avg
11 FROM pg_class
12 WHERE relname = 'bier';
13
14  relname | reltuples | relpages |  size  | avg
15 ---------+-----------+----------+--------+-----
16  bier    |      5000 |       50 | 400 kB | 100

Die Tabelle ist gewachsen. Der Speicher der toten Tupel wurde also nicht wiederverwendet, stattdessen kamen neue Pages hinzu. Im Beispiel würde das bedeuten: Anstatt die schmutzigen Gläser abzuwaschen und wieder zu befüllen, wurden Gläser von neuen Paletten benutzt.

comments powered by Disqus

Artikel der Woche

OpenSSL

Als Protokoll ist SSL/TLS täglich im Einsatz. Mozilla und Google haben kürzlich erklärt, dass mehr als 50 Prozent der HTTP-Verbindungen verschlüsselt aufgebaut werden. Googles Chrome soll ab 2017 sogar vor unverschlüsselten Verbindungen warnen, wenn Sie dort sensible Daten eingeben können. In unserem Security-Tipp werfen wir einen Blick hinter die Kulissen von OpenSSL und zeigen dessen vielfältige Möglichkeiten auf. (mehr)
Einmal pro Woche aktuelle News, kostenlose Artikel und nützliche ADMIN-Tipps.
Ich habe die Datenschutzerklärung gelesen und bin einverstanden.

Linux-Distro

Welche Linux-Distribution setzen Sie vorwiegend auf dem Server ein?

  • Arch Linux
  • CentOS
  • Debian
  • Fedora
  • openSUSE
  • Oracle Linux
  • Red Hat Enterprise Linux
  • SUSE Linux Enterprise Server
  • Ubuntu
  • andere Linux-Distribution
  • FreeBSD
  • OpenBSD

Google+

Ausgabe /2017

Microsite