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.
how to get a merchant account to accept credit cards
One thing is the fact one of the most widespread incentives for making use of your credit cards is a cash-back or rebate offer. Generally, you’ll have access to 1-5 back upon various expenses. Depending on the cards, you may get 1 returning on most purchases, and 5 again on expenses made at convenience stores, gas stations, grocery stores along with ‘member merchants’.
Juhiox
cost zestril 5mg buy zestril tablets order metoprolol 100mg for sale
off white
I must express my love for your generosity giving support to those who should have assistance with this particular question. Your special commitment to passing the solution all through has been surprisingly powerful and has in most cases allowed those much like me to get to their goals. This interesting help and advice implies a whole lot a person like me and even more to my office workers. Many thanks; from all of us.
kyrie 8
I intended to draft you a little note to finally thank you over again over the gorgeous things you have provided in this article. It’s quite remarkably open-handed with people like you giving without restraint all that numerous people would have offered as an e book in order to make some money on their own, chiefly considering that you might well have tried it in the event you wanted. The techniques additionally served as a good way to be sure that other people have similar interest just as mine to grasp a good deal more concerning this problem. I am sure there are several more pleasant times up front for people who looked over your website.
yeezy
Thank you a lot for providing individuals with such a splendid chance to check tips from here. It is often so useful and as well , packed with a lot of fun for me and my office co-workers to visit the blog a minimum of 3 times in one week to learn the newest guides you will have. Of course, I’m just always impressed with all the beautiful tips and hints you give. Selected 4 points in this post are without a doubt the most effective we have had.
jordan travis scott
Needed to draft you a little word to give many thanks the moment again on the marvelous opinions you have contributed here. It has been so strangely open-handed of people like you in giving unreservedly what exactly a few people could have distributed as an electronic book to get some money for themselves, especially since you might have tried it if you wanted. Those strategies additionally served like the fantastic way to fully grasp that some people have the same eagerness really like my very own to know more regarding this issue. I believe there are numerous more enjoyable moments up front for people who see your site.
commercial junk removal service
I do like the way you have framed this problem and it does indeed offer me personally a lot of fodder for thought. Nevertheless, through just what I have observed, I simply hope when other comments stack on that folks continue to be on issue and don’t embark upon a tirade associated with some other news du jour. Yet, thank you for this excellent point and even though I can not really concur with this in totality, I respect your viewpoint.
fear of god
I am glad for writing to make you know what a fabulous encounter my cousin’s child experienced using your site. She discovered several details, most notably what it is like to possess a wonderful teaching mindset to get others completely thoroughly grasp a variety of tortuous subject areas. You undoubtedly exceeded our expectations. Thank you for showing the important, safe, informative as well as fun thoughts on the topic to Emily.
Kichsl
ipratropium 100 mcg usa cost ipratropium 100mcg oral linezolid 600mg
Lean Belly 3x CA
One thing is always that one of the most popular incentives for using your cards is a cash-back and also rebate provision. Generally, you get 1-5 back upon various expenditures. Depending on the credit card, you may get 1 back again on most expenses, and 5 in return on expenses made from convenience stores, gas stations, grocery stores plus ‘member merchants’.
furniture removers
Thanks for the a new challenge you have discovered in your blog post. One thing I’d prefer to comment on is that FSBO connections are built as time passes. By releasing yourself to owners the first weekend break their FSBO is actually announced, prior to masses start off calling on Thursday, you build a good relationship. By sending them tools, educational components, free reports, and forms, you become an ally. If you take a personal curiosity about them and their predicament, you produce a solid relationship that, on many occasions, pays off in the event the owners opt with a real estate agent they know along with trust – preferably you.
Stage Hypnotist
you will have an important blog right here! would you prefer to make some invite posts on my weblog?
Lutein
What I have seen in terms of laptop or computer memory is the fact that there are specifications such as SDRAM, DDR etc, that must fit the technical specs of the motherboard. If the computer’s motherboard is reasonably current and there are no main system issues, changing the memory space literally will take under an hour. It’s one of the easiest pc upgrade types of procedures one can envision. Thanks for discussing your ideas.
golden goose stardan
I have to express my affection for your kindness giving support to visitors who need assistance with in this concern. Your special dedication to passing the solution up and down ended up being particularly powerful and has without exception permitted ladies like me to arrive at their goals. Your new warm and helpful suggestions indicates a lot a person like me and even further to my colleagues. Warm regards; from each one of us.
Travis Scott Jordan
I am also writing to make you understand of the nice experience my friend’s daughter obtained browsing the blog. She mastered many pieces, most notably what it’s like to have a marvelous helping character to get many people quite simply understand various tricky matters. You undoubtedly did more than readers’ expected results. I appreciate you for offering such warm and helpful, dependable, explanatory and easy tips about this topic to Sandra.
supreme
I simply had to thank you very much once more. I am not sure the things that I would’ve undertaken without the type of thoughts provided by you on this area. It became a difficult concern for me, nevertheless finding out the expert approach you processed the issue forced me to cry with gladness. I’m grateful for your work and as well , pray you know what an amazing job your are undertaking training many people via a web site. Most probably you’ve never met all of us.
Jrujxf
order coreg sale chloroquine ca chloroquine sale