« Le site rame » se ramène presque toujours à la base de données. La vérification prend quelques minutes si on la mène dans le bon ordre : d’abord ce qui se voit pendant le ralentissement lui-même, ensuite ce qui s’est accumulé pendant des semaines.

Regardez pendant que le problème se produit

La règle principale : les informations les plus précieuses sont disponibles précisément quand tout est lent. La première commande pour MySQL :

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

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

Vous verrez ce qui s’exécute en ce moment et depuis combien de temps. La réponse est d’ordinaire immédiate : une requête lourde retient les autres, ou cent requêtes identiques attendent la libération d’un verrou.

Les connexions

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

Si Max_used_connections s’approche de max_connections, l’application reçoit périodiquement un refus « trop de connexions ». Relever la limite n’est pas la première chose à faire : chaque connexion occupe de la mémoire, et augmenter la limite sur un serveur qui en manque déjà ne fait qu’accélérer le passage au swap. Cherchez d’abord pourquoi les connexions ne se libèrent pas : d’ordinaire une requête longue, ou l’absence de pool côté application.

Il vaut la peine de regarder la même chose sous un autre angle : une hausse brutale du nombre de connexions peut venir non d’une popularité croissante mais de robots martelant une page lourde — une recherche, un filtre de catalogue, un générateur de rapports.

Les requêtes lentes

Sans journal des requêtes lentes, impossible d’aller plus loin. MySQL :

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

Un jour plus tard :

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

PostgreSQL, dans postgresql.conf :

log_min_duration_statement = 1000

Ensuite l’extension pg_stat_statements, qui montre directement le temps cumulé par requête :

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

Notez bien : ce qui compte n’est pas la requête la plus longue mais celle dont le temps cumulé est le plus grand. Une requête de 50 millisecondes exécutée mille fois par minute nuit davantage qu’un rapport de dix secondes une fois par jour.

Le réglage le plus souvent oublié

Pour MySQL et MariaDB, c’est la taille du pool de tampons InnoDB. La valeur par défaut est de 128 mégaoctets et n’a pas changé depuis des décennies. Sur un serveur dont la base occupe plusieurs gigaoctets, cela signifie une lecture permanente depuis le disque au lieu de la mémoire.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Un repère raisonnable pour un serveur dédié aux bases est la moitié de la mémoire vive ; pour un serveur où tournent aussi le serveur web et PHP, le quart, en veillant à ne pas déclencher le swap. Ce seul réglage apporte d’ordinaire plus qu’une semaine d’optimisation de requêtes.

Deux choses qui dévorent le disque en silence

Les journaux binaires de MySQL. Ils servent à la réplication et à la restauration à un instant donné, grossissent en permanence et, par défaut, ne sont parfois jamais supprimés. Pour vérifier :

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

Si vous n’avez pas de réplication et n’utilisez pas la restauration à un instant donné, fixez la conservation à quelques jours raisonnables.

Les fichiers WAL de PostgreSQL avec un emplacement de réplication oublié. Cas moins connu et plus dangereux : si un emplacement de réplication existe alors que son consommateur ne se connecte plus, PostgreSQL est tenu de conserver tous les journaux depuis la déconnexion — et il les conservera jusqu’à saturation du disque.

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

Un emplacement inactif dont personne n’a besoin doit être supprimé. C’est l’une des rares situations où une base de données mène le disque à zéro à coup sûr.

Taille et croissance

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;

Ce qui est utile, c’est moins le chiffre actuel que la vitesse à laquelle il change. Une base qui a doublé en un mois sans hausse correspondante du trafic signifie d’ordinaire qu’une table de journalisation s’écrit et n’est jamais purgée — sessions, historique de tâches, journal d’événements d’une extension.

La sécurité en deux lignes

Puisque la console de la base est ouverte, deux vérifications valent la peine. D’abord, si la base est joignable depuis l’extérieur (ss -tulpn | grep 3306) : elle ne doit presque jamais être exposée. Ensuite, les comptes autorisés à se connecter depuis n’importe quelle adresse :

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

Les entrées avec host = '%' signifient « connexions de partout ». Si le port est ouvert par-dessus le marché, seul un mot de passe sépare la base de l’internet.

Les sauvegardes

Un dernier point. L’existence d’un fichier de sauvegarde ne garantit rien ; seule une restauration éprouvée le fait. Un vidage jamais restauré a autant de chances de se révéler tronqué, pris avec une erreur de verrou, ou de contenir la mauvaise base. Cela se vérifie à l’avance et sur une machine distincte, pas le jour où la copie devient nécessaire.

Pour la surveillance quotidienne, trois chiffres suffisent : le nombre de connexions, le nombre de requêtes lentes par jour et la taille de la base. Tous trois évoluent lentement et de façon prévisible, et tout mouvement brusque de l’un d’eux mérite un regard. À quoi ils ressemblent sur une page, la démonstration ci-dessous le montre.