批量插入回滚或插入失败导致的MySQL表碎片

2020-11-25 15:57 abce 阅读(1039) 评论(0) 编辑收藏举报

一般大家都知道，delete操作可以引起表碎片问题。但引起表碎片的并不仅仅只有delete操作。这里将演示一下由insert操作引起的表碎片。

在MySQL中，有两种碎片：

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

案例1：带有rollback的insert

> 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表。这可能是一个代价昂贵和漫长的过程，但通常也处理太多页面位于稀疏区域的情况中唯一方法。

刷新页面返回顶部

abce don't dream it , be it

批量插入回滚或插入失败导致的MySQL表碎片

About