Logowanie wykonywania zapytań

Jak w każdej złożonej aplikacji, w PostgreSQL zdarzają się sytuacje, kiedy coś idzie nie tak: zapytania wykonują się zbyt długo, obciążenie serwera rośnie, a użytkownicy zaczynają się denerwować. Logowanie — to sposób na podglądanie, co dzieje się w środku bazy danych, żeby:

1. Wykryć wolne zapytania — te, które spowalniają działanie aplikacji.
2. Optymalizować wydajność — przez analizę zachowania zapytań.
3. Diagnozować błędy — takie jak naruszenia ograniczeń czy zły składnik.
4. Zapewnić audyt — śledzenie, kto i jakie operacje wykonywał na serwerze.

Kluczowe parametry logowania

Pliki konfiguracyjne PostgreSQL (postgresql.conf) mają mnóstwo parametrów związanych z logowaniem. Zobaczmy te najważniejsze.

1. log_statement — logowanie zapytań SQL

Parametr log_statement określa, które zapytania SQL będą zapisywane do loga. Może przyjmować następujące wartości:

• none — wyłącza logowanie zapytań.
• ddl — loguje tylko polecenia definiujące dane (np. CREATE, ALTER, DROP).
• mod — loguje wszystkie polecenia zmieniające dane (np. INSERT, UPDATE, DELETE).
• all — loguje wszystkie zapytania SQL (w tym zwykłe SELECT).

Przykład:

Żeby włączyć logowanie wszystkich zapytań, w pliku postgresql.conf ustaw:

log_statement = 'all'

Jeśli chcesz logować tylko polecenia zmieniające dane:

log_statement = 'mod'

Po zmianie konfiguracji nie zapomnij zrestartować serwera:

sudo systemctl restart postgresql

2. log_duration — logowanie czasu wykonania

Parametr log_duration pozwala zapisywać w logu czas wykonania każdego zapytania. To przydatne, żeby sprawdzić, które zapytania są najbardziej czasochłonne.

Przykład:

Żeby włączyć logowanie czasu wykonania zapytań, ustaw:

log_duration = on

Jeśli chcesz logować tylko wolne zapytania, użyj parametru log_min_duration_statement (omówimy go za chwilę).

3. log_min_duration_statement — logowanie wolnych zapytań

Ten parametr pozwala zapisywać w logu tylko te zapytania, których wykonanie trwa dłużej niż podany czas (w milisekundach). Mega wygodne do wykrywania "zamuleń" w bazie.

Przykład:

Żeby logować zapytania, które trwają dłużej niż 1 sekundę:

log_min_duration_statement = 1000

Jeśli chcesz logować WSZYSTKIE zapytania niezależnie od czasu, po prostu ustaw wartość 0:

log_min_duration_statement = 0

Wartość -1 wyłącza logowanie według czasu trwania.

4. log_line_prefix — struktura wiadomości w logu

Parametr log_line_prefix pozwala ustawić format zapisywanych wiadomości. To przydatne, żeby dodać kontekst do każdego logowanego zapytania (np. nazwa użytkownika, PID, data i czas).

Przykład:

Żeby zapisywać nazwę użytkownika, bazę danych, czas i proces, użyj:

log_line_prefix = '%t [%p]: [%d]: [%u]: '

Tutaj:

• %t — czas zapytania.
• %p — PID procesu.
• %d — nazwa bazy danych.
• %u — nazwa użytkownika.

Lista wszystkich dostępnych opcji: Dokumentacja PostgreSQL.

5. logging_collector — kolektor logów

Parametr logging_collector włącza mechanizm zapisywania logów do plików. Jeśli jest wyłączony, logi lecą tylko na standardowe wyjście (stdout), co może być niewygodne.

Żeby aktywować kolektor logów:

logging_collector = on

Nie zapomnij ustawić ścieżki do pliku loga przez log_directory i log_filename:

log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'

Praktyczne użycie parametrów

Zobaczmy, jak używać parametrów logowania w realnym scenariuszu.

Scenariusz 1: logowanie wszystkich zapytań

Jeśli dopiero zaczynasz pracę z nową bazą i chcesz widzieć wszystko, co się dzieje, ustaw:

log_statement = 'all'
log_line_prefix = '%t [%p]: [%d]: [%u]: '

Teraz każde zapytanie wykonane w bazie będzie zapisane do pliku loga — mega wygodne do debugowania!

Scenariusz 2: szukanie wolnych zapytań

Jeśli zauważyłeś, że serwer czasem "muli", i chcesz znaleźć problematyczne zapytania, ustaw:

log_min_duration_statement = 500  # Logujemy zapytania dłuższe niż 500 ms
log_line_prefix = '%t [%p]: [%d]: [%u]: [%r] '

Gdzie %r dodaje adres IP klienta. Po tej konfiguracji łatwo wyłapiesz zapytania, które przeciążają serwer.

Scenariusz 3: tryb minimalnego logowania

Jeśli twój serwer działa stabilnie, ale potrzebujesz minimalnego logowania do audytu:

log_statement = 'mod'
log_duration = off

Tutaj logowane są tylko zmiany danych (np. INSERT, UPDATE).

Analiza logów

Kiedy logi zaczynają się zapełniać danymi, ważne jest, żeby umieć je analizować! Możesz:

Otworzyć plik z logami w edytorze tekstu:

cat /var/log/postgresql/postgresql.log

Użyć narzędzi do analizy, np. grep, żeby znaleźć wolne zapytania:

grep "duration: " /var/log/postgresql/postgresql.log

Użyć narzędzi do zaawansowanej analizy logów, takich jak pgBadger.

Przydatne porady

Zbyt szczegółowe logowanie może zwiększyć obciążenie dysku. Na produkcji używaj tylko tych parametrów, które są naprawdę potrzebne.

Regularnie czyść lub archiwizuj stare pliki logów, żeby nie zapełnić dysku.

Logowanie jest skuteczne z parametrami, które zbierają tylko potrzebne dane. Na przykład log_min_duration_statement pozwala być precyzyjnym i oszczędzać zasoby.

No i co? Teraz już nie mamy problemu z konfiguracją logowania w PostgreSQL i skutecznym użyciem go do analizy zapytań i podnoszenia wydajności. Jak powiedział jeden (a może nie jeden) nieznany, ale na pewno dobry programista: "Logi — to nasza szansa spojrzeć w przeszłość zapytań i naprawić przyszłość bazy danych".