mardi 30 décembre 2008

MySQL Cluster 6.4

La version 6.4 du moteur de stockage NDB utilisé par MySQL Cluster pointe son nez et nous offre les nouvelles fonctionnalités suivantes :

- Possibilité d'ajouter des noeuds et des groupes de noeuds online, c'est à dire sans recréer totalement le cluster et donc d'avoir un arrêt de service non négligeable
- Support du multithreading au niveau des datanodes
- Base NDB$INFO permettant d'avoir accès aux informations du cluster
- Disponibilité sous Windows

Pour les fonctionnalités apportées par chaque version 6.x vous pouvez vous rendre sur la roadmap du développement de MySQL Cluster. Les versions 6.x étaient disponibles en GPL ou en version commercial dans l'édition Carrier Grade de MySQL Cluster qui maintenant est obsolète et devient tout simplement MySQL Cluster. Toutes les sources (GPL/mysql et Commerciales/mysql-com) sont disponibles ici.

Concernant les performances, le blog de Jonas Oreland, Leader de l'équipe d'ingénieur MySQL (Cluster) depuis Août 2003, annonce des résultats très probants, avec par exemple 950 000 lectures/seconde sur un seul datanode grâce au multithreading du processus ndbd, dont le nouveau binaire est ndbmt, et jusqu'à 600 000 écritures/seconde. Et selon Mikael Ronstrom, Architecte logiciel sénior chez MySQL AB (SUN), on peut donc imaginer atteindre des dizaines de millions de lectures/seconde en multipliant le nombre de noeuds de données.

Ce sont donc de très bonnes nouvelles pour les heureux utilisateurs de MySQL Cluster et d'après Jonas on peut s'attendre à avoir une beta-release pour l'année 2009. Il faudra donc encore être patient mais on devrait pouvoir en profiter bientôt.

Enfin, pour ceux qui utilisent le moteur 6.3 je vous conseille de passer au moins à la version 6.3.19, si vous en avez la possibilité, car elle apporte une modification dans la création des redos en ne flushant sur disque qu'après la création du fichier et non plus tous les 32k blocs, ce qui ralentissait énormément le temps d'initialisation d'un noeud. Ainsi le blog de Shinguz a pu diviser le temps d'initialisation d'un noeud par 8.
Blogged with the Flock Browser

mercredi 17 décembre 2008

Calculer une durée en MySQL

Il est très simple avec PostgreSQL de calculer une différence entre deux dates avec simplement une commande du genre :

SELECT TIMESTAMP '2008-12-17 09:17:31' - TIMESTAMP '2008-12-15 11:28:06';


Mais qu'en est il avec MySQL ?

Eh bien ce n'est pas aussi simple ! En effet, MySQL dispose d'un tas de fonctions mais il ne surcharge pas l'opérateur de soustraction si les arguments sont des dates, à moins que vous utilisiez la syntaxe date-INTERVAL X UNIT
Il faut donc jongler avec la fonction TIMESTAMPDIFF pour obtenir le même résultat qu'avec Postgresql et ne pas rencontrer des problèmes avec les heures d'hiver/d'été.

Vous utiliserez donc la requête suivante :

SELECT @s:= '2008-12-15 11:28:06',@e:='2008-12-17 09:17:31',CONCAT(IF((@days:=TIMESTAMPDIFF(DAY,@s,@e))>0,CONCAT(@days,IF(@days>1,' days ',' day ')),''),SEC_TO_TIME(TIMESTAMPDIFF(SECOND,@s,@e)-86400*@days)) duree;

Vous remarquerez que j'utilise des variables utilisateurs afin de ne pas alourdir la requête en répétant les dates de début et de fin.
Blogged with the Flock Browser

mercredi 10 décembre 2008

Ordres DDL sur un cluster Slony

Introduction

Il est important de connaître les différentes méthodes pour exécuter des instructions DDL en production dans un environnement utlisant Slony.

Première méthode


Nous considérerons dans cette partie que le fichier slon_tools.conf est correctement configurées.

Créer un script SQL pour chaque SET Slony qui doit être modifié.

alter-set1.sql :

ALTER TABLE table1 ADD COLUMN vcol1 VARCHAR(5000),
ADD COLUMN icol2 smallint,
ADD COLUMN ccol3 CHAR(10);
ALTER TABLE table2 ADD COLUMN vcol1 VARCHAR(5000),
ADD COLUMN icol2 smallint,
ADD COLUMN ccol3 CHAR(10);
ALTER TABLE table3 ADD COLUMN vcol1 VARCHAR(5000),
ADD COLUMN icol2 smallint,
ADD COLUMN ccol3 CHAR(10);

alter-set2.sql :
ALTER TABLE table4 ADD COLUMN vcol1 VARCHAR(5000),
ADD COLUMN icol2 smallint,
ADD COLUMN ccol3 CHAR(10);
ALTER TABLE table5 ADD COLUMN vcol1 VARCHAR(5000),
ADD COLUMN icol2 smallint,
ADD COLUMN ccol3 CHAR(10);

Il ne reste plus qu'à effectuer les modifications de structures sur chacun des sets en exécutant les commandes suivantes sur n'importe quel noeud du cluster Slony.

slonik_execute_script 1 /scripts/alter-set1.sql
slonik_execute_script 2 /scripts/alter-set2.sql

Le script slonik_execute_script va exécuter la commande Slony EXECUTE SCRIPT avec les arguments adéquates sur le noeud source de chaque SET, et cet ordre sera propagé sur les noeuds cibles. Le principal problème dans l'utilisation de cette commande est qu'elle vérrouille toutes les tables de la base durant l'exécution des scripts SQL ce qui peut être très problématique pour un service 24/7

Seconde méthode


Cette méthode est valable pour l'ajout de colonnes mais non la suppression.


Comme dans le cas précédent, vous disposez des scripts alter-set1.sql et alter-set2.sql nécessaires pour effectuer les modifications de structures. Exécuter sur chacun des noeuds cibles (subscribers) du cluster Slony les scripts SQL :

psql -U mon_user ma_base < /scripts/alter-set1.sql
psql -U mon_user ma_base < /scripts/alter-set2.sql

Créer 2 nouveaux scripts comme suit :

update_triggers-set1.sql

UPDATE pg_trigger SET tgargs=substring(tgargs for octet_length(tgargs)-1)||E'vvv\000'
WHERE tgname='_mon_cluster' AND tgrelid IN
(SELECT oid FROM pg_class WHERE relname IN ('table1','table2','table3'));

update_triggers-set2.sql

UPDATE pg_trigger SET tgargs=substring(tgargs for octet_length(tgargs)-1)||E'vvv\000'
WHERE tgname='_mon_cluster' AND tgrelid IN
(SELECT oid FROM pg_class WHERE relname IN ('table4','table5'));


Le nombre de caractères v dans la requête de mise à jour correspond au nombre de nouvelles colonnes. Si nous avions ajouté une seule colonne, nous aurions concaténé la valeur E'v\000' au lieu de E'vvv\000'

Dans le cas de la modification du type d'une colonne il n'y a pas besoin de mettre à jour le trigger Slony de la table concernée puisque le nombre de colonnes reste inchangé.


Puis exécuter les scripts suivants sur le noeud source et mettre à jour les triggers Slony :

psql -U mon_user ma_base < /scripts/alter-set1.sql
psql -U mon_user ma_base < /scripts/update_triggers-set1.sql

psql -U mon_user ma_base < /scripts/alter-set2.sql
psql -U mon_user ma_base < /scripts/update_triggers-set2.sql

Cette méthode permet de ne pas utiliser la commande EXECUTE SCRIPT et donc ne pas vérrouiller toutes les tables de la base.

Conclusion

Ce document a permis de comprendre qu'il y a une autre méthode que l'utilisation de la commande EXECUTE SCRIPT pour mettre à jour la structure des tables répliquées par Slony (dans le cas de l'ajout/modification de colonnes), ce qui permet de réduire considérablement l'impact de ces modifications sur une base de production 24/7.

mardi 9 décembre 2008

Utilisation de LAST_INSERT_ID()

La fonction MySQL LAST_INSERT_ID() permet de récupérer la valeur du dernier incrément généré lors de l'insertion de données dans une table contenant une colonne auto_increment.
Il peut être utile de récupérer cette valeur pour la réinjecter dans une seconde table. J'ai par exemple sur mon serveur les 2 tables suivantes :

mysql [localhost] {msandbox} (test) > select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | c |
| 2 | c2 |
| 3 | c3 |
+----+------+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t2;
+------+------------+
| id | dt |
+------+------------+
| 2 | 2008-12-09 |
+------+------------+
1 row in set (0.00 sec)

Ayant eu l'occasion de vérifier le code de certains développeurs j'ai repéré du code utilisant cette fonction mais faisant appel à la table en question sans pour autant que cela soit nécessaire pour le résultat. Je me suis alors posé la question suivante : Y a t il un impact à faire appel à la table sans que cela soit nécessaire ?
Si on regarde les 2 plans d'exécution cela donne :

mysql [localhost] {msandbox} (test) > explain select last_insert_id();
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select last_insert_id() from t2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)


L'appel à la table semble avoir besoin d'accéder à une ligne. Cela semble louche...

Vous l'aurez compris il faut bien sûr bencher pour savoir s'il y a une grosse différence. Pour cela MySQL met à disposition la fonction BENCHMARK() qui n'est pas très connue :

mysql [localhost] {msandbox} (test) > insert into t1(name) values('c4');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > SELECT BENCHMARK(1000000,(SELECT last_insert_id() from t2));
+------------------------------------------------------+
| BENCHMARK(1000000,(SELECT last_insert_id() from t2)) |
+------------------------------------------------------+
| 0 |
+------------------------------------------------------+
1 row in set (5.09 sec)

mysql [localhost] {msandbox} (test) > SELECT BENCHMARK(1000000,(SELECT last_insert_id()));
+----------------------------------------------+
| BENCHMARK(1000000,(SELECT last_insert_id())) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (0.03 sec)


Voilà donc notre réponse. Accéder à la table, chose inutile pour notre résultat, prend plus de temps.

lundi 8 décembre 2008

Calcul de la médiane en SQL

Depuis Oracle 10g il est possible d'effectuer le calcul de la médiane très facilement en utilisant la nouvelle fonction MEDIAN disponible.
Je suppose que vous disposez d'une table data constituée de 2 colonnes semaine et duree. Pour calculer la médiane sous Oracle vous n'avez plus qu'à utiliser la requête suivante :

SELECT semaine,MEDIAN(duree) FROM data GROUP BY semaine;

Pour MySQL c'est beaucoup plus compliqué puisqu'on ne dispose pas de cette magnifique fonction. J'ai donc fait quelques recherches et je suis tombé sur les travaux de frédéric Brouard. J'ai pu trouver la procédure adéquate basée sur la solution de Chris Date.

En supposant que vous disposez toujours de la table data qui chez moi utilise le moteur CSV, la méthode est la suivante :

CREATE TABLE d AS SELECT * FROM data;
ALTER TABLE d ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST, ADD INDEX (semaine,duree) ;

SELECT semaine,round(AVG(duree),2) AS MEDIANE
FROM (
   SELECT semaine,MIN(duree) AS duree
   -- valeurs au dessus
   FROM
   (
       SELECT ST1.semaine,ST1.id, ST1.duree
       FROM    d AS ST1
       INNER JOIN d AS ST2
       ON ST1.duree <= ST2.duree AND ST1.semaine=ST2.semaine
       GROUP BY ST1.id, ST1.duree
       HAVING COUNT(*) <= (SELECT CEILING(COUNT(*) / 2.0)
       FROM d d2 where d2.semaine=ST1.semaine)
   ) SUR
   GROUP BY semaine
   UNION
   SELECT semaine,MAX(duree) AS duree
   -- valeurs en dessous
   FROM
   (
       SELECT ST1.semaine, ST1.duree
       FROM    d AS ST1
       INNER JOIN d AS ST2
       ON ST1.duree >= ST2.duree AND ST1.semaine=ST2.semaine
       GROUP BY ST1.id, ST1.duree
       HAVING COUNT(*) <= (SELECT CEILING(COUNT(*) / 2.0)
       FROM d d2 where d2.semaine=ST1.semaine)
   ) SOU
   GROUP BY semaine
   ) T
GROUP BY semaine;

DROP TABLE d;

Sous Oracle, la requête est très rapide et il n'est pas nécessaire de créer un index particulier. Par contre sous MySQL, si vous vous amusez à supprimer l'index vous le ressentirez très vite si vous disposez de beaucoup de données. Dans mon cas, ma table contient 4874 lignes. Sans l'index, la requête dure près de 5 minutes contre moins de 5 secondes une fois indexée.
Lire la suite...