Optimierung der PostgreSQL-Datenbankleistung

Optimierung der PostgreSQL-Datenbankleistung

Bei der Arbeit stößt jeder Programmierer oder Systemadministrator auf ein schreckliches Problem - die Datenbank wird langsamer. Der Server arbeitet langsam, etwas Unverständliches passiert, die Benutzer beschweren sich, der Kunde flucht. Ich habe nicht umsonst geschrieben, dass das Problem beängstigend ist, vor allem, weil die Lösung hier nicht offensichtlich ist und nicht auf der Oberfläche liegt. Viele Parameter können die Leistung der Basis beeinflussen.

In diesem Artikel habe ich versucht, die meisten von ihnen zu überprüfen, und ich werde auch die Befehle auflisten, die mir helfen, die Gründe für eine langsame Server- oder Datenbankleistung zu verstehen.

Im Folgenden werde ich die Schritte auflisten, die Sie durchlaufen sollten, um die Ursachen des Problems zu verstehen.

1. Schauen Sie sich an, was vorher gemacht wurde.

Hierfür sollte Ihre Anwendung über Git verfügen. Schauen Sie sich an, was zuvor getan wurde. Welche Migrationen Sie durchgeführt haben, welche Einstellungen Sie geändert haben.

Und es ist wünschenswert, dass die Hauptkonfigurationsdatei der Datenbank postgresql.conf ebenfalls in Git liegt. Dann können Sie auch die Änderung der Datenbankeinstellungen kontrollieren.

Natürlich sollten Sie es sich zur Regel machen, keine Änderungen direkt an der Datenbank vorzunehmen, sondern alles über Git und Migrationen zu erledigen.

Vor allem, wenn es um Änderungen an der Datenbankstruktur, das Hinzufügen von Handlern, benutzerdefinierten Funktionen und Prozeduren geht. Diese Arbeiten sollten durch Skripte durchgeführt werden, die wiederum versioniert werden sollten.

2. Wir schauen uns an, was im Betriebssystem passiert.

Wenn es keine Änderungen im Git gab und das Problem unerwartet auftrat, muss als Nächstes untersucht werden, was im Betriebssystem vor sich ging.

Hoffentlich haben Sie die Datenbank auf einem Linux-Rechner installiert. Wenn nicht, schlage ich vor, dass Sie einen Wechsel zu Linux in Betracht ziehen.

Erinnern Sie sich daran, welche Antivirenprogramme oder Firewalls Sie installiert haben. Überprüfen Sie, welche Anwendungen oder Dienste außer der Datenbank auf dem Server laufen. Optimal ist es, wenn nur die Datenbank auf dem Server läuft und sonst nichts. Mit den aktuellen Virtualisierungstechnologien können Sie dies sehr schnell und einfach tun.

3. Überprüfen Sie die Hardware.

Auch hier sollten Sie, wenn Sie nichts am Betriebssystem geändert haben und Sie bereits Linux einsetzen, auf die Hardware achten. Heutzutage lassen einige Hersteller von Hardware die Qualität sinken und Sie können nicht darauf hoffen, dass der Speicher oder die Festplatte nicht im unpassendsten Moment ausfällt. Daher ist das erste, was ich Ihnen raten, die Aufmerksamkeit zu zahlen:

htop

Wenn Sie sehen, dass der Prozessor zu 70 % oder mehr ausgelastet ist und alle Kerne belastet sind, sollten Sie die Leistung erhöhen. Prozessoren sind im Moment billig, und wenn Sie Virtualisierung verwenden, sollten Sie eine zusätzliche Anzahl von Kernen zu Ihrer Basis hinzufügen. Höchstwahrscheinlich ist Ihre Basis aufgrund der erhöhten Last überlastet und Sie sollten ein Upgrade Ihres Servers in Betracht ziehen.

iostat -dx

Achten Sie auf die letzte Spalte. Sie gibt Ihnen einen Eindruck von der Auslastung des Disk-Subsystems. Wenn der Indikator nahe bei 100% liegt, sollten Sie darüber nachdenken, den Speicher zu vergrößern. Wahrscheinlich haben Sie eine sehr große Datenbank und zu wenig RAM auf dem Server, um die Daten zu verarbeiten.

Denken Sie daran, dass sich die Kosten für die Optimierung von Abfragen oder Anwendungscode nicht auszahlen, wenn Sie Hardwareprobleme haben.

4. Installieren Sie ein Überwachungssystem.

Versuchen Sie, das Percona-Überwachungssystem https://www.percona.com/software/database-tools/percona-monitoring-and-management zu installieren. Seine Schnittstelle ist in der Abbildung oben dargestellt. Es ist kostenlos und ermöglicht Ihnen eine schnelle Bewertung der Serverlastdynamik und zeigt alle wichtigen Leistungsindikatoren des Servers an.

5. Analysieren Sie den Dateispeicher.

Führen Sie den Befehl aus:

iostat -x

Sehen Sie sich die Spalten mit den Postfixen _await an. Sie geben Auskunft über die f/s-Latenzanzeige. Wenn der Indikator mehr als 50 ms ohne Last beträgt, sollten Sie darüber nachdenken. Liegt er über 100, sollten Sie dringend Maßnahmen ergreifen.

Der Grund für hohe Werte kann eine falsche Hypervisor-Konfiguration oder der Basisbetrieb über NAS sein. Der letztgenannte Ansatz sollte bei der Konzeption vermieden werden.

6. Analysieren Sie pg_stat_activity.

Wir haben also die Server und Geräte überprüft und nichts Ungewöhnliches gefunden. Wenden wir uns direkt dem Tuning der Basis zu.

Das erste, was uns in den Sinn kommt, ist die Ansicht pg_stat_activity.

Überprüfen Sie zunächst den Parameter track_activity_query_size in den Datenbankeinstellungen, standardmäßig ist er auf 1024 eingestellt. Erhöhen Sie ihn mindestens um das 2-3-fache, in den meisten Fällen reicht das nicht aus.

Jetzt suchen wir nach der Aktivität, die in der Datenbank stattfindet. Vielleicht ist alles viel einfacher und jemand hat eine komplexe Abfrage gestartet und Sie sollten dieses Skript stoppen. Schauen wir uns die aktiven Abfragen an, die länger als 5 Sekunden dauern:

SELECT now() - query_start as „runtime“, usename, datname, wait_event, state, query FROM pg_stat_activity WHERE now() - query_start > '5 seconds'::interval and state='active' ORDER BY runtime DESC;

Abfragen mit „Leerlauf“-Zustand können ebenfalls Verdacht erregen. Und noch schlimmer ist es mit dem Status „idle in transaction“.

Solche Anfragen können mit den folgenden Befehlen gestoppt werden:

SELECT pg_cancel_backend(procpid);

SELECT pg_terminate_backend(procpid);

Der erste Befehl stoppt aktive Anfragen, der zweite den Idle-Typ.

Jetzt lohnt es sich zu sehen, ob Sie irgendwelche hängenden Transaktionen haben. Führen Sie die Abfrage aus:

SELECT pid, xact_start, now() - xact_start AS duration FROM pg_stat_activity WHERE state LIKE '%transaction%' ORDER BY 3 DESC;

Denken Sie daran, dass Transaktionen sofort ausgeführt werden müssen. Schauen Sie sich in der Antwort die Dauer an. Wenn eine Transaktion mehrere Minuten oder gar Stunden hängen bleibt, bedeutet dies, dass sich die Anwendung falsch verhalten hat und die Transaktion unvollständig ist. Dies wirkt sich auf Replikation, VACUUM und WAL aus.

7. Analysieren Sie pg_stat_statements.

Wenn wir zuvor die aktiven Abfragen überprüft haben, ist es nun an der Zeit, zu analysieren, was vorher passiert ist. Die Ansicht pg_stat_statements wird uns dabei helfen. Normalerweise ist sie jedoch standardmäßig deaktiviert, wir sollten sie aktivieren. Dazu fügen wir die folgende Zeile in die Basiskonfiguration ein:

shared_preload_libraries = 'pg_stat_statements'

Führen Sie dann in der aktuellen Sitzung mit der Datenbank den folgenden Befehl aus:

create extension pg_stat_statements;

Wenn Sie vorhaben, sich mit der Leistung der Datenbank zu beschäftigen, brauchen Sie diese Erweiterung. Viele Dinge werden Sie ohne sie nicht tun können. Natürlich wird sie etwas Platz auf Ihrer Festplatte beanspruchen, aber im Vergleich zu den Vorteilen ist das nicht so schlimm.

Mit ihr können Sie Statistiken über die von Ihnen bereits durchgeführten Abfragen erhalten.

Zum Beispiel können wir mit diesem Befehl die Abfragen mit der höchsten CPU-Last ermitteln:

SELECT substring(query, 1, 50) AS short_query, round(total_time::numeric, 2) AS

total_time, calls, rows, round(total_time::numeric / calls, 2) AS avg_time, round((100 *

total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu FROM

pg_stat_statements ORDER BY total_time DESC LIMIT 20;

Und die längsten möglichen Abfragen sind wie folgt:

SELECT substring(query, 1, 100) AS short_query, round(total_time::numeric, 2) AS

total_time, calls, rows, round(total_time::numeric / calls, 2) AS avg_time, round((100 *

total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu FROM

pg_stat_statements ORDER BY avg_time DESC LIMIT 20;

Auf diese Weise können wir nachvollziehen, welche Abfragen die Belastung unserer Datenbank am stärksten beeinträchtigen, und sie gegebenenfalls optimieren.

8. Arbeiten mit der Ansicht pg_stat_user_tables

Die Tabelle pg_stat_user_tables ist ein entscheidender Wettbewerbsvorteil gegenüber anderen Datenbanken. Wir verwenden sie, um Informationen über sequenzielle Lesevorgänge von einer Festplatte zu erhalten, zum Beispiel:

SELECT schemaname, relname, seq_scan, seq_tup_read, seq_tup_read / seq_scan AS avg, idx_scan

FROM pg_stat_benutzer_tabellen

WHERE seq_scan > 0

ORDER BY seq_tup_read DESC

LIMIT 25;

An der Spitze der Tabellen stehen nur die Operationen, die Daten von der Festplatte lesen. Wenn die Tabelle mehr als 2000 Datensätze enthält, sollten solche Operationen per Index und nicht von der Festplatte ausgeführt werden. Das heißt, Sie erhalten die Abfragen, die Sie zuerst optimieren müssen.

Sie sollten sich auch die Zwischenspeicherung dieser Tabellen durch die Ansicht pg_statio_user_tables ansehen.

Die Spalten heap_blks... und idx_blks...

9. Konfigurieren des Speichers für PostgreSQL.

Abhängig von den Servereinstellungen werden Ihre Einstellungen ungefähr wie folgt sein:

effective_cache_size - 2/3 RAM

shared_buffers = RAM/4

temp_buffers = 256MB

work_mem = RAM/32

wartung_work_mem = RAM/16

Ich empfehle jedoch, spezielle Konfiguratoren zu verwenden:

http://pgconfigurator.cybertec.at/ - erweiterter Konfigurator von Cybertec.

https://pgtune.leopard.in.ua/ - Online-Version des klassischen Konfigurators pgtune.

Diese Tools werden Ihnen helfen, die richtigen Einstellungen für Sie vorzunehmen.

10. Konfigurieren des Disk-Subsystems.

Wenn nichts anderes geholfen hat, können Sie als letzten Ausweg die folgenden Einstellungen vornehmen:

fsync=aus

full_page_writes=aus

synchronous_commit=off

In diesem Fall verlieren Sie jedoch an Zuverlässigkeit bei der Datenspeicherung. Wenn PostgreSQL jedoch nicht das einzige System für die Datenverarbeitung ist und die Datenbank asynchron auf analytische Systeme kopiert wird, können Sie mit diesen Einstellungen leben. Denn sie reduzieren die Belastung der Festplatte. Lassen Sie mich ein wenig über diese Einstellungen erklären:

fsync - Protokolldaten werden zwangsweise aus dem Cache des Betriebssystems auf die Festplatte gedumpt.

full_page_write - 4KB OS und 8KB Postgres.

synchronous_commit - die Transaktion wird nur abgeschlossen, wenn die Daten tatsächlich auf die Festplatte übertragen werden.

checkpoint_completion_target - je näher der Wert bei eins liegt, desto weniger E/A-Spitzen bei Checkpoint-Operationen.

Außerdem gibt es effective_io_concurrency - die Anzahl der Datenträger und random_page_cost - das Verhältnis von zufälligen zu sequentiellen Lesevorgängen.

Sie wirken sich nicht direkt auf die Leistung aus, können aber die Arbeit des Optimierers erheblich beeinflussen.

11. Konfigurieren des Optimierers.

join_collapse_limit - wie viele Permutationen sinnvoll sind, um den optimalen Abfrageplan zu finden. Dies ist ein interessanter Parameter. Er ist standardmäßig auf 10 gesetzt, es kann sinnvoll sein, ihn um das 10-20fache zu erhöhen.

default_statistics_target - Anzahl der Datensätze, die beim Sammeln von Statistiken über Tabellen angezeigt werden. Je höher diese Zahl ist, desto schwieriger ist es, Statistiken zu erstellen. Statistiken werden z. B. benötigt, um die „Dichte“ von Daten zu bestimmen.

online_analyze - ermöglicht die sofortige Aktualisierung der Statistiken

online_analyze.enable = on

online_analyze.table_type = „all“

geqo - ermöglicht die genetische Optimierung von Abfragen

enable_bitmapscan = ein

enable_hashagg = ein

enable_hashjoin = ein

enable_indexscan = ein

enable_indexonlyscan = ein

enable_material = ein

enable_mergejoin = ein

enable_nestloop = ein

enable_seqscan = ein

enable_sort = ein

enable_tidscan = ein

12. optimieren Sie die Abfragen.

Sie haben also schwere Abfragen gefunden. Erklären oder analysieren Sie sie, und das erste, worauf Sie achten sollten, sind die folgenden Ausdrücke:

- Seq Scan - bedeutet, dass die Abfrage durch eine sequentielle Suche von Tabellenzeilen erfolgt.

- Verschachtelte Schleifen - Verbindung mit verschachtelten Schleifen.

Zum Beispiel können Sie durch die folgende Antwort der Datenbank alarmiert werden:

Seq Scan ON test (cost=0.00..40.00 ROWS=20)

In der Antwort können wir auch sehen, wie viele Zeilen verarbeitet werden, sowie die Kosten für die erste Zeile..alle Zeilen. Es zählt auf eine ziemlich originelle Weise, es lohnt sich, hier einen relativen Vergleich anzustellen.

Auch, wenn Seq Scan auf einer Tabelle, wo Zeilen sind mehr als mehrere tausend und es ist ein FILTER - in diesem Fall sollten Sie natürlich auf die Felder in der FILTER und finden Sie einen geeigneten Index. Wenn Sie ihn nicht finden - Bingo, Sie haben eines der Probleme gelöst.

Ich werde die Abfrageoptimierung nicht im Detail beschreiben, sie ist Gegenstand eines eigenen Artikels.

Die Logik beim Lesen des Abfrageplans ist einfach:

1) Schauen Sie sich den Operator mit den höchsten Kosten an

2) Handelt es sich um Seq Scan oder verschachtelte Schleifen?

3) Schauen Sie sich den Operator mit den nächsthöheren Kosten an.

Die Optimierung endet meist damit, dass entweder ein Index hinzugefügt wird, die Abfrage vereinfacht wird (Aufteilung, Verwendung verschachtelter Tabellen usw.) oder die Statistiken aktualisiert werden, was merkwürdigerweise der Fall ist.

Ich empfehle die Nutzung des folgenden Dienstes, um Pläne zu lesen:

https://tatiyants.com/pev

Was ist, wenn das Problem auf prod reproduziert wird?

In diesem Fall müssen Sie den Parameter auto_explain verwenden. Er ermöglicht es Ihnen, die Antwort des Optimierers in einem Ordner zu speichern. Er wird durch den folgenden Parameter aktiviert:

session_preload_libraries = 'auto_explain'

auto_explain.log_min_duration = '3s'

auto_explain.log_analyze = true

Jetzt müssen wir die Dateien auf eine schöne Art und Weise anzeigen. Hier wird uns pgBadger helfen. Es erzeugt schönes HTML, aber man kann in den Einstellungen „ertrinken“.

https://github.com/darold/pgbadger

So sieht die einfachste Einstellung aus:

pgbadger -j 4 -p '%t [%p]:[%l-1]' /var/log/postgresql/postgresql-9.6-main.log -o bad.html

Auf Prod ist auto_explain am besten nicht aktiviert oder nur als letztes Mittel und für kurze Zeit. In Fällen, in denen Sie keinen Ressourcenabzug haben.

Es lohnt sich, die am häufigsten auftretenden Fehler hervorzuheben:

• Durchsuchen der Datenbank mit LIKE '%spoon%'. In diesem Fall sollten Sie erwägen, die Suche auf ElasticSearch zu verlagern. Oder verwenden Sie FULL TEXT SEARCH.
• Es wurde kein Caching-System implementiert. Dann sollten Sie sich diese Technologie ansehen: https://github.com/ohmu/pgmemcache. Führen Sie nach dem Herunterladen und Installieren folgende Schritte aus:

shared_preload_libraries = 'pgmemcache'

CREATE EXTENSION pgmemcache;

memcache_server_add('hostname:port'::TEXT)

Verwendung:

memcache_add(schlüssel::TEXT, wert::TEXT)

newval = memcache_decr(schlüssel::TEXT)

memcache_delete(schlüssel::TEXT)

Wenn Sie eine Zwischenspeicherung innerhalb des RDBMS oder eine temporäre Tabelle im Speicher benötigen. Aber oft ist es bequem, ein ORM zu verwenden, oder das Framework hat Einschränkungen, oder Sie müssen nur schnell die Tabelle mit Inmemory-KV-Speicher ersetzen.

- PostgreSQL wird als OLAP verwendet. Die einfachste Option ist die schnelle Migration von Daten in einen Columnstore. https://github.com/citusdata/cstore_fdw

In diesem Artikel habe ich die wichtigsten Probleme aufgeführt, mit denen Entwickler bei der Arbeit mit Datenbanken konfrontiert werden. Wenn Sie einen interessanten Fall oder eine interessante Erfahrung haben, würde ich mich freuen, wenn Sie mir diese per E-Mail zukommen lassen.