Innodb 엔진 사용 시 Index 생성 속도 향상 방법

Tech Note 정보: kimdubi 님이 작성하신 글입니다.; 카테고리: [ MariaDB ]; 게시됨: 08 January 2018; 작성됨: 08 January 2018; 최종 변경: 05 October 2019; 조회수: 12752

--- 내용 요약 ---

-. innodb_sort_buffer_size

Innodb 테이블의 인덱스를 생성 할 땐

sort_buffer_size가 아닌 innodb_sort_buffer_size를 설정해야 하며

innodb_sort_buffer_size=1M (default) -> 64M (MAX)

변경 시 2배정도의 성능향상 효과가 있었음.

innodb_sort_size를 1M -> 64M 으로 변경 시,

210분 -> 110분

105분 -> 49분

55분 -> 30분

55분 -> 32분

그러나 dynamic 변수가 아니라서 변경 시 재기동이 필요함.

* innodb_sort_buffer_size는 데이터를 클러스터 인덱스에서 읽어 들여 정렬한 후 세컨더리 인덱스에 벌크로 입력하는 기능

-. 인덱스 생성 구문 차이

alter table plmp.index_test add primary key (obid),

add index IX_index_test_02 (pfrom_obid,pclass_name),

add index IX_index_test_03 (pto_obid,pclass_name);

처럼 한 쿼리로 여러 인덱스를 생성할 때 disk 에 writing 하는 작업이 덜 일어난다고 하여

생성 속도가 빨라진다고 하는 말이 있었으나 각각 생성하는 것과 차이가 없었음.

-. tmp_table_size / heap_table_size 조정

인덱스 생성도 sorting이 필요하여

tmp_table_size 조정 시 효과가 있을것으로 추측했으나

24M->100M 조정 후에도 성능차이는 없었음.

아래는 테스트 내용입니다.

--0. 테스트 환경

A 서버

서버M : 8GB

innodb_buffer_pool_size=4G

max_heap_table_size=24m

tmp_table_size=24m

innodb_sort_buffer_size=1m

B 서버

서버M : 8GB

innodb_buffer_pool_size=4G

max_heap_table_size=24m

tmp_table_size=24m

innodb_sort_buffer_size=64m

--1. TEST 방법

-. innodb_sort_buffer_size 1M VS innodb_sort_buffer_size 64M

-. 인덱스 생성 구문 한번에 수행 VS 각각 수행

alter table plmp.index_test add primary key (obid),
add index IX_index_test_02 (pfrom_obid,pclass_name),
  add index IX_index_test_03 (pto_obid,pclass_name);

alter table plmp.index_test add primary key (obid);
ALTER TABLE plmp.index_test add INDEX IX_index_test_02 (pfrom_obid,pclass_name);
ALTER TABLE plmp.index_test add INDEX IX_index_test_03 (pto_obid,pclass_name);

--2-1. 대상 테이블과 인덱스

CREATE TABLE `index_test` (
  `obid` varchar(20) COLLATE utf8_bin NOT NULL,
  `pclass_name` varchar(128) COLLATE utf8_bin NOT NULL,
  `pfrom_class` varchar(128) COLLATE utf8_bin NOT NULL,
  `pfrom_obid` varchar(20) COLLATE utf8_bin NOT NULL,
  `pto_class` varchar(128) COLLATE utf8_bin NOT NULL,
  `pto_obid` varchar(20) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`obid`),
  KEY `IX_index_test_02` (`pfrom_obid`,`pclass_name`),
  KEY `IX_index_test_03` (`pto_obid`,`pclass_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `index_test` WRITE;

...... 데이터 적재 ....

--2-2. innodb sort buffer size 1m

-. 작업 중 OS 자원 사용률

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

16624 masvc01 20 0 5795m 4.5g 4484 S 28.9 59.0 125:42.94 mysqld

-. 인덱스 생성 THREAD 확인

MariaDB [(none)]> show full processlist;

+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+

add index IX_index_test_02 (pfrom_obid,pclass_name),

add index IX_index_test_03 (pto_obid,pclass_name) | 0.000 |

2 rows in set (0.00 sec)

-. 한번에 생성 소요시간

Query OK, 0 rows affected (3 hours 31 min 39.53 sec)

Records: 0 Duplicates: 0 Warnings: 0

-. 각각 생성 소요시간

MariaDB [(none)]> alter table plmp.index_test add primary key (obid);

Query OK, 0 rows affected (1 hour 46 min 18.63 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [(none)]> ALTER TABLE plmp.index_test add INDEX IX_index_test_02 (pfrom_obid,pclass_name);

Query OK, 0 rows affected (54 min 35.70 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [(none)]> ALTER TABLE plmp.index_test add INDEX IX_index_test_03 (pto_obid,pclass_name);

Query OK, 0 rows affected (54 min 48.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

--2-2. innodb sort buffer size 64m

-. 작업 중 OS 자원 사용률

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

18648 masvc01 20 0 5795m 5.1g 7920 S 31.2 66.4 127:56.89 mysqld

-. 인덱스 생성 THREAD 확인

MariaDB [(none)]> show full processlist;

add index IX_index_test_02 (pfrom_obid,pclass_name),

add index IX_index_test_03 (pto_obid,pclass_name) | 0.000 |

2 rows in set (0.00 sec)

-. 한번에 생성 소요시간

MariaDB [(none)]> alter table plmp.index_test add primary key (obid),

-> add index IX_index_test_02 (pfrom_obid,pclass_name),

-> add index IX_index_test_03 (pto_obid,pclass_name);

Query OK, 0 rows affected (1 hour 52 min 16.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

-. 각각 생성 소요시간

MariaDB [(none)]> alter table plmp.index_test add primary key (obid);

Query OK, 0 rows affected (49 min 27.73 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [(none)]> ALTER TABLE plmp.index_test add INDEX IX_index_test_02 (pfrom_obid,pclass_name);

Query OK, 0 rows affected (30 min 33.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [(none)]> ALTER TABLE plmp.index_test add INDEX IX_index_test_03 (pto_obid,pclass_name);

Query OK, 0 rows affected (32 min 16.40 sec)

Records: 0 Duplicates: 0 Warnings: 0

--3. tmp_table_size / heap_table_size 24M->100M

=> tmp_table_size , heap_table_size 변경은 인덱스 생성과는 무관

MariaDB [(none)]> alter table plmp.index_test add primary key (obid);

Query OK, 0 rows affected (49 min 17.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [(none)]> ALTER TABLE plmp.index_test add INDEX IX_index_test_02 (pfrom_obid,pclass_name);

Query OK, 0 rows affected (33 min 17.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [(none)]> ALTER TABLE plmp.index_test add INDEX IX_index_test_03 (pto_obid,pclass_name);

Query OK, 0 rows affected (33 min 2.73 sec)

Records: 0 Duplicates: 0 Warnings: 0

-- 참고 1. innodb 일땐 disable / enable 미적용됨 (데이터 적재 전 인덱스 ON/OFF기능)

-. myisam

*************************** 2. row ***************************

Table: myisam_test

Non_unique: 1

Key_name: index1

Seq_in_index: 1

Column_name: num2

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment: disabled

Index_comment:

2 rows in set (0.00 sec)

-. innodb

************************** 5. row ***************************

Table: index_test

Non_unique: 1

Key_name: IX_index_test_03

Seq_in_index: 2

Column_name: pclass_name

Collation: A

Cardinality: 90819165

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

5 rows in set (0.00 sec)

--참고 2. 인덱스 생성 중 TEMP 영역 사용현황

[DBTEST01V:masvc01]/logs001/masvc01/TESTDB/binary] df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/rootvg-root_vg

25G 5.2G 19G 22% /

tmpfs 3.9G 72K 3.9G 1% /dev/shm

/dev/vda1 477M 191M 261M 43% /boot

/dev/mapper/NIP_VG001-engn_lv001

9.8G 1.3G 8.0G 14% /engn001

/dev/mapper/NIP_VG001-data_lv001

148G 86G 55G 62% /data001

/dev/mapper/NIP_VG001-logs_lv001

50G 19G 29G 40% /logs001

/dev/mapper/NIP_VG001-temp_lv001

30G 210M 28G 1% /temp001

ID 생성