«De site is traag» komt vrijwel altijd bij de database uit. De controle kost een paar minuten als u haar in de juiste volgorde doet: eerst wat tijdens de vertraging zelf zichtbaar is, daarna wat zich weken heeft opgehoopt.

Kijk terwijl het probleem zich voordoet

De hoofdregel: de waardevolste gegevens zijn juist beschikbaar terwijl alles traag is. Het eerste commando voor MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

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

U ziet wat er nu draait en hoe lang al. Het antwoord is doorgaans meteen duidelijk: één zware query houdt de rest op, of honderd gelijke query's wachten tot een vergrendeling vrijkomt.

De verbindingen

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

Komt Max_used_connections dicht bij max_connections, dan krijgt de toepassing af en toe de weigering «te veel verbindingen». De limiet verhogen is niet het eerste wat u doet: elke verbinding kost geheugen, en verhogen op een server die al geheugen tekortkomt versnelt alleen de gang naar swap. Zoek eerst uit waarom verbindingen niet worden vrijgegeven: doorgaans een lange query of het ontbreken van een pool aan de kant van de toepassing.

Het loont dezelfde zaak van de andere kant te bekijken: een scherpe stijging van het aantal verbindingen kan niet uit groeiende populariteit voortkomen maar uit bots die een zware pagina bestoken — een zoekfunctie, een catalogusfilter, een rapportgenerator.

De trage query's

Zonder logboek van trage query's komt u niet verder. MySQL:

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

Een dag later:

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

PostgreSQL, in postgresql.conf:

log_min_duration_statement = 1000

Daarna de uitbreiding pg_stat_statements, die de opgetelde tijd per query rechtstreeks toont:

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

Let op: het gaat niet om de langste query maar om die met de grootste opgetelde tijd. Een query van 50 milliseconden die duizend keer per minuut draait, richt meer schade aan dan een rapport van tien seconden eenmaal per dag.

De instelling die het vaakst wordt vergeten

Voor MySQL en MariaDB is dat de grootte van de InnoDB-bufferpool. De standaardwaarde is 128 megabyte en die is in decennia niet veranderd. Op een server waar de database enkele gigabytes beslaat, betekent dat voortdurend lezen van schijf in plaats van uit het geheugen.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Een redelijke richtlijn voor een toegewijde databaseserver is de helft van het geheugen; voor een server waarop ook de webserver en PHP draaien een kwart, met een oog erop dat er geen swap ontstaat. Die ene instelling levert doorgaans meer op dan een week query-optimalisatie.

Twee dingen die stilletjes de schijf opeten

De binaire logboeken van MySQL. Ze zijn nodig voor replicatie en herstel naar een tijdstip, groeien onafgebroken en worden standaard soms nooit verwijderd. Ter controle:

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

Hebt u geen replicatie en gebruikt u geen herstel naar een tijdstip, stel de bewaartermijn dan in op een redelijk aantal dagen.

De WAL-bestanden van PostgreSQL bij een vergeten replicatiesleuf. Een minder bekend en gevaarlijker geval: bestaat er een replicatiesleuf waarvan de afnemer niet meer verbindt, dan is PostgreSQL verplicht alle logboeken sinds de verbreking te bewaren — en het bewaart ze tot de schijf vol is.

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

Een inactieve sleuf die niemand nodig heeft moet worden verwijderd. Het is een van de weinige situaties waarin een database een schijf gegarandeerd tot nul brengt.

Omvang en groei

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;

Nuttig is niet zozeer het huidige getal als wel de snelheid waarmee het verandert. Een database die in een maand verdubbelde zonder overeenkomstige groei van het verkeer betekent doorgaans dat een of andere logtabel wordt volgeschreven en nooit wordt opgeruimd — sessies, taakhistorie, het gebeurtenissenlogboek van een uitbreiding.

Beveiliging in twee regels

Nu u de databaseconsole toch open hebt, lonen twee controles. Ten eerste of de database van buitenaf bereikbaar is (ss -tulpn | grep 3306): naar buiten hoort ze vrijwel nooit te wijzen. Ten tweede de accounts die vanaf elk adres mogen verbinden:

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

Regels met host = '%' betekenen «verbindingen van overal». Staat de poort daarbij open, dan staat er alleen een wachtwoord tussen de database en het internet.

De reservekopieën

Nog één punt. Het bestaan van een reservebestand garandeert niets; alleen een beproefd herstel doet dat. Een dump die nooit is teruggezet, blijkt met even grote kans afgekapt, met een vergrendelingsfout gemaakt, of de verkeerde database te bevatten. Dat controleert u vooraf en op een aparte machine, niet op de dag dat de kopie nodig is.

Voor dagelijks toezicht volstaan drie getallen: het aantal verbindingen, het aantal trage query's per dag en de omvang van de database. Alle drie veranderen traag en voorspelbaar, en elke plotselinge beweging in een ervan is een blik waard. Hoe ze op één pagina ogen, toont de demo hieronder.