„Web se vleče“ téměř vždy končí u databáze. Kontrola zabere pár minut, dělá-li se ve správném pořadí: nejdřív to, co je vidět přímo během zpomalení, potom to, co se hromadilo týdny.

Dívat se je třeba ve chvíli problému

Hlavní pravidlo: nejcennější informace jsou dostupné právě tehdy, když je vše pomalé. První příkaz pro MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Pro 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;"

Uvidíte, co se právě teď provádí a jak dlouho. Odpověď je obvykle vidět hned: jeden těžký dotaz drží ostatní, nebo stovka stejných dotazů čeká na uvolnění zámku.

Spojení

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

Pokud se Max_used_connections těsně blíží k max_connections, aplikace občas dostává odmítnutí „příliš mnoho spojení“. Zvyšovat limit není první krok: každé spojení zabírá paměť a zvýšení limitu na serveru, kterému paměť nestačí, jen urychlí přechod do odkládacího prostoru. Nejdřív je vhodné pochopit, proč se spojení neuvolňují: obvykle za to může dlouhý dotaz nebo chybějící pool na straně aplikace.

Zde je také užitečné podívat se na situaci z druhé strany: prudký růst počtu spojení nemusí být důsledkem růstu popularity, ale hromadného přístupu botů na těžkou stránku — vyhledávání, filtr katalogu, generátor sestav.

Pomalé dotazy

Bez logu pomalých dotazů se dál nelze pohnout. MySQL:

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

Za den:

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

PostgreSQL, v postgresql.conf:

log_min_duration_statement = 1000

Dále rozšíření pg_stat_statements, které rovnou ukáže souhrnný čas každého dotazu:

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

Všimněte si: důležitý není nejdelší dotaz, ale ten s největším souhrnným časem. Dotaz na 50 milisekund prováděný tisíckrát za minutu škodí víc než desetivteřinová sestava jednou denně.

Nastavení, na které se nejčastěji zapomíná

U MySQL a MariaDB je to velikost bufferového poolu InnoDB. Výchozí hodnota je 128 megabajtů a nezměnila se desítky let. Na serveru, kde databáze zabírá několik gigabajtů, to znamená neustálé čtení z disku místo z paměti.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Rozumný orientační bod pro vyhrazený databázový server je polovina operační paměti; pro server, kde vedle běží webový server a PHP, čtvrtina, s ohledem na to, aby nezačalo odkládání. Toto jediné nastavení obvykle přinese víc než týden optimalizace dotazů.

Dvě věci, které nenápadně žerou disk

Binární logy MySQL. Jsou potřeba pro replikaci a obnovu k okamžiku v čase, rostou nepřetržitě a ve výchozím stavu se občas nemažou vůbec. Kontrola:

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

Pokud replikaci nemáte a obnovu k okamžiku v čase nepoužíváte, doba uchování se nastaví na rozumných několik dní.

Logy WAL v PostgreSQL při zapomenutém replikačním slotu. Případ méně známý a nebezpečnější: pokud byl replikační slot vytvořen a odběratel se k němu už nepřipojuje, PostgreSQL je povinen uchovávat všechny logy od okamžiku odpojení — a bude je uchovávat, dokud nedojde disk.

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

Neaktivní slot, který nikdo nepotřebuje, je třeba smazat. Je to jedna z mála situací, kdy databáze zaručeně dovede disk k nule.

Velikost a růst

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;

Užitečné není ani tak aktuální číslo, jako rychlost jeho změny. Databáze, která za měsíc vyrostla na dvojnásobek bez odpovídajícího růstu návštěvnosti, obvykle znamená, že se nějaká logovací tabulka zapisuje a nečistí — sezení, historie úloh, log událostí rozšíření.

Bezpečnost na dva řádky

Když už jste otevřeli konzoli databáze, stojí za to prověřit dvě věci. První — zda je databáze dostupná zvenčí (ss -tulpn | grep 3306): ven by hledět neměla téměř nikdy. Druhá — účty s přístupem z libovolné adresy:

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

Záznamy s host = '%' znamenají „připojovat se lze odkudkoli“. Je-li přitom port otevřený, mezi databází a internetem stojí jen heslo.

Zálohy

Poslední, co stojí za zmínku. Existence souboru zálohy nezaručuje nic — zaručuje jen ověřená obnova. Dump, který nikdy nikdo nerozbaloval, může být se stejnou pravděpodobností useknutý, pořízený s chybou zámku nebo obsahovat jinou databázi. Ověřovat je to třeba předem a na samostatném stroji, ne v den, kdy je kopie potřeba.

K běžnému sledování stačí tři čísla: počet spojení, počet pomalých dotazů za den a velikost databáze. Všechna tři se mění pomalu a předvídatelně a jakákoli prudká změna stojí za pohled. Jak vypadají na jedné stránce, ukazuje ukázka níže.