Les raisons à la lenteur d’un moteur de base de données relationnelle
30 juin 2018
Du fait de l’abandon des cursus spécifiques à la formation des administrateurs de bases de données depuis le milieu des années 2000, le niveau de connaissances du fonctionnement des moteurs s’est complètement effondré. Il n’y a aujourd’hui pas grand intérêt à suivre une formation où la part consacrée aux SGBDR atteint péniblement 30% du temps global de la formation.
La majorité des « bons » DBA sont aujourd’hui aux portes de la retraite et l’économie française – elle n’est pas la seule – est face à un grand désastre que les entreprises tentent péniblement de compenser par la mise à disposition de ressources matérielles de plus en plus importantes pour faire face à l’explosion de la volumétrie. En vain. Le rêve prométhéen d’un monde sans limites n’est qu’un mythe appartenant à la réalité virtuelle. L’augmentation de la volumétrie se heurte désormais à la seule bonne volonté des administrateurs système qui ont désormais en charge l’exploitation de bases, dont le mode de fonctionnement leur échappe en grande partie.
Ressources consommées par un moteur de base de données relationnelle
Les moteurs de bases de données relationnelles consomment, à titre principal, du disque et de la RAM. Les données lues sur disques sont mises en cache de manière à y accéder rapidement lors de requêtes suivantes. Tous les dispositifs logiciels qui consomment inutilement de la RAM – et donc du cache – ou bien ralentissent l’accès aux disque ou bien pénalisent lourdement le fonctionnement des bases de données relationnelles.
L’activité résidente
Un moteur de base de données relationnelle ne peut exprimer toute sa puissance sur un système d’exploitation à la condition qu’il lui soit dédié ! Scott McNealy, l’ancien PDG du Sun Microsystems depuis racheté par Oracle, nous expliquait en son temps qu’un système d’exploitation exécutant Oracle Database s’appelait Oracle. Il avait, de fait, totalement raison.
Sur les systèmes Windows, vous disposez du logiciel Autoruns, afin de désactiver les résidents qui seraient totalement inutiles. Sur Linux, j’utilise pour ma part ntsysv en environnement Red Hat/CentOS/Fedora, compatible avec systemd. Vous pouvez également utiliser Cockpit sur Fedora.
Le choix du système
Compte tenu du fait qu’un système Windows Server consomme aujourd’hui un peu plus de 1 Go sans avoir bougé le petit doigt, je vous recommande chaudement d’installer MySQL/MariaDB/PostgreSQL/Oracle Database/SQL Server sur un système Linux qui, sans interface graphique, consomme à peine 100 Mo.
Non au GUI sur le Serveur
Dans le genre des pratiques imbéciles, il y a celle sous Windows, qui consiste à se connecter en RDP pour exécuter les outils d’administration graphiques des éditeurs (SSMS, PgAdmin, HeidiSQL, MySQL WorkBench, SQL Developer, etc).
L’antivirus
Si vous êtes contraint-e à maintenir un antivirus sur la machine exécutant un logiciel de base de données relationnelle, la seule chose à faire, c’est d’ajouter des exceptions sur les process liés au fonctionnement du moteur et au niveau des répertoires des fichiers de données et de transactions utilisés par ledit moteur. Par l’analyse des contenus écrits et lus sur le disque, l’antivirus pénalise les IO et la vitesse d’accès aux données ! La seule raison objective qui pourrait vous obliger à laisser l’antivirus effectuer sa sale besogne, c’est lorsque vous envoyez des binaires ou du contenu Html/Javascript susceptibles d’être infectés dans les champs de votre base de données !
Impact de la virtualisation
J’avais, il y a quelques années, fait une formation sur l’état de l’art en matière de virtualisation pour un salarié de Thalès. Nous avions deux machines. L’une était équipée de Windows Server 2008 R2 et l’autre de Vmware Vsphere 5.1. Avec Novabench, nous avons réalisé toute une série de benchmarks. En terme d’écriture sur disque, le logiciel exécuté dans des machines virtuelles Vsphere, Vmware Player, VirtualBox indiquait une baisse de la performance de 45% par rapport à la machine physique. Il n’était plus que de 25% sous HyperV, qui utilise un mécanisme dit de paravirtualisation, à l’image de Xen ou KVM sous Linux. En matière de bases de données, il ne faut pas stocker les données à l’intérieur de vos fichiers VMDK ou VHD, dès lors que la taille des bases excède les 100 Go. Mieux vaut attaquer une LUN de votre baie SAN ou votre serveur NAS à l’aide d’un point de montage NFS ou d’un initiator ISCSI géré directement par le système d’exploitation !
SELECT : un peu de SQL !
Compte tenu du faible niveau de connaissances en SQL, une grande majorité de développeurs n’ont pas acquis les bonnes pratiques. En voici quelques unes :
- éviter le SELECT * FROM …, afin de minimiser la quantité de données montées en RAM et d’utiliser les index (enable_seqscan dans PostgreSQL);
- utiliser la clause WHERE, OFFSET ou LIMIT (pour les requêtes paginées) afin de minimiser le nombre de lignes montées et de ne pas épuiser le cache ;
- minimiser le nombre de plans d’exécution en normalisant la manière d’écrire les requêtes et en privilégiant l’utilisation des vues ;
- poser des index sur les champs les plus utilisés au niveau des clauses WHERE ;
- privilégier l’utiliser de vues matérialisées pour le décisionnel, en permettant de construire des index sur des champs appartenant à des tables différentes ;
- créer des index sur les clés étrangères dans le cadre d’une relation 1->1 : le SGBDR privilégie l’index ou la clé de la table disposant du nombre de lignes le plus faible ;
- préférer les contraintes uniques et une bonne gestion d’erreurs aux contrôles de saisie réalisés dans les applications, afin de minimiser les instructions SELECT inutiles ;
- privilégier l’écriture des jointures utilisant l’opérateur JOIN (à partir de SQL Server 2014).
Transactions
Privilégiez l’instruction TRUNCATE TABLE à DELETE pour nettoyer les tables, sauf si vous souhaitez effectuer un ROLLBACK de l’opération. L’instruction TRUNCATE n’est pas transactionnelle !
Tables temporaires
Contrairement à une idée reçue, les tables temporaires ne sont pas montées en mémoire. Elles utilisent les « tablespaces » ou les « groupes de fichiers » temporaires. Pratiques, elles n’apportent aucun gain de performance supplémentaire.
In-Memory
La technologie In-Memory n’a pas grand intérêt pour les bases de données de type DataWareHouse, où l’instruction principale utilisée est le SELECT. En montant les tables en mémoire, vous introduisez un mécanisme concurrent à l’utilisation du cache. L’intérêt du In-Memory tient surtout à la vitesse d’écritures des transactions !
MySQL / MariaDB : préférez InnoDB à MyISAM / Aria
Il n’y a aucun intérêt à choisir MyISAM ou Aria si vous faites des jointures au niveau de vos requêtes sous MySQL / MariaDB. Le temps d’exécution d’une requête sous MyISAM est 66% plus lent si vous utilisez l’opérateur INNER JOIN. En mono-table, MyISAM pulvérise très largement InnoDB.