몇가지 테스트를 해보았다.


1. Explain


MariaDB [performance_test]> EXPLAIN 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'

    -> ;

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

| id   | select_type | table            | type  | possible_keys  | key            | key_len | ref  | rows     | Extra                    |

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

|    1 | SIMPLE      | performance_test | range | date_type_date | date_type_date | 8       | NULL | 25207266 | Using where; Using index |

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

1 row in set (0.00 sec)


MariaDB [performance_test]> EXPLAIN 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')

    -> ;

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

| id   | select_type | table            | type  | possible_keys | key           | key_len | ref  | rows     | Extra                    |

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

|    1 | SIMPLE      | performance_test | range | int_type_date | int_type_date | 4       | NULL | 24998328 | Using where; Using index |

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

1 row in set (0.00 sec)


MariaDB [performance_test]>



두가지 형태를 explain을 해본결과 인덱싱은 동일하게 타지만, key_len이 달랐다.

datetime은 8byte 이고, int는 4byte 이다. (음.. 그럼 bigint 로 하게되면 유사한 결과가 나올지. 나중에 한번 더 해봐야 겟다.)

아무래도 포현범위가 크게 되면 메모리사용량이 많아 지게 되고 I/O도 그에 준하게 발생 할것이여서 가능하면 대용량 데이터를 컨트롤 할때는 숫자형이라도 tinyint ~ bigint 까지 적절히 사용하는것이 좋겠다.


unixtime을 int 형태로 표현 할 수 있는 이유는

"1970-01-01 00:00:00 GMT" 에서 부터 1초씩 증가해서 "2038-01-19 03:14:07" 까지 이고

해당 값음 ,

(1970-01-01 이전 날짜는 -로 표현 된다. )

이유는 32비트 int 형태의 정수표현범위 -2147483646 ~ 2147483647 까지 이기 때문이다,

따라서, 위의 시간이 지나게 되면, 이론적으로는 다시 -2147483646로 표현된다. 

그러면 과거 첫날짜가 되야 되는데.. MySQL은 Null 이라고 하네. 희얀한데... 학교에서는 다시 꺼구로 돌아가는것으로 배웠는데.



이유는 signed int 2147483646는 이진수로 "0111 1111 1111 1111 1111 1111 1111 1111"인데, 여기에 1을 더하면

"1000 0000 0000 0000 0000 0000 0000 0000" 이 되어야 하는데 제일 앞 bit는 Sign bit 임으로 1이면 - 가 된다.(맞나?) ㅋㅋ

(이게 Signed 일때와 unSign 일때 처리 방법이 달라지는데, 너무 오래 전에 배운것이라 기억이 정확하진 않지만,

여하튼 숫자 범위가 넘어가면 더 이상 증가 하지 않는다. 강제로 증가를 시키게 되면 overflow 가 발생 한다.)



unixtime은 2038년 이후를 고려하지 않아도 될 경우에만 사용하도록 한다.

보통 S/W lifecycle 은 5년임으로 최소한 2033년 까지는 걱정 안해도 되지 않을까? ^^(이기적인 생각)

내 나이 50이 넘어서까지 코딩을 하고 있을면...........

그리고 이 세상에 천재같은 사람들이 Y2K때 처럼 기가 막힌 방법을 만들어 낼것이기 때문에 크게 걱정할 필요가 없다.


2. BETWEEN

결론부터 말하면 생각하지 못했던 결과가 나왔다

그 동안 책에서 읽어서, 경험적으로 구간 검색은 between을 사용하는게 효율이 좋다는것을 알고 있었으나 

아래 결과로 확실한 결과가 나왔다.

MariaDB [performance_test]> SELECT SQL_NO_CACHE COUNT(id)

    -> FROM performance_test

    -> WHERE date_type_date between '2000-06-01 00:00:00' AND '2000-07-01 00:00:00'

    -> ;

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

| COUNT(id) |

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

|  12960005 |

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

1 row in set (3.55 sec)


MariaDB [performance_test]> SELECT SQL_NO_CACHE COUNT(id)

    -> FROM performance_test

    -> WHERE int_type_date BETWEEN UNIX_TIMESTAMP('2000-06-01 00:00:00') AND UNIX_TIMESTAMP('2000-07-01 00:00:00')

    -> ;

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

| COUNT(id) |

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

|  12960005 |

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

1 row in set (3.35 sec)


MariaDB [performance_test]>

datetime 을 산술연산자로 비교하는 것보다 between을 사용하는것이 효율이 훨씬 좋왔다.


condition

 datetime / > , <

 datetime / between

unixtime / between

 unixtime / > , <

 Result

 13.61s

 3.55s

 3.35s

 3.14s

여러번 수행하면 약간씩의 차이는 있으나, 전체적인 검색 우위에 영향을 주는 범위는 아니였다.


결론. datetime을 대소 비교로 구간 검색을 할 경우 데이터가 누적 될수록 속도가 현저히 느려지는 현상이 발생 한다.

datetime형 필드를 구간 검색을 할때는 반드시 between을 사용하는 것을 추천한다.


오랜만에 이런걸 해보니 작성하는데 한참 걸리네 ㅎ

이상.


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 (int type) #3 (1.5억건)  (0) 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

이번엔 좀 더 확실한 테스트를 위하여

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

이전글에서 생성한 6.6천만건의 데이터를 DB에 넣고 쿼리 테스트를 해본다.

예상되는 결과는 int 형이 압승일것 같은데, 일단 해보자.


실제 데이터가 들어 있는 테이블을 가정하기 위하여 

auto increment 를 넣고, 문자열, datetime 과 int type으로 테이블을 생성 하였다.


테이블 스크립트는 다음과 같다.

(일단 인덱스 없이 테스트하고, 추후 인덱스를 추가해서 얼마나 차이나는지를 확인 해보겠다.)

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`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8  


데이터는 2000년 1월 1일 ~ 2001년 1월 1일 까지 데이터를 생성 하였다.

unix time으로는   946684800 ~ 978343199 (GMT +9 기준으로.)

MariaDB [performance_test]> select max(date_type_date), min(date_type_date) from performance_test;

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

| max(date_type_date) | min(date_type_date) |

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

| 2001-01-01 09:59:59 | 2000-01-01 00:00:00 |

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

1 row in set (2 min 9.18 sec)


검색기간 : 2001-06-01 ~ 2000-06-30


1. datetime 필드를 이용하여 검색(인덱스 없이)

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) |

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

|   5399995 |

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

1 row in set (2 min 12.10 sec)


MariaDB [performance_test]> 




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) |

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

|   5400000 |

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

1 row in set (2 min 11.18 sec)


MariaDB [performance_test]>  

어라.. 큰차이가 없다. (갯수가 다른것은 실은 조건식이 조금 잘못 됫다.^^)



[ 인덱스 추가 ]

MariaDB [performance_test]> ALTER TABLE `performance_test`

    -> ADD INDEX `date_type_date` (`date_type_date`),

    -> ADD INDEX `int_type_date` (`int_type_date`);

Stage: 1 of 1 'altering table'   99.8% of stage done


Query OK, 0 rows affected (13 min 3.47 sec)

Records: 0  Duplicates: 0  Warnings: 0


MariaDB [performance_test]>

데이터가 많긴 많데, 필드 2개를 인덱싱하는데 13분이나.. 

어찌됫것.. 다시 테스트..



3. 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) |

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

|   5399995 |

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

1 row in set (1.79 sec)


MariaDB [performance_test]> 


4. 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) |

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

|   5400000 |

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

1 row in set (1.30 sec)


MariaDB [performance_test]> 


음... 생각보다 크진않네.;;

여러번 돌려봤는데, 

날짜형으로 비교 했을때는 1.7초대, 

숫자형으로 비교 했을때는 1.3초대. 약 0.4초 정도의 차이를 갖고 있다.

int 가 약 23%정도의 속도 향상이 있다.


좀 더 많은 데이터를 넣었을때 동일한 비율로 높아지는지 한번더 확인 해봐야 겟다.

최종결론은 1.5억건을 넣고 비교한 후 결론을 정리 하도록 하겠다.



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) #3 (1.5억건)  (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

오늘은 그동안 궁금했던

MYSQL 의 날짜 비교에 대하여 대량데이터를 기반으로 작성해본다.


DATETIME 형태와  Unix time을 int 형태로 디비에 저장하고,

속도 비교를 해본다.


먼저 대량 데이터를 만들기 위해서,

간단히 PHP Script 로 데이터를 생성 하였다.


1억건 이상의 데이터를 만들기 위해서

초당5건씩 1년치 데이터를 생성해 보았다.

5 * 3600sec * 24h * 365day = 157,680,000 (약 1.5억건)



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;

Stage: 2 of 2 'End bulk insert'      0% of stage done


Query OK, 66060000 rows affected, 65535 warnings (23 min 57.18 sec)

Records: 66060000  Deleted: 0  Skipped: 0  Warnings: 66060000

 


엇. 그런데 6.6천만건만 들어갓다..;;

데이터가 잘못 만들어진것 같다.


뭐 어찌 됫건, 6천만건도 적은 데이터는 아니기 때문에, 그냥 6천만건으로 진행 해본다.


참고로 PHP로 Raw data file 를 생성하는데 이 데이터를 생성하는것도 싱글프로세스로 하게 되면,

상당히 오랜 시간이 걸리기 때문에 fork를 이용해서 멀티 쓰레드로 구현해봤다.

(참고로 fork는 Unix/Linux 계열에서만 지원하는 것이기 때문에 윈도우php에서는 안될것이다.)


검색하다가 CPU 갯수만큼 쓰래드를 생성시켜서 돌리는 소스를 갖어다가 만들었는데,

검색 창이 닫혀서 출처를 찾을 수가 없네.;


멀티프로세스에 관심있거나, 직접 테스트를 해보고 싶은 사람은 참고해 본다. 

flat 데이터 생성 프로그램은 다음과 같다



int 와 datetime 성능의 상대적인 성능차를 확인 하기 위한 테스트이기 때문에

테스트 머진 성능은 중요하지 않으나, 다음과 같다.

- Maria DB 10.x 

- CentOS 7 64 bit

- Memory 8GB

- Core : 4 Core


MySQL ini 파일은  my-innodb-heavy-4G.cnf을 변경없이 그대로 사용하였다.



실제 DB 쿼리 테스트는 다음 글에서.....

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 (int type) #3 (1.5억건)  (0) 2018.02.27
MySQL InnoDB DATETIME vs Unixtime (int type) #2  (0) 2018.02.27
MariaDB/Galera Cluster 기술 노트!!  (0) 2015.04.16
Haproxy로 .mysql 서비스까지 확인하도록 xinet 사용  (0) 2015.04.16
MySQL Longquery Email로 받기  (0) 2015.03.06

+ Recent posts

티스토리툴바