«Il sito è lento» finisce quasi sempre nel database. La verifica richiede pochi minuti se la si fa nell’ordine giusto: prima ciò che si vede durante il rallentamento stesso, poi ciò che si è accumulato per settimane.

Guardi mentre il problema si verifica

La regola principale: le informazioni più preziose sono disponibili proprio mentre tutto è lento. Il primo comando per MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

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

Vedrà che cosa è in esecuzione adesso e da quanto. La risposta di solito è immediata: una query pesante trattiene le altre, oppure cento query identiche attendono il rilascio di un lock.

Le connessioni

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

Se Max_used_connections si avvicina a max_connections, l’applicazione riceve di tanto in tanto il rifiuto «troppe connessioni». Alzare il limite non è la prima cosa da fare: ogni connessione occupa memoria, e aumentarlo su un server a cui la memoria già manca non fa che accelerare la corsa verso lo swap. Capisca prima perché le connessioni non vengono rilasciate: di solito una query lunga o l’assenza di un pool sul lato dell’applicazione.

Vale la pena guardare la stessa cosa dall’altro lato: un aumento brusco delle connessioni può derivare non da una popolarità crescente ma da bot che martellano una pagina pesante: una ricerca, un filtro di catalogo, un generatore di rapporti.

Le query lente

Senza un registro delle query lente non si va oltre. MySQL:

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

Un giorno dopo:

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

PostgreSQL, in postgresql.conf:

log_min_duration_statement = 1000

Poi l’estensione pg_stat_statements, che mostra direttamente il tempo cumulato per query:

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

Attenzione: ciò che conta non è la query più lunga ma quella con il tempo cumulato maggiore. Una query da 50 millisecondi eseguita mille volte al minuto danneggia più di un rapporto da dieci secondi una volta al giorno.

L’impostazione che si dimentica più spesso

Per MySQL e MariaDB è la dimensione del pool di buffer InnoDB. Il valore predefinito è 128 megabyte e non cambia da decenni. Su un server il cui database occupa diversi gigabyte, ciò significa lettura costante dal disco invece che dalla memoria.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Un riferimento ragionevole per un server dedicato ai database è metà della memoria; per un server dove girano anche il server web e PHP, un quarto, badando a non innescare lo swap. Quella singola impostazione di solito rende più di una settimana di ottimizzazione delle query.

Due cose che divorano il disco in silenzio

I registri binari di MySQL. Servono per la replica e il ripristino a un momento preciso, crescono di continuo e per impostazione predefinita a volte non vengono mai rimossi. Per verificare:

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

Se non ha replica e non usa il ripristino a un momento preciso, imposti la conservazione su pochi giorni ragionevoli.

I file WAL di PostgreSQL con uno slot di replica dimenticato. Caso meno noto e più pericoloso: se esiste uno slot di replica il cui consumatore non si collega più, PostgreSQL è obbligato a conservare tutti i registri dal momento della disconnessione, e li conserverà fino a riempire il disco.

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

Uno slot inattivo di cui nessuno ha bisogno va eliminato. È una delle poche situazioni in cui un database porta il disco a zero con certezza.

Dimensione e crescita

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;

Utile non è tanto la cifra attuale quanto la velocità con cui cambia. Un database raddoppiato in un mese senza un corrispondente aumento del traffico di solito significa che qualche tabella di registrazione viene scritta e mai ripulita: sessioni, cronologia delle attività, registro eventi di un’estensione.

La sicurezza in due righe

Visto che ha aperto la console del database, valgono due verifiche. Primo, se il database è raggiungibile dall’esterno (ss -tulpn | grep 3306): quasi mai deve esserlo. Secondo, gli account che possono collegarsi da qualsiasi indirizzo:

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

Le voci con host = '%' significano «connessioni da ovunque». Se per giunta la porta è aperta, fra il database e internet c’è solo una password.

I backup

Un ultimo punto. L’esistenza di un file di backup non garantisce nulla; lo garantisce solo un ripristino collaudato. Un dump mai ripristinato ha la stessa probabilità di risultare troncato, preso con un errore di lock, o di contenere il database sbagliato. Va verificato in anticipo e su una macchina a parte, non il giorno in cui la copia serve.

Per la sorveglianza quotidiana bastano tre cifre: il numero di connessioni, la quantità di query lente al giorno e la dimensione del database. Tutte e tre cambiano lentamente e in modo prevedibile, e qualsiasi movimento brusco di una di esse merita un’occhiata. Come appaiono su una pagina lo mostra la dimostrazione qui sotto.