问题描述
最近有一台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样本如下:
<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>
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参数的值。
《魔兽世界》大逃杀!60人新游玩模式《强袭风暴》3月21日上线
暴雪近日发布了《魔兽世界》10.2.6 更新内容,新游玩模式《强袭风暴》即将于3月21 日在亚服上线,届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。
艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕,并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时,他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中,玩家将会进入一个全新的海盗主题大逃杀式限时活动,其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场,作为一个独立于主游戏之外的活动,玩家可以用大逃杀的风格来体验《魔兽世界》,不分职业、不分装备(除了你在赛局中捡到的),光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式,玩家在加入海盗主题的预赛大厅区域前,可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹,《巨龙崛起》和《魔兽世界:巫妖王之怒 经典版》的玩家都可以获得奖励。
更新动态
- DzintraErliha-Dreamscape(2024)[24Bit-96kHz]FLAC
- 群星.1999-上华世纪国语总冠军4CD【上华】【WAV+CUE】
- 叶丽仪.1992-仙鹤神针电视剧原声音乐专辑【星传真】【WAV+CUE】
- 陈柏宇.2011-QUINQUENNIUM【SONY】【WAV+CUE】
- 《51首热门当红歌曲 十大顶级发烧女声 3CD》[WAV+CUE][1.6GB]
- 《赵传 深受摇滚迷青睐的台湾音乐人 约定》[WAV+CUE][320MB]
- 《庄心妍 实力唱将 发烧音乐精选集》[WAV+CUE][380MB]
- [音展纪念碟]2006年《TAA台湾第十六届国际HI-ENDHI-FI音响展》2cd[WAV]
- 中国音乐地图之听见西藏山歌牧歌酒歌2020[WAV分轨]
- 王宏伟2017-永远的红飘带[太平洋影音][WAV+CUE]
- 费翔.1997-NOW现在【第一中国】【WAV+CUE】
- 新格合唱团.1981-我唱你和2辑(滚石新格民歌系列)【新格】【WAV+CUE】
- 陈柏宇.2011-QUINQUENNIUM【SONY】【WAV+CUE】
- 雨果唱片-《金唛系列-蔷薇依韵琼瑶》WAV
- 陈百强《凝望(2012华纳LPCD45限量版)[DMI]》[WAV整轨]