Rollback verlangsamt scheinbar nachfolgende Operationen auf der Tabelle

Hallo,

ich habe eine Tabelle mit etwa 15 Spalten und 18 Mio Datensätze. Nun muss ich eine Spalte komplett updaten.

Am Anfang habe ich die Datensätze in 10000der Schritte geupdated, da ein gesamter Update mir doch sehr heftig für den Speicher vorkam, und ich vielleicht nicht unbedingt die Tabelle ewig sperren möchte.
Als ich aber dann bei dem 1.5 millionsten Datensatz ankam, wurden die Updates zunehmend langsamer, obwohl alle 10000 Einträge ein Commit durchgeführt wird!

Als ich das Update der 18 Mio Datensätze in einem Rutsch angeordnet habe (in einer Transaktion), hat mich irgendwann die Gedult verlassen, und ich hab das ganze nach über einer Stunde Rechenzeit abgebrochen. Als ich nach dem Rollback, meine 10000der Variante des Updates wieder versucht habe, so war das ebenfalls sehr langsam, und zwar nun schon von Anfang an!

Ich muss diese Tabelle dringend updaten! Die betroffene Spalte ist ein Timestamp (falls das wichtig ist).


Ich hab auch schon probiert, diese Tabelle in eine andere Temporäre Tabelle zu schreiben, und dabei die betroffene Spalte zu verändern. Danach die Ursprungstabelle truncaten, die Trigger, Constraints und Regeln abschalten und die Werte wieder zurückspielen. Danach die Trigger, Constraints und Regeln wieder zuschalten. Aber auch das geht kaum in einer Transaction, da dies scheinbar doch ein wenig zu heftig ist für die Datenbank.

Hier nun meine 2 Fragen:

Wie kann ich diese Tabelle am Besten updaten?

Wird eine Tabelle langsamer bei einem “Mega-Rollback” (Transaction bricht nach etwas 1 Stunde ab)?


MfG

David

Sollen dann in allen Records in dieser Spalte jeweills derselbe Wert stehen?

Am Anfang habe ich die Datensätze in 10000der Schritte geupdated, da ein gesamter Update mir doch sehr heftig für den Speicher vorkam, und ich vielleicht nicht unbedingt die Tabelle ewig sperren möchte.
Als ich aber dann bei dem 1.5 millionsten Datensatz ankam, wurden die Updates zunehmend langsamer, obwohl alle 10000 Einträge ein Commit durchgeführt wird!

Das mag daran liegen, daß nunmehr bereist 1,5 Millionen zusätzliche Records in der Tabelle sind: dead tuple.

Als ich das Update der 18 Mio Datensätze in einem Rutsch angeordnet habe (in einer Transaktion), hat mich irgendwann die Gedult verlassen, und ich hab das ganze nach über einer Stunde Rechenzeit abgebrochen. Als ich nach dem Rollback, meine 10000der Variante des Updates wieder versucht habe, so war das ebenfalls sehr langsam, und zwar nun schon von Anfang an!

Ein Vacuum full vorher hätte geholfen.

Ich muss diese Tabelle dringend updaten! Die betroffene Spalte ist ein Timestamp (falls das wichtig ist).

Die Frage, die sich mir stellt, ist, ob es nicht vielleicht auch ohne Update geht. Wenn Du überall denselben Wert reinschreiben willst bzw. ein aus den bisherigen Daten berechenbaren Wert, dann kannst Du Dir das evtl. auch sparen und z.B. einen VIEW definieren, der die korrekten Daten anzeigt.

Wird eine Tabelle langsamer bei einem “Mega-Rollback” (Transaction bricht nach etwas 1 Stunde ab)?

Jein. Nicht, wenn man ein Vacuum der Table macht.


Andreas

Vergleich mal die Größe deiner Tabelle mit der Größe, die du für die Daten veranschlagst. Und vergleich mal die Größe der Tabelle (und des Index) vor und nach einem VACUUM FULL.

table size, database size - ads’ corner

Nein, es sind errechnete Werte. Ich hatte ausversehen now() statt clock_timestamp() verwendet. Die Datenbank aht das nicht gestört, aber die Logik die darauf aufbaut. Es sind nun einige Werte in einer Transaktion eingefügt worden, die jetzt den gleichen Zeitstempel haben. Das ist aber laut Logik verboten.
Nun wollte ich mit
UPDATE tabelle set ts = (ts + (random() || ’ milliseconds’)::interval);
dem Problem begegnen.

So müssten die Zeitstempel weitestgehend im Millisekundenbereich wieder eindeutig sein.

Also soll ich alle 1,5 Millionen Werte erstmal einen Full Vacuum machen? Würde ein Full Vacuum auf der Tabelle reichen, oder lieber über die ganze DB? Also alles in einer Transaktion zu machen wäre dann wohl tötlich :slight_smile:
Ich muss es wohl dann in Häppchen verpacken.

Leider ist es besser, wenn die Daten physisch geändert werden, da es sich um eine Korrektur handelt.

Wäre es möglich, dass random() schlicht nicht schnell genug neue Werte liefern kann? Du forderst immerhin 18 Millionen Zufallswerte an.

Btw: das kann sehr schnell wieder doppelte Datensätze ergeben, random() liefert Werte zwischen 0 und 1 und du addierst also 0 oder 1 (meistens jedoch 0) Millisekunden.

Naja, ich wüsste eben keine bessere Lösung um schnellstmöglich den Zeitstempel wieder Eindeutigkeit zu verleihen. Außerdem hat es ja einmal in einer aktzeptablen Zeit geklappt. Als ich es aber noch einmal nachstellen wollte, kam er nicht mehr aus dem Tee. Allerdings hatte ich da nach einem Dump-restore keine Full Vacuum gemacht. Das werd ich das nächste Mal besser machen.

Das Statement lautete eigentlich:
UPDATE tabelle set ts = (ts + (random() || ’ seconds’)::interval);
Ich hab’s falsch abgeschrieben. Der ganz genaue Zeitstempel ist eigentlich auch egal, deswegen will ich auch die Millisekunden nutzen um die Zeitstempel wieder eindeutig zu machen.

Die Zeitstempel werden also um maximal eine Sekunde verfälscht, was in dem Anwendungsfall nicht weiter tragisch ist. Auf 18 Mio Werte kann da schon mal der Zeitstempel wieder doppelt vorkommen, aber es gibt noch mehr Spalten, die zur Eindeutigkeit herangezogen werden. Und da wird es sich um vielleicht 1 bis 1000 Dopplungen handeln. Und wenn es wirklich noch ein paar doppelte geben sollte, so kann ich die immernoch selektieren und updaten.

Trotzdem Danke für die Ideen.

Eine Frage beschäftigt mich aber immer noch.
Soll ich 18 Mio Werte in 1,5 Mio Schritte updaten, dann ein Full Vacuum machen, und dann die nächsten 1,5 Mio Werte angehen? Oder geht das Update nach einen Full Vacuum mit 18 Mio Werte ohne Probleme durch?

Falls keiner darauf eine Antwort weiß, werd ich wohl in den nächsten Tagen hier das Ergebnis berichten :slight_smile:

Keine Ahnung, woran das nun wirklich liegt, die Idee mit dem leeren Zufallsgenerator kam mir erst, als ich dein Query gesehen habe. Es kann gut sein, dass der Entropy-Pool in deinem Server alle ist … dann muss die Datenbank warten, bis neue Zufallswerte bereitstehen. Wenn du alles in kleinen Schritten aktualisierst, ist zwischendurch schon mal etwas Pause, was für neue Zufallswerte sorgt.

Wie wäre es z.B. mit der aktuellen ID als aufaddierten Millisekundenwert - falls deine Tabelle eine ID Spalte hat. Dann bist du nicht auf random() angewiesen.

Das VACUUM FULL musst du erst am Ende der Operation einmalig durchführen, ausser du hast zwischendurch Plattenplatzprobleme. Du musst dir verdeutlichen, das jedes Update eines Tuples einen neuen Datensatz anlegt, du also noch mal 60 Millionen Datensätze hinzubekommst. Insgesamt stehen dann 120 Millionen Datensätze dort drin, wovon aber nur 60 Millionen sichtbar sind.

Wenn du eine Transaktion kurz vor dem Ende zurückrollst, hast du trotzdem 120 Millionen Datensätze, aber nur die ursprünglichen 60 Millionen werden angezeigt.

VACUUM FULL sorgt nun dafür, das der überschüssig belegte Platz freigegeben wird, sobald dieser in keiner Transaktion mehr benötigt wird.

Coole Idee! Die Serial wird zwar ein wenig zu groß nach hinten hin betrachtet, aber mit einer modulo-Operation dürfte ich in etwa den gleichen Effekt hinbekommen. Manchmal steht man einfach auf dem Schlauch :slight_smile: Danke!

Aber das kann doch nicht alles sein, was VACUUM FULL macht?!? Dann kann ich mir nicht erklären, warum das UPDATE nach 1,5 Mio Werte langsamer wird, und das mit einem VACUUM FULL wieder behoben werden soll.

Wie ist das eigentlich mit einer Transaktion, sollte man nach einem bestimmten “Transfervolumen” einen Commit anstreben?

Und da fällt mir noch eine Frage ein:
Normalerweise kann ich ja Zeilen updaten, und während dieser Transaktion kann ein anderer INSERTS durchführen. Beide blockieren sich nicht.

Wenn ich aber Trigger an dem Anfang einer Transaktion abschalte, etwas inserte und update, und dann den Trigger wieder anschalte, ist dann der Trigger nur für meine Transaktion abgeschalten und die anderen Transaktionen wurden mit Trigger durchgeführt, oder habe ich durch die Triggerabschaltung einen Table-Lock erzeugt?
Bisher habe ich immer die Tabelle sicherheitshalber explizit gesperrt.

Nicht wirklich. Es gibt zwar andere Systeme, die auch schon mal von sich aus ein COMMIT in eine laufende Transaktion werfen, aber nötig ist das nicht und gewollt in vielen Fällen wohl auch nicht. Die Anzahl der möglichen Befehle innerhalb einer TX ist hinreichend roß (weiß es grad nicht genau und bin zu faul zu suchen), aber Du kannst recht sicher sein, daß Du dieses Limit kaum erreichen wirst…

Und da fällt mir noch eine Frage ein:
Normalerweise kann ich ja Zeilen updaten, und während dieser Transaktion kann ein anderer INSERTS durchführen. Beide blockieren sich nicht.

Wenn ich aber Trigger an dem Anfang einer Transaktion abschalte, etwas inserte und update, und dann den Trigger wieder anschalte, ist dann der Trigger nur für meine Transaktion abgeschalten und die anderen Transaktionen wurden mit Trigger durchgeführt, oder habe ich durch die Triggerabschaltung einen Table-Lock erzeugt?
Bisher habe ich immer die Tabelle sicherheitshalber explizit gesperrt.

Bei PG ist alles in einer TX gesichert, auch DDL-Befehle. Selbst ORACLE kann das nicht so gut wie PG.


Andreas

Also irgendwie macht mir diese Tabelle mit den 18 Mio Werten einwenig zu schaffen.

Ich habe in dieser Tabelle einen kombinierten Primary Key mit 2 Spalten.
Wenn ich nun exakt nach diesem Selektiere, so wird mir diese Zeile sofort angezeigt. Wenn ich diese aber lösche, dann dauert das in etwas 53 Sek.

Ist das normal bei der Datenmenge in der Tabelle?

(VACUUM FULL vorher ausgeführt :wink: )

Nein. Was sagt ein EXPLAIN ANALYSE?

Andreas

Ich hab gerade das Forum durchwühlt, und das mit dem Explain Analyse gelesen.

So hab ich rausgefunden, dass ich ausversehen einen wichtigen Foreign Key Index gelöscht hatte (hab die Selbstreferenz völlig vergessen).

Mal eine Frage zur Index-Nutzen Analyse: Mit
select * from pg_statio_all_indexes
kann man doch an den Spalten idx_blks_read und idx_blks_hit ablesen ob der entsprechende Index überhaupt genutzt wird, oder?

Ja, vorausgesetzt du hast stats_row_level und stats_block_level in der Config angeschaltet.

Danke, stats_block_level war noch nicht an.

Hier nochmal eine kleine Zusammenfassung:

Auf meinem Testsystem konnte ich durch ein VACUUM FULL nach dem einspielen eines dumps die 18 Mio Werte neusetzen.

Allerdings hab ich es nun ein wenig anders gemacht.

Erst die Tabelle mit LOCK t1 gesperrt.
Mit einem

CREATE TEMP TABLE AS SELECT id, (zeitstempel), a, b FROM t1;
(zeitstempel::timestamp without time zone + (((id % 100000)::numeric/100000)::numeric || ’ seconds’)::interval)::timestamp without time zone as zeitstempel

hab ich alles erstmal in eine temporäre Tabelle gepackt, und die zu updatende Spalte gleich geändert.
Statt des random() hab ich nun die serial-spalte und modulo benutzt.

Bei der Ursprungstabelle hab ich alle indexe gelöscht, trigger deaktiviert, constraints gedropt, und Rules gelöscht.

Dann die Tabelle truncated und die Werte aus der temporären Tabelle zurückgespielt.
Anschließend die constraints, indexe, trigger, und Rules wiederhergestellt.

Und das Beste: Das ganze in einer Transaktion :slight_smile:
Danach nur noch ein VACUUM FULL und alles war wieder schön.

Dies hat auf meinem Testsystem 40 Minuten gedauert.

Bleibt zu hoffen, dass es heut Nacht auf dem Echt-System auch so klappt.

Wie es immer so ist, auf dem Testsystem geht’s immer schneller :slight_smile:

Es hat alles funktioniert, allerdings hat es statt 40 min satte 4 Stunden gedauert.

Der Unterschied kann durch die Hardware und das andere Betriebssystem zusammenhängen. Auf dem Testsystem läuft Gentoo Linux und auf dem Echt-System MS Windows Server 2003.

Danke für eure Hilfe!

Tja. OpenSource-Programme fühlen sich halt auf einem OpenSource-System schlicht wohler…

Andreas