PostgreSQL Performance Tuning Tutorial

Das korrekte Einrichten einer Datenbank ist zunächst der erste Schritt im PostgreSQL Performance Tuning. Mit jeder Tabelle, die Du hinzufügst und jeder Abfrage, die Du ausführst, müssen Deine Datenbanken gewartet und aktualisiert werden, um eine optimale PostgreSQL-Optimierung sicherzustellen.

Die PostgreSQL-Optimierung ist ziemlich unkompliziert, dennoch gibt es einige Dinge, die Du als DBA wissen solltest, um effektiv arbeiten zu können.

Bei der Optimierung des PostgreSQL Performance Tunings einer Anwendung gibt es eine Regel:
beginne nicht zu früh an, zu optimieren. Zunächst solltest Du eine Datenbank und Dein Datenbankschema implementieren.
Wenn Du Dein optimales Setup hast, kannst Du Deine SQL-Abfragen mit Tools überwachen. Dies ist vor allem deshalb wichtig, weil man beim PostgreSQL Performance Tuning oft Kompromisse eingehen muss. Du kannst Dich zum Beispiel dazu entscheiden, eine selten benutzte Abfrage langsam verarbeiten zu lassen, um dafür eine blitzschnelle Antwortzeit bei einer häufig ausgeführten Abfrage zu erhalten.

Wir wollen zunächst verschiedene PostgreSQL-Optimierungen durchgehen und beginnen als Erstes mit ein paar der verfügbaren Basic Setup Optionen:

  • Hardware Updates – Änderungen, die Du auf dem physikalischen Server vornehmen kannst

  • Konfiguration – Änderungen der voreingestellten PostgreSQL-Konfiguration

  • Vacuuming – Möglichkeiten, um mit Vacuum-Einstellungen die Leistung verbessern zu können

Wenn Du Dein System soweit eingerichtet hast, können wir uns als Nächstes anschauen, wie Du Dein Datenbankschema analysieren und verbessern kannst:

  • Analysiere Deine Abfrageleistung – So analysierst Du einzelne Abfragen

  • Analysiere Deine Logs – So erhältst Du Informationen zur Analyse aus Deinem System

  • Index für die Abfrageleistung – Durch das Hinzufügen von Indexen kann die Datenbank Deinen Abfrageplan optimieren

 

Teil 1: Best Practices and Setup

Im ersten Teil des Artikels werden wir erläutern, welche Veränderungen bei den Grundeinstellungen vorgenommen werden können, um eine schnellere PostgreSQL-Performance zu erreichen. Im zweiten Teil werden wir uns damit beschäftigen, wie Du Deine Systemspezifikationen verbessern kannst.

 

Die PostgreSQL Hardware aufrüsten

Der naheliegende Ausgangspunkt für die Optimierung der PostgreSQL-Performance ist, wenn man sich die Hardware des Systems selbst anschaut. Einige der für Deine Datenbank wichtigsten Faktoren sind der verfügbare Arbeitsspeicher, die CPU, der Speicherplatz und die Leistung.

Wenn es um Hardware Updates geht, solltest Du folgendes beachten:

Den Arbeitsspeicher aktualisieren – Der Arbeitsspeicher wird von Deinem System verwendet, um häufig benötigte Daten effektiv zu cachen. Es ist wichtig, den Arbeitsspeicher im Auge zu behalten, wenn Du später Deine Abfragen optimierst. Je größer Dein Cache, desto weniger Festplattenzugriffe muss Deine Datenbank machen – was Deiner Leistung schaden könnte. PostgreSQL versucht die am häufigsten aufgerufenen Daten im Arbeitsspeicher zu behalten, um Leistungssteigerungen basierend auf der Ausführung Deiner Abfragen und der von Dir angegebenen Konfiguration zu erreichen.

Trennung der Anwendung von der Datenbank – Wenn Du Deine Datenbankanwendung auf demselben Server wie Deine Anwendung ausführst, solltest Du in Betracht ziehen, sie zu isolieren. Es wird schwierig sein, Deine Datenbankleistung wirklich zu analysieren und zu verbessern, wenn eine separate Komponente Deine Metriken und Deine Umgebung beeinflusst.

 

Datenbankkonfiguration: Was, warum und wie?

PostgreSQL verfügt über eine Reihe von Standardkonfigurationen. Diese Standardkonfiguration ist aus Kompatibilitätsgründen eingerichtet und stellt im Wesentlichen den beste Versuch dar, allen möglichen Anwendungsfällen von PostgreSQL gerecht zu werden. Dies bedeutet, dass es glücklicherweise einige potenzielle schnelle Gewinne gibt, wenn Du anfängst, die Standardkonfiguration zu optimieren.

Datenbankkonfigurationen in PostgreSQL werden direkt in der Konfigurationsdatei postgresql.conf oder durch Ausführen eines ALTER SYSTEM-Befehls vorgenommen. Wenn jetzt alle aktuellen Konfigurationen Deiner Datenbank angezeigt werden sollen, führst Du einfach den folgenden SHOW-Befehl aus:

SHOW ALL

Dieser Befehl listet alle vorhandenen Konfigurationen und ihre jeweiligen Einstellungen auf. Es ist außerdem wichtig darauf hinzuweisen, dass unterschiedliche Konfigurationen nur unter bestimmten Bedingungen greifen, z.B. bei einem Neustart der Datenbank. Manche Konfigurationsänderungen werden einen Neustart des Servers erfordern und bei anderen muss die Konfiguration neu geladen werden.

Wenn Du Konfigurationsänderungen in Deiner Datenbank durchgeführt hast, kannst Du Details anzeigen, z.B. ob für Dein Konfigurationsupdate ein Neustart erforderlich ist, indem Du den folgenden Befehl ausführst:

SELECT * FROM pg_settings WHERE pending_restart = true;

 

Konfigurationsänderungen des PostgreSQL Performance Tuning

Wahrscheinlich möchtest Du viele verschiedene Konfigurationen ändern, um Deine PostgreSQL Datenbank bestmöglich nutzen zu können. Im folgenden besprechen wir einige der Hauptkonfigurationen, die Du ändern kannst, um mehr Leistung von Deinem System zu erhalten.

max_connections

Über Verbindungen kommunizierst Du Anwendungen mit Deiner Datenbank. Sobald eine Verbindung hergestellt ist, können Abfragen an Deine Datenbank gesendet werden. Deine PostgreSQL Datenbank enthält eine maximale Anzahl an Verbindungen, die Du aufbauen kannst. Du solltest sichergehen, dass keine unnötigen Verbindungen aufgebaut werden, da dies die Leistung beeinträchtigen kann. Weiterhin sollte ein Gleichgewicht zwischen der Zuweisung von Speicher und der Anzahl der Verbindungen hergestellt sein, da Speicherzuweisung pro Verbindung durchgeführt wird.

checkpoint_segments

Ein Checkpoint ist eine regelmäßige Aktion, die Informationen über Dein System speichert. Standardmäßig wird ein Checkpoint nach einer Reihe von Segmenten ausgeführt, wobei Du möglicherweise diesen Wert in Abhängigkeit von Deinem System erhöhen solltest. Es wird oft angenommen, dass die Standardkonfiguration zu aggressiv ist und zu oft Checkpoints ausführt. Daher solltest Du diesen Wert möglicherweise erhöhen, um die Häufigkeit von Checkpoints zu verringern.

work_mem

Wie bereits erwähnt, spielen Speicherzuweisung und -verwaltung eine große Rolle bei der Leistungsoptimierung von PostgreSQL. Wenn Dein System viele komplexe Sortierungen ausführt, kann das Erhöhen des Arbeitsspeichers der Datenbank helfen, die Konfiguration für Dein Setup zu optimieren. Dies ermöglicht PostgreSQL mehr Daten während der Sortierung im Speicher zu cachen, anstatt teure Aufrufe an die Festplatte zu tätigen.

random_page_cost

Diese Einstellung gibt im Wesentlichen die Zeit an, die Dein Optimierer zum Lesen des Speichers benötigen sollte, bevor er auf Deine Festplatte zugreift. Du solltest diese Einstellung nur ändern, wenn Du andere planbasierte Optimierungen vorgenommen hast, z. B. Vacuuming, Indexing oder Ändern Deiner Abfragen und Deines Schemas.

Dies sind nur einige der Optimierungen, die Du für Datenbankkonfigurationen vornehmen kannst, aber es gibt noch viele Weitere. Nachdem Du nun weißt, wie Du Dein Datenbank-Setup optimieren kannst, schauen wir uns einen anderen Bereich an: Vacuuming.

 

Vacuuming benutzen, um Bloats zu verhindern

Der nächste Bereich mit dem wir uns beschäftigen ist Vacuuming. Ein Vacuum ist ist ein Scan, der Tupel als nicht mehr benötigt markiert, damit sie überschrieben werden können. Wenn Du dies nicht tust, kann es bedeuten, dass Du “tote” Tupel in Deinem System hast. Diese “toten” Tupel werden oft als Bloat bezeichnet. Ein Bloat entsteht meistens durch Datensätze, die gelöscht, aktualisiert oder eingefügt werden.

PostgreSQL richtet das Vacuuming standardmäßig ein, aber genauso wie Du andere Einstellungen konfigurieren kannst, kannst Du auch Deine Vacuuming-Einstellungen festlegen. Du kannst die Vacuuming-Einstellungen sogar pro Tabelle festlegen, um eine genauere Optimierung zu erzielen.

Um ein Vacuum durchzuführen, führst Du einfach den folgenden Befehl aus:

VACUUM

Wenn Du den Verlauf vorheriger Vacuums anzeigen möchtest, kannst Du dies mit folgendem Befehl tun:

SELECT * FROM pg_stat_user_tables

Im Allgemeinen kann regelmäßiges Vacuuming nicht oft genug durchgeführt werden. Häufigeres Vaccuming sorgt dafür, dass Bloats auf ein Minimum reduziert werden und die Datenbankleistung hoch bleibt.

 

Teil 2: Analyse der PostgreSQL-Performance

Eine im Vorfeld eingestellte Systemkonfiguration wird die Steigerung der PostgreSQL-Performance begrenzen. Obwohl Du einige Konfigurationen im Vorhinein durchführen kannst, werden viele Deiner Entscheidungen bezüglich der Konfiguration von den Daten und Analysen Deines Systems abhängen. PostgreSQL und die Community bieten einige nützliche Tools für verschiedene Optimierungen.

 

Aktualisiere die Postgres-Algorithmusdaten mit ANALYZE

Bevor wir fortfahren, ist es wichtig, den Lebenszyklus einer Abfrage zu verstehen. Der Lebenszyklus reicht von einer ersten Anfrage an die Datenbank bis hin zur gesendeten Antwort. Wenn Du über eine große Menge an Daten verfügst, kann eine grobe Abfrage Deiner Daten zu Leistungseinbußen führen. Wenn Du Deine Datenbank nacheinander (häufig als Tabellenscan bezeichnet) nach Deinen Daten durchsuchst, wird die Leistung linear skaliert – mehr Zeilen, langsamere Leistung. Aber das kann man verbessern!

Was ist also der Lebenszyklus einer Abfrage? Zunächst erfolgt eine Übertragung der Abfrage an PostgreSQL. Die Abfrage wird danach analysiert und anschließend ein Plan erstellt. Pläne sind die Schritte, die PostgreSQL unternehmen wird, um die angeforderten Daten zu finden. Wie Du Deine Datenbankkonfiguration, Dein Schema und Deine Indizes einrichtest, wirkt sich auf die Leistung dieser Pläne aus. Deshalb ist es wichtig, die Pläne zu verstehen und zu wissen, wie wir sie optimieren können. Schließlich führt die Datenbank den Plan aus und ruft die Daten ab.

Es kann eine Diskrepanz zwischen dem Datenbankplan, den PostgreSQL zum Abrufen Deiner Daten verwenden möchte, und der Art und Weise, wie Deine Daten tatsächlich abgerufen werden, auftreten. Dies liegt daran, dass PostgreSQL seinen Plan auf selten aktualisierten Metriken und Statistiken basiert. Einige Daten müssen regelmäßig aktualisiert werden, damit die Statistiken, die für die Pläne verwendet werden, auf dem neuesten Stand sind.

Hier kommt ANALYZE ins Spiel. Durch Ausführen des ANALYZE-Befehls werden diese Statistiken aktualisiert, sodass Postgres über einen neuen Datensatz zum Erstellen seiner Pläne verfügt. Wenn Du also die Tabellen oder das Schema aktualisierst oder Indizes hinzufügst, denke daran, danach einen ANALYZE-Befehl auszuführen, damit die Änderungen wirksam werden.

 

Verstehe Deine Abfrageleistung

Einer der nächsten offensichtlichen Bereiche ist die Abfrageoptimierung. Die Abfragen, die Du ausführst, können aus vielen Gründen ineffizient sein, sodass wir verstehen müssen, was mit diesen Abfragen geschieht. Hier kommt der Befehl EXPLAIN ins Spiel.

EXPLAIN gibt Dir eine genaue Aufschlüsselung, wie PostgreSQL Deine Abfrage ausführen wird. Der Plan basiert auf Statistiken über die Tabelle und berücksichtigt beispielsweise Datenbankindizes, um den effizientesten Pfad zu Deinen Daten zu finden. Aber EXPLAIN wird nur raten und Dir einen Plan geben, von dem es glaubt, dass er ausgeführt wird. Du kannst EXPLAIN ANALYZE ausführen, um nicht nur die Informationen zum vorhergesagten Plan abzurufen, sondern auch ein Update zu der Leistung der Abfrage.

Wenn Du den Plan hast, den die Datenbank ausführen möchte, kannst Du mit der Analyse beginnen.

 

Logs als Quelle für hochwertige PostgreSQL-Performance Tuning Data

Nehmen wir an, Du hast eine laufende Datenbank und möchtest die langsame Leistung in der Anwendung debuggen. Eine Möglichkeit, dies zu tun, ist durch Logs. Logs sind kurze Informationsanweisungen, die die Anwendung hinterlässt, wenn sie eine Aktion ausführt.

Standardmäßig protokolliert Deine Anwendung nicht alle Daten. Dies liegt daran, dass sich eine Erhöhung der Protokollierung auch auf die Datenbankleistung auswirkt. Während wir durchgehen, wie Du die Log-Einstellungen ändern kannst, um die Leistung zu analysieren – denke also daran, dass die Protokolleinstellungen selbst die Leistung beeinflussen können.

Abhängig von Deiner Konfiguration werden Logs an eine Datei in Deinem System gesendet. Wenn Du herausgefunden hast, wo sich Deine Protokolle befinden, kannst Du diese Protokolle mit einem Tool analysieren.

Nun, da wir wissen, wie gut Logs sind, um die Systemleistung zu verstehen, schauen wir uns an, wie wir vorgehen, um sie einzurichten oder zu ändern.

log_line_prefix

Dies ist das Format Deiner Datenbank-Logs. Das Präfix der Log-Zeile teilt PostgreSQL mit, in welchem ​​Format die Log-Daten ausgegeben werden sollen. Wenn Du eine aussagekräftige Log-Analyse durchführen möchtest, musst Du diesen Wert so einstellen, dass er mit dem Tool kompatibel ist, mit dem Du Deine Protokolle analysierst.

log_statement

Dies ist das Log-Level, mit dem Du arbeitest. Log-Level beziehen sich normalerweise auf die Detailstufe, die Du Dir in Deinen Protokollen wünschst. Möchtest Du beispielsweise absolut alle Logs oder nur Logs eines bestimmten Typs?

log_statement hat hier verschiedene Einstellungen, darunter:

  • ddl, das nur strukturelle Änderungen in der Datenbank protokolliert

  • mod, das Änderungen an vorhandenen Daten protokolliert

  • all, das alles protokolliert

log_checkpoints

Wie in den Konfigurationseinstellungen besprochen, sind Checkpoints in PostgreSQL regelmäßige Aktionen, in denen Daten über Dein System gespeichert werden. Diese Log-Checkpoints können, wenn sie übermäßig groß sind, zu Leistungseinbußen führen. Wenn Du den Verdacht hast, dass dies der Fall sein könnte, kannst Du durch Aktivieren der Log-Checkpoints umfangreiche Daten zu diesen Checkpoints einsehen, z. B. wie oft sie ausgeführt werden und was sie auslösen könnten.

logging_connection

Möglicherweise möchtest Du auch Informationen zu Verbindungen erhalten. Wenn Du nur eine Anwendung hast, die eine Verbindung zu Deiner Datenbank herstellt, aber viele gleichzeitige Verbindungen sehen kannst, kann ein Fehler vorliegen. Zu viele Verbindungen, die Deine Datenbank überfluten, können auch dazu führen, dass Anfragen die Datenbank nicht erreichen und die Endnutzer Deiner Anwendung beeinträchtigt werden.

Nachdem wir nun viele Daten über unser System haben, interessiert uns, welche Tools wir haben, um die Struktur unseres Systems zu verbessern und leistungsfähiger werden zu lassen.

 

Schnellere Abfragen mit Datenbankindexen

In einer Welt ohne Indexe würde jede Anfrage an die Datenbank zu einem vollständigen Scan der gesamten Tabelle führen, um die relevanten Ergebnisse zu finden. Bei einem großen Datensatz kann dies extrem langsam sein – und hier kommt die Datenbankindizierung ins Spiel. Wie in einem Buch geben Indexe Deiner Datenbank-Engine Informationen darüber, wo in etwa in Deinem System die Daten zu finden sind, die Du suchst. Um unser System richtig zu indizieren, benötigen wir ein Verständnis für unsere Daten und wie wir versuchen, darauf zuzugreifen.

PostgreSQL hat hierfür verschiedene Arten von Indexen. Der Unterschied zwischen diesen besteht darin, dass sie alle einen anderen Algorithmus verwenden. Die verfügbaren Indexe sind B-Tree (der Standardindex), Hash, GiST, SP-GiST und GIN. PostgreSQL erstellt implizite Indexe, wenn Du einen Primärschlüssel oder ein Schlüssel-Constraint erstellst. Du musst den Rest Deiner Indexe jedoch manuell hinzufügen.

Die grundlegende Syntax für einen INDEX lautet:

CREATE INDEX index_name ON table_name;

In einigen Anwendungsfällen solltest Du jedoch keinen Index verwenden, z. B. wenn der Aufwand für die Verwendung des Indexes den Nutzen des Algorithmus, wie beispielsweise bei einer kleinen Tabelle, übersteigt. Bei Tabellen mit großen Batch-Updates können jedoch auch Leistungsprobleme auftreten. Es kann sinnvoll sein, Indizes für diese Tabellen während der Aktualisierung vorübergehend zu entfernen, bevor sie wiederhergestellt werden.

 

Da hast Du es: blitzschnelles PostgreSQL Performance Tuning

Hoffentlich hast Du Verständnis dafür erhalten, wie Du mit dem PostgreSQL Performance Tuning Deiner Datenbank beginnen kannst. Du solltest jetzt dazu in der Lage sein, Deine Abfragen mit EXPLAIN zu analysieren, Deine Logs für weitere Einblicke abzurufen und Deine Indexe für eine schnellere Leistung zu verändern. Außerdem solltest Du in der Lage sein, Deine Datenbankkonfiguration an Deine jeweilige Verwendung anzupassen, damit Du das Beste daraus machen kannst.


→ Hier findest Du den Artikel zum direkten PDF-Download: madafa.de/download/artikel-downloads/


Schreibe einen Kommentar