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 : 
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.
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.
<🎜. >
<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 ('aaa');</code>
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'optimisation1 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 :
#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
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.
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 :
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;
}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!
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
