einfaches Update dauert plötzlich mehrere Stunden

Hallo,

Ich verwende seit 2 Jahres das folgendes Update:

UPDATE fahrzeug set spalte3= 100*spalte1/spalte2 where spalte2 > 0


Bis jetzt lief es eigentlich recht schnell - doch ein einem auf den anderen Tag dauert es plötzlich rund 3 Stunden.

Zur Tabelle:

CREATE TABLE public.fahrzeug
(
  f_id integer NOT NULL,
  spalte1 double precision,
  spalte2 double precision,
  spalte3 smallint,
  --weitere 15 spalten
  CONSTRAINT fahrzeug_pkey PRIMARY KEY (f_id)
  --weitere fremdschlüssel
)

Die Tabelle hat ~3mio Datensätze, und auf 2,5mio trifft “where spalte2 >0” zu.


Was ich zuletzt geändert habe:

  • Der Tabelle einen Primärschlüssel hinzugefügt
    Postgres update von 9.3 auf 9.5
    vacuum full

Ich habe bis auf vacuum zu testzwecken alles wieder rückgängig gemacht, komme aber nicht mehr auf das alte Ergebnis.

Auch ein Index

Creare Index ix on Fahrzeug(spalte1, spalte2, spalte3)

oder der Index:

Creare Index ix2 on Fahrzeug(id, spalte1, spalte2, spalte3)

bringen keinen Unterschied.

Habt ihr da eine Idee?


mfg, JayJay

Table/Indexbloat?

Was sagt Explain Analyse eigentlich dazu? Hast Du pgstattuple installiert?

Genau das war es! Vielen Dank. Ich hatte 4 Indexes auf der Tabelle. Nachdem ich alle gelöscht habe, ging es - naja, mittel schnell.

Dann habe ich einen Index für genau diese Situation hinzugefügt und es dauerte nur noch 30 Sekunden :-)))


Mein Problem: Wie indexiere ich die Tabelle denn nun “richtig”? Ich habe ja mehrere Abfragen/Updates und Delete’s, die ALLE schnell sein sollen.

Die Tabelle hat ca 20 Spalten, wovon eigentlich 12 sehr oft genutzt werden.

mfg, JayJay

Indexe hängen von den verwendeten Abfragen ab. Unnötige Indexe verlangsammen Schreiboperationen, gute beschleunigen Leseoperationen.

Danke,

Ein Index beschleunigt nur das Lesen (gute lassen die Schreibzugriffe gleich, schlechte verlangsamen die Schreibzugriffe) - so wie du es geschrieben hast.

Meine Frage ist nun: wie beschleunige ich einen Schreibzugriff?

Z.B. wird an einer Stelle bei rund 600.000 Fahrzeuge update durchgeführt (also 600.000 einzelne updates, wovon genau zwei der ~20 Spalten bestroffen sind)

mfg, JayJay

Ein Update ist unterm Strich ein Delete+Insert (auch nicht ganz richig, der alte Datensatz wird als ungültig markiert), so daß letztendlich auch die Indexe erneuert werden müssen. Ob dabei 2 von 20 Spalten betroffen sind oder 19 ist irrelevant - der Record wird neu geschrieben. Später wird (Auto)Vacuum den alten datensatz, wenn er in keiner Transaktion mehr sichtbar ist, freigeben.

Wenn Du nun zu viele Indexe hast, die nie benötigt werden (Verteilung mit der Gießkanne) dann vergrößert sich die Arbeit bei schreibenden Operationen ohne daß Du bei lesenden einen Nutzen hast.

Du aktualisierst 600.000 Datensätze, wie viele hat die Tabelle insgesamt? Und machst Du das mit 600.000 einzelnen Update-Anweisungen oder in einer? Bist Du Dir sicher, daß so viele Updates gemacht werden müssen? Ist Dein Tabellendesign so, daß dies regelmäßig passieren muß?

Merke: Updates sind teuer. Viel teuerer als Selects. Und auch Indexe sind teuer - sie müssen gepflegt werden, kosten Platz auf der Platte und im Shared Buffers.

Ok danke für die Erklärung des Updates!

Momentan habe ich keinen Index mehr, da ich keine saubere Lösung zusammen bekomme… irgendein Update wird dadurch immer verzögert.

Die Tabelle hat nun rund 3 Millionen Datensätze.

Es werden die 600.000 Daten aus der DB ins Programm geladen (geht schnell), aktualisiert (im Programm, geht schnell) und wieder hochgeladen (dauert sehr lange)

Die 600.000 Updates werden in einer Schleife von einem Java Programm aus abgehandelt und somit jedes einzeln in die DB geschossen - naja, aus momentaner Sicher eher “in die DB geschoben”


Das Muss mindestens 2x am Tag passieren und muss so schnell wie möglich fertig sein.



Das Problem: Es werden täglich mehr Daten und immer rund 20% davon aktualisiert, also wird es auch immer langsamer.

Das Vorgehe ist suboptimal. An Deiner Stelle würde ich versuchen, das Update komplett in der DB machen zu lassen. Was macht Dein Java-Programm mit den Datensätzen? Vermutich könnte das vollständig in der DB umgesetzt werden.

Du könntest das vermutlich beschleunigen, wenn Du alle Updates zu einer einzelnen Transaktion zusammenfassen würdest - vermutlich hast Du jetzt 600.000 Einzeltransaktionen, und damit auch so oft ein fsync(). Du kannst auch mal synchronous_commit auf off setzen, das geht auch via Session oder User - und beschleunigt das das spürbar.

Vielen Dank, das werde ich nun versuchen. Ich melde mich wieder, wenn ich weiteres weiß :slight_smile:

wir können uns auch gern über eine Consulting-Einsatz vor Ort unterhalten :wink:

Danke für dein Angebot, aber ich versuch mich selbst durchzuschlagen.

Ich lasse dieses Update nun komplett in der DB machen und bin hier wirklich deutlich schneller - so spart es enorm viel Zeit und macht gleich viel mehr Spaß!

Nun ist mir aber wieder folgendes aufgefallen:


Ein normaler Lesezugriff auf meine Tabelle (~3mio Datensätze) dauert in der Regel 1,4 Sekunden. Doch sobald ein UPDATE Statement läuft, dauert der selbe Lesezugriff von vorher 16 Sekunden.

Es wird nicht direkt darauf gewartet, denn das UPDATE wird erst nach dem Lesezugriff fertig - (somit kein lock). Aber die Verzögerung nervt doch extrem, denn meine Website soll ja möglichst schnell funktionieren.


Mein Server (Version 9.4) hat noch alle Standard- Einstellungen, bis auf die Belegung auf einen anderen Port, die Option auch von außen zugreifen zu können und synchronous_commit wurde auf “off” gestellt.

Gibt es sonst noch eine Möglichkeit, den Lesezugriff trotz laufendem Update zu beschleunigen?

Hier die 2 Statements:

Update:

UPDATE fahrzeug set marktwert = null where berechnet < Current_Timestamp - INTERVAL  '7 day';

Select:

select * from Fahrzeug WHERE lastread is null  and f_marktwert > 0  order by f_firstread DESC FETCH FIRST 1000 ROWS ONLY;

Ich hoffe ihr könnt mir etwas weiter helfen :slight_smile:

Du solltest shared_buffers hoch setzen. Bei der Masse an Updates auch wal_buffers. Das Explain (analyse, buffers) wäre auch interessant zu wissen, für beide Befehle.