REINDEX Transaktion füllt Datenbank Partition

Wir haben hier ein ziemlich spezielles Problem mit Postgresql. Ganz kurz zur Historie:

  • auf einigen tausend Systemen läuft Postgresql 8.2.x unter CentOS5.2
  • Einige dieser Systeme (mehrere hundert) hatten Wärme Probleme durch verdreckte Lüfter und ähnliches und hängten sich unter hoher Last auf
  • Einmal in der Wochen findet ein REINDEX und ein VACUUM FULL / ANALYSE unserer Datenbank statt
  • der REINDEX findet innerhalb einer Transaktion statt (Tabelle für Tabelle)
  • einige System konnten diese Transaktion nicht zu ende abarbeiten, da durch Überhitzung der CPU die Maschine eingefroren ist
  • Der temporär belegte Speicherplatz für die REINDEX Transaktion (ca. 1GB) wird nicht wieder freigegeben auch VACUUM FULL oder REINDEX “arts” helfen hier nicht weiter
  • Die Hardwareprobleme sind durch diverse Einsätze beseitigt worden
  • Das Problem mit den stark gefüllten Datenbank Partitionen wurde jedoch erst spät erkannt

Nun folgten Untersuchungen in unserem Labor. Als Resultat kann man abschließend sagen:

Wenn man auf einer beliebigen Postgresql Version (Test mit 9.2.x) eine Transaktion startet, anschließend eine Reihe von REINDEX “table x” Kommandos absetzt und dann aus irgendeinem Grund der Postgresql Dienst gekillt wird (z.B. “killall -9 postmaster”). Dann geht der bis hierhin benötigte Speicherplatz in der Datenbank verloren. Keine mir bekannten Wartungstools können Ihn wieder freigeben. Einzig “pg_dump” / “drop” / “pg_restore” stellt die Datenbank in der ursprünglichen Größe wieder her.

Nun zur eigentlichen Frage: gibt es einen anderen Weg als DUMP / RESTORE den Speicherplatz dieser abgebrochenen Transaktionen wieder frei zu geben?

Ich bin für jeden Hinweis dankbar.

Einige Tausend? Cool!

Uncool: 8.2 ist out-of-lifetime, und das schon recht lange. 8.3 auch.

  • Einige dieser Systeme (mehrere hundert) hatten Wärme Probleme durch verdreckte Lüfter und ähnliches und hängten sich unter hoher Last auf

Aua. Kein Monitoring der Temperatur?


  • Einmal in der Wochen findet ein REINDEX und ein VACUUM FULL / ANALYSE unserer Datenbank statt
  • der REINDEX findet innerhalb einer Transaktion statt (Tabelle für Tabelle)
  • einige System konnten diese Transaktion nicht zu ende abarbeiten, da durch Überhitzung der CPU die Maschine eingefroren ist
  • Der temporär belegte Speicherplatz für die REINDEX Transaktion (ca. 1GB) wird nicht wieder freigegeben auch VACUUM FULL oder REINDEX “arts” helfen hier nicht weiter
  • Die Hardwareprobleme sind durch diverse Einsätze beseitigt worden
  • Das Problem mit den stark gefüllten Datenbank Partitionen wurde jedoch erst spät erkannt

Nun folgten Untersuchungen in unserem Labor. Als Resultat kann man abschließend sagen:

Wenn man auf einer beliebigen Postgresql Version (Test mit 9.2.x) eine Transaktion startet, anschließend eine Reihe von REINDEX “table x” Kommandos absetzt und dann aus irgendeinem Grund der Postgresql Dienst gekillt wird (z.B. “killall -9 postmaster”). Dann geht der bis hierhin benötigte Speicherplatz in der Datenbank verloren. Keine mir bekannten Wartungstools können Ihn wieder freigeben. Einzig “pg_dump” / “drop” / “pg_restore” stellt die Datenbank in der ursprünglichen Größe wieder her.

Index löschen und neu erstellen? CREATE INDEX CONCURRENTLY minimiert Schmerzen dabei, braucht aber Zeit. Ich weiß aber jetzt nicht, ob das 8.2 schon kann. VACUUM FULL arbeitet in aktuellen Versionen auch deutlich besser als auf Deinem Oldtimer.

Nun zur eigentlichen Frage: gibt es einen anderen Weg als DUMP / RESTORE den Speicherplatz dieser abgebrochenen Transaktionen wieder frei zu geben?

Ich bin für jeden Hinweis dankbar.

Es wäre, verbunden mit dem Update auf eine nicht so massiv ranzig riechende Version wohl die beste Option.

Nachtrag:
http://wiki.postgresql.org/wiki/Index_Maintenance

fand ich noch via Google, untested.

Ja … Ein Wechsel auf 9.2.x steht auf dem Plan, wenn unsere Applikation in der Lage ist damit umzugehen :wink:

äää ja! Die Zuständigkeiten sind nicht so ganz klar. “So was” kann ja auch “gar nicht” passieren. :slight_smile:

Sollte das wirklich so einfach zu lösen sein? Ich probiere mal ob es funktioniert. Danke für die schnelle Antwort!

ohne Garantie. Vergaß ich zu sagen :wink:

Der Versuch mit dem Index löschen und neu anlegen ist gerade durchgelaufen. Leider ohne Erfolg. Der belegte Platz auf der DB Partition ist nach wie vor der gleiche.

Ich Vermute, dass es an den abgebrochenen Transaktionen liegen könnte. Leider kenne ich keine Möglichkeit diese nachträglich, also nach einem Neustart der Datenbank
Prozesse zurück zu rollen. Jedenfalls wird der belegte Platz weder den Datentabellen noch den Indexen zugeordnet, er scheint irgendwo zwischen den Daten zu liegen.
Ich habe keine Idee wie man diesen nachträglich wieder freigeben kann.

mit:

SELECT pg_size_pretty(pg_database_size(‘db’));

sagt mir Postgres, dass die DB ca. 6GB groß ist (vor den Versuchen das Problem zu provozieren war die DB 2.3 GB!)

mit:

SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
CASE WHEN x.is_unique = 1 THEN ‘Y’
ELSE ‘N’
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
(SELECT indrelid,
max(CAST(indisunique AS integer)) AS is_unique
FROM pg_index
GROUP BY indrelid) x
ON c.oid = x.indrelid
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,indexrelname FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname=‘public’
ORDER BY 1,2;

komme ich auf folgende Werte:

2913 MB in den Tabellen
736 MB in den Indexen

Da frage ich mich doch wo sind die restlichen ca. 3GB ?

Ein paar neue Informationen gibt es zu dem Thema.

Die Datenbank liegt physikalisch (auf dem Labor System) im Verzeichnis “$PGDATA/base/113394”. Nach einem Tip habe ich hier ein Skript laufen lassen, welches zu jedem gefundenem Objekt den realname in der Datenbank ausgeben sollte:

for each in $(ls -1 /DB/pgsql/data/base/113394 |grep “[0-9]”) ; do
psql $DB-c “SELECT relname FROM pg_catalog.pg_class WHERE oid = $each;”
done

Dieses gibt mir auch brav alle Objektnamen die in der Datenbank vorhanden sind inclusive Indexen, aber ab einem bestimmtem Punkt kommt nur noch:

relname

(0 rows)

Ich denke das sind genau die Fragmente in denen die abgebrochenen REINDEX Versuche gelandet sind. Wenn ich diese Objekte summiere und von der Gesamtgröße der DB abziehe, komme ich wieder auf die initiale DB Größe von ca. 2 GB!

Hat irgendwer eine Idee wie man der Datenbank diese Dateien entziehen kann?

Einfaches Löschen hat wie vermutet keinen Erfolg gehabt. Die Transaktionen (mit den diversen REINDEX Versuchen) haben ja sicherlich die Version der Indexe hochgezählt. Wenn ich das richtig verstanden habe hat es mit einer XID zu tun, welche die Datenbank für Views auf ihre Daten heranzieht.