Share
Embed
Transcript
49 slides
-
agenda 1. about mysqlnd – position, role 2. libmysql vs
mysqlnd – functions, features 3. mysqlnd internals I. Type of returning values II.Memory usage 3 -
MySQL client libraries in PHP mysql_connect mysqli PDO Doctrine Eloquent
ZendDB Aura.Sql ADOdb PEAR_MDB2 PEAR_DB 5 -
Those libraries use mysql/mysqli/pdo extensions 6 MySQL client libraries in
PHP is / use mysql extension mysqli extension pdo extension (mysql driver) -
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 -
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) mysql/mysqli/pdo mysqlnd / libmysql 10 -
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 -
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 -
libmysql vs mysqlnd • libmysqlclient (Connector/C) – C library –
Oracle (MySQL AB) – matured • mysqlnd – PHP extension – PHP Community (Andrey, Johannes, Ulf) – relatively recent 14 -
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 -
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 -
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 -
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 -
cons • not yet matured – libmysqlclient is widely used.
at any os, as any language bindings… – mysqlnd is limited used. compared with libmysql 19 -
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 -
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 -
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 -
plugins (removed?) • mysqlnd_mux – Simuler mysqlnd_ms? • mysqlnd_pscache –
Prepared statement cache • mysqlnd_sip – SQL Injection Protection • mysqlnd_mc – Multi Connect 23 -
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] -
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 -
example mysql> CREATE TABLE bits( id int, b bit(8), f
float ); mysql> insert into bits values (1, b'1010', 1.1); 28 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
fetch (mysqlnd) MySQL Server $id $name $dt mysqlnd 3 “123”
6 "do_aki” 19 “2014-10-11 11:30:00” mysqli/pdo 37 -
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 -
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 -
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 -
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 -
fetch (text protocol) MySQL Server $id $name $dt mysqlnd 3
“123” 6 "do_aki” 19 “2014-10-11 11:30:00” mysqli/pdo 43 -
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 -
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 -
“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 -
“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 47 increases memory usage each fetch (until statement released) cannot use “MYSQLI_STORE_RE SULT_COPY_DATA” to prepared statement -
Conclusion • I explain mysqlnd • mysqlnd and libmysql work
differently • use mysqli if you want to full functions of mysqlnd • Prepared statement…… 48