Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
<?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

This comment has been minimized.

Copy link
Owner Author

php-cpm commented Jul 24, 2018

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

@php-cpm

This comment has been minimized.

Copy link
Owner Author

php-cpm commented Jul 24, 2018

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);
}
@php-cpm

This comment has been minimized.

Copy link
Owner 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

This comment has been minimized.

Copy link
Owner 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

This comment has been minimized.

Copy link
Owner 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
You can’t perform that action at this time.