10.1 Optimisation des valeurs du serveur

Vous pouvez accéder aux tailles par défaut des buffers de mysqld avec la commande suivante :@: 

shell> mysqld --help

Cette commande produit la liste de toutes les options et les variables de mysqld. La réponse fournit aussi les valeurs par défaut, et devrait ressembler à ceci :@: 

Possible variables for option --set-variable (-O) are:@:
back_log current value:@: 5
connect_timeout current value:@: 5
delayed_insert_timeout current value:@: 300
delayed_insert_limit current value:@: 100
delayed_queue_size current value:@: 1000
flush_time current value:@: 0
join_buffer_size current value:@: 131072
key_buffer_size current value:@: 1048540
long_query_time current value:@: 10
max_allowed_packet current value:@: 1048576
max_connections current value:@: 100
max_connect_errors current value:@: 10
max_delayed_threads current value:@: 20
max_heap_table_size current value:@: 16777216
max_join_size current value:@: 4294967295
max_sort_length current value:@: 1024
max_tmp_tables current value:@: 32
net_buffer_length current value:@: 16384
record_buffer current value:@: 131072
sort_buffer current value:@: 2097116
table_cache current value:@: 64
tmp_table_size current value:@: 1048576
thread_stack current value:@: 131072
wait_timeout current value:@: 28800

Si vous avez un serveur mysqld en fonctionnement, vous pouvez voir les valeurs réellement utilisées en exécutant la commande suivante :@: 

shell> mysqladmin variables

Toutes les options dont détaillées ci-dessous. Les tailles des buffers et des piles, les longueurs sont toutes données en octets. Vous pouvez ajouter le suffixe ``K'' ou ``M'' pour indiquer kilo-octets or mégaoctets. Par exemple, 16M représente 16 mégaoctets. La casse du suffixe n'a pas d'importance, et; 16M et 16m sont équivalents.

  • back_log Le nombre maximum de tentative de connexions simultanées au serveur MySQL . Cette option entre en jeu lors que le thread principal MySQL reçoit un très GRAND nombre de connexion dans un délai très bref. Le thread principal requiert un peu de temps (même si c'est très peu) pour vérifier la connexion, et lancer un nouveau thread. L'option back_log indique le nombre de connexion qui sont mise en attente, avant que MySQL momentanément cesse de réponse à de nouvelles requêtes. Augmentez la valeur pour accepter un grand nombre de connexions dans un délais très court. En d'autres termes, cette valeur correspond à la taille de la file d'attente de connexions TCP/IP. Votre système d'exploitation a sa propre limite. Sous Unix, le manuel donne plus de détails avec l'entrée listen(2). Vérifier la documentation de votre OS pour connaître la valeur de cette variable. Affecter à back_log une valeur plus grande que le maximum accepté par le système sera inefficace.
  • connect_timeout Le nombre de secondes d'attente d'un paquet de connexion avant que mysqld ne réponde une erreur Bad handshake.
  • delayed_insert_timeout Le temps d'attente d'un thread INSERT DELAYED :@: si les commandes INSERT sont trop longues, le thread INSERT DELAYED se finira.
  • delayed_insert_limit Après avoir inséré delayed_insert_limit lignes, le handler INSERT DELAYED vérifiera qu'il n'y a aucune commande SELECT en attente. Si c'est le cas, le handler INSERT DELAYED sera exécuté avant de continuer.
  • delayed_queue_size La taille de la queue d'attente qui gère les INSERT DELAYED. Si la queue est pleine, tout client qui émet une commande INSERT DELAYED devra attendre qu'une place se libère dans la queue.
  • flush_time Si cette option a une valeur, alors, toutes les flush_time secondes, toutes les tables seront fermées.(pour libérer des ressources, et synchroniser les tables sur le disque).
  • join_buffer La taille des buffers utilisés lors des regroupements sans index (les plus gourmands en place). Le buffer est alloué à chaque regroupement entre deux tables. Augmentez cette valeur pour obtenir un regroupement plus rapide lorsque l'ajout d'index n'est pas possible (ce qui est la méthode la plus efficace pour accélérer une requête).
  • key_buffer Les blocs d'index sont bufferisés et partagé par tous les threads. key_buffer est la taille des buffers de bloc d'index. Cela permettra d'accélerer le traitement des commandes DELETE ou INSERT sur une table avec de nombreux index. Pour accélerer encore plus la commande, reportez vous à la section LOCK TABLES
  • long_query_time Si une requête requiert plus que time secondes, le compteur de slow_queries sera incrémentés.
  • max_allowed_packet La taille maximale d'un paquet. Ce buffer est initialisé à la longueur net_buffer_length octets, mais il peut croître jusqu'à une taille de max_allowed_packet octets, au besoin. Cette valeur par défaut est trop petite pour gérer de gros paquets (et probablement ceux-ci sont érronnés). Il vaut mieux augmenter la taille cette colonne pour pouvoir gérer des objets de BLOB :@: il faudrait que la valeur de cette option soit aussi grande de le plus grand BLOB que vous voulez utiliser.
  • max_connections Le nombre maximal de connexion simutanée de client. En augmentant cette valeur, vous augmenter le nombre de pointeur de fichier que requiert mysqld . Voir ci-après pour plus de détails sur les pointeurs de fichiers
  • max_connect_errors S'il y a plus que max_connect_errors connexions interrompues de la part d'un même hote, toutes les tentatives ultérieures de connexions seront bloquées. Vous pouvez aussi débloquer les hôtes avec la commande FLUSH HOSTS.
  • max_delayed_threads Force le serveur à limiter le nombre de handle INSERT DELAYED à max_delayed_threads. Si vous tentez d'insérez des informations dans une nouvelle table après que tous les threads INSERT DELAYED soient utilisés, l'attribut DELAYED sera ignoré.
  • max_join_size Les regroupements qui vont probablement rassembler plus de max_join_size lignes retourne une erreur. Affectez une limite si vos utilisateurs prennent la mauvaise habitude d'exécuter des regroupements de plusieurs millions de lignes.
  • max_sort_length Le nombre d'octets à utiliser pour classer les objets de type BLOB ou TEXT (Seuls lest max_sort_length premiers octets de chaque valeurs seront utilisés, le reste sera ignoré).
  • max_tmp_tables (Cette option n'est pas encore effective). Nombre maximum de tables temporaires qu'un client peut avoir en même temps.
  • net_buffer_length Le buffer de communication est redimensionné entre deux requête. Cette option ne devrait généralement pas être modifiée, mais si vous avez très peu de mémoire, vous pouvez lui affecter la taille attendue de la requête, c'est à dire la longueur des commandes envoyées par les clients). Si une commande est trop grande, le buffer sera automatiquement étendu jusqu'à max_allowed_packet octets.
  • record_buffer Chaque thread qui effectue une analyse séquentielle, alloue un buffer de cette taille pour chaque table scannée. Pour augmenter le nombre de recherche séquentielles simultanées, augmentez cette valeur.
  • sort_buffer Chaque thread qui a besoin de classer des données alloue un buffer de sort_buffer octets. Augmentez cette valeur pour accélerer les clauses ORDER BY ou GROUP BY. 18.5 Où MySQL enregistre les fichiers temporaires
  • table_cache Le nombre de tables ouvertes par tous les threads. Augmenter cette valeur revient à augmenter le nombre de pointeur de fichier dont mysqld a besoin. MySQL a besoin de deux pointeurs de fichiers pour chaque table. Voir ci-dessous les remarques à propos de la limite du nombre de pointeur de fichier. Pour des détails concernant le cache des tables, 10.8 Comment MySQL ouvre et ferme les tables.
  • tmp_table_size Si une table temporaire excède cette taille, MySQL génère une erreur sous la forme :@: The table nom_table is full. Augmentez la valeur de tmp_table_size pour réaliser des requêtes GROUP BY plus compliquées.
  • thread_stack La taille de la pile pour chaque thread. La plus part du temps, les erreurs repérées par crash-me dépendent de cette valeur. La valeur par défaut est généralement suffisante. Benchmarks.
  • wait_timeout Le nombre de seconde d'inactivité d'une connexion avant qu'elle soit fermée.

    table_cache, max_connections et max_tmp_tables limite le nombre maximal de fichier que le serveur peut avoir ouvert en même temps. Si vous augmentez ces valeurs, vous risquez d'être limité par votre système d'exploitation. Cependant, il est possible d'augmenter le nombre de pointeur de fichier par processus, sur certains systèmes. Consultez la documentation de votre système, car ces manipulations varient beaucoup de l'un à l'autre. table_cache est lié à max_connections. Par exemple, pour 200 connexions, vous devez avoir un cache de table d'au moins 200 clean_docs_mysql clean_docs-php debut.php fin.php index.php menu.php mysql php n, avec n nombre maximum de tables dans un regroupement. MySQL utilise des algorithmes très efficaces, ce qui permet de le faire tourner avec peut de mémoire. Cependant, pour accélérer les traitement, allouez plus de mémoire à MySQL. En ayant beaucoup de mémoire, et beaucoup de table, si vous souhaitez de bonne performances, avec un nombre modéré de clients, utilisez une configuration telle que :@: 

    shell> safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M &

    Si vous avez peu de mémoire et beaucoup de connexion, utilisez une configuration telle que :@: 

    shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k -O record_buffer=100k &

    voire même :@: 

    shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

    Si le nombre de connexion est vraiment très grand, le système va commencer à swapper, à moins que mysqld n'ait été configuré pour utiliser très peu de mémoire pour chaque connexion. Bien entendu, mysqld fonctionne bien mieux si vous avez assez de mémoire pour toutes les connexions.

    NB :@: si vous changez une option de mysqld, cette dernière ne sera prise en compte qu'à la prochaine instance du serveur.

    Pour voir si les changement de paramètres ont été pris en compte, utilisez la commande :@: 

    shell> mysqld -O key_buffer=32m --help

    Assurez vous que l'option --help est mise en dernier, sinon les options placées après seront ignorées.