MySQL drop/truncatre 大表分析及解决方案

2024-06-03 15:52| 来源: 网络整理| 查看: 265

课前知识 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需要占用大量时间，做硬链接删除表和手动删除磁盘文件用时： [[email protected] /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) [[email protected] /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）收集对应的页面号 [[email protected] /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）分析这些页面 [[email protected] /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个页面） [[email protected] /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个页面） [[email protected] /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表测试结果如下 [[email protected] /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) [[email protected] /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) [[email protected] /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) [[email protected] /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) [[email protected] /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) [[email protected] /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) [[email protected] /home/q/mysql/multi/3307/log]# grep 'buf_read_page_low,table_id' mysql-error.log|wc -l 3450 [[email protected]/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) [[email protected] /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.truncate table 时会扫lru列表中所有的页面，期间会持有全局锁，持锁时间不仅与表大小有关，也与当前数据库lru中数据页面数量有关，lru中页面越多，持锁时间越长，即便是一个空表也会有这个过程导致夯死数据库，lru大小不要与bufferpool大小直接挂钩，bufferpool大未必lru列表大，lru列表是随着数据访问量逐步增加的，所以评估一张表truncate耗时可以依据innodb status中的LRU len长度以及表的.ibd文件来判断。 4.由上可知如果业务允许最好采用drop+create清理表，drop表可以通过硬连接+bufferpool数据页后台清理来降低持有数据字典这个全局锁的时间，进而降低对业务的影响附一个saas盘、nvme盘drop表对比 image.png

nvme秒删870G文件也与这个磁盘剩余空间充足(5.8T盘)，及当时无数据写入有关，实际生产环境可能没这么快，需要考虑SSD写放大的场景

mysql8.0优化:

https://mysqlserverteam.com/a-250x-improvement-to-tablespace-truncation-in-mysql-server-8-0-23/

【本文地址】

公司简介

联系我们