„Web sa vlečie“ takmer vždy končí pri databáze. Kontrola zaberie pár minút, ak sa robí v správnom poradí: najprv to, čo je vidieť priamo počas spomalenia, potom to, čo sa hromadilo týždne.

Pozerať sa treba vo chvíli problému

Hlavné pravidlo: najcennejšie informácie sú dostupné práve vtedy, keď je všetko pomalé. Prvý príkaz pre MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Pre 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, čo sa práve teraz vykonáva a ako dlho. Odpoveď je obvykle vidieť hneď: jeden ťažký dotaz drží ostatné, alebo stovka rovnakých dotazov čaká na uvoľnenie zámku.

Spojenia

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

Ak sa Max_used_connections tesne blíži k max_connections, aplikácia občas dostáva odmietnutie „priveľa spojení“. Zvyšovať limit nie je prvý krok: každé spojenie zaberá pamäť a zvýšenie limitu na serveri, ktorému pamäť nestačí, len urýchli prechod do odkladacieho priestoru. Najprv je vhodné pochopiť, prečo sa spojenia neuvoľňujú: obvykle za to môže dlhý dotaz alebo chýbajúci pool na strane aplikácie.

Tu je tiež užitočné pozrieť sa na situáciu z druhej strany: prudký rast počtu spojení nemusí byť dôsledkom rastu popularity, ale hromadného prístupu botov na ťažkú stránku — vyhľadávanie, filter katalógu, generátor zostáv.

Pomalé dotazy

Bez logu pomalých dotazov sa ďalej nedá pohnúť. MySQL:

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

Za deň:

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

PostgreSQL, v postgresql.conf:

log_min_duration_statement = 1000

Ďalej rozšírenie pg_stat_statements, ktoré rovno ukáže súhrnný č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šimnite si: dôležitý nie je najdlhší dotaz, ale ten s najväčším súhrnným časom. Dotaz na 50 milisekúnd vykonávaný tisíckrát za minútu škodí viac než desaťsekundová zostava raz denne.

Nastavenie, na ktoré sa najčastejšie zabúda

Pri MySQL a MariaDB je to veľkosť bufferového poolu InnoDB. Predvolená hodnota je 128 megabajtov a nezmenila sa desaťročia. Na serveri, kde databáza zaberá niekoľko gigabajtov, to znamená neustále čítanie z disku namiesto z pamäte.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Rozumný orientačný bod pre vyhradený databázový server je polovica operačnej pamäte; pre server, kde vedľa beží webový server a PHP, štvrtina, s ohľadom na to, aby nezačalo odkladanie. Toto jediné nastavenie obvykle prinesie viac než týždeň optimalizácie dotazov.

Dve veci, ktoré nenápadne žerú disk

Binárne logy MySQL. Sú potrebné na replikáciu a obnovu k okamihu v čase, rastú nepretržite a v predvolenom stave sa občas nemažú vôbec. Kontrola:

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

Ak replikáciu nemáte a obnovu k okamihu v čase nepoužívate, doba uchovania sa nastaví na rozumných niekoľko dní.

Logy WAL v PostgreSQL pri zabudnutom replikačnom slote. Prípad menej známy a nebezpečnejší: ak bol replikačný slot vytvorený a odberateľ sa k nemu už nepripája, PostgreSQL je povinný uchovávať všetky logy od okamihu odpojenia — a bude ich uchovávať, kým nedôjde disk.

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

Neaktívny slot, ktorý nikto nepotrebuje, treba zmazať. Je to jedna z mála situácií, keď databáza zaručene dovedie disk k nule.

Veľkosť a rast

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žitočné nie je ani tak aktuálne číslo, ako rýchlosť jeho zmeny. Databáza, ktorá za mesiac vyrástla na dvojnásobok bez zodpovedajúceho rastu návštevnosti, obvykle znamená, že sa nejaká logovacia tabuľka zapisuje a nečistí — relácie, história úloh, log udalostí rozšírenia.

Bezpečnosť na dva riadky

Keď ste už otvorili konzolu databázy, oplatí sa preveriť dve veci. Prvú — či je databáza dostupná zvonka (ss -tulpn | grep 3306): von by hľadieť nemala takmer nikdy. Druhú — účty s prístupom z ľubovoľnej adresy:

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

Záznamy s host = '%' znamenajú „pripájať sa možno odkiaľkoľvek“. Ak je pritom port otvorený, medzi databázou a internetom stojí len heslo.

Zálohy

Posledné, čo stojí za zmienku. Existencia súboru zálohy nezaručuje nič — zaručuje len overená obnova. Dump, ktorý nikdy nikto nerozbaľoval, môže byť s rovnakou pravdepodobnosťou useknutý, vyhotovený s chybou zámku alebo obsahovať inú databázu. Overovať to treba vopred a na samostatnom stroji, nie v deň, keď je kópia potrebná.

Na bežné sledovanie stačia tri čísla: počet spojení, počet pomalých dotazov za deň a veľkosť databázy. Všetky tri sa menia pomaly a predvídateľne a akákoľvek prudká zmena stojí za pohľad. Ako vyzerajú na jednej stránke, ukazuje ukážka nižšie.