Archive pour le mot-clef ‘pratique’

Désactiver les clés étrangères

Mercredi 15 juillet 2009

Une petite « astuce » pour se remettre dans le bain du blogging… Comme vous avez pu le constater la fréquence de mise à jour a un peu diminuée depuis notre retour de la MySQL Conf, corrigeons cela avec cette petite remise en jambe à classer dans la catégorie « pratique ».

La désactivation du contrôle des clés étrangères est intéressante lorsque vous devez exécuter sur votre serveur MySQL un script de création de tables par exemple. Il se peut dans ce cas que l’ordre de création des différentes tables ne soit pas « logique ».

J’entends par là qu’une table A peut contenir une contrainte basée sur une clé étrangère référençant le champ d’une table B… qui n’existe pas encore ! La « logique » voudrait que les tables soient inscrites dans le script selon les liens qui existent entre elles mais cela n’est pas toujours le cas.

Cette opération de classement pouvant s’avérer fastidieuse à effectuer manuellement, vous pouvez vous affranchir des vérifications effectuées par le SGBD concernant les clés étrangères en utilisant la commande suivante :

mysql> SET foreign_key_checks = 0;

Vous êtes ainsi tranquille le temps de créer vos tables. Réactiver ce contrôle n’est pas plus compliqué :

mysql> SET foreign_key_checks = 1;

Le chapitre de la documentation correspondant aux foreign keys nous indique que c’est d’ailleurs la technique employée par mysqldump.

Sauvegarder ses procédures stockées avec mysqldump

Mardi 7 avril 2009

Une fois n’est pas coutume, un billet qui se lit en 10s :

Pour exporter vos procédures stockées grâce à mysqldump, n’oubliez pas l’option -R. Sans elle votre prochaine restauration risque de vous apporter quelques surprises. Si les triggers sont bien sauvegardés par défaut, il n’en va pas de même pour les procédures stockées.

Ainsi :
mysqldump -umy_user -p MY_DB MY_TABLE1 MY_TABLE2 > /tmp/dump_my_db.sql

… Sauvegardera bien les tables MY_TABLE1 et MY_TABLE2 de la base MY_DB, mais pas les procédures stockées…

L’option -R permet de sauvegarder cette fois vos bases, procédures stockées comprises :

mysqldump -umy_user -p -R MY_DB MY_TABLE1 MY_TABLE2 > /tmp/dump_my_db.sql

Pour ne sauvegarder que vos procédures stockées, utilisez par exemple :

mysqldump –umy_user -p -R --all-databases --no-data --no-create-db --no-create-info > /tmp/dump_proc.sql

mysqldump est riche en options, un petit coup d’oeil sur la doc de temps en temps permet d’exploiter de nouvelles idées.

le trigger au secours des function-based index (FBI)

Mercredi 1 avril 2009

Constat : les FBI (function-based index) ne sont pas disponibles en MySQL.

Comment faire dans ce cas pour obtenir par exemple l’équivalent de l’index suivant ?

CREATE INDEX idx_str_len ON my_table (length(str));

Une des solutions les plus simples consiste à utiliser un trigger qui assurera la mise à jour d’une colonne supplémentaire que l’on créera dans la table cible.

Partons de la table suivante :

CREATE TABLE `t` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`date` timestamp NOT NULL,
`str` varchar(100) NOT NULL default '0',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

Le but est de pouvoir obtenir une réponse rapide à la requête suivante :

select sql_no_cache count(*) from t where length(str) between 20 and 70;

Afin de simuler un jeu d’essai similaire à ma table de production, j’alimente ma table de test grâce à une procédure stockée déjà évoquée sur dbnewz.

Je la modifie ici afin d’obtenir une longueur aléatoire pour ma chaîne de caractère « str » :

delimiter //
CREATE PROCEDURE fill_table(nb_rows INT)
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
INSERT INTO t (str) VALUES(repeat('a', round(rand()*100)));
UNTIL i >= nb_rows
END REPEAT;
END;
//
delimiter ;

Afin de valider deux scénarios pour ce billet, j’ai utilisé deux machines, une machine perso et un serveur pro. Deux configurations différentes : la perso n’est absolument pas « tunée », en configuration strictement d’origine (5.1.32), la seconde a des processeurs et de la RAM à revendre.
J’afficherai donc les temps d’exécution des deux machines à titre de comparaison.

Lire le reste de cet article »

surveillez vos my.cnf : can’t find message file share/english/errmsg.sys

Mardi 24 mars 2009

Oui je me suis fait avoir, oui je suis parti en mode R&D pour comprendre d’où sortait cette erreur sans cause apparente, oui j’ai tenté de bidouiller mon my.cnf pour rajouter la variable « language » et définir ainsi le path vers ce fameux « errmsg.sys » inconnu au bataillon de mon serveur MySQL…

Faute avouée est à demi-pardonnée, alors autant dire que publiée sur un blog elle est carrément excusée ;)

« L’astuce » est donc la suivante :

Vérifiez que vos « clients » MySQL lisent bien le bon my.cnf… La doc ne fait pourtant pas mystère de la façon dont les fichiers de configuration (my.cnf en tête de gondole) sont lus.

« If multiple instances of a given option are found, the last instance takes precedence. » Autrement dit, le dernier qui parle à raison et mon my.cnf (/etc/my.cnf) était écrasé par d’autres copies de my.cnf dont je n’avais pas vérifié l’existence cette fois-ci.

Pour vérifier les fichiers lus par défaut (et dans quel ordre) :

> mysql --help | grep 'my.cnf'

On obtient sur cette machine :

order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

Si vous ne souhaitez pas que le /etc/mysql/my.cnf écrase ou redéfinisse certaines de vos variables (« datadir » et « basedir » par exemple) du /etc/my.cnf précédent, un renommage du my.cnf superflu résoudra le problème.

Liens symboliques et fichiers temporaires sous MyISAM

Dimanche 15 février 2009

Récemment confronté à des problèmes ponctuels d’espace disque sur un serveur gros consommateur de ce type de ressources (datawarehouse), j’ai dû temporairement jongler entre différentes partitions afin de permettre au serveur MySQL de continuer à fonctionner.

Le problème :
La partition accueillant le répertoire d’installation standard de notre MySQL arrivant à saturation, il devenait impossible de passer certaines commandes dont le ALTER TABLE. Celle-ci nécessite en effet la plupart du temps (pour MyISAM) la création de fichiers temporaires (.MYD, .MYI, .frm) dont les tailles sont semblables à celles des fichiers initiaux (aux modifications de structure près). Le hic : la taille restante sur cette partition ne permettait pas la création de tels fichiers.

Sur une table de plusieurs centaines de millions de lignes, pesant quelques dizaines de Go, un ALTER TABLE prend souvent plusieurs heures… Je vous conseille donc de vérifier dès le départ si vous disposez sur votre partition d’un espace au moins équivalent à la somme des .MYD + .MYI de votre table si vous souhaitez éviter de saturer totalement l’espace disque.

Si malgré tout vous poussez le vice jusqu’à défier toutes vos sondes de monitoring et obtenez un « Use 100% »  sur un « df -h », sachez que le .TMD susceptible de faire son apparition est là pour tenter de réparer les fichiers temporaires (au moins le .MYD) crées lors du ALTER TABLE. Cette étape supplémentaire rallonge fortement l’opération initiale à tel point qu’il est parfois plus judicieux de l’interrompre, supprimer les fichiers temporaires initialement crées par le ALTER, ainsi que le .TMD, et de tout reprendre à zéro avec cette fois un espace disque suffisant.

Pour éviter d’en arriver là, et si l’espace disque vient à manquer sur la partition de votre MySQL, les liens symboliques peuvent vous sauver la mise. Ils permettent de déplacer bases et tables MyISAM sur différentes partitions / disques, soit pour des raisons de performance (on peut alors allouer une base/table à un disque) soit comme ici surtout pour des raisons de place.

Le jonglage à base de liens symboliques permet-il d’éviter tous les problèmes ? Quel rôle joue la variable TMPDIR ?

Lire le reste de cet article »

Encore une question réccurente…

Mardi 6 janvier 2009

Bonjour à tous, c’est mon 1er post de l’année 2009, donc je vous souhaite à tous mes meilleurs voeux pour cette nouvelle année… Après 15 jours sans accés au réseau ( oui cela m’arrive une fois l’an ) me voila de retour en ligne. Comme le signalait arnaud, j’espère rencontrer le maximum de personne à Paris pour la soirée Open Source. En attendant, faisons un peu de MySQL!
La question du jour m’a été posée par mon ami Geoff et c’est une question assez simple. Comment mettre les differentes valeurs d’une colonne sur une ligne?
Lire le reste de cet article »

Les covering index, de la théorie à la pratique avec MyISAM et InnoDB

Jeudi 20 novembre 2008

Pour faire suite au dernier schéma sur les structures comparées d’un index MyISAM et InnoDB, ce billet a pour but de détailler une optimisation nommée covering index.
On appelle ainsi un index lorsqu’il « couvre » l’intégralité des données recherchées et évite ainsi un parcours des enregistrements souvent basé sur des random I/O, spécialement couteux sur disque.

A propos des random I/O, voici un court extrait d’un billet précédent sur les SSD :

Sur un disque classique un “random read” entraîne (du plus couteux au moins couteux) :
- le déplacement de la tête de lecture/écriture sur la bonne piste (”seek time”)
- une fois la tête sur la bonne piste, il faut encore repérer sur celle-ci le bloc secteur demandé (”rotational latency”)
- la lecture et la transmission de la donnée vers le système.

Les quelques chiffres suivants permettent de mesurer les conséquences du paragraphe précédent et ainsi de mieux visualiser les enjeux soulevés par les covering index.

On se base ici sur la base d’un enregistrement de 100 bytes. Notez que ces valeurs sont des ordres de grandeur :

En mémoire :
Sequential read : 5 000 000 read/s (500 Mb /s)
Random read : 250 000 read/s (25 Mb /s)

=> En mémoire, un coefficient x20 existe entre les performances en sequentiel et en lecture aléatoire.

Sur disque :
Sequential read : 500 000 r/s (50 Mb /s)
Random read : 100 r/s (10 Kb /s)

=> Sur disque, un coefficient x5000 (!) existe entre les performances en sequentiel et en lecture aléatoire.

Lire le reste de cet article »

Cardinalité, sélectivité et distributivité d’un index MySQL : quel impact sur le plan d’exécution ?

Vendredi 5 septembre 2008

Nouveau volet de notre série consacrée aux index, la notion de sélectivité d’un index est ici à l’honneur.
Souvenez-vous du billet précédent : notre table de test contient 1 million de lignes et un champ « flag » à la cardinalité très faible : 2.

Cardinalité ? Sélectivité ? Avant d’aller plus loin, deux définitions s’imposent. Tout d’abord la cardinalité d’un index : c’est le nombre de valeurs uniques qu’il contient.

La sélectivité d’un index se calcule elle de la façon suivante :

Selectivité = Cardinalité(Idx) / Nombre total d’enregistrements dans la table
Résultat des courses notre index à une sélectivité de 2 /1 000 000 = 2e-6, qui a dit « peu sélectif » ?

A l’opposé d’une si faible sélectivité on trouve celle d’une clé primaire puisque celle-ci a une selectivité de 1. Exprimé autrement : à une « entrée » de la clé primaire ne correspond qu’un seul enregistrement dans la table.

D’où la règle générale suivante : plus un index est sélectif, plus il est efficace, c’est à dire capable d’identifier rapidement la ou les données recherchées.

L’optimiseur MySQL est chargé de transformer votre requête en un plan d’exécution le plus efficace possible, suit-il à la lettre cette règle générale ?

Vous vous doutez bien que non…

Lire le reste de cet article »

Générer un jeu de données : shell, mysqlslap, et procédure stockée

Mardi 19 août 2008

Le prochain article de notre série consacrée aux index MySQL approche et j’ai besoin pour ce prochain épisode de générer une table de test de la forme suivante :

CREATE TABLE `t` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`date` timestamp NOT NULL,
`flag` tinyint(4) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `flag` (`flag`)
) ENGINE=MyISAM;


La structure est définie, reste à alimenter la table, disons 1 million d’enregistrements.

La valeur du champ « flag » est importante pour nos tests ultérieurs, sa valeur doit pour le moment être comprise entre 0 et 1, le tout à peu près uniformément distribué.

La requête sera la suivante :

INSERT INTO test.t (flag) VALUES (ROUND(RAND()));

Il faut maintenant exécuter celle-ci 1 million de fois.
Voyons ce que cela donne en utilisant le shell, mysqlslap ou bien encore une procédure stockée.

Lire le reste de cet article »

Installation du MySQL Cluster

Vendredi 18 juillet 2008

Le but de ce billet est d’installer rapidement une configuration permettant de tester le cluster MySQL.
Il existe plusieurs façons de faire, en voici une basée sur les binaires de la dernière version à ce jour, la cluster 6.2.15. Rappelez-vous que depuis la version 5.1.25 de MySQL, les binaires du cluster ne sont plus inclus mais disposent de leur propre branche.

Haute disponibilité (redondance), montée en charge et haute performance (données en mémoire vive), une architecture en « shared-nothing » (aucun élément du cluster ne partage le même hardware) et un serveur mysql doté d’un nouveau moteur de stockage (NDB encore appelé NDBCLUSTER), voici quelques caractéristiques du cluster MySQL. Voyons comment l’installer en un minimum d’étapes.

Vue d’ensemble des éléments composant un cluster

Ce schéma issu de la documentation MySQL expose les différents élements qui composent un cluster MySQL, passons-les rapidement en revue.

Les composants d\'un cluster MySQL

Lire le reste de cet article »