„Sajt je spor" gotovo uvek se svede na bazu podataka. Provera traje nekoliko minuta ako se radi ispravnim redom: prvo ono što se vidi tokom samog usporenja, zatim ono što se nakuplja nedeljama.

Gledajte dok se problem dešava

Glavno pravilo: najdragocenija informacija dostupna je upravo dok je sve sporo. Prva komanda za MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

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

Videćete šta radi upravo sada i koliko dugo. Obično je odgovor neposredan: jedan težak upit drži ostale, ili stotinu istovetnih upita čeka da se oslobodi zaključavanje.

Veze

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

Ako se Max_used_connections približava max_connections, aplikacija povremeno dobija odbijanje „too many connections". Podizanje ograničenja nije prvo što treba uraditi: svaka veza uzima memoriju, a podizanje na serveru kojem memorije već nedostaje samo ubrzava put u swap. Prvo utvrdite zašto se veze ne oslobađaju: obično je to dug upit ili nepostojanje bazena veza na strani aplikacije.

Vredi to pogledati i sa druge strane: nagli porast veza može doći ne od rastuće popularnosti nego od botova koji udaraju po teškoj stranici — pretrazi, filteru kataloga, generatoru izveštaja.

Spori upiti

Bez dnevnika sporih upita nema se kuda dalje. MySQL:

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

Dan kasnije:

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

PostgreSQL, u postgresql.conf:

log_min_duration_statement = 1000

Zatim proširenje pg_stat_statements, koje direktno prikazuje ukupno vreme po upitu:

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

Pažnja: nije bitan najduži upit nego onaj sa najvećim ukupnim vremenom. Upit od 50 milisekundi koji se izvršava hiljadu puta u minuti pravi više štete od desetosekundnog izveštaja jednom dnevno.

Podešavanje koje se najčešće zaboravlja

Za MySQL i MariaDB to je veličina InnoDB bafer bazena. Podrazumevano je 128 megabajta i nije se menjalo decenijama. Na serveru gde baza zauzima nekoliko gigabajta to znači stalno čitanje sa diska umesto iz memorije.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Razumna smernica za namenski server baze je polovina RAM-a; za server koji vrti i veb server i PHP, četvrtina, uz pažnju da se ne krene u swap. To jedno podešavanje obično daje više od nedelju dana optimizacije upita.

Dve stvari koje tiho jedu disk

MySQL binarni dnevnici. Potrebni su za replikaciju i vraćanje na tačku u vremenu, rastu neprekidno, a podrazumevano se ponekad nikada ne uklanjaju. Za proveru:

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

Ako nemate replikaciju i ne koristite vraćanje na tačku u vremenu, postavite zadržavanje na razumnih nekoliko dana.

PostgreSQL WAL fajlovi sa zaboravljenim slotom replikacije. Manje poznat i opasniji slučaj: ako slot replikacije postoji ali se njegov potrošač više ne povezuje, PostgreSQL je dužan da čuva svaki dnevnik od trenutka prekida — i čuvaće ih dok disk ne nestane.

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

Neaktivan slot koji nikome ne treba mora se ukloniti. To je jedna od retkih situacija u kojima baza podataka zagarantovano dovodi disk na nulu.

Veličina i 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;

Korisna nije toliko trenutna brojka koliko brzina kojom se menja. Baza koja se udvostručila za mesec dana bez odgovarajućeg porasta saobraćaja obično znači da se neka tabela za beleženje upisuje i nikada ne čisti — sesije, istorija poslova, dnevnik događaja nekog dodatka.

Bezbednost u dva reda

Kad već imate otvorenu konzolu baze, vredi proveriti dve stvari. Prvo, da li je baza dostupna spolja (ss -tulpn | grep 3306): gotovo nikada ne bi trebalo da gleda napolje. Drugo, naloge koji se mogu povezati sa bilo koje adrese:

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

Unosi sa host = '%' znače „veze odasvud". Ako je i port otvoren, lozinka je jedino što stoji između baze i interneta.

Rezervne kopije

Još jedna stvar na kraju. Postojanje fajla rezervne kopije ne garantuje ništa; garantuje samo isprobano vraćanje. Kopija koja nikada nije vraćana ima podjednake izglede da ispadne skraćena, napravljena sa greškom zaključavanja ili da sadrži pogrešnu bazu. To se proverava unapred i na zasebnoj mašini, a ne onog dana kada kopija zatreba.

Za svakodnevno praćenje dovoljne su tri brojke: broj veza, broj sporih upita dnevno i veličina baze. Sve tri menjaju se sporo i predvidivo, a svaki nagli pomak jedne od njih vredi pogledati. Kako izgledaju na jednoj stranici pokazuje demo ispod.