Site-ul merge greu: ce te uiți în MySQL și PostgreSQL

„Site-ul merge greu” ajunge aproape întotdeauna la baza de date. Verificarea durează câteva minute dacă este făcută în ordinea corectă: mai întâi ce se vede chiar în timpul încetinirii, apoi ce s-a acumulat în săptămâni.

Trebuie privit în momentul problemei

Regula principală: cea mai valoroasă informație este disponibilă exact atunci când totul este lent. Prima comandă pentru MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

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

Vei vedea ce se execută chiar acum și de cât timp. De obicei răspunsul se vede imediat: o interogare grea le ține pe celelalte, sau o sută de interogări identice așteaptă eliberarea unui blocaj.

Conexiuni

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

Dacă Max_used_connections se apropie strâns de max_connections, aplicația primește periodic refuzul „prea multe conexiuni”. Ridicarea limitei nu este primul lucru de făcut: fiecare conexiune ocupă memorie, iar creșterea limitei pe un server căruia deja nu îi ajunge memoria doar grăbește trecerea la spațiul de interschimb. Mai întâi merită înțeles de ce nu se eliberează conexiunile: de obicei vina este o interogare lungă sau absența unui pool în aplicație.

Tot aici este util să privești situația din cealaltă parte: o creștere bruscă a numărului de conexiuni poate fi urmarea nu a creșterii popularității, ci a accesării în masă de către boți a unei pagini grele — căutarea, filtrul de catalog, generatorul de rapoarte.

Interogări lente

Fără jurnalul interogărilor lente nu se poate merge mai departe. MySQL:

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

După o zi:

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

PostgreSQL, în postgresql.conf:

log_min_duration_statement = 1000

Mai departe, extensia pg_stat_statements, care arată direct timpul cumulat al fiecărei interogări:

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

Atenție: importantă nu este interogarea cea mai lungă, ci cea cu timpul cumulat cel mai mare. O interogare de 50 de milisecunde executată de o mie de ori pe minut face mai mult rău decât un raport de zece secunde o dată pe zi.

Setarea care se uită cel mai des

Pentru MySQL și MariaDB este dimensiunea bufferului InnoDB. Valoarea implicită este de 128 de megaocteți și nu s-a schimbat de decenii. Pe un server unde baza ocupă câțiva gigaocteți, asta înseamnă citire permanentă de pe disc în loc de memorie.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Un reper rezonabil pentru un server dedicat bazelor de date este jumătate din memoria RAM; pentru un server pe care lucrează alături serverul web și PHP — un sfert, cu grijă să nu înceapă interschimbul. Această singură setare aduce de obicei mai mult decât o săptămână de optimizare a interogărilor.

Două lucruri care mănâncă discul pe nesimțite

Jurnalele binare MySQL. Sunt necesare pentru replicare și pentru restaurarea la un moment de timp, cresc permanent și, implicit, uneori nu se șterg deloc. Verificare:

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

Dacă nu ai replicare și nu folosești restaurarea la un moment de timp, durata de păstrare se stabilește la câteva zile rezonabile.

Jurnalele WAL în PostgreSQL la un slot de replicare uitat. Un caz mai puțin cunoscut și mai periculos: dacă un slot de replicare a fost creat, iar consumatorul nu se mai conectează la el, PostgreSQL este obligat să păstreze toate jurnalele din momentul deconectării — și le va păstra până se termină discul.

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

Un slot inactiv de care nu are nevoie nimeni trebuie șters. Este una dintre puținele situații în care o bază de date duce garantat discul la zero.

Dimensiune și creștere

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;

Utilă nu este atât cifra curentă, cât viteza schimbării ei. O bază care s-a dublat într-o lună fără o creștere corespunzătoare a traficului înseamnă de obicei că se scrie într-un tabel de jurnalizare care nu este curățat — sesiuni, istoricul sarcinilor, jurnalul de evenimente al unei extensii.

Securitate în două linii

Dacă tot ai deschis consola bazei de date, merită verificate două lucruri. Primul — dacă baza este accesibilă din exterior (ss -tulpn | grep 3306): în afară nu ar trebui să privească aproape niciodată. Al doilea — conturile cu acces de la orice adresă:

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

Înregistrările cu host = '%' înseamnă „conectarea se poate face de oriunde”. Dacă și portul este deschis, între bază și internet stă doar o parolă.

Copii de rezervă

Ultimul lucru care merită spus. Existența unui fișier de copie de rezervă nu garantează nimic — garantează doar o restaurare verificată. Un dump care nu a fost niciodată restaurat poate la fel de bine să fie trunchiat, făcut cu o eroare de blocare sau să conțină altă bază. Asta trebuie verificat din timp și pe o mașină separată, nu în ziua în care copia devine necesară.

Pentru supravegherea zilnică sunt suficiente trei cifre: numărul de conexiuni, numărul de interogări lente pe zi și dimensiunea bazei. Toate trei se schimbă lent și previzibil, iar orice schimbare bruscă merită o privire. Cum arată ele pe o singură pagină arată demonstrația de mai jos.