A „lassú a webhely” szinte mindig az adatbázisnál köt ki. Az ellenőrzés néhány percet vesz igénybe, ha helyes sorrendben végzed: először azt, ami közvetlenül a lassulás idején látszik, aztán azt, ami hetek óta gyűlt.

A probléma pillanatában kell nézni

A fő szabály: a legértékesebb információ pontosan akkor érhető el, amikor minden lassú. Az első parancs MySQL-hez:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

PostgreSQL-hez:

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

Látod, mi fut éppen most, és mennyi ideje. A válasz általában azonnal látszik: egy nehéz lekérdezés tartja a többit, vagy száz egyforma lekérdezés vár egy zár feloldására.

Kapcsolatok

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

Ha a Max_used_connections szorosan megközelíti a max_connections értéket, az alkalmazás időnként „túl sok kapcsolat” elutasítást kap. A korlát emelése nem az első lépés: minden kapcsolat memóriát foglal, és a korlát emelése egy amúgy is memóriahiányos szerveren csak felgyorsítja a lapozóterületre való átmenetet. Előbb azt érdemes megérteni, miért nem szabadulnak fel a kapcsolatok: általában egy hosszú lekérdezés vagy az alkalmazásoldali pool hiánya a hibás.

Itt hasznos a helyzetet a másik oldalról is megnézni: a kapcsolatszám hirtelen növekedése nem a népszerűség növekedéséből, hanem abból is fakadhat, hogy botok tömegesen keresnek fel egy nehéz oldalt — keresést, katalógusszűrőt, jelentésgenerátort.

Lassú lekérdezések

A lassú lekérdezések naplója nélkül nem lehet továbblépni. MySQL:

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

Egy nap múlva:

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

PostgreSQL, a postgresql.conf fájlban:

log_min_duration_statement = 1000

Ezután a pg_stat_statements bővítmény, amely rögtön megmutatja az egyes lekérdezések összesített idejét:

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

Figyelj: nem a leghosszabb lekérdezés a fontos, hanem az, amelynek a legnagyobb az összesített ideje. Egy 50 ezredmásodperces lekérdezés, amely percenként ezerszer fut le, többet árt, mint egy tízmásodperces jelentés naponta egyszer.

A beállítás, amelyről a leggyakrabban megfeledkeznek

MySQL-nél és MariaDB-nél ez az InnoDB pufferkészletének mérete. Az alapérték 128 megabájt, és évtizedek óta nem változott. Egy olyan szerveren, ahol az adatbázis több gigabájt, ez azt jelenti, hogy a rendszer memória helyett folyamatosan lemezről olvas.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Észszerű támpont dedikált adatbázisszerverhez az operatív memória fele; olyan szerverhez, ahol mellette webszerver és PHP fut, a negyede, arra ügyelve, hogy ne induljon el a lapozás. Ez az egyetlen beállítás általában többet ad, mint egy hét lekérdezésoptimalizálás.

Két dolog, amely észrevétlenül eszi a lemezt

A MySQL bináris naplói. A replikációhoz és az időpontra való visszaállításhoz kellenek, folyamatosan nőnek, és alapból néha egyáltalán nem törlődnek. Ellenőrzés:

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

Ha nincs replikációd, és nem használsz időpontra való visszaállítást, a megőrzési időt állítsd észszerű néhány napra.

WAL-naplók a PostgreSQL-ben elfelejtett replikációs slot mellett. Kevésbé ismert és veszélyesebb eset: ha egy replikációs slot létrejött, és a fogyasztó már nem csatlakozik hozzá, a PostgreSQL köteles megőrizni minden naplót a lekapcsolódás pillanatától — és meg is őrzi, amíg el nem fogy a lemez.

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

Az inaktív, senkinek nem kellő slotot törölni kell. Ez azon kevés helyzetek egyike, amikor egy adatbázis garantáltan nullára viszi a lemezt.

Méret és növekedés

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;

Nem is annyira az aktuális szám hasznos, mint a változás üteme. Egy adatbázis, amely egy hónap alatt a kétszeresére nőtt a látogatottság megfelelő növekedése nélkül, általában azt jelenti, hogy valamelyik naplózó tábla íródik, és nem takarítják — munkamenetek, feladatelőzmények, egy bővítmény eseménynaplója.

Biztonság két sorban

Ha már megnyitottad az adatbázis konzolját, két dolgot érdemes ellenőrizni. Az elsőt: elérhető-e az adatbázis kívülről (ss -tulpn | grep 3306) — kifelé szinte soha nem szabadna néznie. A másodikat: a bármely címről elérhető fiókokat:

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

A host = '%' bejegyzések azt jelentik, hogy „bárhonnan lehet csatlakozni”. Ha eközben a port is nyitva van, az adatbázis és az internet között csak egy jelszó áll.

Mentések

Az utolsó dolog, amit érdemes elmondani. Egy mentésfájl megléte semmit nem garantál — csak az ellenőrzött visszaállítás garantál. Az a dump, amelyet soha nem állítottak vissza, ugyanolyan valószínűséggel lehet csonka, zárolási hibával készült, vagy nem a megfelelő adatbázist tartalmazza. Ezt előre kell ellenőrizni, külön gépen, nem azon a napon, amikor a másolatra szükség van.

A mindennapi figyeléshez három szám elég: a kapcsolatok száma, a napi lassú lekérdezések száma és az adatbázis mérete. Mindhárom lassan és kiszámíthatóan változik, és bármelyik hirtelen változása megér egy pillantást. Hogy néznek ki egyetlen oldalon, azt az alábbi bemutató mutatja.