Archive pour le mot-clef ‘index’

Méthodes de suppression des index inutiles

Lundi 5 septembre 2011

Les vacances étant terminées, nous allons boucler notre tour de vue des index inutiles en voyant quels outils vont nous aider à découvrir les index qui peuvent être supprimés. Le dernier article présentait en effet des indications qui fonctionnent généralement bien mais qui ont l’inconvénient de demander beaucoup de travail manuel et de laisser de côté tout un pan d’index qui peuvent être inutiles : ceux qui ne sont pas en doublon ni redondants, qui n’ont pas une cardinalité faible mais qui ne sont tout simplement pas utilisés par l’application.
Lire le reste de cet article »

Index candidats à la suppression

Mercredi 6 juillet 2011

Après avoir constaté dans les articles précédents que les index inutiles causent des baisses de performances non négligeables, nous allons voir dans cet article qu’il n’est pas aussi simple qu’il y paraît de déterminer si un index est utile ou non, même si dans certains cas la réponse semble évidente.
Lire le reste de cet article »

Le coût des index inutiles – 2nde partie

Vendredi 27 mai 2011

Dans l’article précédent, nous nous étions demandés quelle était la dégradation des performances en écriture quand on ajoute des index. On peut élargir la réflexion en se penchant sur les conditions qui améliorent ou diminuent la vitesse d’écriture dans une table.
Lire le reste de cet article »

Le coût des index inutiles

Lundi 16 mai 2011

On vous a tout le temps dit et redit que les index étaient indispensables pour les performances en lecture d’une base de données et vous avez eu droit à des exemples spectaculaires où les temps de réponses sont divisés par 10 000 ou par 1 000 000 rien qu’en ajoutant un index judicieux. Bien. On vous a également prévenu que chaque index posé dégrade les écritures et qu’il ne faut donc pas en abuser. Mais vous a-t-on déjà montré quel type de dégradation en écriture on peut attendre quand on ajoute un index ? C’est ce dont nous allons parler dans cet article.
Lire le reste de cet article »

Instrumentation et performance

Lundi 24 janvier 2011

Instrumenter son application correctement représente un pas important dans la recherche des performances optimales. De bons outils permettent également de gagner du temps, qui est toujours précis. Cet article va vous donner un exemple de la valeur ajoutée que peut procurer un bon outil : le simple fait d’obtenir un rapport précis sur un problème rencontré permet de résoudre en 5 minutes un gros problème de performance qui ne trouvait pas de solution depuis des semaines. Lire le reste de cet article »

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 »

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 »

Dessine-moi MySQL : structure d’un index MyISAM et InnoDB

Vendredi 24 octobre 2008

Deuxième exemplaire (traduire « 2ème essai ») concernant ces schémas « à main levée », au menu du jour une comparaison entre la structure d’un index MyISAM et celle d’un index InnoDB :

J’ai tenté de tenir compte de vos commentaires précédents, notamment sur les majuscules, est-ce plus lisible ?

Selon les « règles » établies pour cette série « Dessine-moi MySQL », les schémas doivent être suffisamment explicites pour ne pas nécessiter d’explications supplémentaires…
Cependant, vu que je n’ai pas 10 ans d’école d’arts graphiques derrière moi (ça s’est vu ?), et que chaque règle a son exception, je rajouterai tout de même ceci :

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 »