PostgreSQL Performance Tuning für eine schnellere Abfrageausführung

Zielsetzung

Unser Ziel ist es, eine Dummy -Abfrage -Ausführung in der PostgreSQL -Datenbank schneller auszuführen, indem Sie nur die integrierten in den verfügbaren Tools zur Verfügung stellen
in der Datenbank.

Betriebssystem- und Softwareversionen

• Betriebssystem: Red Hat Enterprise Linux 7.5
• Software: PostgreSQL Server 9.2

Anforderungen

PostgreSQL Server Base Installieren. Zugriff auf das Befehlszeilenwerkzeug PSQL und Eigentum an der Beispieldatenbank.

Konventionen

• # - erfordert, dass gegebene Linux -Befehle mit Root -Berechtigungen entweder direkt als Stammbenutzer oder mit Verwendung von ausgeführt werden können sudo Befehl
• $ - Angegebene Linux-Befehle, die als regelmäßiger nicht privilegierter Benutzer ausgeführt werden sollen

Einführung

PostgreSQL ist eine zuverlässige Open -Source -Datenbank, die im Repository der modernen Verteilung verfügbar ist. Die Benutzerfreundlichkeit, die Möglichkeit, Erweiterungen zu verwenden, und die Stabilität, die sie für die Beliebtheit ergänzt.
Während die Basisfunktionalität wie die Beantwortung von SQL -Abfragen bereitgestellt wird, speichern Sie eingefügte Daten konsequent, Handhabung von Transaktionen usw. Die meisten ausgereiften Datenbanklösungen bieten Tools und Know-hows darüber, wie es geht
Stellen Sie die Datenbank ein, identifizieren Sie mögliche Engpässe und können die Leistungsprobleme lösen, wenn das System, das durch die angegebene Lösung betrieben wird, wächst.

Postgresql ist keine Ausnahme, und in diesem
Leitfaden werden wir das integrierte Werkzeug verwenden erklären Eine langsam laufende Frage schneller abschließen. Es ist weit von einer realen Datenbank entfernt, aber man kann den Hinweis auf die Verwendung der integrierten Werkzeuge nutzen. Wir werden einen PostgreSQL Server Version 9 verwenden.2 auf Red Hat Linux 7.5, aber die in diesem Handbuch gezeigten Tools sind auch in viel älteren Datenbank- und Betriebssystemversionen vorhanden.

Das Problem zu lösen

Betrachten Sie diese einfache Tabelle (die Spaltennamen sind selbsterklärend):

foobardb =# \ d+ Mitarbeiter Tabelle "öffentlich.Mitarbeiter "Spalte" | Typ | Modifikatoren | Speicher | Statistiken Ziel | Beschreibung --------------+----------+------- ------------------------------------------+--- -------+--------------+------------- EMP_ID | Numerisch | Nicht null Standard-NextVal ('personal_seq' :: RegClass) | Haupt | | First_Name | Text | nicht null | erweitert | | last_name | text | nicht null | erweitert | | birth_year | numeric | nicht null | main | birth_month | numerisch | nicht null | main | birg | birgdofmonth | numerisch | nicht null | main | | Indexe: "personal_pkey" Primärschlüssel, BTree (emp_id) hat OIDs: Nein 

Mit Rekorde wie:

foobardb =# select * aus den Mitarbeitern limit 2; emp_id | First_Name | last_name | birth_year | BURTY_MONTH | birth_dayofmonth --------+------------+-----------+------------+- -----------+------------------ 1 | Emily | James | 1983 | 3 | 20 2 | John | Smith | 1990 | 8 | 12 

In diesem Beispiel sind wir die nette Firma und stellten eine Bewerbung namens HBApp ein, die an seinem Geburtstag eine E -Mail „Alles Gute zum Geburtstag“ an den Mitarbeiter sendet. Die Anwendung findet jeden Morgen die Datenbank, um Empfänger für den Tag zu finden (vor den Arbeitszeiten möchten wir unsere HR -Datenbank nicht aus Freundlichkeit töten).
Die Anwendung führt die folgende Abfrage aus, um die Empfänger zu finden:

foobardb =# SELECT EMP_ID, First_Name, last_name von Mitarbeitern, wobei Birth_month = 3 und Birth_dayofmonth = 20; emp_id | First_Name | Last_name --------+------------+----------- 1 | Emily | James 

Alle funktionieren gut, die Benutzer erhalten ihre E -Mails. Viele andere Anwendungen verwenden die Datenbank und die Tabelle der Mitarbeiter innerhalb des Rechnungswesens und BI. Das nette Unternehmen wächst und wächst den Arbeitnehmertisch aus. Mit der Zeit läuft die Anwendung zu lang, und die Ausführung überschneidet sich mit dem Beginn der Arbeitsstunden, die sich in der Reaktionszeit der langsamen Datenbank in geschäftskritischen Anwendungen ergibt. Wir müssen etwas tun, um diese Abfrage schneller laufen zu lassen, sonst wird die Anwendung nicht eingelöst, und damit wird es in der netten Firma weniger Freundlichkeit geben.

In diesem Beispiel werden wir keine erweiterten Tools verwenden, um das Problem zu lösen, nur eine, die von der Basisinstallation bereitgestellt wird. Lassen Sie uns sehen, wie der Datenbankplaner die Abfrage ausführt erklären.

Wir testen nicht in der Produktion; Wir erstellen eine Datenbank zum Testen, erstellen die Tabelle und fügen zwei oben erwähnte Mitarbeiter ein. Wir verwenden die gleichen Werte für die Abfrage in diesem Tutorial die ganze Zeit,
Also passt bei jedem Lauf nur ein Rekord mit der Abfrage zusammen: Emily James. Dann führen wir die Abfrage mit den vorhergehenden Erklären Sie Analyse Um zu sehen, wie es in der Tabelle mit minimalen Daten ausgeführt wird:

foobardb =# Erklären Sie analysieren ausgewählt EMP_ID, First_Name, last_name von Mitarbeitern wobei Birth_month = 3 und Birth_dayofmonth = 20; Abfrageplan -------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- SEQ-Scan für Mitarbeiter (Kosten = 0.00… 15.40 Zeilen = 1 Breite = 96) (tatsächliche Zeit = 0.023… 0.025 Zeilen = 1 Schleifen = 1) Filter: ((birth_month = 3 :: numerisch) und (birth_dayofmonth = 20 :: numerisch)) Zeilen mit Filter entfernt: 1 Gesamtlaufzeit: 0.076 ms (4 Zeilen) 

Das ist sehr schnell. Möglicherweise so schnell wie als das Unternehmen zum ersten Mal den HBApp einsetzte. Nachahmen wir den Stand der gegenwärtigen Produktion nach foobardb Durch das Laden von so vielen (gefälschten) Mitarbeitern in die Datenbank wie in der Produktion (Hinweis: Wir benötigen die gleiche Speichergröße in der Testdatenbank wie in der Produktion).

Wir werden einfach Bash verwenden, um die Testdatenbank zu füllen (vorausgesetzt, wir haben 500.000 Mitarbeiter in der Produktion):

$ für j in 1… 500000; Echo "in Mitarbeiter einfügen (First_Name, last_name, birgy_year, birg month, birg.dayofmonth) Werte ('Benutzer $ j', 'test', 1900,01,01);"; fertig | psql -d foobardb 

Jetzt haben wir 500002 Mitarbeiter:

foobardb =# Wählen Sie Graf (*) von Mitarbeitern aus; Graf -------- 500002 (1 Reihe) 

Lassen Sie uns die Erklärungsanfrage erneut ausführen:

foobardb =# Erklären Sie analysieren ausgewählt EMP_ID, First_Name, last_name von Mitarbeitern wobei Birth_month = 3 und Birth_dayofmonth = 20; Abfrageplan -------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- SEQ-Scan auf Mitarbeitern (Kosten = 0.00… 11667.63 Zeilen = 1 Breite = 22) (tatsächliche Zeit = 0.012… 150.998 Zeilen = 1 Schleifen = 1) Filter: ((birth_month = 3 :: numerisch) und (birth_dayofmonth = 20 :: numerisch)) Zeilen, die mit Filter entfernt wurden: 500001 Total Runtime: 151.059 ms 

Wir haben immer noch nur ein Match, aber die Abfrage ist deutlich langsamer. Wir sollten den ersten Knoten des Planers bemerken: SEQ -Scan Welches steht für sequentielle Scan - Die Datenbank liest das Ganze
Tabelle, während wir nur einen Rekord brauchen, wie a Grep würde in verprügeln. In der Tat kann es tatsächlich langsamer sein als Grep. Wenn wir die Tabelle in eine CSV -Datei namens exportieren /tmp/exp500k.CSV:

 foobardb =# Mitarbeiter zu '/tmp/exp500k kopieren.CSV 'Gremiter', 'CSV -Header; Kopieren Sie 500002 

Und grep die Informationen, die wir benötigen (wir suchen nach dem 20. Tag des 3. Monats, die letzten beiden Werte in der CSV -Datei in jedem
Linie):

$ time grep ", 3,20" /tmp /exp500k.CSV 1, Emily, James, 1983,3,20 Real 0m0.067S Benutzer 0m0.018S SYS 0M0.010s 

Dies ist, abgesehen von dem Rücken, als langsamer und langsamer, wenn der Tisch wächst.

Die Lösung ist die Indizierung der Ursache. Kein Mitarbeiter kann mehr als ein Geburtsdatum haben, was aus genau einem besteht Geburtsjahr, Geburtsmonat Und Birth_dayofmonth - Diese drei Felder bieten also einen eindeutigen Wert für diesen bestimmten Benutzer. Und ein Benutzer wird von seinem/ihr identifiziert emp_id (Es kann mehr als einen Mitarbeiter im Unternehmen mit demselben Namen geben). Wenn wir in diesen vier Feldern eine Einschränkung deklarieren, wird auch ein implizite Index erstellt:

foobardb =# Alter Tabelle Mitarbeitern addieren Einschränkungen birth_uniq eindeutig (emp_id, birth_year, birth_month, birth_dayofmonth); Hinweis: Änderung der Tabelle / Hinzufügen eindeutig erstellt implizite Index "birth_uniq" für Tabellen -Mitarbeiter "Mitarbeiter" 

Also haben wir einen Index für die vier Felder erhalten. Mal sehen, wie unsere Abfrage ausgeführt wird:

foobardb =# Erklären Sie analysieren ausgewählt EMP_ID, First_Name, last_name von Mitarbeitern wobei Birth_month = 3 und Birth_dayofmonth = 20; Abfrageplan -------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- SEQ-Scan für Mitarbeiter (Kosten = 0.00… 11667.19 Zeilen = 1 Breite = 22) (tatsächliche Zeit = 103.131… 151.084 Zeilen = 1 Schleifen = 1) Filter: ((birth_month = 3 :: numerisch) und (birth_dayofmonth = 20 :: numerisch)) Zeilen, die mit Filter entfernt wurden: 500001 Total Runtime: 151.103 ms (4 Zeilen) 

Das ist identisch mit dem letzten, und wir können sehen, dass der Plan der gleiche ist, der Index wird nicht verwendet. Erstellen wir einen weiteren Index durch eine eindeutige Einschränkung auf emp_id, Geburtsmonat Und Birth_dayofmonth Nur (schließlich fragen wir nicht nach Geburtsjahr in Hbapp):

foobardb =# table table table -Mitarbeiter fügen Einschränkungen für birth_uniq_m_dom einzigartig (emp_id, birth_month, birth_dayofmonth); Hinweis: Änderung der Tabelle / Hinzufügen eindeutig erstellt implizite Index "birth_uniq_m_dom" für Tabellen "Mitarbeiter" 

Lassen Sie uns das Ergebnis unserer Abstimmung sehen:

foobardb =# Erklären Sie analysieren ausgewählt EMP_ID, First_Name, last_name von Mitarbeitern wobei Birth_month = 3 und Birth_dayofmonth = 20; Abfrageplan -------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- SEQ-Scan für Mitarbeiter (Kosten = 0.00… 11667.19 Zeilen = 1 Breite = 22) (tatsächliche Zeit = 97.187… 139.858 Zeilen = 1 Schleifen = 1) Filter: ((birth_month = 3 :: numerisch) und (birth_dayofmonth = 20 :: numerisch)) Zeilen, die mit Filter entfernt wurden: 500001 Total Runtime: 139.879 ms (4 Zeilen) 

Nichts. Der obige Unterschied ergibt sich aus der Verwendung von Caches, aber der Plan ist der gleiche. Lass uns weiter gehen. Als nächstes erstellen wir einen weiteren Index auf emp_id Und Geburtsmonat:

foobardb =# table table table -Mitarbeiter fügen Einschränkungen von birth_uniq_m einzigartig (emp_id, birg month) hinzu; Hinweis: Änderung der Tabelle / Hinzufügen eindeutig erstellt implizite Index "birth_uniq_m" für Tabellen -Mitarbeiter "Mitarbeiter" 

Und führen Sie die Anfrage erneut aus:

foobardb =# Erklären Sie analysieren ausgewählt EMP_ID, First_Name, last_name von Mitarbeitern wobei Birth_month = 3 und Birth_dayofmonth = 20; Abfrageplan -------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- Indexscan mit gebürtigen_uniq_m bei den Mitarbeitern (cost = 0.00… 11464.19 Zeilen = 1 Breite = 22) (tatsächliche Zeit = 0.089… 95.605 Zeilen = 1 Schleifen = 1) INDEX -Cond: (birth_month = 3 :: numerisch) Filter: (birgty_dayofmonth = 20 :: numerisch) Gesamtlaufzeit: 95.630 ms (4 Zeilen) 

Erfolg! Die Abfrage ist 40% schneller und wir können sehen, dass sich der Plan geändert hat: Die Datenbank scannt nicht mehr die gesamte Tabelle, sondern verwendet den Index auf Geburtsmonat Und emp_id. Wir haben alle Mischungen der vier Felder erstellt, nur einer bleibt übrig. Es lohnt sich zu versuchen:

foobardb =# table table table Mitarbeiter fügen Einschränkungen für birth_uniq_dom einzigartig (emp_id, birgddayofmonth); Hinweis: Änderung der Tabelle / Hinzufügen eindeutig erstellt implizite Index "birth_uniq_dom" für Tabellen "Mitarbeiter" 

Der letzte Index wird auf Feldern erstellt emp_id Und Birth_dayofmonth. Und das Ergebnis ist:

foobardb =# Erklären Sie analysieren ausgewählt EMP_ID, First_Name, last_name von Mitarbeitern wobei Birth_month = 3 und Birth_dayofmonth = 20; Abfrageplan -------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ Indexscan mit BURTY_UNIQ_DOM für Mitarbeiter (cost = 0.00… 11464.19 Zeilen = 1 Breite = 22) (tatsächliche Zeit = 0.025… 72.394 Zeilen = 1 Schleifen = 1) INDEX -Cond: (birth_dayofmonth = 20 :: numerisch) Filter: (birth_month = 3 :: numerisch) Gesamtlaufzeit: 72.421 ms (4 Zeilen) 

Jetzt ist unsere Abfrage etwa 49% schneller und verwendet den letzten (und nur den letzten) Index, der erstellt wurde. Unsere Tabelle und verwandte Indizes sehen wie folgt aus:

foobardb =# \ d+ Mitarbeiter Tabelle "öffentlich.Mitarbeiter "Spalte" | Typ | Modifikatoren | Speicher | Statistiken Ziel | Beschreibung --------------+----------+------- ------------------------------------------+--- -------+--------------+------------- EMP_ID | Numerisch | Nicht null Standard-NextVal ('personal_seq' :: RegClass) | Haupt | | First_Name | Text | nicht null | erweitert | | last_name | text | nicht null | erweitert | | birth_year | numeric | nicht null | main | birth_month | numerisch | nicht null | main | birgdofmonth | numerisch | nicht null | main | | Indexe: "maßstabnehmer_pkey" Primärschlüssel, btree (emp_id) "birth_uniq" eindeutige Einschränkung, btree (emp_id, birth_year, birth_month, birg.dayofmonth) "birg_uniq_dom" Eindeutige Einschränkung, Bree (emp_id, birgdayofmonth) "birgdofmonth)" birgh) "bir Birgmmonth)" bir Birgmmonth) "bir Birgmmonth)" bir Birgmmonth) "bir Birth_uniq_m". Einschränkung, btree (emp_id, birg month) "birg_uniq_m_dom" Eindeutige Einschränkung, btree (emp_id, birg month, birgddayofmonth) hat OIDs: Nein 

Wir brauchen nicht die erstellten Zwischenindizes, in dem Plan deutlich, dass er sie nicht verwenden wird, also lassen wir sie fallen:

foobardb =# table table table -Mitarbeiter löschen Einschränkungen birth_uniq; Änderungstabelle foobardb =# Alter Tabelle Mitarbeiter fallen als Einschränkung birth_uniq_m; ALTER TABLE FOOBARDB =# TABLE TABLE MITGRIFTEN SPRECHT BERTRAINT BURTY_UNIQ_M_DOM; Tabelle ändern 

Am Ende gewinnt unsere Tabelle nur einen zusätzlichen Index, was für die doppelte doppelte Geschwindigkeit von HBApp niedrig ist:

foobardb =# \ d+ Mitarbeiter Tabelle "öffentlich.Mitarbeiter "Spalte" | Typ | Modifikatoren | Speicher | Statistiken Ziel | Beschreibung --------------+----------+------- ------------------------------------------+--- -------+--------------+------------- EMP_ID | Numerisch | Nicht null Standard-NextVal ('personal_seq' :: RegClass) | Haupt | | First_Name | Text | nicht null | erweitert | | last_name | text | nicht null | erweitert | | birth_year | numeric | nicht null | main | birth_month | numerisch | nicht null | main | birgdofmonth | numerisch | nicht null | main | | Indexe: "personal_pkey" Primärschlüssel, btree (emp_id) "birth_uniq_dom" eindeutige Einschränkung, btree (emp_id, birgddayofmonth) hat OIDs: Nein 

Und wir können unsere Abstimmung in die Produktion einführen, indem wir den Index hinzufügen, den wir als am nützlichsten gesehen haben:

Altertabelle Mitarbeitern addieren Einschränkungen birgty_uniq_dom eindeutig (emp_id, birth_dayofmonth);

Abschluss

Unnötig zu erwähnen, dass dies nur ein Dummy -Beispiel ist. Es ist unwahrscheinlich, dass Sie das Geburtsdatum Ihres Mitarbeiters in drei separaten Feldern speichern, während Sie ein Feld vom Datumstyp verwenden können, wodurch der Datum der Date-bezogenen Operationen viel einfacher ist, als Monats- und Tageswerte als Ganzzahlen zu vergleichen. Beachten Sie auch, dass die oben genannten Abfragen nicht als übermäßige Tests fit sind. In einem realen Szenario müssen Sie die Auswirkungen des neuen Datenbankobjekts auf jede andere Anwendung testen, die die Datenbank verwendet, sowie Komponenten Ihres Systems, die mit HBApp interagieren.

In diesem Fall können wir beispielsweise die Tabelle für Empfänger in 50% der ursprünglichen Reaktionszeit verarbeiten, die wir am anderen Ende der Anwendung praktisch 200% der E -Mails produzieren können Alle 500 Tochtergesellschaften der netten Firma), die möglicherweise zu einer Spitzenlast woanders führen können. Vielleicht erhalten die Mail -Server eine Menge E -Mails für „Happy Birthday“, um die täglichen Berichte an das Management zu senden, was zu Verzögerungen führt des Versands. Es ist auch ein bisschen weit von der Realität entfernt, dass jemand, der eine Datenbank stimmt.

Beachten Sie jedoch, dass wir bei der Abfrage nur mit dem integrierten PostgreSQL 50% Leistungsschub bei der Abfrage erzielt haben erklären Feature, um einen einzelnen Index zu identifizieren, der in der gegebenen Situation nützlich sein könnte. Wir haben auch gezeigt, dass eine relationale Datenbank nicht besser ist als eine klare Textsuche, wenn wir sie nicht verwenden, da sie verwendet werden sollen.

Verwandte Linux -Tutorials:

• Dinge zu installieren auf Ubuntu 20.04
• Ubuntu 20.04 Postgresql Installation
• Ubuntu 22.04 Postgresql Installation
• Eine Einführung in Linux -Automatisierung, Tools und Techniken
• Linux -Leistungsoptimierung: Tools und Techniken
• Dinge zu tun nach der Installation Ubuntu 20.04 fokale Fossa Linux
• Linux -Download
• Linux -Konfigurationsdateien: Top 30 am wichtigsten
• Wie man Daten anhält, um in Java zu postgresql
• Dinge zu installieren auf Ubuntu 22.04