Hallo,
wir verarbeiten große Mengen an Messdaten und sind mit der bisherigen Lösung mittels MSSQL-Datenbank an die Leistungsgrenzen gestoßen.
Dann sind wir auf TimescaleDB gekommen und haben somit auch den ersten Kontakt zu Postgres.
Wir haben ein System aufgesetzt und zum Testen 300 GB an Daten erzeugt. Die Einfügegeschwindigkeit ist nahezu konstant geblieben, wirklich sehr gut. Leider ist aber die Abfragegeschwindigkeit sehr durchwachsen. Aufgefallen ist uns auch, dass die 24 Worker insgesamt nur ca. 50 MB Arbeitsspeicher verwenden.
Von Microsoft sind wir gewohnt, dass der Server so viel Speicher wie konfiguriert verwendet, um diverse Indizes zu cachen.
Unser System:
Windows System 2016, 64 GB Arbeitsspeicher, RAID 0 über 6 HDDs mit insgesamt 6 TB Speicher
Ich habe die Konfiguration angehängt. Die Konfiguration wurde mittels timescaledb-tune optimiert. Mir ist dabei aufgefallen, dass bei shared_buffers nur 512 MB eingetragen. Hab es auch probiert, den Wert auf 16 GB zu ändern, leider ohne wirklicher Änderung im Verhalten.
Ist die Konfiguration so richtig?
Bitte um Hilfe
Grüße,
antispam88
postgres.7z (7.14 KB)
PostgreSQL läuft zwar unter Windows, aber gerade Dinge wie die Verwaltung von großen RAM-Mengen (shared buffers) unter Windows ist, ähm, nicht so toll.
Ansonsten: schaue Dir die Abfragen mit EXPLAIN (ANALYSE, BUFFERS) an, um die Ausführungspläne etc. zu sehen. Sind da SSD’s im Einsatz?
Postgres setzt sehr stark auf den Cache des Dateisystems und verwaltet wenig eigene. Eine Erhöhung von shared_buffers auf 16GB verlagert letztendlich das Caching nur. Die größte Auswirkung für Postgres ist die Tatsache, dass der Optimizer bei mehr Speicher verstärkt Indizes verwendet - aber die korrekte Einstellung von effective_cache_size hat einen ähnlichen Effekt.
Für komplexe Abfragen mit Aggregationen spielt work_mem eine große Rolle. Bei 64GB Hauptspeicher würde ich deutlich mehr als die 18MB verwenden. Das hängt natürlich davon ab, wie viele gleichzeitige Abfrage Du erwartest, aber wenn das eher wenige sind, würde ich mal mit 128MB oder 256MB anfangen. Genauen Aufschluss kann aber nur ein Execution Plan mit explain (analyze, buffers) geben, wie akretschmer schon erwähnt hat.
Wie gesagt: die Einstellung passt schon, der Optimizer verwendet diese Information schon bei der Entscheidung wie “teuer” ein Zugriff über einen Index ist.
Aber ohne den Execution plan der mit explain (analyze, buffers) erstellt wurde, kann man nicht wirklich sagen ob die Einstellungen passen. In vielen Fällen ist auch nicht die Konfiguration von Postgres für unbefriedigende Performance verantwortlich, sehr häufig kann man durch Umschreiben der Abfragen oder das Anlegen anderer Indizes wesentlich mehr herausholen.
Wie am Anfang der Datei erklärt ist, sind das die Defaultwerte die verwendet werden, wenn die Datei nicht geändert wurde. Die kann und darf man natürlich verändern.
Man sollte versuchen zu verstehen, was die Parameter bedeuten. “random_page_cost” gibt an, wie ‘teuer’ ein Zugriff auf einen zufälligen Block auf Disk ist, im Vergleich zu sequential page cost. (hier wird sequentiell, also linear gelesen). Das ist auf 1 und sollte so bleiben. Setzt man random_page_cost runter (wie Du es IIRC getan hast) bevorzugt man letztlich Index-Scans, also diese werden eher eingesetzt. Das kann Sinn machen bei SSD’s, die faktisch keine Positionierungszeiten haben, bei sich drehenden Scheiben würde ich random page cost eher bei 4 lassen.
Viel mehr können wir jetzt nicht sagen, ohne die Abfrage und das EXPLAIN (analyse, buffers) zu sehen.