Rollback verlangsamt scheinbar nachfolgende Operationen auf der Tabelle

Allgemeines zu PostgreSQL.
tigger
Benutzer
Beiträge: 41
Registriert: Fr 2. Mai 2008, 00:55
Kontaktdaten:

Rollback verlangsamt scheinbar nachfolgende Operationen auf der Tabelle

Beitragvon tigger » Fr 2. Mai 2008, 02:14

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

akretschmer
Erfahrener Benutzer
Beiträge: 7046
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Beitragvon akretschmer » Fr 2. Mai 2008, 08:48

tigger hat geschrieben:Hallo,

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

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
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

ads
Erfahrener Benutzer
Beiträge: 4148
Registriert: Mi 8. Feb 2006, 10:58
Wohnort: MD
Kontaktdaten:

Beitragvon ads » Fr 2. Mai 2008, 09:59

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
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören Bild

Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

PostgreSQL Service & Support

tigger
Benutzer
Beiträge: 41
Registriert: Fr 2. Mai 2008, 00:55
Kontaktdaten:

Beitragvon tigger » So 4. Mai 2008, 14:36

akretschmer hat geschrieben:Sollen dann in allen Records in dieser Spalte jeweills derselbe Wert stehen?


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.

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

Ein Vacuum full vorher hätte geholfen.


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 :)
Ich muss es wohl dann in Häppchen verpacken.

akretschmer hat geschrieben: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.


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


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

ads
Erfahrener Benutzer
Beiträge: 4148
Registriert: Mi 8. Feb 2006, 10:58
Wohnort: MD
Kontaktdaten:

Beitragvon ads » So 4. Mai 2008, 14:43

tigger hat geschrieben:UPDATE tabelle set ts = (ts + (random() || ' milliseconds')::interval);


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.
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören Bild



Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler



PostgreSQL Service & Support

tigger
Benutzer
Beiträge: 41
Registriert: Fr 2. Mai 2008, 00:55
Kontaktdaten:

Beitragvon tigger » So 4. Mai 2008, 23:52

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


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.

ads hat geschrieben: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.


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 :)

ads
Erfahrener Benutzer
Beiträge: 4148
Registriert: Mi 8. Feb 2006, 10:58
Wohnort: MD
Kontaktdaten:

Beitragvon ads » Mo 5. Mai 2008, 00:29

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.
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören Bild



Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler



PostgreSQL Service & Support

tigger
Benutzer
Beiträge: 41
Registriert: Fr 2. Mai 2008, 00:55
Kontaktdaten:

Beitragvon tigger » Mo 5. Mai 2008, 03:22

ads hat geschrieben: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.


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 :) Danke!

ads hat geschrieben: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.


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.

akretschmer
Erfahrener Benutzer
Beiträge: 7046
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Beitragvon akretschmer » Mo 5. Mai 2008, 07:16

tigger hat geschrieben:Wie ist das eigentlich mit einer Transaktion, sollte man nach einem bestimmten "Transfervolumen" einen Commit anstreben?

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
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

tigger
Benutzer
Beiträge: 41
Registriert: Fr 2. Mai 2008, 00:55
Kontaktdaten:

Beitragvon tigger » Di 6. Mai 2008, 15:38

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 ;) )


Zurück zu „Allgemeines“

Wer ist online?

Mitglieder in diesem Forum: Bing [Bot] und 0 Gäste