Feb/100
Natural ID vs Generated ID
Au boulot, je suis régulièrement confronté à ce que les développeurs croient être le mieux pour une application, versus ce que je crois qui est le mieux pour le serveur de base de données. Voici donc une petite étude que j’ai fais concernant les Natural ID versus les Generated ID.
Premièrement, il n’y a aucune garantie que les Natural IDs ne changeront jamais, sous aucune circonstance. En fait, il y a très peu de cas où nous pouvons en être 100% sur. Les Generated ID eux ne changent jamais, sous aucune circonstance.
De plus, advenant le cas où le natural ID changerait, il faudra gérer ce changement en cascade pour chaque clé étrangère. Ce changement invalidera tous les enregistrements en cache, pour tous les niveaux de cache, autant pour la table primaire et que les tables “enfants”.
Dans un contexte de faible contrainte d’intégrité, la possibilité que quelqu’un soit tenté d’updater la valeur d’une « foreign key » est élevé puisqu’il s’agit de business value. On s’expose à des problèmes d’intégrité majeurs. À l’inverse, nous ne serons pas portés à updater une valeur numérique puisqu’elle ne possède aucune signification, ou si nous voulons réellement le faire, nous devrons passer par le bon service pour nous assurer de la signification de la clé générée que nous possédons.
Deuxièmement, en adoptant les Natural ID, il devient impossible d’adopter une naming convention en ce qui concerne les Primary Key et les Foreign/Surogate key. Nous ferons face a une série de champs différent, sans facilement et rapidement distinguer s’ils sont des identifiants uniques. Il faudra se référer à la structure de la table chaque fois qu’on fait des requêtes manuellement. Pour les clés qui sont transportées hors de leur domaine d’affaire (dans une application SOA par exemple), il est encore plus difficile de savoir s’il s’agit de clés fesant référance à un enregistrement qui est stocké “ailleur”, ou s’il s’agit simplement de valeurs du domaine d’affaire en question.
Troisièmement, les Natural ID rendent la construction d’un Data warehouse plus difficile et davantage nécessaire. L’objectif d’un data warehouse est de compiler des données difficiles à obtenir, soit par la complexité du schema ou par la quantité grandissante de données; Les Natural ID augmentent la quantité de données à maintenir, diminuent la performance et complexifient le schema. De plus, si un Natural ID change, le datawarehouse devient out of date et il n’y a pas de mécanisme pour updater les ID en cascade.
Quatrièmement, avec Hibernate, les Natural Key ajoutent de la complexité au code: pour chaque clé naturelle constituée de 2 champs et plus, un objet de type “Identifier” doit être créé afin d’identifier de manière unique un objet. Hibernate gère déjà automagiquement les clés auto-incrementes et n’a pas besoin d’objet de type “Identifier”.
Cinquièmement, InnoDB utilise un concept appelé Clustered Key. Ceci implique que chaque index supplémentaire crée devient préfixé de la clé primaire. Un Generated ID de type INT ajouterait 4 bytes à chaque index, alors qu’un Natural ID VARCHAR avec une moyenne de 20 char ajouterait 20 byte à chaque index. Sur une table de 145000 enregistrements, on parle de 2.21Mb d’index supplémentaire à maintenir en RAM. De plus, pour chaque Foreign Key créé, on parle de 16 bytes supplémentaires de différence pour chaque enregistrement de chaque Foreign Key. La quantité de données à maintenir en RAM grossit excessivement rapidement.
Imaginez une DB qui tourne autour de 220 requêtes/seconde. Imaginez le travail supplémentaire que devra effectuer le serveur si seulement 10 des 220 requêtes scannent la moitié des 145000 enregistrements. Ça donne approximativement 10 X 1.10 Mb = 11Mb/seconde pour une seule table qui possède un seul Natural ID. C’est énorme.
Dernièrement, voici d’autres arguments en rafale
- L’utilisation des Generated ID élimine la duplication des business data
- Si la Natural ID est constitué de plusieurs champs différents, force est de constater qu’il n’y a pas réellement de “vrai” Natural ID.
- Les Generated ID numériques autoincrement offre implicitement la possibilité de connaitres les dernières entrées insérées de la table.
- Des clés primaires qui peuvent changer sont tout simplement une mauvaise pratique
- Plusieurs articles trouvés sur le Web affirment que de manière générale, les ORM ont plus de facilité et plus sont plus efficaces avec des ID sur un seul champ.
Jul/090
Design de bases de données qui change souvent
On m’a demandé un jour ce que je recommande pour des designs de databases appelées à être modifiées régulièrement. La personne prévoyait avoir besoin d’ajouter des champs fréquemment pour répondre à ses besoins.
La première interrogation qui m’est venu en tête a été de lui demander pourquoi. Pourquoi a t’il besoin d’ajouter des champs et des tables de manière fréquente ? Pourquoi ne sait-il pas d’avance ce que fera son application ? La réponse est simple: la portion de l’application qui est déjà en ligne est complètement fonctionnelle, étudiée et testée. En fait, l’application est personnalisable et chaque client qui l’achète peut avoir des particularités différentes d’un autre client. D’où le besoin d’ajouter des champs et tables régulièrement.
Il n’y a pas de réponse magique à cette question. Il suffit d’y répondre avec “le gros bon sens”. D’un côté purement relationnel, la normalisation répond bien à ce besoin. Une base de données très normalisée offre une flexibilité exceptionnelle. Si la base de données contient déjà beaucoup d’information, un bon design normalisé peut éviter des scripts de migration qui peuvent être lourds à exécuter.
Le problème avec les bases de données “trop” normalisées est qu’elles sont parfois moins performantes. La flexibilité et la performance font rarement bon ménage. De plus, les requêtes deviennent plus complexes à écrire et maintenir. On s’éloigne également des designs orientés objet qui sont souvent adoptés dans les langages OO comme Java et PHP. C’est un compromis qu’il faut être prêt à faire.
Le défi survient lors de l’ajout d’un champ sur une table déjà existante. Il est facile de créer une dénormalisation qui répond bien à un besoin immédiat, mais crée une pénalité à long terme. C’est un problème auquel je suis régulièrement confronté. Une table qui possédait 10 champs originalement en possède 25 trois ans plus tard. Lorsqu’on se rend compte de l’inefficacité de ce schema, il est souvent complexe et très coûteux de refactoriser la table.
Schématiser la database sur papier (avec Workbench ou d’autres Database Designer) est un bon truc pour identifier des problèmes et les résoudre.
En résumé, n’ayez pas peur de normaliser vos tables, mais n’abusez pas ! Surtout, n’attendez pas qu’il soit trop tard. La quantité de code à modifier peut être une raison valable pour ne pas le faire. Évitez de vous retrouver dans cette situation. Faites-le dès que le besoin se présente!
Jun/090
Connexion Master Slave: erreur commune
Je vois régulièrement des diagrammes d’architecture où les flèches pour la connexion du Slave / Master sont dans le mauvais sens. Il faut savoir qu’avec MySQL, c’est le Slave qui se connecte au Master pour aller chercher le binlog, et non l’inverse. Ça devrait donc donner un diagramme comme ceci:
Jan/098
MyISAM ou InnoDB ?
Un ami me demandait aujourd’hui si une application qu’il utilise à son travail pourrait être plus performante si les tables étaient Innodb plutôt que MyISAM ? Oui. Non. Peut-être. Il n’y a pas de réponse à cette question; il y beaucoup trop de facteurs à considérer.
Peter Zaitsev a publié hier un article sur le sujet. Comme il indique, il faut d’abord s’interroger pour savoir “pourquoi” les tables sont MyISAM à la base. Le sont-ils pour une raison particulière ou parce qu’elles utilisent le storage engine par défaut de MySQL ?
Les 2 storages engines possèdent des avantages différents qui les rendent aussi performant l’un que l’autre, dépendamment de l’utilisation qu’on en fait. Pour répondre adéquatement à mon ami, il aurait fallu que je connaisse quel genre de requêtes son application fait, à quelle fréquence, combien de connexions simultanées utilisent la base de données, etc.. À défaut de connaître toutes ces informations, voici quelques différences qui peuvent vous faire opter pour InnoDB plutôt que MyISAM.
En fait, il faut savoir que de manière générale, là où MyISAM brille, InnoDB est hors compétition, et vice versa. MyISAM est reconnu pour sa vitesse d’écriture, sa faible consommation de mémoire et d’espace disque, sa rapidité à retourner un count(*) et les fulltext index.
InnoDB lui consomme plus de mémoire et d’espace disque, les requêtes d’écritures sont moins rapides que MyISAM, les count(*) sont excessivement lents et il ne supporte pas les fulltext index. Il requiert de modifier la configuration par défaut pour être performant alors que MyISAM performe bien avec la configuration par défaut. À son avantage, il possède du row-level locking, des transactions (acid compliant), des clustered primary key index et son implémentation permet une meilleure “concurrency” (plusieurs opérations simultanées). Lorsque configuré adéquatement, il devient très performant.
Lorsque la concurrence devient un enjeu, qu’il y a beaucoup de requêtes d’écriture et de lecture, InnoDB est le meilleur choix. Plusieurs personnes affirment qu’il devrait être le storage engine par défaut; il est vrai qu’il offre beaucoup plus d’avantages que MyISAM et qu’il offre de meilleures performances en générale (c’est mon avis). Par contre, il ne faut pas négliger les autres storages engines, car ils peuvent être beaucoup plus efficace dans certains cas. Dans le cours de Performance Tunning, on nous enseigne à savoir où, quand, comment et pourquoi un storage engine sera meilleur qu’un autre.
Oct/0810
Je hais phpMyAdmin
Je hais phpMyAdmin. Ironiquement, je m’en sers encore beaucoup et pour me contredire davantage, je vais même avouer que c’est “relativement” un bon outil. Mais je le hais quand même.
PhpMyAdmin est l’outil qui m’a fait découvrir MySQL lorsque j’étais encore à l’école. C’est un outil idéal pour débuter en développement Web puisqu’il est intégré à des logiciels comme easyphp ou est souvent ajouté à LAMP (Linux Apache MySQL PHP). L’outil est convivial et permet de faire beaucoup d’opérations, même pour un utilisateur inexpérimenté en SQL. Plusieurs hébergeurs Web l’offrent dans leurs plans puisqu’il est gratuit, simple à installer et facile à maintenir.
Mais quand on connait bien le SQL, plus précisément MySQL, et qu’on s’en sert régulièrement comme je le fais, on en vient à constater ses lacunes. Donc, dans le top 5 des choses que je déteste de phpMyAdmin:
- La création des Foreign Key
Il n’y a rien de plus simple dans phpMyAdmin que de créer une base de données et des tables. Pourtant, appliquer les concepts de clés étrangères est un exercice complexe qu’il n’est pas possible de faire à la création de la table. Une des plus grandes forces de phpMyAdmin est donc intimement liée à une de ses plus grandes faiblesses. Ce manque incite les nouveaux utilisateurs de MySQL à ne pas créer de clés étrangères. - Les int(11)
Je vois régulièrement des int(11) chez les utilisateurs de phpMyAdmin. Contrairement au char et varchar, la “longueur” des types numérique n’est pas une limite imposée. Un int(9) unsigned possède le même nombre maximal qu’un int(11). L’ironie, c’est que la longueur maximale d’un int unsigned, 4294967295, est de 10 chiffres ce qui fait de la valeur par défaut de phpMyAdmin, le int(11), un non-sens. Le seul et unique impact du nombre qu’on peut attribuer à un type numérique est lorsqu’on utilise l’attribut Zerofill. Dans ce cas, le nombre sera “padder” de 0 jusqu’à la longueur désirée. Onze (11) n’est pas une limite non plus. J’ai créé un int(100) unsigned zerofill, inséré la valeur 1, et il y avait bien 99 zéros devant mon 1 lorsque j’ai fait un SELECT. Le comportement par défaut de phpMyAdmin est trompeur. - Les LIMIT 0,30
Pour que le résultat d’une requête s’affiche rapidement, phpMyAdmin n’affiche pas la totalité des enregistrements. Il ajoute automatiquement un “LIMIT 0,30″ à chaque requête. La différence sur le temps d’exécution est remarquable, mais cette technique n’est vraiment pas pratique. - Son dynamisme
Je donne 2 sur 10 au dynamisme. J’ai eu un “vieux” phpMyAdmin designé pour MySQL 4 qui se connectait à un MySQL 5. Beaucoup de nouveaux status (show [global] status) et de nouvelles variables (show [global] variables) n’étaient pas présents dans la page qui sert à les afficher. Pourtant, si on exécute la requête manuellement les valeurs sont bien présentes. Rien de très dynamique là dedans. De plus, j’aimerais avoir accès à un “textbox” en tout temps dans le but de taper des requêtes SQL. Actuellement, je dois cliquer sur la section “SQL” qui pop une fenêtre et le résultat de celle-ci s’affiche dans une nouvelle page. Je déplore aussi le fait que je dois constamment modifier le fameux LIMIT 0,30 pour mettre une autre limit, forçant à ré-exécuter la requête et réafficher le résultat. Ce sont des choses qui pourraient aisément être évitées grâce à AJAX. - Sa facilité à commettre des gaffes
Deux cliques, c’est suffisant pour supprimer des rows, vider une table ou même une base de données entière ! Lorsqu’on tape les statements dans le CLI, nous sommes beaucoup plus alertes et conscients de ce que nous faisons. Nous perdons un peu le sens des responsabilités avec phpMyAdmin car on clique, clique et clique encore et on finit par commettre des choses irréversibles, rapidement. L’interface de gestion des privilèges est chaotique. Je préfère de loin créer les privilèges en tapant directement les statements nécessaires pour être sur de ne pas commettre d’erreur.
En résumé, phpMyAdmin demeure en bon outil malgré tout, mais ces 5 points me font rager plusieurs fois par semaine. Les outils fournir par MySQL possèdent beaucoup de faiblesses (et de bugs!). À défaut d’avoir de meilleurs outils graphiques, j’alterne entre le client en command line (le “CLI”) et phpMyAdmin.
Jul/082
Trouver les indexes inutiles
Arjen Lentz a écrit un article très intéressant pour trouver les indexes inutilisés. En gros, il explique la règle du 30% pour les indexes. Pour ceux qui ne la connaissent pas, les indexes ne sont pas utilisés si environs 30% des valeurs indexées sont les mêmes. Arjen propose donc une requête qui permet de trouver quel indexes sont inférieurs à 30% grâce à la cardinalité.
Mais attention! Sa technique est un peu controversée. Premièrement, il faut savoir que les 30% sont une approximation. Les indexes peuvent être utilisés si 25% ou 40% des valeurs sont pareil.
J’ai fait mes propres tests sur la base de données avec laquelle je travail au boulot et les résultats m’ont assez étonné. EXPLAIN indiquait qu’un index avec une cardinalité de 4 (la table possédait 3422 000 rows) était utilisé. C’est beaucoup plus que 30%. J’ai ensuite ajouté un indexe sur un champ dont je savais les valeurs identiques à 99%. L’index était également utilisé !
La règle du 30% ne faisait aucun sens selon mes tests jusqu’à ce que je réalise que toutes les tables avec lesquelles je testais étaient InnoDB. Les résultats étaient tout à fait différents après avoir modifié les tables pour MyISAM. La règle est bonne.
La raison pour laquelle InnoDB utilise l’index même si 99% des valeurs sont identiques demeure relativement étrange. Comme vous devez le savoir, InnoDB possède des covering index et plein d’autre statistique se qui le rend apte à décider si l’utilisation de l’index est nécessaire ou pas. De plus, la cardinalité avec InnoDB est une estimation; on ne peut donc pas se fier à la valeur retournée comme valeur sûre. ANALYZE TABLE met à jour la cardinalité des indexes, mais ça demeure une estimation et les résultats peuvent varier si on roule le statement plusieurs fois.
ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing ten random dives to each of the index trees and updating index cardinality estimates accordingly. Note that because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate as it doesn’t take all rows into account.
En résumé, la règle du 30% est vraie et relativement fiable, mais elle ne s’applique pas à InnoDB. De plus, même si vous trouvez des indexes avec une petite cardinalité sur une table MyISAM, ca ne veut pas nécessairement dire qu’il est inutilisé. Utilisez votre jugement à savoir s’il est pertinent de l’enlever.
Jun/085
Stocker des fichiers dans MySQL
Est-il mauvais de stocker des fichiers dans MySQL ? Il n’y a pas de bonne ou mauvaise réponse à cette question. Tout dépend de vos besoins. Personnellement, je préfère stocker les fichiers à l’extérieur de la base de données pour les raisons suivantes:
- Le filesystem va mieux cacher les fichiers
- Le serveur MySQL va avoir plus de facilité à cacher les autres données
- Le débit de donnée du serveur va être moins élevé
- Il est plus facile de réorganiser et maintenir les fichiers
- Le tablespace demeure petit (si vous devez utiliser InnoDB)
Une bonne approche est de stocker un pointeur vers les fichiers sur le filesystem plutôt que le binaire du fichier directement dans la BD. Il y a cependant des avantages à les stocker dans la base de données:
- Toutes les données sont centralisées à une place pour les backups
- C’est plus simple à gérer avec la réplication et/ou du load balancing
- Il est possible de demeurer ACID compliant.
J’ai eu une situation qui aurait pu devenir très problématique dernièrement lorsque je devais faire la maintenance de la base de données de l’application RT. Pour faire une histoire courte, RT stock des attachements d’email dans une table InnoDB et le tablespace d’InnoDB est devenu corrompu lorsqu’il a atteint 40 Go. Lorsqu’on sélectionnait des enregistrements précis de la table Attachements, le serveur plantait et mysql_safe le repartait automatiquement. Heureusement, il s’agissait d’un Slave. Comme nous avions qu’un seul slave, nous avons du le “reconstruire” à partir du Master. 40Go de données, c’est long à backuper et restaurer et le downtime que ça a créé était considérablement long. Un downtime est toujours trop long. Sur les 40Go, environ 30 étaient des fichiers d’attachements. Ça aurait été beaucoup plus simple et rapide si les fichiers n’étaient pas stockés dans MySQL…
Apr/080
Modèle de données
Robin Schumacher, directeur Product Management de MySQL, a écrit un article avec lequel je suis 100% d’accord.
“Why you want to be good at data modeling”
http://dev.mysql.com/tech-resources/articles/why-data-modeling.html
Il est vrai que trop souvent, les entreprises n’accordent pas suffisamment de temps et d’importance à leur modèle de données. Certains designs créés “on the fly” durant le développement d’une application peuvent tenir la route un long moment. Cependant, si la DB prend de l’ampleur (en nombre de tables, d’utilisateurs et de requêtes), ces designs peuvent finir par offrir de piètre performance. Même les meilleures requêtes possibles deviennent inefficaces.
Un article qui confirme bien ce que je croyais.