本篇文章给人人带来的内容是关于MySQL线程处于Opening tables的题目处理(附示例),有肯定的参考价值,有须要的朋侪能够参考一下,愿望对你有所协助。
题目形貌
近来有一台MySQL5.6.21的服务器,在运用宣布后,并发线程Threads_running敏捷升高,到达2000摆布,大批线程处于守候Opening tables、closing tables状况,运用端相干逻辑接见超时。
【剖析历程】
1、16:10运用宣布完毕后,Opened_tables不停增添,以下图所示:
检察当时毛病时期抓取的pt-stalk日记文件,时候点2019-01-18 16:29:37,Open_tables 的值为3430,而table_open_cache的设置值为2000。
当Open_tables值大于table_open_cache值时,每次新的session翻开表,有一些没法掷中table cache,而不能不从新翻开表。如许回响反映出来的征象就是有大批的线程处于opening tables状况。
2、这个实例下的表,加上体系数据库下合计851张,远小于table_open_cache的2000,为何会致使Open_tables到达3430呢
从官方文档中能够获得诠释,
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.
当时并发线程数到达1980,假定这些并发衔接中有30%是接见2张表,其他都是单表,那末cache size就会到达(1980*30%*2+1980*70%*1)=2574
3、QPS在宣布前后都比较安稳,从外部要求来看并没有突增的衔接要求,但在宣布后threads_running上升到靠近2000的高位,一向延续。猜想是因为某个宣布的SQL语句触发了题目。
4、检察当时抓取的processlist信息,有一句SQL并发接见很高,查询了8张物理表,SQL样本以下:
select id,name,email from table1 left join table2
union all
select id,name,email from table3 left join table4
union all
select id,name,email from table5 left join table6
union all
select id,name,email from table7 left join table8
where id in ('aaa');
5、在测试环境中建立雷同的8张表,清空表缓存,单个session实行SQL前后对照,Open_tables的值会增添8,假如高并发的情况下,Open_tables的值就会大幅增添。
题目重现
在测试环境上模仿高并发接见的场景,并发1000个线程同时实行上面的SQL语句,复现了生产环境相似的征象,Open_tables敏捷到达3800,大批历程处于Opening tables、closing tables状况。
优化计划
1、 定位到题目缘由后,我们与开辟同事沟通,发起优化该SQL,下降单句SQL查询表的数目或大幅下降该SQL的并发接见频次。
不过开辟同事还没来的及优化,生产环境上毛病又涌现了。当时DBA排障时将table_open_cache从2000增添4000,CPU使用率上升,结果并不显著,守候Opening tables的题目依旧存在。
2、 剖析毛病时期抓取的pstack信息,用pt-pmp聚合后,看到大批线程在open_table时守候mutex资本:
#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
这时候table_cache_manager中的mutex争执异常严峻。
因为MySQL5.6.21下table_open_cache_instances参数的默许值为1,想到增大table_open_cache_instances参数,增添表缓存分区,应当能够减缓争用。
3、 在测试环境上,我们调解两个参数table_open_cache_instances=32,table_open_cache=6000,一样并发1000个线程实行题目SQL,此次守候Opening tables、closing tables的线程消逝了,MySQL的QPS也从12000上升到55000。
对照雷同情况下,只调解table_open_cache=6000,守候Opening tables的历程数从861下降到203,题目有所减缓,有600多个历程已从守候Opening tables变成运转状况,QPS上升到40000摆布,但不能根治。
源码剖析
查了下代码有关table_open_cache的相干逻辑:
1、Table_cache::add_used_table函数以下,当新的衔接翻开的表在table cache中不存在时,翻开表加入到used tables list:
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; }
2、每次add_used_table会挪用Table_cache::free_unused_tables_if_necessary函数,当满足m_table_count > table_cache_size_per_instance &&m_unused_tables时,实行remove_table,消灭m_unused_tables列表中过剩的cache。个中table_cache_size_per_instance= table_cache_size / table_cache_instances,MySQL5.6的默许设置是2000/1=2000,当m_table_count值大于2000而且m_unused_tables非空时就实行remove_table,将m_unused_tables中的table cache清空。如许m_table_count就是Open_tables的值正常会维持在2000高低。
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); } }
3、增大table_cache_instances为32,当Open_tables凌驾(2000/32=62)时,就会满足前提,加快上述逻辑中m_unused_tables的清算,使得table cache中数目进一步削减,会致使Table_open_cache_overflows升高。
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参数的值。
以上就是MySQL线程处于Opening tables的题目处理(附示例)的细致内容,更多请关注ki4网别的相干文章!