Wprowadzenie do pg_stat_statements: instalacja i konfiguracja rozszerzenia

Rozszerzenie pg_stat_statements w PostgreSQL to narzędzie do zbierania statystyk zapytań. Pozwala zobaczyć, które zapytania są wykonywane najczęściej, które zajmują najwięcej czasu i jak efektywnie wykorzystywane są zasoby bazy danych. Zamiast analizować każde zapytanie ręcznie przy pomocy EXPLAIN, możemy ogarnąć ogólny obraz wydajności bazy danych.

Zalety używania pg_stat_statements:

Monitoring w czasie rzeczywistym: możesz zobaczyć, które zapytania właśnie teraz obciążają bazę danych.

Analiza wydajności całego systemu: info jest dostępne dla wszystkich zapytań w bazie, a nie tylko tych, które postanowisz analizować ręcznie.

Wyszukiwanie wolnych zapytań: łatwo sprawdzić, które zapytania zajmują najwięcej czasu.

Wykrywanie powtarzających się zapytań: pozwala zoptymalizować cache i dodać indeksy do popularnych zapytań.

Instalacja i konfiguracja pg_stat_statements

Skoro już wiesz, po co jest pg_stat_statements, zobaczmy krok po kroku, jak je zainstalować i skonfigurować.

1. Sprawdzamy gotowość PostgreSQL. Upewnij się, że twój PostgreSQL obsługuje rozszerzenie pg_stat_statements. To rozszerzenie jest w standardzie od PostgreSQL 9.2. Żeby sprawdzić, czy jest dostępne, wykonaj:

SELECT extname FROM pg_extension;

Jeśli pg_stat_statements nie ma na liście, to znaczy, że admin go nie zainstalował.

Tak powinno wyglądać zainstalowane i aktywowane rozszerzenie:

2. Dodanie rozszerzenia.

Żeby aktywować pg_stat_statements, trzeba dodać je do listy bibliotek ładowanych przy starcie PostgreSQL. Robi się to w pliku konfiguracyjnym postgresql.conf.

Kroki:

1. Znajdź plik postgresql.conf. Zwykle jest w katalogu danych PostgreSQL.
2. Otwórz go do edycji.
3. Dodaj albo zmień linię:

   shared_preload_libraries = 'pg_stat_statements'

Po co to? Bo pg_stat_statements wymaga wcześniejszego załadowania, bo śledzi zapytania na poziomie systemu.

4. Zapisz zmiany i zrestartuj serwer PostgreSQL, żeby aktywować zmiany. Poniżej komenda dla Linuxa:

   sudo systemctl restart postgresql
   

Jeśli kodzisz albo testujesz lokalnie, zwykły restart serwera też załatwi sprawę.

3. Tworzenie rozszerzenia w bazie danych. Jak już serwer PostgreSQL został zrestartowany, możemy utworzyć rozszerzenie pg_stat_statements w konkretnej bazie danych. Połącz się z wybraną bazą przez psql albo inny tool i wykonaj:

CREATE EXTENSION pg_stat_statements;

Jeśli wszystko poszło OK, komenda zakończy się bez błędów. Teraz pg_stat_statements jest aktywne dla twojej bazy.

4. Konfiguracja parametrów pg_stat_statements.

Po instalacji rozszerzenia warto ustawić parametry jego działania, żeby dobrze zbierało statystyki. Najważniejsze parametry ustawiasz w pliku postgresql.conf.

Podstawowe parametry

• pg_stat_statements.track
• Określa, które zapytania będą śledzone.
• Wartości:
  • all — śledź wszystkie zapytania (zalecane do debugowania i analizy).
  • top — śledź tylko zapytania najwyższego poziomu.
  • none — wyłącz śledzenie.
• Przykład ustawienia:

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • Określa maksymalną liczbę zapytań, które będą trzymane w statystykach.
  • Domyślnie: 5000.
  • Jeśli masz dużo zapytań w systemie, warto zwiększyć np. do:

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • Określa, czy statystyki mają być zachowane między restartami serwera.
  • Wartości: on albo off.
  • Lepiej zostawić on:

    pg_stat_statements.save = on
    

Po zmianie parametrów znowu zrestartuj serwer PostgreSQL.

Sprawdzanie działania pg_stat_statements

Jak już rozszerzenie jest zainstalowane i skonfigurowane, sprawdźmy, czy działa. Żeby zobaczyć zebrane statystyki zapytań, wykonaj taki select:

SELECT
    queryid,        -- Unikalny identyfikator zapytania
    query,          -- Tekst zapytania
    calls,          -- Liczba wywołań zapytania
    total_time,     -- Całkowity czas wykonania (w milisekundach)
    rows            -- Liczba wierszy zwróconych przez zapytanie
FROM pg_stat_statements
ORDER BY total_time DESC;

Co znaczą kolumny?

• queryid: unikalny identyfikator zapytania, przydatny do szukania takich samych zapytań z różnymi parametrami.
• query: tekst SQL zapytania, które było wykonane.
• calls: ile razy zapytanie było wywołane.
• total_time: całkowity czas (suma czasu wszystkich wywołań zapytania).
• rows: liczba wierszy zwróconych przez zapytanie.

Na przykład, jeśli widzisz, że zapytanie z calls = 100 i total_time = 50000 (50 sekund) zajmuje większość czasu w systemie, to jasny sygnał, że trzeba je zoptymalizować.

Typowe scenariusze użycia pg_stat_statements

1. Wyszukiwanie najwolniejszych zapytań. Żeby znaleźć zapytania, które zajmują najwięcej czasu, posortuj wyniki po total_time:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

2. Wykrywanie najbardziej aktywnych zapytań. Żeby znaleźć zapytania, które są wykonywane najczęściej, posortuj po calls:

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. Analiza użycia indeksów. Jeśli widzisz dużo wolnych zapytań, sprawdź użycie indeksów. Na przykład, w zapytaniach z filtrowaniem (WHERE) brak indeksu często powoduje słabą wydajność.

Czyszczenie danych pg_stat_statements

Czasem możesz chcieć wyczyścić zebrane statystyki, żeby zacząć analizę od zera. Możesz to zrobić komendą:

SELECT pg_stat_statements_reset();

Po resecie wszystkie statystyki zostaną wyczyszczone i zbieranie danych zacznie się od nowa.

Praktyczne porady

Ograniczaj ilość zbieranych statystyk: jeśli pracujesz na mocno obciążonym systemie z milionami zapytań, zostaw pg_stat_statements.max na rozsądnym poziomie, żeby nie obciążać niepotrzebnie bazy.

Regularnie czyść statystyki: warto to robić przed analizą wydajności, żeby nie mieszać starych i nowych danych.

Zwracaj uwagę na wolne zapytania: nawet jeśli są rzadko wykonywane, pojedyncze wolne zapytanie może mocno obciążyć twoją bazę.

Teraz już wiesz, jak zainstalować, skonfigurować i używać rozszerzenia pg_stat_statements do analizy wydajności zapytań. W następnym wykładzie pogłębimy temat wyszukiwania wolnych zapytań i optymalizacji ich działania.