Mastering mysqlnd - Speaker Deck

Slide 1

Slide 1 text

2014/10/11 PHP Conference Japan 2014 do_aki 1 updated 2014-10-21

Slide 2

Slide 2 text

@do_aki ＠do_aki http://do-aki.net/ 2

Slide 3

Slide 3 text

agenda 1. about mysqlnd – position, role 2. libmysql vs mysqlnd – functions, features 3. mysqlnd internals I. Type of returning values II.Memory usage 3

Slide 4

Slide 4 text

1.about mysqlnd 4

Slide 5

Slide 5 text

MySQL client libraries in PHP mysql_connect mysqli PDO Doctrine Eloquent ZendDB Aura.Sql ADOdb PEAR_MDB2 PEAR_DB 5

Slide 6

Slide 6 text

Those libraries use mysql/mysqli/pdo extensions 6 MySQL client libraries in PHP is / use mysql extension mysqli extension pdo extension (mysql driver)

Slide 7

Slide 7 text

mysql / mysqli / pdo (mysql_driver) access MySQL server directly? 7

Slide 8

Slide 8 text

No 8

Slide 9

Slide 9 text

Communication between MySQL server and PHP • Network access (tcp, socket) • Encryption (SSL) or not • Analyze Wire Protocol • Compression (deflate) or not • Convert to (or from) PHP variables • Error Handring (Exception) 9

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Chart of Doctrine Zend_DB ADOdb mysqli PEAR::MDB2 PDO mysql Application mysqlnd libmysql Zend Engine PHP Script MySQL Server ref:http://d.hatena. ne.jp/do_aki/2011121 4/1323831558 11

Slide 12

Slide 12 text

2.libmysql vs mysqlnd 12

Slide 13

Slide 13 text

Chart of Doctrine Zend_DB ADOdb mysqli PEAR::MDB2 PDO mysql Application mysqlnd libmysql Zend Engine PHP Script MySQL Server ref:http://d.hatena. ne.jp/do_aki/2011121 4/1323831558 13

Slide 14

Slide 14 text

libmysql vs mysqlnd • libmysqlclient (Connector/C) – C library – Oracle (MySQL AB) – matured • mysqlnd – PHP extension – PHP Community (Andrey, Johannes, Ulf) – relatively recent 14

Slide 15

Slide 15 text

compile options (example) • libmysql ./configure \ --with-mysql=/usr \ --with-mysqli=/usr/bin/mysql_config \ --with-pdo-mysql=/usr • mysqlnd ./configure \ --with-mysql=mysqlnd \ --with-mysqli=mysqlnd \ --with-pdo-mysql=mysqlnd 15

Slide 16

Slide 16 text

mysqlnd and php versions • not available (only libmysql) before php 5.3 • bundled but optional (default is libmysql) php 5.3 series • default (libmysql is optional) php 5.4 or later 16 *libmysql is not deprecated

Slide 17

Slide 17 text

which is used? • php –i or phpinfo() – mysql : Client API version – mysqli : Client API library version – pdo_mysql : Client API version mysqli : mysqli_get_client_info() PDO : $pdo->getAttribute( PDO::ATTR_CLIENT_VERSION) • include “mysqlnd” string or not 17 same as

Slide 18

Slide 18 text

pros • resolve license problems – libmysql: GPLv2 with FOSS License Exception – mysqlnd : PHP license • not need build libmysqlclient before compile PHP – mysqlnd is bundled in php source code • “highly optimized for and tightly integrated into PHP” 18

Slide 19

Slide 19 text

cons • not yet matured – libmysqlclient is widely used. at any os, as any language bindings… – mysqlnd is limited used. compared with libmysql 19

Slide 20

Slide 20 text

incompatibilities • mysqlnd cannot use OLD_PASSWORD – not support MySQL server before 4.1 – "mysqlnd cannot connect to MySQL 4.1+ using old authentication" • mysqlnd don’t read “my.cnf” – affect charset – no PDO::MYSQL_ATTR_READ_DEFAULT_FILE • Type of returning value is different – example: BIT type written by manual – It mentions later 20

Slide 21

Slide 21 text

mysqlnd only • Asynchronous, non-blocking queries – MYSQLI_ASYNC (mysqli::query) – mysqli::reap_async_query – mysqli::poll • performance statistics – mysqli_get_client_stats – mysqli::get_connection_stats – http://php.net/manual/mysqlnd.stats.php • functions – mysqli_stmt::get_result – mysqli_result::fetch_all 21

Slide 22

Slide 22 text

plugins • mysqlnd_ms – Replication and load balancing – http://pecl.php.net/package/mysqlnd_ms • mysqlnd_qc – Client side query cache – http://pecl.php.net/package/mysqlnd_qc • mysqlnd_uh – MySQLnd Userland Handler – http://pecl.php.net/package/mysqlnd_uh • mysqlnd_memcache – Translating SQL for InnoDB Memcached – http://pecl.php.net/package/mysqlnd_memcache 22

Slide 23

Slide 23 text

plugins (removed?) • mysqlnd_mux – Simuler mysqlnd_ms? • mysqlnd_pscache – Prepared statement cache • mysqlnd_sip – SQL Injection Protection • mysqlnd_mc – Multi Connect 23

Slide 24

Slide 24 text

3.mysqlnd internals 24

Slide 25

Slide 25 text

type of prepared statement Server side prepared statement Client side prepared statement prepare COM_PREPARE SELECT age FROM user WHERE name = ? execute COM_EXECUTE bind parameter "do_aki" prepare SELECT age FROM user WHERE name = ? execute COM_QUERY SELECT age FROM user WHERE name = ‘do_aki’ 25 [default PDO settings] [Preventing SQL Injection]

Slide 26

Slide 26 text

prepared statement and protocols Server side prepared statement Client side prepared statement prepare COM_PREPARE SELECT age FROM user WHERE name = ? execute COM_EXECUTE bind parameter "do_aki" result set 29 (integer) prepare SELECT age FROM user WHERE name = ? execute COM_QUERY SELECT age FROM user WHERE name = ‘do_aki’ result set “29” (string) text protocol binary protocol 26

Slide 27

Slide 27 text

I.Type of returning values 27

Slide 28

Slide 28 text

example mysql> CREATE TABLE bits( id int, b bit(8), f float ); mysql> insert into bits values (1, b'1010', 1.1); 28

Slide 29

Slide 29 text

PDO text protocol $pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, true); $stmt = $pdo->prepare("SELECT * FROM bits"); $stmt->execute(); var_dump($stmt->fetchAll(PDO::FETCH_ASSOC); binary protocol $pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false); $stmt = $pdo->prepare("SELECT * FROM bits"); $stmt->execute(); var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)); 29

Slide 30

Slide 30 text

PDO (libmysql) text protocol array(1) { [0]=> array(3) { [“id”]=> string(1) "1" [“b”]=> string(1) " " [“f”]=> string(3) "1.1" } } binary protocol array(1) { [0]=> array(3) { [“id”]=> string(1) "1" [“b”]=> string(1) " " [“f”]=> string(3) "1.1" } } “b” value is 0x10 30

Slide 31

Slide 31 text

PDO (mysqlnd) text protocol array(1) { [0]=> array(3) { [“id”]=> string(1) "1" [“b”]=> string(2) "10" [“f”]=> string(3) "1.1" } } binary protocol array(1) { [0]=> array(3) { [“id”]=> int(1) [“b”]=> int(10) [“f”]=> float(1.1000000238419) } } 31

Slide 32

Slide 32 text

PDO text protocol array(3) { [“id”]=> string(1) "1" [“b”]=> string(1) “\x10" [“f”]=> string(3) "1.1" } binary protocol array(3) { [“id”]=> string(1) "1" [“b”]=> string(1) “\x10" [“f”]=> string(3) "1.1" } mysql nd array(3) { [“id”]=> string(1) "1" [“b”]=> string(2) "10" [“f”]=> string(3) "1.1" } array(3) { [“id”]=> int(1) [“b”]=> int(10) [“f”]=> float(1.1000000238419) } libmy sql 32

Slide 33

Slide 33 text

mysqli text protocol $res = $mysqli->query("SELECT * FROM bits"); while($row = $res->fetch_assoc()) { $rows[] = $row; } var_dump($rows); binary protocol $stmt = $mysqli->prepare("SELECT * FROM bits"); $stmt->execute(); $stmt->bind_result($id, $b, $f); while($stmt->fetch()) { $rows[] = [‘id’=>$id, ‘b’=>$b, ‘f’=>$f]; } var_dump($rows); 33

Slide 34

Slide 34 text

mysqli (libmysql/mysqlnd) text protocol array(1) { [0]=> array(3) { [0]=> string(1) "1" [1]=> string(2) "10" [2]=> string(3) "1.1" } } binary protocol array(1) { [0]=> array(3) { [0]=> int(1) [1]=> int(10) [2]=> float(1.1000000238419) } } 34

Slide 35

Slide 35 text

II.Memory usage 35

Slide 36

Slide 36 text

fetch (libmysql) MySQL Server $id $name $dt 123 “do_aki” “2014-10-11 11:30:00” libmysql mysqli/pdo “id”:123, “name”:”do_aki”, “datetime”: “2014-10-11 11:30:00” 36

Slide 37

Slide 37 text

fetch (mysqlnd) MySQL Server $id $name $dt mysqlnd 3 “123” 6 "do_aki” 19 “2014-10-11 11:30:00” mysqli/pdo 37

Slide 38

Slide 38 text

fetch (detail) MySQL Server 3 “123” 6 "do_aki” 19 “2014-10-11 11:30:00” zval zval zval MYSQLND_RES zval* zval* zval* $id $name $dt MEMORY POOL (use malloc) 38 line 1 internal buffers

Slide 39

Slide 39 text

fetch * N MySQL Server 3 “123” 6 "do_aki” 19 “2014-10-11 11:30:00” zval zval zval MYSQLND_RES zval* zval* zval* $id $name $dt MEMORY POOL (use malloc) 39 line N internal buffers geting fat until statement released

Slide 40

Slide 40 text

solution for reduced memory • use MYSQLI_STORE_RESULT_COPY_DATA – fetch with copy – php >= 5.6.0 – Not for PDO yet… – http://blog.ulf-wendel.de/2014/php-5- 7-mysqlnd-memory-optimizations/ 40

Slide 41

Slide 41 text

fetch with copy MySQL Server 3 “123” 6 "do_aki” 19 “2014-10-11 11:30:00” zval zval zval MYSQLND_RES $id $name $dt 41 line N 123 “do_aki” “2014-10-11 11:30:00” no internal buffers

Slide 42

Slide 42 text

but but but 42

Slide 43

Slide 43 text

fetch (text protocol) MySQL Server $id $name $dt mysqlnd 3 “123” 6 "do_aki” 19 “2014-10-11 11:30:00” mysqli/pdo 43

Slide 44

Slide 44 text

fetch (binary protocol) MySQL Server $id $name $dt mysqlnd 123 6 "do_aki” 2014-10-11 11:30:00 mysqli/pdo 123 “do_aki” “2014-10-11 11:30:00” 44

Slide 45

Slide 45 text

fetch (detail) MySQL Server zval zval zval MYSQLND_RES zval* zval* zval* $id $name $dt 123 “do_aki” “2014-10-11 11:30:00” 123 6 "do_aki” 2014-10-11 11:30:00 45 line 1

Slide 46

Slide 46 text

“2014-10-11 11:30:00” “2014-10-11 11:30:00” “2014-10-11 11:30:00” “do_aki” “do_aki” “do_aki” 123 123 123 fetch*N MySQL Server zval zval zval MYSQLND_RES zval* zval* zval* $id $name $dt 123 “do_aki” “2014-10-11 11:30:00” 123 6 "do_aki” 2014-10-11 11:30:00 46 line N

Slide 47

Slide 47 text

Slide 48

Slide 48 text

Conclusion • I explain mysqlnd • mysqlnd and libmysql work differently • use mysqli if you want to full functions of mysqlnd • Prepared statement…… 48

Slide 49

Slide 49 text

Thank you 49 2014/10/11 PHP Conference Japan 2014 do_aki