--- 내용 요약 ---
-. 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;
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host      | db   | Command | Time | State          | Info                                                                                                                                                                             | Progress |
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 16 | root | localhost | NULL | Query   |   85 | altering table | 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) |    0.000 |
| 18 | root | localhost | NULL | Query   |    0 | init           | show full processlist                                                                                                                                                            |    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;
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host      | db   | Command | Time | State          | Info                                                                                                                                                                             | Progress |
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 16 | root | localhost | NULL | Query   |   85 | altering table | 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) |    0.000 |
| 18 | root | localhost | NULL | Query   |    0 | init           | show full processlist                                                                                                                                                            |    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