Performance-Probleme / hohe CPU-Auslastung durch PostgreSQL 8.4 Server

Hallo,

ich habe ein Problem mit der Performance eines PostgreSQL 8.4-Servers unter OpenSuSE 10.3. auf einem Dual-Core AMD Opteron 2GHz Rechner.

Ich habe auf 8 Nodes eines Clusters parallel ein Programm laufen, dabei schreiben alle diese Rechner (jeder etwa alle 10 bis 30 Sekunden) über Tage hinweg Daten in eine gemeinsam genutzte PostgreSQL-DB auf dem Server. Die Einträge sind jeweils nur kurze Texteinträge, also keine Bilder, Videos oder ähnliches. Um einen Eintrag durchzuführen muss in einigen Fällen vorher mit einfachen SELECT-Queries getestet werden, ob ein bestmimter Wert schon in der DB ist. Auch nichts großes.

Dennoch ist das Bottleneck der Aktion momentan der DB-Server! :confused: Um genau zu sein, dessen CPU, wie man hier sieht. (Man beachte die Werte der PostgreSQL-Prozesse in der %CPU-Spalte!)

Irgendwas läuft erheblich falsch. Woran kann es liegen? Falsche Serverconfiguration? Ich benutze die Standard-PostgreSQL Configfiles von SuSE 10.3. Ich habe sie gerade nicht hier (bin mittlerweile zu Hause), kann sie aber bei Bedarf morgen früh gerne posten.

Ich habe außerdem den Eindruck, dass es anfangs besser lief und die Performacne mit der Zeit schlechter geworden ist. Hier ist der Load des Clusters kurz nach dem Start der Aktion. Alle ixion-Rechner sind Nodes und tragen die Daten auf den Server ganz unten rechts rechts ein. Nach ein paar Tagen sieht es allerdings so aus. Wie man sieht, rechnen die Nodes die meiste Zeit schon gar nicht mehr, weil sie sehr lange auf den DB-Server zu warten scheinen. In den ersten 2 Stunden wurden ~1600 Datensätze eingetragen, heute wurden in 6 Stunden 600 Datensätze eingetragen (es dauern nicht alle Berechnungen gleich lang, aber so groß sind die Unterschiede nicht und bei der Anzahl sollten sich lange und kurze definitiv ausgleichen).

Kann die CPU-Last dadurch kommen, dass das Programm irgendwelche Ressourcen auf dem Server verschwendet (dass es zB Connections nicht zu macht)? Man beachte allerdings, dass der RAM-Verbrauch nicht ansteigt und noch tonnenweise RAM da ist.

Außerdem ist komisch, dass meine Test-Queries sofort und schnell beantwortet werden, wenn ich mich über SSH auf den DB-Server einlogge, psql starte und Queries tippe.

Ich bin ratlos. Hat jemand eine Idee? Auf welche Config-Optionen oder Logfile-Einträge sollte ich achten?

Wenn du die Standardkonfiguration verwendest, hast du in der Tat Performanceprobleme.
Wie sehen die folgenden Werte aus: “shared_buffers”, “work_mem”?

Hast du deine Tabellen irgendwie partitioniert?

Weitere Frage: was meint EXPLAIN ANALYSE zu den Selects? Läuft Autovacuum?

Andreas

Vermutlich liegt das an den Standardeinstellungen. Wieviel RAM hast du denn auf dem Server für die DB zur Verfügung? Nutzt du wirklich noch openSuse 10.3? Oder ist das ein Tippfehler?

Gruß Uwe

Vielen Dank schonmal für das Interesse! Ich wollte nur kurz sagen, dass ich doch erst morgen wieder vor Ort sein werde und daher erst dann eure Fragen zur Konfiguration beantworten kann.

Ich kann zumindest schonmal sagen, dass die Tabellen nicht partitioniert sind (es sind 4 Tabellen, die größte von ihnen enthält 1,5 Millionen Datensätze, die kleinste 30.000). Alle Tabellen haben sequentielle PKs aber momentan noch keine Indices. Die SELECT-Queries sehen so aus:

SELECT some_field FROM some_table WHERE value1 = 'StringX' AND value2 = 'StringY';

Die Ausgabe von ANALYZE bringt ja, wenn ich das richtig verstehe, nur aus der wirklich gefüllten DB was, daher gibt’s die morgen.


Und der DB-Server ist ein recht gewöhnlicher Desktop-Rechner, also kein Clusternode. Er hat 3GB RAM und sonst nix zu tun, X11 ist aus (Runlevel 3). Mehr Infos dazu auch morgen.

Ohne Index? Dann hast du natürlich einen seq-scan laufen, der dauert halt. Je nach Länge der Strings da entweder ein Index über je die Spalten, oder kombiniert über beide, oder vielleicht auch als Hash über value1 || value2 und dann halt passend abfragen. Dazu passend shared_buffers einstellen, damit möglichst der Index zumindest im RAM bleibt. work_mem brauchst (bei dieser Abfrage) eher nicht viel.


Andreas

Oooooh, ich habe nach der Lektüre von akretschmers Post gerade mal einen Artikel über Indices gelesen und dabei festgestellt, dass ich da vorher etwas gehörig falsch verstanden hatte. Ich dachte, es sei geschickter, die erst nach dem Einfügen großer Datenmengen zu erstellen. Das gilt aber wahrscheinlich nicht, wenn man während des Einfügens schon Sachen in sehr großen Tabellen nachsehen muss, denke ich mal – zumindest bei mir, denn in der oben geposteten Prozessanzeige sieht man ja, dass die PG-Prozesse beim SELECT hängen (und nicht beim INSERT).

Das mit den fehlenden Indices wird bestimmt mein Problem sein! Vielen Dank schonmal, ich erstelle morgen die Indices und berichte was passiert ist (bzw. poste die Configfiles falls das noch nicht alles war).

Btw, erfreulicherweise hat im Query oben ‘StringX’ die Länge 4, wobei nur ein sehr kleiner Anteil an Zeilen die gleichen 4 Character enthält. Allerdings hast ‘StringY’ die Länge 1 und sehr viele der Einträge enthalten den gleichen Buchstaben. :expressionless:

EDIT: Nachdem was ich gerade noch anderswo gelesen habe kann man das Problem mit dem ‘StringY’ umgehen, wenn man einen Index über beide Spalten macht, wobei zuerst die X-Spalte angegeben wird.

Also, ich habe die Serverkonfiguration geändert und Indices erzeugt, jetzt sieht das alles sehr gut aus. Vielen Dank für alle Tipps!

Erstmal vorneweg: ich benutze SuSE 11.3, nicht 10.3 wie oben versehentlich geschrieben.

Zu den Fragen und als Erklärung, was ich gemacht habe:

@ads: In der alten Standardconfig war shared_buffers=64MB und work_mem=2MB. Ich habe das auf 768MB und 256MB gesetzt. Tabellen waren, wie oben erwähnt, nicht partitioniert und sind es auch jetzt nicht.

@akretschmer: Autovacuum war vorher an (in der Config war die Zeile auskommentiert, laut der Doku oben im File ist es dann an) und ich habe es jetzt explizit eingeschaltet, indem ich das Kommtantarzeichen vor der Zeile weggemacht habe. Zu EXPLAIN ANALYZE siehe unten.

@Gisman: Der Server hat 2GB RAM, nicht 3GB wie ich dachte (und muss außer PostgreSQL nichts tun). OpenSuSE 11.3.

Hier noch die EXPLAIN ANALZE-Ausgaben der SELECT-Queries (nach den gerade erwähnten Configänderungen). Den wirklichen Vorteil haben die Indices gebracht, denke ich:

ptgl=# SELECT count(sse_id) FROM plcc_sse;
  count  
---------
 1748151
(1 row)

ptgl=# SELECT count(contact_id) FROM plcc_contact;
  count  
---------
 1850452
(1 row)


ptgl=# EXPLAIN ANALYZE SELECT chain_id from plcc_chain WHERE pdb_id = '3kmf' AND chain_name = 'A';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Seq Scan on plcc_chain  (cost=0.00..4612.63 rows=1 width=4) (actual time=127.040..127.040 rows=0 loops=1)
   Filter: (((pdb_id)::text = '3kmf'::text) AND ((chain_name)::text = 'A'::text))
 Total runtime: 127.090 ms
(3 rows)

ptgl=# EXPLAIN ANALYZE SELECT sse_id from plcc_sse WHERE chain_id = '40';
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Seq Scan on plcc_sse  (cost=0.00..43011.68 rows=28 width=4) (actual time=1871.130..1871.130 rows=0 loops=1)
   Filter: (chain_id = 40)
 Total runtime: 1871.162 ms
(3 rows)

ptgl=# CREATE INDEX plcc_idx_sse_chainFK ON plcc_sse (chain_id);
CREATE INDEX
ptgl=# EXPLAIN ANALYZE SELECT sse_id from plcc_sse WHERE chain_id = '40';
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Index Scan using plcc_idx_sse_chainfk on plcc_sse  (cost=0.00..8.93 rows=28 width=4) (actual time=0.082..0.082 rows=0 loops=1)
   Index Cond: (chain_id = 40)
 Total runtime: 0.106 ms
(3 rows)

Ich habe außerdem alle Foreign Key-Felder indiziert (um JOINs und CASCADEs zu beschleunigen) und die Felder, auf denen WHERE-Bedingungen in SELECTs vorkommen (wie das im Beispiel-Code).

Nach all diesen Änderungen haben die Nodes gerade in 10 Minuten 300 Datensätze auf dem Server eingetragen, yay!

Vielen Dank nochmal an alle, das Forum hier hat mir in letzter Zeit schon ein paar mal sehr weitergeholfen. :sunglasses:



256 MByte für work_mem ist, hüstel, zu viel. Bedenke: work_mem wird pro Select und da wieder pro Knoten allokiert, der es braucht. Hast Du also 5 gleichzeitige Selects mit je 2 Nodes (welche work_mem brauchen), kann es Dir passieren, daß 2,56 GByte RAM ZUSÄTZLICH allokiert werden, was recht sicher in den SWAP führt. Und 5 Selects mit je 2 Node ist nicht gerade viel.


Andreas

Oook, ich habe es mal auf 32MB geändert und pg_ctl reload gemacht.

Einen wirklichen Vorteil hat dir außerdem “shared_buffers” gebracht.
Mit der Standardeinstellung hast du quasi ständig einen kalten Cache, weil die Datenbank die Daten da immer herauswirft und neue nachlädt.

Coole Sache, alles ist fertig und in die DB eingetragen. Die 3 größten Tabellen enthalten jetzt zusammen 7 Millionen Datensätze und es ging dank neuer Einstellungen und Indices echt flott: Ich hatte nach meinem letzten Post wegen Änderungen am Programm alles nochmal komplett neu gestartet und laut den Logs hat es diesmal unter 2 Tage gedauert. (Letztes Mal war nach 5 Tagen ungefähr die Hälfte eingetragen.)

Jetzt haben wir die Konfiguration betrachtet. Bleibt die Frage, wieso das immer noch 2 Tage und nicht 4 oder 5 Stunden dauert … sind immerhin nur 7 Millionen Einträge, das ist nicht sonderlich viel.

Das dauert nur so lange, weil die Nodes den Kram, den sie eintragen, vorher ausrechnen müssen (fieser scientific computing-Kram, der dauert halt). Hat mit dem DB-Server nichts mehr zu tun, man sieht am jetzigen Load deutlich, dass mittlerweile die CPUs der Nodes das Bottleneck sind, dem DB-Server ist eher langweilig. :wink:

(Dass auf dem früher geposteten Bild der CPU-Load der Nodes relativ niedrig erscheint (ungefähr 1/8 Auslastung) liegt daran, dass die Angabe auf dem Bild sich auf alle 8 CPUs des jeweiligen Nodes bezieht, das Programm aber aus diversen Gründen jeweils nur einen davon benutzt – den aber zu 100%.)