MySQL線程處于Opening tables的問題解決（附示例）

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.

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');

#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

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); ??} }

/**?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);?}