Langsame einfache Update-Abfrage in der PostgreSQL-Datenbank mit 3 Millionen Zeilen

Ich versuche eine einfache UPDATE table SET column1 = 0 auf eine Tabelle mit ~3 Mio Zeilen auf Postegres 8.4 aber es nimmt für immer zu beenden. Es läuft schon seit mehr als 10 min. nun, in meinem letzten Versuch.

Bevor ich versuchte zu laufen ein VACUUM und ANALYZE-Befehle auf dem Tisch und ich habe auch versucht zu erstellen, einige Indizes (obwohl ich bezweifle, das wird keinen Unterschied machen, in diesem Fall) aber keiner scheint zu helfen.

Irgendwelche anderen Ideen?

Dank,
Ricardo

Update:

Dies ist die Struktur der Tabelle:

CREATE TABLE myTable
(
  id bigserial NOT NULL,
  title text,
  description text,
  link text,
  "type" character varying(255),
  generalFreq real,
  generalWeight real,
  author_id bigint,
  status_id bigint,
  CONSTRAINT resources_pkey PRIMARY KEY (id),
  CONSTRAINT author_pkey FOREIGN KEY (author_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT c_unique_status_id UNIQUE (status_id)
);

Ich versuche zu laufen UPDATE myTable SET generalFreq = 0;

InformationsquelleAutor der Frage Ricardo Lage | 2010-07-29

  1. 10

    Werfen Sie einen Blick auf diese Antwort: PostgreSQL langsam auf einem großen Tisch mit arrays und viele updates

    Ersten start mit einem besseren Wert für FILLFACTOR, ein VAKUUM VOLLER Kraft Tabelle umschreiben und überprüfen Sie die HOT-updates, die nach Ihrem UPDATE-Abfrage:

    SELECT n_tup_hot_upd, * FROM pg_stat_user_tables WHERE relname = 'myTable';

    HOT-updates viel schneller, wenn Sie haben eine Menge von Datensätzen zu aktualisieren. Weitere Informationen zu HOT finden Sie in diesem Artikel.

    Ps. Sie benötigen die version 8.3 oder besser.

    InformationsquelleAutor der Antwort Frank Heikens

  2. 23

    Ich das update der Tabellen 1 oder 2 Milliarden Zeilen mit verschiedenen Werten für die einzelnen Zeilen. Jeder Lauf macht ~100 Millionen änderungen (10%).
    Mein Erster Versuch war, um Sie zu gruppieren in der Transaktion von 300K-updates direkt auf einer bestimmten partition als Postgresql nicht immer optimieren vorbereitete Abfragen, wenn Sie Partitionen.

    1. Transaktionen der Reihe "UPDATE myTable SET myField=value WO
      myId=id"
      Gibt 1,500 updates/sec. das bedeutet, dass jeder laufen würde
      nehmen Sie sich mindestens 18 Stunden.
    2. HOT-updates-Lösung wie hier beschrieben mit FILLFACTOR=50. Gibt
      1,600 updates/sec. Nutze ich SSD ' s, so ist es eine kostspielige Verbesserung, da es
      verdoppelt die Größe des Speichers.
    3. Einfügen in eine temporäre Tabelle der aktualisierte Wert und mischen Sie Sie nach
      mit UPDATE...AUS Gibt 18,000 updates/sec. wenn ich ein VAKUUM
      für jede partition; von 100.000 bis/s sonst. Cooool.
      Hier ist die
      Reihenfolge der Operationen:
    CREATE TEMP TABLE tempTable (id BIGINT NOT NULL, field(s) to be updated,
CONSTRAINT tempTable_pkey PRIMARY KEY (id));

    Sammeln eine Reihe von updates in einem Puffer, je nach verfügbaren RAM
    Wenn es gefüllt ist, müssen Sie zum ändern der Tabelle/partition, in Ausführung oder abgeschlossen:

    COPY tempTable FROM buffer;
UPDATE myTable a SET field(s)=value(s) FROM tempTable b WHERE a.id=b.id;
COMMIT;
TRUNCATE TABLE tempTable;
VACUUM FULL ANALYZE myTable;

    Das bedeutet, dass ein Lauf jetzt dauert 1,5 h statt 18h für 100 Millionen updates, Vakuum enthalten.

    InformationsquelleAutor der Antwort Le Droid

  3. 7

    Nach einer Wartezeit von 35 min. für meine UPDATE Abfrage zu beenden (und immer noch nicht) habe ich beschlossen, etwas anderes zu versuchen. Also, was ich Tat, war ein Befehl:

    CREATE TABLE table2 AS 
SELECT 
  all the fields of table1 except the one I wanted to update, 0 as theFieldToUpdate
from myTable

    Dann Indizes hinzufügen, löschen Sie dann die alte Tabelle und benennen Sie die neue Ihren Platz ein zu nehmen. Das hat nur 1,7 min. zu verarbeiten, plus einige zusätzliche Zeit, um neu die Indizes und Einschränkungen. Aber es kam Hilfe! 🙂

    Natürlich, dass funktionierte nur, weil niemand sonst wurde mit der Datenbank. Ich brauchen würde, um die Tabelle zu sperren ersten, wenn dies in einer Produktionsumgebung.

    InformationsquelleAutor der Antwort Ricardo Lage

  4. 3

    Heute habe ich viele Stunden damit verbracht, mit ähnlichen Problem. Ich habe ein Lösung: drop alle constraints/indices, bevor das update. Egal, ob die Spalte aktualisiert werden, indiziert ist oder nicht, es scheint, wie psql-updates alle Indizes für alle aktualisierten Zeilen. Nachdem das update abgeschlossen ist, fügen Sie die Einschränkungen/index zurück.

    InformationsquelleAutor der Antwort Tregoreg

  5. 2

    Versuchen, diese (beachten Sie, dass generalFreq startet als Typ REAL ist, und bleibt die gleiche):

    ALTER TABLE myTable ALTER COLUMN generalFreq TYPE REAL USING 0;

    Diese überschreibt die Tabelle, ähnlich wie ein TROPFEN + ERSTELLEN, und erstellen Sie Indizes. Aber alles in einem Befehl. Viel schneller (etwa 2x) und Sie nicht haben, um mit Abhängigkeiten und Neuerstellen von Indizes und anderen Sachen, obwohl es nicht die Tabelle sperren (access-exklusiv-D. H. eine vollständige Sperre) für die Dauer. Oder vielleicht das ist, was Sie wollen, wenn Sie wollen, alles andere Schlange dahinter. Wenn Sie nicht aktualisieren "zu viele" Zeilen dieser Weg langsamer ist, als nur ein update.

    InformationsquelleAutor der Antwort Fabiano Bonin

  6. 0

    Wie werden Sie ausgeführt? Wenn Sie eine Schleife, die jede Zeile und ausführen einer update-Anweisung, die Sie ausführen potenziell Millionen von einzelnen updates, das ist, warum es wird unglaublich langsam.

    Wenn Sie mit einer einzigen update-Anweisung für alle Datensätze in einer Erklärung würde es viel schneller, und wenn dieser Prozess langsam ist, dann ist es wohl an deiner hardware mehr als alles andere. 3 Millionen ist eine Menge von Datensätzen.

    InformationsquelleAutor der Antwort Tom Gullen

  7. 0

    Die erste Sache, die ich vorschlagen würde (aus https://dba.stackexchange.com/questions/118178/does-updating-a-row-with-the-same-value-actually-update-the-row) ist nur für update-Zeilen, die "brauchen" es, ex:

     UPDATE myTable SET generalFreq = 0 where generalFreq != 0;

    (möglicherweise müssen auch ein index auf generalFreq). Dann wirst du aktualisieren, weniger Zeilen. Aber nicht, wenn die Werte alle nicht-null schon, aber die Aktualisierung weniger Zeilen "kann helfen", da es sonst updates und alle Indizes unabhängig davon, ob sich der Wert geändert hat oder nicht.

    Andere Möglichkeit: wenn die Sterne ausrichten in Bezug auf die Standardwerte und nicht-null-constraints, drop können Sie die alte Spalte und erstellen ein weiteres nur durch einstellen der Metadaten, sofortige Zeit.

    InformationsquelleAutor der Antwort rogerdpack

  8. -2

    versuchen 

    UPDATE myTable SET generalFreq = 0.0;

    Vielleicht ist es eine casting-Ausgabe

    InformationsquelleAutor der Antwort Chocolim

Schreibe einen Kommentar