Saturday, July 21, 2012

MySQL or MySQLi or PDO

Lessons learned:
  • MySQLi is 3-4 times slower than MySQL when fetching less then 500 datasets
  • MySQLi is 2-4 times faster than MySQL when fetching more than 500 datasets
  • PDO is 2-5 times slower than MySQL/MySQLi
  • Unbuffered queries are 15-40 percent faster than buffered queries in MySQLi
  • Unbuffered queries are 10-25 percent faster than buffered queries in MySQL for less than 10000 datasets
  • Unbuffered queries are 3-7 percent slower than buffered queries in MySQL for more than 10000 datasets
  • Unbuffered queries are 0-5 percent faster than buffered queries in PDO
  • Non thread safe versions of PHP on win32 are 50 percent faster than thread safe versions

Here is the test script:
$table = 'test1.test2';
benchmark($table, 100);
benchmark($table, 500);
benchmark($table, 1000);
benchmark($table, 5000);
benchmark($table, 10000);
benchmark($table, 50000);
benchmark($table, 100000);

function benchmark($table, $size) {
  mysql_connect('127.0.0.1', 'root', '');
  mysql_query('drop table if exists '.$table);
  mysql_query("CREATE TABLE $table (id int(11) AUTO_INCREMENT,
    str1 varchar(255), str2 varchar(255), PRIMARY KEY (id)) ENGINE=INNODB");
  mysql_query("begin");
  for ($i=0; $i<$size; $i++) {
    mysql_query("insert into $table values(null, 'hello$i', 'world$i')");
  }
  mysql_query("commit");
  // warm up mysql cache
  $db = new PDO('mysql:host=127.0.0.1', 'root', '');
  foreach ($db->query('select * from '.$table) as $vals) $test = $vals;


  $start = microtime(true);
  mysql_connect('127.0.0.1', 'root', '');
  $result = mysql_query('select * from '.$table);
  while ($row = mysql_fetch_assoc($result)) $test = $row;
  echo $size.' mysql-buffered '.number_format(microtime(true)-$start, 5)."\n";

  $start = microtime(true);
  mysql_connect('127.0.0.1', 'root', '');
  $result = mysql_unbuffered_query('select * from '.$table);
  while ($row = mysql_fetch_assoc($result)) $test = $row;
  echo $size.' mysql-unbuffered '.number_format(microtime(true)-$start, 5)."\n";

  $start = microtime(true);
  $db = mysqli_connect('127.0.0.1', 'root', '');
  foreach (mysqli_query($db, 'select * from '.$table) as $row) $test = $row;
  echo $size.' mysqli-buffered '.number_format(microtime(true)-$start, 5)."\n";

  $start = microtime(true);
  $db = mysqli_connect('127.0.0.1', 'root', '');
  foreach (mysqli_query($db, 'select * from '.$table, MYSQLI_USE_RESULT)
    as $row) $test = $row;
  echo $size.' mysqli-unbuffered '.number_format(microtime(true)-$start, 5)."\n";

  $start = microtime(true);
  $db = new PDO('mysql:host=127.0.0.1', 'root', '');
  foreach ($db->query('select * from '.$table) as $vals) $test = $vals;
  echo $size.' pdo-buffered '.number_format(microtime(true)-$start, 5)."\n";

  $start = microtime(true);
  $db = new PDO('mysql:host=127.0.0.1', 'root', '',
    array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));
  foreach ($db->query('select * from '.$table) as $vals) $test = $vals;
  echo $size.' pdo-unbuffered '.number_format(microtime(true)-$start, 5)."\n";
}
Tests were made with PHP 5.3.10, MySQL 5.5.29, Kernel 3.2.0, 64bit, 3.4GHz (QEMU), values in seconds:
100 mysql-buffered 0.00011
100 mysql-unbuffered 0.00015
100 mysqli-buffered 0.00041
100 mysqli-unbuffered 0.00032
100 pdo-buffered 0.00043
100 pdo-unbuffered 0.00041
500 mysql-buffered 0.00034
500 mysql-unbuffered 0.00045
500 mysqli-buffered 0.00033
500 mysqli-unbuffered 0.00024
500 pdo-buffered 0.00058
500 pdo-unbuffered 0.00058
1000 mysql-buffered 0.00059
1000 mysql-unbuffered 0.00064
1000 mysqli-buffered 0.00037
1000 mysqli-unbuffered 0.00030
1000 pdo-buffered 0.00090
1000 pdo-unbuffered 0.00096
5000 mysql-buffered 0.00288
5000 mysql-unbuffered 0.00290
5000 mysqli-buffered 0.00077
5000 mysqli-unbuffered 0.00054
5000 pdo-buffered 0.00340
5000 pdo-unbuffered 0.00341
10000 mysql-buffered 0.00564
10000 mysql-unbuffered 0.00580
10000 mysqli-buffered 0.00123
10000 mysqli-unbuffered 0.00079
10000 pdo-buffered 0.00665
10000 pdo-unbuffered 0.00656
50000 mysql-buffered 0.04469
50000 mysql-unbuffered 0.04609
50000 mysqli-buffered 0.01915
50000 mysqli-unbuffered 0.01735
50000 pdo-buffered 0.04679
50000 pdo-unbuffered 0.04587
100000 mysql-buffered 0.08471
100000 mysql-unbuffered 0.09054
100000 mysqli-buffered 0.03967
100000 mysqli-unbuffered 0.03343
100000 pdo-buffered 0.09257
100000 pdo-unbuffered 0.09148

3 comments:

  1. Replies
    1. 5.5.29, I made a new test with new hardware.

      Delete
  2. Nice one.. But try ro use more columns,or use joins. For example, I modified it to 10 columns and left-joined with another 10 column and here are results (unbuffered only):

    100 mysql 0.00547
    100 mysqli 0.00471
    100 pdo 0.00475
    500 mysql 0.02040
    500 mysqli 0.01142
    500 pdo 0.01372
    1000 mysql 0.02980
    1000 mysqli 0.02943
    1000 pdo 0.03326
    5000 mysql 0.17732
    5000 mysqli 0.10896
    5000 pdo 0.13948
    10000 mysql 0.38488
    10000 mysqli 0.21087
    10000 pdo 0.27576

    ReplyDelete

Labels

performance (23) benchmark (6) MySQL (5) architecture (5) coding style (5) memory usage (5) HHVM (4) C++ (3) Java (3) Javascript (3) MVC (3) SQL (3) abstraction layer (3) framework (3) maintenance (3) Go (2) Golang (2) HTML5 (2) ORM (2) PDF (2) Slim (2) Symfony (2) Zend Framework (2) Zephir (2) firewall (2) log files (2) loops (2) quality (2) real-time (2) scrum (2) streaming (2) AOP (1) Apache (1) Arrays (1) C (1) DDoS (1) Deployment (1) DoS (1) Dropbox (1) HTML to PDF (1) HipHop (1) OCR (1) OOP (1) Objects (1) PDO (1) PHP extension (1) PhantomJS (1) SPL (1) SQLite (1) Server-Sent Events (1) Silex (1) Smarty (1) SplFixedArray (1) Unicode (1) V8 (1) analytics (1) annotations (1) apc (1) archiving (1) autoloading (1) awk (1) caching (1) code quality (1) column store (1) common mistakes (1) configuration (1) controller (1) decisions (1) design patterns (1) disk space (1) dynamic routing (1) file cache (1) garbage collector (1) good developer (1) html2pdf (1) internationalization (1) invoice (1) just-in-time compiler (1) kiss (1) knockd (1) legacy code (1) legacy systems (1) logtop (1) memcache (1) memcached (1) micro framework (1) ncat (1) node.js (1) openssh (1) pfff (1) php7 (1) phpng (1) procedure models (1) ramdisk (1) recursion (1) refactoring (1) references (1) regular expressions (1) search (1) security (1) sgrep (1) shm (1) sorting (1) spatch (1) ssh (1) strange behavior (1) swig (1) template engine (1) threads (1) translation (1) ubuntu (1) ufw (1) web server (1) whois (1)