Dans le précédent post, nous avons optimisé une requête en abandonnant un des principes du SQL (dire au SGBD ce qu’on souhaite faire, mais pas comment le faire). Ici nous allons voir un exemple où le fait de penser en SQL va nous permettre de rendre performante une requête difficile à améliorer.
Archive pour le mot-clef ‘tuning’
Comment réécrire une requête SQL ? Partie 2
Lundi 21 décembre 2009A better MySQLTuner – Sheeri K. Cabral
Samedi 22 août 2009MySQLTuner est un script Perl qui produit un rapport sur la configuration de votre serveur MySQL et donne des pistes d’optimisation. On peut bien sûr s’interroger sur la manière dont l’analyse est faite et surtout sur la pertinence des recommendations. C’est exactement l’exercice qu’a fait Sheeri pour nous, en examinant le script sur toutes ses coutures.
Il en ressort que pas mal d’affirmations et de recommendations sont hardcodées et ne tiennent absolument pas compte des spécificités de votre base. Un exemple ? Si le cache de requêtes est désactivé, alors le script va systématiquement vous remonter qu’il s’agit d’un problème, même si vous avez sciemment désactivé ce cache.
A partir de toutes ces constatations, Sheeri a commencé à faire évoluer le script, en ajoutant pour l’instant quelques options intéressantes, comme celle permettant de formater le rapport de manière à le rendre facilement lisible par un tableur : il devient ainsi plus facile de voir l’évolution de certains indicateurs dans le temps en lançant le script à intervalles réguliers. A suivre !
Cardinalité, sélectivité et distributivité d’un index MySQL : quel impact sur le plan d’exécution ?
Vendredi 5 septembre 2008Nouveau 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…
Les index MySQL : types, placements, efficacité
Vendredi 27 juin 2008Déjà trois semaines d’écoulées depuis que certains d’entre vous, les « héros », ont posé leurs questions (oui il est possible de devenir un héros rien qu’en lisant dbnewz ! Les véritables héros sont d’ailleurs abonnés au tout nouveau flux feedburner 
)
Trois semaines d’attente, cela mérite un billet digne de ce nom, c’est parti.
Indexer, pourquoi ?
L’indexation peut avoir plusieurs buts :
- Accéder à ses données plus rapidement, les index sont en effet l’outil le plus puissant pour accélérer les temps d’exécution de vos requêtes jusqu’à parfois plusieurs centaines de % !
- Définir le degré d’unicité d’une colonne donnée : chaque champ doit-il être unique ? les doublons sont-ils autorisés ?
Principe de fonctionnement
Lorsque vous envoyez une requête à votre serveur MySQL, celle-ci est d’abord confiée au « parseur » SQL qui a pour but de vérifier si la syntaxe de votre demande est correcte. Cette étape franchie, la requête passe par « l’optimiseur ». Il s’agit ici de déterminer le plan d’exécution de la requête afin que celle-ci s’exécute le plus rapidement possible.
L’optimiseur détecte si d’éventuels index sont disponibles, si c’est le cas il décidera de s’en servir… ou pas : il est parfois plus rapide de ne pas se servir d’un index ! Nous verrons pourquoi au cours de cette série d’articles.
Une fois le plan d’exécution achevé, c’est le moteur de stockage qui prend le relais, celui-ci peut être vu comme un « module » de MySQL :
Choisir l’implémentation de ses index : « B-TREE » ou « HASH », quelles différences ?
Lundi 19 mai 2008Préambule technique à une série de futurs articles, je ne vous en dis pas plus, l’épisode du jour a pour point de départ un moteur de stockage MySQL avec à la clé la possibilité, ou pas, de définir l’implémentation de ses index : B-TREE ou HASH.
Ce choix n’est en effet pas toujours disponible, c’est même plutôt rare puisque seul le moteur de stockage MEMORY vous permet depuis la version 4.1 de MySQL, d’effectuer ce choix. Nous ne parlerons pas ici du MySQL Cluster et de son moteur NDB qui sera abordé spécifiquement dans un autre épisode.
Pourquoi alors se soucier de ce type d’implémentation si seul le moteur MEMORY offre la possibilité de choisir ?
- MyISAM et InnoDB pourraient à l’avenir proposer ce choix.
- Afin de comprendre plus finement comment fonctionnent les index que vous utilisez tous les jours, se pencher sur la façon dont ils sont implémentés permet de mieux appréhender certains résultats.
log-slave-updates
Jeudi 5 juillet 2007Des erreurs en enchaînant des réplications? J’ai retrouvé souvent la même erreur sur des configurations en « multi master » ou celles qui utilisent des « relay slave ». Imaginons que vous ayez 3 serveurs A -> B -> C. A étant le master, B le relay slave et C le slave. Tout se passe bien, le résultat de la commande « show slave status » vous informe que tout roule et pourtant le slave (C) n’est pas à jour. Alors pourquoi? magie vaudou?
Non! Vous avez tout simplement oublié d’ajouter sur votre relay slave (B) le paramètre « log-slave-updates ».
Un slave n’enregistre pas dans son journal (ses binlogs) les commandes qu’il reçoit de son master. Donc dans notre cas (B) est à jour mais (C) n’a aucun moyen de connaître les commandes car (C) lit juste les binlogs de (B).
En rajoutant log-slave-updates dans le my.cnf de (B), il écrira toutes les commandes dans les binlogs et (C) sera ainsi à jour.
C’est aussi un bon moyen pour contrôler toutes les commandes exécutées sur votre slave. Cela m’a permis un jour de trouver la source d’un problème. Un client mal configuré venait mettre à jour un slave et faisait ainsi planter la réplication.
Innodb et « autoshrink »
Lundi 18 juin 2007Aujourd’hui j’ai reçu un message d’un collègue qui était surpris de voir que son « datafile » InnoDB gardait la même taille aprés avoir effacé des tables ou des éléments de ses tables.
C’est tout à fait normal! Toutes les bases de données marchent de la même facon. Un tablespace s’agrandira tout seul si vous le créez en mode autoextend et ce sous MySQL mais aussi Oracle, IBM DB2,… Si vous n’activez pas la fonction autoextend quand la base voudra allouer de la place, vous verrez une belle erreur.
La question est pourquoi? Performances!
Pour gagner en performances, vous voulez que votre base pré alloue de l’espace disque, des blocks de data pour vous. Nous parlons alors d’extend. Vu ces conditions veut on vraiment désallouer des extends pour les réallouer ensuite? La réponse est non!
Comment alors récupérer de l’espace? La seule solution est d’exporter les data, d’effacer les files et importer le tout.
Que peut on faire pour optimiser ça?
Effacer un maximum avant d’ajouter de l’information et ainsi contrôler du mieux que l’on peut la taille de votre tablespace.
Et pour MySQL?
Après avoir discuté avec Peter Zaitsev et Heikki Tuuri, il semblerait que « innodb_file_per_table » qui est apparu avec MySQL 4.1.3, pourrait contourner le problème. Cela semblerai être plus performant qu’un seul tablespace.
« innodb_file_per_table » est un paramètre de configuration dans le my.cnf, et est pris en compte seulement lors de la création d’une table. Donc pour migrer d’un seul tablespace vers plusieurs fichiers vous devrez encore une fois faire un export/import.
Une fois ceci fait, si vous effacez vos entrées et faite un « OPTIMIZE TABLE » l’OS libérera l’espace. Chaque table a son propre fichier .ibd et la commande recrée un fichier, efface l’ancien et renomme le nouveau.
Ce qui veut dire qu’a un instant t vous avez 2 fois les data, prévoyez donc une place suffisante sur votre disque et n’attendez pas le dernier moment.
Pour conclure, autoextend c’est bien mais vous devez néanmoins surveillez ça de prés.
Com_admin_commands
Lundi 18 juin 2007Si vous jouez un peu avec MySQL vous devez déjà connaitre la commande SHOW STATUS. Elle vous donne un snapshot de tous les compteurs interne de MySQL. Ces compteurs traquent des événements précis, par exemple:
mysql> show status like ‘Com_select’;
+—————+————+
| Variable_name | Value |
+—————+————+
| Com_select | 2293615720 |
+—————+————+
1 row in set (0.00 sec)
Voila le nombre de select qu’il y a eu sur ce server, 2.3 milliards… oups…
mysql> show status like ‘Questions’;
+—————+———–+
| Variable_name | Value |
+—————+———–+
| Questions | 533719732 |
+—————+———–+
1 row in set (0.00 sec)
‘Questions’ est le nombre de requêtes et commandes envoyées au serveur et devrait donc être logiquement la somme de toutes les « Com_* ».
Est ce un bug? 533 millions alors que rien qu’en select je suis déjà à 2.3 milliard?
En fait tous ces compteurs sont des entiers non signés. Donc sur des plateformes 32bit nous atteignons un maximum de 4.2 milliards avant que cela revienne à zéro. Donc tout est bien normal ici.
Maintenant la valeur qui est bizarre pour moi est ‘Com_admin_commands’.
mysql> show status like ‘Com_admin_commands’;
+——————–+————+
| Variable_name | Value |
+——————–+————+
| Com_admin_commands | 2731594764 |
+——————–+————+
1 row in set (0.00 sec)
2,7 milliards, ok mais à quoi correspond une commande admin? Je n’ai pas trouvé une seule ligne de documentation la dessus. Donc la seule solution est de regarder le code source (J’adore l’Open Source). C’est ainsi que l’on découvre que cela correspond à
- COM_TABLE_DUMP: demande au serveur d’envoyer la définition des tables et les data au format RAW. C’est utilisé par la réplication si vous utilisez LOAD DATA FROM MASTER. C’est commande est obsolète, NE PLUS UTILISER.
- COM_CHANGE_USER: dit au serveur que le client veut changer le user associé à la session – mysql_change_user( ).
- COM_BINLOG_DUMP: demande au serveur d’envoyer un flux constent des binlogs à partir d’une certaine valeur – mysqlbinlog
- COM_SHUTDOWN: commande l’arrêt du serveur – mysql_shutdown( )
- COM_PING: ping pour MySQL – mysql_ping( )
- COM_DEBUG: force un dump dans le log d’erreurs – mysql_dump_debug_info( )
Pour plus de détails la dessus, je vous conseille le nouveau livre de Sasha Pachev aux éditions O’Reilly, Understanding MySQL Internals qui est sorti en Avril 2007 et a été présenté pendant la conférence MySQL.
Donc revenons à cette base, nous avons 2.3 milliards de commandes admin, et cela semble être le plus vraisemblablement à cause de la fonction ping. Je me demande vraiment à quoi cela peut servir si ce n’est à garder des connections ouvertes pour rien.
- à t, je ping tout est ok
- à t+1, je perd ma connection
- à t+2, j’execute ma commande et j’ai pas de connection
C’est une fonction faite pour les clients qui gardent les connections en attente pendant longtemps…ce qui est INUTILE avec MySQL. Au lieu de garder des connections dans le vide… FERMEZ les!
max_connections
Mardi 22 mai 2007Aujourd’hui fut la journée des ‘too many connections’. En effet pas loin de 3 applications ont planté du fait que le nombre maximum de connexions MySQL avait été atteint. Le message d’erreur est très parlant. Comment est ce possible? mysqld autorise max_connections+1 clients à se connecter. Le ‘+1′ est une extra connexion réservée aux comptes ayant le privilège SUPER. Donc si votre user applicatif à ce privilège, vous vous retrouvez bloqués. Prenez comme principe d’avoir les privilèges minimaux pour vos utilisateurs, INSERT, UPDATE, DELETE et SELECT suffisent largement.
La valeur par défaut du paramètre est de 100.
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+
Donc à vous de connaître vos besoins et d’allouer un nombre maximum suffisant pour votre application. Cela peut être utile pour vous montrer une erreur logicielle si le nombre est trop important.
Maintenant quelle est le problème si j’autorise trop de connexions simultanées. Tout simplement d’ utiliser trop de mémoire et de planter votre serveur. Il faut garder en tête cette rapide équation:
Un connexion ( MySQL thread ) utilise en RAM au maximum:
( thread_stack + net_buffer_length + max_allowed_packet + read_buffer_size + join_buffer_size + tmp_table_size + myisam_sort_buffer_size )
Sachant que vous avez déjà alloué de la mémoire à votre buffer pool / key cache, restez vigilant à ne pas dépasser le total de votre serveur.
Avec MySQL 5.0, une nouvelle variable est apparue, ‘max_user_connections’ pour limiter le nombre de connexions concurrentes pour un même utilisateur. C’est une variable globale pour TOUS les utilisateurs et activée pour les valeurs > 0.
mysql> show variables like 'max_user_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_user_connections | 0 |
+----------------------+-------+