MySQL 테이블 단편화

일반적으로 데이터베이스 사용자는 DELETE 문을 사용한 테이블 단편화에 대하여 알고 있습니다. 대량 삭제를 수행할 때마다 대부분의 경우 디스크 공간을 회수하기 위해 항상 테이블을 다시 작성합니다. 그러나 DELETE만 테이블 단편화를 일으킬 수 있지 않습니다. INSERT 문으로 테이블 조각화가 어떻게 발생하는지 확인 해 보겠습니다.

MySQL에는 두 가지 종류의 단편화가 있습니다.

• 테이블 내에서 일부 InnoDB 페이지가 완전히 비어 있는 단편화.
• 일부 InnoDB 페이지가 완전히 채워지지 않은 조각화(페이지에 여유 공간이 있음).

INSERT를 사용한 테이블 조각화에는 세 가지 사례가 있습니다.

• INSERT ROLLBACK
• INSERT 실패
• 페이지 분할을 통한 단편화

 

테스트 환경

아래와 같이 테스트 환경을 만들었습니다.

• DB: test
• Tables : frag, ins_frag, frag_page_spl
• Table Size: 2G

사례 1: INSERT ROLLBACK

처음에는 " ins_frag " 라는 새 테이블을 만듭니다 . 그런 다음 BEGIN을 사용하여 트랜잭션을 생성하고 아래와 같이 " frag " 테이블에서 " ins_frag " 테이블로 데이터를 복사합니다 .

mysql> create table ins_frag like frag;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ins_frag select * from frag;
Query OK, 47521280 rows affected (3 min 7.45 sec)
Records: 47521280  Duplicates: 0  Warnings: 0

#Linux shell
sakthi-3.2# ls -lrth
total 8261632
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 03:00 ins_frag.ibd

위에서 INSERT가 실행되었지만 INSERT를 커밋/롤백하지 않았습니다. 두 테이블 모두 2GB 의 디스크 공간을 차지했습니다 . 

이제 INSERT를 ROLLBACK 하겠습니다.

mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
| 47521280 |
+----------+
1 row in set (1.87 sec)

mysql> rollback;
Query OK, 0 rows affected (5 min 45.21 sec)

mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


#Linux shell
sakthi-3.2# ls -lrth
total 8261632
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 03:09 ins_frag.ibd

INSERT를 롤백한 후 " ins_frag " 테이블 은 여전히 ​​동일한 2GB 디스크 공간을 차지합니다 . MySQL 클라이언트를 통해 단편화 공간을 찾아봅니다.

mysql> 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_schema='test' and table_name='ins_frag';
+----------+----------+-------+----------+
| DATABASE | TABLE.   | TOTAL | DATAFREE |
+----------+----------+-------+----------+
| test     | ins_frag | 0.00G | 1.96G    |
+----------+----------+-------+----------+
1 row in set (0.01 sec)

INSERT를 롤백하면 단편화가 됩니다. 디스크 공간을 회수하려면 테이블을 다시 작성해야 합니다.

mysql> alter table ins_frag engine=innodb;
Query OK, 0 rows affected (2.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

#Linux shell

sakthi-3.2# ls -lrth
total 4131040
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   112K Jun 17 03:11 ins_frag.ibd

 

사례 2: INSERT 실패

테스트를 위해 두 개의 MySQL 클라이언트 세션(세션 1 및 세션 2)을 만들었습니다. 

세션 1에서 트랜잭션 내에서 동일한 INSERT 문을 실행하고 있습니다. 그러나 이번에는 세션 2에서 INSERT 쿼리를 중단하고 종료했습니다.

세션 1

#Linux shell

sakthi-3.2# ls -lrth
total 4131040
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   112K Jun 17 04:02 ins_frag.ibd

#MySQL shell

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ins_frag select * from frag;   #is running

세션 2

mysql> pager grep -i insert ; show processlist;
PAGER set to 'grep -i insert'
| 33 | root            | localhost | percona | Query   |    14 | executing              | insert into ins_frag select * from frag |
4 rows in set (0.00 sec)

mysql> kill 33;
Query OK, 0 rows affected (0.00 sec)

INSERT가 중단되어 실패했습니다.

세션 1

mysql> insert into ins_frag select * from frag;
ERROR 2013 (HY000): Lost connection to MySQL server during query

#Linux shell

sakthi-3.2# ls -lrth
total 4591616
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   212M Jun 17 04:21 ins_frag.ibd

#MySQL shell

mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.10 sec)

INSERT가 완료되지 않았으며 테이블에 데이터가 없습니다. 그러나 여전히 테이블 .ibd 파일은 2GB 까지 늘어났습니다. MySQL 클라이언트를 통해 단편화 공간을 살펴보자.

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_schema='test' and table_name='ins_frag';
+----------+----------+---------+----------+
| DATABASE | TABLE    | TOTAL   | DATAFREE |
+----------+----------+---------+----------+
| test     | ins_frag | 0.03M   | 210.56M  |
+----------+----------+---------+----------+
1 row in set (0.01 sec)

테이블에 단편화된 공간이 있고 공간을 회수하기 위해 테이블을 다시 작성해야 함을 보여줍니다.

mysql> alter table ins_frag engine='innodb';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#Linux shell

sakthi-3.2# ls -lrth
total 4131040
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   112K Jun 17 04:32 ins_frag.ibd

 

사례 3: 페이지 분할을 통한 단편화

우리는 내부적으로 InnoDB 레코드가 InnoDB 페이지에 저장된다는 것을 알고 있습니다. 각 페이지 크기는 기본적으로 16K이지만 페이지 크기를 변경할 수 있는 옵션이 있습니다.

InnoDB 페이지에 새 레코드 또는 인덱스 항목을 수용할 공간이 충분하지 않은 경우 두 페이지로 분할되어 각각 약 50%가 차게 됩니다. 즉, rollback이나 delete가 없는 insert 전용 워크로드의 경우에도 평균 페이지 사용률이 75%에 불과할 수 있으므로 이러한 종류의 내부 페이지 조각화에서는 25%의 손실이 발생할 수 있습니다.

인덱스가 정렬 방식으로 구축되면 혼잡이 더 심해지며 테이블에 인덱스의 임의 위치로 이동하는 삽입이 많으면 페이지 분할이 발생합니다.

내림차순 인덱스가 있는 테이블을 만들었습니다.

mysql> show create table frag_page_spl\G
*************************** 1. row ***************************
Table: frag_page_spl
Create Table: CREATE TABLE `frag_page_spl` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(16) DEFAULT NULL,
`messages` varchar(600) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_spl` (`messages` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.07 sec)

INFORMATION_SCHEMA.INNODB_METRICS 테이블에서 페이지 분할 활동을 모니터링할 수 있습니다. 이를 위해서는 InnoDB 모니터를 활성화해야 합니다.

mysql> SET GLOBAL innodb_monitor_enable=all;
Query OK, 0 rows affected (0.09 sec)

그런 다음 6개의 병렬 스레드로 INSERT를 무작위로 진행하는 스크립트를 수행 하였습니다.

mysql> 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_schema='test' and table_name='frag_page_spl';
+----------+---------------+----------+----------+
| DATABASE | TABLE.        | TOTAL    | DATAFREE |
+----------+---------------+----------+----------+
| test     | frag_page_spl | 2667.55M | 127.92M  |
+----------+---------------+----------+----------+
1 row in set (0.00 sec)

INNODB_METRICS에서 페이지 분할 카운터가 증가한 것을 볼 수 있습니다. 127.92MB 의 단편화를 생성한 52186 개의 페이지 분할 작업이 발생 했음을 보여줍니다 .

분할 페이지가 생성되면 뒤로 이동할 수 있는 유일한 방법은 생성된 페이지를 병합 임계값 아래로 떨어뜨리는 것입니다. 이 경우 InnoDB는 병합 작업으로 분할 페이지에서 데이터를 이동합니다. MERGE_THRESHOLD는 테이블 및 특정 인덱스에 대해 구성할 수 있습니다.

데이터를 재구성하는 다른 방법은 테이블을 OPTIMIZE하는 것입니다. 테이블 OPTIMIZE는 매우 무겁고 긴 프로세스일 수 있지만 너무 많은 페이지가 단편화 되어 있는 상황에서 복구하는 유일한 방법입니다.