테스트 배경
특정 RDS에서 아래 쿼리가 생각보다 너무 느려서 살펴보던 중 row_format = COMPACT(MySQL 5.6 default) 인 것을 확인하였습니다 (MySQL 5.7 default DYNAMIC)
row_format은 데이터를 저장할 때 페이지를 어떻게 사용하느냐, 물리적으로 어떻게 저장할 것이냐 에 대한 설정이기 때문에 성능에 큰 영향을 줄 수 있는 파라미터로,
이 RDS는 row_format = COMPACT를 사용하고 있기 때문에 쿼리가 불필요하게 느린 것이라고 판단하여 (아래 그림 참고)
그렇다면 각각 row_format이 COMPACT / DYNAMIC일 때 실제로 어떻게 저장되고 어떻게 차이가 나는지 확인해보기로 했습니다.
compact 동작방식
- text ,lob, varchar 큰 데이터가 들어오면 prefix 768 bytes만 같은 레코드에 저장하고 나머지는 off-page에 저장하게 됨
- 같은 page에 큰 데이터의 prefix를 768bytes나 할당하기 때문에 그만큼 1개 data page에 많은 레코드를 담을 수 없게 되어 필요한 data page를 많이 요구하게됨
- 이는 곧 I/O 증가로 이어짐 ==> 이런 점 때문에 compact를 사용하는 billing의 쿼리 성능이 느린 것으로 추측
dynamic 동작방식
- compact와 달리 큰 값에 대해 20bytes의 포인터만 저장하고, 데이터는 off page에 저장하기 때문에 훨씬 효율적으로 큰 데이터를 저장할 수 있음
문제의 쿼리
select
test1.pkey as pkey1_3_0_,
.
.
.
.
test2.test2_seq as pay_deta2_5_0__
from test1 test1
left outer join test2 test2 on test1.pkey = test2.pkey and test1.pay_seq = test2.pay_seq
where
test1.pkey in (0, 20230902, 20230903)
and test2.pkey in (0, 20230902, 20230903)
and test1.register_datetime >= '2023-09-03 21:00:00'
and test1.register_datetime < '2023-09-03 22:00:00'
order by test1.register_datetime asc;
+----+-------------+--------------+---------------------------+-------+-------------------------------+-----------------------------+---------+--------------------------------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+---------------------------+-------+-------------------------------+-----------------------------+---------+--------------------------------+--------+----------+-----------------------+
| 1 | SIMPLE | test1 | forever,20230902,20230903 | range | PRIMARY,IDX_REGISTER_DATETIME | IDX_REGISTER_DATETIME | 6 | NULL | 457581 | 30.00 | Using index condition |
| 1 | SIMPLE | test2 | forever,20230902,20230903 | ref | IDX_test2_PAY_SEQ_PKEY | IDX_test2_PAY_SEQ_PKEY | 114 | tt.test1.pay_seq,tt.test1.pkey | 1 | 100.00 | NULL |
+----+-------------+--------------+---------------------------+-------+-------------------------------+-----------------------------+---------+--------------------------------+--------+----------+-----------------------+
2 rows in set, 1 warning (0.06 sec)
mysql> show table status like 'test2';
+------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+--------------------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+--------------------------------+---------+
| test2 | InnoDB | 10 | Compact | 406171337 | 812 | 330033004544 | 0 | 99371188224 | 342884352 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | |partitioned | |
+------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+--------------------------------+---------+
mysql> show table status like 'test1';
+------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| test1 | InnoDB | 10 | Compact | 308351258 | 390 | 120404197376 | 0 | 110880571392 | 535822336 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | partitioned | |
+------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
=> row_Format이 compact 인 것을 제외하면 플랜이나 그 외의 것들은 특이사항이 없음
테스트 결과
- 단순히 text , varchar big size 데이터가 들어온다고 모두 위 compact / dynamic 그림처럼 off-page 방식으로 동작하는 것이 아니었다
- 특히 compact 타입일 땐 768bytes보다 크면 무조건 off-page 방식, dynamic일 땐 40bytes 이상이면 off-page 방식인 줄 알았으나 innodb page 16KB 기준, 8KB이상의 레코드가 저장될 때에나 위처럼 동작하게 된다
- 문제의 RDS의 경우 varchar(4000) 등 크게 할당된 varchar컬럼이 있었고 실제 데이터도 1KB이상이었으나, 하나의 레코드가 8KB 이상 되는 것은 없었기 때문에 off-page 저장되는 것이 없었고 이로 인해 COMPACT, DYNAMIC 차이가 없었음
- 문제의 RDS에선 원인이 아니었으나, 큰 데이터를 저장할 때 COMPACT, DYNAMIC 은 data page 개수 등에서 분명히 차이가 있으므로 무조건 DYNAMIC을 사용하자
테스트
문제의 RDS의 데이터 중 test2 테이블을 덤프받아 native mysql에 각각 row_format compact / dynamic으로 저장한 뒤
innodb_ruby 를 활용하여 각 row_format별로 데이터 페이지의 개수 차이를 확인해보자
-rw-r----- 1 root root 17016291328 Sep 18 07:31 test2_compact#p#forever.ibd
-rw-r----- 1 root root 5490343936 Sep 18 07:49 test2_compact#p#20230903.ibd
-rw-r----- 1 root root 17016291328 Sep 18 09:34 test2_dynamic#p#forever.ibd
-rw-r----- 1 root root 5490343936 Sep 18 09:52 test2_dynamic#p#20230903.ibd
-rw-r--r-- 1 root root 17758233 Sep 18 11:21 compact_index.txt
-rw-r--r-- 1 root root 17758233 Sep 18 11:28 dynamic_index.txt
-rw-r--r-- 1 root root 455 Sep 18 11:50 compact_page.txt
-rw-r--r-- 1 root root 455 Sep 18 11:51 dynamic_page.txt
-rw-r--r-- 1 root root 27645 Sep 18 12:21 compact_region.txt
-rw-r--r-- 1 root root 27645 Sep 18 12:21 dynamic_region.txt
-rw-r--r-- 1 root root 1053 Sep 18 12:50 compact_space.txt
-rw-r--r-- 1 root root 1053 Sep 18 12:50 dynamic_space.txt
[root@831d3e1a52ea pg]# cat compact_page.txt
type count percent description
INDEX 316616 94.48 B+Tree index
ALLOCATED 18444 5.50 Freshly allocated
IBUF_BITMAP 21 0.01 Insert buffer bitmap
XDES 20 0.01 Extent descriptor
FSP_HDR 1 0.00 File space header
INODE 1 0.00 File segment inode
[root@831d3e1a52ea pg]# cat dynamic_page.txt
type count percent description
INDEX 316616 94.48 B+Tree index
ALLOCATED 18444 5.50 Freshly allocated
IBUF_BITMAP 21 0.01 Insert buffer bitmap
XDES 20 0.01 Extent descriptor
FSP_HDR 1 0.00 File space header
INODE 1 0.00 File segment inode
[root@831d3e1a52ea pg]# cat compact_space.txt
id name root fseg fseg_id used allocated fill_factor
38544 4 internal 3 706 800 88.25%
38544 4 leaf 4 216696 216736 99.98%
38545 5 internal 5 263 352 74.72%
38545 5 leaf 6 39502 45152 87.49%
38546 6 internal 7 245 286 85.66%
38546 6 leaf 8 31592 36128 87.44%
38547 7 internal 9 129 156 82.69%
38547 7 leaf 10 27516 31456 87.47%
[root@831d3e1a52ea pg]# cat dynamic_space.txt
id name root fseg fseg_id used allocated fill_factor
36544 4 internal 3 706 800 88.25%
36544 4 leaf 4 216696 216736 99.98%
36545 5 internal 5 263 352 74.72%
36545 5 leaf 6 39502 45152 87.49%
36546 6 internal 7 245 286 85.66%
36546 6 leaf 8 31592 36128 87.44%
36547 7 internal 9 129 156 82.69%
36547 7 leaf 10 27516 31456 87.47%
=> compact, dynamic 의 데이터 사이즈도 동일했고 page 개수, fill_factor 등등 모든 저장된 형태가 동일함
앞서 살펴본 그림과 매뉴얼의 내용대로라면
- COMPACT는 768만큼의 prefix 저장 후 off-page,
- DYNAMIC은 애초에 data page엔 20bytes만큼의 포인터만 저장 후 나머지는 모두 off-page에 저장하기 때문에
data page의 개수에 차이가 있어야하고, 저장되는 방식도 달라야하거늘 왜 동일한 것일까 ?
위 현상을 한참 찾아보다 MySQL 매뉴얼에서 아래의 충격적인 내용을 확인하였다… 이 내용이 왜 row_format 쪽 매뉴얼엔 없는 것인가,,,,
For for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings, the maximum row length is slightly less than half a database page size.
For example, the maximum row length is slightly less than 8KB for the default 16KB InnoDB page size.
For a 64KB innodb_page_size setting, the maximum row length is slightly less than 16KB.
If a row does not exceed the maximum row length, all of it is stored locally within the page.
If a row exceeds the maximum row length, variable-length columns are chosen for external off-page storage until the row fits within the maximum row length limit.
External off-page storage for variable-length columns differs by row format:
즉, row_format 에 따른 off-page 저장 방식의 차이는 Innodb page 16KB 기준 한 레코드가 8KB보다 클 때 발생한다는 것,,
그리고 문제의 테이블은 비록 compact format을 사용하지만 레코드 하나의 크기가 끽해야 최대 2KB미만이어서 위에서 확인해봤을 때 차이가 없었던 것이다
mysql> select max(length(approve_option_body)) from test2 where pkey in (0, 20230902, 20230903);
+----------------------------------+
| max(length(approve_option_body)) |
+----------------------------------+
| 1232 |
+----------------------------------+
1 row in set (2 min 40.98 sec)
mysql> select max(length(cancel_option_body)) from test2 where pkey in (0, 20230902, 20230903);
+---------------------------------+
| max(length(cancel_option_body)) |
+---------------------------------+
| 306 |
+---------------------------------+
1 row in set (21.51 sec)
그래서 레코드 8KB가 넘냐 안넘냐를 기준으로 테스트를 다시 진행해보았다
두번째 테스트 데이터 준비
### 레코드가 8KB보다 작을 때
mysql> use test;
Database changed
mysql> create table tb_compact
-> (
-> a int auto_increment primary key,
-> b varchar(10000)
-> ) charset=latin1 row_format=compact;
Query OK, 0 rows affected (0.04 sec)
mysql> create table tb_dynamic
-> (
-> a int auto_increment primary key,
-> b varchar(10000)
-> ) charset=latin1 row_format=dynamic;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tb_compact(b) values(REPEAT('a', 1000));
Query OK, 1 row affected (0.04 sec)
mysql>
mysql> insert into tb_dynamic(b) values(REPEAT('a', 1000));
Query OK, 1 row affected (0.04 sec)
### 레코드가 8KB보다 클 때
mysql> use test;
Database changed
mysql> create table tb_compact
-> (
-> a int auto_increment primary key,
-> b varchar(10000)
-> ) charset=latin1 row_format=compact;
Query OK, 0 rows affected (0.04 sec)
mysql> create table tb_dynamic
-> (
-> a int auto_increment primary key,
-> b varchar(10000)
-> ) charset=latin1 row_format=dynamic;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tb_compact(b) values(REPEAT('a', 10000));
Query OK, 1 row affected (0.04 sec)
mysql>
mysql> insert into tb_dynamic(b) values(REPEAT('a', 10000));
Query OK, 1 row affected (0.04 sec)
레코드가 8KB보다 작을 때
compact
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_compact space-index-pages-summary
page index level data free records
3 41 0 1025 15227 1
5 0 0 0 16384 0
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_compact -p 3 page-dump
#<Innodb::Page::Index:0x0000000000fafaf0>:
fil header:
#<struct Innodb::Page::FilHeader
checksum=217622452,
offset=3,
prev=nil,
next=nil,
lsn=2562511,
type=:INDEX,
flush_lsn=0,
space_id=23>
fil trailer:
#<struct Innodb::Page::FilTrailer checksum=217622452, lsn_low32=2562511>
page header:
#<struct Innodb::Page::Index::PageHeader
n_dir_slots=2,
heap_top=1145,
n_heap_format=32771,
n_heap=3,
format=:compact,
garbage_offset=0,
garbage_size=0,
last_insert_offset=128,
direction=:no_direction,
n_direction=0,
n_recs=1,
max_trx_id=0,
level=0,
index_id=41>
fseg header:
#<struct Innodb::Page::Index::FsegHeader
leaf=
<Innodb::Inode space=<Innodb::Space file="test/tb_compact.ibd", page_size=16384, pages=6>, fseg=2>,
internal=
<Innodb::Inode space=<Innodb::Space file="test/tb_compact.ibd", page_size=16384, pages=6>, fseg=1>>
sizes:
header 120
trailer 8
directory 4
free 15227
used 1157
record 1025
per record 1025.00
page directory:
[99, 112]
system records:
#<struct Innodb::Page::Index::SystemRecord
offset=99,
header=
#<struct Innodb::Page::Index::RecordHeader
length=5,
next=128,
type=:infimum,
heap_number=0,
n_owned=1,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=nil,
lengths=nil,
externs=nil>,
next=128,
data="infimum\x00",
length=8>
#<struct Innodb::Page::Index::SystemRecord
offset=112,
header=
#<struct Innodb::Page::Index::RecordHeader
length=5,
next=112,
type=:supremum,
heap_number=1,
n_owned=2,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=nil,
lengths=nil,
externs=nil>,
next=112,
data="supremum",
length=8>
garbage records:
records:
#<struct Innodb::Page::Index::UserRecord
type=:clustered,
format=:compact,
offset=128,
header=
#<struct Innodb::Page::Index::RecordHeader
length=8,
next=112,
type=:conventional,
heap_number=2,
n_owned=0,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=[],
lengths={"b"=>1000},
externs=[]>,
next=112,
key=
[#<struct Innodb::Page::Index::FieldDescriptor
name="a",
type="INT",
value=1,
extern=nil>],
row=
[#<struct Innodb::Page::Index::FieldDescriptor
name="b",
type="VARCHAR(10000)",
value=
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
extern=nil>],
sys=
[#<struct Innodb::Page::Index::FieldDescriptor
name="DB_TRX_ID",
type="TRX_ID",
value=1301,
extern=nil>,
#<struct Innodb::Page::Index::FieldDescriptor
name="DB_ROLL_PTR",
type="ROLL_PTR",
value=
#<struct Innodb::DataType::RollPointerType::Pointer
is_insert=true,
rseg_id=50,
undo_log=#<struct Innodb::Page::Address page=294, offset=272>>,
extern=nil>],
child_page_number=nil,
transaction_id=1301,
roll_pointer=
#<struct Innodb::DataType::RollPointerType::Pointer
is_insert=true,
rseg_id=50,
undo_log=#<struct Innodb::Page::Address page=294, offset=272>>,
length=1017>
=> 레코드 크기가 1KB로, 8KB보다 작을 땐 off-page가 생기지 않음 (아래 extern=nil 확인 )
대신 varchar(1000) 만큼을 모두 하나의 data page에 저장하는 모습임
key=
[#<struct Innodb::Page::Index::FieldDescriptor
name="a",
type="INT",
value=1,
extern=nil>],
row=
[#<struct Innodb::Page::Index::FieldDescriptor
name="b",
type="VARCHAR(10000)",
value=
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
extern=nil>],
dynamic
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_dynamic space-index-pages-summary
page index level data free records
3 42 0 1025 15227 1
5 0 0 0 16384 0
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_dynamic -p 3 page-dump
#<Innodb::Page::Index:0x0000000000fafc80>:
fil header:
#<struct Innodb::Page::FilHeader
checksum=2324395643,
offset=3,
prev=nil,
next=nil,
lsn=2571765,
type=:INDEX,
flush_lsn=0,
space_id=24>
fil trailer:
#<struct Innodb::Page::FilTrailer checksum=2324395643, lsn_low32=2571765>
page header:
#<struct Innodb::Page::Index::PageHeader
n_dir_slots=2,
heap_top=1145,
n_heap_format=32771,
n_heap=3,
format=:compact,
garbage_offset=0,
garbage_size=0,
last_insert_offset=128,
direction=:no_direction,
n_direction=0,
n_recs=1,
max_trx_id=0,
level=0,
index_id=42>
fseg header:
#<struct Innodb::Page::Index::FsegHeader
leaf=
<Innodb::Inode space=<Innodb::Space file="test/tb_dynamic.ibd", page_size=16384, pages=6>, fseg=2>,
internal=
<Innodb::Inode space=<Innodb::Space file="test/tb_dynamic.ibd", page_size=16384, pages=6>, fseg=1>>
sizes:
header 120
trailer 8
directory 4
free 15227
used 1157
record 1025
per record 1025.00
page directory:
[99, 112]
system records:
#<struct Innodb::Page::Index::SystemRecord
offset=99,
header=
#<struct Innodb::Page::Index::RecordHeader
length=5,
next=128,
type=:infimum,
heap_number=0,
n_owned=1,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=nil,
lengths=nil,
externs=nil>,
next=128,
data="infimum\x00",
length=8>
#<struct Innodb::Page::Index::SystemRecord
offset=112,
header=
#<struct Innodb::Page::Index::RecordHeader
length=5,
next=112,
type=:supremum,
heap_number=1,
n_owned=2,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=nil,
lengths=nil,
externs=nil>,
next=112,
data="supremum",
length=8>
garbage records:
records:
#<struct Innodb::Page::Index::UserRecord
type=:clustered,
format=:compact,
offset=128,
header=
#<struct Innodb::Page::Index::RecordHeader
length=8,
next=112,
type=:conventional,
heap_number=2,
n_owned=0,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=[],
lengths={"b"=>1000},
externs=[]>,
next=112,
key=
[#<struct Innodb::Page::Index::FieldDescriptor
name="a",
type="INT",
value=1,
extern=nil>],
row=
[#<struct Innodb::Page::Index::FieldDescriptor
name="b",
type="VARCHAR(10000)",
value=
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
extern=nil>],
sys=
[#<struct Innodb::Page::Index::FieldDescriptor
name="DB_TRX_ID",
type="TRX_ID",
value=1309,
extern=nil>,
#<struct Innodb::Page::Index::FieldDescriptor
name="DB_ROLL_PTR",
type="ROLL_PTR",
value=
#<struct Innodb::DataType::RollPointerType::Pointer
is_insert=true,
rseg_id=55,
undo_log=#<struct Innodb::Page::Address page=299, offset=272>>,
extern=nil>],
child_page_number=nil,
transaction_id=1309,
roll_pointer=
#<struct Innodb::DataType::RollPointerType::Pointer
is_insert=true,
rseg_id=55,
undo_log=#<struct Innodb::Page::Address page=299, offset=272>>,
length=1017>
=> 레코드가 1KB일 땐, COMPACT와 전혀 다르지 않다. 동일하게 하나의 data page에 모두 저장되는 모습
레코드가 8KB보다 클 때
compact
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_compact space-index-pages-summary
page index level data free records
3 41 0 813 15439 1
5 0 0 0 16384 0
=> 3번 page에 813 bytes의 레코드가 하나 있는 것으로 보임.
앞서 레코드 사이즈가 1KB일 땐 하나의 data page에 모두 저장되었는데 이번엔 813 bytes만 저장되었다 나머지 9200 bytes는 어디로 간것일까..
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_compact -p 3 page-dump
#<Innodb::Page::Index:0x0000000000fafaf0>:
fil header:
#<struct Innodb::Page::FilHeader
checksum=3289322324,
offset=3,
prev=nil,
next=nil,
lsn=2543164,
type=:INDEX,
flush_lsn=0,
space_id=23>
fil trailer:
#<struct Innodb::Page::FilTrailer checksum=3289322324, lsn_low32=2543164>
page header:
#<struct Innodb::Page::Index::PageHeader
n_dir_slots=2,
heap_top=933,
n_heap_format=32771,
n_heap=3,
format=:compact,
garbage_offset=0,
garbage_size=0,
last_insert_offset=128,
direction=:no_direction,
n_direction=0,
n_recs=1,
max_trx_id=0,
level=0,
index_id=41>
fseg header:
#<struct Innodb::Page::Index::FsegHeader
leaf=
<Innodb::Inode space=<Innodb::Space file="test/tb_compact.ibd", page_size=16384, pages=6>, fseg=2>,
internal=
<Innodb::Inode space=<Innodb::Space file="test/tb_compact.ibd", page_size=16384, pages=6>, fseg=1>>
sizes:
header 120
trailer 8
directory 4
free 15439
used 945
record 813
per record 813.00
page directory:
[99, 112]
system records:
#<struct Innodb::Page::Index::SystemRecord
offset=99,
header=
#<struct Innodb::Page::Index::RecordHeader
length=5,
next=128,
type=:infimum,
heap_number=0,
n_owned=1,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=nil,
lengths=nil,
externs=nil>,
next=128,
data="infimum\x00",
length=8>
#<struct Innodb::Page::Index::SystemRecord
offset=112,
header=
#<struct Innodb::Page::Index::RecordHeader
length=5,
next=112,
type=:supremum,
heap_number=1,
n_owned=2,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=nil,
lengths=nil,
externs=nil>,
next=112,
data="supremum",
length=8>
garbage records:
records:
#<struct Innodb::Page::Index::UserRecord
type=:clustered,
format=:compact,
offset=128,
header=
#<struct Innodb::Page::Index::RecordHeader
length=8,
next=112,
type=:conventional,
heap_number=2,
n_owned=0,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=[],
lengths={"b"=>788},
externs=["b"]>,
next=112,
key=
[#<struct Innodb::Page::Index::FieldDescriptor
name="a",
type="INT",
value=1,
extern=nil>],
row=
[#<struct Innodb::Page::Index::FieldDescriptor
name="b",
type="VARCHAR(10000)",
value=
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
extern=
#<struct Innodb::Field::ExternReference
space_id=23,
page_number=4,
offset=38,
length=9232>>],
sys=
[#<struct Innodb::Page::Index::FieldDescriptor
name="DB_TRX_ID",
type="TRX_ID",
value=1290,
extern=nil>,
#<struct Innodb::Page::Index::FieldDescriptor
name="DB_ROLL_PTR",
type="ROLL_PTR",
value=
#<struct Innodb::DataType::RollPointerType::Pointer
is_insert=true,
rseg_id=42,
undo_log=#<struct Innodb::Page::Address page=286, offset=272>>,
extern=nil>],
child_page_number=nil,
transaction_id=1290,
roll_pointer=
#<struct Innodb::DataType::RollPointerType::Pointer
is_insert=true,
rseg_id=42,
undo_log=#<struct Innodb::Page::Address page=286, offset=272>>,
length=805>
=> 3번 페이지를 page-dump로 확인해보니 드디어 b컬럼에 대해 externs=[“b”]> off-page가 발생한 것을 확인할 수 있음
header=
#<struct Innodb::Page::Index::RecordHeader
length=8,
next=112,
type=:conventional,
heap_number=2,
n_owned=0,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=[],
lengths={"b"=>788},
externs=["b"]>,
=> length={“b”=>788} 을 확인한다. 맨위에서 compact 동작방식의 768 bytes와는 수치가 살짝 다르지만 비슷한 로직을 드디어 확인했다
extern=
#<struct Innodb::Field::ExternReference
space_id=23,
page_number=4,
offset=38,
length=9232>>],
=> 이 레코드의 off-page는 4번 페이지에 저장되었으며, 그 크기는 9232bytes라 한다
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_compact -p 4 page-dump
#<Innodb::Page::Blob:0x0000000000fb01d0>:
fil header:
#<struct Innodb::Page::FilHeader
checksum=4279130595,
offset=4,
prev=0,
next=0,
lsn=2543164,
type=:BLOB,
flush_lsn=0,
space_id=23>
fil trailer:
#<struct Innodb::Page::FilTrailer checksum=4279130595, lsn_low32=2543164>
blob header:
{:length=>9232, :next=>nil}
blob data:
00000000 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
00000016 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
00000032 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
00000048 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
.
.
.
00009216 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
=> 4번 page를 확인해보면, type=:BLOB 으로 저장되어있음을 확인할 수 있고 9200 bytes만큼의 blob data를 쭉 확인할 수 있다!!
dynamic
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_dynamic space-index-pages-summary
page index level data free records
3 42 0 45 16207 1
5 0 0 0 16384 0
=> 3번 page에 45bytes의 레코드 하나가 저장된 것으로 보임. 10,000 bytes의 문자열을 넣었는데 45bytes밖에 안됨. 벌써 COMPACT와 다르다
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_dynamic -p 3 page-dump
#<Innodb::Page::Index:0x0000000000fafaf0>:
fil header:
#<struct Innodb::Page::FilHeader
checksum=1440227673,
offset=3,
prev=nil,
next=nil,
lsn=2553982,
type=:INDEX,
flush_lsn=0,
space_id=24>
fil trailer:
#<struct Innodb::Page::FilTrailer checksum=1440227673, lsn_low32=2553982>
page header:
#<struct Innodb::Page::Index::PageHeader
n_dir_slots=2,
heap_top=165,
n_heap_format=32771,
n_heap=3,
format=:compact,
garbage_offset=0,
garbage_size=0,
last_insert_offset=128,
direction=:no_direction,
n_direction=0,
n_recs=1,
max_trx_id=0,
level=0,
index_id=42>
fseg header:
#<struct Innodb::Page::Index::FsegHeader
leaf=
<Innodb::Inode space=<Innodb::Space file="test/tb_dynamic.ibd", page_size=16384, pages=6>, fseg=2>,
internal=
<Innodb::Inode space=<Innodb::Space file="test/tb_dynamic.ibd", page_size=16384, pages=6>, fseg=1>>
sizes:
header 120
trailer 8
directory 4
free 16207
used 177
record 45
per record 45.00
page directory:
[99, 112]
system records:
#<struct Innodb::Page::Index::SystemRecord
offset=99,
header=
#<struct Innodb::Page::Index::RecordHeader
length=5,
next=128,
type=:infimum,
heap_number=0,
n_owned=1,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=nil,
lengths=nil,
externs=nil>,
next=128,
data="infimum\x00",
length=8>
#<struct Innodb::Page::Index::SystemRecord
offset=112,
header=
#<struct Innodb::Page::Index::RecordHeader
length=5,
next=112,
type=:supremum,
heap_number=1,
n_owned=2,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=nil,
lengths=nil,
externs=nil>,
next=112,
data="supremum",
length=8>
garbage records:
records:
#<struct Innodb::Page::Index::UserRecord
type=:clustered,
format=:compact,
offset=128,
header=
#<struct Innodb::Page::Index::RecordHeader
length=8,
next=112,
type=:conventional,
heap_number=2,
n_owned=0,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=[],
lengths={"b"=>20},
externs=["b"]>,
next=112,
key=
[#<struct Innodb::Page::Index::FieldDescriptor
name="a",
type="INT",
value=1,
extern=nil>],
row=
[#<struct Innodb::Page::Index::FieldDescriptor
name="b",
type="VARCHAR(10000)",
value="",
extern=
#<struct Innodb::Field::ExternReference
space_id=24,
page_number=4,
offset=38,
length=10000>>],
sys=
[#<struct Innodb::Page::Index::FieldDescriptor
name="DB_TRX_ID",
type="TRX_ID",
value=1292,
extern=nil>,
#<struct Innodb::Page::Index::FieldDescriptor
name="DB_ROLL_PTR",
type="ROLL_PTR",
value=
#<struct Innodb::DataType::RollPointerType::Pointer
is_insert=true,
rseg_id=44,
undo_log=#<struct Innodb::Page::Address page=288, offset=272>>,
extern=nil>],
child_page_number=nil,
transaction_id=1292,
roll_pointer=
#<struct Innodb::DataType::RollPointerType::Pointer
is_insert=true,
rseg_id=44,
undo_log=#<struct Innodb::Page::Address page=288, offset=272>>,
length=37>
header=
#<struct Innodb::Page::Index::RecordHeader
length=8,
next=112,
type=:conventional,
heap_number=2,
n_owned=0,
info_flags=0,
offset_size=nil,
n_fields=nil,
nulls=[],
lengths={"b"=>20},
externs=["b"]>,
=> lengths={“b”=>20} , 맨위에서 본 dynamic의 동작방식과 동일하게 20bytes를 체크한다 !
마찬가지로 3번 page를 자세히 확인해보자
row=
[#<struct Innodb::Page::Index::FieldDescriptor
name="b",
type="VARCHAR(10000)",
value="",
extern=
#<struct Innodb::Field::ExternReference
space_id=24,
page_number=4,
offset=38,
length=10000>>],
=> 역시 b컬럼에 대해 extern, off-page가 발생하였고 4번 페이지에 저장되었다.
COMPACT와 달리 768bytes 만큼의 prefix 조차도 저장하지 않아서 10,000 bytes 그대로 off-page에 저장되는 모습이다
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_dynamic -p 4 page-dump
#<Innodb::Page::Blob:0x0000000000fafaf0>:
fil header:
#<struct Innodb::Page::FilHeader
checksum=3045201149,
offset=4,
prev=0,
next=0,
lsn=2553982,
type=:BLOB,
flush_lsn=0,
space_id=24>
fil trailer:
#<struct Innodb::Page::FilTrailer checksum=3045201149, lsn_low32=2553982>
blob header:
{:length=>10000, :next=>nil}
blob data:
00000000 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
00000016 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
00000032 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
00000048 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
.
.
.
00009984 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
=> 4번 page를 자세히 확인해보자
마찬가지로 type=:BLOB 으로 저장되었고 10,000 bytes만큼의 blob-data 를 확인할 수 있다!!
데이터 건 수에 따른 COMPACT DYNAMIC data-page 차이
mysql> select count(*),avg(length(b)) from tb_compact;
+----------+----------------+
| count(*) | avg(length(b)) |
+----------+----------------+
| 100 | 10000.0000 |
+----------+----------------+
1 row in set (0.02 sec)
mysql> select count(*),avg(length(b)) from tb_dynamic;
+----------+----------------+
| count(*) | avg(length(b)) |
+----------+----------------+
| 100 | 10000.0000 |
+----------+----------------+
1 row in set (0.01 sec)
* compact
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_compact space-index-pages-summary
page index level data free records
3 41 1 98 16154 7
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_compact space-index-pages-summary | wc -l
538
* dynamic
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_dynamic space-index-pages-summary
page index level data free records
3 42 0 4500 11704 100
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_dynamic space-index-pages-summary | wc -l
218
mysql> select count(*),avg(length(b)) from tb_compact;
+----------+----------------+
| count(*) | avg(length(b)) |
+----------+----------------+
| 12800 | 10000.0000 |
+----------+----------------+
1 row in set (0.42 sec)
mysql> select count(*),avg(length(b)) from tb_dynamic;
+----------+----------------+
| count(*) | avg(length(b)) |
+----------+----------------+
| 12800 | 10000.0000 |
+----------+----------------+
1 row in set (0.45 sec)
### compact
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_compact space-index-pages-summary | wc -l
3326
### dynamic
[root@caeaedb95ff7 data]# innodb_space -s ibdata1 -T test/tb_dynamic space-index-pages-summary | wc -l
510
=> COMPACT : 데이터 100건일 때 벌써 index depth가 늘어나고, 12800건 일 땐 data page 개수가 3326개
dynamic : 12800 건일 때에도 data page 개수는 510개 밖에 안됨.
text 등 큰 데이터를 저장할 때 compact / dynamic 의 차이는 데이터 건 수가 많아질 수록 점점 커질 것이기 때문에 반드시 dynamic을 사용하자