MySQL InnoDB DATETIME vs Unixtime (int type) #1

오늘은 그동안 궁금했던

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 데이터 생성 프로그램은 다음과 같다

<?php

error_reporting(E_ALL & ~E_NOTICE);

$fp = fopen(dirname(__FILE__).'/testdata.txt','w');

fputs($fp,"id,str100byte,date_type_date,int_type_date\n");

fclose($fp);

// 동시 실행 프로세스 수

$maxProcCount = getCpuCount();

//$maxProcCount += 2;

echo "Cpu count ". $maxProcCount."\n";

$i = 946684800;

$step = 3600*24;

echo ">>>". pcntl_waitpid(0, $status) ."\n";

for ( $j = 0 ; $j < $maxProcCount; $j++)

{

    makefile( $i );

    $i += $step;

}

while (pcntl_waitpid(0, $status) != -1) {

    //$status = pcntl_wexitstatus($status);

    if ( $i > 978307200 ) continue;

    // job 할당

    makefile($i);

    $i += $step;

}

function makefile($a)

{

        $pid = pcntl_fork();

    // error

    if ($pid == -1) {

        echo '[ERROR] could not fork'."\n";

        exit;

    }

    // child

    else if ($pid == 0) {

        // 단위계산 수행

        $startTime = microtime(true);

        $fp = fopen(dirname(__FILE__).'/testdata.txt','a');

        $end = $a + (3600*24); // 여기가 10으로 되어 있었네;;

//      echo "start .. " . gmdate("Y-m-d H:i:s", $a)."..\n";

        for ($i = $a; $i<$end ;$i+=1)

        {

                fprintf($fp,"null,\"%s\",\"%s\",%s\n",RandomString(100), gmdate("Y-m-d H:i:s", $i), $i);

                fprintf($fp,"null,\"%s\",\"%s\",%s\n",RandomString(100), gmdate("Y-m-d H:i:s", $i), $i);

                fprintf($fp,"null,\"%s\",\"%s\",%s\n",RandomString(100), gmdate("Y-m-d H:i:s", $i), $i);

                fprintf($fp,"null,\"%s\",\"%s\",%s\n",RandomString(100), gmdate("Y-m-d H:i:s", $i), $i);

                fprintf($fp,"null,\"%s\",\"%s\",%s\n",RandomString(100), gmdate("Y-m-d H:i:s", $i), $i);

                //fprintf($fp,"null,%s,%s,%s\n",RandomString(100), gmdate("Y-m-d H:i:s", $i), $i);

                if ( $i % 1000 == 0 )

                {

                        usleep(1);

                }

        }

        fclose($fp);

        printf("end .. %s, (%.3f sec)\n", gmdate("Y-m-d", $a) , microtime(true) - $startTime);

        exit;

    }

    // parent

    else {

    }

}

function RandomString($len = 10)

{

    $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

    $randstring = '';

    $strlen = strlen($characters);

    for ($i = 0; $i < $len ; $i++) {

        $randstring .= $characters[rand(0, $strlen)];

    }

    return $randstring;

}

// CPU 프로세서 수 반환

function getCpuCount()

{

    return substr_count(file_get_contents('/proc/cpuinfo'), 'processor');

}

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