Résoudre le problème du thread MySQL dans l'ouverture des tables (avec exemples)

Le contenu de cet article concerne la résolution du problème du thread MySQL dans l'ouverture des tables (avec des exemples). Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

Description du problème

Il y a récemment un serveur MySQL5.6.21 après la sortie de l'application, le thread simultané Threads_running augmente rapidement, atteignant environ 2000, et un grand nombre. des threads attendent l'ouverture des tables, l'état de fermeture des tables et le délai d'attente d'accès logique lié à l'application.

[Processus d'analyse]

1. Après la sortie de l'application à 16h10, Opened_tables continue d'augmenter, comme le montre la figure suivante :

Afficher le défaut à ce moment-là Dans le fichier journal pt-stalk capturé au cours de la période, au moment du 18/01/2019 16:29:37, la valeur de Open_tables est 3430 et la valeur de configuration de table_open_cache est 2000.

Lorsque la valeur Open_tables est supérieure à la valeur table_open_cache, chaque fois qu'une nouvelle session ouvre la table, certaines tables ne peuvent pas accéder au cache de table et doivent rouvrir la table. Le phénomène qui en résulte est qu'il y a un grand nombre de threads dans l'état des tables d'ouverture.

2. Les tables de cet exemple, plus la base de données système, totalisent 851, ce qui est bien inférieur aux 2000 de table_open_cache. Pourquoi Open_tables atteint-il 3430

Cela peut être expliqué à partir des documents officiels.
https://dev.mysql.com/doc/refman/5.6/en/table-cache.html

table_open_cache is related to max_connections. For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute.

Le nombre de threads simultanés a atteint 1980 à ce moment-là, en supposant 30 % de ces connexions simultanées. Si 2 tables sont accédées et que les autres sont toutes des tables uniques, alors la taille du cache atteindra (1980*30%*2+1980*70%*1) = 2574

3 . QPS est relativement stable avant et après la sortie , à en juger par les demandes externes, il n'y a pas d'augmentation soudaine des demandes de connexion, mais après la sortie, threads_running a atteint un sommet de près de 2000 et continue. On suppose qu'une certaine instruction SQL publiée a déclenché le problème.

4. Vérifiez les informations de la liste de processus capturées à ce moment-là. Il existe une déclaration selon laquelle l'accès simultané SQL est très élevé. 8 tables physiques ont été interrogées :

5. Créez les mêmes 8 tables dans l'environnement de test, videz le cache des tables et comparez avant et après l'exécution de SQL en une seule session. La valeur de Open_tables augmentera de 8. S'il y a une concurrence élevée, la valeur. d'Open_tables augmentera considérablement.

<code>select id,name,email from table1 left join table2<br/>union all<br/>select id,name,email from table3 left join table4<br/>union all<br/>select id,name,email from table5 left join table6<br/>union all<br/>select id,name,email from table7 left join table8<br/>where id in (&#39;aaa&#39;);</code>

Reproduction du problème

Simulez le scénario d'accès simultané élevé dans l'environnement de test, exécutez l'instruction SQL ci-dessus simultanément avec 1000 threads et reproduisez le production Un phénomène similaire se produit dans l'environnement. Open_tables atteint rapidement 3800, et un grand nombre de processus sont dans l'état Ouverture des tables et fermeture des tables.

Plan d'optimisation

1 Après avoir localisé la cause du problème, nous avons communiqué avec nos collègues de développement et suggéré d'optimiser le SQL pour réduire le nombre de SQL à phrase unique. interroger les tables ou réduire considérablement la fréquence d'accès simultané SQL.

Cependant, avant que les collègues de développement puissent l'optimiser, le problème s'est à nouveau produit dans l'environnement de production. À cette époque, lors du dépannage du DBA, le table_open_cache est passé de 2 000 à 4 000. L'utilisation du processeur a augmenté, mais l'effet n'était pas évident. Le problème de l'attente pour l'ouverture des tables existait toujours.

2. Analysez les informations pstack capturées lors de l'échec et agrégez-les avec pt-pmp Nous voyons qu'un grand nombre de threads attendent des ressources mutex lorsqu'ils open_table :

At. cette fois, mutex dans table_cache_manager Le conflit était très grave.

#0  0x0000003f0900e334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1  0x0000003f0900960e in _L_lock_995 () from /lib64/libpthread.so.0
#2  0x0000003f09009576 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3  0x000000000069ce98 in open_table(THD*, TABLE_LIST*, Open_table_context*) ()
#4  0x000000000069f2ba in open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) ()
#5  0x000000000069f3df in open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int) ()
#6  0x00000000006de821 in execute_sqlcom_select(THD*, TABLE_LIST*) ()
#7  0x00000000006e13cf in mysql_execute_command(THD*) ()
#8  0x00000000006e4d8f in mysql_parse(THD*, char*, unsigned int, Parser_state*) ()
#9  0x00000000006e62cb in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
#10 0x00000000006b304f in do_handle_one_connection(THD*) ()
#11 0x00000000006b3177 in handle_one_connection ()
#12 0x0000000000afe5ca in pfs_spawn_thread ()
#13 0x0000003f09007aa1 in start_thread () from /lib64/libpthread.so.0
#14 0x0000003f088e893d in clone () from /lib64/libc.so.6

Étant donné que la valeur par défaut du paramètre table_open_cache_instances sous MySQL5.6.21 est 1, j'ai pensé qu'augmenter le paramètre table_open_cache_instances et ajouter des partitions de cache de table devrait atténuer les conflits.

3. Dans l'environnement de test, nous avons ajusté les deux paramètres table_open_cache_instances=32, table_open_cache=6000, et avons également exécuté le SQL problématique avec 1000 threads simultanément. Cette fois, les threads en attente d'ouverture et de fermeture des tables ont disparu. , et MySQL QPS a également augmenté de 12 000 à 55 000.

Par rapport à la même situation, en ajustant uniquement table_open_cache=6000, le nombre de processus en attente d'ouverture de tables est passé de 861 à 203. Le problème a été atténué. Plus de 600 processus sont passés de l'état d'attente d'ouverture de tables à l'état d'exécution. et le QPS a augmenté à 40 000 environ, mais pas de remède.

Analyse du code source

J'ai vérifié le code pour la logique pertinente de table_open_cache :

1 La fonction Table_cache::add_used_table est la suivante. la connexion ouvre la table, Lorsque la table n'existe pas dans le cache, ouvrez la table et ajoutez-la à la liste des tables utilisées :

2 À chaque fois, add_used_table appellera la fonction Table_cache::free_unused_tables_if_necessary, lorsque m_table_count > table_cache_size_per_instance &&m_unused_tables est satisfait, Remove_table est exécuté et vidé le cache supplémentaire dans la liste m_unused_tables. Parmi eux, table_cache_size_per_instance= table_cache_size / table_cache_instances. La configuration par défaut de MySQL5.6 est 2000/1=2000 Lorsque la valeur m_table_count est supérieure à 2000 et que m_unused_tables n'est pas vide, Remove_table est exécuté pour vider le cache des tables dans m_unused_tables. De cette façon, m_table_count est la valeur de Open_tables et restera normalement autour de 2000.

bool Table_cache::add_used_table(THD *thd, TABLE *table)
{
  Table_cache_element *el;

  assert_owner();

  DBUG_ASSERT(table->in_use == thd);

  /*
    Try to get Table_cache_element representing this table in the cache
    from array in the TABLE_SHARE.
  */
  el= table->s->cache_element[table_cache_manager.cache_index(this)];

  if (!el)
  {
    /*
      If TABLE_SHARE doesn't have pointer to the element representing table
      in this cache, the element for the table must be absent from table the
      cache.

      Allocate new Table_cache_element object and add it to the cache
      and array in TABLE_SHARE.
    */
    DBUG_ASSERT(! my_hash_search(&m_cache,
                                 (uchar*)table->s->table_cache_key.str,
                                 table->s->table_cache_key.length));

    if (!(el= new Table_cache_element(table->s)))
      return true;

    if (my_hash_insert(&m_cache, (uchar*)el))
    {
      delete el;
      return true;
    }

    table->s->cache_element[table_cache_manager.cache_index(this)]= el;
  }

  /* Add table to the used tables list */  
  el->used_tables.push_front(table);

  m_table_count++;  free_unused_tables_if_necessary(thd);

  return false;
}

3. Augmentez table_cache_instances à 32. Lorsque Open_tables dépasse (2000/32=62), la condition sera remplie et le nettoyage de m_unused_tables dans la logique ci-dessus sera accéléré, réduisant encore le nombre dans le cache de table. Cela entraînera une augmentation de Table_open_cache_overflows.

void Table_cache::free_unused_tables_if_necessary(THD *thd)
{
  /*
    We have too many TABLE instances around let us try to get rid of them.

    Note that we might need to free more than one TABLE object, and thus
    need the below loop, in case when table_cache_size is changed dynamically,
    at server run time.
  */
  if (m_table_count > table_cache_size_per_instance && m_unused_tables)
  {
    mysql_mutex_lock(&LOCK_open);
    while (m_table_count > table_cache_size_per_instance &&
           m_unused_tables)
    {
      TABLE *table_to_free= m_unused_tables;      
      remove_table(table_to_free);
      intern_close_table(table_to_free);
      thd->status_var.table_open_cache_overflows++;
    }
    mysql_mutex_unlock(&LOCK_open);
  }
}

4、当table_open_cache_instances从1增大到32时，1个LOCK_open锁分散到32个m_lock的mutex上，大大降低了锁的争用。

/** Acquire lock on table cache instance. */
  void lock() { mysql_mutex_lock(&m_lock); }
  /** Release lock on table cache instance. */
  void unlock() { mysql_mutex_unlock(&m_lock); }

解决问题

我们生产环境同时采取下面优化措施，问题得以解决：
1、 读写分离，增加read节点，分散master库的压力；
2、 调整table_open_cache_instances=16；
3、 调整table_open_cache=6000；

总结

当出现Opening tables等待问题时，
1、建议找出打开表频繁的SQL语句，优化该SQL，降低单句SQL查询表的数量或大幅降低该SQL的并发访问频率。

2、设置合适的table cache，同时增大table_open_cache_instances和 table_open_cache参数的值。

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!