Postgres 11.2 + TimescaleDB 1.2.1

Konfiguration des PostgreSQL Datenbankservers.
Antworten
antispam88
Neuer Benutzer
Beiträge: 2
Registriert: Sa 9. Mär 2019, 13:54

Postgres 11.2 + TimescaleDB 1.2.1

Beitrag von antispam88 » Sa 9. Mär 2019, 14:24

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
Dateianhänge
postgres.7z
(7.14 KiB) 15-mal heruntergeladen

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

Re: Postgres 11.2 + TimescaleDB 1.2.1

Beitrag von akretschmer » Sa 9. Mär 2019, 14:54

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

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

Re: Postgres 11.2 + TimescaleDB 1.2.1

Beitrag von castorp » Sa 9. Mär 2019, 15:46

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

antispam88
Neuer Benutzer
Beiträge: 2
Registriert: Sa 9. Mär 2019, 13:54

Re: Postgres 11.2 + TimescaleDB 1.2.1

Beitrag von antispam88 » Mo 11. Mär 2019, 06:56

akretschmer hat geschrieben:
Sa 9. Mär 2019, 14:54
Ansonsten: schaue Dir die Abfragen mit EXPLAIN (ANALYSE, BUFFERS) an, um die Ausführungspläne etc. zu sehen. Sind da SSD's im Einsatz?
Als Datenspeicher sind sechs SCSI-Platten in einem RAID-0 im Einsatz.
castorp hat geschrieben:
Sa 9. Mär 2019, 15:46
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 kann ich dem Optimizer mehr Speicher geben? effective_cache_size ist bei mir bei 49125MB, also bei 3/4 vom Hauptspeicher.
Bei work_mem kann ich noch versuchen, den Speicher zu erhöhen. Habe auch schon die Einstellungen mittels https://pgtune.leopard.in.ua/#/ versucht zu optimieren. Hier wurde mir unter anderm vergeschlagen, den Wert random_page_cost auf 4 zu erhöhen - ohne große Auswirkungen.

Insgesamt ist mir aufgefallen, dass viele Parameter auskommentiert sind (z.B. #temp_buffers o. #autovacuum_work_mem). Soll man diese Parameter nicht mehr verändert?

Hat jemand für Windows, im Speziellen für Server 2016, eine passende Konfiguration? Ich weiß, dass es die eine richtige nicht gibt, aber einfach als Anhaltspunkt.

Vielen Dank
antispam88

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

Re: Postgres 11.2 + TimescaleDB 1.2.1

Beitrag von castorp » Mo 11. Mär 2019, 08:00

antispam88 hat geschrieben:Wie kann ich dem Optimizer mehr Speicher geben? effective_cache_size ist bei mir bei 49125MB, also bei 3/4 vom Hauptspeicher.
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.
antispam88 hat geschrieben:Insgesamt ist mir aufgefallen, dass viele Parameter auskommentiert sind (z.B. #temp_buffers o. #autovacuum_work_mem). Soll man diese Parameter nicht mehr verändert?
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.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

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

Re: Postgres 11.2 + TimescaleDB 1.2.1

Beitrag von akretschmer » Mo 11. Mär 2019, 08:20

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

Antworten