Aufräumen nach Updates

Allgemeines zu PostgreSQL.
Antworten
chkaufmann
Neuer Benutzer
Beiträge: 21
Registriert: Di 8. Mai 2018, 08:38

Aufräumen nach Updates

Beitrag von chkaufmann » Sa 10. Nov 2018, 12:53

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:

Code: Alles auswählen

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:

Code: Alles auswählen

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:

Code: Alles auswählen

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:

Code: Alles auswählen

select RI.SWIMRESULTID from RANKINGITEM RI 
where RI.SWIMRESULTID between 0 and 10000
Explain ergibt folgendes:

Code: Alles auswählen

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

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

Re: Aufräumen nach Updates

Beitrag von akretschmer » Sa 10. Nov 2018, 13:05

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

chkaufmann
Neuer Benutzer
Beiträge: 21
Registriert: Di 8. Mai 2018, 08:38

Re: Aufräumen nach Updates

Beitrag von chkaufmann » Mo 12. Nov 2018, 07:21

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

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

Re: Aufräumen nach Updates

Beitrag von akretschmer » Mo 12. Nov 2018, 08:50

virtuelles Windows in Produktiveinsatz - gruselig. Shared_buffers in der Config angepaßt?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

chkaufmann
Neuer Benutzer
Beiträge: 21
Registriert: Di 8. Mai 2018, 08:38

Re: Aufräumen nach Updates

Beitrag von chkaufmann » Mo 12. Nov 2018, 09:23

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

castorp
Erfahrener Benutzer
Beiträge: 1086
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Aufräumen nach Updates

Beitrag von castorp » Mo 12. Nov 2018, 09:44

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.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Antworten