MySQL drop/truncatre 大表分析及解决方案
2020-02-05 本文已影响0人
GAOCHAO_DBA
课前知识
1.表空间组成
表空间是由索引组成，每个索引2个segment(非叶子节点段，叶子节点段);
segment有多个组组成，每个组256个extent，每个extent 1M;
extent由页面组成，每个extent有64个页面，每个页面16k;
页面由一行一行数据组成;
总结:表空间====>段====>组====>簇(区)====>页面====>行====>列。
2.drop表流程
drop表需要先释放索引树然后再删除表空间
背景
mysql> drop table logdata;
Query OK, 0 rows affected (59.43 sec)
870G的一个冷表drop需要60秒左右，这60秒带来的问题：
1.普通主从可能会造成磁盘io升高，影响集群响应，延迟增大...
2.PXC这类集群直接会堵死整个集群
疑问
1.大表清理选择drop还是truncate?
2.drop大表慢的原因?
3.drop表会清理bufferpool中页面吗?
4.关闭AHI能够节省drop表的时间吗?
5.drop期间数据库可以执行DML吗？
6.bufferpool总大小影响drop表时间吗？
7.被删除的表在bufferpool中的大小影响drop表时间吗？
环境
机器硬件:raid10+24CPU+128G内存
MySQL:percona5.6.44
BufferPool 16G:无预热
查看drop表在哪个方法上耗时最长
image.png
image.png
drop表耗时最长的方法及功能如下
耗时1）que_eval_sql（用来释放索引树）
fseg_free_extent /*释放索引段时循环调用释放extent,每个extent都会调用一次,每个extent 1M，可以通过ibd文件计算有多少个extent*/
/*删除自适应hash*/
if (ahi) { /*innodb_adaptive_hash_index如果为on会调用，否则跳过，注意有些版本是没有if(ahi)这个判断的，所以关闭AHI是否会生效取决于版本，percona5.6.44是没有的，oracle-mysql5.7.17是有的*/
for (i = 0; i < FSP_EXTENT_SIZE; i++) { /*FSP_EXTENT_SIZE为每个区的页面，为64*/
if (!xdes_mtr_get_bit(descr, XDES_FREE_BIT, i, mtr)) {
/* Drop search system page hash index
if the page is found in the pool and
is hashed */
btr_search_drop_page_hash_when_freed(page_id_t(space,first_page_in_extent + i),page_size);
}
}
}
/*释放索引段时循环调用释放extent,每个extent都会调用一次*/
flst_remove /*从指定文件段列表(全满区列表,空闲区列表,半满区列表)里面移除该node区*/
fut_get_ptr
buf_page_get_gen(xxx,mode,xxx) /*mode=BUF_GET=10,bufferpool中不存在会去磁盘获取*/
buf_page_hash_get_low(buf_pool, page_id) /*先从bufferpool中读取描述符页面*/
buf_read_page_low（如果在bufferpool中未找到描述符页面则调用这个方法到磁盘上获取）
#自适应hash删除逻辑
btr_search_drop_page_hash_when_freed（当页面从bufferpool逐出或者释放索引段的时候需要删除bufferpool中对应的AHI）
block= buf_page_get_gen(xxx,BUF_PEEK_IF_IN_POOL,xxxx) /*mode=BUF_PEEK_IF_IN_POOL,bufferpool=12中不存在不去磁盘获取*/
block= buf_page_hash_get_low(buf_pool, page_id)
if (block)
btr_search_drop_page_hash_index(block)
耗时2）fil_delete_tablespace（用来释放bufferpool页面、删除.ibd磁盘文件）
buf_LRU_remove_pages(释放bufferpool页面)
buf_flush_dirty_pages(移除脏页)
os_file_delete_func(删除ibd文件)
--------------870G冷表删除慢原因--------------
1.需要释放AHI
2.需要释放extent时加载磁必要的盘页面到bufferpool
3.需要删除.ibd文件
继续深入第一问：删除.ibd文件慢原因
答案
saas盘，磁盘性能太差，删除.ibd需要占用大量时间，做硬链接删除表和手动删除磁盘文件用时：
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/f_biddb4.ful.20191118121600/font_search_log]# ln logdata.ibd logdata.ibd_bak
mysql> drop table logdata;
Query OK, 0 rows affected (25.24 sec)
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/f_biddb4.ful.20191118121600/font_search_log]# time rm -f logdata.ibd_bak
real 0m29.764s
user 0m0.000s
sys 0m28.475s
继续深入第二问：释放AHI慢原因
答案
870G表对应页面57016320个页面，每个页面都会调用一次，有些版本有if (ahi)这个判断，可以关闭AHI减少这部分时间，有些版本都没有if (ahi)这个判断，所以无论是否关闭AHI都会调用，释放AHI大概思路为，如果页面在bufferpool且被hash则释放该AHI，否则直接返回,源码为
if (ahi) { /*innodb_adaptive_hash_index如果为on会调用，否则跳过，注意有些版本是没有if(ahi)这个判断的，所以关闭AHI是否会生效取决于版本，percona5.6.44是没有的，oracle-mysql5.7.17是有的*/
for (i = 0; i < FSP_EXTENT_SIZE; i++) {
if (!xdes_mtr_get_bit(descr, XDES_FREE_BIT, i, mtr)) {
/* Drop search system page hash index
if the page is found in the pool and
is hashed */
btr_search_drop_page_hash_when_freed(page_id_t(space,first_page_in_extent + i),page_size);
}
}
}
继续深入第三问：释放extent时加载哪些磁盘页面到bufferpool
探索drop表会将哪些页面加载到bufferpool
1)修改storage/innobase/buf/buf0rea.cc源码在buf_read_page_low方法处添加sql_print_information打印被加载到bufferpool中页面对应的space_id(表空间id),及page_no(页面号)
......
buf_page_t* bpage;
ulint wake_later;
ibool ignore_nonexistent_pages;
sql_print_information("===============================buf_read_page_low,table_id:%lu,page_no:%lu",space,offset);
*err = DB_SUCCESS;
......
2）重启，做硬连接，drop 表
3）收集对应的页面号
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# grep 'buf_read_page_low,table_id' mysql-error.log|less
2020-02-05 16:23:53 11264 [Note] ===============================buf_read_page_low,table_id:78,page_no:0
2020-02-05 16:23:53 11264 [Note] ===============================buf_read_page_low,table_id:78,page_no:2
2020-02-05 16:23:53 11264 [Note] ===============================buf_read_page_low,table_id:78,page_no:18808832
2020-02-05 16:23:53 11264 [Note] ===============================buf_read_page_low,table_id:78,page_no:41009152
2020-02-05 16:23:53 11264 [Note] ===============================buf_read_page_low,table_id:78,page_no:35012608
2020-02-05 16:23:53 11264 [Note] ===============================buf_read_page_low,table_id:78,page_no:17989632
2020-02-05 16:23:53 11264 [Note] ===============================buf_read_page_low,table_id:78,page_no:55115776
4）分析这些页面
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# innodb_space --system-space-file ibdata1 --table-name font_search_log/logstat -p 0 page-dump|grep 'type=>'|less
:type=>:FSP_HDR,（表空间的第1个页面）
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# innodb_space --system-space-file ibdata1 --table-name font_search_log/logstat -p 2 page-dump|grep 'type=>'|less
:type=>:INODE,（表空间的第3个页面）
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# innodb_space --system-space-file ibdata1 --table-name font_search_log/logstat -p 18808832 page-dump|grep 'type=>'|less
:type=>:XDES,（表空间每256个区一个组，该组第一个区的第一个页面，用来登记该组256个区的属性）
.........（全部为XDES,共3448个）
表空间第一个组的XES在FSP_HDR里面，所有XES数量对应磁盘文件大小总共为3449个(INODE与FSP_HDR在一个区)共占用3449*256/1024=862.25G，与.ibd文件非常接近
答案
drop表时会将表空间第一个页面，第三个页面，以及每个组（256个extent一个组）的第一个页面加载到bufferpool，数据页和索引页不会加载到bufferpool
研究过程中新发现
1.做硬连接删除表的时候，如果手动先删除索引，然后在删除表，则非常快
2.删除索引的时候，删除第一个索引很慢，后续的索引删除都很快
做硬链接，先删除索引，在drop表测试结果如下
[root@l-xxxxxxx.xxxx.xxxx /home/q/mysql/multi/3307/f_biddb4.ful.20191118121600/font_search_log]# ln logdata.ibd logdata.ibd_bak
mysql> alter table logdata drop index idx_uid;
Query OK, 0 rows affected (20.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table logdata drop index idx_create_at;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table logdata drop index idx_rule;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table logdata drop index idx_page;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table logdata drop index idx_urlfrom;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table logdata drop index idx_global_created;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop table logdata;
Query OK, 0 rows affected (2.65 sec)
探索为什么drop第一个索引慢，drop后续索引快（索引大小对结果影响不是很大）
1.上面drop表已经知道会有3449个页面加载到bufferpool
2.依次drop索引，然后观察bufferpool数据页面，并统计加载到磁盘的页面号
mysql> alter table logdata drop index idx_uid;
Query OK, 0 rows affected (23.24 sec)
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# grep 'buf_read_page_low,table_id' mysql-error.log|wc -l
3418(删除第一个索引就已经和drop整张表载入bufferpool的页面3449数量相近了了)
mysql> alter table logdata drop index idx_create_at;
Query OK, 0 rows affected (0.23 sec)
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# grep 'buf_read_page_low,table_id' mysql-error.log|wc -l
3436
mysql> alter table logdata drop index idx_rule;
Query OK, 0 rows affected (0.12 sec)
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# grep 'buf_read_page_low,table_id' mysql-error.log|wc -l
3445
mysql> alter table logdata drop index idx_page;
Query OK, 0 rows affected (0.13 sec)
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# grep 'buf_read_page_low,table_id' mysql-error.log|wc -l
3448
mysql> alter table logdata drop index idx_urlfrom;
Query OK, 0 rows affected (0.07 sec)
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# grep 'buf_read_page_low,table_id' mysql-error.log|wc -l
3449
mysql> alter table logdata drop index idx_global_created;
Query OK, 0 rows affected (1.07 sec)
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# grep 'buf_read_page_low,table_id' mysql-error.log|wc -l
3450
[root@l-xxxxx.xx.xx/home/q/mysql/multi/3307/f_biddb4.ful.20191118121600/font_search_log]# ln logdata.ibd logdata.ibd_bak
mysql> drop table logdata;
Query OK, 0 rows affected (4.41 sec)
[root@l-xxxxx.xx.xx /home/q/mysql/multi/3307/log]# grep 'buf_read_page_low,table_id' mysql-error.log|wc -l
3453(新增加的3个是与改系统表有关的3个页面)
-----drop第一个索引慢，drop后续索引快的原因-----
在删除第一个索引的时候就已经将绝大多数的描述符页面(INODE,XDES)加载到bufferpool了，所以后续删除索引及表就变得非常快
最后，此次学习得出的几个结论
一、知道了删除大表耗时的几个位置在drop表时我们就可以对其今进行优化
1.优化删除.ibd慢：做硬连接删除，然后使用Linux truncate命令逐步缩小.ibd_bak文件进行删除
2.优化释放AHI慢：此处暂时没有想到好的方法，网上有些人说临时关闭AHI，这个关闭可以在线通过innodb_adaptive_hash_index=off立即清空AHI，这样就不会清理AHI了，但是所有页面是否要循环取决于版本(percona-5.6.44版本没有if (ahi)这个判断，所以无论是否关闭AHI都会循环调用,oracle-mysql5.7.17这个版本有if (ahi)这个判断，所以关闭AHI是可以生效的，其他版本是否有这个判断，请自行判断)，这个参数在线关闭是否会影响线上环境，需要根据各自的业务和环境来进行选择
3.优化释放索引段加载描述符页面到bufferpool慢，还是用SSD来解决吧，普通磁盘性能实在是差差差...
4.drop表会清理bufferpool脏页，但是不会清理bufferpool数据页，所以对于热点表还是先采取rename方式，在进行删除操作
二、删除索引并不会释放表空间，这部分索引只是还给了表的free列表，并没有清理
三、drop表或者删除索引的时候只会将XDES描述符页面(每256个区加载一个XDES页面)加载到bufferpool，在释放extent过程中通过xdes_init方法来重新初始化该XDES描述符内对应区的属性，将其置为干净可用状态，在整个过程中数据和索引页面不会加载到bufferpool
四、drop表期间会持有row_mysql_lock_data_dictionary数据字典锁，这个锁是一个全局锁，对于后续操作数据字典的都会阻塞，例如create、show create、select等操作，被阻塞的SQL状态表现为Opening tables，drop表这个SQL的状态为checking permissions,这个锁会在删除表时获取直到将.ibd文件删除才会用row_mysql_unlock_data_dictionary释放，另外truncate table也会走这个持这个锁的流程
五、看了truncate流程后，对于允许drop或者truncate的表，优选drop,原因如下:
1.truncate table与mysql版本有很大关系，版本不同影响很大,5.6(本人percona-5.6.29) truncate是真正的删除.ibd文件然后重建，5.7(本人percona-5.7.26)是释放所有的索引树然后重用该ibd文件,也就是说5.6可以采用硬连接来消除删除.ibd这段时间，但是5.7不行，因为是重用该.ibd文件，硬连接空间会随着.ibd一同释放
2.truncate table不管5.6还是5.7都会立即清理bufferpool的数据页和脏页，而drop table只立即释放脏页面
3.由上可知如果业务允许最好采用drop+create清理表，drop表可以通过硬连接+bufferpool数据页后台清理来降低持有数据字典这个全局锁的时间，进而降低对业务的影响
附一个saas盘、nvme盘drop表对比
image.png
nvme秒删870G文件也与这个磁盘剩余空间充足(5.8T盘)，及当时无数据写入有关，实际生产环境可能没这么快，需要考虑SSD写放大的场景
