Loading...
système

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.

 

 

100 comments
  1. lebron james shoes

    I simply needed to say thanks all over again. I’m not certain the things I would’ve made to happen in the absence of the type of tips revealed by you about such a problem. It had become a real challenging matter in my position, nevertheless being able to see your specialised strategy you resolved that forced me to cry for delight. Now i’m happy for the assistance and then hope that you are aware of a great job that you’re undertaking training people today using a site. Most probably you’ve never got to know all of us.

  2. supreme

    I would like to voice my passion for your generosity supporting men and women who require assistance with this one topic. Your special commitment to passing the solution up and down appears to be extremely useful and has consistently enabled folks just like me to get to their desired goals. Your amazing interesting recommendations means a whole lot to me and even further to my peers. Thanks a ton; from everyone of us.

  3. kd 12

    I must convey my affection for your generosity giving support to individuals who actually need guidance on this particular concern. Your personal commitment to getting the message all through had become really functional and have in most cases enabled guys and women like me to arrive at their ambitions. Your own useful instruction can mean a lot to me and even further to my fellow workers. Thank you; from each one of us.

  4. russell westbrook shoes

    I want to express my passion for your kind-heartedness for those people who absolutely need help with this one concept. Your real commitment to passing the message up and down appears to be surprisingly significant and have continuously encouraged some individuals just like me to achieve their dreams. Your own useful guidelines entails much a person like me and somewhat more to my mates. Warm regards; from each one of us.

  5. supreme clothing

    I wish to show my admiration for your generosity in support of persons who need guidance on in this area. Your personal commitment to getting the solution along appeared to be astonishingly valuable and have always encouraged employees just like me to realize their dreams. Your personal valuable publication entails this much a person like me and somewhat more to my peers. With thanks; from each one of us.

  6. bape clothing

    My spouse and i got quite peaceful when John managed to deal with his inquiry while using the precious recommendations he got out of the blog. It’s not at all simplistic just to possibly be freely giving concepts which others could have been trying to sell. And we all acknowledge we now have you to be grateful to for this. All of the illustrations you made, the straightforward website navigation, the relationships your site help instill – it is all sensational, and it’s really helping our son and the family feel that the issue is cool, which is extraordinarily essential. Thanks for the whole lot!

  7. curry 7 shoes

    Needed to put you that little bit of observation in order to say thanks as before for the precious thoughts you’ve featured at this time. It’s really incredibly open-handed of people like you to give unhampered all that many individuals would’ve marketed as an ebook in order to make some cash for themselves, mostly seeing that you might have done it if you ever considered necessary. These tactics in addition worked as a easy way to recognize that many people have a similar zeal just like my own to grasp a good deal more on the subject of this issue. I know there are some more enjoyable moments up front for many who read through your site.

  8. golden goose sneakers

    I must express my admiration for your generosity giving support to men who need assistance with that concern. Your real dedication to passing the solution up and down had become exceedingly advantageous and has consistently encouraged men and women just like me to get to their aims. Your entire interesting report signifies so much to me and especially to my peers. With thanks; from each one of us.

  9. cheap kd 13

    I would like to show my affection for your kindness for folks who actually need assistance with this situation. Your special dedication to passing the solution all-around became wonderfully powerful and has allowed some individuals like me to get to their dreams. Your personal informative recommendations implies much a person like me and substantially more to my office workers. Thanks a lot; from all of us.

  10. curry shoes

    I wish to convey my love for your kind-heartedness in support of all those that really need assistance with your issue. Your very own dedication to getting the message up and down appears to be incredibly helpful and has frequently permitted many people just like me to get to their dreams. Your useful guide implies this much to me and further more to my fellow workers. Thanks a ton; from all of us.

  11. ggdb

    I want to point out my love for your generosity in support of folks who really want assistance with this important content. Your special commitment to passing the solution along ended up being remarkably interesting and have permitted professionals like me to achieve their objectives. The valuable guidelines signifies a whole lot a person like me and a whole lot more to my colleagues. Regards; from each one of us.

  12. off white clothing

    I wish to express my appreciation for your kindness in support of folks who require help with this concern. Your very own dedication to passing the message all through appeared to be really useful and has all the time empowered workers much like me to get to their targets. Your entire insightful suggestions implies a lot a person like me and especially to my fellow workers. Best wishes; from all of us.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.