Das Problem der Optimierung stellt sich besonders akut, wenn es zu Verlangsamungen kommt, und es gibt sehr wenig Dokumentation zu diesem Thema, insbesondere in russischer Sprache. Im Folgenden versuchen wir, die wichtigsten Aspekte der PostgreSQL-Optimierung zu betrachten.
Festplatten und Dateisysteme
Wenn Ihr Server über mehrere physische Festplatten verfügt, können Sie die Datenbankdateien und das Transaktionslog auf verschiedene Festplatten verteilen:
– Stoppen Sie den Server.
– Verschieben Sie die Verzeichnisse pg_clog und pg_xlog, die sich im Datenbankverzeichnis befinden, auf eine andere Festplatte.
– Erstellen Sie am alten Standort einen symbolischen Link.
– Starten Sie den Server.
Konfigurationsanpassung (durch Bearbeiten der Datei postgresql.conf)
Ressourcenkonfiguration
shared_buffers
Die Größe des zwischen Prozessen geteilten PostgreSQL-Speichers, der für die Ausführung aktiver Operationen erforderlich ist. Geben Sie nicht zu viel an, da PostgreSQL auch den Festplattencache verwendet.
Werte:
Mittlere Datenmenge und 256–512 MB verfügbarer Speicher: 16–32 MB
Große Datenmenge und 1–4 GB verfügbarer Speicher: 64–256 MB
temp_buffers
Puffer für temporäre Objekte, hauptsächlich für temporäre Tabellen.
Kann auf etwa 16 MB eingestellt werden.
max_prepared_transactions
Anzahl der gleichzeitig vorbereiteten Transaktionen.
Für die Arbeit mit 1C hat dieser Parameter keine Bedeutung, da PREPARE TRANSACTION dort nicht verwendet wird.
Kann auf dem Standardwert – 5 – belassen werden.
work_mem
Spezialspeicher, der für Sortierungen und das Caching von Tabellen pro Abfrage verwendet wird.
Bei der Festlegung dieses Parameters ist die Anzahl der gleichzeitig ausgeführten konkurrierenden Abfragen zu berücksichtigen.
Bei einem Speicher von 1–4 GB wird eine Einstellung von 32–128 MB empfohlen.
maintenance_work_mem
Speicher, der für VACUUM-, CREATE INDEX-, ALTER TABLE- und FOREIGN KEY-Operationen verwendet wird.
Sollte einen höheren Wert als work_mem haben. Zu große Werte führen zur Verwendung von Swap-Speicher.
Bei einem Speicher von 1–4 GB wird eine Einstellung von 128–512 MB empfohlen.
max_stack_depth
Ein spezieller Stack für den Server, der idealerweise mit der im Betriebssystemkern eingestellten Stack-Größe übereinstimmen sollte. Eine Einstellung eines größeren Werts als im Kernel kann zu Fehlern führen.
Es wird eine Einstellung von 2–4 MB empfohlen.
max_fsm_relations
Maximale Anzahl von Tabellen, für die der freie Platz in der gemeinsamen Free-Space-Map überwacht wird. Diese Daten werden von VACUUM gesammelt.
Legen Sie den Parameter entsprechend der Anzahl der Tabellen in Ihrer Datenbank mit einem Puffer fest. (Gilt für Versionen vor 8.4)
max_fsm_pages
Anzahl der Blöcke, für die Informationen über den freien Platz gespeichert werden. Die Informationen werden im geteilten Speicher gespeichert, wobei pro Eintrag 6 Bytes benötigt werden.
Die Verwendung dieses Parameters ermöglicht es, VACUUM FULL für die Datenbank zu vermeiden; es reicht VACUUM aus. (Gilt für Versionen vor 8.4)
Dieser Parameter muss mindestens 16 * max_fsm_relations betragen.
Dieser Parameter wird automatisch bei der Datenbankerstellung mit dem Dienstprogramm initdb festgelegt.
Er kann auch manuell festgelegt werden: Als erste Näherung kann die Hälfte der durchschnittlichen Anzahl von Einträgen genommen werden, die zwischen den Aufrufen des VACUUM-Befehls geändert (UPDATE oder DELETE) werden.
Diesen Wert können Sie abschätzen (die Datenbank sollte bereits eine Weile laufen), indem Sie Folgendes ausführen:
vacuum full analyze;
NOTICE: number of page slots needed (196272) exceeds max_fsm_pages (153600)
HINWEIS: Erwägen Sie, den Konfigurationsparameter „max_fsm_pages“ auf einen Wert über 196272 zu erhöhen.
max_files_per_process
Maximale Anzahl von Dateien, die von einem Prozess und seinen Unterprozessen gleichzeitig geöffnet werden können.
Verringern Sie diesen Parameter, wenn während des Betriebs die Meldung „Too many open files“ erscheint.
Schreiben von Transaktionslogs auf die Festplatte
commit_delay und commit_siblings
Der Wert von commit_delay wird in Mikrosekunden angegeben (Standardwert: 0). Der Wert von commit_siblings wird in Stück angegeben (Standardwert: 5).
commit_delay bestimmt die Verzögerung zwischen dem Schreiben eines Eintrags in den Transaktions-Log-Puffer und dem tatsächlichen Schreiben auf die Festplatte. Wenn bei erfolgreichem Abschluss einer Transaktion mindestens commit_siblings andere Transaktionen aktiv sind, wird der Eintrag für die Dauer von commit_delay verzögert. Wenn in dieser Zeit eine andere Transaktion abgeschlossen wird, werden ihre Änderungen gemeinsam mit einem einzigen Systemaufruf auf die Festplatte geschrieben. Diese Parameter können die Leistung verbessern, wenn viele kleine Transaktionen parallel ausgeführt werden.
fsync
Dieser Parameter steuert das Schreiben von Daten aus dem Cache auf die Festplatte beim Abschluss von Transaktionen.
Wenn Sie den Wert
fsync=off
setzen, werden die Daten nicht sofort nach Abschluss der Operationen auf die Festplatte geschrieben. Dies kann die Geschwindigkeit von INSERT- und UPDATE-Operationen erheblich erhöhen, birgt jedoch das Risiko einer Beschädigung der Datenbank bei einem Ausfall (unerwarteter Stromausfall, Betriebssystemfehler, Fehler der Speicherunterstruktur).
Verwenden Sie diese Option nur, wenn Sie über eine zuverlässige USV (Unterbrechungsfreie Stromversorgung) und Software verfügen, die das System bei niedrigem Batteriestand ordnungsgemäß herunterfährt.
Es wird nicht empfohlen, fsync unter Windows zu deaktivieren, aufgrund der Instabilität des Systems.
wal_sync_method
Methode, die zum Erzwingen des Schreibens von Daten auf die Festplatte verwendet wird.
Wenn fsync=off ist, wird dieser Parameter nicht verwendet.
Mögliche Werte:
open_datasync – Datenschreiben mit der Methode open() und dem Parameter O_DSYNC
fdatasync – Aufruf der Methode fdatasync() nach jedem Commit
fsync_writethrough – Aufruf von fsync() nach jedem Commit unter Ignorierung paralleler Prozesse
fsync – Aufruf von fsync() nach jedem Commit
open_sync – Datenschreiben mit der Methode open() und dem Parameter O_SYNC
Nicht alle Methoden sind auf bestimmten Plattformen verfügbar. Standardmäßig wird die erste verfügbare Methode im System verwendet.
full_page_writes
Setzen Sie diesen Parameter auf off, wenn fsync=off ist. Andernfalls
Wenn dieser Parameter auf on steht, schreibt PostgreSQL den Inhalt jeder Seite in den Transaktions-Log während der ersten Modifikation der Tabelle nach einem Checkpoint. Dies ist notwendig, da Seiten möglicherweise nur teilweise geschrieben werden, wenn das Betriebssystem während des Prozesses abstürzt. Dies führt dazu, dass neue und alte Daten auf der Festplatte gemischt werden. Die Protokollebene des Transaktions-Logs reicht möglicherweise nicht aus, um die Daten nach einem Absturz vollständig wiederherzustellen. full_page_writes gewährleistet eine korrekte Wiederherstellung, jedoch auf Kosten einer erhöhten Schreibmenge im Transaktions-Log. (Da der Transaktions-Log immer mit einem Checkpoint beginnt. Die einzige Möglichkeit, die Schreibmenge zu reduzieren, besteht darin, das checkpoint-Intervall zu erhöhen).
wal_buffers
Speichergröße, die im SHARED MEMORY für die Verwaltung von Transaktions-Logs verwendet wird.
Bei verfügbarem Speicher von 1–4 GB wird empfohlen, 256–1024 KB einzustellen.
Abfrageoptimierung
enable_nestloop
Bestimmt die Verwendung des Abfrageplaners. In einigen Fällen kann das Deaktivieren des Planers die Ausführungszeit einzelner Abfragen, teils erheblich, verkürzen. In den meisten Fällen ermöglicht der Planer jedoch eine Reduzierung der Abfrageausführungszeit. Es sollte nicht dauerhaft deaktiviert werden.
random_page_cost
Legt die Einschätzung des Planers für die „Kosten“ nicht-sequenzieller Datenzugriffe fest. Standardwert ist 4,0. Eine Verringerung dieses Werts im Verhältnis zu seq_page_cost führt dazu, dass der Planer Indexscans bevorzugt; eine Erhöhung macht Indexscans „teurer“. Sie können beide Werte ändern, um das Verhältnis der Kosten für E/A-Datenträgeroperationen im Verhältnis zu den Kosten der CPU-Nutzung anzupassen, das durch die folgenden Parameter beschrieben wird.
Auf Servern mit schnellen Festplattenarrays ist es sinnvoll, den Standardwert auf 3,0, 2,5 oder sogar 2,0 zu senken. Wenn der aktive Teil Ihrer Datenbank viel größer ist als der Arbeitsspeicher, versuchen Sie, den Parameterwert zu erhöhen. Sie können die Wahl des optimalen Werts auch aus der Perspektive der Abfrageleistung angehen. Wenn der Abfrageplaner häufiger als nötig sequenzielle Scans (sequential scans) gegenüber Indexscans (index scans) bevorzugt, senken Sie den Wert. Und umgekehrt, wenn der Planer einen langsamen Index auswählt, wenn er dies nicht tun sollte, ist es sinnvoll, den Wert zu erhöhen. Testen Sie die Ergebnisse nach der Änderung sorgfältig mit einem möglichst breiten Satz von Abfragen. Senken Sie den Wert von random_page_cost niemals unter 2,0; wenn Sie der Meinung sind, dass random_page_cost weiter gesenkt werden sollte, ist es in diesem Fall ratsamer, die Einstellungen der Planerstatistik zu ändern.
cpu_tuple_cost
Legt die Einschätzung des Planers für die Kosten der Verarbeitung jeder Zeile während der Abfrageausführung fest. Standardwert ist 0,01.
cpu_index_tuple_cost
Legt die Einschätzung des Planers für die Kosten der Verarbeitung jedes Index während einer Indexscan-Operation fest. Standardwert ist 0,005.
cpu_operator_cost
Legt die Einschätzung des Planers für die Kosten der Ausführung jedes Operators oder jeder Funktion während der Abfrageausführung fest. Standardwert ist 0,0025.
effective_cache_size
Gibt dem Abfrageplaner Informationen über die Speichermenge, die das Betriebssystem für das Caching von Dateien verwendet, pro Abfrage.
Diesen Parameter im Betriebssystem können Sie in den Einstellungen einsehen:
Für Windows: Im Task-Manager, Registerkarte Leistung, Physischer Speicher – Systemcache.
Für Linux: Geben Sie den Befehl free ein, der erforderliche Wert befindet sich in der Spalte cached (in kB).
Dieser Wert muss durch die Anzahl der gleichzeitig konkurrierenden Abfragen geteilt werden (durchschnittliche Anzahl der Datenbankverbindungen plus Puffer).
default_statistics_target
Legt die Tiefe der Statistiken für Tabellen fest. Größere Werte können die Ausführungszeit des ANALYZE-Befehls erhöhen, verbessern aber die Erstellung des Abfrageplans.
Es wird empfohlen, einen Wert von etwa 100 einzustellen.
constraint_exclusion
Aktiviert oder deaktiviert die Verwendung von CONSTRAINT-Einschränkungen in Tabellen durch den Planer bei der Erstellung von Abfragen.
Es wird empfohlen, den Wert auf on zu setzen. Wenn Sie CONSTRAINT-Einschränkungen für Tabellen ändern, müssen Sie deren Statistiken aktualisieren, indem Sie ANALYZE ausführen, da andernfalls falsche Abfragepläne erstellt werden.
Statistikerfassung
stats_command_string
Ob der Statistiksammler Informationen über den aktuell ausgeführten Befehl und den Startzeitpunkt seiner Ausführung erhalten soll.
Auf on setzen
stats_start_collector
Ob die Statistiksammlung aktiviert werden soll.
Auf on setzen
stats_row_level, stats_block_level
Ob Informationen zur Aktivität auf Zeilen- und Blockebene gesammelt werden sollen.
Setzen auf
stats_row_level=on
stats_block_level=off
stats_reset_on_server_start
Ob die Statistik beim Neustart des Servers zurückgesetzt werden soll.
Auf off setzen
Autovacuum
VACUUM – „Müllsammlung“. VACUUM gibt den von „toten“ Daten belegten Speicherplatz frei. Bei normalen Datenoperationen löscht PostgreSQL die Daten nicht physisch aus den Tabellen; dies geschieht durch den VACUUM-Befehl.
autovacuum
Ob das Autovacuum (automatischer Start von VACUUM) aktiviert werden soll. Auf on setzen.
autovacuum_naptime
Pause zwischen den Autovacuum-Ausführungen.
Abhängig davon, wie häufig die Daten in Ihren Tabellen aktualisiert werden. Kann etwa 5 Minuten betragen, standardmäßig 1 Minute.
autovacuum_analyze_threshold
Bestimmt die minimale Anzahl von Einfüge-, Aktualisierungs- oder Löschvorgängen, die erforderlich sind, um ein Signal zum Starten von ANALYZE in einer beliebigen Tabelle auszulösen. Dieser Parameter kann für einzelne Tabellen durch Ändern der Speichereinstellungen angepasst werden.
autovacuum_vacuum_threshold
Bestimmt die minimale Anzahl von Einfüge-, Aktualisierungs- oder Löschvorgängen, die erforderlich sind, um ein Signal zum Starten von VACUUM in einer beliebigen Tabelle auszulösen. Dieser Parameter kann für einzelne Tabellen durch Ändern der Speichereinstellungen angepasst werden.
Sperrungen
max_locks_per_transaction
Anzahl der Sperren pro Transaktion: auf etwa 250 setzen.
deadlock_timeout
Zeit bis zur Erkennung von Deadlocks. Auf etwa 2 Sekunden setzen.
Mögliche Fehler
Nach der Korrektur der Konfigurationsdatei kann PostgreSQL nicht starten und folgenden Fehler ausgeben:
FATAL: could not create shared memory segment:...
Failed system call was shmget(key=5432001, size=140075008, 03600).
Dieser Fehler bedeutet in der Regel, dass die Anforderung von PostgreSQL für ein Shared-Memory-Segment den SHMMAX-Parameter Ihres Kernels überschreitet. Sie können entweder die Anforderungsgröße reduzieren oder den Kernel mit einem größeren SHMMAX neu konfigurieren. Um die Anforderungsgröße (derzeit 140075008 Bytes) zu reduzieren, reduzieren Sie den shared_buffers-Parameter von PostgreSQL (derzeit 16384) und/oder den max_connections-Parameter (derzeit 10).
Wenn die Anforderungsgröße bereits klein ist, ist es möglich, dass sie kleiner ist als der SHMMIN-Parameter Ihres Kernels. In diesem Fall muss die Anforderungsgröße erhöht oder SHMMIN neu konfiguriert werden.
Die PostgreSQL-Dokumentation enthält weitere Informationen zur Konfiguration des Shared Memory.
Zur Behebung muss der Parameter SHMMAX im System erhöht werden. Dies kann wie folgt erfolgen (für Linux-Betriebssysteme):
Führen Sie den folgenden Befehl aus:
echo 150829120 > /proc/sys/kernel/shmmax
wobei Sie Ihren eigenen neuen Wert für den Parameter angeben (welcher Wert genau – kann im PostgreSQL-Fehlerprotokoll nachgesehen werden). Dieser Vorgang setzt den Parameter zur Laufzeit, aber nach einem Neustart des Systems gehen die Änderungen verloren. Um dies zu verhindern, fügen Sie die folgende Zeile in die Datei /etc/sysctl.conf ein:
kernel.shmmax = 150829120
wobei Sie Ihren eigenen Wert angeben.
Konfigurationsbeispiel für einen Server mit 2 GB Arbeitsspeicher
/var/lib/pgsql/data/postgresql.conf:
to be done
/etc/sysctl.conf:
to be done
Mittlere Konfiguration
Durchschnittliche Konfiguration für maximale Leistung. Für einen bestimmten Anwendungsfall können andere Einstellungen besser geeignet sein. Lesen Sie diese Anleitung sorgfältig durch und passen Sie PostgreSQL entsprechend an.
RAM – Speichergöße
* shared_buffers = 1/8 des RAM oder mehr (aber nicht mehr als 1/4);
* work_mem = 1/20 des RAM;
* maintenance_work_mem = 1/4 des RAM;
* max_fsm_relations = geplante Anzahl von Tabellen in den Datenbanken * 1,5;
* max_fsm_pages = max_fsm_relations * 2000;
* fsync = true;
* wal_sync_method = fdatasync;
* commit_delay = zwischen 10 und 100;
* commit_siblings = zwischen 5 und 10;
* effective_cache_size = 0,9 des von free angezeigten Caching-Werts;
* random_page_cost = 2 für schnelle CPUs, 4 für langsame CPUs;
* cpu_tuple_cost = 0,001 für schnelle CPUs, 0,01 für langsame CPUs;
* cpu_index_tuple_cost = 0,0005 für schnelle CPUs, 0,005 für langsame CPUs;
* autovacuum = on
* autovacuum_vacuum_threshold = 1800
* autovacuum_analyze_threshold = 900
Datenbankwartung