MySQL Innodb notes de configuration et d’optimisation

Pour compléter une note précédente sur Innodb et UTF8 voici quelques notes de configuration de MySQL avec le moteur de base de donnée Innodb sous forme de checklist pour toute solution d’hébergement ou architecture MySQL est dédier eZ Publish, Dotclear 2 et autre CMS.

Utilisez le moteur Innodb

Forcer le moteur par defaut de stokage en InnoDB pour la création de toute nouvelle table sans spécification du moteur souhaité.

<span style="color: #808080; font-style: italic;">#</span> <span style="color: #808080; font-style: italic;"># Moteur par defaut de stokage, ici InnoDB parce que  Ez publish</span> <span style="color: #808080; font-style: italic;">#</span> default-storage-engine  = InnoDB

UTF8

Prévoir dès le départ l’encodage des base en UFT8

<span style="color: #808080; font-style: italic;">#</span> <span style="color: #808080; font-style: italic;"># Encodage UTF8</span> <span style="color: #808080; font-style: italic;">#</span> default-character-<span style="color: #007800;">set=</span>utf8 default-<span style="color: #007800;">collation=</span>utf8 <span style="color: #007800;">collation_server=</span>utf8_general_ci <span style="color: #007800;">character_set_server=</span>utf8 skip-character-set-client-handshake

Configurer l’écoute sur toute les interfaces réseaux

Ouvrir à un utilisation via le réseau sans limitation d’interface ou d’adresse.

<span style="color: #808080; font-style: italic;">#</span> <span style="color: #808080; font-style: italic;"># Instead of skip-networking you can listen only on</span> <span style="color: #808080; font-style: italic;"># localhost which is more compatible and is not less secure.</span> bind-address            = <span style="color: #808080; font-style: italic;">#skip-networking</span>

Optimisation tablespace

Prévoir le lancement du moteur innodb avec comme configuration un fichier tablespace par table. Cela va créé un fichier tablespace pour chaque table innodb et vous évitera d’avoir dans quelque mois un unique fichier tablespace de plusieurs giga octet impossible à manipuler.

<span style="color: #808080; font-style: italic;">#skip-innodb</span> innodb_file_per_table

Optimisation Log

Bloquer les log binaire qui consomme beaucoup d’espace disque et n’apporte pas de sécurité sur une plate-forme qui n’a pas de réplication Master / Slave MySQL.

<span style="color: #808080; font-style: italic;">#log_bin                        = /var/log/mysql/mysql-bin.log</span>

Optimisation innodb_buffer_pool_size

innodb_buffer_pool_size définie la taille de buffer mémoire que InnoDB utilise pour mettre en cache les données et les index de tables. Plus cette valeur est grand, et moins vous ferez d’accès disques. Sur un serveur dédiés, vous pouvez monter cette valeur jusqu’à 80% de la mémoire physique de la machine. Ne lui donnez pas une valeur trop grande, car cela peut engendrer l’utilisation de mémoire sur le disque par votre serveur (swap).

10 réflexions sur “ MySQL Innodb notes de configuration et d’optimisation ”

  1. Que se passe t’il si on a déjà une base innodb avec des paramètres par défaut et qu’on active le mode « innodb_file_per_table » ?
    Ca convertit l’existant ? Ca ne créé des fichiers séparés que pour les nouvelles tables ?

  2. Nan ratatouille

    La directive innodb_file_per_table est à utilisé dès le départ si non tu te retrouve avec un fichier /var/lib/mysql/ibdata1 énorme. Regarde le tiens.

    J’ai eu le cas sur une grosse base de 11Go avec un fichier ibdata1 de 9Go. J’ai fair la manipulation suivante : stopper l’activité DB, faire un dump avec mysqldump, ajouter innodb_file_per_table à ta config, relancer Mysql, injecter ta sauvegarde dump. Temps total une matiné de boulot.

    A noté que les tablespace ne diminue jamais. Si ta table monte à 10Go puis retombe a 4Go suite à des manipulation le fichier tablespace va rester coller à 10Go.

    Dans ce cas, pour gagner de la place tu dois recréé ta table (sauvegarde et injection). C’est d’ailleurs ce que fait la commande OPTIMIZE TABLE sur un moteur innodb. Elle recréé la table.

    Bon courage

  3. ok merci pour ces infos.

    Ca va ma base est jeune et toute petite, et c’est d’ailleurs pour ça que je préfère y réfléchir maintenant (idem utf8)

    D’ailleurs PHP qui retourne de l’iso-8859-1 + base en utf-8 c’est pas risqué ? il vaut mieux tout passer en utf-8 ?

  4. Pas de soucis ratatouille.
    T’a base est encodé en UTF8 et contient del’iso-8859-1. Donc le retour d’un select de donne du iso-8859-1. Par contre regarde un fichier dump de ta base….:-)

  5. Vaut mieux tout passer en UTF-8 (base, fichiers,…), parce que sinon c’est trop de sources de problèmes.

  6. Bon avec pas mal de retard je viens de tester le passage en innodb/utf8

    côté surprises :
    – innodb : après dump / drop database / réimport : les gros fichiers sont restés 🙁 je vais tenter une suppression manuelle mais ça n’est pas rassurant
    – la conversion utf-8 était également en bonne voie (visiblement le changement latin1->utf8 dans phpmyadmin convertit bien les données, mysql retourne bien de l’utf8 à PHP, les balises header&co fonctionnent bien dans php/html mais 2 gros problèmes subsistent :

    • le texte HTML intégré dans les fichiers PHP (templates) n’est pas retourné en utf-8 par php. Donc un fichier .php créé sous windows avec un echo « activité » ; verra son accent pété… je n’ai rien trouvé comme solution dans l’immédiat à part forcer la conversion du format standard (windows-1252 vers utf-8, fichier par fichier). D’autres langages que j’ai fréquenté sont capables de lire un fichier « code » en windows-1252 et de retourner de l’utf-8…. pas php ? Je sais que ça peut sembler étrange, mais d’expérience c’est souvent plus le bordel d’avoir des fichiers sources en utf-8 que l’inverse (mais je veux bien des retours d’autres personnes là dessus).
    • en PHP5 le support de l’utf8 est foireux, toutes les fonctions natives de manipulations de chaines sont visiblement déconseillées… et du coup il faut passer par des bidouilles pour s’en sortir (extension, conversion…). Ca me bloque un peu avant même d’aller plus loin. Je manipule pas mal de chaines (split, longueur, comparaison…) et si tout risque de déconner ça ne me branche pas trop.

    Bref pour l’instant je n’ai pas poussé mon test plus loin (je vais chercher plus d’infos/retours d’expérience) et vais déjà m’arrêter à innoDB, probablement en attendant PHP6 🙁

  7. ratatouille dump tes bases, toute, stop mysql, active la directive innodb_file_per_table efface le fichier /var/lib/mysql/ibdata1, relance mysql, recharge tes base avec ton dump. Oui c’est long comme opération et pas sans contrainte de production.

    Pour le php, je suis pas la référence sur le sujet. Mais sous debian regarde le paquet utf8-migration-tool qui je crois apporte des outils de conversion de fichier. Regarde aussi si les locale sont correctement configurées pour tout les charset que tu souhaite utiliser.

  8. Pour innodb_file_per_table, n’est-ce pas plus contre-productif qu’autre chose d’un point de vue accès disque & perf ? Il me semble que tu as mis un lien à ce sujet y a pas longtemps sur twitter.

    En même temps, c’est vrai que j’aime pas trop voir grandir mon fichier ibdata.

    C’est la grosse faiblesse d’InnoDB à mon sens (sans parler de la tolérance à la panne).

    Un innodb_file_per_database serait déjà un meilleur compromis je pense.

  9. Exactement Nicolas, innodb_file_per_table est contre productif quand il y à beaucoup d’inserts/deletes/updates selon ce billet de Umang Gopani.

    En toute franchise je suis sur le cul :p

  10. @ratatouille : je n’ai jamais eu de problème entre MySQL et PHP, tout en UTF-8.
    Dans ton code PHP, fais :
    echo ini_get('default_charset');echo mb_regex_encoding();echo mb_internal_encoding();
    Si tout est bien configuré, les trois doivent te renvoyer UTF-8.
    À part ça, il faut absolument que tes sources soient encodées en UTF-8, sinon tu auras des problèmes…
    Tes pages html doivent aussi contenir : <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> ou l’équivalent dans l’en-tête envoyée par le serveur, mais je préfère que ça soit dans la page (tu peux faire les deux : dans la page et dans l’en-tête).

    Si tu as encore des problèmes, je peux surement t’aider. Si tu utilises eclipse, cvs, svn,… méfis-toi qu’il ne te mettent pas du windows-1252 de partout : il y a pleins de paramètres planqués de tous les côtés.

    Avant, où je bossais, on me faisait coder en java et les gros c*** me faisaient ch*** à mettre leur base en latin1, c’était l’enfer, yavait toujours pleins de problèmes de partout. J’avais même dû internationaliser un site et on avais dû laisser tomber pleins de langues à cause de la base de données foireuse. À chaque nouveau projet, je leur disais de me faire une base en utf-8, mais à chaque fois ils recommençaient à faire n’importe quoi… c’est bien beau de se dire dba oracle, mais quand on ne comprend même pas les histoires d’encoding, c’est pas la peine de bosser.

    Maintenant je suis libre (démissionné 😛 ) : c’est du bonheur en PHP5.3 et MySQL, tout en utf-8 🙂

Laisser un commentaire