Performanceproblem bei DELETE

Hallo Zusammen

Ich habe ein Gerät mit einer PostgreSQL 9.1 Datenbank auf welchem verschiedene Datensätze, darunter auch mehrere bytea abgespeichert werden. Viele dieser Tabellen besitzen einen Foreign Key, welcher meistens auch gleich der Primary Key, die ID der referenzierten Tabelle ist.

Die Tabellen werden weder kaskadiert noch truncated, es werden zuerst sämtliche referenzierten Einträge und nach und nach bis nach oben in der Hierarchie das eigentliche Tupel gelöscht.
Dies resultiert in etwa 190 DELETE Queries welche für eine Löschung ausgeführt werden müssen und das eigentliche Problem ist, dass die Datenbank dafür ungefähr 6s benötigt. Bei mehreren Datensätzen die gelöscht werden sollen ergibt sich eine unglaublich lange Wartezeit.

Eine Löschung wird in Java über das JDBC Interface in einer Transaktion gemacht.
Um ein Zeitverlust im Java-Code auszuschliessen habe ich ein Dump der Datenbank erstellt, Eine Löschung in Java vorgenommen und dabei die SQL Queries geloggt, Die Datenbank zurückgesetzt und die Queries direkt in pgAdmin III ausgeführt. Direkt mit den Queries dauerte dies auch wieder mehr als 6s.

Eine weitere Vermutung war, dass zuerst alle refernzierten Einträge gesucht werden müssen, da in PostgreSQL die Foreign Keys nicht standardmässig indexiert werden.
Um dies auszuschliessen habe ich eine Löschung in einer gut gefüllten und einer fast leeren Datenbank vorgenommen. Auch in der fast leeren dauerte die Löschung über 6s.

In der PostgreSQL Datenbank sollten ja eigentlich nur der Speicher freigegeben werden und zu einem späteren Zeitpunkt bei einem VACUUM richtig gelöscht werden. Woher kommt dann dieser Zeitverlust?

Danke im Voraus für die Hilfe
Liebe Grüsse

Schau Dir bitte mal das zugehörige Explain analyse analyse an. Unter Umständen kann helfen:

  • bist Du sicher, daß wirklich aus DB-Sicht alles in einer TX ist?
  • mit PREPARE das Löschen vorbereiten
  • mit Cascade arbeiten und einfach den Master-Datensatz löschen, ich versteh eh nicht, warum Du das händisch aufdröselst

Möglicherweise wird Dir aber das Explain schon zeigen, daß da vielleicht seq-scans nötig sind.

Und auch wenn das eigentliche Löschen erst beim VACUUM passiert: das WAL muß dennoch nach jedem COMMIT gesynct werden. Ich vermute ja, Du hast das nicht in EINER TX alles, sondern jedes DELETE in einer TX.

Danke für die Hinweise, ich habe diese versucht zu überprüfen.



Ich habe die Constraints der betroffenen Tabellen versuchsweise auf ON DELETE CASCADE abgeändert. Sämtliche FK’s der betroffenen Tabellen sind indexiert. Leider dauert das löschen immernoch ungefähr gleich lange.
Wenn ich mit CASCADE arbeite sollte sich das mit der Transaktion ja erledigt haben, da nur noch eine DELETE Query an die Datenbank abgesetzt wird.

Um einen Überblick über die Daten zu bekommen habe ich die Grössen der bytea mit folgendem Begehl analysiert:
SELECT octet_length(column) FROM table;

Dabei bin ich gesamthaft für das löschen eines Datensatzes (mit allen referenzierten Einträgen) auf über 70MB gekommen.

Wenn tatsächlich nur Speicher freigegeben wird sollte dies eigentlich unabhängig von der Datengrösse sein oder habe ich da etwas falsch verstanden.

Wie sieht das Explain analyse aus? Kann es sein, daß da viel Bloat in den Tabellen ist? Prüf das mal, z.B. via https://wiki.postgresql.org/wiki/Show_database_bloat oder schau in pg_class, wie das Verhältniss von relpages zu reltuples ist.

Ich habe soeben eine neue Datenbank mit kaskadierten Tabellen in einem neuen Tablespace angelegt und dort einen Datensatz hinzugefügt und gelöscht. Das Explain Analyze zeigte mir graphisch nur das DELETE auf der Tabelle mit einem Pfeil zu einem blauen Fragezeichen.
Data Output zeigt mir auf welchen Tabellen die meiste Zeit benötigt wird. Es sind Tabellen welche bytea enthalten. Darin werden Bilder unkomprimiert im RAW Format abgespeichert.

Das Skript mit dem Bloat das du gepostet hast habe ich durchlaufen lassen. In meinem Schema public wo sich alle meine Tabellen befinden Zeigt es mir diese Werte an, ich weiss aber nicht genau wie diese zu interpretieren sind.

tbloat numeric auf den tabellen in public habe ich Werte zwischen 16.0 und 23.0
wastedbytes numeric: 8192-180224
ibloat numeric: 2.0 überall
wastedibytes double precision 8192 überall

UPDATE: Der Data Output des Explain Analyze

“QUERY PLAN”
“Delete on table0 (cost=0.00…8.27 rows=1 width=6) (actual time=0.111…0.111 rows=0 loops=1)”
" -> Index Scan using pk_table0 on table0 (cost=0.00…8.27 rows=1 width=6) (actual time=0.064…0.065 rows=1 loops=1)"
" Index Cond: (table0_id = 4)"
“Trigger for constraint fk1 on table1: time=0.754 calls=1”
“Trigger for constraint fk2 on table1: time=0.899 calls=1”
“Trigger for constraint fk3on table2: time=0.586 calls=1”
“Trigger for constraint fk4 on table2: time=0.699 calls=1”
“Trigger for constraint fk5 on table2: time=0.485 calls=1”
“Trigger for constraint fk6 on table2: time=0.510 calls=1”
“Trigger for constraint fk7 on table2: time=0.540 calls=1”
“Trigger for constraint fk8 on table2: time=0.557 calls=1”
“Trigger for constraint fk9 on table2: time=0.497 calls=1”
“Trigger for constraint fk10 on table2: time=0.487 calls=1”
“Trigger for constraint fk11 on table2: time=0.846 calls=1”
“Trigger for constraint fk12 on table2: time=0.728 calls=1”
“Trigger for constraint fk13 on table2: time=0.558 calls=1”
“Trigger for constraint fk14 on table2: time=0.555 calls=1”
“Trigger for constraint fk15 on table1: time=0.599 calls=1”
“Trigger for constraint fk16 on table1: time=0.484 calls=1”
“Trigger for constraint fk17 on table3: time=2.316 calls=17”
“Trigger for constraint fk18 on table4: time=2.299 calls=17”
“Trigger for constraint fk19 on table5: time=9.227 calls=2”
“Trigger for constraint fk20 on table6: time=61.782 calls=1”
“Trigger for constraint fk21 on table7: time=3774.833 calls=17”
“Trigger for constraint fk22 on table7: time=1441.281 calls=17”
“Total runtime: 5301.851 ms”

Yeah, ich meinte auch eher die Ausgaben, die es als Text erzeugt. Also nicht das bunte Bild, was bei PGAdmin unten rauspurzelt.

Data Output zeigt mir auf welchen Tabellen die meiste Zeit benötigt wird. Es sind Tabellen welche bytea enthalten. Darin werden Bilder unkomprimiert im RAW Format abgespeichert.

Du kannst für solche Dinge spezielle Storage-Parameter angeben, siehe http://www.postgresql.org/docs/9.3/static/sql-altertable.html -> SET STORAGE, aber die dürfte meiner Meinung nach kaum Einfluß auf die Geschwindigkeit beim löschen haben.

Das Skript mit dem Bloat das du gepostet hast habe ich durchlaufen lassen. In meinem Schema public wo sich alle meine Tabellen befinden Zeigt es mir diese Werte an, ich weiss aber nicht genau wie diese zu interpretieren sind.

tbloat numeric auf den tabellen in public habe ich Werte zwischen 16.0 und 23.0
wastedbytes numeric: 8192-180224
ibloat numeric: 2.0 überall
wastedibytes double precision 8192 überall

Ähm, ja. Ich hab damit auch noch ned wirklich gearbeitet. An sich sehen die Werte aber harmlos aus, zumindest was wastedbytes betrifft. Unsicher bin ich mir bei den Werten zwischen 16 und23.


UPDATE: Der Data Output des Explain Analyze

“QUERY PLAN”
“Delete on table0 (cost=0.00…8.27 rows=1 width=6) (actual time=0.111…0.111 rows=0 loops=1)”
" -> Index Scan using pk_table0 on table0 (cost=0.00…8.27 rows=1 width=6) (actual time=0.064…0.065 rows=1 loops=1)"
" Index Cond: (table0_id = 4)"
“Trigger for constraint fk1 on table1: time=0.754 calls=1”
“Trigger for constraint fk2 on table1: time=0.899 calls=1”
“Trigger for constraint fk3on table2: time=0.586 calls=1”
“Trigger for constraint fk4 on table2: time=0.699 calls=1”
“Trigger for constraint fk5 on table2: time=0.485 calls=1”
“Trigger for constraint fk6 on table2: time=0.510 calls=1”
“Trigger for constraint fk7 on table2: time=0.540 calls=1”
“Trigger for constraint fk8 on table2: time=0.557 calls=1”
“Trigger for constraint fk9 on table2: time=0.497 calls=1”
“Trigger for constraint fk10 on table2: time=0.487 calls=1”
“Trigger for constraint fk11 on table2: time=0.846 calls=1”
“Trigger for constraint fk12 on table2: time=0.728 calls=1”
“Trigger for constraint fk13 on table2: time=0.558 calls=1”
“Trigger for constraint fk14 on table2: time=0.555 calls=1”
“Trigger for constraint fk15 on table1: time=0.599 calls=1”
“Trigger for constraint fk16 on table1: time=0.484 calls=1”
“Trigger for constraint fk17 on table3: time=2.316 calls=17”
“Trigger for constraint fk18 on table4: time=2.299 calls=17”
“Trigger for constraint fk19 on table5: time=9.227 calls=2”
“Trigger for constraint fk20 on table6: time=61.782 calls=1”
“Trigger for constraint fk21 on table7: time=3774.833 calls=17”
“Trigger for constraint fk22 on table7: time=1441.281 calls=17”
“Total runtime: 5301.851 ms”

Warum brauchen die letzten Trigger so lange?fk21 und fk22 sind nahezu Deine Gesamtzeit.

Okay danke für den Hinweis ich werde das noch anschauen.

Diese zwei Trigger lösen das Delete auf den beiden Tabellen aus, welche den grössten Teil der Daten beherbergen.
Es sind die Tabellen mit den bytea und den unkompressierten Bildern. Sie brauchen auch die meiste Zeit beim löschen. Es sind je 17 Bilder im RAW Format. Also 34 Zeilen mit bytea.

Prüfe noch mal, ob da Indexe sind. Lösche mal direkt ein Datensatz und schaue Dir da auch Explain analyse an.

Ich werde das noch überprüfen. Lustigerweise, wenn ich die Bilder komprimiert als JPEG abspeichere dauert das löschen nur noch ca 1s… Bei gleicher Anzahl Zeilen. Ist es also doch abhängig von der Grösse?