批量插入回滚或插入失败导致的MySQL表碎片
2020-11-25 15:57 abce 阅读(999) 评论(0) 编辑 收藏 举报
1.表中的页是完全空闲的
2.表中的页没有被完全填充,还有部分空闲空间
有三种情况下,insert操作会导致表碎片:
1.带有rollback的insert
2.失败了的insert操作
3.页分裂导致的碎片
测试环境:Server version: 5.7.23-log MySQL Community Server (GPL)
表:frag、ins_frag、frag_page_spl
> create table ins_frag like frag; Query OK, 0 rows affected (0.01 sec) > begin; Query OK, 0 rows affected (0.00 sec) > insert into ins_frag select * from frag; Query OK, 5964924 rows affected (2 min 23.04 sec) Records: 5964924 Duplicates: 0 Warnings: 0 > # ll -ltrh |grep frag -rw-r----- 1 mysql mysql 29K Nov 8 09:38 frag.frm -rw-r----- 1 mysql mysql 29K Nov 8 09:41 ins_frag.frm -rw-r----- 1 mysql mysql 2.3G Nov 8 09:46 frag.ibd -rw-r----- 1 mysql mysql 2.3G Nov 8 09:48 ins_frag.ibd
到此,已经执行了insert操作,但是还没有提交或回滚insert操作。表占用了2.3G的磁盘空间。
现在,开始回滚上面的insert操作。
> select count(*) from ins_frag; +----------+ | count(*) | +----------+ | 5964924 | +----------+ 1 row in set (1.08 sec) > rollback; Query OK, 0 rows affected (2 min 27.15 sec) > select count(*) from ins_frag; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) > # ll -ltrh |grep frag -rw-r----- 1 mysql mysql 29K Nov 8 09:38 frag.frm -rw-r----- 1 mysql mysql 29K Nov 8 09:41 ins_frag.frm -rw-r----- 1 mysql mysql 2.3G Nov 8 09:46 frag.ibd -rw-r----- 1 mysql mysql 2.3G Nov 8 10:01 ins_frag.ibd
回滚结束后,表ins_frag仍然占用了2.3GB的磁盘空间。
> SELECT -> table_schema AS 'DATABASE', -> TABLE_NAME AS 'TABLE', -> CONCAT ( ROUND( ( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2 ), 'G' ) 'TOTAL', -> CONCAT ( ROUND( data_free / ( 1024 * 1024 * 1024 ), 2 ), 'G' ) 'DATAFREE' -> FROM -> information_schema.TABLES -> WHERE -> TABLE_NAME = 'ins_frag'; +----------+----------+-------+----------+ | DATABASE | TABLE | TOTAL | DATAFREE | +----------+----------+-------+----------+ | ysoap | ins_frag | 2.18G | 2.23G | +----------+----------+-------+----------+ 1 row in set (0.00 sec) >
说明insert回滚后,产生了碎片。
现在我们重构这个表,释放空间。
> alter table ins_frag engine=innodb; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 > # ll -ltrh |grep frag -rw-r----- 1 mysql mysql 29K Nov 8 09:38 frag.frm -rw-r----- 1 mysql mysql 2.3G Nov 8 09:46 frag.ibd -rw-r----- 1 mysql mysql 29K Nov 8 10:16 ins_frag.frm -rw-r----- 1 mysql mysql 128K Nov 8 10:16 ins_frag.ibd
案例2:失败的insert操作
在会话1中,开启一个事务执行insert操作。但是会在会话2中kill掉会话1。
会话1:
# ll -ltrh |grep frag -rw-r----- 1 mysql mysql 29K Nov 8 09:38 frag.frm -rw-r----- 1 mysql mysql 2.3G Nov 8 09:46 frag.ibd -rw-r----- 1 mysql mysql 29K Nov 8 10:16 ins_frag.frm -rw-r----- 1 mysql mysql 128K Nov 8 10:16 ins_frag.ibd > begin; Query OK, 0 rows affected (0.00 sec) > insert into ins_frag select * from frag; #运行过程中
会话2将会话1杀掉:
> pager grep -i insert ; show processlist; PAGER set to 'grep -i insert' | 1603454 | root | localhost | ysoap | Query | 5 | Sending data | insert into ins_frag select * from frag | 16 rows in set (0.00 sec) > kill 1603454; Query OK, 0 rows affected (0.00 sec) >
回到会话1
> insert into ins_frag select * from frag; ERROR 2013 (HY000): Lost connection to MySQL server during query > select count(*) from ins_frag; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) > # ll -ltrh |grep frag -rw-r----- 1 mysql mysql 29K Nov 8 09:38 frag.frm -rw-r----- 1 mysql mysql 2.3G Nov 8 09:46 frag.ibd -rw-r----- 1 mysql mysql 29K Nov 8 10:16 ins_frag.frm -rw-r----- 1 mysql mysql 1.2G Nov 8 10:28 ins_frag.ibd
insert操作被中途kill了。表中没有数据。但是,磁盘上还是占用了1.2GB的物理空间。
> SELECT -> table_schema AS 'DATABASE', -> TABLE_NAME AS 'TABLE', -> CONCAT ( ROUND( ( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2 ), 'G' ) 'TOTAL', -> CONCAT ( ROUND( data_free / ( 1024 * 1024 * 1024 ), 2 ), 'G' ) 'DATAFREE' -> FROM -> information_schema.TABLES -> WHERE -> TABLE_NAME = 'ins_frag'; +----------+----------+-------+----------+ | DATABASE | TABLE | TOTAL | DATAFREE | +----------+----------+-------+----------+ | ysoap | ins_frag | 1.04G | 1.15G | +----------+----------+-------+----------+ 1 row in set (0.01 sec) >
现在我们再次重构这个表,释放空间。
> alter table ins_frag engine='innodb'; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 > # ll -ltrh |grep frag -rw-r----- 1 mysql mysql 29K Nov 8 09:38 frag.frm -rw-r----- 1 mysql mysql 2.3G Nov 8 09:46 frag.ibd -rw-r----- 1 mysql mysql 29K Nov 8 10:34 ins_frag.frm -rw-r----- 1 mysql mysql 128K Nov 8 10:34 ins_frag.ibd
案例3:页分裂导致的碎片
在内部,InnoDB记录存储在InnoDB页中。默认情况下,每个页大小是16K,但是可以选择更改页面大小。
如果InnoDB页没有足够的空间容纳新的记录或索引条目,它将被分成两个页面,每个页大约有50%的空间是满的。这意味着,即使对于只有插入的工作负载,没有回滚或删除,最终也可能只有75%的平均页面利用率——因此这种内部页面碎片的损失为25%。
索引是通过排序构建的,如果表有很多插入到索引中的随机位置,就会导致页分割。
为了做一个实验,我创建了一个具有排序索引(降序)的表:
> show create table frag_page_spl\G *************************** 1. row *************************** Table: frag_page_spl Create Table: CREATE TABLE `frag_page_spl` ( `ID` varchar(64) NOT NULL, `TYPE` varchar(255) DEFAULT NULL, `TIME` datetime(3) NOT NULL, `USER_ID` varchar(255) DEFAULT NULL, `TASK_ID` varchar(64) DEFAULT NULL, `PINST_ID` varchar(64) DEFAULT NULL, `ACTION` varchar(255) DEFAULT NULL, `MESSAGE` varchar(4000) DEFAULT NULL, `FULL_MSG` longblob, PRIMARY KEY (`ID`), KEY `idx_pinstid` (`PINST_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
可以通过INFORMATION_SCHEMA.INNODB_METRICS监控表的页分裂活动。不过需要开启innodb monitor。
> set global innodb_monitor_enable=all;
创建6个并发线程,随机插入数据,完成后查看:
> select name,count,type,status,comment from information_schema.innodb_metrics where name like '%index_page_spl%'\G *************************** 1. row *************************** name: index_page_splits count: 52186 type: counter status: enabled comment: Number of index page splits 1 row in set (0.05 sec) mysql> SELECT -> table_schema as 'DATABASE', -> table_name as 'TABLE', -> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') 'TOTAL', -> CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'M') 'DATAFREE' -> FROM information_schema.TABLES -> where table_name='frag_page_spl'; +----------+---------------+----------+----------+ | DATABASE | TABLE. | TOTAL | DATAFREE | +----------+---------------+----------+----------+ | percona | frag_page_spl | 2667.55M | 127.92M | +----------+---------------+----------+----------+ 1 row in set (0.00 sec)
从innodb_metrics来看,我们可以看到页面分裂计数器增加了。输出显示发生了52186个页分割操作,创建了127.92 MB的碎片。
一旦创建了页分裂,惟一的方法就是将创建的页面降至合并阈值以下。当这种情况发生时,InnoDB通过合并操作将数据从分裂页面中移出。MERGE_THRESHOLD对于表和特定的索引是可配置的。
另一种重新组织数据的方法是optimize表。这可能是一个代价昂贵和漫长的过程,但通常也处理太多页面位于稀疏区域的情况中唯一方法。