Tip MySQL: "innodb_file_per_table" ou comment utiliser des fichiers de table séparés pour éviter le manque d’espace disque!!

Bon,
je suis pas très fier de ce que j’ai fait mais je vous relate mes erreurs et comment je suis arrivé à les résoudre pour vous éviter de faire pareil…

Situation

J’installe Mysql sur mon nouveau serveur dédié, monstre de calcul (8 cpu « machin » bridge) et d’espace disque ( 1To). Je configure ma base de données au format  Innodb ( le format qui permet les contraintes externes, etc.); je configure les capacités de mémoire, l’espace disque temporaire;
bref, je crois avoir tout passé en revue…

Puis j’alimente ma base avec des données provenant de réseaux sociaux – autant dire que la volumétrie est importante.

Out of space

Au bout de quelques semaines, je m’aperçoit que le fichier ibdata qui est dans le répertoire de données grossit mais ne rétrécit jamais , y compris lorsque je supprime des tables de plusieurs centaines de GigaOctets.
Finalement j’atteins la taille limite de mon disque et là je suis mal surtout que je dois produire des rapports d’analyse pour avant hier.

L’option de la mort qui tue

C’est là  que je m’aperçoit que par défaut à  l’installation,  MySQL et configuré pour gérer toutes les tables et leurs index dans un seul et même fichier –  le fameux « ibdata » et que ce fichier  est voué a grossir –  jamais à  réduire !
Il aurait fallu utiliser l’option « innodb_file_per_table » dans le fichier de configuration (my.cnf) pour que chaque table utilise son propre fichier de stockage des données et d’index.
Ok, je l’ai pas fait; mais alors comment faire pour rattraper ma bourde ?

Play it like a barbarian

Oui, je dirais qu’il faut y aller « à  la barbare » c’est à  dire faire une réinstallation des bases!
Première chose, faire une sauvegarde complète des bases : 

mysqldump -R -q -p --all-databases > 20120402_mysql_all.sql

Ensuite, arrêter MySQL et supprimer tous les fichiers du répertoire des bases de données( moi, je fais un move sur un support de sauvegarde):

service mysql stop
mv  /home/mysql/* /home/backup/mysql_old_dir

Redémarrer Mysql :

service mysql start

Enfin, restaurer les tables :

mysql -u root  < 20120402_mysql_all.sql

Je vous cache pas qu’il faut aimer avoir des sueurs froides dans le dos pendant quelques longues minutes.

N’hésitez pas à  commenter ce post si vous avez été dans le même cas ! 

Vagrant : Le développement sur VM

Nous nous sommes tous heurter à la difficulté de mettre en place son environnement de développement, surtout dans le cadre de la mise en place d’une plateforme homogène entre plusieurs postes sans ajout de logiciels tiers et le plus proche possible de l’environnement de production.

– Exemple : une plateforme PHP, MySQL, Postgres dans le cadre de la migration d’un application PHP de MySQL vers Postgres.

Au fil de mes recherches sur le net et surement avec beaucoup de retard je suis tombé sur Vagrant. (http://vagrantup.com)

Vagrant a pour but de rendre plus simple l’utilisation de machine virtuelle dans le cadre de l’environnement de développement. On peut le dire le but est atteint pour la facilité de développement entre la machine hôte et la VM.

Le plus compliqué avec Vagrant va être de créer sa propre machine proche des habitudes de développement de l’équipe. Car Vagrant n’est pas là pour cela, il est juste là pour le montage de la machine et son utilisation au quotidien.

Heureusement, la communauté est très active sur le sujet et des outils sont là pour simplifier la création de sa propre Box avec quelques connaissances. Je pense notamment à Veewee (https://github.com/jedi4ever/veewee) qui est vraiment un outil à s’accaparer pour la création de sa propre Box, n’hésitez pas à dupliquer le projet et pusher vos modifications pour mettre à disposition vos Box, c’est d’ailleurs ce que je vais faire.

Mais, ne vous inquiétez pas, je vous prépare un petit tutoriel pour l’utilisation de Veewee avec une Box en français.

Et, important à savoir tout cela, c’est en Ruby.

Tip SQL: pourquoi Migrer de MySQL vers PostgreSQL?

Comment dire….
quand on a fini de jouer avec MySQL et que l’on veut passer en production, on cherche un moteur SQL de pros.
C’est un peu comme quand tu as cassé ta perceuse achetée chez Lidl (en promo !) et que tu bave devant la bosh dans le rayon chez casto…
Moi, mon vendeur, il m’a fait l’article et je viens tout juste de déballer le paquet:  PostgreSQL 9.
Avec ça, j’ai bien envie de percer des trous partout dans mes serveurs…
Bon, je m’emporte:

  1. MySQL c’est quand même bien!
  2. je fais toujours des courses chez lidl
  3. même facebook utilise la perceuse pas cher de chez Lidl ( MySQL)
C’est quoi les avantages de MySQL ?
  • d’abord le moteur est facilement configurable
  • l’outil de requêtage MysqlWorkBench est pas mal –  je dirais même , il s’approche de ce que fait Microsoft.
  • la doc en ligne est ok
  • la communauté est active
Mais quand on se penche un peu sur le bestiau, il y a des trucs qui me gênent : 
  1. la sauvegarde incrémentale InnoDB => pas disponible en mode gratos. Vas donc sauvegarder 400 Go d’un serveur sur-sollicité….
  2. le mode mono-thread sur les requêtes : Fais donc une auto-jointure sur une table de 25 millions d’enregistrements; ben, tu peux pas Mr patate !! en tout cas pas avec ton kimsuffi à  trois euros six sous. 
  3. pas de sharding (répartition de données)  sauf à  l’implémenter côté applicatif; Bref, réinventer ce foutu fil à couper le beurre que j’ai eu tant de mal à réinventer les 50 dernières fois.
  4. et puis j’ai aussi envie de changer les rideaux , donc je veux ma nouvelle perceuse !
Alors quelle perceuse doit-je prendre ? en général, le vendeur vous demande ce que vous allez en faire.
Moi, je veux :
  1. une Bdd gratos
  2. facile à  administrer 
  3. grosse volumétrie
  4. qui va se comporter en entrepôt de données ( peu de transactions, beaucoup de lectures, beaucoup de calculs)
  5. compatible avec ce qui a déjà  été implémenté côté applicatif
  6. le plus proche possible de l’ANSI SQL 92 ( utilisation des instructions JOIN )
Vous savez maintenant ce que j’ai choisi comme Base de données….

Et vous, vous pensez quoi des avantages de l’un et de l’autre ?

Tip MySQL: restaurer une table à partir d’une sauvegarde complète

En tant qu’administrateur de base de données MySQL, vous avez surement mis en place une  politique de sauvegarde des bases de données qui permet de sauvegarder toutes les bases installées.
Pour ma part j’utilise automysqlbackup qui permet de faire une rotation des bases de données.
Or ce logiciel sauvegarde l’ensemble des bases dans un seul et même fichier.

Imaginons maintenant qu’un développeur vienne vous dire qu’il a détruit les données d’une table et qu’il faudrait restaurer uniquement cette table à  partir des données de la veille.

La première chose que vous faites et de croiser les doigts en allant regarder dans le répertoire de sauvegarde s’il y a bien un dump des bases.
Ensuite, vous essayez de localiser la table en question avec votre éditeur préféré pour faire un copier coller dans l’IHM MySQL Workbench.
Problème:  le fichier de dump fait 20 Go!
même vi a du mal à  ouvrir le fichier…
Vous devez donc vous pencher sur les capacité du programme sed dont voici une bonne introduction.

L’explication suivante s’appuie sur un exemple précis:
 restaurer la table comments qui appartient à  la base de donées facebook.

Identification des patterns

Si on regarde de plus près le type de sauvegarde qu’automysqlbackup effectue ( grâce au programme mysqldump), on remarque plusieurs choses :

  • avant  chaque création de base de données on retrouve un commentaire.Exemple : 


— Current Database: `facebook`

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `facebook` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `facebook`;

  • pour chaque table , la table est détruite ( commande DROP)

DROP TABLE IF EXISTS `comment`;

  • la table est crée, verrouillée, les données sont insérées puis enfin la table est déverrouillée : 

UNLOCK TABLES;

On peut donc en déduire une stratégie :

  1. d’abord extraire la base de données cherchée et la sauvegarder dans un fichier
  2. ensuite extraire la table  cherchée et la sauvegarder dans un fichier

Extraction de la Base  de données 

On va donc utiliser le pattern « Current Database » pour  extraire uniquement la base de données facebook:

sed -n -e ‘/Current Database: .facebook/,/Current Database/p’ mon_fichier_de_dump.sql > facebook.sql

cette commande va extraire à  partir du fichier mon_fichier_de_dump.sql tout ce qui se trouve entre le texte « Current Database: `facebook` » et le prochain texte « Current Database: »  et sauvegarder dans le fichier facebook.sql.

l’option -n avec l’option /p  permet à  sed de n’écrire que les lignes comprises entre les deux patterns.
l’option -e

Extraction de la table

Une fois que la base de données est extraite, on peut maintenant extraire la table comment :

sed -n -e ‘/DROP TABLE.*`comment`/,/UNLOCK TABLES/p’ facebook.sql > comment.sql

Restauration de la table

 Il suffit ensuite de restaurer la table comments dans votre serveur de base de données avec la commande mysql:

mysql -h myserver.mynetwork.com  -u root -pmyPassword -D facebook< facebook.sql

Evidemment, vous pourrez modifier

Tip SQL/MDX: chercher le top X pour chaque groupe

Une des demandes les plus récurrentes, lorsque l’on fait des tableaux de bord c’est de retrouver les « 10 meilleurs clients par zone commerciale », ou encore les « 10 plus gros contributeurs facebook par page » .

Aujourd’hui je vous propose 3 façons de résoudre cette problématique en SQL, selon le système  utilisé.
Imaginons la table suivante:

Create   TABLE Contribs (id_page int, page varchar(50), fan varchar(50) , contribs INT);

INSERT INTO Contribs VALUES (2, ‘françois Hollande’, ‘toto’ , 65);
INSERT INTO Contribs VALUES (2,’françois Hollande’, ‘titi’ , 50);
INSERT INTO Contribs VALUES  (2,’françois Hollande’, ‘tutu’ , 30);
INSERT INTO Contribs VALUES (2,’françois Hollande’, ‘tata’ , 20);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘toto’ , 5);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘titi’ , 41);
INSERT INTO Contribs VALUES   (8,’Nicolas Sarkozy’, ‘tutu’ , 35);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘tata’ , 66);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘tete’ , 67);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘toto’ , 35);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘tata’ , 15);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘titi’ , 5);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘tutu’ , 3);
INSERT INTO Contribs VALUES  (24,’Marine Le Pen’, ‘zozo’ , 2);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘zizi’ , 69);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘zaza’ , 100);
INSERT INTO Contribs VALUES  (8,’Nicolas Sarkozy’, ‘zuzu’ , 1230);

Cette table correspond à la liste des fans ( champ fan) de pages page facebook avec la liste

Sql Server:

Bon là c’est tellement simple que s’en est bête… on va utiliser la fonction RANK, introduite avec la version 2005
la fonction RANK() , permet de calculer un auto-incrément lors d’une requête.
Par exemple:

select *   from  ( select page, fan, contribs, RANK() over ( Partition by page order by contribs DESC)  as Rank
from  Contribs ) as TEMP
where Rank <=10;

MySql:

Bon, là  on va utiliser une fonctionnalité que j’appellerais « procédurale »de Mysql et non ensembliste.
Je m’explique:
au sein même de la requête, on utilise l’incrémentation au fur et à  mesure que le moteur lit et ordonne les lignes qu’il trouve.
On doit d’abord ordonner les contributeurs  par page:
select page, fans, contribs from Contribs  order by page, contribs DESC;

On introduit l’incrémentation au travers d’une variable : @count.
Dès que le moteur de requête change de  page, il est nécessaire de remettre à  zéro la variable @count.
L’indication du changement de page se fera avec la variable : @oldPage

select  @oldPage := 0 , @count := 0;
SELECT
    @count := IF(id_page <=> @oldPage, @count+1, 0) as mycount,
    @oldPage := id_page,
    page,
    fan,
    contribs
FROM Contribs
ORDER BY id_page, contribs DESC;

Il suffit ensuite de filtrer :

select fan,page, contribs  from (SELECT
    @count := IF(id_page <=> @oldPage, @count+1, 0) as mycount,
    @oldPage := id_page,
    page,
    fan,
    contribs
FROM Contribs
ORDER BY id_page, contribs DESC)  t
where mycount<=10;

ANSI SQL:

On doit toujours revenir aux bases…
Sachant que le langage SQL ne sait pas compter la position dans le résultat, il faut trouver autre chose.
On peut essayer d’imaginer d’extraire pour chaque contribution ( contribs)  le sous-ensemble des contributions supérieures et inférieures.

Pour chaque contribs, on peut extraire le sous ensemble des contribs supérieures:

select C2.contribs, C1.contribs
from  Contribs as C1
join Contribs as C2 on C1.contribs >= C2.contribs

On veut maintenant n’avoir que les contribs qui n’ont que trois contribs qui lui sont supérieurs. On va compter pour chaque sous ensemble le nombre distinct de contribs  :

select min(C1.contribs)
from  Contribs as C1
join Contribs as C2 on C1.contribs >= C2.contribs
group by C2.contribs
having count(DISTINCT C1.contribs) <=3;

Avec cette requête, on récupère ainsi les trois plus hautes contribs c’est à  dire 69,100 et 1230

Enfin, on veut faire le regroupement par page :

select min(C1.contribs) , C1.page
from  Contribs as C1
join Contribs as C2 on C1.contribs >= C2.contribs and C1.id_page = C2.id_page
group by C2.contribs, C1.page
having count(DISTINCT C1.contribs) <=3;

Il ne reste plus qu’à  injecter les noms des contributeurs :

select c.*  from  Contribs c
JOIN
(select min(C1.contribs) as contribs, C1.id_page
from  Contribs as C1
join Contribs as C2 on C1.contribs >= C2.contribs and C1.id_page = C2.id_page
group by C2.contribs, C1.id_page
having count(DISTINCT C1.contribs) <=3) t
on t.id_page = c.id_page and c.contribs = t.contribs;

Et voilà:  on récupère les trois meilleurs contributeurs par page!!
C’est d’ailleurs la requête la plus élégante et celle qui fonctionnera partout.

MDX:

Alors là, le système est très simple  avec l’instruction TopCount puisqu’il permet de faire le filtre pour chaque sous-ensemble.
Par exemple, on crée un « set » des clients à qui on a le plus vendu sur 2012, puis on peut l’utiliser  en dans une requête MDX:

with set [Top10cust] as ‘TopCount({[Client].[ClientName].Members}, 10.0, ([Time].[2012], [Measures].[sales]))’
member [Measures].[Ventes] as ‘[Measures].[sales]’
select {[Measures].[Ventes]} on COLUMNS,
 {[Top10cust]}  ON ROWS
from [Commercial]

Où se trouve le binaire des librairies MySQL pour Snow Leopard ?

Depuis plusieurs semaines, je cherchais le moyen de compiler proprement la librairie MySQL pour RoR.
Cet article m’a permis de découvrir qu’Apple met à disposition les sources librairies des outils intégrés dans Snow Leopard et même certains binaires comme c’est de le cas pour MySQL.