Mysql tmp_table_size max_heap_table_size

Sur mon serveur il y a 2 jours mon tmp_table_size = max_heap_table_size(16M) .

J’ai fait un job cron qui s’exécute une fois par heure et génère un rapport à partir de: created_tmp_disk_tables , created_tmp_files , created_tmp_tables

Dans mon rapport: created_tmp_disk_tables + created_tmp_files + created_tmp_tables = 100% de mes données temporaires

Avec ça :

  1. avec tmp_table_size = max_heap_table_size = 16M le rapport m’a montré le prochain rapport moyen:
    • 27,37% (created_tmp_disk_tables)
    • 1,16% (created_tmp_files)
    • 71.48% (created_tmp_tables)

Comment puis-je optimiser ces résultats?

  1. avec tmp_table_size = max_heap_table_size = 20M dans la première heure:

    • 23,48% (created_tmp_disk_tables)
    • 32,44% (created_tmp_files)
    • 44,07% (created_tmp_tables)

Après 7 heures (à partir du redémarrage):

  • 21,70% (created_tmp_disk_tables)
  • 33,75% (created_tmp_files)
  • 44.55% (created_tmp_tables)

Ce n’est pas ce à quoi je m’attendais.

  • les tables de disques sont 27.37% de 27.37% à 21.70% -> beaucoup plus attendu
  • les dossiers temporaires augmentent de 1.16% à 33.75% -> pourquoi?
  • les tables de mémoire ont diminué de 71.48% à 44.55% -> étrange; devrait augmenter

Chaque fois que vous augmentez tmp_table_size et max_heap_table_size , gardez à l’esprit que la définition de ces parameters ne permet pas d’améliorer le comportement des requêtes. En fait, les requêtes inefficaces se comportent moins bien qu’avant. Dans quelles circonstances?

Lorsqu’une requête effectue une jointure ou un sorting (via ORDER BY ) sans bénéficier d’un index, une table temporaire doit être formée en mémoire. Cela incrémenterait Created_tmp_tables .

Que se passe-t-il si la table temporaire atteint le nombre d’octets dans tmp_table_size et nécessite plus d’espace? La séquence d’événements suivante se produit:

  1. Le traitement des requêtes doit cesser
  2. Créer une table temporaire sur le disque
  3. Transférer le contenu de la table temporaire basée sur la mémoire dans la table temporaire basée sur le disque
  4. Déposer dans la table temporaire basée sur la mémoire
  5. Le traitement des requêtes continue à utiliser la table temporaire basée sur le disque

Ce processus s’incrémente Created_tmp_disk_tables

Connaissant ces mécanismes, explorons ce qui s’est passé dans chaque cas

les tables de disques ont diminué de 27,37% à 21,70% -> beaucoup plus attendu

Cela peut facilement se produire si les requêtes qui ont été exécutées avant ont mis en cache les résultats restant dans la RAM. Cela éliminerait le besoin de traiter la requête depuis le début et de ne pas recréer les mêmes tables temporaires volumineuses.

les dossiers temporaires augmentent de 1,16% à 33,75% -> pourquoi?

Ce n’est pas surprenant. Cela fait simplement ressortir le fait que certaines requêtes nécessitent des tables temporaires. Ils ont été créés en RAM d’abord. Cela indique simplement la présence de requêtes qui ne se joignent pas bien (peut-être join_buffer_size est trop petite) ou ORDER BY des colonnes ou des colonnes non indexées avec une table temporaire (peut-être que sort_buffer_size est trop petit).

les tables de mémoire ont diminué de 71,48% à 44,55% -> étrange; devrait augmenter

Ce n’est pas surprenant non plus. S’il y a suffisamment d’appels pour la même requête avec les mêmes valeurs, les sortings et les jointures peuvent être préemptés par la réalisation de requêtes provenant de résultats précédemment mis en cache.

RECOMMANDATION

À la lumière de ces choses, voici ce qui pourrait être ajusté:

  • join_buffer_size
  • sort_buffer_size
  • Créer des index qui seraient utilisés
    • dans les jointures via eq_ref
    • en quelque sorte via des parsings d’index dans l’ordre

L’objective global devrait être d’empêcher la création de la table temporaire autant que possible. Augmenter simplement tmp_table_size et max_heap_table_size permet aux requêtes et aux tables inefficaces de ne pas fonctionner correctement.