Aufräumen nach Updates

Viele Select Queries in meiner Anwendung sind seit der Umstellung auf PostgreSQL deutlich langsamer. Ich konnte das Problem soweit eingrenzen, dass es immer dann der Fall ist, wenn EXPLAIN entweder “dirtied” oder “written” buffers liefert.

Hier ein Beispiel:

select RI.SWIMRESULTID from RANKINGITEM RI
left join SWIMRESULT SR on RI.SWIMRESULTID = SR.SWIMRESULTID
where RI.SWIMRESULTID between 0 and 10000

Erster Aufruf von Explain:

Index Only Scan using ix_rankingitem_swimresult on rankingitem ri  (cost=0.57..17442.90 rows=51011 width=4) (actual time=0.039..13239.834 rows=56753 loops=1)
  Index Cond: ((swimresultid >= 0) AND (swimresultid <= 10000))
  Heap Fetches: 17940
  Buffers: shared hit=41989 read=2872 dirtied=69
Planning time: 0.223 ms
Execution time: 13242.287 ms

Zweiter Aufruf von Explain:

Index Only Scan using ix_rankingitem_swimresult on rankingitem ri  (cost=0.57..17442.90 rows=51011 width=4) (actual time=0.032..27.544 rows=56753 loops=1)
  Index Cond: ((swimresultid >= 0) AND (swimresultid <= 10000))
  Heap Fetches: 17910
  Buffers: shared hit=44832
Planning time: 0.182 ms
Execution time: 29.140 ms

Das “dirtied” und “written” Zeit braucht ist ja ok, aber soviel?

CLUSTER ist wohl keine Lösung, denn bei meinen Updates sind die Datensätze ja wieder auf verschiedene Pages verteilt.

Das Beispiel wird übrigens noch krasser, wenn ich folgendes SQL aufrufe:

select RI.SWIMRESULTID from RANKINGITEM RI 
where RI.SWIMRESULTID between 0 and 10000

Explain ergibt folgendes:

Index Only Scan using ix_rankingitem_swimresult on rankingitem ri  (cost=0.57..17442.90 rows=51011 width=4) (actual time=0.052..16698.903 rows=56753 loops=1)
  Index Cond: ((swimresultid >= 0) AND (swimresultid <= 10000))
  Heap Fetches: 19121
  Buffers: shared hit=32434 read=13562 dirtied=1833 written=90
Planning time: 0.155 ms
Execution time: 16702.595 ms

Was habe ich noch für Möglichkeiten, das zu verbessern?

Christian

sind die Zeiten reproduzierbar, oder kann es sein, daß beim lahmen Lauf grad viele andere Aktivitäten waren? Oder checkpoints? Hast Du da schnelle Platten oder eher welche mit der Performance von Lochbändern? PG-Version & OS?

Wenn ich das gleiche Explain mehrmals aufrufe, dann ist zuerst langsam mit vielen dirties / written, dann sind es weniger und die Ausführungszeit wird deutlich schneller.
Die DB wird für eine Website verwendet, je nach Betrieb 5000-10000 Seitenaufrufe pro Stunde, jeweils etwa 5-10 SQL Statements.
PG Version 10.4,
Windows Server 2012 R2
8 CPU, 8GB RAM
Harddisk SCSI, nicht ganz klar. Es ist ein virtueller Server.

Christian

virtuelles Windows in Produktiveinsatz - gruselig. Shared_buffers in der Config angepaßt?

Windows / virtuell: Nun ja, ich will das nicht verteidigen aber manchmal gibt es “äussere Vorgaben”.
shared_buffers habe ich auf 512MB - In Dokumentationen steht, man soll das bei Windows nicht auf 25% des RAM hochsetzen oder gilt das nicht mehr und ich wüde besser 2GB machen?

Christian

Mit einer aktuellen Postgres Version (wie die von Dir verwendete 10.4) ist es kein Problem shared_buffers auch unter Windows höher zu setzen. 2GB klingen für ein 8GB RAM System OK. Wenn sonst nichts auf dem Rechner läuft, würde ich vermutlich effective_cache_size auf 6GB setzen, damit der Planer einen Hinweis hat, wieviel Daten ge-cached sind (das belegt keinen Speicher, sondern ändert nur die Strategie des Planers)

Vielleicht macht es Sinn die (auto) vacuum Einstellungen der entsprechenden Tabelle(n) etwas aggressiver einzustellen, damit das schneller (=nach weniger Änderungen) aufräumt.