PostgreSQL Performance Tuning dla szybszego wykonywania zapytania

Cel

Naszym celem jest sprawienie, aby wykonywanie zapytań działało szybciej w bazie danych PostgreSQL za pomocą tylko dostępnych narzędzi wbudowanych
W bazie danych.

Wersje systemu operacyjnego i oprogramowania

• System operacyjny: Red Hat Enterprise Linux 7.5
• Oprogramowanie: PostgreSQL Server 9.2

Wymagania

PostgreSQL Server Base instaluj i uruchomiony. Dostęp do narzędzia wiersza poleceń PSQL oraz własność przykładowej bazy danych.

Konwencje

• # - Wymaga, aby podane polecenia Linux są wykonywane z uprawnieniami root bezpośrednio jako użytkownik root lub za pomocą sudo Komenda
• $ - Biorąc pod uwagę polecenia Linux, które mają być wykonywane jako zwykły użytkownik niepewny

Wstęp

PostgreSQL to niezawodna baza danych open source dostępna w repozytorium wielu nowoczesnych dystrybucji. Łatwość użycia, zdolność do używania rozszerzeń i stabilność, jaką zapewnia wszystko, co dodaje do popularności.
Zapewniając podstawową funkcjonalność, takie jak odpowiadanie na zapytania SQL, konsekwentnie przechowuje wkładane dane, obsługę transakcji itp. Większość dojrzałych rozwiązań bazy danych zawiera narzędzia i know-hows, jak to zrobić
Dostosuj bazę danych, zidentyfikuj możliwe wąskie gardła i być w stanie rozwiązać problemy z wydajnością, w których system zasilany przez dane rozwiązanie rośnie.

PostgreSQL nie jest wyjątkiem i w tym
Przewodnik użyjemy wbudowanego narzędzia wyjaśnić Aby szybciej zapytań powolne zapytanie. Jest daleka od bazy danych w świecie rzeczywistym, ale można podjąć wskazówkę na temat wykorzystania wbudowanych narzędzi. Użyjemy serwera PostgreSQL w wersji 9.2 na Red Hat Linux 7.5, ale narzędzia pokazane w tym przewodniku są również obecne w znacznie starszych wersjach bazy danych i systemu operacyjnego.

Problem do rozwiązania

Rozważ tę prostą tabelę (nazwy kolumn są oczywiste):

foobardb =# \ D+ Tabela pracowników „publiczne.Kolumna Pracowników ------------------------------------------+--- " RegClass) | Main | | First_name | Tekst | Nie zerowy | rozszerzony | | Last_name | Tekst | Nie zerowy | rozszerzony | | Birth_year | Numer | nie zerowy | główny | | Birth_month | Numer | Numer | Główny | Not Null | Main | | Indeks: „Employe_pkey” Key, Btree (EMP_ID) ma OIDS: Nie 

Z płytami takimi jak:

foobardb =# Wybierz * z limitu pracowników 2; emp_id | First_name | Last_name | Birth_year | Birth_month | Birth_dayofmonth --------+------------+-----------+------------+- -----------+------------------ 1 | Emily | James | 1983 | 3 | 20 2 | John | Smith | 1990 | 8 | 12 

W tym przykładzie jesteśmy fajną firmą i wdrożyliśmy aplikację o nazwie HBAPP, która wysyła e -mail „Wszystkiego najlepszego” do pracownika w jego/jej urodziny. Aplikacja każdego ranka pyta bazę danych, aby znaleźć odbiorców na dzień (przed godzinami pracy nie chcemy zabić naszej bazy danych HR z życzliwości).
Aplikacja uruchamia następujące zapytanie, aby znaleźć odbiorców:

foobardb =# Wybierz emp_id, pierwszą nazwę_name, last_name od pracowników, w których Birth_month = 3 i Birth_dayofonth = 20; emp_id | First_name | Last_name --------+------------+----------- 1 | Emily | James 

Wszystkie działa dobrze, użytkownicy otrzymują pocztę. Wiele innych aplikacji korzysta z bazy danych, a pracownicy są w tabeli, takich jak rachunkowość i bi. Ładna firma rośnie i więc rośnie stół pracowników. Z czasem aplikacja działa zbyt długo, a wykonanie pokrywa się z rozpoczęciem godzin pracy, w wyniku czego powolny czas reakcji bazy danych w aplikacjach krytycznych misji. Musimy coś zrobić, aby to zapytanie działało szybciej, w przeciwnym razie aplikacja nie zostanie wdrożona, a wraz z nią w ładnym towarzystwie będzie mniej uprzejmości.

W tym przykładzie nie będziemy używać żadnych zaawansowanych narzędzi do rozwiązania problemu, tylko jeden dostarczony przez instalację podstawową. Zobaczmy, jak planista bazy danych wykonuje zapytanie z wyjaśnić.

Nie testujemy w produkcji; Tworzymy bazę danych do testowania, tworzymy tabelę i wstawiamy do niej dwóch pracowników wspomnianych powyżej. Używamy tych samych wartości dla zapytania przez cały czas w tym samouczku,
Więc przy każdym biegu tylko jeden rekord będzie pasował do zapytania: Emily James. Następnie uruchamiamy zapytanie z poprzednim Wyjaśnij analizę Aby zobaczyć, jak jest wykonywany z minimalnymi danymi w tabeli:

foobardb =# Wyjaśnij Analiza Wybierz emp_id, pierwszą nazwę_name, last_name od pracowników, w których Birth_month = 3 i Birth_dayofonth = 20; Plan zapytania ------------------------------------------------ -------------------------------------------------- --- SKAN SEQ na pracowników (koszt = 0.00… 15.40 wierszy = 1 szerokość = 96) (czasowy czas = 0.023… 0.025 wierszy = 1 pętle = 1) Filtr: ((Birth_month = 3 :: Numer) i (Birth_dayofmonth = 20 :: numeryczne)) Rzędy usunięte przez filtr: 1 całkowity czas wykonania: 0.076 MS (4 wiersze) 

To jest naprawdę szybkie. Być może tak szybko, jak wtedy, gdy firma po raz pierwszy wdrożyła HBAPP. Naśladujmy stan obecnej produkcji foobardb Ładując tylu (fałszywych) pracowników do bazy danych, ile mamy w produkcji (uwaga: potrzebujemy tego samego rozmiaru pamięci w testowej bazie danych jak w produkcji).

Po prostu użyjemy Bash do wypełnienia testowej bazy danych (zakładając, że mamy 500.000 pracowników w produkcji):

$ dla j in 1… 500000; Echo „Wstaw do pracowników (nazwa pierwszej, nazwa_nastum, Birth_year, Birth_month, Birth_dayofonth) Wartości („ User $ j ”,„ test ”, 1900,01,01);”; gotowe | PSQL -D foobardb 

Teraz mamy 500002 pracowników:

foobardb =# Wybierz liczbę (*) od pracowników; Liczba -------- 500002 (1 rząd) 

Uruchommy ponownie zapytanie:

foobardb =# Wyjaśnij Analiza Wybierz emp_id, pierwszą nazwę_name, last_name od pracowników, w których Birth_month = 3 i Birth_dayofonth = 20; Plan zapytania ------------------------------------------------ -------------------------------------------------- -------- SKAN SEQ na pracowników (koszt = 0.00… 11667.63 wiersze = 1 szerokość = 22) (czasowy czas = 0.012… 150.998 wierszy = 1 pętle = 1) Filtr: ((Birth_month = 3 :: Numer) i (Birth_dayofmonth = 20 :: numeryczne)) Rzędy usunięte przez filtr: 500001 Całkowity czas wykonywania: 151.059 MS 

Wciąż mamy tylko jeden mecz, ale zapytanie jest znacznie wolniejsze. Powinniśmy zauważyć pierwszy węzeł planera: SKAN SEQ który oznacza sekwencyjny skan - baza danych odczytuje całość
Tabela, podczas gdy potrzebujemy tylko jednego rekordu, jak Grep by się w grzmotnąć. W rzeczywistości może być faktycznie wolniejsze niż grep. Jeśli wyeksportujemy tabelę do nazywanego pliku CSV /tmp/exp500k.CSV:

 foobardb =# kopiuj pracowników do '/tmp/exp500k.CSV „Delimiter”, „nagłówek CSV; Kopia 500002 

I grep potrzebne informacje (szukamy 20. dnia trzeciego miesiąca, ostatnie dwie wartości w pliku CSV w każdym
linia):

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

To jest buforowanie na bok, uznane za wolniejsze i wolniejsze wraz ze wzrostem stolika.

Rozwiązanie jest z przyczyn indeksowania. Żaden pracownik nie może mieć więcej niż jednej daty urodzenia, który składa się dokładnie z jednego rok urodzenia, miesiąc urodzenia I Birth_dayofmonth - Więc te trzy pola zapewniają unikalną wartość dla tego konkretnego użytkownika. A użytkownik jest identyfikowany przez jego/jej emp_id (W firmie może być więcej niż jeden pracownicy o tej samej nazwie). Jeśli ogłosimy ograniczenie tych czterech pól, zostanie również utworzony indeks niejawny:

foobardb =# alter Table Pracownicy Dodaj ograniczenie Birth_Uniq unikalne (emp_id, narodziny_year, nr month, narodziny_dayofmonth); UWAGA: Zmień tabelę / Dodaj unikalne utworzy indeks niejawny „Birth_uniq” dla tabeli „pracowników” 

Więc otrzymaliśmy indeks dla czterech pola, zobaczmy, jak działa nasze zapytanie:

foobardb =# Wyjaśnij Analiza Wybierz emp_id, pierwszą nazwę_name, last_name od pracowników, w których Birth_month = 3 i Birth_dayofonth = 20; Plan zapytania ------------------------------------------------ -------------------------------------------------- ---------- Skan SEQ na pracowników (koszt = 0.00… 11667.19 wierszy = 1 szerokość = 22) (rzeczywisty czas = 103.131… 151.084 Rzny = 1 pętle = 1) Filtr: ((Birth_month = 3 :: Numer) i (Birth_dayofmonth = 20 :: numeryczne)) Rzędy usunięte przez filtr: 500001 Całkowity czas wykonywania: 151.103 ms (4 wiersze) 

Jest to identyczne z ostatnim i widzimy, że plan jest taki sam, indeks nie jest używany. Utwórzmy kolejny indeks przez unikalne ograniczenie emp_id, miesiąc urodzenia I Birth_dayofmonth Tylko (w końcu nie pytamy rok urodzenia W HBApp):

foobardb =# alter Table Pracownicy Dodaj ograniczenie Birth_Uniq_m_dom unikalne (emp_id, narodziny_month, Birth_dayofmonth); UWAGA: Zmień tabelę / Dodaj unikalne utworzy indeks niejawny „Birth_uniq_m_dom” dla tabeli „Pracownicy” 

Zobaczmy wynik naszego strojenia:

foobardb =# Wyjaśnij Analiza Wybierz emp_id, pierwszą nazwę_name, last_name od pracowników, w których Birth_month = 3 i Birth_dayofonth = 20; Plan zapytania ------------------------------------------------ -------------------------------------------------- --------- SKAN SEQ na pracowników (koszt = 0.00… 11667.19 wierszy = 1 szerokość = 22) (rzeczywisty czas = 97.187… 139.858 wierszy = 1 pętle = 1) Filtr: ((Birth_month = 3 :: Numer) i (Birth_dayofmonth = 20 :: numeryczne)) Rzędy usunięte przez filtr: 500001 Całkowity czas wykonywania: 139.879 ms (4 wiersze) 

Nic. Powyższa różnica wynika z użycia pamięci podręcznej, ale plan jest taki sam. Chodźmy dalej. Następnie utworzymy kolejny indeks emp_id I miesiąc urodzenia:

foobardb =# alter Table Pracownicy Dodaj ograniczenie Birth_Uniq_m unikalne (emp_id, narodziny_month); UWAGA: Zmień tabelę / Dodaj unikalne utworzy indeks niejawnego „Birth_uniq_m” dla tabeli „Pracownicy” 

I ponownie uruchom zapytanie:

foobardb =# Wyjaśnij Analiza Wybierz emp_id, pierwszą nazwę_name, last_name od pracowników, w których Birth_month = 3 i Birth_dayofonth = 20; Plan zapytania ------------------------------------------------ -------------------------------------------------- ---------------------------- Skanowanie indeksu za pomocą Birth_Uniq_m w sprawie pracowników (koszt = 0.00… 11464.19 wierszy = 1 szerokość = 22) (rzeczywisty czas = 0.089… 95.605 wierszy = 1 pętle = 1) Indeks Cond: (Birth_month = 3 :: Numer) Filter: (Birth_dayofmonth = 20 :: numeryczny) Całkowity czas wykonania: 95.630 ms (4 wiersze) 

Powodzenie! Zapytanie jest o 40% szybciej i widzimy, że plan się zmienił: baza danych nie skanuje już całej tabeli, ale używa indeksu na miesiąc urodzenia I emp_id. Stworzyliśmy wszystkie mieszanki czterech pól, pozostaje tylko jeden. Warto spróbować:

foobardb =# alter Table Pracownicy Dodaj ograniczenie Birth_Uniq_dom unikalne (emp_id, narodziny_dayofmonth); UWAGA: Zmień tabelę / Dodaj unikalne utworzy indeks niejawny „Birth_uniq_dom” dla tabeli „Pracownicy” 

Ostatni indeks jest tworzony na polach emp_id I Birth_dayofmonth. A wynik jest:

foobardb =# Wyjaśnij Analiza Wybierz emp_id, pierwszą nazwę_name, last_name od pracowników, w których Birth_month = 3 i Birth_dayofonth = 20; Plan zapytania ------------------------------------------------ -------------------------------------------------- ------------------------------ Skanowanie indeksu za pomocą Birth_Uniq_dom w sprawie pracowników (koszt = 0.00… 11464.19 wierszy = 1 szerokość = 22) (rzeczywisty czas = 0.025… 72.394 rzędy = 1 pętle = 1) Indeks Cond: (Birth_dayofonth = 20 :: numeryczny) Filtr: (Birth_month = 3 :: Numer) Całkowity czas wykonania: 72.421 ms (4 wiersze) 

Teraz nasze zapytanie jest o około 49% szybsze, przy użyciu ostatniego (i tylko ostatniego) utworzonego indeksu. Nasza tabela i powiązane indeksy wyglądają następująco:

foobardb =# \ D+ Tabela pracowników „publiczne.Kolumna Pracowników ------------------------------------------+--- " RegClass) | Main | | First_name | Tekst | Nie zerowy | rozszerzony | | Last_name | Tekst | Nie zerowy | rozszerzony | | Birth_year | Numer | nie zerowy | główny | | Birth_month | Numer | Numer | Główny | Not NULL | Main | | Indeks: „Employe_pkey” Klucz podstawowy, Btree (emp_id) „Birth_uniq” Unikalne ograniczenie, Btree (EMP_ID, Birth_year, Birth_month, Birth_dayofmonth) „Birth_uniq_dom” Unikalne ograniczenie, Btree (EMP_ID, Birth_dayofmonth) „Birth_uniq_m” unikalne Ograniczenie, btree (emp_id, narodziny_month) „Birth_uniq_m_dom” unikalne ograniczenie, btree (emp_id, narodziny_month, narodziny_dayofmonth) ma OIDS: nie 

Nie potrzebujemy utworzonych indeksów pośrednich, plan wyraźnie stwierdza, że ​​ich nie użyje, więc upuszczamy je:

foobardb =# alter tabela pracowników upuść ograniczenie nr_uniq; Alter Table FoObardB =# alter Table Pracownicy upuść ograniczenie nr_uniq_m; Alter Table foobardb =# alter tabela pracowników upuść ograniczenie nr_uniq_m_dom; Zmień tabelę 

Ostatecznie nasza tabela zyskuje tylko jeden dodatkowy wskaźnik, który jest niski koszt za ściśle podwójną prędkość HBAPP:

foobardb =# \ D+ Tabela pracowników „publiczne.Kolumna Pracowników ------------------------------------------+--- " RegClass) | Main | | First_name | Tekst | Nie zerowy | rozszerzony | | Last_name | Tekst | Nie zerowy | rozszerzony | | Birth_year | Numer | nie zerowy | główny | | Birth_month | Numer | Numer | Główny | Not Null | Main | | Indeks: „Employe_pkey” klucz podstawowy, Btree (emp_id) „Birth_uniq_dom” Unikalne ograniczenie, Btree (emp_id, Birth_dayofmonth) ma OIDS: nie 

I możemy wprowadzić nasze strojenie do produkcji, dodając indeks, który widzieliśmy jako najbardziej przydatne:

alter Table Pracownicy Dodaj ograniczenie Birth_Uniq_dom unikalne (emp_id, narodziny_dayofmonth);

Wniosek

Nie trzeba dodawać, że to tylko manekin. Jest mało prawdopodobne, abyś przechowywał datę urodzenia pracownika w trzech oddzielnych polach, podczas gdy możesz użyć pola typu daty, umożliwiając operacje związane z datą w sposób znacznie łatwiejszy niż porównywanie wartości miesiąca i dnia jako liczby całkowite. Należy również zauważyć, że powyższe zapytania wyjaśniające nie pasują do nadmiernych testów. W scenariuszu w świecie rzeczywistym musisz przetestować wpływ nowego obiektu bazy danych na dowolną inną aplikację, która korzysta z bazy danych, a także komponentów systemu, które oddziałują z HBAPP.

Na przykład w tym przypadku, jeśli możemy przetworzyć tabelę dla odbiorców w 50% pierwotnego czasu odpowiedzi, możemy wirtualnie wyprodukować 200% e -maili na drugim końcu aplikacji (powiedzmy, że HBAPP działa w sekwencji dla sekwencji Cała 500 spółki zależnej firmy Nicea), która może skutkować szczytem obciążenia gdzie indziej - być może serwery pocztowe otrzymają wiele e -maili „Wszystkiego najlepszego”, aby przekazać tuż przed wysłanie codziennych raportów do zarządzania, co spowoduje opóźnienia dostawy. Nieco dalekie jest od rzeczywistości, że ktoś dostrajający bazę danych utworzy indeksy z niewidomymi próbami i błędem - a przynajmniej miejmy nadzieję, że tak jest w firmie zatrudniającej tak wiele osób.

Należy jednak zauważyć, że zyskliśmy 50% wzrostu wydajności na zapytaniu tylko za pomocą wbudowanego w PostgreSQL wyjaśnić funkcja zidentyfikowania pojedynczego indeksu, która może być przydatna w danej sytuacji. Pokazaliśmy również, że każda relacyjna baza danych nie jest lepsza niż jasne wyszukiwanie tekstu, jeśli nie używamy ich, ponieważ mają być używane.

Powiązane samouczki Linux:

• Rzeczy do zainstalowania na Ubuntu 20.04
• Ubuntu 20.04 Instalacja PostgreSQL
• Ubuntu 22.04 Instalacja PostgreSQL
• Wprowadzenie do automatyzacji, narzędzi i technik Linuksa
• Optymalizacja wydajności Linux: Narzędzia i techniki
• Rzeczy do zrobienia po zainstalowaniu Ubuntu 20.04 Focal Fossa Linux
• Pobierz Linux
• Linux Pliki konfiguracyjne: Top 30 Najważniejsze
• Jak przetrwać dane do PostgreSQL w Javie
• Rzeczy do zainstalowania na Ubuntu 22.04