dasini.net – Journal d'un expert MySQL

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:

SHOW CREATE TABLE city\G
*************************** 1. row ***************************
Table: city
Create Table: CREATE TABLE city (
ID int(11) NOT NULL AUTO_INCREMENT,
Name char(35) NOT NULL DEFAULT  »,
CountryCode char(3) NOT NULL DEFAULT  »,
District char(20) NOT NULL DEFAULT  »,
Population int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

La commande EXPLAIN, permet d’avoir le plan d’exécution:

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using temporary; Using filesort

L’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:

SHOW STATUS LIKE ‘Last_query_cost’\G

*************************** 1. row ***************************
Variable_name: Last_query_cost
Value: 4963.520924

Ajoutons un index sur la colonne countrycode:

ALTER TABLE city ADD INDEX Idx_cc(CountryCode);

SHOW CREATE TABLE city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE city (

ID int(11) NOT NULL AUTO_INCREMENT,

Name char(35) NOT NULL DEFAULT  »,

CountryCode char(3) NOT NULL DEFAULT  »,

District char(20) NOT NULL DEFAULT  »,

Population int(11) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (ID),

KEY Idx_cc (CountryCode)

) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: city

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4079

Extra: Using temporary; Using filesort

L’index Idx_cc ne sert à rien

Ajoutons alors un index sur la colonne population

ALTER TABLE city ADD INDEX Idx_population(Population);

SHOW CREATE TABLE city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE city (

ID int(11) NOT NULL AUTO_INCREMENT,

Name char(35) NOT NULL DEFAULT  »,

CountryCode char(3) NOT NULL DEFAULT  »,

District char(20) NOT NULL DEFAULT  »,

Population int(11) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (ID),

KEY Idx_cc (CountryCode),

KEY Idx_population (Population)

) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: c

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4079

Extra: Using temporary; Using filesort

Pas mieux ! L’index Idx_population ne sert à rien.

Effacement des 2 index:

ALTER TABLE city DROP INDEX Idx_cc, DROP INDEX Idx_population;

Ajoutons un index composite sur les colonnes population, countrycode:

ALTER TABLE city ADD INDEX Idx_population_cc(Population, CountryCode);

SHOW CREATE TABLE city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE `city` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT  »,

`CountryCode` char(3) NOT NULL DEFAULT  »,

`District` char(20) NOT NULL DEFAULT  »,

`Population` int(11) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (`ID`),

KEY `Idx_population_cc` (`Population`,`CountryCode`)

) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: c

type: index

possible_keys: NULL

key: Idx_population_cc

key_len: 7

ref: NULL

rows: 4079

Extra: Using index; Using temporary; Using filesort

Ç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:

SHOW STATUS LIKE ‘Last_query_cost’\G

*************************** 1. row ***************************
Variable_name: Last_query_cost
Value: 4963.520924

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

ALTER TABLE test2.city ADD INDEX Idx_cc_population(CountryCode,Population);

SHOW CREATE TABLE city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE `city` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT  »,

`CountryCode` char(3) NOT NULL DEFAULT  »,

`District` char(20) NOT NULL DEFAULT  »,

`Population` int(11) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (`ID`),

KEY `Idx_population_cc` (`Population`,`CountryCode`),

KEY `Idx_cc_population` (`CountryCode`,`Population`)

) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

EXPLAIN SELECT AVG(Population) FROM city GROUP BY CountryCode\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: c

type: index

possible_keys: NULL

key: Idx_cc_population

key_len: 7

ref: NULL

rows: 4079

Extra: Using index

1 row in set (0.00 sec)

SHOW STATUS LIKE ‘Last_query_cost’\G

*************************** 1. row ***************************
Variable_name: Last_query_cost
Value: 4963.520924

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

FLUSH STATUS;

En utilisant l’index idx_population_cc (le mauvais index)

SELECT AVG(Population) FROM city use index(idx_population_cc) GROUP BY CountryCode;

…

232 rows in set (0.00 sec)

SHOW STATUS LIKE ‘handler%’;

 +----------------------------+-------+

 | 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:

SHOW SESSION STATUS LIKE ‘sort%’;

 +-------------------+-------+

 | 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.

SHOW SESSION STATUS LIKE ‘created%’;

 +-------------------------+-------+

 | 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

FLUSH STATUS;

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)

SHOW STATUS LIKE ‘handler%’;

 +----------------------------+-------+

 | 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 🙂

SHOW SESSION STATUS LIKE ‘sort%’;

 +-------------------+-------+

 | Variable_name     | Value |

 +-------------------+-------+

 | Sort_merge_passes | 0     |

 | Sort_range        | 0     |

 | Sort_rows         | 0     |

 | Sort_scan         | 0     |

 +-------------------+-------+

pas de tri

SHOW SESSION STATUS LIKE ‘created%’;

 +-------------------------+-------+

 | 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:

• Assurer la promotion de MySQL
• L’entraide des utilisateurs avec notamment un forum (http://www.developpez.net/forums/f44/bases-donnees/mysql/)
• Organiser des rencontres inter communautés, barcamp, conférences… (http://www.lemug.fr/actualite/)
• Participer au développement de MySQL, en contribuant à certains travaux (développements, documentation…)

…

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:

 

1/ Arrêter le serveur MySQL

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 🙂

 

2/ Démarrer le serveur en désactivant la vérifications des droits

$ 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.

 

3/ Connexion au serveur MySQL

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>

 

4/ Protéger le serveur

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)

 

5/ Changer le mot de passe

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)

 

6/ Arrêter le serveur

Pour sortir de la configuration skip-grant-tables, il faut arrêter le serveur, pour mieux le redémarrer…

 

7/ Redémarrer le serveur normalement

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>

 

(<- précédent)

Conserver la structure d’une table si elle doit être modifiée

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.

Conclusion

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/

(<- précédent)

Modifier automatiquement des données sélectionnées

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)

(<- précédent)

mysqlslap : un utilitaire de test de charge et de performance

Disponible depuis MySQL 5.1.4, mysqlslap permet d’effectuer des tests de stress et de charge sur votre serveur MySQL. Vous pourrez alors tester les performances de votre SGBDR préféré, après, par exemple, une nouvelle installation, un changement d’architecture ou après avoir modifié des paramètres de configuration.

Créé pour devenir le meilleur ami des administrateurs de bases de données et des développeurs, le client mysqlslap envoie des requêtes au serveur MySQL en créant plusieurs connexions simultanées. A la fin de la simulation, un rapport de diagnostic est crée sur la sortie standard. Vous avez aussi la possibilité de l’écrire dans un fichier au format CSV utilisable directement avec Calc d’OpenOffice ou Excel de Microsoft.

Exemple d’utilisation de mysqlslap avec les options suivantes :

• user, password, socket : autentification classique d’un client MySQL,
• concurrency : nombre de clients effectuant simultanément une requête SELECT,
• iterations : nombre d’itérations du test à faire,
• number-of-queries : nombre total de requêtes effectuées lors du test,
• engine : moteur(s) à utiliser,
• auto-generate-sql : mysqlslap génère automatiquement un jeu de tests,
• number-int-cols : nombre de colonnes de type INT à créer,
• number-char-cols : nombre de colonnes de type CHAR à créer,
• auto-generate-sql-load-type : permet de spécifier le type de requêtes (lecture, insertion, lecture sur la clé primaire, mise à jour ou mixte)

Tests de performances avec des tables MyISAM et innoDB:

shell> mysqlslap --user=daz --password --socket=/tmp/mysql51.sock
--concurrency=1,100   --iterations=10  --number-of-queries=1000
--engine=myisam,innodb   --auto-generate-sql  --number-int-cols=2
--number-char-cols=3   --auto-generate-sql-load-type=mixed


Benchmark
   Running for engine myisam
   Average number of seconds to run all queries: 1.576 seconds
   Minimum number of seconds to run all queries: 1.539 seconds
   Maximum number of seconds to run all queries: 1.631 seconds
   Number of clients running queries: 1
   Average number of queries per client: 1000
Benchmark
   Running for engine myisam
   Average number of seconds to run all queries: 1.792 seconds
   Minimum number of seconds to run all queries: 1.543 seconds
   Maximum number of seconds to run all queries: 2.107 seconds
   Number of clients running queries: 100
   Average number of queries per client: 10
Benchmark
   Running for engine innodb
   Average number of seconds to run all queries: 2.477 seconds
   Minimum number of seconds to run all queries: 2.430 seconds
   Maximum number of seconds to run all queries: 2.663 seconds
   Number of clients running queries: 1
   Average number of queries per client: 1000
Benchmark
   Running for engine innodb
   Average number of seconds to run all queries: 2.704 seconds
   Minimum number of seconds to run all queries: 0.725 seconds
   Maximum number of seconds to run all queries: 5.272 seconds
   Number of clients running queries: 100
   Average number of queries per client: 10

Conclusion

Toujours aussi performant, fiable et robuste, certes un peu moins simple à administrer mais tellement plus riche, cette nouvelle version de MySQL devrait faire le bonheur des administrateurs de bases de données et des développeurs. Nous espérons que cet article vous a mis l’eau à la bouche et vous donnons d’ors et déjà rendez-vous au prochain numéro pour explorer plus en détail le partitionnement de MySQL.

Les nouveautés de MySQL 5.1 — (part 1/5)

Quelques jours de vacances 🙂 , une interruption de service  🙁 , un changement d’hébergeur, …  et une nouvelle année !

Que demande, le peuple informaticien ? des logiciels performants, qui améliorent la productivité et peu coûteux, en ses temps de crises, l’open source peut être une (petite) partie de la réponse.

Au nom de toute l’équipe, je vous souhaite une bonne et heureuse année 2009.