Mittwoch, 16. November 2011

PostgreSQL und die Luftblasen in Indexen

B-Tree Indexe sind, wie der Name schon sagt, Indexe, die nach dem B-Baum Prinzip abgespeichert werden.

Details zum Thema B-Baum lassen sich bei Wikipedia nachlesen:
http://de.wikipedia.org/wiki/B-Baum

Passieren viele Veränderungen auf den Index - viele Updates auf den indizierten Spalten bzw. viele Deletes - so kann sich der Index bei PostgreSQL aufblähen.
Durch dieses Operationen kann es passieren, dass der Baum nicht mehr richtig ausbalanziert ist. Natürlich dauert es länger, einen nicht-ausbalancierten Baum zu durchsuchen, als einen ausbalancierten Baum.

Wie aber kommt es dazu?
Wie lässt es sich Monitoren?
Wie lässt es sich beheben?


Bei PostgreSQL sind die Index-Pages (Blätter) 8 kB groß.

Ist die Page voll und es kommt ein neuer Wert dazu, der in diese Page einsortiert werden müsste, so wird die Page geteilt. Wenn beim Anlegen eines Indexes alle Pages zu 100% gefüllt werden, kann es passieren, dass gleich beim ersten Wert, der neu dazukommt, es zu einer Page-Teilung kommt. Um dieses zu verhindern, lässt sich bei der Indexerstellung angeben, wie voll die Pages beim Anlegen gepackt werden dürfen. Default ist 90. Das bedeutet, die Pages werden, wenn sie angelegt werden, maximal zu 90% gefüllt. Je kleiner der Fillfactor, um so mehr Speicherplatz wird benötigt. Hier ein Beispiel mit einer einfachen Tabelle und einem Index mit 50% Füllung, einem mit den 90% aus dem Default und einem mit 100%.

CREATE TABLE t(i INTEGER, j INTEGER);
INSERT INTO t VALUES (generate_series(1,10000), random()*100+1);
CREATE INDEX i_fillfactor_default ON t(j);
CREATE INDEX i_fillfactor50 ON t(j) WITH (fillfactor=50);
CREATE INDEX i_fillfactor100 ON t(j) WITH (fillfactor=100);

SELECT
relname,
pg_size_pretty(relpages * 8 * 1024) AS size,
relpages,
reltuples
FROM pg_class
WHERE oid > 17000 ORDER BY relpages desc;

relname size relpages reltuples
t 360 kB 45 10000
i_fillfactor50 336 kB 42 10000
i_fillfactor_default 192 kB 24 10000
i_fillfactor100 176 kB 22 10000

Wie erwartet ist der Index mit nur halb gefüllten Pages ungefähr doppelt so groß wie der vollgefüllte.

Jetzt erzeugen wir mal etwas Traffic:

DELETE FROM t WHERE j > 50;
INSERT INTO t values (generate_series(10001,20000), random()*100+1);
UPDATE t SET j=103 WHERE j=47;
UPDATE t SET j=104 WHERE j=42;
DELETE FROM t WHERE j BETWEEN 23 AND 25;
INSERT INTO t values (generate_series(20001,25000), random()*100+1);

SELECT
relname,
pg_size_pretty(relpages * 8 * 1024) AS size,
relpages,
reltuples
FROM pg_class
WHERE oid > 17000 ORDER BY relpages desc;

relname size relpages reltuples
t 720 kB 90 19324
i_fillfactor100 584 kB 73 19324
i_fillfactor_default 568 kB 71 19324
i_fillfactor50 568 kB 71 19324

Zu sehen ist hier, dass die Tabelle ca. um das doppelte gewachsen ist. Von 360 kB auf 720 kB. Von vorher 10000 Zeilen auf 19324 Zeilen. Der 100% Index ist jedoch um mehr als das dreifache gewachsen und der Default-Index ebenfalls fast um das dreifache. Obwohl die Tabelle doppelt so groß geworden ist, ist der 50% Index nur um 60% gewachsen.

Um die Indexe zu bereinigen, können zwei Wege eingeschlagen werden. Zum einen kann ein REINDEX durchgeführt werden. Der Nachteil dabei ist, es wird gelockt. Während REINDEX läuft, müssen Schreibzugriffe warten. Auch Lesezugriffe, die den Index verwenden möchten, müssen warten.

PostgreSQL kennt jedoch CREATE INDEX CONCURRENTLY, bei dem weder Schreib- noch Lesezugriffe geblockt werden. Es kann hiervon jedoch nur ein Index pro Tabelle zur selben Zeit angelegt werden. Ein normaler CREATE INDEX würde jedoch auch wieder Schreibzugriffe blockieren.

Die komfortableste Lösung ist daher den Index mit CONCURRENTLY neu anzulegen, danach den alten Index zu droppen und ggf. danach noch mit Hilfe von RENAME den Namen von dem neuen Index auf den Namen von den alten Index zu setzen. Im Beispiel habe ich den 100er Index mit REINDEX bearbeitet und für die beiden anderen zwei neue Indexe erstellt:

REINDEX INDEX i_fillfactor100;
CREATE INDEX CONCURRENTLY i_fillfactor_default2 ON t(j);
CREATE INDEX CONCURRENTLY i_fillfactor_50_2 ON t(j) WITH (fillfactor=50);

Hier das Zwischenergebnis:

SELECT
relname,
pg_size_pretty(relpages * 8 * 1024) AS size,
relpages,
reltuples
FROM pg_class
WHERE oid > 17000 ORDER BY relpages desc;

relname size relpages reltuples
t 720 kB 90 19324
i_fillfactor_50_2 632 kB 79 19324
i_fillfactor_default 568 kB 71 19324
i_fillfactor50 568 kB 71 19324
i_fillfactor_default2 360 kB 45 19324
i_fillfactor100 328 kB 41 19324


Jetzt noch die alten Indexe löschen und die neuen umbenennen:

DROP INDEX i_fillfactor_default;
ALTER INDEX i_fillfactor_default2 RENAME TO i_fillfactor_default;
DROP INDEX i_fillfactor50;
ALTER INDEX i_fillfactor_50_2 RENAME TO i_fillfactor50;

SELECT
relname,
pg_size_pretty(relpages * 8 * 1024) AS size,
relpages,
reltuples
FROM pg_class
WHERE oid > 17000 ORDER BY relpages desc;

relname size relpages reltuples
t 720 kB 90 19324
i_fillfactor50 632 kB 79 19324
i_fillfactor_default 360 kB 45 19324
i_fillfactor100 328 kB 41 19324

Schön zu sehen ist, dass der 50% Index gewachsen, statt geschrumpft ist. Die beiden anderen sind jedoch extrem geschrumpft.

Fazit

Bei viel Bewegung auf der Tabelle ist es besser einen Index mit einem kleineren Fillfactor zu verwenden. Die Indexgröße und die Tabellengröße sollten beobachtet werden. Ist das Wachstum des Index erheblich größer als das Wachstum der regelmäßig und korrekt gevacuumten Tabelle, so hat sich der Index aufgebläht und eine Bereinigung ist notwendig.

Dieses und andere Themen behandeln wir in unseren Schulungen: http://www.2ndquadrant.de

Keine Kommentare:

Kommentar veröffentlichen