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.
Jan/101
L’importance des clés primaires avec mk-table-sync
J’adore Maatkit. Je m’en sers régulièrement dans toutes sortes d’occasions. Pour tous les Slaves que je configure, j’ajoute un petit script maison qui utilise mk-table-sync afin valider l’intégrité des données sur le Slave avec le Master. Ce petit script envoie un email avec les différences et les requêtes à exécuter le cas échéant.
J’ai remarqué que mk-table-sync possède certaines limitations, c’est-à-dire que le format de la table et l’encoding joue un rôle important sur la manière dont l’outil effectue ses comparaisons. Les tables sans clé primaire ou d’identifiant unique sont particulièrement problématiques, et c’est tout à fait compréhensible. Par définition, s’il n’y a pas d’identifiant unique, il est impossible d’être 100% sur que l’enregistrement #1 sur le Master correspond à l’enregistrement #1 sur le Slave. En étant limité à ce niveau, mk-table-sync préfère “croire” que l’ensemble des données sur le Slave sont erronés et propose des requêtes pour supprimer et réinsérer tous les enregistrements de la table.
Bien que je m’assure que toutes les tables possèdent des clés primaires lors de leur création, il m’est arrivé de me faire avoir. Le statement CREATE TABLE tableName SELECT * FROM tableName2 WHERE …. ; crée une table SANS index! Du coup, mon petit script s’est mis à envoyer des emails de 17Mo de requêtes SQL, ce que Thunderbird digère très mal 
La leçon est: si vous utilisez mk-table-sync, assurez-vous de TOUJOURS avoir un identifiant unique sur vos tables!
Jan/101
Resolutions Geeks de 2010
L’année 2009 a été une année chargée pour MySQL: après son achat par Sun, il passe aux mains d’Oracle. Ce dernier achat fait beaucoup jaser et plusieurs personnes s’interrogent sur l’avenir de MySQL. Je suppose que nous saurons en 2010 ce que Oracle compte faire de MySQL. Personnellement, je préfère attendre les publications officielles plutôt que de m’énerver sur les spéculations alarmistes qu’on a pu lire ces derniers mois…
De mon côté, l’année 2010 s’avère une année très mouvementée:
- Chez iWeb, je participe à un ambitieux projet qui devrait voir le jour au courant de l’été.
- Le soir et les fins de semaine, je passe la majorité de mon temps au HEC pour satisfaire les exigences de mes cours.
Malgré tout, j’ai quand même l’intention de (ce sont mes résolutions):
- Écrire sur mon blog plus régulièrement
- Être davantage présent chez les diverses communautés montréalaises et Open-Source
- Parfaire mes habiletés de ScrumMaster/Chef d’équipe
- Faire des recherches et tests plus sérieux avec Drizzle et MariaDB
Et vous, quelle sont vos résolutions geek 2010 ?
Dec/090
Retrospective du Demo Camp de Montréal
Comme plusieurs bons ScrumMasters, j’applique des principes de scrums dans ma vie quotidienne. Dans ce sens, je me permets de publier ma rétrospective du Demo Camp d’hier soir.
Dans les points négatifs à améliorer:
- Ne pas essayer de faire une présentation de 30 minutes en 10 minutes
- S’informer davantage sur la formule de présentation
- Prévoir que le Xorg de mon debian n’allait pas supporter les résolutions des mégas projecteurs
- Prévoir que simuler le crash d’une DB et la recouvrir live implique un certain nombre de risques
- Pratiquer la présentation, plus, beaucoup plus
Dans les points positifs à retenir:
- De la bière gratuite fournie par Microsoft
- Élargis mon réseau de contacts
J’ai spécialement aimé la présentation de DokDok.
Comme promis, je publie le script de backup que j’ai finalement à peine présenté. Anyway, pour les intéressés, il se trouve ici.
Nov/092
MySQL Sandbox: bravo !
J’ai souvent entendu parler de MySQL Sandbox. Pour effectuer des tests avec un Master/Slave en fin de semaine, j’ai décidé de l’essayer puisque je n’avais que mon laptop. MySQL Sandbox est un outil pour installer un ou plusieurs serveurs isolés, sans affecter les autres.
Wow! Juste Wow! MySQL Sandbox est un outil vraiment génial! J’ai pu créer une instance de MySQL Master avec 2 instances Slaves sur la même machine en moins de 1 minute! C’est le genre de tâche qui prend de 30 minutes à 1 heure lorsqu’un administrateur expérimenté le fait manuellement. MySQL Sandbox permet non seulement d’installer rapidement 1 ou plusieurs serveurs, il permet aussi d’installer des versions différentes en quelque instant !
De plus, des options prédéfinies permettent de créer un setup Master-Slave ou Master-Master automatiquement. Il vient avec des outils permettant d’effectuer des tâches d’administration complexes de manière excessivement simple. En résumé, il permet carrément de jouer avec MySQL.
Comment ça marche? On télécharge la version de MySQL qu’on souhaite installer. Ensuite, on peut
Créer une instance de MySQL:
make_sandbox /path/to/mysql-X.X.XX-osinfo.tar.gz
Créer un Master avec 2 Slaves:
make_replication_sandbox /path/to/mysql-X.X.XX-osinfo.tar.gz
Créer 4 serveurs avec une réplication circulaire (Master-Master)
make_replication_sandbox –circular=4 /path/to/mysql-X.X.XX-osinfo.tar.gz
Ces 3 commandes installent, configurent et démarrent le ou les serveurs MySQL. En quelques mots, MySQL Sandbox c’est simplicité, rapidité et fun!
Nov/092
Un bout de DBA au Career Demo Camp de Montréal
Mercredi le 2 décembre, je serai parmi les speakers au Carrer Demo Camp de Montréal. Le CDCM est un événement gratuit qui se déroule simultanement et dans les même locaux que le Microsoft Techdays de Montréal. Il est animé par mon ami Jean-Luc Sanscartier et Joey DeVilla.
Au menu, une démonstration sur une manière simple et efficace de faire des backups MySQL gratuitement.
Avis aux intéressés! Il reste encore quelques places!
Oct/090
Une belle histoire de Scaling
J’ai lu une histoire très intéressante aujourd’hui à propos de l’utilisation de MySQL chez SoftLayer. Il raconte comment ils ont atteind les limites de MySQL de 5 manières différentes avant de trouver “la” solution pour construire un datawarehouse. Une belle histoire de scaling!
http://sldn.softlayer.com/09/2009/building-the-data-warehouse/
Disponible en anglais uniquement…
Oct/090
SQL_MODE bonne ou mauvaise idée ?
MySQL est connu pour être très flexible avec la validation des données. Les conversions silencieuses ne sont pas des pratiques courantes parmi les autres SGBD. Au lieu de lancer des erreurs, MySQL lance des warnings, ce que la majorité des applications ne gèrent pas. (Est-ce que votre application fait un SHOW WARNINGS; à chaque requête?)
Néanmoins, la variable SQL_MODE permet de contrôler ce comportement. Plusieurs niveaux de validation peuvent donc être assignés, partant d’une validation quasi absente (par défaut) à une validation très stricte. Ce qui peut paraître comme une bonne affaire me parait plutôt comme une très mauvaise idée.
Le problème avec le SQL_MODE c’est que par défaut, la valeur est vide (oui oui!). Il n’y a pas de mode prédéfinie ce qui donne un comportement très souple. Plusieurs personnes ne savent pas que cette variable existe et construisent une application qui repose malheureusement sur cette souplesse. Lorsque votre application finie par en dépendre, c’est-à-dire qu’elle se comporte “normalement” avec cette absence de validation de données, vous risquez gros.
On peut insérer des dates impossibles comme le 31 février ou 0000-00-00 , des divisions par 0 dans des opérations mathématiques et des valeurs qui dépassent largement la limite possible des champs. Votre application fait peut-être tout ça, sans même que vous le sachiez! Elle ne produit aucune erreur puisque du côté de MySQL, il n’y a que des Warnings.
Imaginez le scénario: vous désirez loguer des transactions bancaires, mais un bug idiot fait que toutes les dates des transactions sont insérées avec un string quelconque, 1-janvier-2009 par exemple, ce que MySQL converti en 0000-00-00. Le champ pour stocker l’IP de la personne qui fait la transaction est un SMALLINT, tous vos IP atteignent la valeur maximum et sont convertis en 32767.
Un bon conseil: si vous débutez un nouveau projet, assurez-vous de mettre un SQL_MODE strict pour vous éviter des problèmes tôt ou tard ! Essayez-le sur une application existante pour voir comme elle réagit!
Le SQL_MODE peut être modifié par session ou globalement pour l’ensemble des usagés. Parmi les plus stricts, on retrouve
strict_trans_table: Si une valeur n’a pas pu être insérée dans une table transactionnelle sans modification, la commande est annulée. Pour une table non-transactionnelle, la commande est annulée si cela survient dans une ligne unique ou dans la première ligne d’une insertion multiple
traditional: MySQL se comporte comme un système SQL “traditionnel”. Une description simple est que ce mode “émet une erreur et non pas une alerte” lors de l’insertion d’une valeur incorrecte dans une colonne. Note : si vous utilisez un moteur de table non-transactionnel, les commandes INSERT/UPDATE s’arrêteront dès que l’erreur est repérée.