Numéro un pour les applications en ligne, MySQL se positionne maintenant sur le secteur des applications d’entrepôts de données et d’informatique décisionnelle…
http://dasini.net/blog/presentations/?#presentation_mysql51
Le format de l’examen de certification MySQL change.
Actuellement l’examen se déroule sur la version 5.0 (en anglais, chinois ou japonais). Les différents niveaux sont:
– CMDEV: Certified MySQL 5.0 Developer
– CMDBA: Certified MySQL Database Administrator
– CMCDBA: Certified MySQL Cluster DBA
– CMA: Certified MySQL Associate
Pour être certifié CMDEV ou CMDBA, il faut passer 2 examens, respectivement CMDEV-I et CMDEV-II ou CMDBA-I et CMDBA-II.
Chaque examen dure 90 minutes et 70 questions attendent une réponse de votre part.
Le problème avec ce genre de tests, est qu’ils sont composés de QCM et de phrases a trous, ce qui a pour conséquences de plus juger votre capacité à apprendre un livre par cœur que de savoir si vous êtes réèlement capable de remonter une base corrompue ou d’optimiser des requêtes problématiques…
La nouvelle certification Certified MySQL 5.1 DBA est basée sur des cas pratiques de DBA tel que la sauvegarde et la restauration d’une base, la création de comptes utilisateurs, l’import des données… brefs les tâches quotidiennes d’un DBA (MySQL).
Cette nouvelle certif devrait être mise en place courant 2009.
Le but de cet article est d’optimiser une simple requête (SELECT avg(Population) FROM city GROUP BY CountryCode) et surtout de comprendre comment l’optimiseur procède, en étudiant les résultats donnés par les variables qui permettent de surveiller le serveur MySQL.
Le schéma utilisé est le schéma world téléchargeable ici
Voici la structure de la table city:
La commande EXPLAIN, permet d’avoir le plan d’exécution:
EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\GL’optimiseur fait un full table scan (type: ALL) ce qui n’est pas forcement une bonne nouvelle. De plus aucun index n’est utilisé (key: NULL), ce qui est logique car la table n’en contient pas (sic !)
Using temporary; Using filesort indiquent la création d’un table temporaire et le tri des données (pas très bon pour les performances surtout si la table temporaire est créée sur le disque)
La variable Last_query_cost permet de récupérer le coût de la requête:
*************************** 1. row ***************************
Variable_name: Last_query_cost
Value: 4963.520924
Ajoutons un index sur la colonne countrycode:
L’index Idx_cc ne sert à rien
Ajoutons alors un index sur la colonne population
Pas mieux ! L’index Idx_population ne sert à rien.
Effacement des 2 index:
Ajoutons un index composite sur les colonnes population, countrycode:
Çaparait un peu meilleur, l’index Idx_population_cc est utilisé.
Extra: using index nous indique que MySQL utilise un index couvrant (covering index), c’est à dire que l’information est entièrement accessible en parcourant l’index (pas d’accès aux données).
De plus l’optimiseur fait un full index scan: type: index
Voyons le coût de la requête:
La valeur de (l’obscure) Last_query_cost est cependant le même que pour les requêtes précédentes…
Ajoutons un index composite sur les colonnes countrycode,population
L’optimiseur estime que le coût de la requête est toujours le même, cependant l’index Idx_cc_population(CountryCode,Population) optimise les performance de notre requête car il n’y a plus de Using temporary ni de Using filesort.
Il suffit de pousser encore un peu plus loin notre analyse pour en être définitivement (?) convaincu…
Initialisation des variables de sessions
En utilisant l’index idx_population_cc (le mauvais index)
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 4079 |
| Handler_read_next | 4079 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 232 |
| Handler_read_rnd_next | 233 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 3847 |
| Handler_write | 232 |
+----------------------------+-------+
Les handler sont des indicateurs liés au moteur de stockage.
Handler_read_first comptabilise le nombre de fois que la première valeur de l’index est lue.
Handler_read_key indique le nombre d’enregistrement récupéré graçe à l’index.
Handler_read_next indique une lecture ordonnée de l’index (une valeur, puis la suivante, puis la suivante…).
Handler_read_first, Handler_read_key & Handler_read_next, indiquent là, un full index scan
Handler_read_rnd & Handler_read_rnd_next indiquent un full table scan sur la table temporaire
Handler_update nous donne une indication sur le nombre de mise à jours dans la table temporaire (à cause du tri)
Handler_write indique le nombre de lignes insérées dans la table temporaire
Ces 2 derniers paramètres confirme donc la création de la table temporaire et l’opération de tri
Pour avoir plus d’informations sur le tri:
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 232 |
| Sort_scan | 1 |
+-------------------+-------+
Sort_rows: nombre de lignes triées (nombre d’enregistrements de la table temporaire).
Sort_scan: nombre de tri.
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
Created_tmp_tables: nombre de table temporaire crée. La bonne nouvelle est que la table temporaire est créee en mémoire (Created_tmp_disk_tables=0)
réinitialise les variables de sessions
En utilisant l’index idx_cc_population (le bon index)
SELECT AVG(Population) FROM city use index(idx_population_cc) GROUP BY CountryCode; … 232 rows in set (0.00 sec)+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 4079 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
Handler_read_first & Handler_read_next valident le full index scan et puis c’est tout 🙂
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
pas de tri
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
pas de table(s) temporaire(s)
cqfd
Il est pafois utile de désactiver le log binaire lors d’une restauration. MySQL permet de le désactiver pour une session avec la commande SQL_LOG_BIN:
mysql> SET SESSION sql_log_bin = 0;Lors de la restauration avec le client mysql on peut donc utiliser la ligne de commande suivante:
shell> mysql –execute=« SET SESSION sql_log_bin=0; SOURCE mon_fichier_dump.sql; »Il est possible d’influencer l’optimiseur pour qu’il choisisse d’utiliser ou de ne pas utiliser un index particulier. Les clauses à placer dans votre requête SELECT sont les suivantes:
USE INDEX : utilise l’index passé en argument (MySQL ne l’utilisera pas si l’index est plus couteux qu’un full table scan)
FORCE INDEX : utilise l’index passé en argument (MySQL ne l’utilisera pas …s’il ne peut pas l’utiliser 🙂 )
IGNORE INDEX : n’utilise pas l’index passé en argument
La plus part du temps, il se débrouille trés bien sans indications, mais parfois…
Dans cet exemple, j’utilise une table rental_daz inspirée de la table rental de la base de donnée sakila, voici sa structure:
12:14 daz$sakila> SHOW CREATE TABLE rental_daz\G
*************************** 1. row ***************************
Table: rental_daz
Create Table: CREATE TABLE `rental_daz` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
Voici ma requête:
SELECT * FROM rental_daz WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR);
La commande EXPLAIN me permet de connaitre son plan d’exécution:
12:14 daz$sakila> EXPLAIN SELECT * FROM rental_daz
WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental_daz
type: range
possible_keys: rental_date
key: rental_date
key_len: 8
ref: NULL
rows: 2744
Extra: Using where
L’optimiseur voit l’index composite rental_date et l’utilise. Ça à l’air pas mal…
Voyons le coût de cette requête,grâce à au paramètre LAST_QUERY_COST :
12:19 daz$sakila> SHOW STATUS LIKE 'Last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 3842.609000 | +-----------------+-------------+
Empêchons l’optimiseur d’utiliser l’index rental_date:
12:20 daz$sakila> EXPLAIN SELECT * FROM rental_daz IGNORE INDEX(rental_date)
WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental_daz
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16298
Extra: Using where
L’optimiseur n’utilise donc pas l’index (il ne le voit même pas). Un full table scan est donc effectué. C’est à priori plus coûteux que d’utiliser l’index. Regardons le coût de cette requêtes.
16:18 daz$sakila> SHOW STATUS LIKE 'Last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 3356.599000 | +-----------------+-------------+
Malgré le full table scan, cette requête est moins coûteuse que celle qui utilise l’index !!!
Essayons de vérifier cela en durée. Le client mysqlslap est tout indiqué :
mysqlslap -uroot -proot –create-schema=sakila -i50
-q »SELECT * FROM rental_daz WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR); »
Benchmark
Average number of seconds to run all queries: 0.287 seconds
Minimum number of seconds to run all queries: 0.140 seconds
Maximum number of seconds to run all queries: 1.172 seconds
Number of clients running queries: 1
Average number of queries per client: 1
mysqlslap -uroot -proot –create-schema=sakila -i50
-q »SELECT * FROM rental_daz ignore index(rental_date) WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR); »
Benchmark
Average number of seconds to run all queries: 0.167 seconds
Minimum number of seconds to run all queries: 0.078 seconds
Maximum number of seconds to run all queries: 1.094 seconds
Number of clients running queries: 1
Average number of queries per client: 1
mysqlslap confirme bien que le full table scan est, dans ce cas précis, plus performant que la recherche indexée par intervalle. On se trouve bien dans un cas où l’optimiseur se trompe.
Le MySQL User Groupe.fr (LeMUG.fr), association loi 1901, réunit les utilisateurs francophones de la base de données open source MySQL. (http://www.lemug.fr/)
LeMUG.fr poursuit plusieurs objectifs:
…
La collecte des dons et adhésions se fera par le biais d‘IZI-collecte, solution intégrée de mobilisation et de collecte en ligne pour les organisations non-lucratives:
Page d’inscription: http://www.lemug.fr/inscription/
Inscription: http://lemugfr.cotiserenligne.fr/
A très vite, sur LeMUG.fr
En cas de perte du mot de passe root surtout si c’est votre seul compte (super) administrateur, vous vous trouvez dans une situation pour le moins embarrassante.
MySQL propose un moyen de s’en sortir. Certes, si le mot de passe est perdu vous ne pourrai pas le récupérer, car il est stocké haché dans la base:
mysql> SELECT user, password FROM mysql.user; +---------+-------------------------------------------+ | user | password | +---------+-------------------------------------------+ | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | +---------+-------------------------------------------+ 1 rows in set (0.39 sec)
Néanmoins il est possible de le changer. Voici les étapes à suivre:
Cela ne devrait pas poser trop de problèmes:
mysql stop (sous linux)
NET STOP MySQL (sous windows)
…
ou dans le pire des cas, débranchez la machine 🙂
$ mysqld --skip-grant-tables
Il important de noter qu’une fois démarré avec skip-grant-table, n’importe qui peut se connecter au serveur MySQL et avec tout les droits… Inutile de préciser que le serveur est à ce moment particulièrement vulnérable.
Connectez vous au serveur mysql, comme à l’accoutumée.
$ mysql --user=UtilisateurExistantPas --password=MotDePasse Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.30-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
La première chose à faire, une fois connecté est de sécuriser le serveur en remettant en place la vérification des droits:
mysql> FLUSH PRIVILEGES;
$ mysql --user=UtilisateurExistantPas --password=MotDePasse ERROR 1045 (28000): Access denied for user 'UtilisateurExistantPas'@'localhost' (using password: YES)
L’heure est enfin venue de se créer un nouveau mot de passe root
mysql> SET PASSWORD FOR root@localhost=PASSWORD('m0T2pA55e');
Query OK, 0 rows affected (0.06 sec)
Pour sortir de la configuration skip-grant-tables, il faut arrêter le serveur, pour mieux le redémarrer…
et le tour est joué 🙂
$ mysql --user=root --password=m0T2pA55e Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
La problématique est de mettre à jour le schéma de l’application en changeant la structure de certaines tables.
Changer le schéma a comme principal impact d’obliger de modifier les requêtes de l’application. Il sera donc nécessaire de les identifier pour les mettre à jour à leur tour, ce qui peut rapidement devenir fastidieux. Au travers de l’exemple qui suit, nous allons créer une vue qui va masquer le changement de table ce qui nous évite de modifier les requêtes applicatives. Une nouvelle version de l’application pourra utiliser la nouvelle table sans être obligé d’utiliser la vue, on assure ainsi la compatibilité ascendante.
Ma table de départ est la table livre:
CREATE TABLE 'livre' (
'id_livre' char(17) NOT NULL,
'auteur' char(50) default NULL,
PRIMARY KEY ('id_livre')
);
Les requêtes, du coté de l’application, sont les suivantes:
SELECT id_livre FROM livre; SELECT auteur FROM livre; SELECT * FROM livre;
De cette structure où je ne peux gérer que des livres, j’en crée une autre qui m’offre plus de souplesse, la table produit:
CREATE TABLE 'produit' (
'id_produit' mediumint(9) NOT NULL auto_increment,
'isbn' char(17) default NULL,
'auteur' char(50) default NULL,
PRIMARY KEY ('id_produit'),
UNIQUE KEY 'isbn' ('isbn')
);
Les seuls produits disponible sont mes livres, je remplis donc ma table produit avec le contenu de la table livre :
INSERT INTO produit (isbn, auteur) SELECT id_livre, auteur FROM livre;
La dernière phase consiste à créer la vue « livre », il me faut donc au préalable effacer la table du même nom. Les vues et les tables partageant le même espace de nom.
DROP TABLE livre; CREATE VIEW livre AS SELECT isbn AS id_livre, auteur FROM produit;
Les changements sont transparents pour les trois requêtes de mon application.
Voici un petit tour d’horizon sur les vues, qui nous l’espérons aura contribué à affiner votre vision sur ce sujet. Il est certain que ces tables virtuelles amènent une certaine souplesse au schéma et il serait dommage de ne pas en profiter. Cependant, ce n’est pas non plus une solution miracle, car ajouter des objets peut rapidement rendre le schéma complexe. Maintenant à vous de voir dans quels cas les vues pourrons vous être utiles. Pour nous, c’est tout… vu.
MySQL 5 : Les vues — (part 1/7)
Le MySQL User Group (LeMUG.fr) vous invite à une rencontre de débats et d’échanges autour des dernières tendances de l’open source, avec la participation des Club Utilisateurs GUSES (Solaris), JUG (Java) et OSS Get-Together Paris et la présence exceptionnelle de Simon Phipps, Sun’s Chief Open Source Officer
Mercredi 14 janvier 2009 à partir de 18h30
42, Avenue d’léna 75016 Paris
(métro 9 station Iéna)
Agenda de la soirée
18h30 — Accueil
19h00 — Simon Phipps, Chief Open Source Officer, Sun Microsystems : « Latest open source trends and business models »
19h30 — Présentation des Clubs Utilisateurs GUSES (Solaris), JUG (Java), LEMUG.fr (MySQL) et OSSGTP
20h00 — Table-ronde avec Simon Phipps et les présidents des Clubs Utilisateurs sur la stratégie de Sun autour de Java, Solaris et MySQL… entre autres, débat avec Pascal Borghino Président de l’association LeMUG.FR
20h30 — Cocktail et networking
Infos pratiques
Date & heure : 14/01/2009 de 18h30 à 21h30
Lieu : Sun Microsystems
42, Avenue d’léna , 75016 Paris
Accès : métro 9 station Iéna
Tarif : gratuit
Inscription : http://fr.sun.com/sunnews/events/2009/jan/soiree_open_source/
Pour ce troisième exemple, nous allons nous intéresser au schéma (là encore très simplifié) d’une application qui permet de vendre des produits en France et au Royaume-Uni, en euro, livre et dollar. Cette application possède une table produit, qui contient le produit (son identifiant) et son prix hors taxe en euro.
Structure de la table produit:
CREATE TABLE produit ( id_produit mediumint(8) unsigned NOT NULL auto_increment, prix_ht decimal(6,2) default NULL, PRIMARY KEY (id_produit) )
Nous disposons également des tables devise et tva qui gèrent respectivement le taux de change des devises et la TVA de différents pays.
Tables devise et tva:
CREATE TABLE devise (
devise enum('Euro', 'Dollar', 'Livre') NOT NULL,
valeur decimal(6,5) default NULL,
PRIMARY KEY (devise)
);
INSERT INTO devise VALUES ('Livre',0.66017);
INSERT INTO devise VALUES ('Dollar',1.29852);
INSERT INTO devise VALUES ('Euro',1);
CREATE TABLE tva (
pays enum('France', 'Royaume-Uni') NOT NULL,
normal decimal(3,1) default NULL,
reduit decimal(3,1) default NULL,
PRIMARY KEY (pays)
);
INSERT INTO tva VALUES ('Royaume-Uni',17.5,5.0);
INSERT INTO tva VALUES ('France',19.6,5.5);
Le besoin est le suivant : disposer simplement des prix TTC pour chaque pays.
On va donc créer deux vues par pays qui nous permettrons de disposer des prix TTC en fonction de la devise.
La vue produit_france, contient les produits, le prix TTC et le prix TTC réduit qui correspond à l’ajout de la TVA réduite. Les prix sont en euros.
Vue produit_france :
CREATE VIEW 'produit_france' AS
SELECT 'produit'.'id_produit' AS 'produit',
round((((('produit'.'prix_ht' * 'tva'.'normal') / 100) + 'produit'.'prix_ht')
* 'devise'.'valeur'),2) AS 'Prix_ttc_€',
round((((('produit'.'prix_ht'* 'tva'.'reduit') / 100) + 'produit'.'prix_ht')
* 'devise'.'valeur'),2) AS 'Prix_ttc_reduit_€'
FROM (('produit' join 'tva') join 'devise')
WHERE (('tva'.'pays' = 'France') and ('devise'.'devise' = 'Euro'));
Certains clients préférant la monnaie de l’oncle Sam, une deuxième vue, produit_france_dollar, est nécessaire pour avoir les prix en dollar.
Vue produit_france_dollar:
CREATE VIEW 'produit_france_dollar' AS
SELECT 'produit'.'id_produit' AS 'produit',
round((((('produit'.'prix_ht' * 'tva'.'normal') / 100) + 'produit'.'prix_ht')
* 'devise'.'valeur'),2) AS 'Prix_ttc_$',
round((((('produit'.'prix_ht'* 'tva'.'reduit') / 100) + 'produit'.'prix_ht')
* 'devise'.'valeur'),2) AS 'Prix_ttc_reduit_$'
FROM (('produit' join 'tva') join 'devise')
WHERE (('tva'.'pays' = 'France') and ('devise'.'devise' = 'Dollar'));
Même principe pour le Royaume-Uni :
CREATE VIEW 'produit_royaume_uni' AS
SELECT 'produit'.'id_produit' AS 'produit',
round((((('produit'.'prix_ht' * 'tva'.'normal') / 100) + 'produit'.'prix_ht')
* 'devise'.'valeur'),2) AS 'Net_price_£',
round((((('produit'.'prix_ht'* 'tva'.'reduit') / 100) + 'produit'.'prix_ht')
* 'devise'.'valeur'),2) AS 'reduced_ Net_price_£'
FROM (('produit' join 'tva') join 'devise')
WHERE (('tva'.'pays' = 'Royaume-Uni') and ('devise'.'devise' = 'Livre'));
Avec les prix en dollar :
CREATE VIEW 'produit_royaume_uni_dollar' AS
SELECT 'produit'.'id_produit' AS 'produit',
round((((('produit'.'prix_ht' * 'tva'.'normal') / 100) + 'produit'.'prix_ht')
* 'devise'.'valeur'),2) AS 'Net_price_$',
round((((('produit'.'prix_ht'* 'tva'.'reduit') / 100) + 'produit'.'prix_ht')
* 'devise'.'valeur'),2) AS 'reduced_ Net_price_$'
FROM (('produit' join 'tva') join 'devise')
WHERE (('tva'.'pays' = 'Royaume-Uni') and ('devise'.'devise' = 'Dollar'));
(à suivre… Conserver la structure d’une table)