테스트 목적

innodb_sort_buffer_size 를 크게 잡으면 index 생성 속도에 얼마나 도움이 될까?
online DDL 중 DML 성능 향상에도 도움이 될까?

테스트 환경

  • 5.7.mysql_aurora.2.04.9
  • r5.2xlarge writer 1대
  • 테스트 대상 테이블 정보
 CREATE TABLE `stock1` (
  `s_i_id` int(11) NOT NULL,
  `s_w_id` smallint(6) NOT NULL,
  `s_quantity` smallint(6) DEFAULT NULL,
  `s_dist_01` char(24) DEFAULT NULL,
  `s_dist_02` char(24) DEFAULT NULL,
  `s_dist_03` char(24) DEFAULT NULL,
  `s_dist_04` char(24) DEFAULT NULL,
  `s_dist_05` char(24) DEFAULT NULL,
  `s_dist_06` char(24) DEFAULT NULL,
  `s_dist_07` char(24) DEFAULT NULL,
  `s_dist_08` char(24) DEFAULT NULL,
  `s_dist_09` char(24) DEFAULT NULL,
  `s_dist_10` char(24) DEFAULT NULL,
  `s_ytd` decimal(8,0) DEFAULT NULL,
  `s_order_cnt` smallint(6) DEFAULT NULL,
  `s_remote_cnt` smallint(6) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`s_w_id`,`s_i_id`),
  KEY `fkey_stock_21` (`s_i_id`),
  CONSTRAINT `fkey_stock_1_1` FOREIGN KEY (`s_w_id`) REFERENCES `warehouse1` (`w_id`),
  CONSTRAINT `fkey_stock_2_1` FOREIGN KEY (`s_i_id`) REFERENCES `item1` (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

+-------------+------------+---------------+----------------+
| table_name  | table_rows | DATA_SIZE(MB) | INDEX_SIZE(MB) |
+-------------+------------+---------------+----------------+
| stock1      |  192659975 |      70237.00 |        3991.00 |
+-------------+------------+---------------+----------------+

테스트 대상 파라미터

Command-Line Format–innodb-sort-buffer-size=#
System Variableinnodb_sort_buffer_size
ScopeGlobal
DynamicNo
TypeInteger
Default Value1048576
Minimum Value65536
Maximum Value67108864
  • index 생성 시 data sorting 하는 buffer의 크기를 정의하는 파라미터
  • innodb_sort_buffer에서 sorting 하고 disk로 내려쓰게 되며 buffer의 크기가 커질 수록 merge 횟수가 줄어들어 Disk I/O 등에서 성능 이점이 있음
  • online DDL 수행 중 발생하는 DML record를 담는 row log buffer 의 extend 단위로도 사용됨
    (row log buffer 는 최대 innodb_online_alter_log_max_size 값만큼 증가할수있음 )

결론

  • default 1MB로 놓고 쓰자
    • innodb_sort_buffer_size를 늘리면 인덱스 생성 속도는 아주 조금 향상되나 side effect가 크다
      default 1MB에 → 최대값 64MB로 설정하는 건 큰 메리트가 없음. disk와 CPU의 성능이 좋을 수록 그 차이는 더 미비할 것
    • innodb_sort_buffer_size 를 늘릴 수록 인덱스 여러개를 한꺼번에 생성 했을 때 memory leak으로 보이는 현상이 심함
    • 미세하지만 innodb_sort_buffer_size를 늘릴 수록 오히려 Online DDL 중 DML 성능이 떨어짐

index 생성 소요시간 테스트

default 1MB와 64MB의 차이가 크지 않음, 5%~10% 정도의 성능 차이

#### innodb_sort_buffer_size = 64KB
mysql> create index idx_test_01 on stock1 ( s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 );

Query OK, 0 rows affected (2 hours 48 min 47.72 sec)
Records: 0 Duplicates: 0 Warnings: 0


#### innodb_sort_buffer_size = 1MB
mysql> create index idx_test_01 on stock1 ( s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 );

Query OK, 0 rows affected (2 hours 11 min 13.15 sec)
Records: 0 Duplicates: 0 Warnings: 0


#### innodb_sort_buffer_size = 8MB
mysql> create index idx_test_01 on stock1 ( s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 );

Query OK, 0 rows affected (2 hours 8 min 51.46 sec)
Records: 0 Duplicates: 0 Warnings: 0


#### innodb_sort_buffer_size = 16MB
mysql> create index idx_test_01 on stock1 ( s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 );

Query OK, 0 rows affected (2 hours 5 min 48.70 sec)
Records: 0 Duplicates: 0 Warnings: 0


#### innodb_sort_buffer_size = 32MB
mysql> create index idx_test_01 on stock1 ( s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 );

Query OK, 0 rows affected (2 hours 4 min 59.64 sec)
Records: 0 Duplicates: 0 Warnings: 0


#### innodb_sort_buffer_size = 64MB
mysql> create index idx_test_01 on stock1 ( s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 );

Query OK, 0 rows affected (2 hours 4 min 48.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

online DDL 중 DML QPS 테스트

  • alter table stock1 add column test1 varchar(200),algorithm=inplace, lock=none; 
  • 위 DDL 수행 중 128 threads, Read 45 % Write 45% Others 10% 비율로 30초간 부하 테스트 7회 수행 후, 뒤의 4회로 평균 산출
  • 유의미한 차이는 아니지만 innodb_buffer_size 가 클수록 오히려 QPS가 떨어지는 결과가 나옴 
    • 왜일까,,,,,,, => online DDL 수행 중 발생한 DML 데이터는 row_log_buffer ( innodb_online_alter_log_max_size ) 에 저장됨 이 때 row_log_buffer 는 여러 chunk 단위로 나뉘는데, 그 chunk 단위는 innodb_sort_buffer_size의 크기임 결국 innodb_sort_buffer_size의 크기가 클 수록 row log buffer 내 chunk extend 횟수가 줄어들기 때문에 DML 성능에 이점이 있을 수 있을 것이라고 기대했으나 반대의 결과가 나옴

sort_buffer_size , rnd_buffer_size 같은 버퍼도 일정 값 넘어서 설정하면 효과 없다는데 같은 맥락일듯

#### NO DDL, DML ONLY
queries:                             1070494 (35579.24 per sec.)
queries:                             1079044 (35787.83 per sec.)
queries:                             1062181 (35277.79 per sec.)
queries:                             1085398 (35998.80 per sec.)

#### innodb_sort_buffer_size = 64K
queries:                             929224 (30728.81 per sec.)
queries:                             941740 (31165.30 per sec.)
queries:                             939693 (31183.89 per sec.)
queries:                             925846 (30745.61 per sec.)

#### innodb_sort_buffer_size = 1M
queries:                             914122 (30372.16 per sec.)
queries:                             931132 (30895.06 per sec.)
queries:                             920218 (30560.92 per sec.)
queries:                             929405 (30846.84 per sec.)

#### innodb_sort_buffer_size = 8M
queries:                             853290 (28358.40 per sec.)
queries:                             848449 (28159.97 per sec.)
queries:                             860407 (28521.78 per sec.)
queries:                             863537 (28674.37 per sec.)

#### innodb_sort_buffer_size = 16M
queries:                             855897 (28392.20 per sec.)
queries:                             835787 (27653.29 per sec.)
queries:                             851361 (28228.19 per sec.)
queries:                             849894 (28067.34 per sec.)

#### innodb_sort_buffer_size = 32M
queries:                             826788 (27398.12 per sec.)
queries:                             807663 (26799.70 per sec.)
queries:                             855277 (28415.28 per sec.)
queries:                             872884 (28964.68 per sec.)

#### innodb_sort_buffer_size = 64M
queries:                             873414 (27942.96 per sec.)
queries:                             846271 (28102.47 per sec.)
queries:                             838817 (27707.62 per sec.)
queries:                             850687 (28238.55 per sec.)

innodb_sort_buffer_size 에 따른 resource 사용량 

생성 인덱스

mysql> alter table stock1 add index idx01 (s_dist_01),
-> add index idx02 (s_dist_02),
-> add index idx03 (s_dist_03),
-> add index idx04 (s_dist_04),
-> add index idx05 (s_dist_05),
-> add index idx06 (s_dist_06),
-> add index idx07 (s_dist_07),
-> add index idx08 (s_dist_08),
-> add index idx09 (s_dist_09),
-> add index idx10 (s_dist_10);

innodb_sort_buffer_size = 1MB

innodb_sort_buffer_size = 64MB

  • innodb_sort_buffer_size = 1MB일 때와 64MB일 때 메모리 사용량 차이가 꽤 많이 나는 것을 볼 수 있음
    • innodb_sort_buffer_size 가 DDL operation 단위로 할당되기 때문임
  • tmp files 수치도 한번에 증가함, 11 = 1 + 10 (작업용 임시파일 1개 + index 10개 생성 각각 1개씩)
  • USABLE MEMORY 12,600 MB => 10,000 MB => 11,500MB , index 생성 중 2,600MB 사용됐다가 완료 후 1,500MB 반환됨. 일부 반환 안되는 메모리가 있는듯
  • innodb_sort_buffer_size 사용 공식
(6 /*FTS_NUM_AUX_INDEX*/ * (3*@@GLOBAL.innodb_sort_buffer_size) + 2 * number_of_partitions * number_of_secondary_indexes_created * (@@GLOBAL.innodb_sort_buffer_size/dict_index_get_min_size(index)*/) * 8 /*64-bit sizeof *buf->tuples*/")

innodb_sort_buffer_size 동작 방식

innodb_sort_buffer_size 크기가 sort & merge 동작에 어떻게 영향을 주는지 확인해보자

======== data sort&merge ========
#0  row_merge_tuple_sort (index=0x7f6d200c8590, n_uniq=5, n_field=5, dup=0x0, tuples=0x7f6d202488c0, aux=0x7f6d204488c0, low=0, high=1899)
    at /MySQL_sources/mysql-8.0.25/storage/innobase/row/row0merge.cc:986
#1  0x0000000004bab1aa in row_merge_tuple_sort (index=0x7f6d200c8590, n_uniq=5, n_field=5, dup=0x0, tuples=0x7f6d202488c0, aux=0x7f6d204488c0, low=0, high=3799)
    at /MySQL_sources/mysql-8.0.25/storage/innobase/row/row0merge.cc:989
#2  0x0000000004bab1aa in row_merge_tuple_sort (index=0x7f6d200c8590, n_uniq=5, n_field=5, dup=0x0, tuples=0x7f6d202488c0, aux=0x7f6d204488c0, low=0, high=7598)
    at /MySQL_sources/mysql-8.0.25/storage/innobase/row/row0merge.cc:989
#3  0x0000000004bab43a in row_merge_buf_sort (buf=0x7f6d200c5b50, dup=0x0) at /MySQL_sources/mysql-8.0.25/storage/innobase/row/row0merge.cc:999
#4  0x0000000004baf16a in row_merge_read_clustered_index (trx=0x7f6e21433078, table=0x7f6d200b8e30, old_table=0x7f6d180f5a80, new_table=0x7f6d180f5a80, online=true, index=0x7f6d200c80a0,
    fts_sort_idx=0x0, psort_info=0x0, files=0x7f6d200c5a40, key_numbers=0x7f6d200c80c8, n_index=1, add_cols=0x0, add_v=0x0, col_map=0x0, add_autoinc=18446744073709551615, sequence=...,
    block=0x7f6d0bd00000 "", skip_pk_sort=false, tmpfd=0x7f6e1811116c, stage=0x7f6d200c8330, eval_table=0x7f6d200b8e30) at /MySQL_sources/mysql-8.0.25/storage/innobase/row/row0merge.cc:2271


======== index 생성을 위한 buffer 할당 등 index 생성 준비 단계 ========
#5  0x0000000004bb34c3 in row_merge_build_indexes (trx=0x7f6e21433078, old_table=0x7f6d180f5a80, new_table=0x7f6d180f5a80, online=true, indexes=0x7f6d200c80a0, key_numbers=0x7f6d200c80c8,
    n_indexes=1, table=0x7f6d200b8e30, add_cols=0x0, col_map=0x0, add_autoinc=18446744073709551615, sequence=..., skip_pk_sort=false, stage=0x7f6d200c8330, add_v=0x0,
    eval_table=0x7f6d200b8e30) at /MySQL_sources/mysql-8.0.25/storage/innobase/row/row0merge.cc:3753


======== inplace 로 DDL 처리 함수 호출 ========
#6  0x0000000004a34194 in ha_innobase::inplace_alter_table_impl<dd::Table> (this=0x7f6d2009c9c8, altered_table=0x7f6d200b8e30, ha_alter_info=0x7f6e18112010, old_dd_tab=0x7f6d18021900,
    new_dd_tab=0x7f6d20024580) at /MySQL_sources/mysql-8.0.25/storage/innobase/handler/handler0alter.cc:6126
#7  0x0000000004a0e6b5 in ha_innobase::inplace_alter_table (this=0x7f6d2009c9c8, altered_table=0x7f6d200b8e30, ha_alter_info=0x7f6e18112010, old_dd_tab=0x7f6d18021900,
    new_dd_tab=0x7f6d20024580) at /MySQL_sources/mysql-8.0.25/storage/innobase/handler/handler0alter.cc:1281
#8  0x00000000033bbc41 in handler::ha_inplace_alter_table (this=0x7f6d2009c9c8, altered_table=0x7f6d200b8e30, ha_alter_info=0x7f6e18112010, old_table_def=0x7f6d18021900,
    new_table_def=0x7f6d20024580) at /MySQL_sources/mysql-8.0.25/sql/handler.h:5859
#9  0x000000000339f1ed in mysql_inplace_alter_table (thd=0x7f6d20006240, schema=..., new_schema=..., table_def=0x7f6d18021900, altered_table_def=0x7f6d20024580, table_list=0x7f6d2002a388,
    table=0x7f6d20011eb0, altered_table=0x7f6d200b8e30, ha_alter_info=0x7f6e18112010, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, alter_ctx=0x7f6e18112ef0,
    columns=std::set with 0 elements, fk_key_info=0x7f6d200be118, fk_key_count=0, fk_invalidator=0x7f6e18112e20) at /MySQL_sources/mysql-8.0.25/sql/sql_table.cc:13025
#10 0x00000000033aa8bc in mysql_alter_table (thd=0x7f6d20006240, new_db=0x7f6d2002a9a0 "testdb", new_name=0x7f6d20029ff8 "tb_test", create_info=0x7f6e18114380, table_list=0x7f6d2002a388,
    alter_info=0x7f6e18114210) at /MySQL_sources/mysql-8.0.25/sql/sql_table.cc:16924
#11 0x0000000003919803 in Sql_cmd_create_or_drop_index_base::execute (this=0x7f6d2002abe8, thd=0x7f6d20006240) at /MySQL_sources/mysql-8.0.25/sql/sql_cmd_ddl_table.cc:474


======== thread connect && command parsing ========
#12 0x00000000032cdb32 in mysql_execute_command (thd=0x7f6d20006240, first_level=true) at /MySQL_sources/mysql-8.0.25/sql/sql_parse.cc:3426
#13 0x00000000032d2a43 in dispatch_sql_command (thd=0x7f6d20006240, parser_state=0x7f6e18115aa0) at /MySQL_sources/mysql-8.0.25/sql/sql_parse.cc:5000
#14 0x00000000032c93a0 in dispatch_command (thd=0x7f6d20006240, com_data=0x7f6e18116b40, command=COM_QUERY) at /MySQL_sources/mysql-8.0.25/sql/sql_parse.cc:1841
#15 0x00000000032c78f9 in do_command (thd=0x7f6d20006240) at /MySQL_sources/mysql-8.0.25/sql/sql_parse.cc:1320
#16 0x000000000349d601 in handle_connection (arg=0x9602cd0) at /MySQL_sources/mysql-8.0.25/sql/conn_handler/connection_handler_per_thread.cc:301
#17 0x0000000005096688 in pfs_spawn_thread (arg=0xb2f2c60) at /MySQL_sources/mysql-8.0.25/storage/perfschema/pfs.cc:2898
#18 0x00007f6e32f72ea5 in start_thread () from /lib64/libpthread.so.0
#19 0x00007f6e315619fd in clone () from /lib64/libc.so.6
  • mysqld를 gdb 로 trace를 찍어보면 위처럼 row를 merge&sort 하는 작업이 계속 일어나는 것을 확인할 수 있음
  • row0merge, row_merge , sort 키워드를 힌트로 debugging 을 계속 진행함

innodb_sort_buffer_size = 1MB

T@9: row0merge.cc: 1038: | | | | | | | | | | | ib_merge_sort: write 0x7fa94c58ffcd,41,0 EOF
T@9: row0merge.cc: | | | | | | | | | | <row_merge_buf_write
T@9: row0merge.cc: 1112: | | | | | | | | | | >row_merge_write
T@9: row0merge.cc: 1113: | | | | | | | | | | | ib_merge_sort: fd=41 ofs=0

T@9: row0merge.cc: 1038: | | | | | | | | | | | ib_merge_sort: write 0x7fa94c58ffcd,41,1 EOF
T@9: row0merge.cc: | | | | | | | | | | <row_merge_buf_write
T@9: row0merge.cc: 1112: | | | | | | | | | | >row_merge_write
T@9: row0merge.cc: 1113: | | | | | | | | | | | ib_merge_sort: fd=41 ofs=1048576

T@9: row0merge.cc: 1038: | | | | | | | | | | | ib_merge_sort: write 0x7fa94c58ffcd,41,2 EOF
T@9: row0merge.cc: | | | | | | | | | | <row_merge_buf_write
T@9: row0merge.cc: 1112: | | | | | | | | | | >row_merge_write
T@9: row0merge.cc: 1113: | | | | | | | | | | | ib_merge_sort: fd=41 ofs=2097152
.
.
.
T@9: row0merge.cc: 1038: | | | | | | | | | | | ib_merge_sort: write 0x7fa94c58ffcd,41,10 EOF
T@9: row0merge.cc: | | | | | | | | | | <row_merge_buf_write
T@9: row0merge.cc: 1112: | | | | | | | | | | >row_merge_write
T@9: row0merge.cc: 1113: | | | | | | | | | | | ib_merge_sort: fd=41 ofs=10485760
.
.
.
T@9: row0merge.cc: 1038: | | | | | | | | | | | ib_merge_sort: write 0x7fa94c58ffcd,41,100 EOF
T@9: row0merge.cc: | | | | | | | | | | <row_merge_buf_write
T@9: row0merge.cc: 1112: | | | | | | | | | | >row_merge_write
T@9: row0merge.cc: 1113: | | | | | | | | | | | ib_merge_sort: fd=41 ofs=104857600
  • index 생성을 위해 데이터 sorting 한 결과를 1MB 씩 처리하는 내부 동작을 확인할 수 있음
[root@1508b86c3e65 tmp]# lsof -p 624 | grep tmp | grep -v sock
mysqld 624 root 41u REG 0,154 20971520 3303241 /usr/local/makayal/mysql/tmp/ibKhKV6x (deleted)
mysqld 624 root 42u REG 0,154 0 3303242 /usr/local/makayal/mysql/tmp/ibpprcYS (deleted)

[root@1508b86c3e65 tmp]# lsof -p 624 | grep tmp | grep -v sock
mysqld 624 root 41u REG 0,154 33554432 3303241 /usr/local/makayal/mysql/tmp/ibKhKV6x (deleted)
mysqld 624 root 42u REG 0,154 0 3303242 /usr/local/makayal/mysql/tmp/ibpprcYS (deleted)

[root@1508b86c3e65 tmp]# lsof -p 624 | grep tmp | grep -v sock
mysqld 624 root 41u REG 0,154 42991616 3303241 /usr/local/makayal/mysql/tmp/ibKhKV6x (deleted)
mysqld 624 root 42u REG 0,154 0 3303242 /usr/local/makayal/mysql/tmp/ibpprcYS (deleted)
  • lsof로 mysql의 pid를 잡으면 innodb_tmpdir에 생성된 두개의 temporary file을 확인할 수 있음
  • index 생성 중에는 두개의 temporary file이 생성됨
    • 하나는 sorting 작업 돌리고 merge 되는 파일, 하나는 앞의 작업 파일을 merge 받는 파일로 innodb_sort_buffer_size 1MB 씩 증가하는 것을 볼 수 있음

innodb_sort_buffer_size = 64MB

T@7: row0merge.cc: 1038: | | | | | | | | | | | ib_merge_sort: write 0x7f4f9bffff79,41,0 EOF
T@7: row0merge.cc: | | | | | | | | | | <row_merge_buf_write
T@7: row0merge.cc: 1112: | | | | | | | | | | >row_merge_write
T@7: row0merge.cc: 1113: | | | | | | | | | | | ib_merge_sort: fd=41 ofs=0

T@7: row0merge.cc: 1038: | | | | | | | | | | | ib_merge_sort: write 0x7f4f9bffff79,41,1 EOF
T@7: row0merge.cc: | | | | | | | | | | <row_merge_buf_write
T@7: row0merge.cc: 1112: | | | | | | | | | | >row_merge_write
T@7: row0merge.cc: 1113: | | | | | | | | | | | ib_merge_sort: fd=41 ofs=67108864

T@7: row0merge.cc: 1038: | | | | | | | | | | | ib_merge_sort: write 0x7f4f9bfffff1,41,2 EOF
T@7: row0merge.cc: | | | | | | | | | | <row_merge_buf_write
T@7: row0merge.cc: 1112: | | | | | | | | | | >row_merge_write
T@7: row0merge.cc: 1113: | | | | | | | | | | | ib_merge_sort: fd=41 ofs=134217728
.
.
.

T@7: row0merge.cc: 1038: | | | | | | | | | | | ib_merge_sort: write 0x7f4f9bfffff1,41,10 EOF
T@7: row0merge.cc: | | | | | | | | | | <row_merge_buf_write
T@7: row0merge.cc: 1112: | | | | | | | | | | >row_merge_write
T@7: row0merge.cc: 1113: | | | | | | | | | | | ib_merge_sort: fd=41 ofs=671088640
innodb_sort_buffer_size = 1MB 일 때와 달리 64MB씩 담아서 merge 하고 있음
[root@1508b86c3e65 tmp]# lsof -p 353 | grep tmp | grep -v sock
mysqld 353 root 41u REG 0,154 805306368 3303240 /usr/local/makayal/mysql/tmp/ibd5CxlG (deleted)
mysqld 353 root 42u REG 0,154 0 3303241 /usr/local/makayal/mysql/tmp/ibdcmt7k (deleted)

[root@1508b86c3e65 tmp]# lsof -p 353 | grep tmp | grep -v sock
mysqld 353 root 41u REG 0,154 872415232 3303240 /usr/local/makayal/mysql/tmp/ibd5CxlG (deleted)
mysqld 353 root 42u REG 0,154 0 3303241 /usr/local/makayal/mysql/tmp/ibdcmt7k (deleted)

[root@1508b86c3e65 tmp]# lsof -p 353 | grep tmp | grep -v sock
mysqld 353 root 41u REG 0,154 939524096 3303240 /usr/local/makayal/mysql/tmp/ibd5CxlG (deleted)
mysqld 353 root 42u REG 0,154 0 3303241 /usr/local/makayal/mysql/tmp/ibdcmt7k (deleted)
  • 인덱스 생성 중 lsof 로 잡은 temporary file도 64MB 씩 증가하고 있음