Skip to content

Instantly share code, notes, and snippets.

@php-cpm
Created July 24, 2018 09:37
Show Gist options
  • Save php-cpm/dc5bc659ecb7732ccc890d852b5ee56e to your computer and use it in GitHub Desktop.
Save php-cpm/dc5bc659ecb7732ccc890d852b5ee56e to your computer and use it in GitHub Desktop.
<?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);
}
@php-cpm
Copy link
Author

php-cpm commented Jul 24, 2018

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-cpm
Copy link
Author

php-cpm commented Jul 27, 2018

<?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.

@php-cpm
Copy link
Author

php-cpm commented Jul 30, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment