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.

<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>

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