Ubuntu-fr

ethan7888

Optimizer tmp_table_size et max_heap_table_size

Bonjour,

Nous avons des lenteurs d’exécution sur certaines requêtes SQL, notre prestataire du site web, nous a conseillé d'augmenter les valeurs "tmp_table_size" et "max_heap_table_size"

Mais je ne sais pas comment faire, étant un véritable noob sur ce sujet, il y a un calcul à faire avec la ram de la machine ou autre par exemple pour trouver le bon chiffre à mettre sur ces deux options ?

Notre machine actuelle qui fait tourner la base est de 4 vcpu de 3,1 Ghz avec 15 Go de ram

Auriez-vous une idée ?

En vous remerciant d'avance

bruno

Re : Optimizer tmp_table_size et max_heap_table_size

Bonjour,

Avant de faire quoi que ce soit je te conseille d'utiliser MySQLTuner. Si MySQL tourne depuis suffisamment longtemps, cela devrait donner de bonnes indications sur les optimisations à faire.

Dernière modification par bruno (Le 13/12/2017, à 14:05)

ethan7888

Re : Optimizer tmp_table_size et max_heap_table_size

Bonjour,

Avant de faire quoi que ce soit je te conseille d'utiliser MySQLTuner. Si MySQL tourne depuis suffisamment longtemps, cela devrait donner de bonnes indications sur les optimisations à faire.

Bonjour,

Merci beaucoup pour votre retour,

Voici ce que m'afficher le résultat de mysqltuner:

"General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: [url]http://[Merci de relire les règles]/1mi7c4C[/url]
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache ( 431)
Variables to adjust:
    query_cache_type (=1)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    table_open_cache (> 431)"

Donc si je n'ai pas les trois options suivantes:
query_cache_type (=1)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    table_open_cache (> 431)"

J'ai lancé ce test sur l'environnement de démo, il n'y a pas de risque potentielle si je le lance sur l'environnement de prod ? je ne sais pas vraiment qu'elle type de test il fait ^^

Cordialement

bruno

Re : Optimizer tmp_table_size et max_heap_table_size

Il faut utiliser MySQLTuner sur l'environnement de prod, sinon cela n'a aucun intérêt, et le service MySQL doit tourner depuis plusieurs jours/semaines. S'il vient d'être redémarré cela n'a aucun intérêt non plus…
Il n'y a aucun risque à utiliser ce script, si tu veux savoir ce qu'il fait : https://github.com/major/MySQLTuner-per … TERNALS.md

Attention de bien comprendre chaque paramètre à modifier. Si la modifications de certains paramètres peut améliorer les performances, cela peut aussi avoir exactement l'effet inverse

En fait, il serait bon de demander au « prestataire du site web » sur quoi il se base pour donner ce conseil. En effet, de ce que je sais, augmenter ces valeurs n’améliore pas l'exécution des requêtes (encore une fois cela aurait même plutôt l’effet inverse…)
Il faudrait aussi savoir quelle sont ces requêtes qui sont « lentes ». Si ont fait une requête horriblement complexe sur des tables mal foutues, on peut bidouiller les paramètres de MySQL dans tous les sens sans que cela n'améliore le temps d'exécution.

ethan7888

Re : Optimizer tmp_table_size et max_heap_table_size

Il faut utiliser MySQLTuner sur l'environnement de prod, sinon cela n'a aucun intérêt, et le service MySQL doit tourner depuis plusieurs jours/semaines. S'il vient d'être redémarré cela n'a aucun intérêt non plus…
Il n'y a aucun risque à utiliser ce script, si tu veux savoir ce qu'il fait : https://github.com/major/MySQLTuner-per … TERNALS.md

Attention de bien comprendre chaque paramètre à modifier. Si la modifications de certains paramètres peut améliorer les performances, cela peut aussi avoir exactement l'effet inverse

En fait, il serait bon de demander au « prestataire du site web » sur quoi il se base pour donner ce conseil. En effet, de ce que je sais, augmenter ces valeurs n’améliore pas l'exécution des requêtes (encore une fois cela aurait même plutôt l’effet inverse…)
Il faudrait aussi savoir quelle sont ces requêtes qui sont « lentes ». Si ont fait une requête horriblement complexe sur des tables mal foutues, on peut bidouiller les paramètres de MySQL dans tous les sens sans que cela n'améliore le temps d'exécution.

Bonjour,

En faite pour la petite histoire, voici le message d'erreur que j'avais dans les logs apache2 de mon serveur :

"[proxy_fcgi:error] [pid 24075] (32)Broken pipe: [client 127.0.0.1:44422] AH01075: Error dispatching request to : (passing brigade to output filters)" Et dans la me tranche d'horaires ou nous avions ce type d'erreurs, nous avions des 504 connexion time out sur le serveur. (Nous avons deux serveurs: un serveur web + serveur de database mysql)

Le prestataire du site web en a conclut les choses suivantes:

"Pour les erreurs 504, il est possible que se soit le  backend PHP qui est en cause. Il se peut que cela viennent du timeout du module proxy_fcgi Apache.

En ce qui concerne le temps d'exécution de la requête, voici le résultat graphique de la commande mysql EXPLAIN (voir la pièce jointe: https://imgur.com/a/uGK9Q)

On peut y voir qu'une table temporaire est construite, probablement parce que la clause GROUP BY n'a rien à voir avec la clause ORDER BY (cf. internal-temporary-tables).
Je ne sais pas pour l'instant si nous avons le contrôle sur cette requête mais en attendant, vous pouvez augmenter les valeurs tmp_table_size et max_heap_table_size comme indiqué dans le dernier lien cité."

Ok, je vais demander de suite au prestataire et je vous tiens au courant, merci encore pour tout vos conseils ^^

Cordialement

bruno

Re : Optimizer tmp_table_size et max_heap_table_size

Je ne vois pas l'image en lien, mais s'il s'agit effectivement d'une requête avec des GROUP BY / ORDER BY sur des tables mal indexées (ce qui oblige à créer un table temporaire en RAM) je doute que l'augmentation des valeurs citées y change quelque chose, cela pourrait me empirer…

Je suggérerais plutôt de regarder du coté de sort_buffer_size

ethan7888

Re : Optimizer tmp_table_size et max_heap_table_size

Je ne vois pas l'image en lien, mais s'il s'agit effectivement d'une requête avec des GROUP BY / ORDER BY sur des tables mal indexées (ce qui oblige à créer un table temporaire en RAM) je doute que l'augmentation des valeurs citées y change quelque chose, cela pourrait me empirer…

Je suggérerais plutôt de regarder du coté de sort_buffer_size

Bonjour,

Merci encore !

Je vais voir cela ^^