Optimieren von E/A-Vorgängen (Input/Output) für PostgreSQL

PostgreSQL ist eine der beliebtesten Open-Source-Datenbanken der Welt und verfügt über erfolgreiche Implementierungen in mehreren geschäftskritischen Umgebungen in verschiedenen Domänen, wobei High-End-OLTP-Anwendungen in Echtzeit verwendet werden, die Millionen und Milliarden von Transaktionen pro Tag durchführen. PostgreSQL I/O ist ziemlich zuverlässig, stabil und performant auf so ziemlich jeder Hardware, sogar in der Cloud.

Um sicherzustellen, dass Datenbanken in der erwarteten Skalierung und mit den erwarteten Antwortzeiten ausgeführt werden, ist ein gewisses Maß an Performance Engineering erforderlich. Nun, das Erreichen einer guten Datenbankleistung hängt von verschiedenen Faktoren ab. Die Datenbankleistung kann aus verschiedenen Gründen beeinträchtigt werden, z. B. durch die Dimensionierung der Infrastruktur, eine ineffiziente Datenbankwartungsstrategie, schlechten SQL-Code oder schlecht konfigurierte Datenbankprozesse, die nicht alle verfügbaren Ressourcen nutzen – CPU, Arbeitsspeicher, Netzwerkbandbreite und Festplatten-E/A.

Was kann dazu führen, dass sich die Datenbankleistung verschlechtert?

1. Schlecht geschriebene Abfragen mit schlechten Joins, Logik usw. die viel CPU und Speicher beanspruchen
2. Abfragen, die vollständige Tabellenscans für große Tabellen aufgrund einer unsachgemäßen Indizierung durchführen
3. Schlechte Datenbankwartung ohne ordnungsgemäße Statistiken
4. Ineffiziente Kapazitätsplanung, die zu unzureichend dimensionierter Infrastruktur führt
5. Unsachgemäßes logisches und physisches Design
6. Es gibt kein Verbindungspooling, das dazu führt, dass Anwendungen eine große Anzahl von Verbindungen auf unkontrollierbare Weise herstellen

Das sind also viele potenzielle Bereiche, die Leistungsprobleme verursachen können. Einer der wichtigsten Bereiche, auf die ich mich in diesem Blog konzentrieren möchte, ist die Optimierung der PostgreSQL-E/A-Leistung (Input/Output). Die Optimierung der Eingabe-/Ausgabevorgänge von PostgreSQL ist von entscheidender Bedeutung, insbesondere in einer transaktionsintensiven Umgebung wie OLTP oder in einer Data-Warehousing-Umgebung mit komplexer Datenanalyse auf riesigen Datensätzen.

In den meisten Fällen werden Probleme mit der Datenbankleistung hauptsächlich durch hohe E/A-Vorgänge verursacht. Das bedeutet, dass Datenbankprozesse mehr Zeit damit verbringen, entweder auf den Datenträger zu schreiben oder von ihm zu lesen. Jeder Echtzeitdatenvorgang ist E/A-gebunden, es muss unbedingt sichergestellt werden, dass die Datenbank E/A-optimiert ist. In diesem Blog konzentriere ich mich auf häufige E/A-Probleme, auf die PostgreSQL-Datenbanken in Echtzeit-Produktionsumgebungen stoßen können.

Optimieren von PostgreSQL-E/A

Die Optimierung von PostgreSQL I/O ist für den Aufbau einer hochleistungsfähigen und skalierbaren Datenbankarchitektur unerlässlich. Schauen wir uns verschiedene Faktoren an, die sich auf die E/A-Leistung auswirken:

1. Indizierung
2. Aufteilend
3. Kontrollpunkte
4. VAKUUM, ANALYSIEREN (mit FILLFACTOR)
5. Andere E/A-Probleme
6. PostgreSQL I/O in der Cloud
7. Werkzeuge

Indizierung

Die Indizierung ist eine der wichtigsten Optimierungstechniken, die eine wichtige Rolle bei der Verbesserung der Datenbank-E/A-Leistung spielt. Dies gilt wirklich für jede Datenbank. PostgreSQL unterstützt verschiedene Indextypen, die Lesevorgänge erheblich beschleunigen können, was zu einer verbesserten Skalierbarkeit für Anwendungen führt. Das Erstellen von Indizes ist zwar recht einfach und unkompliziert, aber für Datenbankadministratoren und Entwickler ist es wichtig, dass sie wissen, welche Art von Index für welche Spalten ausgewählt werden soll. Letzteres basiert auf verschiedenen Faktoren wie Abfragekomplexität, Datentyp, Datenkardinalität, Schreibvolumen, Datengröße, Festplattenarchitektur, Infrastruktur (Public Cloud, Private Cloud oder On-Premises) usw.

Die Indizierung kann zwar die Leseleistung von Abfragen erheblich verbessern, aber auch die Schreibvorgänge in den indizierten Spalten verlangsamen. Schauen wir uns ein Beispiel an:

Auswirkungen von Indizes auf READ-Vorgänge

Eine Tabelle namens emp mit rund 1 Million Zeilen.

READ-Leistung ohne Index

postgres=# select * from emp where eid=10;

eid | ename | peid | did | doj

—–+—————+——–+——+————

10 | emp | | 1 | 2018-06-06

(1 row)

 

Time: 70.020 ms => Es dauerte ungefähr 70+ Millisekunden, um mit einer Zeile zu antworten

READ-Leistung mit einem Index

Lassen Sie uns einen Index für die eid-Spalte setzen und den Unterschied sehen

postgres=# create index indx001 on emp ( eid );

CREATE INDEX

postgres=# select * from emp where eid=10;

eid | ename | peid | did | doj

——+————-+——-+——+————

10 | emp | | 1 | 2018-06-06

(1 row)

 

Time: 0.454 ms => 0.4+ Millisekunden!! Das ist ein großer Unterschied – ist es nicht?

Daher ist die Indizierung wichtig.

Auswirkungen von Indizes auf WRITE-Vorgänge

Indizes verlangsamen die Leistung von Schreibvorgängen. Während sich die Indizes auf alle Arten von Schreibvorgängen auswirken, sehen wir uns einige Analysen zu den Auswirkungen von Indizes auf INSERTs an

Einfügen von 1 Million Zeilen in eine Tabelle ohne Indizes

postgres=# do $$

postgres$# declare

postgres$# i integer;

postgres$# begin

postgres$# for i in 1..1000000 loop

postgres$# insert into emp values (i,'emp',null,1,current_date);

postgres$# end loop;

postgres$# end $$;

DO

Time: 4818.470 ms (00:04.818) => Dauert ca. 4,8 Sekunden

Einfügen der gleichen 1 Million Zeilen mit einem Index

Lassen Sie uns zuerst einen Index erstellen

postgres=# create index indx001 on emp ( eid );

CREATE INDEX

postgres=# do $$

postgres$# declare

postgres$# i integer;

postgres$# begin

postgres$# for i in 1..1000000 loop

postgres$# insert into emp values (i,'emp',null,1,current_date);

postgres$# end loop;

postgres$# end $$;

DO

Time: 7825.494 ms (00:07.825) => Dauert ca. 7,8 Sekunden

Wie wir also beobachten können, hat sich die INSERT-Zeit mit nur einem Index um 80 % erhöht und kann viel länger dauern, wenn mehrere Indizes vorhanden sind. Noch schlimmer kann es werden, wenn es funktionsbasierte Indizes gibt. Damit müssen DBAs leben! Indizes erhöhen die Schreibleistung. Es gibt jedoch Möglichkeiten, dieses Problem anzugehen, das von der Festplattenarchitektur abhängt. Wenn der Datenbankserver mehrere Plattendateisysteme verwendet, können die Indizes und Tabellen auf mehreren Tablespaces platziert werden, die sich über mehrere Plattendateisysteme befinden. Auf diese Weise kann eine bessere I/O-Leistung erreicht werden.

Index-Management TIPPS

1. Machen Sie sich mit der Notwendigkeit von Indizes vertraut. Intelligente Indizierung ist der Schlüssel.
2. Vermeiden Sie es, mehrere Indizes zu erstellen, und auf keinen Fall unnötige Indizes, da dies die Schreibleistung wirklich beeinträchtigen kann.
3. Überwachen Sie die Verwendung von Indizes, und löschen Sie alle nicht verwendeten Indizes.
4. Wenn indizierte Spalten Datenänderungen unterliegen, werden auch die Indizes aufgebläht. Organisieren Sie die Indizes also regelmäßig neu.

Aufteilend

Eine effektive Partitionierungsstrategie kann E/A-Leistungsprobleme in hohem Maße reduzieren. Große Tabellen können basierend auf Geschäftslogik partitioniert werden. PostgreSQL unterstützt die Tabellenpartitionierung. Obwohl es derzeit nicht alle Funktionen vollständig unterstützt, kann es nur bei einigen der Echtzeit-Anwendungsfälle helfen. In PostgreSQL sind partitionierte untergeordnete Tabellen vollständig individuell für die Mastertabelle, was einen Engpass darstellt. Beispielsweise können Einschränkungen, die für die Mastertabelle erstellt wurden, nicht automatisch an die untergeordneten Tabellen vererbt werden.

Aus der Perspektive des Ausgleichs von E/A kann die Partitionierung jedoch wirklich hilfreich sein. Alle untergeordneten Partitionen können auf mehrere Tablespaces und Plattendateisysteme aufgeteilt werden. Abfragen mit einem Datumsbereich in der "where"-Klausel, die auf die Tabelle trifft und basierend auf dem Datumsbereich partitioniert ist, können von der Partitionierung profitieren, indem nur eine oder zwei Partitionen anstelle der vollständigen Tabelle gescannt werden.

Kontrollpunkte

Prüfpunkte definieren den konsistenten Zustand der Datenbank. Sie sind von entscheidender Bedeutung, und es ist wichtig, dass Prüfpunkte regelmäßig genug auftreten, um sicherzustellen, dass Datenänderungen dauerhaft auf dem Datenträger gespeichert werden und die Datenbank jederzeit in einem konsistenten Zustand ist. Eine unsachgemäße Konfiguration von Prüfpunkten kann jedoch zu Problemen mit der E/A-Leistung führen. Datenbankadministratoren müssen bei der Konfiguration von Prüfpunkten akribisch vorgehen, um sicherzustellen, dass es keine E/A-Spitzen gibt, und dies hängt auch davon ab, wie gut die Festplatten sind und wie gut das Layout der Datendateien gestaltet ist.

Was macht der Checkpoint?

Einfach ausgedrückt stellen Kontrollpunkte Folgendes sicher:

1. Alle festgeschriebenen Daten werden in die Datendateien auf dem Datenträger geschrieben.
2. Clog-Dateien werden mit dem Commit-Status aktualisiert.
3. Transaktionsprotokolldateien in pg_xlog Verzeichnis (jetzt pg_wal) werden wiederverwendet.

Das erklärt, wie E/A-intensiv Checkpoints sind. Es gibt Parameter in postgresql.conf, die konfiguriert / abgestimmt werden können, um das Verhalten von Prüfpunkten zu steuern, und diese Parameter sind max_wal_size, min_wal_size, checkpoint_timeout und checkpoint_completion_target. Diese Parameter bestimmen, wie häufig die Checkpoints auftreten sollen und innerhalb welcher Zeit die Checkpoints abgeschlossen sein müssen.

Wie kann man verstehen, welche Konfiguration für Checkpoints besser ist? Wie kann man sie tunen?

Hier sind einige Tipps:

1. Werten Sie die Datenbank-TPS aus. Werten Sie das Gesamtvolumen der Transaktionen aus, die an einem Geschäftstag in der Datenbank stattfinden, und ermitteln Sie auch, zu welchem Zeitpunkt die höchste Anzahl von Transaktionen in der Datenbank eintrifft.
2. Diskutieren Sie regelmäßig mit Anwendungsentwicklern und anderen technischen Teams, um die Statistiken zur Datenbanktransaktionsrate sowie das zukünftige Transaktionswachstum zu verstehen.
3. Dies kann auch von der Datenbankseite aus erfolgen:
   1. Überwachen Sie die Datenbank und werten Sie die Anzahl der Transaktionen aus, die im Laufe des Tages stattfinden. Dies kann durch Abfragen von pgcatalog-Tabellen wie pg_stat_user_tables erfolgen.
   2. Werten Sie die Anzahl der pro Tag generierten WAL-Archivdateien aus
   3. Überwachen Sie, um zu verstehen, wie die Prüfpunkte funktionieren, indem Sie log_checkpoints Parameter aktivieren
   • 2018-06-06 15:03:16.446 IST [2111] LOG: checkpoint starting: xlog

2018-06-06 15:03:22.734 IST [2111] LOG: checkpoint complete: wrote 12112 buffers (73.9%); 0 WAL file(s) added, 0 removed, 25 recycled; write=6.058 s, sync=0.218 s, total=6.287 s; sync files=4, longest=0.178 s, average=0.054 s; distance=409706 kB, estimate=412479 kB

• 1. Ermitteln Sie, ob die aktuelle Prüfpunktkonfiguration für die Datenbank gut genug ist. Konfigurieren Sie checkpoint_warning Parameter (standardmäßig auf 30 Sekunden konfiguriert), um die folgenden Warnungen in den Postgres-Protokolldateien anzuzeigen.
  • 2018-06-06 15:02:42.295 IST [2111] LOG: checkpoints are occurring too frequently (11 seconds apart)

2018-06-06 15:02:42.295 IST [2111] HINT: Consider increasing the configuration parameter "max_wal_size".

Was bedeutet die obige Warnung?

Checkpoints treten in der Regel immer dann auf, wenn Protokolldateien im Wert von max_wal_size (standardmäßig 1 GB, d. h. 64 WAL-Dateien) aufgefüllt werden oder wenn checkpoint_timeout (alle 5 Minuten pro Standard) erreicht wird. Die obige Warnung bedeutet, dass konfigurierte max_wal_size nicht ausreichend ist und die Prüfpunkte alle 11 Sekunden auftreten, was wiederum bedeutet, dass 64 WAL-Dateien in PG_WAL Verzeichnis in nur 11 Sekunden gefüllt werden, was zu häufig vorkommt. Mit anderen Worten, wenn es weniger häufige Transaktionen gibt, treten die Kontrollpunkte alle 5 Minuten auf. Erhöhen Sie also, wie der Hinweis vorschlägt, den Parameter max_wal_size auf einen höheren Wert, max_min_size Parameter kann auf den gleichen oder einen niedrigeren Wert als den vorherigen erhöht werden.

Ein weiterer wichtiger Parameter, der aus Sicht der E/A-Leistung zu berücksichtigen ist, ist checkpoint_completion_target, der standardmäßig auf 0,5 konfiguriert ist.

checkpoint_completion_target = 0,5 x checkpoint_timeout = 2,5 Minuten

Das bedeutet, dass Checkpoints 2,5 Minuten Zeit haben, um die schmutzigen Blöcke mit der Festplatte zu synchronisieren. Reichen 2,5 Minuten? Das muss evaluiert werden. Wenn die Anzahl der zu schreibenden Dirty-Blöcke sehr hoch ist, können 2,5 Minuten sehr, sehr aggressiv erscheinen, und dann kann eine E/A-Spitze beobachtet werden. Die Konfiguration des Parameters completion_target muss auf der Grundlage von max_wal_size und checkpoint_timeout Werten erfolgen. Wenn diese Parameter auf einen höheren Wert angehoben werden, sollten Sie checkpoint_completion_target entsprechend erhöhen.

VAKUUM, ANALYSIEREN (mit FILLFACTOR)

VACUUM ist eine der leistungsstärksten Funktionen von PostgreSQL. Es kann verwendet werden, um Aufblähungen (fragmentierten Speicherplatz) in Tabellen und Indizes zu entfernen, und wird von Transaktionen generiert. Die Datenbank muss regelmäßig einem Vakuum unterzogen werden, um eine gesunde Wartung und eine bessere Leistung zu gewährleisten. Auch hier kann es zu ernsthaften Leistungsproblemen führen, wenn die Datenbank nicht regelmäßig aktualisiert wird. ANALYZE muss zusammen mit VACUUM (VACUUM ANALYZE) ausgeführt werden, um aktuelle Statistiken für den Abfrageplaner zu gewährleisten.

VACUUM ANALYZE kann auf zwei Arten durchgeführt werden: manuell, automatisch oder beides. In einer Echtzeit-Produktionsumgebung ist es in der Regel beides. Automatic VACUUM wird durch den Parameter "Autovacuum" aktiviert, der standardmäßig auf "on" eingestellt ist. Wenn die automatische Bereinigung aktiviert ist, beginnt PostgreSQL automatisch mit dem automatischen Vakuumieren der Tabellen in regelmäßigen Abständen. Die Kandidatentabellen, die abgesaugt werden müssen, werden von Autovakuumprozessen auf der Grundlage verschiedener Schwellenwerte erfasst, die durch verschiedene Autovakuum*-Parameter festgelegt werden, diese Parameter können optimiert / abgestimmt werden, um sicherzustellen, dass die Aufblähungen der Tabellen regelmäßig gelöscht werden. Schauen wir uns einige Parameter und ihre Verwendung an –

Autovacuum parameters

autovacuum=on	Dieser Parameter wird verwendet, um die Selbstbereinigung zu aktivieren / deaktivieren. Die Standardeinstellung ist "on".
log_autovacuum_min_duration = -1	Protokolliert die Dauer des Selbstbereinigungsprozesses. Dies ist wichtig, um zu verstehen, wie lange der Autovakuumprozess gelaufen ist.
autovacuum_max_workers = 3	Anzahl der erforderlichen Selbstvakuumprozesse. Dies hängt davon ab, wie aggressiv Datenbanktransaktionen sind und wie viele CPUs Sie für Selbstbereinigungsprozesse anbieten können.
autovacuum_naptime = 1 min	Autovakuum-Ruhezeit zwischen den Autovakuum-Läufen.
Parameter, die den Schwellenwert für den Start des Autovakuumprozesses definieren Selbstbereinigungsaufträge werden gestartet, wenn ein bestimmter Schwellenwert erreicht ist. Nachfolgend sind die Parameter aufgeführt, mit denen ein bestimmter Schwellenwert festgelegt werden kann, auf dessen Grundlage der Selbstvakuumprozess gestartet wird. autovacuum_vacuum_threshold = 50		Die Tabelle wird bereinigt, wenn mindestens 50 Zeilen in einer Tabelle aktualisiert / gelöscht werden.
autovacuum_analyze_threshold = 50		Die Tabelle wird analysiert, wenn mindestens 50 Zeilen in einer Tabelle aktualisiert / gelöscht werden.
autovacuum_vacuum_scale_factor = 0.2		Die Tabelle wird bereinigt, wenn mindestens 20% der Zeilen in einer Tabelle aktualisiert / gelöscht werden.
autovacuum_analyze_scale_factor = 0.1		Die Tabelle wird bereinigt, wenn mindestens 10% der Zeilen in einer Tabelle aktualisiert / gelöscht werden.

Parameter, die über dem Schwellenwert liegen, können basierend auf dem Datenbankverhalten geändert werden. Datenbankadministratoren müssen die heißen Tabellen analysieren und identifizieren und sicherstellen, dass diese Tabellen so häufig wie möglich bereinigt werden, um eine gute Leistung zu gewährleisten. Das Ermitteln eines bestimmten Werts für diese Parameter kann in einer Umgebung mit vielen Transaktionen, in der sich die Daten jede Sekunde ändern, eine Herausforderung darstellen. Oft ist mir aufgefallen, dass Autovakuumprozesse ziemlich lange dauern und am Ende zu viele Ressourcen in Produktionssystemen verbrauchen.

Ich würde vorschlagen, sich nicht vollständig auf den Autovakuumprozess zu verlassen, der beste Weg ist, einen nächtlichen VAKUUMANALYZE-Job zu planen, damit die Belastung des Autovakuums reduziert wird. Erwägen Sie zunächst, große Tabellen mit einer hohen Transaktionsrate manuell zu vakuumieren.

VAKUUM VOLL

VACUUM FULL hilft dabei, den aufgeblähten Speicherplatz in den Tabellen und Indizes zurückzugewinnen. Dieses Dienstprogramm kann nicht verwendet werden, wenn die Datenbank online ist, da es die Tabelle sperrt. Tabellen dürfen nur dann mit VACUUM FULL beaufschlagt werden, wenn die Anwendungen heruntergefahren werden. Die Indizes werden auch zusammen mit den Tabellen während VACUUM FULL neu organisiert.

Werfen wir einen Blick auf die Auswirkungen von VACUUM ANALYZE

Bloats: Wie erkennt man Bloats? Wann entstehen Blähungen?

Hier sind einige Tests:

Ich habe eine Tabelle der Größe 1 GB mit 10 Millionen Zeilen.

postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;

?column?

—————-

1

postgres=# select count(*) From pgbench_accounts ;

count

—————–

10000000

Schauen wir uns die Auswirkungen von Aufblähungen auf eine einfache Abfrage an: select * from pgbench_accounts;

Im Folgenden finden Sie den EXPLAIN-Plan für die Abfrage:

postgres=# explain analyze select * from pgbench_accounts;

QUERY PLAN

————————————————————————————————————————————————

Seq Scan on pgbench_accounts (cost=0.00..263935.00 rows=10000000 width=97)

(actual time=0.033..1054.257 rows=10000000 loops=1)

Planning time: 0.255 ms

Execution time: 1494.448 ms

Lassen Sie uns nun alle Zeilen in der Tabelle aktualisieren und die Auswirkungen der obigen SELECT-Abfrage sehen.

postgres=# update pgbench_accounts set abalance=1;

UPDATE 10000000

postgres=# select count(*) From pgbench_accounts ;

count

—————–

10000000

Nachfolgend finden Sie den EXPLAIN-PLAN der Abfrage nach der Ausführung von UPDATE.

postgres=# explain analyze select * from pgbench_accounts;

QUERY PLAN

———————————————————————————————————————————————————-

Seq Scan on pgbench_accounts (cost=0.00..527868.39 rows=19999939 width=97)

(actual time=404.474..1520.175 rows=10000000 loops=1)

Planning time: 0.051 ms

Execution time: 1958.532 ms

Die Größe der Tabelle wurde nach dem UPDATE auf 2 GB erhöht.

postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;

?column?

—————–

2

Wenn man die Kostenzahlen des früheren EXPLAIN PLAN beobachten und vergleichen kann, gibt es einen großen Unterschied. Die Kosten sind stark gestiegen. Noch wichtiger ist, dass bei genauer Beobachtung die Anzahl der Zeilen (etwas mehr als 19 Millionen), die nach dem UPDATE gescannt werden, höher ist, was fast doppelt so hoch ist wie die tatsächlich vorhandenen Zeilen (10 Millionen). Das bedeutet, dass die Anzahl der aufgeblähten Zeilen 9+ Millionen beträgt und die tatsächliche Zeit ebenfalls erhöht und die Ausführungszeit von 1,4 Sekunden auf 1,9 Sekunden erhöht wurde.

Das ist also die Auswirkung, wenn die TABELLE nach dem UPDATE nicht VAKUUMIERT wird. Die obigen EXPLAIN PLAN-Zahlen bedeuten genau, dass die Tabelle aufgebläht ist.

Wie kann man feststellen, ob die Tabelle aufgebläht ist? Verwenden Sie das pgstattuple contrib-Modul:

postgres=# select * from pgstattuple('pgbench_accounts');

table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent

————+————-+————+—————+——————+—————-+——————–+————+————–

2685902848 | 10000000 | 1210000000 | 45.05 | 9879891 | 1195466811 | 44.51 | 52096468 | 1.94

Die obige Zahl zeigt an, dass die Hälfte der Tabelle aufgebläht ist.

Lassen Sie uns die Tabelle vakuumanalysieren und die Auswirkungen jetzt sehen:

postgres=# VACUUM ANALYZE pgbench_accounts ;

VACUUM

postgres=# explain analyze select * from pgbench_accounts;

QUERY PLAN

———————————————————————————————————————————————–

Seq Scan on pgbench_accounts (cost=0.00..428189.05 rows=10032005 width=97)

(actual time=400.023..1472.118 rows=10000000 loops=1)

Planning time: 4.374 ms

Execution time: 1913.541 ms

Nach der VAKUUMANALYSE sind die Kostenzahlen gesunken. Jetzt zeigt die Anzahl der gescannten Zeilen fast 10 Millionen an, auch die tatsächliche Zeit und die Ausführungszeit haben sich nicht wesentlich geändert. Das liegt daran, dass die Aufblähungen in der Tabelle zwar verschwunden sind, die Größe der zu scannenden Tabelle jedoch gleich bleibt. Unten sehen Sie den pgstattuple-Ausgabeposten VACUUM ANALYZE.

postgres=# select * from pgstattuple('pgbench_accounts');

table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent

————+————-+————+—————+——————+—————-+——————–+————+————–

2685902848 | 10000000 | 1210000000 | 45.05 | 0 | 0 | 0 | 1316722516 | 49.02

Die obige Zahl zeigt an, dass alle Aufblähungen (tote Tupel) verschwunden sind.

Schauen wir uns die Auswirkungen von VACUUM FULL ANALYZE an und sehen, was passiert:

postgres=# vacuum full analyze pgbench_accounts ;

VACUUM

postgres=# explain analyze select * from pgbench_accounts;

QUERY PLAN

—————————————————————————

Seq Scan on pgbench_accounts (cost=0.00..263935.35 rows=10000035 width=97)

(actual time=0.015..1089.726 rows=10000000 loops=1)

Planning time: 0.148 ms

Execution time: 1532.596 ms

Wenn Sie beobachten, ähneln die Ist-Zeit und die Ausführungszeit den Zahlen vor UPDATE. Außerdem wurde die Größe der Tabelle von 2 GB auf 1 GB verringert.

postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;

?column?

—————–

1

Das ist die Wirkung von VACUUM FULL.

FILLFACTOR-FAKTOR

FILLFACTOR ist ein sehr wichtiges Attribut, das einen echten Unterschied in der Datenbankwartungsstrategie auf Tabellen- und Indexebene ausmachen kann. Dieser Wert gibt an, wie viel Speicherplatz von den INSERTs innerhalb eines Datenblocks verwendet werden soll. Der FILLFACTOR-Wert ist standardmäßig auf 100 % festgelegt, was bedeutet, dass INSERTs den gesamten in einem Datenblock verfügbaren Speicherplatz nutzen können. Dies bedeutet auch, dass kein Speicherplatz für UPDATEs verfügbar ist. Dieser Wert kann bei stark aktualisierten Tabellen auf einen bestimmten Wert verringert werden.

Dieser Parameter kann für jede Tabelle und einen Index konfiguriert werden. Wenn FILLFACTOR auf den optimalen Wert konfiguriert ist, können Sie auch einen echten Unterschied in der VACUUM-Leistung und der Abfrageleistung feststellen. Kurz gesagt, optimale FILLFACTOR-Werte stellen sicher, dass keine unnötige Anzahl von Blöcken zugewiesen wird.

Schauen wir uns das gleiche Beispiel oben an –

Die Tabelle hat eine Million Zeilen

postgres=# select count(*) From pgbench_accounts ;

count

—————–

10000000

Vor dem Update beträgt die Größe der Tabelle 1 GB

postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;

?column?

——–

1

postgres=# update pgbench_accounts set abalance=1;

UPDATE 10000000

Nach dem Update wurde die Größe der Tabelle nach dem UPDATE auf 2 GB erhöht

postgres=# select pg_relation_size('pgbench_accounts')/1024/1024/1024;

?column?

———

2

Das bedeutet, dass die Anzahl der Blöcke, die der Tabelle zugeordnet sind, um 100 % erhöht wurde. Wenn der FILLFACTOR konfiguriert wurde, hat sich die Größe der Tabelle möglicherweise nicht um diesen Rand erhöht.

Woher weiß man, welcher Wert für FILLFACTOR konfiguriert werden soll?

Es hängt alles davon ab, welche Spalten aktualisiert werden und wie groß die aktualisierten Spalten sind. Im Allgemeinen wäre es gut, den FILLFACTOR-Wert auszuwerten, indem Sie ihn in UAT-Datenbanken testen. Wenn die zu aktualisierenden Spalten beispielsweise 10 % der gesamten Tabelle ausmachen, sollten Sie den Füllfaktor auf 90 % oder 80 % konfigurieren.

Wichtiger Hinweis:Wenn Sie den FILLFACTOR-Wert für die
vorhandene Tabelle mit den Daten ändern, müssen Sie einen VACUUM FULL oder eine Reorganisation der Tabelle durchführen, um sicherzustellen, dass der FILLFACTOR-Wert für die vorhandenen Daten wirksam ist.

TIPPS ZUM STAUBSAUGEN

1. Wie oben erwähnt, sollten Sie in Erwägung ziehen, den VACUUM ANALYZE-Auftrag jede Nacht manuell auf den stark genutzten Tabellen auszuführen, auch wenn die Selbstbereinigung aktiviert ist.
2. Erwägen Sie, VACUUM ANALYZE für Tabellen nach der Masseneinfügung auszuführen. Dies ist wichtig, da viele glauben, dass das Vakuumieren nach INSERTs möglicherweise nicht erforderlich ist.
3. Überwachen Sie, um sicherzustellen, dass hochaktive Tabellen regelmäßig mit einem Vakuum versehen werden, indem Sie die Tabelle pg_stat_user_tables abfragen.
4. Verwenden Sie pg_stattuple contrib-Modul, um die Größe des aufgeblähten Speicherplatzes innerhalb der Tabellensegmente zu ermitteln.
5. Das Dienstprogramm VACUUM FULL kann nicht auf Produktionsdatenbanksystemen verwendet werden. Erwägen Sie die Verwendung von Tools wie pg_reorg oder pg_repack, mit denen Sie Tabellen und Indizes online ohne Sperren neu organisieren können.
6. Stellen Sie sicher, dass der AUTOVACUUM-Prozess während der Geschäftszeiten (mit hohem Datenverkehr) länger ausgeführt wird.
7. Aktivieren Sie log_autovacuum_min_duration Parameter, um die Zeiten und die Dauer von AUTOVACUUM-Prozessen zu protokollieren.
8. Stellen Sie vor allem sicher, dass FILLFACTOR für Tabellen und Indizes mit hoher Transaktionsrate auf einen optimalen Wert konfiguriert ist.

Andere E/A-Probleme

Datenträger-Sortierung

Abfragen, die eine Sortierung durchführen, sind ein weiteres häufiges Ereignis in Echtzeit-Produktionsdatenbanken, und die meisten davon können nicht vermieden werden. Abfragen mit Klauseln wie GROUP BY, ORDER BY, DISTINCT, CREATE INDEX, VACUUM FULL usw. Führen Sie eine Sortierung durch und die Sortierung kann auf der Festplatte erfolgen. Die Sortierung erfolgt im Speicher, wenn die Selektion und Sortierung anhand indizierter Spalten erfolgt. Hier spielen Composite-Indizes eine Schlüsselrolle. Indizes werden aggressiv im Arbeitsspeicher zwischengespeichert. Andernfalls, wenn die Daten auf dem Datenträger sortiert werden müssen, würde sich die Leistung drastisch verlangsamen.

Um sicherzustellen, dass die Sortierung im Speicher erfolgt, kann der Parameter work_mem verwendet werden. Dieser Parameter kann auf einen Wert konfiguriert werden, so dass die gesamte Sortierung im Speicher erfolgen kann. Der Hauptvorteil dieses Parameters besteht darin, dass er nicht nur in postgresql.conf konfiguriert werden kann, sondern auch auf Sitzungs-, Benutzer- oder Datenbankebene. Wie hoch sollte der work_mem Wert sein? Woher weiß man, welche Abfragen eine Datenträgersortierung durchführen? Wie überwacht man Abfragen, die eine Datenträgersortierung in einer Echtzeit-Produktionsdatenbank durchführen?

Die Antwort lautet: Konfigurieren Sie log_temp_files Parameter auf einen bestimmten Wert. Der Wert wird in Bytes angegeben, ein Wert von 0 protokolliert alle temporären Dateien (zusammen mit ihren Größen), die aufgrund der Datenträgersortierung auf dem Datenträger generiert wurden. Sobald der Parameter konfiguriert ist, können Sie die folgenden Meldungen in den Protokolldateien sehen

2018-06-07 22:48:02.358 IST [4219] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp4219.0", size 200425472

2018-06-07 22:48:02.358 IST [4219] STATEMENT: create index bid_idx on pgbench_accounts(bid);

2018-06-07 22:48:02.366 IST [4219] LOG: duration: 6421.705 ms statement: create index bid_idx on pgbench_accounts(bid);

Die obige Meldung bedeutet, dass die CREATE INDEX-Abfrage eine Festplattensortierung durchgeführt und eine Datei mit einer Größe von 200425472 Bytes generiert hat, die 191+ MB groß ist. Das bedeutet genau, dass der Parameter work_mem auf 191+ MB oder höher konfiguriert werden muss, damit diese spezielle Abfrage eine Speichersortierung durchführt.

Nun, für die Anwendungsabfragen kann work_mem Parameter nur auf Benutzerebene konfiguriert werden. Bevor Sie dies tun, achten Sie auf die Anzahl der Verbindungen, die der Benutzer mit der Datenbank herstellt, und auf die Anzahl der Sortierabfragen, die von diesem Benutzer ausgeführt werden. Weil PostgreSQL versucht, jedem Prozess in jeder Verbindung work_mem zuzuweisen (indem es eine Sortierung durchführt), was möglicherweise den Speicher auf dem Datenbankserver verhungern lassen könnte.

Layout des Datenbankdateisystems

Das Entwerfen eines effizienten und leistungsfördernden Datenbankdateisystemlayouts ist aus Leistungs- und Skalierbarkeitssicht wichtig. Wichtig ist, dass dies nicht von der Datenbankgröße abhängt. Im Allgemeinen wird angenommen, dass große Datenbanken eine Hochleistungs-Festplattenarchitektur benötigen, was NICHT der Fall ist. Selbst wenn die Datenbankgröße 50 GB beträgt, benötigen Sie möglicherweise eine gute Festplattenarchitektur. Und das ist möglicherweise nicht ohne zusätzliche Kosten möglich.

Hier sind einige TIPPS dafür:

1. Stellen Sie sicher, dass die Datenbank über mehrere Tablespaces verfügt, wobei Tabellen und Indizes basierend auf den Transaktionsraten gruppiert sind.
2. Der Tablespace muss für eine ausgeglichene E/A auf mehreren Plattendateisystemen platziert werden. Dadurch wird auch sichergestellt, dass mehrere CPUs ins Spiel kommen, um Transaktionen über mehrere Festplatten hinweg durchzuführen.
3. Erwägen Sie, pg_xlog oder pg_wal Verzeichnis auf einem separaten Datenträger in einer Datenbank mit hohem Transaktionsaufkommen zu platzieren.
4. Stellen Sie sicher, dass *_cost Parameter basierend auf der Infrastruktur konfiguriert werden
5. Verwenden Sie iostat, mpstat und andere E/A-Überwachungstools, um die E/A-Statistiken über alle Festplatten hinweg zu verstehen und die Datenbankobjekte entsprechend zu entwerfen / zu verwalten.

PostgreSQL in der Cloud

Die Infrastruktur ist entscheidend für eine gute Datenbankleistung. Performance-Engineering-Strategien unterscheiden sich je nach Infrastruktur und Umgebung. Besondere Vorsicht ist bei PostgreSQL-Datenbanken geboten, die in der Cloud gehostet werden. Das Leistungsbenchmarking für Datenbanken, die auf physischen Barebone-Servern in einem lokalen Rechenzentrum gehostet werden, kann sich grundlegend von Datenbanken unterscheiden, die in der Public Cloud gehostet werden.

Im Allgemeinen können Cloud-Instanzen etwas langsamer sein, und die Benchmarks unterscheiden sich erheblich, insbesondere in Bezug auf E/A. Führen Sie immer E/A-Latenzprüfungen durch, bevor Sie eine Cloud-Instanz auswählen / erstellen. Zu meiner Überraschung erfuhr ich, dass die Leistung von Cloud-Instanzen auch je nach Region variieren kann, obwohl sie vom selben Cloud-Anbieter stammen. Um dies näher zu erläutern: Eine Cloud-Instanz mit denselben Spezifikationen, die in zwei verschiedenen Regionen erstellt wurde, kann zu unterschiedlichen Leistungsergebnissen führen.

Massenladen von Daten

Offline-Massenladevorgänge sind in der Datenbankwelt weit verbreitet. Sie können eine erhebliche E/A-Last erzeugen, was wiederum die Leistung beim Laden von Daten verlangsamt. Ich habe mich in meiner Erfahrung als DBA mit solchen Herausforderungen konfrontiert gesehen. Oft wird das Laden von Daten schrecklich langsam und muss optimiert werden. Hier sind einige Tipps. Wohlgemerkt, diese gelten nur für Offline-Datenladevorgänge und können nicht für das Laden von Daten in die Live-Produktionsdatenbank berücksichtigt werden.

Da die meisten Datenladevorgänge außerhalb der Geschäftszeiten ausgeführt werden, stellen Sie sicher, dass die folgenden Parameter während des Datenladens konfiguriert sind:

• 1. Konfigurieren Sie Prüfpunkt-bezogene Werte, die groß genug sind, damit Prüfpunkte keine Leistungsprobleme verursachen.
  2. Schalten Sie full_page_write aus
  3. Wal-Archivierung ausschalten
  4. Konfigurieren Sie synchronous_commit Parameter auf "aus"
  5. Löschen von Einschränkungen und Indizes für die Tabellen, die dem Datenladen ausgesetzt sind (Einschränkungen und Indizes können nach dem Laden der Daten mit einem größeren work_mem Wert neu erstellt werden)
  6. Wenn Sie die Daten aus einer CSV-Datei laden, können Sie mit größeren maintenance_work_mem gute Ergebnisse erzielen.
  7. Obwohl es einen erheblichen Leistungsvorteil gibt, schalten Sie den fsync-Parameter NICHT aus, da dies zu Datenbeschädigungen führen kann.

TIPPS für die Cloud-Performance-Analyse

1. Führen Sie gründliche E/A-Latenztests mit pgbench durch. Meiner Erfahrung nach hatte ich ziemlich gewöhnliche Leistungsergebnisse, als ich im Rahmen der TPS-Evaluierung Überprüfungen der Datenträgerlatenz durchführte. Bei einigen Public-Cloud-Instanzen gab es Probleme mit der Cache-Leistung. Dies hilft bei der Auswahl der geeigneten Spezifikationen für die Cloud-Instanz, die für die Datenbanken ausgewählt wurde.
2. Cloud-Instanzen können sich von Region zu Region unterschiedlich verhalten. Eine Cloud-Instanz mit bestimmten Spezifikationen in einer Region kann andere Leistungsergebnisse erzielen als eine Cloud-Instanz mit denselben Spezifikationen in einer anderen Region. Meine pgbench-Tests, die auf mehreren Cloud-Instanzen (alle dieselben Spezifikationen mit demselben Cloud-Anbieter) in verschiedenen Regionen ausgeführt wurden, lieferten mir bei einigen von ihnen unterschiedliche Ergebnisse. Dies ist besonders wichtig, wenn Sie in die Cloud migrieren.
3. Für die Abfrageleistung in der Cloud ist möglicherweise ein anderer Optimierungsansatz erforderlich. DBAs müssen *_cost-Parameter verwenden, um sicherzustellen, dass fehlerfreie Abfrageausführungspläne generiert werden.

Tools zur Überwachung der PostgreSQL-Leistung

Es gibt verschiedene Tools zur Überwachung der PostgreSQL-Leistung. Lassen Sie mich einige davon hervorheben.

pg_top ist ein großartiges Tool, um die PostgreSQL-Datenbank dynamisch zu überwachen. Ich würde dieses Tool für DBAs aus verschiedenen Gründen wärmstens empfehlen. Dieses Tool hat zahlreiche Vorteile, lassen Sie mich sie auflisten:

• 1. pg_top Tool verwendet eine Textschnittstelle und ähnelt dem Unix-Dienstprogramm "top".
  2. Listet die Prozesse und die verwendeten Hardware-Ressourcen übersichtlich auf. Was mich an diesem Tool begeistert, ist, dass es Ihnen klar sagt, ob sich ein bestimmter Prozess gerade auf DISK oder CPU befindet – das ist meiner Meinung nach hervorragend. Datenbankadministratoren können den Prozess, der länger auf dem Datenträger ausgeführt wird, eindeutig auswählen.
  3. Sie können den EXPLAIN PLAN der Top-SQLs dynamisch oder sofort überprüfen
  4. Sie können auch sofort herausfinden, welche Tabellen oder Indizes gescannt werden

1. Nagios ist ein beliebtes Monitoring-Tool für PostgreSQL, das sowohl Open-Source- als auch kommerzielle Versionen hat. Für die Überwachung sollte eine Open-Source-Version ausreichen. Benutzerdefinierte Perl-Skripte können erstellt und in das Nagios-Modul eingebunden werden.
2. Pgbadger ist ein beliebtes Tool, mit dem PostgreSQL-Protokolldateien analysiert und Leistungsberichte erstellt werden können. Dieser Bericht kann verwendet werden, um die Leistung von Prüfpunkten und die Datenträgersortierung zu analysieren.
3. Zabbix ist ein weiteres beliebtes Tool für die PostgreSQL-Überwachung.

ClusterControl ist eine aufstrebende Management-Plattform für PostgreSQL. Neben der Überwachung verfügt es auch über Funktionen zum Bereitstellen von Replikations-Setups mit Load Balancern,