Hébergement de serveurs de bases de données : MySQL, PostgreSQL et MongoDB

Les problèmes de performance des bases de données viennent presque toujours de l'une de ces trois causes : pas assez de mémoire, ce qui oblige à lire le pool de tampons depuis le disque, des E/S de stockage qui ne peuvent pas suivre le débit des transactions, ou des conflits CPU à cause de trop de requêtes en même temps qui partagent un pool de ressources. Les serveurs dédiés bare metal éliminent ces trois problèmes du côté de l'hébergement.

Cet article parle des paramètres de configuration spécifiques pour MySQL, PostgreSQL et MongoDB sur du matériel dédié, avec des valeurs de référence pour les niveaux de serveurs InMotion Hosting. Ce sont des points de départ, pas des réglages universels. Les caractéristiques de ta charge de travail vont demander des ajustements, mais avoir des valeurs de départ vérifiées, c'est plus rapide que de régler à partir des valeurs par défaut.

Pourquoi les charges de travail des bases de données doivent être sur du matériel dédié

Les bases de données sont super sensibles aux conflits de ressources que l'hébergement mutualisé peut créer. Quand le pool de tampons InnoDB de MySQL atteint sa limite de taille et commence à vider des pages, chaque requête suivante qui a besoin d'une page vidée doit lire le disque. Dans un environnement mutualisé, le trafic d'un autre locataire peut faire baisser l'occupation de votre pool de tampons au pire moment possible.

Sur un serveur dédié, le pool de mémoire tampon contient ce que tu as configuré. Si tu alloues 100 Go à InnoDB, tu disposes de 100 Go. Point final. La prévisibilité que cela crée n'est pas un avantage mineur. C'est la différence entre une base de données qui fonctionne de manière constante sous la charge et une autre qui se comporte de manière imprévisible.

Ça surprend pas mal d'administrateurs de bases de données qui considèrent la variabilité des performances comme une caractéristique inhérente aux bases de données. En fait, c'est souvent un problème lié à l'hébergement.

Configuration MySQL / MariaDB

Dimensionnement du pool de tampons InnoDB

La décision la plus importante à prendre pour la configuration de MySQL, c'est la taille du pool de mémoire tampon InnoDB. L'idée, c'est de garder tout ton ensemble de données de travail en mémoire. Sur un serveur de 64 Go (InMotion Essential ou Advanced), alloue 40 à 48 Go au pool de mémoire tampon. Sur un système de 192 Go, tu peux raisonnablement allouer 140 à 160 Go :

• innodb_buffer_pool_size = 140 Go ( sur un serveur Extreme de 192 Go)
• innodb_buffer_pool_instances = 8 ( réduit les conflits de mutex sur les systèmes multicœurs)
• innodb_log_file_size = 2G ( des journaux de reprise plus gros réduisent la fréquence des points de contrôle)
• innodb_flush_log_at_trx_commit = 1 ( complètement conforme à ACID ; mets-le à 2 pour les charges de travail non critiques avec beaucoup d'écritures)
• innodb_io_capacity = 2000 ( augmentez à 4000+ pour NVMe pour permettre une utilisation complète des E/S)

Réglage NVMe pour MySQL

Les paramètres par défaut de MySQL ont été pensés pour les disques rotatifs ou SSD SATA. Sur NVMe, il faut changer quelques paramètres pour éviter de limiter artificiellement le débit d'E/S :

• innodb_io_capacity_max = 8000 ( permet une utilisation intensive des E/S sur NVMe)
• innodb_read_io_threads = 8 ( augmentez la valeur par défaut de 4 pour profiter NVMe )
• innodb_write_io_threads = 8 ( même raison)
• innodb_flush_method = O_DIRECT ( évite le cache de page du système d'exploitation pour empêcher le double tamponnage avec le pool de tampons InnoDB)

Quand O_DIRECT est activé sur NVMe, MySQL ne passe pas par le cache de page du système d'exploitation et gère tout son propre pool de tampons. Ça évite que le pool de tampons et le système d'exploitation mettent en cache les mêmes données de leur côté, ce qui, sur un système de 192 Go, gaspillerait beaucoup de mémoire si les deux couches essayaient de mettre en cache le jeu de données.

Journalisation des requêtes lentes

Active la journalisation des requêtes lentes à partir d'un seuil de 100 ms comme outil de surveillance permanent, pas seulement pendant le dépannage :

• slow_query_log = ON
• long_query_time = 0,1 ( seuil de 100 ms)
• log_queries_not_using_indexes = ON ( enregistre les analyses complètes des tables, même si elles sont rapides)

PostgreSQL

Paramètres de mémoire

La configuration PostgreSQL est moins agressive que celle de MySQL par défaut, car elle est faite pour tourner avec d'autres processus. Sur un serveur de base de données dédié, tu peux pousser beaucoup plus haut :

• shared_buffers = 48 Go ( 25 % de la RAM sur un système de 192 Go ; PostgreSQL recommande 25 % comme point de départ, même si certaines charges de travail tirent profit de valeurs plus élevées)
• effective_cache_size = 144 Go ( indique au planificateur de requêtes combien de mémoire est disponible pour la mise en cache ; réglé sur 75 % de la RAM)
• work_mem = 256 Mo ( par opération de tri/hachage ; multiplier par max_connections pour l'utilisation potentielle totale ; valeur de départ prudente)
• maintenance_work_mem = 4 Go ( utilisé pour VACUUM, CREATE INDEX et d'autres trucs du même genre)
• max_wal_size = 8 Go ( réduit la fréquence des points de contrôle pour les charges de travail avec beaucoup d'écritures)

Une valeur de 25 % pour shared_buffers, c'est une PostgreSQL , pas un plafond. Les charges de travail avec des ensembles de données volumineux et fréquemment consultés tirent leur avantage de valeurs pouvant atteindre 40 % de la RAM, avec une augmentation proportionnelle de effective_cache_size. Le planificateur de requêtes utilise effective_cache_size pour choisir entre les analyses d'index et les analyses séquentielles. Une valeur inexacte conduit donc à des plans de requêtes sous-optimaux.

Pool de connexions avec PgBouncer

PostgreSQL un processus par connexion. Avec plus de 200 connexions en même temps, le temps passé à changer de contexte de processus devient important. PgBouncer sert de proxy de connexion qui garde un petit groupe de PostgreSQL réelles, multiplexant des centaines de connexions d'applications à travers elles.

Pour les applis avec des centaines d'utilisateurs en même temps, installe PgBouncer sur le même serveur et configure les applis pour qu'elles se connectent à PgBouncer sur le port 6432. Le pooling en mode transaction convient à la plupart des charges de travail des applis web ; le pooling en mode session est nécessaire pour les applis qui utilisent des tables temporaires ou des verrous consultatifs.

Performances NVMe WAL

Les performances PostgreSQL dépendent beaucoup du débit d'écriture du WAL (Write-Ahead Log). Chaque transaction validée écrit un enregistrement WAL avant de revenir au client. Sur NVMe, les opérations WAL fsync se font en quelques microsecondes, alors que ça prend des millisecondes sur les SSD SATA. Ça améliore direct le débit des transactions pour les charges de travail avec beaucoup d'écritures.

Configurez wal_compression = on pour réduire le volume WAL pour les charges de travail avec beaucoup de lectures et parfois de gros écrits. Pour les réplicas analytiques qui reçoivent une réplication en continu, NVMe le primaire et le réplica garantit que le décalage de réplication reste minime, même pendant les périodes de forte écriture.

Configuration de MongoDB

Dimensionnement du cache WiredTiger

Le moteur de stockage WiredTiger de MongoDB utilise un cache interne qui est séparé du cache de page du système d'exploitation. Par défaut, le cache WiredTiger est réglé sur 50 % de la RAM moins 1 Go. Sur un système de 192 Go, ça fait environ 95 Go. Pour les serveurs de base de données dédiés, tu peux augmenter cette valeur :

• storage.wiredTiger.engineConfig.cacheSizeGB : 120 ( dans mongod.conf pour un serveur dédié de 192 Go)

WiredTiger compresse son cache interne (Snappy par défaut). Sur NVMe avec de la marge CPU, la compression zstd donne de meilleurs ratios avec une charge CPU acceptable, ce qui réduit la charge I/O effective pour les grosses collections de documents.

Configuration de la lecture/écriture et du journal

Pour les déploiements de répliques sur un seul serveur dédié qui tourne plusieurs instances mongod, configure bien les préoccupations d'écriture :

• w : majorité pour les données financières ou critiques (attend que la majorité du jeu de répliques confirme)
• j : true active la journalisation, qui écrit sur NVMe de confirmer ; coût de latence acceptable sur NVMe
• readPreference : secondaryPréféré pour les charges de travail avec beaucoup de lectures, ça répartit la charge de lecture entre les membres répliqués.

Stratégie RAID pour les charges de travail des bases de données

Les serveurs dédiés InMotion utilisent un RAID logiciel configuré avec mdadm. C'est important pour comprendre les performances, parce que le RAID logiciel sur NVMe un processeur multicœur moderne ne se comporte pas comme les contrôleurs RAID matériels classiques avec cache d'écriture alimenté par batterie.

Niveau RAID	Capacité utilisable	Lire Performance	Écrire Performance	Cas d'utilisation
RAID 0 (bande)	7,68 To (complet)	2x séquentiel	2x séquentiel	Espace de travail, données pas super importantes
RAID 1 (miroir, réglage par défaut d'InMotion)	3,84 To	Lire depuis n'importe quel disque	Écris sur les deux disques	Bases de données de production
Pas de RAID (disque dur unique)	3,84 To	NVMe maximale	NVMe maximale	Lire les répliques avec une sauvegarde externe

Pour les serveurs de bases de données de production, RAID 1 via mdadm est le bon choix par défaut. La pénalité d'écriture est minime sur NVMe les deux disques sont assez rapides pour que les écritures en miroir restent en avance sur la plupart des exigences de débit des applications), et la redondance protège contre la défaillance d'un seul disque sans perte de données pendant la période de remplacement.

Le RAID, c'est pas une stratégie de sauvegarde. Un bug logiciel qui abîme le répertoire de données, une commande DROP TABLE accidentelle ou un ransomware affectent les deux disques en miroir en même temps. Le stockage de sauvegarde automatisé de 500 Go de Premier Care offre une vraie protection contre ces modes de défaillance.

Stratégie de sauvegarde pour les bases de données de production

Sauvegarde MySQL

Pour les bases de données MySQL de moins de 50 Go, mysqldump nocturne avec –single-transaction permet de faire des sauvegardes cohérentes sans bloquer les tables. Pour les bases de données plus grosses, Percona XtraBackup fait des sauvegardes physiques à chaud qui se restaurent plus vite que les sauvegardes SQL. Enregistre les sauvegardes sur le volume de sauvegarde de 500 Go de Premier Care, qui est hors serveur.

PostgreSQL

pg_dump pour les petites bases de données ; pg_basebackup pour les sauvegardes physiques de bases de données plus importantes. Pour des exigences RPO proches de zéro, configurez l'archivage WAL continu vers le volume de sauvegarde : chaque segment WAL terminé est automatiquement transféré, offrant une capacité de restauration ponctuelle avec une granularité généralement comprise entre 5 et 10 minutes.

Sauvegarde MongoDB

mongodump permet de faire des sauvegardes logiques ; pour les gros déploiements, les instantanés au niveau du système de fichiers du répertoire de données WiredTiger (pris quand la base de données est inactive ou à un moment cohérent) sont plus rapides à restaurer. Pour les déploiements de jeux de répliques, faire des sauvegardes à partir d'un membre secondaire évite tout impact sur le débit d'écriture primaire.

Choisir le bon niveau de serveur dédié

Taille de la base de données	Connexions simultanées	Niveau recommandé	Coût mensuel
Moins de 20 Go de mémoire vive	Jusqu'à 100	Essentiel (64 Go DDR4)	99,99 $ par mois
Ensemble de travail de 20 à 50 Go	100-300	Avancé (64 Go DDR4, RAID-1)	149,99 $ par mois
Ensemble de travail de 50 à 140 Go	300-500	Élite	199,99 $ par mois
Ensemble de travail de plus de 140 Go	500+	Extrême (192 Go DDR5 ECC)	349,99 $ par mois

Ces seuils partent du principe que le serveur est dédié à la charge de travail de la base de données. Les serveurs mixtes qui hébergent à la fois la couche applicative et la base de données ont besoin d'une plus grande marge de manœuvre en matière de mémoire à tous les niveaux.

Pour commencer

• Prix des serveurs dédiés : inmotionhosting .com/dedicated-servers/dedicated-server-price
• ServeursNVMe : inmotionhosting .nvme
• Premier Care pour les sauvegardes automatiques : inmotionhosting .com/blog/inmotion-premier-care/

L'équipe APS InMotion Hostings'occupe de la gestion au niveau du système d'exploitation et peut vous aider pour la configuration initiale dans le cadre du service Premier Care. La consultation mensuelle d'une heure offerte par InMotion Solutions est super utile pour vérifier le réglage de la base de données, surtout quand on migre une base de données de production depuis un hébergement partagé, où l'amélioration des performances est souvent importante.