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.

 

 

130 comments
  1. kyrie 5

    I would like to express my appreciation to you just for bailing me out of this particular circumstance. After browsing throughout the the web and obtaining proposals which were not beneficial, I thought my life was well over. Existing without the strategies to the difficulties you have sorted out as a result of this guide is a crucial case, and those that might have badly affected my entire career if I had not noticed the website. Your talents and kindness in controlling every item was very useful. I’m not sure what I would have done if I had not discovered such a thing like this. It’s possible to at this time look forward to my future. Thanks so much for the reliable and sensible help. I will not hesitate to propose the blog to any person who ought to have guide about this issue.

  2. bape

    I want to show my admiration for your generosity for all those that really want assistance with your concept. Your very own commitment to getting the solution all around came to be especially powerful and have without exception empowered people just like me to attain their pursuits. Your new useful suggestions implies this much to me and somewhat more to my mates. Many thanks; from all of us.

  3. off white clothing

    I must get across my appreciation for your kind-heartedness for people that should have help with that content. Your special dedication to getting the message all through was particularly beneficial and have in most cases made professionals just like me to attain their endeavors. Your own invaluable suggestions indicates this much a person like me and especially to my peers. With thanks; from each one of us.

  4. stephen curry shoes

    I precisely needed to appreciate you once again. I do not know what I would’ve implemented in the absence of these smart ideas shared by you concerning such a subject. It had been the alarming difficulty in my position, however , spending time with this expert approach you managed it made me to jump for contentment. I am just grateful for the help and expect you really know what an amazing job you happen to be accomplishing instructing other individuals by way of your site. I am certain you haven’t met any of us.

  5. giannis antetokounmpo shoes

    I just wanted to write down a simple message to appreciate you for all the fabulous strategies you are posting at this site. My time-consuming internet research has finally been honored with beneficial details to exchange with my friends and classmates. I would claim that we visitors actually are rather blessed to exist in a wonderful community with many awesome people with helpful hints. I feel really happy to have used the website and look forward to some more entertaining moments reading here. Thank you once again for all the details.

  6. supreme new york

    I have to point out my affection for your kindness giving support to people who really need guidance on that subject. Your real dedication to getting the message around became exceedingly informative and have all the time empowered girls much like me to realize their targets. Your entire helpful publication means this much a person like me and somewhat more to my fellow workers. Thanks a lot; from everyone of us.

  7. off white nike

    I simply had to say thanks all over again. I do not know what I might have tried in the absence of those concepts shared by you about this subject. Entirely was a terrifying situation in my opinion, nevertheless taking a look at the very skilled way you processed the issue took me to jump with joy. Now i am happy for your advice and hope that you realize what an amazing job you have been getting into training the rest through the use of your webpage. I am certain you’ve never encountered all of us.

  8. off white t shirt

    I must convey my passion for your kindness giving support to people that really need help on this particular subject. Your real dedication to passing the message up and down came to be exceptionally helpful and have truly enabled men and women like me to attain their targets. Your insightful instruction implies a whole lot to me and additionally to my fellow workers. Thanks a ton; from everyone of us.

  9. yeezy supply

    I have to voice my love for your kind-heartedness for persons who need guidance on this particular concept. Your very own commitment to passing the solution around became incredibly useful and have frequently encouraged workers like me to get to their targets. The invaluable report means this much a person like me and extremely more to my peers. Warm regards; from everyone of us.

  10. supreme clothing

    Needed to put you a bit of word so as to thank you very much over again with your striking pointers you have shown on this page. This is certainly strangely open-handed with you to give without restraint precisely what a number of us would’ve marketed for an e-book to generate some bucks for themselves, principally considering that you could have done it in case you desired. Those suggestions additionally acted like the great way to be sure that other people have the identical interest like mine to realize much more in regard to this problem. I believe there are lots of more pleasurable sessions in the future for individuals that look over your website.

  11. bape clothing

    I simply had to thank you very much once more. I’m not certain the things that I would have carried out in the absence of the smart ideas shown by you relating to that theme. It actually was a real scary difficulty for me personally, however , considering a new professional tactic you resolved that took me to jump over delight. I will be happy for your assistance and thus expect you are aware of an amazing job your are undertaking instructing the rest with the aid of your websites. Most likely you have never met any of us.

  12. supreme

    I wish to voice my admiration for your generosity in support of visitors who actually need guidance on that subject matter. Your special commitment to getting the solution throughout came to be extraordinarily important and has all the time empowered girls much like me to realize their targets. Your entire warm and friendly instruction entails much a person like me and much more to my office colleagues. Warm regards; from everyone of us.

  13. kd shoes

    I want to voice my gratitude for your kind-heartedness giving support to people who have the need for help with this question. Your special dedication to getting the message around appeared to be definitely advantageous and have in most cases encouraged those much like me to reach their aims. Your new helpful publication means this much a person like me and extremely more to my office colleagues. Thanks a ton; from each one of us.

  14. kawhi leonard shoes

    I simply desired to say thanks once again. I’m not certain the things that I would have gone through in the absence of the type of basics documented by you regarding such a problem. Previously it was a very scary scenario for me personally, however , witnessing a expert form you processed that made me to weep with contentment. Now i am happier for your information and then sincerely hope you comprehend what a great job your are putting in instructing the mediocre ones through a web site. I’m certain you haven’t come across any of us.

Laisser un commentaire

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