'benchmarktest'에 해당되는 글 1건

MySQL mysql vs mysqli vs pdo Benchmark Test



mysql, mariadb 모두 테스트를 해보았지만 결과는 거의 비슷하게 나왔습니다.


처리 속도 Benchmark 테스트는 아래의 PHP 소스 코드로 진행하였습니다.



▶ 벤치마크 테스트 소스


<?php

error_reporting(E_ALL);

ini_set('display_errors', TRUE);

ini_set('display_startup_errors', TRUE);

set_time_limit(0);


$db_addr = '127.0.0.1';

$db_user = 'user';

$db_pass = 'pass';

$table = 'test.benchmark_table';


benchmark($table, 100000);


function benchmark($table, $rows) {

  global $db_addr;

  global $db_user;

  global $db_pass;

  $table_innodb1 = $table."_innodb1";

  $table_innodb2 = $table."_innodb2";

  $table_innodb3 = $table."_innodb3";

  $table_myisam1 = $table."_myisam1";

  $table_myisam2 = $table."_myisam2";

  $table_myisam3 = $table."_myisam3";


  mysql_connect($db_addr, $db_user, $db_pass);

  mysql_query('drop table if exists '.$table_innodb1);

  mysql_query('drop table if exists '.$table_innodb2);

  mysql_query('drop table if exists '.$table_innodb3);

  mysql_query('drop table if exists '.$table_myisam1);

  mysql_query('drop table if exists '.$table_myisam2);

  mysql_query('drop table if exists '.$table_myisam3);

  mysql_query("CREATE TABLE $table_innodb1 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");

  mysql_query("CREATE TABLE $table_innodb2 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");

  mysql_query("CREATE TABLE $table_innodb3 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");

  mysql_query("CREATE TABLE $table_myisam1 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");

  mysql_query("CREATE TABLE $table_myisam2 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");

  mysql_query("CREATE TABLE $table_myisam3 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");


  mysql_query("begin");

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

    mysql_query("insert into $table_innodb1 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_innodb2 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_innodb3 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_myisam1 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_myisam2 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_myisam3 values (0, 'benchmark test $i', rand() * $rows * 100)");

  }

  mysql_query("commit");


  mysql_conn1($db_addr, $db_user, $db_pass, $table_innodb1);

  mysql_conn2($db_addr, $db_user, $db_pass, $table_innodb2);

  mysql_conn3($db_addr, $db_user, $db_pass, $table_innodb3);

  mysql_conn1($db_addr, $db_user, $db_pass, $table_myisam1);

  mysql_conn2($db_addr, $db_user, $db_pass, $table_myisam2);

  mysql_conn3($db_addr, $db_user, $db_pass, $table_myisam3);

}


function mysql_conn1($db_addr, $db_user, $db_pass, $table) {

  $i = 0;

  $start = get_time();

  $db = mysql_connect($db_addr, $db_user, $db_pass);

  $result = mysql_query('select * from '.$table, $db);

  while ($row = mysql_fetch_assoc($result)) { $i++; $test = $row; }

  mysql_close($db);

  echo 'rows : '.$i.' '.$table.' mysql '.round(get_time()-$start, 5)."<br>";

}


function mysql_conn2($db_addr, $db_user, $db_pass, $table) {

  $i = 0;

  $start = get_time();

  $db = new mysqli($db_addr, $db_user, $db_pass);

  $result = $db->query('select * from '.$table);

  while ($row = $result->fetch_assoc()) { $i++; $test = $row; }

  $db->close();

  echo 'rows : '.$i.' '.$table.' mysqli '.round(get_time()-$start, 5)."<br>";

}


function mysql_conn3($db_addr, $db_user, $db_pass, $table) {

  $i = 0;

  $start = get_time();

  $db = new PDO('mysql:host='.$db_addr, $db_user, $db_pass);

  foreach ($db->query('select * from '.$table) as $row) { $i++; $test = $row; }

  $db = null;

  echo 'rows : '.$i.' '.$table.' pdo '.round(get_time()-$start, 5)."<br>";

}


function get_time() {

  $t = explode(' ', microtime());

  return ((float)$t[0] + (float)$t[1]);

}

?>



▶ MySQL 5.1.73 결과


rows : 100000 redblue.benchmark_table_innodb1 mysql  0.39674
rows : 100000 redblue.benchmark_table_innodb2 mysqli 0.3815
rows : 100000 redblue.benchmark_table_innodb3 pdo    0.33571


rows : 100000 redblue.benchmark_table_myisam1 mysql  0.11391
rows : 100000 redblue.benchmark_table_myisam2 mysqli 0.11894
rows : 100000 redblue.benchmark_table_myisam3 pdo    0.12141



▶ MariaDB 5.5.52 결과


rows : 100000 test.benchmark_table_innodb1 mysql  0.2214
rows : 100000 test.benchmark_table_innodb2 mysqli 0.37862
rows : 100000 test.benchmark_table_innodb3 pdo    0.28196


rows : 100000 test.benchmark_table_myisam1 mysql  0.22379
rows : 100000 test.benchmark_table_myisam2 mysqli 0.19565
rows : 100000 test.benchmark_table_myisam3 pdo    0.28953


다른 분들이 테스트 한 결과에는 mysqli 가 좀 더 빠르던데 저는 이상하게 거의 비슷하게 결과가 나오는군요~


그나마 mariadb 에서는 효과가 있는 듯 합니다.


블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

댓글을 달아 주세요