Mastering mysqlnd

2014/10/11
PHP Conference Japan 2014

Transcript

1. 2014/10/11 PHP Conference Japan 2014 do_aki 1 updated 2014-10-21
2. @do_aki ＠do_aki http://do-aki.net/ 2
3. agenda 1. about mysqlnd – position, role 2. libmysql vs mysqlnd – functions, features 3. mysqlnd internals I. Type of returning values II.Memory usage 3
4. 1.about mysqlnd 4
5. MySQL client libraries in PHP mysql_connect mysqli PDO Doctrine Eloquent ZendDB Aura.Sql ADOdb PEAR_MDB2 PEAR_DB 5
6. Those libraries use mysql/mysqli/pdo extensions 6 MySQL client libraries in PHP is / use mysql extension mysqli extension pdo extension (mysql driver)
7. mysql / mysqli / pdo (mysql_driver) access MySQL server directly? 7
8. No 8
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) 9
10. 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
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 11
12. 2.libmysql vs mysqlnd 12
13. 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
14. libmysql vs mysqlnd • libmysqlclient (Connector/C) – C library – Oracle (MySQL AB) – matured • mysqlnd – PHP extension – PHP Community (Andrey, Johannes, Ulf) – relatively recent 14
15. 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
16. 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
17. 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
18. 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
19. cons • not yet matured – libmysqlclient is widely used. at any os, as any language bindings… – mysqlnd is limited used. compared with libmysql 19
20. 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
21. 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
22. 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
23. plugins (removed?) • mysqlnd_mux – Simuler mysqlnd_ms? • mysqlnd_pscache – Prepared statement cache • mysqlnd_sip – SQL Injection Protection • mysqlnd_mc – Multi Connect 23
24. 3.mysqlnd internals 24
25. 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]
26. 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
27. I.Type of returning values 27
28. example mysql> CREATE TABLE bits( id int, b bit(8), f float ); mysql> insert into bits values (1, b'1010', 1.1); 28
29. 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
30. 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
31. 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
32. 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
33. 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
34. 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
35. II.Memory usage 35
36. 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
37. fetch (mysqlnd) MySQL Server $id $name $dt mysqlnd 3 “123” 6 "do_aki” 19 “2014-10-11 11:30:00” mysqli/pdo 37
38. 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
39. 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
40. 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
41. 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
42. but but but 42
43. fetch (text protocol) MySQL Server $id $name $dt mysqlnd 3 “123” 6 "do_aki” 19 “2014-10-11 11:30:00” mysqli/pdo 43
44. 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
45. 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
46. “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
47. “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
48. Conclusion • I explain mysqlnd • mysqlnd and libmysql work differently • use mysqli if you want to full functions of mysqlnd • Prepared statement…… 48
49. Thank you 49 2014/10/11 PHP Conference Japan 2014 do_aki