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.
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!
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.
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
Comment promouvoir sécuritairement un Slave en Master
Promouvoir un Slave en Master est une opération qu’un DBA doit faire à l’occasion, parfois pour mettre à jour une nouvelle version ou parfois pour se sortir de la m****, car le Master a rendu l’âme. C’est une opération qui n’est pas tellement difficile, mais qu’il ne faut surtout pas négliger.
1. Vérifier la configuration du Slave
Vérifiez que la configuration est semblable à celle du Master, car si vous avez promu le Slave pour vous sortir de la m****, il faut qu’il puisse soutenir la même charge que le Master avait. Gardez en tête que le Slave a originalement été configuré pour être un Slave. Il possède donc des configurations qui lui sont propres. Un bon DBA a probablement pris la peine de mettre le Slave read_only; c’est une des premières options à enlever pour promouvoir. Assurez-vous également que l’ensemble des bases de données soit répliqué, mais surtout la DB mysql pour que les users/password soient les mêmes.
Après avoir promu le Slave en Master, il est toujours possible d’avoir les informations de réplication en tapant : show slave status; Si c’est le cas, vous n’avez pas complété une étape très importante: supprimer complètement la configuration de réplication. Négliger cette étape fait en sorte que votre nouveau Master a toujours la possibilité de se connecter à un autre serveur MySQL pour répliquer les données. Il ne faudrait en aucun cas que le nouveau master commence à répliquer les données d’un autre serveur. Pour éviter que ça se produise, il suffit de faire:
-
CHANGE MASTER TO
-
MASTER_HOST = '',
-
MASTER_USER = '';
2. Les backups
Si le Slave était utilisé pour faire des backups, il est important (le mot est faible) de désactiver tous les crons (ou autres) de backups. Surtout si comme moi, vous utilisez les bonnes vieilles méthodes “old school” comme les copies binaires ou un dump sql à tous les X heures qui nécessitent d’arrêter ou mettre en read only le service. Imaginez que votre nouveau Master s’éteint magiquement à minuit pour faire une copie… oups !
3. La topologie réseau
Il est possible avec MySQL se configurer une réplication via un réseau public. Ça peut paraître bête, mais valider avant tout que les applications qui se connectaient avant puissent toujours se connecter sur le nouveau Master.
Ce sont trois petits points à valider avant d’entreprendre le “switch” Slave – Master. J’encourage tout le monde à le faire même si vous ne prévoyez pas faire de changement. Ce n’est pas très long à faire et ça vous évitera beaucoup de stress si une catastrophe survient.. !
Jun/095
Comment convertir une DB de latin1 à UTF8
Un des défis d’être francophone consiste à bien gérer l’encoding. Je ne connais aucune personne n’ayant jamais eu de problème un jour où un autre. On m’a déjà approché pour trouver la manière la plus efficace de convertir une base de données de Latin1 à UTF8. Voici ce que j’ai trouvé. Ce n’est peut-être pas la manière la plus efficace, mais je n’ai pas eu problème après l’avoir testé. J’ai utilisé cette technique pour convertir une base de données de 115Go et tout c’est bien déroulé.
L’astuce consiste à transformer les chaines de caractères à un format binaire, pour ensuite les reconvertir en UTF8.
-
ALTER DATABASE myDbNameDEFAULT CHARACTER SET utf8;
-
-
ALTER TABLE Groups DEFAULT CHARACTER SET utf8;
-
-
ALTER TABLE Groups MODIFY Domain VARBINARY(64) NULL DEFAULT NULL,
-
MODIFY Type VARBINARY(64) NULL DEFAULT NULL,
-
MODIFY Description VARBINARY(255) NULL DEFAULT NULL,
-
MODIFY Name VARBINARY(200) NULL DEFAULT NULL;
-
-
ALTER TABLE Groups MODIFY Domain VARCHAR(64) CHARACTER SET utf8 NULL DEFAULT NULL,
-
MODIFY Type VARCHAR(64) CHARACTER SET utf8 NULL DEFAULT NULL,
-
MODIFY Description VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL,
-
MODIFY Name VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL;
That’s it ! Super simple et efficace. Un coup qu’une string originalement Latin1 est convertie en format binaire, il n’y a plus de character set ni de collation qui tiennent. Elle devient un format binaire au même titre qu’une image JPG par exemple. Ensuite, on reprend ce format binaire et on réapplique un character set (et la collation par défaut dans mon exemple).
Il faut cependant ne pas négliger un aspect lors de cette conversion. Les caractères Latin1 utilise 1 byte par caractère alors qu’un caractère UTF8 utilisent 3 bytes par caractère. Si l’espace disque est une de vos contraintes, il ne faut pas prendre cette conversion à la légère!
May/092
La gestion des IP dans MySQL
La gestion des IP dans MySQL est très simple. Premièrement, il faut savoir que la manière la plus efficace de stocker un IP et de le représenté sous une forme numérique, soit un INT UNSIGNED (donc 4 bytes) plutot qu’un CHAR(15) de 15 bytes.
Il demeure malgré tout possible de manipuler les IP avec leur forme alphanumérique en utilisant 2 function de MySQL: INET_ATON() et INET_NTOA().
mysql> SELECT INET_ATON('192.168.20.76');
+----------------------------+
| INET_ATON('192.168.20.76') |
+----------------------------+
| 3232240716 |
+----------------------------+
mysql> SELECT INET_NTOA(3232240716);
+-----------------------+
| INET_NTOA(3232240716) |
+-----------------------+
| 192.168.20.76 |
+-----------------------+
Si vous avez voulez savoir si un IP fait parti d’un sous reseaux, vous pouvez faire des manipulations bitwise:
SET @myIP := INET_ATON('192.168.20.76');
SET @theNetMask = INET_ATON('255.255.255.255');
-- La premiere addresse du subnet s'écrit (@myIP & @theNetMask) -- et la dernière (@myIP | ~ @theNetMask & 0xffffff); -- Donc, pour savoir si un ip fait parti d'un sous-réseaux:
SELECT INET_ATON('192.168.20.0')
BETWEEN (@myIP & @theNetMask)
AND (@myIP | ~ @theNetMask & 0xffffff);
Si on désire connaitre chaque parti du IP à partir de sa notation numérique, on peut faire:
SET @myIP := INET_ATON('192.168.20.76');
SELECT @myIP, (@myIP >> 24) as firstOctet,
(@myIP>>16) & 255 as secondOctet,
(@myIP>>8) & 255 as thirdOctet,
@myIP & 255 as fourthOctet;
+------------+------------+-------------+------------+-------------+ | @myIP | firstOctet | secondOctet | thirdOctet | fourthOctet | +------------+------------+-------------+------------+-------------+ | 3232240716 | 192 | 168 | 20 | 76 | +------------+------------+-------------+------------+-------------+
Notez que les 2 fonctions sont limité à 32bits, donc il ne fonctionne que pour les IPv4. De plus, l’utilisation des methodes rend impossible l’utilisation des indexes lors d’une recherche. Il est préférable de transformer à l’avance le IP avec la fonction PHP ip2long() par exemple.
Mar/092
Truc rapide pour faire un csv avec MySQL
Je dois régulièrement créer des rapports pour la comptabilité (et d’autres gens moins à l’aise avec des ordinateurs) au boulot. Le moyen facile est de leur envoyer le tout dans un fichier CSV converti en excel par email.
Il y a plusieurs manières de créer un CSV à partir de MySQL. Voici la manière que je qualifie de “standard”:
-
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
-
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-
LINES TERMINATED BY '\n'
-
FROM test_table;
Une manière un peu plus rapide (trouvé en fouillant sur google):
-
mysql -umyUser-p dbName -B -e "SELECT a,b,a+b FROM test_table;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv
Et maintenant.. (roulement de tambour).. “MA” manière!
-
CREATE TABLE test_table_csv SELECT a,b,a+b FROM test_table; ALTER test_table_csv ENGINE = csv;
Maintenant, si vous allez voir dans /var/lib/mysql/dbName/ vous y trouverez un fichier csv nommé test_table_csv.CSV. MySQL s’est occupé de la syntaxe “compliqué” pour nous! Le seul inconvénient de cette manière est que vous devez avoir les permissions pour lire /var/lib/mysql.