Comprendre son fichier de configuration – 2ème partie

22 décembre 2011 par stephane

Pour notre deuxième volet sur les points les plus importants à regarder lors de la configuration d’un serveur MySQL, nous allons nous occuper du cache de requêtes, de la réplication et de la journalisation. En route !

Cache de requêtes

Le cache de requêtes est une excellente idée des années 90 pour accélérer les performances des SELECT. Malheureusement ce cache n’est absolument pas scalable en terme de connexions simultanées, ce qui signifie que dans 99,9% des cas, les performances seront meilleures quand le cache est désactivée.

Par conséquent, le tuning du cache de requêtes est très simple, il vous suffit d’indiquer dans votre fichier de configuration :
query_cache = 0

Pour les plus curieux, sachez que la désactivation du cache de requêtes n’empêche pas le serveur d’utiliser la mutex d’accès au cache de requêtes : même désactivé, le cache de requêtes peut diminuer vos performances !! Dans Percona Server et MySQL 5.5, query_cache = 0 désactive également cette mutex, mais pour les versions antérieures, il vous faudra recompiler le serveur sans support du cache de requêtes pour vous débarasser de cette mutex.

Réplication

La réplication est tellement utile qu’il est difficile de s’en passer : scale-out, haute disponibilité, aide aux backups, slaves dédiés pour les statistiques… la liste des applications est longue !

Sur toutes les instances impliquées dans un schéma de réplication, il faut absolument que chaque instance ait un server-id unique. Si vous utilisez un système de déploiement de configuration, pensez à faire en sorte que le système de déploiement génère lui-même un id qui soit garanti comme étant unique.

Sur le master, il faut également activer le paramètre log_bin pour mettre en route les logs binaires nécessaires à la réplication (et qui sont aussi utiles pour le Point In Time Recovery), par exemple :
log_bin = /data/mysql-bin

Le serveur s’occupe tout seul de numéroter les fichiers et d’en faire la rotation. A ce propos, il peut être pratique de forcer la rotation quand le fichier atteint une taille plus faible que celle par défaut (1 Go) :
max_binlog_size = 100000000
et de purger automatiquement les fichiers au bout d’un certain temps :
expire_logs_days = 7

Est également recommandé l’option sync_binlog = 1 qui assure le flush du log binaire à chaque commit (meilleure assurance contre les pertes de données en cas de crash).

Sur les slaves, il n’est en général pas nécessaire d’activer les logs binaires, sauf si le slave est lui-même un master. Dans ce cas, il faut penser à ajouter le paramètre log_slave_updates, qui indique au serveur d’écrire dans les logs binaires les écritures reçues par la réplication.

La seule autre option vraiment intéressante est read_only = 1, qui empêche tous les utilisateurs n’ayant pas le droit SUPER d’écrire sur le serveur. Cette option peut éviter des gros problèmes de désynchronisations si accidentellement une application tente d’écrire directement sur un slave au lieu d’écrire sur le master.

Ne pas oublier également de configurer un compte pour la réplication avec les droits REPLICATION SLAVE et REPLICATION CLIENT.

Journalisation

Avoir des traces de ce qui se passe dans le serveur est évidemment une très bonne idée, aussi bien pour résoudre les problèmes que pour prendre des mesures préventives. Il existe avec MySQL 3 types de logs : general log pour l’ensemble des connexions et requêtes, slow query log pour les requêtes lentes et error log pour les erreurs et warnings.

Il est préférable d’éviter d’activer le general log en production, hormis pour des périodes courtes pour investiguer sur un problème précis, car les performances s’en ressentent lourdement (et que vous risquez de vous retrouver très vite à court d’espace disque). Cela se traduit par :
general_log = OFF

Le slow query log est particulièrement utile pour repérer les requêtes les plus coûteuses de votre application. Toutes les requêtes au-delà d’un seuil que vous définissez sont écrites et peuvent servir à générer un rapport des requêtes les plus lentes. Une configuration typique est la suivante :
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

Enfin le log d’erreur devrait être activé, soit par le paramètre log_error soit en écrivant dans syslog, avec le paramètre syslog que vous trouverez généralement dans la section [mysqld_safe]. Sachez que les 2 solutions ont des avantages et des inconvénients, l’inconvénient étant toujours que vous risquez de perdre la trace d’erreurs à cause de la rotation de votre fichier de log. Vous trouverez plus d’informations dans ce billet.

Nous verrons dans le dernier article de cet série les paramètres les plus importants pour InnoDB. D’ici là, bonnes fêtes de fin d’année !

Mots-clefs : , ,

3 commentaires sur “Comprendre son fichier de configuration – 2ème partie”

  1. Jean Moniatte dit :

    Super, merci pour cette série, je la trouve très instructive.

    Bonne fêtes !

  2. Ced dit :

    Bonjour et merci pour toutes ces infos très utiles.
    J’aurai une question sur le cache : je gère une application back office avec seulement une dizaine de personnes connectés et des requêtes assez lourde au niveau jointure. Je pensais qu’utiliser le cache serait une bonne idée et un bon gain de performance, mais après lecture de cet article, j’ai un doute que ce cache ne provoque seulement une charge supplémentaire sur le serveur…

    Bonne journée !

  3. stephane dit :

    Tous les cas de figure sont possibles, bien sûr, mais à partir du moment où l’une de ces 2 conditions est remplies, il y a assez peu de chances que le cache de requêtes soit utile :
    - la base n’est pas en read-only
    - le nombre de connexions simultanées à la base et actives est assez élevé (disons Threads_running > 10)

Laisser une réponse