이번엔 좀 더 확실한 테스트를 위하여
1.5억건의 데이터를 대상으로 테스트를 진행 해보겠다.
참고 : 1.5억건 정도 되는 데이터를 이런식의 테이블에 입력하는것은 바람직 하지 않다.
이런 대량의 데이터는 최소한 파티셔닝등을 이용해서 검색구간을 분리 해주고,
데이터의 성격에 따라서 내림차순 인덱싱을 이용하는 등의 방법이 튜닝을 해야만 한다.
극악의 조건에서 Performance Test 를 하기 위한 목적임을 이해해 주기 바란다.
그럼 1.5억건(약 21GB)의 데이터를 테이블에 입력 해보겠다.
(데이터 생성방법은 이전글을 참고해 보도록 한다.)
데이터를 insert 할때는 index를 일단 disable 처리한다.
그렇지 않으면 insert 할때마다 index table을 업데이트 하기 때문에 입력되는 속도가 상당히 느려진다.
아래의 순서로 사용하여 데이터를 로드 해볼것이다.
(아래의 결과 화면은 테스트의 목적과 다른 내용임으로 스킵하는걸로..)
// 1. 기존에 입력된 테이블 truncate truncate table performance_test; // 2. 인덱스등의 키를 disable (입력 속도 향상을 위하여) alter table performance_test disable keys; // 3. 데이터 로드 LOAD DATA LOCAL INFILE '/root/testdata.txt' REPLACE INTO TABLE performance_test CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES; // 4. 인덱스 활성화 alter table performance_test enable keys; |
<< 데이터 입력 결과 >>
MariaDB [performance_test]> truncate table performance_test; Query OK, 0 rows affected (0.49 sec) MariaDB [performance_test]> alter table performance_test disable keys; Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [performance_test]> LOAD DATA LOCAL INFILE '/root/testdata.txt' -> REPLACE INTO TABLE performance_test -> CHARACTER SET utf8 -> FIELDS -> TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -> LINES -> TERMINATED BY '\n' -> IGNORE 1 LINES; Query OK, 158544000 rows affected, 65535 warnings (1 hour 18 min 21.48 sec) Records: 158544000 Deleted: 0 Skipped: 0 Warnings: 158544000 MariaDB [performance_test]> alter table performance_test enable keys; Query OK, 0 rows affected, 1 warning (0.10 sec) MariaDB [performance_test]>select count(id) from performance_test; +-----------+ | count(id) | +-----------+ | 158544000 | +-----------+ 1 row in set (1 min 36.02 sec) MariaDB [performance_test]> |
1.5억건(약 21GB)을 입력 하는데 1시간 18분이나 걸렷고, 단순 count 만 하는데도 1분 36초가 걸렸다.
이제 지난 번과 같은 방식으로 datetime 형과 unixtime 형태로 쿼리 날리면서 결과를 확인해보겠다.
이 정도의 데이터를 관리하는데 인덱스 없이 하는 경우는 없음으로
인덱스를 없는 상태의 쿼리는 생략하고, 인덱스가 걸려있는 상태로 테스트를 해보겠다.
현재 테이블 상태는 다음과 같다.
MariaDB [performance_test]> show create table performance_test; +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | performance_test | CREATE TABLE `performance_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `str100byte` varchar(100) NOT NULL, `date_type_date` datetime NOT NULL, `int_type_date` int(11) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `date_type_date` (`date_type_date`), KEY `int_type_date` (`int_type_date`) ) ENGINE=InnoDB AUTO_INCREMENT=158594701 DEFAULT CHARSET=utf8 | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [performance_test]> |
1. date 필드를 이용하여 검색 (인덱스 사용)
MariaDB [performance_test]> SELECT SQL_NO_CACHE COUNT(id) -> FROM performance_test -> WHERE date_type_date > '2000-06-01 00:00:00' -> AND date_type_date < '2000-07-01 00:00:00' -> ; +-----------+ | COUNT(id) | +-----------+ | 12959995 | +-----------+ 1 row in set (13.61 sec) |
지난번 6.6천만건 대비 데이터량은 250%(1.5억건) 증가 하였으나,
검색 속도는 1.7초대에 나온것에 비하면 검색 속도는 현저하게 느려졌다.
2. unixtime 필드를 이용하여 검색 (인덱스 사용)
MariaDB [performance_test]> SELECT SQL_NO_CACHE COUNT(id) -> FROM performance_test -> WHERE int_type_date > UNIX_TIMESTAMP('2000-06-01 00:00:00') -> AND int_type_date < UNIX_TIMESTAMP('2000-07-01 00:00:00') -> ; +-----------+ | COUNT(id) | +-----------+ | 12959995 | +-----------+ 1 row in set (3.14 sec) MariaDB [performance_test]> |
datetime 형을 사용한것 대비 검색속도는 unixtime 사용하는 것이 우월하다.
본 실험 결과는 datetime 형과 int 형태를 사용한것에 대한 단순 비교지만,
작은 차이라도 빈번한 검색이 이루어 지는 경우는
int 형을 사용하는 것이 DBMS에 부하가 낮다 라는것을 알 수 있었다.
(예: 세션DB에서 expire 여부 검색)
문론 데이터를 SELECT 했을때 보여지는 값이 날짜 형식이 아니여서 보기는 어렵지만,
퍼포먼스를 생각한다면 unixtime 을 사용하는것을 고려하는 것도 좋겠다.
다음은 결론(번외편)
2018/02/27 - [DBMS/MySQL] - MySQL InnoDB DATETIME vs Unixtime (int type) #1
2018/02/27 - [DBMS/MySQL] - MySQL InnoDB DATETIME vs Unixtime (int type) #2
2018/02/27 - [DBMS/MySQL] - MySQL InnoDB DATETIME vs Unixtime (int type) #3 (1.5억건) <
2018/02/27 - [DBMS/MySQL] - MySQL InnoDB DATETIME vs Unixtime (결론) #4
'DBMS > MySQL' 카테고리의 다른 글
| MySQL InnoDB DATETIME vs Unixtime (결론) #4 (1) | 2018.02.27 |
|---|---|
| MySQL InnoDB DATETIME vs Unixtime (int type) #2 (0) | 2018.02.27 |
| MySQL InnoDB DATETIME vs Unixtime (int type) #1 (0) | 2018.02.27 |
| MariaDB/Galera Cluster 기술 노트!! (0) | 2015.04.16 |
| Haproxy로 .mysql 서비스까지 확인하도록 xinet 사용 (0) | 2015.04.16 |