mysql_benchmark.php

<?php
// refer & edit from https://odino.org/redis-slow-with-php-think-again/
$conn  = mysqli_connect('127.0.0.1', 'root');
mysqli_select_db($conn, 'mysql_benchmark');

for ($i = 0; $i < 100; $i++) {
  $start = microtime(true);
  for ($j = 0; $j < 10000; $j++) {
    $query = mysqli_query($conn, 'SELECT * FROM users WHERE id = 1');
    $key = sprintf("key:%05d", $j);

    $persone = mysqli_fetch_row($query);
  }
  $time = microtime(true)-$start;
  printf("%6d req/sec\n", $j/$time);
}

on my laptop MacBook Pro (13-inch, 2017, Two Thunderbolt 3 ports) CPU 2.3 GHz Intel Core i5

result is 6630 req/sec AVG

old version which only run mysql command to fetch data get better result

34048 req/sec AVG

<?php
$conn  = mysqli_connect('127.0.0.1', 'root');
mysqli_select_db($conn, 'mysql_benchmark');

$query = mysqli_query($conn, 'SELECT * FROM users WHERE id = 1');
for ($i = 0; $i < 100; $i++) {
  $start = microtime(true);
  for ($j = 0; $j < 10000; $j++) {
    $key = sprintf("key:%05d", $j);
    $persone = mysqli_fetch_row($query);
  }
  $time = microtime(true)-$start;
  printf("%6d req/sec\n", $j/$time);
}

ALL MY MISTAKE!

I used php 7.1.19 + opcache + xdebug opened

after I comment out xdebug extension I ran again and got

10601 req/sec compare to 6630 req/sec

5288493 req/sec compare to 34048 req/sec

<?php
//mysqli function example
$conn = mysqli_connect('127.0.0.1', 'root', '', 'redis_benchmark');
$avg = [];
for ($i = 0; $i < 20; $i++) {
  $start = microtime(true);
  for ($j = 0; $j < 10000; $j++) {
    $key = sprintf("key:%05d", $j);
$query = mysqli_query($conn, 'SELECT * FROM users WHERE id = 1');
    $persone = mysqli_fetch_row($query);
  }
  $time = microtime(true)-$start;

  $avg[] = intval($j/$time);
  printf("%6d req/sec\n", $j/$time);
}

echo array_sum($avg) / count($avg) . " req/sec avg";

<?php
//mysqli class example
$conn = new mysqli('127.0.0.1', 'root', '', 'redis_benchmark');
$avg = [];
for ($i = 0; $i < 20; $i++) {
  $start = microtime(true);
  for ($j = 0; $j < 10000; $j++) {
    $key = sprintf("key:%05d", $j);
$query = $conn->query('SELECT * FROM users WHERE id = 1');
    $persone = $query->fetch_row();
  }
  $time = microtime(true)-$start;

  $avg[] = intval($j/$time);
  printf("%6d req/sec\n", $j/$time);
}

echo array_sum($avg) / count($avg) . " req/sec avg";

<?php
//new pdo example
$conn = new PDO('mysql:host=127.0.0.1;port=3306;dbname=redis_benchmark;charset=UTF8;','root','', array(PDO::ATTR_PERSISTENT=>true));
$avg = [];
for ($i = 0; $i < 20; $i++) {
  $start = microtime(true);
     $query = 'SELECT * FROM users WHERE id = 1';
     $stmt=$conn->prepare($query);
  for ($j = 0; $j < 10000; $j++) {
     $key = sprintf("key:%05d", $j);
     $stmt->execute();
     do { $stmt->fetch(); $stmt->closeCursor();} while($stmt->nextRowset());
  }
  $time = microtime(true)-$start;

  $avg[] = intval($j/$time);
  printf("%6d req/sec\n", $j/$time);
}

echo array_sum($avg) / count($avg) . " req/sec avg";

driver	query and fetch	only fetch
mysqli function 127.0.0.1	10884.35	5523957.12
mysqli class 127.0.0.1	10894.45	5388053.32
mysqli class localhost	14454.55	5388053.32
PDO mysql 127.0.0.1	10339.99	3862854.09
PDO mysql localhost	14512.27	4034046.15

mysql connect localhost using Unix socket, it's faster than TCP connect when using ip

mention

PDO::ATTR_PERSISTENT means

• the PDO connection holds until process ended
• 1 php-fpm holds 1 mysql connection
• if you have 30 machines each 100 php-fpm, once you use persistent mysql connection, your MySQL server will get 30 * 100 threads connected, and yet if your server set max threads less than it ,your client get error.

sudo dtruss -c php mysql.php

CALL                                        COUNT
bsdthread_register                              1
connect                                         1
fchmod                                          1
ftruncate                                       1
getentropy                                      1
getpid                                          1
getrlimit                                       1
getuid                                          1
shm_open                                        1
sigprocmask                                     1
thread_selfid                                   1
csops                                           2
fstatat64                                       2
fstatfs64                                       2
getegid                                         2
gettid                                          2
issetugid                                       2
openat_nocancel                                 2
socket                                          2
unlink                                          2
access                                          3
geteuid                                         3
getdirentries64                                 4
sysctl                                          4
fcntl_nocancel                                  5
madvise                                         6
pread                                          12
open                                           13
close                                          15
fcntl                                          16
lstat64                                        17
mprotect                                       17
readlink                                       18
read_nocancel                                  19
munmap                                         35
lseek                                          41
sigaction                                      45
close_nocancel                                 48
open_nocancel                                  50
stat64                                         56
fstat64                                        58
ioctl                                          73
mmap                                           75
getattrlist                                   132
sendto                                       1556
recvfrom                                     3233
poll                                         3234