Introduction
Le moteur de stockage « federated » a été introduit à partir de MySQL 5.0.3. Ce moteur de stockage permet d’introduire la fonctionnalité de tables proxy avec MySQL (comme avec les services C.I.S. de Sybase), fonctionnalité qui permet d’accéder aux données de tables existant physiquement dans des bases de données sur des serveurs MySQL distants.
Avec la version MySQL 5.0, seules des tables distantes de serveurs MySQL peuvent être visibles avec le moteur de stockage « federated ». Dans de futures versions, ces tables distantes pourront être des tables de bases de données Sybase, Oracle et autres SGBD.
Cet article se propose de regarder en profondeur ce qui se passe lors de l’utilisation des tables de type federated au sein de MySQL et montre clairement que les tables de type federated doivent être utilisées sur des tables de petite volumétrie ou bien en indiquant clairement des critères très restrictifs dans la sélection.
Cas pratique
Dans le cas pratique, deux serveurs MySQL DBA_T1_MYS et DBA_T2_MYS sont installés sur une même machine.
On désire pouvoir lire à partir du serveur DBA_T2_MYS la table t_asi_users existant dans la base de données cgcam du serveur DBA_T1_MYS.
Le moteur de stockage « federated » va permettre de mettre en œuvre cette technique : pour cela, la table t_asi_users sera créée localement dans la base de données cgcam_r du serveur DBA_T2_MYS et elle sera du type « federated », c’est-à-dire qu’elle ne référencera que la structure de la table distante t_asi_users du serveur DBA_T1_MYS ainsi que les paramètres de connexion au serveur DBA_T1_MYS pour lire les données de cette table.
Les tables de type « federated » seront plutôt appelées dans la suite de cet article des tables proxy et elles peuvent référencer tout type de table distante (MyISAM, InnoDB, ARCHIVE, etc.).
Lors de la création d’une table proxy dans une base de données, le fichier de définition <table_name>.frm de la table proxy est uniquement créé.
Mise en œuvre du cas pratique
Vérification de la disponibilité du moteur « federated »
Sur le serveur DBA_T2_MYS, il faut que le moteur « federated » soit disponible. La variable serveur have_federated_engine permet de savoir si ce moteur est disponible :
mysql-DBA_T2_MYS > show variables like '%federated%';+-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | have_federated_engine | YES | +-----------------------+-------+
Si le moteur de stockage « federated » n’est pas disponible :
- soit la version standard est utilisée au lieu d’une version Max ou Pro
- soit l’option
--with-federated-storage-enginen’a pas été utilisée lors de la compilation de MySQL.
Création des tables proxy
Syntaxe de création des tables proxy
Pour créer une table proxy dans une base de données MySQL, les mots clés ENGINE=FEDERATED et CONNECTION='<connect_string>' sont utilisés dans la syntaxe de la création de la table.
CREATE TABLE <table_name> (
[table_definition]
)
ENGINE=FEDERATED
CONNECTION='scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name'</table_name>
La chaîne CONNECTION définit les paramètres de connexion pour accéder au serveur distant hébergeant la table tbl_name dans la base de données db_name.
Exemples :
CONNECTION='mysql://qry_cgcam_r:mot_de_passe@CGC:40102/cgcam/t_asi_users'
schemeest toujours mysql pour la version 5.0 de MySQL, ce mot clé a été introduit pour le support futur de tables proxy référençant des tables sur des serveurs distants autres que MySQL (comme Sybase ou Oracle).- La chaîne password est actuellement définie en clair dans le mot clé
CONNECTION, aussi il faut faire attention aux informations de connexion utilisées et ne pas utiliser des users ayant trop de privilèges, car le mot de passe est accessible avec les commandesSHOW CREATE TABLE. Il faut garder à l’esprit que dans de futures versions, la chaîneCONNECTIONsubira des modifications pour pallier notamment à ce problème.
À la création de la table proxy, MySQL effectue une validation en se connectant au serveur distant spécifié dans la chaîne CONNECTION.
Création de la table proxy t_asi_users dans la base de données cgcam_r du serveur DBA_T2_MYS
Pour simplifier la création de la table proxy t_asi_users, il suffit de lancer la commande SHOW CREATE TABLE pour la table t_asi_users du serveur DBA_T1_MYS et d’adapter le résultat obtenu.
mysql-DBA_T1_MYS> show create table t_asi_users\G;*************************** 1. row *************************** Table: t_asi_users Create Table: CREATE TABLE `t_asi_users` ( `id` int(3) NOT NULL default '0', `login` char(3) NOT NULL default '', `nom` varchar(30) NOT NULL default '', `prenom` varchar(30) NOT NULL default '', `pwd` varchar(32) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `idx_login` (`login`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
Le user qry_cgcam_r sera utilisé pour se connecter au serveur DBA_T1_MYS depuis le serveur DBA_T2_MYS et les droits nécessaires sur la table t_asi_users dans la base de données cgcam sont donnés :
mysql-DBA_T1_MYS> grant all on cgcam.t_asi_users to 'qry_cgcam_r'@'%' identified by 'mot_de_passe';
mysql-DBA_T1_MYS> flush privileges;
La commande create table t_asi_users est alors lancée sur le serveur DBA_T2_MYS dans la base de données cgcam_r en spécifiant qu’il s’agit d’une table proxy avec le mot clé ENGINE=FEDERATED et en indiquant tous les paramètres de connexion au serveur DBA_T1_MYS (user qry_cgcam_r) grâce à la chaîne CONNECTION.
mysql-DBA_T2_MYS> use cgcam_r;
mysql-DBA_T2_MYS> CREATE TABLE `t_asi_users` (
`id` int(3) NOT NULL default '0',
`login` char(3) NOT NULL default '',
`nom` varchar(30) NOT NULL default '',
`prenom` varchar(30) NOT NULL default '',
`pwd` varchar(32) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_login` (`login`)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://qry_cgcam_r:mot_de_passe@CGC:40102/cgcam/t_asi_users';Une fois créée, nous n’avons qu’un fichier de définition de la table t_asi_users.frm dans la base de données cgcam_r. La mise en œuvre est alors terminée, depuis DBA_T2_MYS / cgcam_r, il est possible de lire le contenu de la table t_asi_users existant physiquement dans la base de données DBA_T1_MYS / cgcam.
mysql-DBA_T2_MYS> select login from t_asi_users;+-------+ | login | +-------+ | SPA | | FAF | | EMO | | GOA | +-------+
Limitations du moteur de stockage federated
- Dans une première version, le serveur distant doit être un serveur MySQL. Le support d’autres moteurs SGBD sera ajouté dans le futur.
- La table distante sur laquelle la table proxy pointe doit exister avant de tenter d’y accéder.
- Une table proxy peut pointer sur une autre table proxy.
- Les tables proxy ne supportent pas les transactions.
- Les tables proxy supportent les commandes
SELECT, INSERT, UPDATEetDELETE. Les commandes DDL commeALTER TABLEetDROP TABLEne sont pas supportées. - Les tables proxy ne peuvent bénéficier du cache de requêtes.
Que se passe-t-il durant les interrogations et mises à jour ?
Process
Lors de la première interrogation de la table t_asi_users depuis DBA_T2_MYS, un process est créé au sein du serveur DBA_T1_MYS et ce dernier est persistant : la connexion reste persistante après la première interrogation.
mysql-DBA_T1_MYS> show processlist;Id User Host db Command Time State Info 3 root localhost:1386 cgcam Query 0 NULL show processlist 4 DBA_T2_MYS_maint localhost:1390 NULL Binlog Dump 1202 Has sent all binlog NULL to slave; waiting for binlog to be updated 5 qry_cgcam_r localhost:1392 cgcam Sleep 1004 NULL
Ce process est unique même si il existe plusieurs clients sur DBA_T2_MYS qui interrogent t_asi_users.
Plans d’exécutions
Pour analyser ce qui se passe dans la communication entre DBA_T1_MYS et DBA_T2_MYS, le serveur DBA_T1_MYS est démarré avec l’option --log pour tracer toutes les requêtes qui sont exécutées sur ce serveur :
Fichier $CFG/DBA_T1_MYS.cnf ( $CFG = /Software/mysql/dba/DBA_T1_MYS/cfg ) :
$CFG/DBA_T1_MYS.cnf
...
log = /Software/mysql/dba/DBA_T1_MYS/log/query/DBA_T1_MYS.genq.log
...Plans d’exécution dans les sélections
Sur le serveur DBA_T2_MYS, la table proxy t_asi_users est interrogée plusieurs fois de la façon suivante :
mysql-DBA_T2_MYS> select login from t_asi_users where login='SPA';
mysql-DBA_T2_MYS> select login from t_asi_users where login='SPA';
mysql-DBA_T2_MYS> select login from t_asi_users where login='SPA';
Le fichier de log du serveur DBA_T1_MYS affiche alors les requêtes ci-dessous :
050926 15:33:05 2 Connect qry_cgcam_r@localhost on cgcam
2 Query SHOW TABLE STATUS LIKE 't_asi_users'
2 Query SELECT `id`, `login`, `nom`, `prenom`, `pwd` FROM
`t_asi_users` WHERE (`login` = 'SPA')
050926 15:33:06 2 Query SHOW TABLE STATUS LIKE 't_asi_users'
2 Query SELECT `id`, `login`, `nom`, `prenom`, `pwd` FROM
`t_asi_users` WHERE (`login` = 'SPA')
050926 15:33:07 2 Query SHOW TABLE STATUS LIKE 't_asi_users'
2 Query SELECT `id`, `login`, `nom`, `prenom`, `pwd` FROM
`t_asi_users` WHERE (`login` = 'SPA')Deux points importants apparaissent :
- à chaque exécution d’une requête sur la table proxy, l’existence de la table est validée sur le serveur distant avec la commande
SHOW TABLE STATUS LIKE 't_asi_users'; - toutes les colonnes définies dans la table proxy en fonction des critères spécifiés sont retournées au client même si le client ne sélectionne qu’une seule colonne de la table.
A présent, la table t_asi_users est altérée dans la base de données cgcam du serveur primaire DBA_T1_MYS avec l’ajout d’une colonne test_col mais la définition de la table proxy t_asi_users dans la base de données cgcam_r du serveur DBA_T2_MYS n’est pas modifiée en conséquence :
mysql> alter table t_asi_users add column test_col varchar(2) not null default '';
Aucun problème ne se pose avec l’altération effectuée car il s’agit d’un ajout de colonnes (en cas de suppressions ou de modifications de colonnes, il en est tout autrement bien sur) :
mysql-DBA_T2_MYS> select login from t_asi_users where login='SPA';
mysql-DBA_T2_MYS> select login from t_asi_users where login='SPA';
Le fichier de log du serveur DBA_T1_MYS affiche alors les requêtes ci-dessous :
050926 16:00:29 2 Query SHOW TABLE STATUS LIKE 't_asi_users'
2 Query SELECT `id`, `login`, `nom`, `prenom`, `pwd` FROM
`t_asi_users` WHERE (`login` = 'SPA')
050926 16:00:39 2 Query SHOW TABLE STATUS LIKE 't_asi_users'
2 Query SELECT `id`, `login`, `nom`, `prenom`, `pwd` FROM
`t_asi_users` WHERE (`login` = 'SPA')Plans d’exécutions dans les mises à jour (UPDATE et DELETE)
Pour les commandes DELETE et UPDATE lancées sur une table proxy, la ligne ou les lignes candidates à la mise à jour sont extraites et les commandes DELETE et UPDATE sont alors lancées pour chaque ligne avec des clauses WHERE portant sur toutes les colonnes définies dans la table proxy :
mysql-DBA_T2_MYS> update t_asi_users set prenom='Test2';050926 16:48:12 2 Query SHOW TABLE STATUS LIKE 't_asi_users' 2 Query SELECT `id`, `login`, `nom`, `prenom`, `pwd` FROM `t_asi_users` 2 Query UPDATE `t_asi_users`SET id = 7, login = 'FAF', nom = 'TEST', prenom = 'Test2', pwd = '0d76256527f8d6a15d7148f192f4b26a' WHERE id = 7 AND login = 'FAF' AND nom = 'TEST' AND prenom = 'Test' AND pwd = '0d76256527f8d6a15d7148f192f4b26a' 2 Query UPDATE `t_asi_users`SET id = 9, login = 'EMO', nom = 'TEST', prenom = 'Test2', pwd = '841c1220eed079745c7d65440c841f7e' WHERE id = 9 AND login = 'EMO' AND nom = 'TEST' AND prenom = 'Test' AND pwd = '841c1220eed079745c7d65440c841f7e' 2 Query UPDATE `t_asi_users`SET id = 10, login = 'GOA', nom = 'TEST', prenom = 'Test2', pwd = '9c1a0edf56083f7ab0e7ddd60e7e7779' WHERE id = 10 AND login = 'GOA' AND nom = 'TEST' AND prenom = 'Test' AND pwd = '9c1a0edf56083f7ab0e7ddd60e7e7779'
Pour les commandes UPDATE, bien que la commande UPDATE porte sur la colonne prenom, les autres colonnes (id, login, nom et pwd) sont également mises à jour pour réappliquer les valeurs obtenues lors de la sélection. Ce comportement n’est pas particulièrement optimal.
mysql-DBA_T2_MYS> delete from t_asi_users where prenom like 'Test2%';050926 16:48:12 2 Query SHOW TABLE STATUS LIKE 't_asi_users' 2 Query SELECT `id`, `login`, `nom`, `prenom`, `pwd` FROM `t_asi_users` 2 Query DELETE FROM `t_asi_users` WHERE id = 7 AND login = 'FAF' AND nom = 'TEST' AND prenom = 'Test2' AND pwd = '0d76256527f8d6a15d7148f192f4b26a' LIMIT 1 2 Query DELETE FROM `t_asi_users` WHERE id = 9 AND login = 'EMO' AND nom = 'TEST' AND prenom = 'Test2' AND pwd = '841c1220eed079745c7d65440c841f7e' LIMIT 1 2 Query DELETE FROM `t_asi_users` WHERE id = 10 AND login = 'GOA' AND nom = 'TEST' AND prenom = 'Test2' AND pwd = '9c1a0edf56083f7ab0e7ddd60e7e7779' LIMIT 1
Les commandes DELETE et UPDATE doivent en conséquence être utilisées avec des clauses très restrictives compte tenu des plans d’exécution générées.
Par ailleurs :
- que se passe-t-il si un utilisateur modifie entre temps une ligne impliquée dans le paquet d’ordres
UPDATEouDELETEenvoyées ? - pourquoi les ordres
UPDATEmettent à jour systématiquement des colonnes qui ne sont pas du tout impliquées dans la mise à jour ?
Il n’y a rien à signaler pour les commandes INSERT sur une table proxy.
Conclusion
En bref :
- pour la sélection : rapatriement de toutes les colonnes définies dans la table proxy indépendamment des colonnes sélectionnées dans la requête sur la table proxy.
- pour la mise à jour : rapatriement de toutes les colonnes définies pour la table proxy pour toutes les lignes candidates à la mise à jour avec les critères spécifiés, puis envoi des commandes
UPDATEetDELETEpour chacune des lignes avec les clausesWHEREnécessaires.
Compte tenu de ce comportement, il est nécessaire de faire très attention aux tables proxy pour éviter des E/S intensifs (rapatriement de toutes les colonnes pour un grand nombre de lignes) et dans la mesure du possible, des clauses très restrictives (quelques lignes) doivent être appliquées durant la sélection ou la mise à jour de tables proxy.