„Witryna muli” prawie zawsze sprowadza się do bazy danych. Sprawdzenie zajmuje kilka minut, jeśli robić je we właściwej kolejności: najpierw to, co widać wprost w czasie spowolnienia, potem to, co zbierało się tygodniami.

Patrzeć trzeba w chwili problemu

Główna zasada: najcenniejsze informacje są dostępne dokładnie wtedy, gdy wszystko jest wolne. Pierwsze polecenie dla MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Dla PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

Zobaczymy, co wykonuje się właśnie teraz i jak długo. Zwykle odpowiedź widać od razu: jedno ciężkie zapytanie trzyma pozostałe albo setka identycznych zapytań czeka na zwolnienie blokady.

Połączenia

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Jeśli Max_used_connections zbliża się wprost do max_connections, aplikacja okresowo dostaje odmowę „zbyt wiele połączeń”. Podniesienie limitu nie jest pierwszą rzeczą do zrobienia: każde połączenie zajmuje pamięć, a zwiększenie limitu na serwerze, któremu jej brakuje, tylko przyspiesza przejście na swap. Najpierw warto zrozumieć, dlaczego połączenia nie są zwalniane: zwykle winne jest długie zapytanie albo brak puli po stronie aplikacji.

Tu samo warto spojrzeć na sprawę z drugiej strony: gwałtowny wzrost liczby połączeń może wynikać nie ze wzrostu popularności, lecz z masowych odwiedzin botów na ciężkiej stronie — wyszukiwarce, filtrze katalogu, generatorze raportów.

Wolne zapytania

Bez dziennika wolnych zapytań nie ma jak ruszyć dalej. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Po dobie:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, w postgresql.conf:

log_min_duration_statement = 1000

Dalej rozszerzenie pg_stat_statements, które od razu pokazuje łączny czas każdego zapytania:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Uwaga: ważne jest nie zapytanie najdłuższe, lecz to o największym czasie łącznym. Zapytanie na 50 milisekund wykonywane tysiąc razy na minutę szkodzi bardziej niż dziesięciosekundowy raport raz na dobę.

Ustawienie, o którym zapomina się najczęściej

W MySQL i MariaDB jest to rozmiar puli buforowej InnoDB. Wartość domyślna to 128 megabajtów i nie zmieniała się od dziesięcioleci. Na serwerze, gdzie baza zajmuje kilka gigabajtów, oznacza to nieustanne czytanie z dysku zamiast z pamięci.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Rozsądny punkt odniesienia dla dedykowanego serwera baz to połowa pamięci operacyjnej; dla serwera, na którym obok pracują serwer WWW i PHP — jedna czwarta, z oglądaniem się na to, żeby nie zaczął się swap. To jedno ustawienie zwykle daje więcej niż tydzień optymalizacji zapytań.

Dwie rzeczy, które niepostrzeżenie zjadają dysk

Dzienniki binarne MySQL. Są potrzebne do replikacji i odtwarzania do punktu w czasie, rosną nieprzerwanie, a domyślnie bywa, że nie są usuwane wcale. Sprawdzenie:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

Jeśli replikacji nie mamy, a odtwarzania do punktu w czasie nie używamy, okres przechowywania ustawia się na rozsądne kilka dni.

Dzienniki WAL w PostgreSQL przy zapomnianym gnieździe replikacji. Przypadek mniej znany i groźniejszy: jeśli gniazdo replikacji utworzono, a odbiorca już się do niego nie podłącza, PostgreSQL ma obowiązek przechowywać wszystkie dzienniki od chwili rozłączenia — i będzie je przechowywał, dopóki nie skończy się dysk.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

Nieaktywne gniazdo, którego nikt nie potrzebuje, trzeba usunąć. To jedna z niewielu sytuacji, w których baza danych z gwarancją doprowadza dysk do zera.

Rozmiar i wzrost

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

Przydatna jest nie tyle bieżąca liczba, ile tempo jej zmiany. Baza, która urosła dwukrotnie w miesiąc bez odpowiadającego wzrostu odwiedzin, zwykle oznacza, że jakaś tabela dziennikowa jest zapisywana i nieczyszczona — sesje, historia zadań, dziennik zdarzeń wtyczki.

Bezpieczeństwo w dwóch wierszach

Skoro już otworzyliśmy konsolę bazy, warto sprawdzić dwie rzeczy. Pierwsza — czy baza jest dostępna z zewnątrz (ss -tulpn | grep 3306): na zewnątrz nie powinna patrzeć prawie nigdy. Druga — konta z dostępem z dowolnego adresu:

mysql -e "SELECT user, host FROM mysql.user"

Wpisy z host = '%' oznaczają „podłączać się można skądkolwiek”. Jeśli przy tym port jest otwarty, między bazą a internetem mamy wyłącznie hasło.

Kopie zapasowe

Ostatnia rzecz warta powiedzenia. Istnienie pliku kopii zapasowej niczego nie gwarantuje — gwarantuje wyłącznie sprawdzone odtworzenie. Zrzut, którego nigdy nie rozwijano, z równym prawdopodobieństwem okaże się ucięty, zdjęty z błędem blokady albo zawierający nie tę bazę. Sprawdzać to trzeba z wyprzedzeniem i na osobnej maszynie, a nie w dniu, w którym kopia okazała się potrzebna.

Do codziennej obserwacji wystarczą trzy liczby: liczba połączeń, liczba wolnych zapytań na dobę i rozmiar bazy. Wszystkie trzy zmieniają się powoli i przewidywalnie, a każda gwałtowna zmiana jest warta spojrzenia. Jak wyglądają na jednej stronie, pokazuje demonstracja poniżej.