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.
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/098
MySQL Query Cache
La query cache de MySQL joue un rôle important dans la performance de plusieurs sites Web. Elle a pour avantage d’être transparente, c’est-à-dire que la ou les applications qui s’en servent n’ont pas besoin d’être modifiées.
J’ai recu la semaine passée la question suivante (je résume):
Je souhaite utiliser une cache pour le menu de mon site afin de le rendre plus performant. Puisque le contenu du menu ne change pratiquement jamais, est-il plus avantageux d’utiliser APC Cache que la Query Cache de MySQL puisque la communication s’effectue selon un schéma comme:
php -> cache_apc (ram)
php -> mysql -> query_cache (ram)Je souhaite réduire au minimum les requêtes SQL exécutées.
Personnellement, j’utiliserais la cache de MySQL. Tout d’abord, il faut savoir qu’il y a une énorme différence entre les 2 caches.
MySQL Query Cache est centralisée sur le serveur MySQL, c’est-à-dire qu’elle utilise la RAM de la machine du serveur MySQL. Elle possède un mécanisme d’invalidation basique, mais efficace. L’invalidation se produit lorsque des valeurs d’une table sont en cache, et que ces valeurs sont mise à jour par une application ou manuellement par un utilisateur.
APC Cache est centralisée sur le serveur Web, c’est-à-dire qu’elle utilise la RAM disponible sur le serveur qui roule Apache et PHP (en supposant que vous utilisez Apache). Vous serez responsable d’invalider la cache lorsque nécessaire. Si quelqu’un modifie la valeur directement dans MySQL, la cache possèdera la vieille valeur jusqu’à ce qu’un processus l’invalide.
Puisque les données ne changent pratiquement jamais, je ne me casserais pas la tête à réinventer la roue. MySQL fait déjà pour vous ce que APC ferait, sans le moindre effort. De plus, il est plus ou moins vrai de dire que d’appeler la cache correspond à une requête. Oui, la string SQL est nécessairement envoyé à MySQL, mais lorsque celui-ci la reçoit et valide que cette requête est cachée, il retourne immédiatement le résultat sans rien “processer”. C’est comme ça que fonctionne APC aussi, il lui faut un identifiant unique pour associer le résultat, tout comme fait MySQL avec le HASH de la requête.
Les caches (peu importe laquelle) sont tout aussi efficaces avec une petite requête qui consomme peu de ressource qu’avec une grosse qui en demande beaucoup. Il est donc plus avantageux de cacher les processus lourds que les légers.
Ce qu’il faut surtout se soucier lorsqu’on utilise une cache, c’est comment et à quelle fréquence il faut l’invalider. Lorsque la Query Cache de MySQL est activée, le processus de cacher les résultats et de les invalider s’effectue tout seul de manière invisible. Ainsi, d’autres requêtes que vous ne soupçonnez même pas bénéficient de la cache. À l’inverse, il faut modifier le code pour chaque requête que vous souhaitez cacher avec APC.
Sep/092
Sondage: Storage engine non officiel
Depuis quelques années, beaucoup de storages engines ont vue le jour pour répondre à des besoins que les storages engines fournis avec MySQL ne font pas bien, voir pas du tout. Selon moi, la majorité d’entre eux sont des projets qui risquent mourir dans un proche avenir. Toujours selon moi, pour que les projets gratuits et opensource demeurent longtemps, il faut qu’ils jouissent d’une certaine popularité, ce que la majorité des storages engines ne possèdent pas. De plus, ils doivent être en mesure de prouver qu’ils sont sans faille; on ne peut se permettre de perdre des données à cause d’un engine immature.
Malgré tout, je crois en un storage engine non officiel: Percona-XtraDB. Il est basé sur le populaire engine InnoDB, mais offre de meilleures performances tout en demeurant 100% compatible à ce que InnoDB peut accomplir. On peut donc remplacer aveuglement InnoDB par XtraDB sans craindre quoi que ce soit! On pourrait ainsi dire qu’il s’agit d’un nouveau InnoDB, plus rapide et plus robuste.
Comme son nom l’indique, il est principalement développé par Percona, une entreprise pour laquelle j’ai un très grand respect. Bien qu’il soit encore jeune, j’ai confiance qu’il atteigne une maturité très rapidement. Je suis de très près son évolution et j’ai déjà élaboré quelques tests avec.
J’aimerais connaitre vos opinions sur les storages engines non officiels.
Loading ...Aug/090
Importation d’un CSV
On m’a soumis un problème il y a quelques semaines (désolé du délai, c’est l’été pour tout le monde hein.. 
) à propos d’importation CSV qui était terriblement lente. J’ai reçu très peu d’information, donc voici des tests que j’ai effectués afin qu’il puissent être utilisés comme point de référence pour des comparaisons. Voici la problématique telle qu’on me l’a soumise
Pour un simple fichier CSV d’environ 1000 lignes (quelques Mo), j’ai largement le temps soit de manger, soit de faire une sieste, … pour une base de données MyISAM. Si j’utilise une base de données InnoDB, celle dont j’ai besoin, plus d’une journée. Je me base uniquement sur un seul utilisateur. A terme, j’aurai des To de données à gérer.
La Machine de tests:
Une VM avec 512 Mo de RAM avec un mauvais IO puisque d’autres VM roulent sur le même serveur. Voici un bout de la config que j’ai utilisé
key_buffer = 40M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 8
thread_concurrency = 2
table_cache = 384
sort_buffer_size = 768K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
La table de tests:
CREATE TABLE `csvimport` (
`a` int(11) NOT NULL,
`b` varchar(255) NOT NULL,
`c` varchar(255) NOT NULL,
`d` date NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8
et un fichier CSV de 1114112 lignes (106 Mo) que j’ai fabriqué avec cette technique.
mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (10.14 sec)
Records: 1114112 Deleted: 0 Skipped: 0 Warnings: 0
——————–
La même table converti en INNODB
mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (27.71 sec)
Records: 1114112 Deleted: 0 Skipped: 0 Warnings: 0
——————–
La même table converti en MyISAM
mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (6.31 sec)
Records: 1114112 Deleted: 0 Skipped: 0 Warnings: 0
——————–
Je refais les mêmes tests, mais avec une table surindexée.
ALTER TABLE csvimport ADD INDEX(a), ADD INDEX(b), ADD INDEX(c), ADD INDEX(d);
——————–
MyISAM
mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (41.44 sec)
Records: 1114112 Deleted: 0 Skipped: 0 Warnings: 0
——————–
INNODB
mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (1 min 4.90 sec)
Records: 1114112 Deleted: 0 Skipped: 0 Warnings: 0
Conclusion
MyISAM est reconnu pour sa rapidité d’insertion. On peut le remarquer avec les résultats. Il est clair qu’il y a un problème si vous avez le temps de vous faire un sandwich lorsque vous importez un CSV de 1000 lignes. Ce problème peut être de plusieurs natures. Il est très difficile de savoir d’où vient le problème sans faire quelques tests. Peut-être que le problème vient des disques, de la carte RAID, du CPU, d’une très mauvaise configuration, etc.. Ça peut être n’importe quoi.
Mar/093
Les tables temporaires
J’oublie parfois à quel point les tables temporaires peuvent être pratiques. On tente de sortir des rapports avec des tables qui ne sont pas prévues pour ça. On écrit des requêtes inimaginables et souvent très lentes. Résultat: on perd notre temps.
La solution: les tables temporaires ! Ce n’est pas très coûteux et drôlement pratique. Au lieu de prendre 25 minutes à écrire “une” requête qui prend un temps énorme à s’exécuter, prenez 10 minutes pour écrire 2-3 requêtes simples qui utilisent des tables temporaires et sortez le même résultat en quelques secondes!
Et si c’est efficace, performant (surtout performant) et récurrent, pourquoi ne pas transformer ces tables temporaires en view ?
Jan/091
General Log sur demande
Dans les nouveautés de MySQL 5.1, il y a l’activation / désactivation des logs sur demande. J’adore ce feature. Pour moi, c’est un outil de debug. Je m’en sers pour trouver des problèmes de performance ou de transactions qui sont souvent complexes à identifier dans une application avec une grosse architecture.
Le principe est simple: dans un environnement isolé, c’est-à-dire un environnement où vous savez que vous êtes le seul à travailler, accédez à l’interface problématique de votre application. Activez les logs avec la commande:
SET GLOBAL general_log = 1;
Lancez ensuite l’opération problématique. Lorsque terminé, désactivez de suite les logs:
SET GLOBAL general_log = 0;
Le log est maintenant rempli de toutes les requêtes effectuées durant l’opération. C’est là que commence l’analyse. Regarder les requêtes, la quantité, l’ordre dans lequel elles sont faites, la gestion des transactions, etc.. Il n’est habituellement pas souhaitable de voir:
- Plusieurs requêtes identiques – vous devriez songer à un système de cache
- Plusieurs requêtes consécutives pour aller chercher une étendue d’enregistrement de la même table (ex: .. WHERE id = 1; .. WHERE id =2; .. WHERE id = 3; .. WHERE id = 4;)
- Plusieurs connexions différentes (rappelez-vous, il s’agit d’une opération déclenchée par un seul utilisateur)
- Plusieurs transactions simultanées.
Les logs peuvent vous démontrer plusieurs problèmes différents. J’ai découvert qu’un processus peu performant d’une application envoyait jusqu’à 5Mo de requêtes SQL. Si on estime qu’une requête SQL fait environ 100 octets, imaginez le nombre de requêtes effectuées pour se rendre à 5Mo.
Il est important de désactiver de suite les logs, car ceux-ci rendent le serveur lent et utilise abusivement de l’espace disque. De plus, l’analyse du problème peut être faussée si quelqu’un accède à votre environnement et effectue des opérations. Et surtout, n’activez JAMAIS les logs sur un serveur en production. J’ai déjà vu un log augmenter de plusieurs centaines de Mo à la minute.
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.