Parametrage de MySql optimisation




Pour optimiser MySql il faut régler les paramètres du fichier de configuration /etc/mysql/my.cnf. et relancer mysql /etc/init.d/mysql restart

On peut en cours d’utilisation (phpMyAdmin fenêtre SQL) modifier les paramètres avec la commande de type :

SET GLOBAL query_cache_size = 250000000;                               (mettre query_cache_size à 250 Mo)

 

Pour faire un tuning 2 logiciels Tuning-primer et mysqltuner à installer sur votre serveur.

Description de quelques paramétrages MySQL :

? Key_buffer_size

– Tampon qui stock les index des tables MyISAM
– 25 à 30% de la RAM, pour un serveur dédié MySQL, en full MyISAM
– Pas trop grand car risque de swap !
– Ratio: key_reads / key_read_requests < 0.03 (0.01 encore mieux), sinon l’augmenter
– La fraction utilisée du buffer est : (Key_blocks_used * key_cache_block_size) / key_buffer_size

 

? myisam_sort_buffer_size

– Tampon pour la création d’index pour les requêtes de maintenance: ALTER TABLE, REPAIR TABLE, LOAD DATA INFILE
– SET SESSION myisam_sort_buffer_size = 800*1024*1024;
ALTER TABLE ma_table ADD INDEX …;

? bulk_insert_buffer_size

– Tampon pour les insertions massives

• INSERT …SELECT
• INSERT VALUES(),(),(),….
• LOAD DATA INFILE

 

? innodb_buffer_pool_size

– Tampon pour stocker les index et des données des tables InnoDB
– Jusqu’à 80% de la RAM, pour un serveur dédié MySQL, en full InnoDB

? innodb_flush_logs_at_trx_commit (1 par défaut)

– 0: Risque de pertes de transactions validées en cas de crash d ‘InnoDB
– 1: transactions flushées après chaque commit. Pas de pertes de transactions validées (ACID)
– 2: Risque de pertes de transactions validées uniquement en cas de crash de l’O.S.

 

? innodb_log_buffer_size

– Taille du tampon des logs d’InnoDB
– Vidé environ toutes les secondes (checkpoint)
– En général entre 8Mo & 16Mo

? innodb_log_file_size

– Taille des fichiers de log d’InnoDB (2 par défaut)
– Une grande valeur améliore les performances. Mais augmente le temps de restauration
– Valeurs courantes : 64Mo à 512Mo

 

? table_cache

– Cache des descripteurs de fichier
– Chaque table ouverte nécessite un descripteur de fichiers (par connection), plus un pour le .MYI (MyISAM)
– Augmenter votre table_cache si opened_tables croit rapidement

? thread_cache

– Cache des threads
– Chaque session prend un thread à la connexion et le rend à la déconnexion
– Augmenter si threads_created croit rapidement
– Taux de succès du cache de thread: threads_created/connections

 

? read_buffer_size

– Chaque thread qui fait une recherche séquentielle alloue un buffer de cette taille pour son scan. Si vous faites de nombreux scan séquentiels, vous pourriez avoir besoin d’augmenter cette valeur
– Tampon d’enregistrements pour les full table scans

 ? open_files_limit

– Il est courant de l’augmenter assez largement (ex : 65000)

 

? table_open_cache

– Si l’indicateur Opened_tables augmente, il est nécessaire d’accroitre la variable ‘table_open_cache’
– L’augmentation de cette variable augmente le nombre de descripteurs

 

? sort_buffer_size

  • Tampon pour GROUP BY / ORDER BY
  • Indicateurs :
    • Sort_scan  : indique en général les tablescan
    • Sort_merge_passes : pas assez d’espace dans le sort buffer pour faire un tri. Il faut peut-être augmenter le sort_buffer_size.

? join_buffer_size

– Zone de jointure des requêtes n’utilisant pas d’index. Elle peut être augmenté mais le mieux est de trouver les indexes pertinents pour cette requête.

– Ce buffer est alloué une fois pour chaque jointure entre deux tables.

? tmp_table_size

– Tampon pour les tables temporaires stockées en mémoire (memory). Au delà, elles sont copiées sur disque (MyISAM)

 

? query_cache_size

– La mémoire allouée pour stocker les résultats des vieilles requêtes
– Pour désactiver, il suffit de positionner à 0

 

? max_allowed_packet

– Le buffer de message est initialisé avec net_buffer_length octets, mais peut grandir jusqu’à max_allowed_packet octets lorsque nécessaire.
– Cette valeur est par défaut petit, pour intercepter les gros paquets, probablement erronés. Vous devez augmenter cette valeur si vous utilisez de grandes colonnes BLOB. Cette valeur doit être aussi grande que le plus grand BLOB que vous utiliserez.

 

? thread_cache_size

– nombre threads
– Dans beaucoup de cas, on met cette valeur à 0.

 

 





  • Digg
  • Del.icio.us
  • StumbleUpon
  • Reddit
  • Twitter
  • RSS

Si vous avez apprécié cet article, s’il vous plait, prenez le temps de laisser un commentaire
ou de souscrire au flux afin de recevoir les futurs articles directement dans votre lecteur de flux.

Laisser un Message