Audit d’une base de données relationnelle
7 mars 2019
Cela faisait 6 années et demi que je n’avais pas fait d’audit de bases de données. La dernière fois, c’était pour le compte d’une SSII de l’Amienois qui basculait les données de son logiciel métier vers SQL Server. A l’issue de l’audit, la SSII créa un demi-poste consacré au tuning des bases de données et à la pose d’index bien sentis. Et puis, sur mes préconisations, elle recruta un chef de projet, aux compétences solides sur le modèle relationnel, chargé de refondre la base de données.
Les problèmes de performance des bases de données apparaissent, en général, avec l’augmentation de la volumétrie des bases, surtout quand les instances s’exécutent dans des environnements virtualisés. Le surdimensionnement des machines, s’il permet dans un 1er temps de voir venir, ne peut pas occulter bien longtemps la faiblesse de la mise en œuvre du modèle relationnel. Des compétences insuffisantes en SQL et sur la compréhension du fonctionnement d’un moteur de bases de données relationnelles finissent par transformer une Ferrari en une simple pétrolette !
L’environnement de la base de données
L’un des critères les plus importants pour le confort de fonctionnement d’une base de données relationnelle est la mémoire dont dispose la machine. Il faut également être en capacité d’évaluer en quoi des processus concurrents sont susceptibles d’impacter la performance du moteur de bases de données. Dans le cadre d’un serveur LAMP hébergeant des applications PHP, il ne peut pas y avoir d’optimisation du moteur de bases de données sans optimisation du fonctionnement du serveur Web Apache ou Nginx, par quelques directives bien senties concernant notamment la mise en cache des fichiers. Sans opcode, les calculs en PHP seront également beaucoup plus lents ! La mise en œuvre du PHP-FPM vous permettra, quant à elle, d’isoler et d’optimiser la configuration PHP de chacune des applications installées.
La qualité du modèle relationnel
Les éléments objectifs pour évaluer la qualité du modèle relationnel sont tout d’abord les règles de nommage utilisées. Nom de tables au pluriel (contacts), préfixe au nom de tables (t_contact), noms de champ identiques pour désigner des données différentes (auto, id), répétition indicée du nom d’un champ (contact1, contact2, etc) sont des éléments immédiats montrant la faible qualité du modèle relationnel. L’absence de clés primaires, de contraintes d’intégrité relationnelle, d’index uniques pour la gestion des doublons, de check constraints est un indicateur de la faible compétence – voire de l’incompétence – des équipes en charge de la mise en place du schéma de la base de données relationnelle.
La performance d’un moteur de bases de données relationnel
Pour l’essentiel, la performance des instances MySQL, MariaDB, PostgreSQL, SQL Server ou Oracle Database est intimement liée à la quantité de mémoire utilisée pour la mise en cache des blocs lus sur disque et accessoirement, selon les motorisations, des données issues de l’exécution des requêtes. L’accès aux données se fait par la lecture des blocs de pages d’index mis en cache. Il faut à tout prix, au niveau du SQL, éviter le SELECT *, l’usage du SELECT sans clause WHERE qui contribuent à épuiser le cache de manière rédhibitoire. Les index simples ou combinés doivent porter sur les champs utilisés dans les clauses WHERE. La pose d’index suppose la mise en place de plans de maintenance permettant leur réorganisation et leur reconstruction régulières. Ces plans permettent également de prendre en charge la défragmentation des blocs de données !
La politique de sauvegarde des données
L’une des erreurs assez communes des personnes en charge de l’infrastructure est de croire que la mise en œuvre d’un cluster synchrone est susceptible de résoudre tous les problèmes en matière de disponibilité. Le cluster n’est là que pour palier le crash d’une machine. En mode synchrone, sa mise en œuvre va propager toutes les erreurs de manipulation d’une instance vers l’autre. Le cluster n’empêchera jamais à un DBA, un utilisateur ou un développeur de faire un DROP, un TRUNCATE ou un DELETE. En matière de disponibilité, il est plus sûr d’utiliser les mécanismes de sauvegarde des journaux de transactions fournis par l’éditeur et de mettre en œuvre une réplication master-slave asynchrone.