dasini.net – Journal d'un expert MySQL

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

Masquer la complexité du schéma

L’équipe de développement doit écrire un moteur de recherche pour une application de commerce électronique. Voici un extrait des tables de la base de données impliquées dans la recherche des produits du site.

La difficulté est de générer la bonne requête avec les bonnes jointures (plus d’une dizaine), à chaque recherche. Une solution pour faciliter le travail des développeurs est de créer une vue qui fait référence à toutes les tables impliquées dans la recherche des produits. Les recherches se feront donc sur cette vue, avec des requêtes plus simples à écrire.

La vue est créée avec l’algorithme TEMPTABLE, les verrous sur les tables sous-jacentes seront libérés plus rapidement ce qui permettra de moins pénaliser les autres requêtes. TEMPTABLE a la particularité de rendre la vue non modifiable, mais cela ne gêne pas du tout, car la vue n’est accédée qu’en lecture.

Extrait de la vue « moteur de recherche »:

CREATE ALGORITHM = TEMPTABLE
VIEW moteur_de_recherche
AS
SELECT
   s.product_name as nom_produit,
   ... (les champs nécessaires)
FROM product s
 LEFT JOIN product_files sf ON s.product_id=sf.file_product_id
 LEFT JOIN ..... (toutes les tables impliquées)
WHERE ... ;

Cette astuce permet de « simplifier » de façon assez significative le schéma.

Un dernier mot pour noter que cette solution ne permet pas d’améliorer les performances de la recherche, dans le sens où la requête qui génère la vue est lancée à chaque appel de cette dernière.

(à suivre… Modifier automatiquement des données sélectionnées)

La haute disponibilité consiste à faire en sorte qu’un service ou une architecture soit le moins souvent indisponible…

http://dasini.net/blog/presentations/?#Haute_dispo_avec_MySQL

PDF à télécharger

(<- précédent)

Le moteur de stockage CSV

Le moteur de stockage CSV n’est pas une vraie nouveauté. Il est disponible depuis MySQL 4.1.4 (MySQL 5.1 pour M.S. Windows). Sa particularité est de stocker les données dans un fichier texte au format CSV (Comma Separated Values) où les données sont séparées par une virgule. Les avantages sont multiples, comme la possibilité d’éditer les données avec un éditeur de texte ou un tableur(Open Office Calc, Excel…) ou encore la possibilité de charger simplement d’énormes volumes de données dans le serveur MySQL.

Créer sa table au format CSV est très simple. On reste dans la logique MySQL de création de table : il faut en fait simplement spécifier CSV comme moteur (il n’est pas possible d’avoir des index, ni du partitionnement).

Pour créer une table au format CSV:

CREATE TABLE t_csv (
   id int, nom CHAR(50),
   prenom CHAR(50)
) ENGINE=CSV;

Le support de XML/XPath

A partir de MySQL version 5.1.5, un support basique de XML est implémenté sous la forme de deux fonctions: ExtractValue et UpdateXML.

• ExtractValue permet d’extraire les valeurs des différentes balises,

• UpdateXML permet de modifier la sortie d’un document XML.

Quelques exemples d’utilisation de ces deux fonctions avec la table t_xml et le fichier XML suivant (contenu dans le champ texte de la table t_xml)

Fichier XML et la table t_xml:

<?xml version="1.0" encoding="ISO-8859-1"?>
<profil>
   <formation categorie="MySQL" nbr_formation="5">
      <formateur>Olivier DASINI</formateur>
      <formateur>Pierre DUMONT</formateur>
      <Entreprise>Anaska</Entreprise>
   </formation>
   <formation categorie="PHP" nbr_formation="5">
      <formateur>Olivier DASINI</formateur>
      <formateur>Cyril PIERRE DE GEYER</formateur>
      <formateur>Romain BOURDON</formateur>
      <formateur>Julien PAULI</formateur>
      <Entreprise>Anaska</Entreprise>
   </formation>
   <formation categorie="Linux" nbr_formation="10">
      <formateur>Pierre DUMONT</formateur>
      <formateur>Fabien ALLARD</formateur>
      <Entreprise>Anaska</Entreprise>
   </formation>
   <formation categorie="Open Office" nbr_formation="3">
      <formateur>Sarah HAIM</formateur>
      <Entreprise>Anaska</Entreprise>
   </formation>
</profil>

CREATE TABLE t_xml (
   id int(11) NOT NULL AUTO_INCREMENT,
   texte text,
   PRIMARY KEY (id)
) ENGINE=MyISAM;

Pour avoir la liste des formateurs :

mysql> SELECT EXTRACTVALUE(texte,'/profil/formation/formateur') AS result FROM
t_xml\G
*************************** 1. row ***************************
result: Olivier DASINI Pierre DUMONT Olivier DASINI Cyril PIERRE DE GEYER Romain
BOURDON Julien PAULI Pierre DUMONT Fabien ALLARD Sarah HAIM
1 row in set (0.00 sec)

Récupérer seulement les formateurs MySQL :

mysql>SELECT  EXTRACTVALUE(texte,'/profil/formation[contains(@categorie,"MySQL")
]/formateur') AS result FROM t_xml\G
*************************** 1. row ***************************
result: Olivier DASINI Pierre DUMONT
1 row in set (0.09 sec)

Remplacer <formateur>Olivier DASINI</formateur> par <formateur>Freshdaz</formateur>

mysql> SELECT UPDATEXML('<profil><formation categorie="MySQL" nbr_formation="5">
<formateur>Olivier DASINI</formateur><formateur>Pierre DUMONT</formateur>
</formation></profil>', '/profil/formation/formateur[contains(.,"Olivier")]'
, '<formateur>Freshdaz</formateur>') AS result\G
*************************** 1. row ***************************
result: <profil><formation categorie="MySQL" nbr_formation="5">
<formateur>Freshdaz</formateur><formateur>Pierre DUMONT</formateur>
</formation></profil>
1 row in set (0.00 sec)

(à suivre… mysqlslap)

Ca y est,it’s over 🙁

Néanmoins, un constat s’impose: excellent cru !

Je n’ai malheureusement pu être présent qu’a deux conférences:
Architectures haute disponibilité avec MySQL… et pour cause 🙂
La salle était rempli, ce qui prouve que MySQL est un sujet qui intéresse.

L’excellent Damien Seguy avec Hackez moi ça, qui traite de la sécurité à fait un véritable show. La salle était malheureusement trop petite pour tout le monde.

En tout cas, félicitation aux conférenciers, à toute l’équipe et au président Arnaud Limbourg.

Merci à tous et vivement le prochain 🙂

Utiliser les vues

Voici quelques exemples pratiques très simples pour illustrer les différents besoins auxquels peuvent répondre les vues. On aura ici, une vue administrateur de base de données. Les objets créés ne seront pas utilisés directement par les utilisateurs mais aux travers d’une application.

Contrôler l’intégrité en restreignant l’accès aux données pour améliorer la confidentialité

La table employe de mon application, contient toutes les informations sur les employées.

CREATE TABLE `employe` (
  `id_employe` int(10) unsigned NOT NULL auto_increment,
  `nom` char(45) NOT NULL,
  `prenom` char(45) NOT NULL,
  `tel_perso` char(10) NOT NULL,
  `tel_bureau` char(10) NOT NULL,
  `statut` char(45) NOT NULL,
  `ville` char(45) NOT NULL,
  `salaire` decimal(7,2) NOT NULL,
  PRIMARY KEY  (`id_employe`)
) ;

Toutes les informations présentes dans cette table ne sont pas pertinentes pour les trois types d’utilisateurs suivant: le comptable, la secrétaire pour Paris et la secrétaire pour le reste de la France.

Une solution est donc de créer une vue par type.

Pour le comptable, il faut avoir accès aux champs nom, prénom, téléphone du bureau, statut et salaire de chaque employée. On fait donc un partitionnement vertical de la table employe. La vue correspondante est la suivante :

CREATE ALGORITHM=MERGE  SQL SECURITY DEFINER VIEW `v_comptable` AS
SELECT nom, prenom, tel_bureau, statut, salaire FROM employe;

Le profil « secrétaire pour Paris », n’a pas besoin de l’identifiant et il ne doit surtout pas avoir accès aux salaires, cette information étant confidentielle. Autre restriction, ce profil ne gère que les employées de la filiale de Paris. Le partitionnement est vertical et horizontal.

CREATE ALGORITHM= MERGE SQL SECURITY DEFINER VIEW `v_secretaire_paris` AS
SELECT nom, prenom, tel_perso, tel_bureau, statut FROM employe
WHERE ville = 'Paris';

Notre troisième vue est très proche de la deuxième. La seule différence vient du fait que là, on veut les employés qui ne travaillent pas à Paris.

CREATE ALGORITHM= MERGE SQL SECURITY DEFINER VIEW `v_secretaire_autre` AS
SELECT nom, prenom, tel_perso, tel_bureau, statut FROM employe
WHERE ville <> 'Paris';

(à suivre… Masquer la complexité du schéma)

(<- précédent)

MySQL Cluster : support des données sur disque

MySQL propose une solution de haute disponibilité : MySQL Cluster. Cette technologie permet de mettre en place une architecture cluster shared nothing à l’aide de tables au format NDBCluster. Jusqu’à MySQL 5.0, MySQL Cluster ne fonctionnait qu’entièrement en mémoire. Ceci excluait donc certaines bases de données trop volumineuses. La nouvelle version 5.1 lève cette limitation en permettant de mettre les données sur disque.

Pour enregistrer ses données sur le disque, il faut au préalable définir deux nouveaux objets : un LOGFILE GROUP et un TABLESPACE.

LOGFILE GROUP : permet gérer les undo log et le crash-recovery

Pour créer un logfile group:

CREATE LOGFILE GROUP lg_1
   ADD UNDOFILE 'undo_1.dat'
   INITIAL_SIZE 16M
   UNDO_BUFFER_SIZE 2M
   ENGINE NDB;
ALTER LOGFILE GROUP lg_1
   ADD UNDOFILE 'undo_2.dat'
   INITIAL_SIZE 12M
   ENGINE NDB;

TABLESPACE : pour stocker les données. A noter que le tablespace utilise le logfile group créé

On peut maintenant créer la table et l’associer au tablespace

Création d’un tablespace:

CREATE TABLESPACE ts_1
   ADD DATAFILE 'data_1.dat'
   USE LOGFILE GROUP lg_1
   INITIAL_SIZE 32M
   ENGINE NDB;
ALTER TABLESPACE ts_1
   ADD DATAFILE 'data_2.dat'
   INITIAL_SIZE 48M
   ENGINE NDB;

Création d’une table au format NDBCluster avec données sur disque:

CREATE TABLE 'City' (
   'ID' int(11) NOT NULL AUTO_INCREMENT,
   'Name' char(35) NOT NULL DEFAULT '',
   'Population' int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY ('ID')
) TABLESPACE ts_1
   STORAGE DISK
   ENGINE=NDB;

Les tables de journalisation

MySQL offre la possibilité de journaliser l’activité du serveur. en activant le general log. Il est aussi est possible de journaliser seulement les requêtes lentes (celles qui s’exécutent en un temps supérieur au seuil que vous aurez préalablement fixé) en activant le slow_query_log.

MySQL 5.1 permet toujours de journaliser ces informations dans un fichier, et ajoute la possibilité de le faire en plus dans une table.

Cerise sur le gâteau, la journalisation peut être démarrée ou arrêtée à chaud.

Les commandes SHOW VARIABLES LIKE ‘general_log’ et SHOW VARIABLES LIKE ‘log_output’ permettent, respectivement, de savoir si le general_log est activé et sous quelle forme.

Pour afficher l’état du general_log:

mysql> SHOW VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.03 sec)

mysql> SHOW VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+

Le general_log peut être activé dynamiquement avec la commande SET :

mysql> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.04 sec)

Le format de sortie peut lui aussi être changé dynamiquement :

+---------------------+-------------------------+-----------+-----------
+--------------+---------------------------------------+
| event_time          | user_host               | thread_id | server_id
| command_type | argument                              |
+---------------------+-------------------------+-----------+-----------
+--------------+---------------------------------------+
| 2008-02-21 15:51:06 | daz[daz] @ localhost [] |        10 |        51
| Query        | SET GLOBAL log_output = ‘TABLE, FILE' |
| 2008-02-21 15:51:06 | daz[daz] @ localhost [] |        10 |        51
| Query        | SELECT * FROM mysql.general_log       |
+---------------------+-------------------------+-----------+-----------
+--------------+---------------------------------------+
2 rows in set (0.00 sec)

mysql> SET GLOBAL log_output = 'TABLE,FILE';
Query OK, 0 rows affected (0.00 sec)

Les mêmes opérations sont possibles sur le slow_query_log :

mysql> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name | Value  |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.slow_log;
+------------+----------------------+-----------+-----------+---------------+-----+
| start_time | user_host query_time | lock_time | rows_sent | rows_examined | db |
      | last_insert_id | insert_id | server_id | sql_text
-----------------------------------------------------------------------------------
| 2008-02-21 15:57:13 | daz[daz] @ localhost [] | 00:00:00 | 00:00:00 | 0 | 0 |
      | test | 0 | 0 | 51 | SELECT * FROM mysql.slow_log |
| 2008-02-21 15:57:38 | daz[daz] @ 640m [192.168.1.106] | 00:00:00 | 00:00:00 | 268 |
      | 268 | mysql | 0 | 0 | 51 | SELECT * FROM information_schema.
      GLOBAL_VARIABLES G |
| 2008-02-21 15:57:43 | daz[daz] @ localhost [] | 00:00:00 | 00:00:00 | 2 | 2 | test |
      | 0 | 0 | 51 | SELECT * FROM mysql.slow_log |
+------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

(à suivre… Le moteur de stockage CSV & Le support de XML/XPath)

(<- précédent)

Le programmateur d’évènements

Pouvoir automatiser ses tâches de manière fiable et simple est le rêve de tout administrateur de base de données. Le programmateur d’évènements (Event Scheduler) est un planificateur de tâches (CRON-like) embarqué dans MySQL 5.1.

Il est alors possible d’exécuter, de façon récurrente ou unique, des requêtes, en fonction de la date et de l’heure.

L’évènement se crée avec la commande CREATE EVENT.

CREATE EVENT nom_evenement ON SCHEDULE
      <moment> DO <code_sql>

L’évènement peut être lancé une seule fois (AT) ou de manière répétitive (EVERY)

<moment> = AT | EVERY

L’évènement est constitué d’un ensemble de requêtes.

<code_sql> = requêtes sql

Créer une vue matérialisée rafraîchie toutes les 10 minutes :

DELIMITER //
CREATE EVENT vue_materialisee
ON SCHEDULE EVERY 10 MINUTE
DO
BEGIN
   TRUNCATE TABLE _event.City_fra;
   INSERT INTO _event.City_fra
      SELECT * FROM world.City WHERE CountryCode='FRA'
      ORDER BY name;
END//
DELIMITER ;

La réplication par les données (row based)

MySQL permet de journaliser dans un fichier les requêtes d’écriture effectuées sur le serveur. Ce fichier, le binary log, stocke ces requêtes en un format binaire (d’où son nom). Il est indispensable à la réplication, et est également utilisé pour la restauration du serveur.

A partir de MySQL 5.1, le serveur permet de stocker cette information, non plus seulement sous forme de requêtes (statement based) mais également sous forme de lignes (row based). Cette nouvelle fonctionnalité est intéressante, en particulier en cas de réplication de requêtes non déterministes.

La commande SHOW VARIABLES LIKE ‘binlog_format’ permet de connaître le format du journal binaire

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

Trois options de journalisation sont disponibles :

• Statement based (mode par défaut) : On journalise la requête telle-quelle (pas son résultat),
• Row based : On journalise le résultat de la requête,
• Mixed : MySQL choisit entre statement et row en fonction du contexte.

Le mode de journalisation peut être changé dynamiquement avec la commande SET

mysql> SET SESSION binlog_format='ROW';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> SET SESSION binlog_format='STATEMENT';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)

mysql> SET SESSION binlog_format='MIXED';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)

(à suivre… MySQL Cluster & general log, slow query log dans une table)

Le Forum PHP 2008 aura lieu les 8 et 9 décembre 2008 à l’ASIEM dans le VIIème arrondissement à Paris (France).

• ouverture des portes : 8h30
• début des sessions : 9h
• fin des sessions : vers 18h

Les thèmes principaux pour cette édition sont :

Web services professionnels et Grands projets en PHP : organisation, méthodes et bonnes pratiques.

Voici quelques conférenciers:

Zeev Zuraski, co-architecte de PHP et co-fondateur de Zend

Zak Greant est un contributeur de longue date à PHP. Il participe aussi activement à Mozilla et à la FSF.

Laura Thompson est salariée de Mozilla en tant que Senior Software Engineer. Elle a entre autres mis sur pied le site des add-ons avec CakePHP.

Lukas Smith est devenu en quelques années une des pièces maitresse de PHP : en particulier, il coordonne tous les développements via le wiki de PHP.

Cyril PIERRE de GEYER (conf1 & conf2) est expert PHP pour Anaska du groupe AlterWay, l’organisme de formation pour l’OpenSource. Co auteur du livre PHP5 avancé il est l’un des fondateurs et ancien président de l’Association française des utilisateurs de PHP et participe aux sites PHPteam.net et PHPfrance.

Damien Seguy est un des fondateurs de l’hébergeur Open Source Nexen Services, maintenant partie du groupe Alter Way, où il développe les services experts. Il participe à la promotion de PHP, MySQL et des logiciels libres auprès des professionnels et des institutions. Il est aussi connu pour être le PHPère des éléPHPants en peluche, avoir lancé PHP Québec et l’AFUP, édité le livre ‘Sécurité PHP 5 et MySQL 5’ et les certifications PHP chez l’éditeur Zend.

Olivier DASINI est responsable du pôle SGBD, consultant et formateur certifié MySQL pour la société ANASKA ALTER WAY. Il est l’auteur de nombreux articles et participe à la vie de la communauté MySQL au travers de nombreux forums et sites. Il est également co-fondateur du MySQL User Group francophone (LeMUG.fr)