Performance-Tuning für Plattenzugriffe

Postgresql ist zu langsam. :slight_smile:

Ich arbeite mit Tabellen mit vielen Schreibzugriffen (inserts).
Dauerlast (von ca. 10 Clients, unter “Dauerfeuer”): Rund 4 einfache Inserts und 6 einfache Selects, gleichzeitig.

Die Datenmenge ist etwa 5 GB (ohne Indizes), die Tabellen recht einfach und mit relativ vielen Indizes versehen.
Die größte Tabelle mit ca. 40 Millionen Zeilen, etwa fünf andere Tabellen zwischen 1 und 10 Millionen Zeilen.
Einige wenige Constraints und Trigger, nichts wirklich komplexes.

Alles auf einer 3.5GHz-Maschine, mit SATA-Platte und linux unter ext3-fs. Der Server ist ein reiner DB-Server.

Mein Problem ist, daß Postgresql zu langsam ist. Ursache ist meines Erachtens die Festplattenperformance:

top sagt:
top - 20:30:40 up 86 days, 11:41, 1 user, load average: 3.22, 3.54, 3.52
Tasks: 73 total, 2 running, 71 sleeping, 0 stopped, 0 zombie
Cpu(s): 2.0% us, 0.3% sy, 0.0% ni, 0.0% id, 97.7% wa, 0.0% hi, 0.0% si
Mem: 2072892k total, 1977880k used, 95012k free, 4224k buffers
Swap: 3903752k total, 38112k used, 3865640k free, 1760664k cached

(man beachte die 97.7% wa.)

iostat sagt es deutlicher:
avg-cpu: %user %nice %system %iowait %steal %idle
0,33 0,00 0,33 99,33 0,00 0,00
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sdb 145,67 18,67 3506,67 56 10520

99.33% iowait :frowning:

Ich habe bisher versucht:

  • einen geclusterten Index anlegen (sehr großer Performancegewinn für Selects, die viele Einträge zurückgeben!)
  • überflüssige Indizes löschen (mässiger Performancegewinn für die Inserts)
  • Logging deaktivieren (mässiger Performancegewinn)
  • Inserts in grosse Transaktionen verpacken (brachte wenig)

RAID ist derzeit aus Zeit und Geldgründen nicht drin.

Ich hätte kein Problem, wenn die Indizes verzögert geschrieben werden.
Bringt tuning in der .conf-Datei was in Punkto Geschwindigkeit? - Derzeit sieht die so aus:

sort_mem=4096
shared_buffers = 1500 # min 16 or max_connections2, 8KB each
temp_buffers = 1000 # min 100, 8KB each
max_prepared_transactions =200 # can be 0 or more
work_mem = 4096 # min 64, size in KB
maintenance_work_mem = 65535 # min 1024, size in KB
max_stack_depth = 4096 # min 100, size in KB
max_fsm_pages = 24000 # min max_fsm_relations
16, 6 bytes max_fsm_relations = 500 # min 100, ~70 bytes each
bgwriter_delay = 200 # 10-10000 milliseconds between
bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
fsync = off # turns forced synchronization on or of
commit_delay = 0 # range 0-100000, in microseconds
commit_siblings = 5 # range 1-1000

log_parser_stats = on
log_planner_stats = on
log_executor_stats = on

stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
stats_reset_on_server_start = off

der Rest ist auf Standardwerten.

Ich bin für jeden Tip dankbar.


Thomas

Eine Kleinigkeit, die mir auffällt: wieso loggst du soviel mit?

Ansonsten: wenn I/O dein Problem ist, kann die Datenbank nicht viel (gar nichts ist auch falsch, aber nicht viel) tun. Wie wäre es mit wenigstens einer zweiten Platte, auf der man dediziert Tablespaces ablegen kann?

Was bringt denn die Platte für einen Durchsatz? Mal mit hdparm testen. Vielleicht kann man da was optimieren.

Japp. Zumindest das WAL auf einer anderen Spindel könnte schon was bringen.

Andreas

hmm - ich habe eine zweite platte drin (die ist derzeit unbenutzt).
da das system nur als db-server arbeitet, ist es aber eh (fast) nur die db, die auf die hd zugreift.

kann ich denn die tables auf der einen und die indices auf der anderen platte haben?

ich hatte die datenbank ursprünglich mal “normalisierter” konzipiert und werde wohl dahin zurückkehren (damit mutieren eine reihe von indizierten volltext-feldern zu simplen integern).

jetz bin ich erstmal drei tage weg und danke vorerst für die antworten!

thomas

Ja, das geht ab 8.x. CREATE TABLESPACE erzeugt einen neuen Tablespace, und bei CREATE INDEX oder CREATE TABLE kannst Du kann sagen, auf welchem Tablespace das gehen soll.

Indexe und suche in Textfeldern ist sicherlich deutlich teurer als bei INTs, sollte also auch helfen.

Andreas, schönes weekend wünschend

Schau Dir mal diesen Thread an:
http://lists.debian.org/debian-user-german/2007/01/msg00322.html

In Re: SATA Frust schreibt auch M. Ott, daß die Platte unter seinen Bedingungen ohne DMA läuft. Das könnte auch bei Dir der Fall sein und die Probleme erklären.

Also, hdparm anwerfen, nachschauen.

Andreas

ich nutze grml, das ist ein debian-basiertes system, das die beiden sata-platten ganz fein mit dma erkennt.

Um die weitergehende Normalisierung (und entsprechende Anpassung der Scripts) werde ich nicht rumkommen. Ansonsten ich hab mich mit diversen Alternativen rumgeschlagen:

  • Ausrangiertes SCSI-Array (gibts mit RAID5 oder 10 schon ab 75 Euro) kaufen
  • Die beiden SATA-Platten als Raid0 konfigurieren (=Neuinstallation)
  • Indizes und Daten trennen

Ich grüble noch, was ich versuchen soll.

Thomas

Okay, grml ist bekannt. :slight_smile:

Um die weitergehende Normalisierung (und entsprechende Anpassung der Scripts) werde ich nicht rumkommen. Ansonsten ich hab mich mit diversen Alternativen rumgeschlagen:

  • Ausrangiertes SCSI-Array (gibts mit RAID5 oder 10 schon ab 75 Euro) kaufen
  • Die beiden SATA-Platten als Raid0 konfigurieren (=Neuinstallation)
  • Indizes und Daten trennen

Ich grüble noch, was ich versuchen soll.

Thomas

Naja, Explain nannte ich wohl schon einmal…

Andreas

Aus dem Explain-Output werde ich nicht wirklich schlau - ich weiss auch nicht, ob sich das für meine eigentlich supersimplen Queries rentiert.
Ich nutze EXPLAIN gelegentlich, um zu sehen, ob meine Indizes verwendet werden.

Ich arbeite derzeit noch an der Problematik, dass PG (bei mir) keine Indizes verwendet, wenn es “NULL”-Werte in Felder gibt - ich habe das bei der Konzeption der Datenbank nicht berücksichtigt und stolpere da immer wieder drüber.

Inzwischen hab ich überall einen Default-Wert eingebaut und arbeite daran, alle Spalten auf NOT NULL zu kriegen. Bei 40 Millionen Datensätzen und einem Server, der eh schon am Limit arbeitet, ist ein “update table set spalte=0 where spalte IS NULL” aber ein sehr zähes Unterfangen.

Ich habe inzwischen einen Tablespace auf der zweite, bisher unbenutzten Festplatte eingerichtet und einige oft upgedatete Volltextindizes dorthin ausgelagert. Das Ergebnis ist prima: der WA ist von 99 auf rund 75 gefallen, die Inserts laufen spürbar schneller!

Wenn ich die DB noch umbaue, kann ich einen grossen Volltextindex zugunsten eines Integer-Index droppen - ich denke mal, dann wird das ganze nochmal um ein einiges schneller.

Thomas

Es gibt sicherlich immer eine Menge Möglichkeiten, wie man Performance zurückgewinnen kann, aber die meisten Tipps kann man nicht nach Schema F ansetzen, sonst wären sie ja schon eingebaut bzw. als Voreinstellung in der Config vorhanden.

Im Gegenteil: viele Einstellungen muss man abhängig vom Anwendungsfall und vom System einstellen und das erfordert jemanden mit etwas Hintergrundwissen und eine kleine Weile Beobachtung, um den Engstellen auf die Spur zu kommen.

Hattest du übrigends die ganzen Loggingeinstellungen mal ausgeschaltet wie in meinem ersten Post nachgefragt?

Naja, falls das geheim ist, versteh ich, daß Du ihn nicht zeigen willst :wink:

Ich arbeite derzeit noch an der Problematik, dass PG (bei mir) keine Indizes verwendet, wenn es “NULL”-Werte in Felder gibt - ich habe das bei der Konzeption der Datenbank nicht berücksichtigt und stolpere da immer wieder drüber.

Mmh. Das ist mir bisher noch nie aufgefallen, daß das so ist. PG ignoriert allerdings Indexe, wenn deren Anwendung keinen Sinn ergeben würde. Auch hier: Explain Analyse zeigt Details.

Ich habe inzwischen einen Tablespace auf der zweite, bisher unbenutzten Festplatte eingerichtet und einige oft upgedatete Volltextindizes dorthin ausgelagert. Das Ergebnis ist prima: der WA ist von 99 auf rund 75 gefallen, die Inserts laufen spürbar schneller!

Fein, ist ja schon mal was.

Wenn ich die DB noch umbaue, kann ich einen grossen Volltextindex zugunsten eines Integer-Index droppen - ich denke mal, dann wird das ganze nochmal um ein einiges schneller.

das würde ich jetzt auch vermuten…

Andreas

Nein, ich gestehe.
Nach diversen Problemen a la “warum wird mein index nicht verwendet” habe ich das logging “intensiviert”, um dann mit vacuum analyze dem queryplaner ein wenig zu helfen.

Ich wollte eigentlich die Originaleinstellungen (einer blanken Installation) wiederherstellen, weil ich ein wenig Angst habe, dass mir der Query-Planer ein ganz fehlendes Logging übelnimmt.

Ich versuch das mal die Tage.

Es ist vollbracht: Die DB läuft auf einem ausgemusterten Raid-0-Array:
6x18GB SCSI mit 10.000 U/Min in einem Proliant-Kasterl an einem antiken PERC 2, den ich mit 128MB Cache bestückt habe.

Der “Dauerdurchsatz” dieser Config ist mit rund 25MB/Sek. grauslig langsam und nicht halb so schnell wie die “alten” SATA2-HDs, aber es skaliert ganz wunderbar: Ab etwa vier bis fünf clients wird die SCSI-Konfiguration schneller als die SATA-Config - bei rund 30 gleichzeitigen Verbindungen (die alle die DB mit Selects und Inserts behämmern) ist die Konfiguration dann mehr als doppelt so schnell wie vorher!

Die Zahl der Spindeln scheint hier mehr zu bringen als der reine Durchsatz von HDs und Controllern.
Ich träume schon davon, 12x72GB mit 15.000 Runden einzubauen - ich bräucht nur noch einen Sponsor dafür :slight_smile:

Thomas