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
Tags: cluster, Haute disponibilité, MySQL, replication, shared disk
Archive for décembre, 2008
(<- précédent)
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)
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)
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
Tags: Forum PHP
(<- précédent)
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)
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)
Tags: general log, MySQL, MySQL 5.1, MySQL Cluster, slow query log
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)
Tags: event scheduler, MySQL, MySQL 5.1, replication, row based, statement based
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)
Tags: Conférence, Forum PHP
Ads
