Optimisation de la base de données : matériel et configuration

Séparer

Une grande part des performances vient de MariaDB ou MySQL, comme pour d’autres applications sur la même base. La page conseille un serveur de base distinct : l’application a des pics et une mémoire changeante, la base veut de la RAM et des IOPS stables, et les deux ne se règlent pas pareil.

Matériel

Au moins 2 vCPU. Le nombre fixe le vrai parallélisme. Surveiller : au-dessus de 60 % en continu, ou de 50 % longtemps, ajouter des vCPU.

Au moins 8 Go de RAM. Le buffer pool sert de cache : une base bien réglée ne lit le disque pour les données que moins de 1 % du temps. Ne pas compter sur le swap. En garder un peu, et baisser le swappiness au minimum.

Disque et IOPS : viser environ trois fois l’espace utilisé, pour ne pas agrandir à chaque saturation. Sur AWS, plus de stockage donne aussi plus d’IOPS. Départ vers 2000 à 3000 IOPS, disque d’au moins 50 Go. Une base de 20 Go mène vers 60 Go.

Monter en vertical aussi longtemps que possible, jusqu’à environ 64 vCPU et 128 Go. Les offres cloud regroupent les ressources. Au-delà d’une machine, un réplica reçoit les lectures lourdes : Lectures depuis une base secondaire.

Buffer pool

innodb-buffer-pool-size est le réglage le plus important, et le défaut historique n’utilise pas le matériel. Il doit contenir le jeu de travail : ce qu’on touche chaque jour. Exemple ERP : base de 80 Go sur 4 ans, croissance de 20 Go la dernière année, donc un pool de 20 Go. À environ 60 % de la RAM, cela fait 20 / 0,6, soit environ 33 Go.

Le mesurer après quelques jours. Taux de succès au-dessus de 99 % : inutile d’agrandir. Utilisation loin de 95 à 100 % : inutile aussi. SHOW ENGINE INNODB STATUS montre la section BUFFER POOL AND MEMORY. L’extrait : taille 2790528, 1594 buffers libres, taux 999 / 1000.

Le titre de section écrit 5G. Le fichier d’exemple, pour 8 Go de RAM, met 4.8G.

Autres paramètres

Configuration utilisée chez Frappe, à ajuster.

• innodb-flush-method = O_DIRECT : entrées-sorties directes.
• innodb-flush-log-at-trx-commit = 1 : le journal à chaque commit. Ne pas le baisser pour un petit gain.
• innodb-file-per-table = 1.
• Journal des requêtes lentes vers /var/lib/mysql/mysql-slow.log.
• query-cache-type et query-cache-size à 0 : le cache de requêtes nuit.

Le bloc [mysqld] publié fixe aussi InnoDB par défaut, key-buffer MyISAM 32 Mo, myisam-recover FORCE,BACKUP, max-allowed-packet 256 Mo puis, plus bas, max_allowed_packet 512 Mo, max-connect-errors 1000000, innodb = FORCE, datadir /var/lib/mysql/, binlog 14 jours, sync-binlog = 1, tables temporaires 32 Mo, max-connections 500, caches de threads et de tables, tmp-disk-table-size 5120 Mo, max-statement-time 10800, deux fichiers de journal InnoDB de 512 Mo, format barracuda, innodb-large-prefix = 1, innodb-old-blocks-time 5000, utf8mb4. [mysql] et [mysqldump] sont dans le même fichier. log-queries-not-using-indexes vaut 0.

Suivi

Pas d’outil Frappe dédié. Prometheus et Grafana sont cités, avec le journal lent et pt-query-digest. Exporters : Node Exporter, MySQLD Exporter, PMM.

À suivre : CPU près de 100 %, RAM d’une base bien réglée autour de 90 % (100 % risque le swap ou le plantage, bien en dessous de 90 % le pool n’utilise pas la RAM), lectures disque faibles une fois le cache chaud, pics de lectures de lignes InnoDB, taux d’échec du buffer pool sous 1 %, attentes de verrous (mettre en file plutôt qu’en parallèle), churn de la sous-chaîne LRU (les sauvegardes logiques hors heures de travail).

Références : variables InnoDB et serveur MariaDB, un article Percona, le buffer pool MySQL 8, Percona Toolkit.

Point d’attention

Le titre annonce 5 Go, l’exemple 8 Go de RAM met 4,8 Go, et le calcul ERP aboutit à environ 33 Go. max-allowed-packet apparaît à 256 Mo et à 512 Mo dans le même fichier.