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