Des procedures stokées pour connaitre et générer des stats sur l'etat d'une base de données, l'etat d'une table, etc.

Généralement sur les serveur de production ou sont installé les serveur MySQL, on ne trouve pas des outils de qui permettent de gérer facilement et rapidement les base de donnée via une interface graphique (phpMyAdmin & like) et qui sont plus pratique que de le faire par le Terminal (client MySQL classique).

Cependant les DB admins, les IT ou les developpeurs eux même, sont obligés parfois de passer par un Terminal et de tapez des longues requetes via le client MySQL pour avoir des informations sur l'etat du serveur, l'etat d'une base de données, l'etat d'une table, etc.!

Pour cela, on compte faire dans ce qui suit quelques procedures qui nous seront utiles, rapides et pratiques dans ces cas :

• Problème : C'est d'avoir un idée sur l'etat d'une base de données et l'etat d'une table en créeant des fonctions qui nous permet de recupéré les infos suivantes :
  

• Table 1 : Avoir un aperçu sur les bases qui existent sur le serveur MySQL, l'espace d'occupation en MB, en Pourcentage et un Histograme qui une représentation graphique de l'ensemble de données qui met en évidence l'importance de l'espace occupé par chaque BD :
  

mysql> CALL dbsize();
+--------------------+-----------+----------+------------------------------------+
| base               | Size (MB) | Pourcent | Graph                              |
+--------------------+-----------+----------+------------------------------------+
| test               |    0.0010 |     0.01 | *                                  |
| olap               |    0.0011 |     0.01 | *                                  |
| webauth            |    0.0020 |     0.01 | *                                  |
| cdcol              |    0.0031 |     0.02 | *                                  |
| information_schema |    0.0040 |     0.03 | *                                  |
| phpmyadmin         |    0.0070 |     0.04 | *                                  |
| faq_edt            |    0.0447 |     0.29 | *                                  |
| projectstatus      |    0.0652 |     0.42 | *                                  |
| idma_101           |    0.0880 |     0.56 | *                                  |
| faq_sql            |    0.1003 |     0.64 | *                                  |
| mos                |    0.1488 |     0.95 | *                                  |
| idma_102_old       |    0.1772 |     1.13 | *                                  |
| wordpress          |    0.1895 |     1.21 | *                                  |
| idma_102           |    0.2640 |     1.69 | **                                 |
| mysql              |    1.6560 |    10.59 | *****                              |
| olapx0d            |    3.3677 |    21.54 | ********                           |
| tn_blog_feed       |    9.5201 |    60.88 | *********************              |
| TOTAL :            |   15.6374 |   100.00 | ********************************** |
+--------------------+-----------+----------+------------------------------------+
18 rows in set (2.81 sec)

Query OK, 0 rows affected (3.80 sec)

La suite sur : Des procedures stokées pour connaitre et générer des stats sur l'etat d'une base de données, l'etat d'une table, etc.

dolfine.blogspot.com

GNU Screen, Simplifie encore la vie :

Screen is a full-screen window manager that multiplexes a physical terminal between several processes (typically interactive shells)

Dans la société ou je travaille (encore une fois :), nous utilisons plusieurs serveurs MySQL tournant sous debian linux, ou chaque jours nous somme obligé de se connecter a plusieurs serveur simultanément. Et l'erreur commise par la majorité de mes collègues, est l'ouverture de plusieurs Shell en même temps! (en utilisant SecureCRT, Putty, ...), jusqu’a la saturation de leurs pauvres machines (un shell est toujours gourmand coté ressource :).

Cependant, la solution est dans l'utilisation d'un multiplixeur des shell comme GNU Screen [http://www.gnu.org/software/screen/] .

Et voici quelques commandes fort utiles :

• Pour lister les sessions : screen - ls
• Pour créer une session dont le nom session_name : screen -D -R -S session_name
• Pour créer une nouvelle fenetre : Ctrl-A + c
• Pour quitter la fenêtre en cours : Ctrl-A + k
• Pour détacher une session screen : Ctrl-A + d
• Pour naviguer entre les fenêtres 1 : Ctrl-A + "
• Pour naviguer entre les fenêtres 2 : Ctrl-A + [0-9-] (Switch to window number 0 - 9, or to the blank window.)
• Et pour en savoir plus tapez "man screen" :p

Essayez, c'est magique :)

MySQL le fait aussi !

Dans la société où je travaille, je vois de plus en plus des erreurs et des mauvaises habitudes chez plusieurs développeurs (parfois par paresse [http://fr.wikipedia.org/wiki/Paresse_intellectuelle] et parfois par ignorance) :

• Pas mal de fois, je vois un développeur qui se connecte à deux sessions sur le même serveur, une pour MySQL et l'autre pour les commandes Shell, pour autant MySQL fait les deux!
• Pas mal de fois, je vois un développeur qui lance une grosse requête à MySQL, et puis il reste 10 min à copier le résultat affiché.
• Pas mal de fois, je vois un développeur qui tape 10 fois successive la touche Entrée dans le shell MySQL, rien que pour nettoyer la l'écran.
• Pas mal de fois, je vois un développeur qui tape Ctrl-C et arrête le pauvre programme, pour ne pas exécuter la requête qu'il vient de saisir.
• ...

Pourtant MySQL le dit a chaque fois qu'on se connecte :

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is XYZ to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


Tapez 'help' en ligne de commande, et MySQL va afficher ce qu'il sait faire :

• ego (\G) : Send command to MySQL server, display result vertically.
• Si vous voulez que MySQL affiche le résultat verticalement, faites-le en entrant \G (c'est plus lisible!)

mysql> SELECT * FROM db.table\G


• system (\!) : Execute a system shell command.
• Si vous voulez que MySQL exécute une commande système (y compris le clear screen), faites-le en commentant par \!

mysql> system ls -la
mysql> \! clear
mysql> \! scp ~/export.dmp user@host:/home/user/


• clear (\c) Clear command.
• Si vous décidez d'annuler une commande que vous etes en train de taper, faites-le en entrant \c : [1]

mysql> SELECT
-> USER()
-> \c
mysql>


• tee (\T) : Append everything into given outfile.
• Pour enregistrer les requêtes et leur résultat, utilisez la commande tee. Toutes les données affichées a l'écran seront ajoutées a un fichier donne. Cela peut être très pratique pour déboguer. Vous pouvez activer cette fonctionnalité en ligne de commande, avec l'option --tee, ou interactivement avec la commande tee. Le fichier tee peut être désactive interactivement avec la commande notée. Exécuter tee a nouveau re-active le log. Sans paramètre, le fichier précèdent sera utilise. Notez que tee envoie les requêtes dans le fichier après chaque commande, juste avant que MySQL ne l'affiche. [1]

mysql> tee ~/mySQLlog
mysql> notee


• pager (\P) : Set PAGER [to_pager]. Print the query results via PAGER.
• La lecture et la recherche dans les résultats de requêtes en mode interactif est possible en utilisant les programmes Unix less, more, ou tout autre programme similaire, avec l'option --pager. Si vous ne spécifier par de valeur d'option, MySQL regarde la valeur de la variable d'environnement PAGER, et utilise ce pager. Vous pouvez aussi l'activer interactivement avec la commande pager et la désactiver avec nopager. La commande prend un argument optionnel : s'il est fournit, le programme de pager indique sera utilise. Sinon, le pager sera celui indique en ligne de commande, ou stdout si aucun pager n'était indique. [1]

• ...
  

---

[1] : le manuel de référence du système de base de données MySQL. MySQL Server Books Online .

ALICE AU CORE DE MySQL-5.1 :PART2: L'Optimiseur | The Optimizer

Dans la documentation officielle de MySQL on trouve dans le chapitre "7.2.4. Comment MySQL optimise les clauses WHERE" que "Cette section est incomplète. MySQL fait de très nombreuses optimisations, et nous n'avons pas eu le temps de toutes les documenter.". Alors on va la reprendre les posts qui viennent, dans l'objectif de mettre un point sur les inconvénients d'une requête mal écrite ou très complexes pour MySQL. Par ce que, même si le temps de l'évaluation de la condition "WHERE" est relativement négligeable, il semble qu'il est très utile de le supprimer!

L'optimisation des requêtes SELECT se fait dans le fichier sql_select.cc et certaines des optimisations effectuées par MYSQL sont :

• Si une dans une requête, un champ "field = constante", alors elle change toute les références de "field" à "constante" : "a=b AND b=c AND c=1" devient "b=1 AND a=1 AND c=1"
• Suppression des conditions constantes ou inutiles : ( 1 = 1, b = b, ..).
• Suppression des conditions impossibles : "item IS NULL" alors que "item" ne s'annule jamais.
• Retourne "Impossible WHERE" ou MySQL détecte rapidement les commandes SELECT impossibles (WHERE 1 = 2) et ne retourne aucune ligne.
• Suppression des parentheses inutiles: ((a AND b) AND c OR (((a AND b) AND (c AND d)))).
• Retourne le COUNT(*) sans "WHERE" d'une table simple directement depuis les informations de la table.
• etc..
  

Mais le plus important à savoir, reste comment MySQL optimise ce genre de requêtes afin de pouvoir en déduire les meilleures façons d'écrire nos requêtes et plus généralement la meilleure façon de concevoir ses bases de données.

Une balade dans le fichier sql_select.cc fera l'affaire :

Une définition étroite de l'OPTIMISEUR : est l'ensemble de routines qui décident quel methode d'exécution le SGBD devrait prendre pour les requetes. MySQL change ces routines fréquemment, ainsi vous devriez comparer ce qui est dit ici avec ce qu'est dans le code source. Pour faciliter les choses, cette description inclut des notes se rapportant à la routine appropriée, par exemple "/sql/select_cc, optimize_cond()". Quand une reqeute est changée en une autre reqeute qui fournit le même résultat, c'est la TRANSFORMATION. La plupart des transformations sont moins évidentes, mais quelques unes peuvent rendre l'exécution plus rapide.

Par exemple, MySQL peut changer :

 SELECT ... WHERE 5 = a

en

 SELECT ...WHERE a = 5

Voici le diagramme montrant la structure de code du handle_select() dans /sql/sql_select.cc, le code du serveur qui manipule les requetes :

   handle_select()
  mysql_select()
    JOIN::prepare()
      setup_fields()
 
    //{{{ L'optimisation commence ici ... 
    JOIN::optimize()       
      optimize_cond()
      opt_sum_query()
      make_join_statistics()
        get_quick_record_count()
        choose_plan()
          /* Trouve la meilleur fcon d'acces aux tables spécifié par l'utilisateur  */
          optimize_straight_join()
            best_access_path()
          /* Trouvez les plans les plus optimaux parmi le sous-ensemble de tous les 
               combinaisons possibles des requetes                                    */
          greedy_search()
            best_extension_by_limited_search()
              best_access_path()
          /* Exécutez une recherche approfondie du plan optimal */
          find_best()
      make_join_select()
    // }}} L'optimisation s'arrete ici

    JOIN::exec()

• Bien que le mot "JOIN" apparaisse, ses routines d'optimiseur sont les memes pour pour toutes les requetes.
• L'identation dans l'expositions du diagramme la methodes d'appel. Ainsi vous pouvez voir que handle_select() appelle mysql_select() qui appelle JOIN::prepare() qui appelle le setup_fields(), et ainsi de suite.
• La première partie de mysql_select() est JOIN::prepare() pour l'analyse du contexte, l'identification des metadata, et la transformations de quelques sous requetes.
• L'optimiseur est JOIN::optimize() et toutes les routines subalternes.
• Quand l'optimiseur finit son travail, JOIN::exec() execute ce que JOIN::optimize() vient de décidé .
• Les routines optimize_cond() et opt_sum_query() font les transformations.
  
• La routine make_join_statistics() remonte toute les informations qu'elle trouve au sujet des index qui pourraient être utiles pour accéder aux tables de la requete.

ALICE AU CORE DE MySQL

Avez vous jamais mis les pieds dans le code du MySQL ?

Moi je trouve que c'est très amusent même. Premièrement, par curiosité parce que en tant que développeur je me demandent toujours a quoi ressemble le core d'un SGBD pareil; deuxièmement et du point de vue technique, la simple lecture du code n'est pas inutile ! Pas de tout!

En fait, MySQL est programmé en C++, qui est un langage de programmation super puissant et trop rapide du coté exécution. Et la Rapidité, Puissance, Stabilité, sont les choses qui hérite MySQL du sont ancêtre. En plus, le C++ est l'un des langages de programmation les plus populaires dans l'industrie informatique et par suite le plus connu chez les développeurs du monde entier. La chose qui rend une balade dans le core de MySQL, loin d'être réservée a une bande d'élites!.

En lisant la documentation des dev (disponible ici[en]) ou le code source, on peut profiter de deux choses :

1. On peut chopper pas mal de trucs signalés par les développeurs de MySQL dans les fichiers sources /* en tant que notes dans les commentaires */ et qui n'existent pas dans le manuel de d'utilisateur final.


2. En lisant les algorithmes utilisés par MySQL, on pourra bien comprendre comment MySQL gère et répond à nos requêtes, et par suite comment les améliorer, les meilleus choses a faire, les raccourcis a prendre, etc...

Le code source de MySQL est disponible pour ceux souhaitant et c'est par ici : http://www.mysql.com

En passant par la

Et en arrivant jusqu'a la

La version "Tarball (in 5.1 both Unix & Windows, tar.gz)" contient le répertoire mysql-5.0.24a par ses 91,4 Mo, 5093 fichiers et 304 répertoires.

The wrong way to upgrade MySQL

Friday, May 19th, 2006

Expect a longer post in the near future on upgrade procedures. For now enjoy this quote from a gentoo user illustrating the worst way to upgrade.

linolium: is there a way to wipe every single table and start over from scratch?
linolium: ( the upgrade from mysql 4 to 5 didn’t go as smoothly as planned
me: did you read the upgrade notes?
linolium: no, I just hoped that portage would be kind enough to do those things for me

http://ebergen.net/wordpress/category/geek/mysql/

Ca me rappel quelqu'un..

MySQL Consulting

Aujourd'hui je me suis trouvé face a face avec la base de donneés du plus grand client X de notre société Y (plus de 60 Gega de conneries de tout genre). Ou on ma demandé de livrer une étude statistique qui touche toute la base. Pas seulement ca, mais en cadeau j'ai eu la spec tech du notre chere consultant certifié en pas mal de choses (je suppose), ou il me propose ses best practices en matiere de bases de données et la maitrise de mySql, l'une d'elle consiste de faire des jointures entre des tables de 4 +/- millions de lignes, une autre calcule le cumul des champs par periode et par type d'une table de 70 +/- millions d'enregistrements 4 millions de fois dans la même boucle et a mettre le resultat en une seule table...

... et merci de faire ca vite!!

mais ca va pas la tête ou quoi ??