[phptek 2015] Extending MySQL with PHP's Native Driver

PHP’s MySQL Native Driver (mysqlnd) has been providing great performance benefits since PHP 5.3, but there is more to it than just replacing libmysqlclient.

MySQL Native Drivers plugin architecture provides the ability to do read/write splitting, caching, load balancing, and more. Learn how to do all of these things simply and transparently, as well as how to write your own plugins using PHP.

Transcript

1. Extending MySQL with PHP's MySQL Native Driver
2. Proprietary and Confidential •Community Engineer at Engine Yard •Author of Zend PHP 5 Certification Study Guide, Sitepoints PHP Anthology: 101 Essential Tips, Tricks & Hacks & PHP Master: Write Cutting Edge Code •A contributor to Zend Framework 1 & 2, phpdoc, & PHP internals • Original creator of PHAR/PHP_Archive •@dshafik Davey Shafik
3. Let’s start a conversation about mental health in tech mhprompt.org
4. PHP & MySQL
5. PHP & MySQL
6. PHP & MySQL PHP
7. ext/mysql PHP & MySQL PHP ext/mysqli ext/pdo_mysql
8. ext/mysql PHP & MySQL libmysqlclient PHP ext/mysqli ext/pdo_mysql
9. libmysqlclient (GPL) ext/mysql PHP & MySQL PHP ext/mysqli ext/pdo_mysql
10. libmysqlclient (GPL) mysqlnd (5.3+) ext/mysql PHP & MySQL PHP ext/mysqli ext/pdo_mysql
11. libmysqlclient (GPL) mysqlnd (5.3+) ext/mysql ext/mysql (5.5+) PHP & MySQL PHP ext/mysqli ext/pdo_mysql
12. PHP & MySQL mysqlnd PHP ext/mysqli ext/pdo_mysql
13. PHP & MySQL mysqlnd PHP ext/mysqli ext/pdo_mysql pecl/mysqlnd_memcache pecl/mysqlnd_ms pecl/mysqlnd_qc pecl/mysqlnd_uh
14. MySQL Native Driver mysqlnd
15. • Added in PHP 5.3 • Default since 5.4+ (but you can still use libmysqlclient) • Uses PHP’s internal C infrastructure for seamless integration • Uses PHP’s memory management, minimizes memory usage • Uses PHP streams for I/O MySQL Native Driver (mysqlnd)
16. • Simply do not specify the path to libmysqlclient when compiling
 
 --with-mysqli
 --with-pdo-mysql • Debian/Ubuntu have a php5-mysqlnd package that replaced php5-mysql
 
 sudo apt-get install php5-mysqlnd Installation
17. MySQLnd Plugins
18. • Installed from PECL • pecl install mysqlnd_<name> • mysqlnd_ms — Easily perform read/write splitting between master and slave (ms) servers, with simple load balancing • mysqlnd_memcache — Transparently translate SQL to use the MySQL 5.6 memcache-protocol compatible NoSQL daemon • mysqlnd_qc — Transparent application-level query cache • mysqlnd_uh — Allows writing mysqlnd plugins in PHP Installating Plugins
19. Read/Write Splitting mysqlnd_ms
20. • For Master/Slave(s) replication topologies • Allows transparent simple auto-routing of read and write queries • Allows custom routing for more complex situations • pecl install mysqlnd_ms • extension=mysqlnd_ms.so • mysqlnd_ms.enable=1 • mysqlnd_ms.config_file=/path/to/mysqlnd_ms.json Read/Write Splitting
21. Proprietary and Confidential { "appname": { "master": { "master_0": { "host": "master.mysql.host", "port": "3306", "user": "dbuser", "password": "dbpassword", "db": "dbname" } }, "slave": { "slave_0": { "host": "slave.mysql.host", "port": "3306" "user": "dbuser", "password": "dbpassword", "db": "dbname" }, } } } Read/Write Splitting
22. Proprietary and Confidential "master": { "master_0": { "host": "master.mysql.host", "port": "3306", "user": "dbuser", "password": "dbpassword", "db": "dbname" } }, Read/Write Splitting
23. Proprietary and Confidential "slave": { "slave_0": { "host": "slave.mysql.host", "port": "3306" "user": "dbuser", "password": "dbpassword", "db": "dbname" }, } Read/Write Splitting
24. Automatic Routing
25. • Automatically route all queries that start with SELECT to the slave • Does not capture queries that start with (SELECT • Does capture write queries like SELECT ... INTO Automatic Routing
26. Manual Routing
27. • Achieved using SQL Hints (special comments) • MYSQLND_MS_MASTER_SWITCH — Run the statement on the master • MYSQLND_MS_SLAVE_SWITCH — Run the statement on the slave • MYSQLND_MS_LAST_USED_SWITCH — Run the statement on whichever server was last use • Constants that turn into ms=master, ms=slave, , and ms=last_used Manual Routing
28. Proprietary and Confidential $sql = sprintf( "/*%s*/ SELECT * FROM table_name;",
 MYSQLND_MS_MASTER_SWITCH ); Manual Routing: Send to Master
29. Proprietary and Confidential $sql = sprintf( "/*%s*/ CREATE TEMPORARY TABLE `temp_table_name` SELECT * FROM 
 table_name;", MYSQLND_MS_SLAVE_SWITCH ); Manual Routing: Send to Slave
30. Proprietary and Confidential { "appname": { "master": { ... }, "slave": { ... } "master_on_write": 1 } } Replication Lag & Transparent Master on Write
31. Load Balancing
32. • random — a random slave is picked for each read query • random once — a random slave is picked for the first read query and re-used for the remainder of the request • round robin — a new slave is picked for each read query, in the order they are defined • user — a user-specified callback determines which slave will be called for each query Load Balancing
33. Proprietary and Confidential { "appname": { "master": { … }, "slave": { … } }, "filters": { "random": 1 } } Load Balancing: Random
34. Proprietary and Confidential { … "filters": { "random": { "sticky": 1 } } } Load Balancing: Random Once
35. Proprietary and Confidential { … "filters": { "random": { "weights": { "slave1": 2 "slave2": 2 "slave3": 1 "master": 1 } } } } Load Balancing: Weighted Random
36. Proprietary and Confidential { … "filters": { "roundrobin" } } Load Balancing: Weighted Random
37. Proprietary and Confidential { … "filters": { "user": { "function_name" } } } Load Balancing: User • Must be a function • Return a single server • user_multi returns an array containing an array of master, and slave servers
38. Failover
39. • disabled: Never failover automatically. This is the default. • master: Always failover to the master • loop_before_master: If a slave request is being attempted, it will first loop through the slaves, before trying the master. Failover
40. Proprietary and Confidential { … "failover": { "strategy": "loop_before_master" } } Failover
41. • remember_failed: Remember failed servers for the remainder of the session. Defaults to false, but is recommended. • max_retries: The number of retries that should be attempted on each server before considering it failed. • Each server will only be tried once per iteration of the list, and will be removed only after failing N times. This defaults to 0 which means unlimited, so a server will never be removed; this conflicts with the remember_failed configuration option. Failover
42. Proprietary and Confidential { … "failover": { "strategy": "loop_before_master", "remember_failed": true, "max_retries": 1 } } Failover
43. Proprietary and Confidential During transactions failover is automatically disabled Failover
44. Transactions
45. • When using transactions you want all queries to go to the same server • Use MYSQLND_MS_MASTER_SWITCH or MYSQLND_MS_LAST_USED_SWITCH • When using MYSQLND_MS_LAST_USED_SWITCH turn off auto- commit Transactions
46. Proprietary and Confidential $mysqli->autocommit(false); // disable autocommit, implicitly starts a transaction $mysqli->query('BEGIN'); // queries $mysqli->query('COMMIT'); // or ROLLBACK $mysqli->autocommit(true); // enable autocommit Transactions: Disable Auto-Commit (mysqli)
47. Proprietary and Confidential $pdo->setAttribute('PDO::ATTR_AUTOCOMMIT', false); 
 // disable autocommit, implicitly starts a transaction $pdo->exec('BEGIN'); // queries $pdo->exec('COMMIT'); // or ROLLBACK $pdo->setAttribute('PDO::ATTR_AUTOCOMMIT', true); 
 // disable autocommit Transactions: Disable Auto-Commit (PDO)
48. Memcache mysqlnd_memcache
49. • MySQL 5.6 introduced the Innodb Memcache Daemon • Memcache backed by innodb • Skips the query parser, optimizer, and other unnecessary parts • Faster • Much faster in 5.7 • Can use the data from other — regular — queries Memcache
50. • Not included with Ubuntu/Debian • Add the official Oracle apt repository and install mysql • Enable with: Installation SOURCE /usr/share/mysql/innodb_memcached_config.sql; INSTALL PLUGIN daemon_memcached SONAME "libmemcached.so";
51. • Creates innodb_memcache with three tables: • cache_policies: This table contains policies determining how GET, SET, DELETE, and FLUSH commands are executed. • containers: This table contains a list of tables to expose via memcache • config_options: This table contains memcache configuration options — specifically the multi-column value separator (defaults to a pipe “|”) and the table_map_delimiter (defaults to a period “.”) Installation
52. Creating a Collection • name: The name used to refer to the collection via memcache • db_schema: The database name • db_table: The database table name • key_columns: The column name containing the key • value_columns: The columns that contain values, specify them as comma separated values. In memcache column values are separated by a pipe (as per the config_options table)
53. • flags: The memcache flags to set • cas_column: The CAS value assigned by memcache • expire_time_column: The expiration time (in seconds), or 0 to never expire • unique_idx_name_on_key: The name of the index which places a UNIQUE constraint on the key. If the key is the primary key, specify PRIMARY Creating a Collection
54. Proprietary and Confidential CREATE DATABASE kv_data; USE kv_data; CREATE TABLE kv_store ( `key` VARCHAR(255), `value` VARCHAR(1024), `flags` INT, `cas` BIGINT UNSIGNED, `exp` INT, primary key(`key`) ) ENGINE = INNODB; Create the Table
55. Proprietary and Confidential INSERT INTO innodb_memcache.containers( name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key ) VALUES ( 'kv_data', 'kv_data', 'kv_store', 'key', 'value', 'flags', 'cas', 'exp', 'PRIMARY' ); Tell the Plugin about the Table
56. Proprietary and Confidential $ telnet localhost 11211 telnet> set test.key 0 0 11 Hello World STORED telnet> get test.key VALUE test.key 0 11 Hello World END Using Memcache
57. • Defaults to “default” collection, or the first one • Switch using: get @@collection_name
 telnet> get @@kv_data VALUE @@kv_data 0 16 kv_data/kv_store END 
 • Use the table_map_delimiter: telnet> get @@kv_data.test.key VALUE @@kv_data.test.key 0 11 Hello World END Multiple Collections
58. mysqlnd_memcache
59. ; Load the extension extension=mysqlnd_memcache.so ; Enable it mysqlnd_memcache.enable=1 Queries as matched by the MYSQLND_MEMCACHE_DEFAULT_REGEXP constant: /^\s*SELECT\s*(.+?)\s*FROM\s*`?([a-z0-9_]+)`? \s*WHERE\s*`?([a-z0-9_]+)`?\s*=\s*(?(?=[“'])["'] ([^"']*)["']|([0-9e\.]*))\s*$/is mysqlnd_memcache
60. •SELECT `value` FROM kv_store WHERE `key` = ‘test.key’; Won’t Match: • SELECT * FROM kv_store WHERE `key` = 'test.key'; SELECT `value` FROM kv_store WHERE `key` = `test.key` AND value LIKE '%foo%'; SELECT `key` FROM kv_store WHERE value LIKE '%foo%'; Queries
61. Query Caching mysqlnd_qc
62. Proprietary and Confidential Query Caching in MySQL Pros Cons Easy to use Simplistic Transparent Easy to invalidate Single threaded
63. mysqlnd_qc
64. • default: per-process in-memory storage (built-in) • user: user-defined custom storage backend (built-in) • memcache: use memcached for storage • sqlite: use sqlite for storage • apc: use APC for storage — requires both mysqlnd_qc and APC be compiled statically, and may not work with apcu (for PHP 5.5+) Storage Backends
65. Proprietary and Confidential mysqlnd_qc_set_storage_handler('memcache'); mysqlnd_qc.memc_server = 'localhost' mysqlnd_qc.memc_port = '11211' // or ini_set('mysqlnd_qc.memc_server', 'localhost'); ini_set('mysqlnd_qc.memc_port', '11211'); Storage Backends
66. • Transparently cache all SELECT queries • That do not include dynamic columns (e.g. NOW() or LAST_INSERT_ID()) • php.ini • mysqlnd_qc.cache_by_default = 1 Caching
67. • Time Based: 30 second TTL • Set it manually: •mysqlnd_qc.ttl = 1800 • Using a SQL hint: MYSQLND_QC_TTL_SWITCH $sql = sprintf(
 "/*%s%d*/SELECT * FROM table”, MYSQLND_QC_TTL_SWITCH, 1800 );
 /*qc_tt=10*/SELECT * FROM table Cache Invalidation
68. • SQL Hint: MYSQLND_QC_DISABLE_SWITCH $sql = sprintf( "/*%s*/SELECT * FROM table”, MYSQLND_QC_DISABLE_SWITCH ); /*qc=off*/SELECT * FROM table Cache Opt-Out
69. • Done using mysqlnd_qc_set_cache_condition() • Three arguments: • 1st Argument: MYSQLND_QC_CONDITION_META_SCHEMA_PATTERN • 2nd Argument: Pattern to match, LIKE syntax: % multi-char, _ single-char • 3rd Argument: TTL (optional, defaults to INI setting) Conditional Caching
70. // Cache all session data mysqlnd_qc_set_cache_condition(
 MYSQLND_QC_CONDITION_META_SCHEMA_PATTERN, "myapp.session", 5*60 ); // Cache all user data mysqlnd_qc_set_cache_condition( MYSQLND_QC_CONDITION_META_SCHEMA_PATTERN, "myapp.user_%", 15 ); Conditional Caching
71. • User callback • false: should not be cached • true: try to cache for the default TTL • int: try to cache for int seconds Pattern Based Caching
72. Proprietary and Confidential function is_cacheable($sql) { if (preg_match(
 "/SELECT (.*?) FROM session (.*)/ism", $sql) === 1) { return 5*60; } if (preg_match( "/SELECT (.*?) FROM user_(.*?) (.*)/ism", $sql) === 1) { return 15; } return false; }
 mysqlnd_qc_set_is_select('is_cacheable'); Pattern Based Caching
73. Custom Plugins
74. Installation
75. Proprietary and Confidential svn co http://svn.php.net/repository/pecl/ mysqlnd_uh/trunk mysqlnd_uh cd mysqlnd_uh phpize ./configure --enable-mysqlnd-uh make sudo make install php.ini: extension=mysqlnd_uh.so Installation
76. Proxies
77. • mysqlnd_uh is an application level mysql proxy • Two types of proxies: • connection proxies: MysqlndUhConnection
 mysqlnd_uh_set_connection_proxy() • prepared statement proxies: MysqlndUhPreparedStatement
 mysqlnd_uh_set_statement_proxy() • If you don’t call parent methods, you may crash PHP MySQLnd_uh Proxies
78. Proprietary and Confidential class ConnectionRecorderProxy extends MysqlndUhConnection { public function connect($connection, $host, $user, $password, $database, $port, $socket, $mysql_flags) { $time = time(); file_put_contents("/tmp/connection-log", "Connection to $host on port $port for user $user at " .date('r', $time) . PHP_EOL); return parent::connect($connection, $host, $user, $password, $database, $port, $socket, $mysql_flags); } } Creating a Connection Proxy
79. Transforming Queries
80. • To transform queries we must use both Connection and Statement proxies • Connection proxy handles calls to mysqli->query() and 
 pdo->query() • Statement proxy handles call to mysqli->prepare() and 
 pdo->prepare() Transforming Queries
81. Proprietary and Confidential class MySQLndPluginQueryTransformer { static public transform($sql) { … The Query Transformer
82. Proprietary and Confidential // A list of constants to replace $constants = [ ‘MYSQLND_MS_MASTER_SWITCH', 'MYSQLND_MS_SLAVE_SWITCH', ‘MYSQLND_MS_LAST_USED_SWITCH', 'MYSQLND_QC_ENABLE_SWITCH', ‘MYSQLND_QC_DISABLE_SWITCH', 'MYSQLND_QC_TTL_SWITCH', ]; // Replace all the hints foreach ($constants as $constant) { $sql = str_replace($constant, constant($constant), $sql); } // Handle MYSQLND_QC_TTL_SWITCH= which would double up the = $sql = str_replace(MYSQLND_QC_TTL_SWITCH.'=', MYSQLND_QC_TTL_SWITCH, $sql); return $sql; MySQLndPluginQueryTransformer::transform()
83. Proprietary and Confidential class MySQLndPluginConnectionProxy extends MysqlndUhConnection { public function query($connection, $query) { // Transform the query $q = MySQLndPluginQueryTransformer::transform($query); // Call & return the parent function return parent::query($connection, $q); } } Connection Proxy
84. Proprietary and Confidential class MySQLndPluginStatementProxy extends MysqlndUhPreparedStatement { public function prepare($connection, $query) { // Transform the query $q = MySQLndPluginQueryTransformer::transform($query); // Call & return the parent function return parent::prepare($connection, $query); } } Statement Proxy
85. Proprietary and Confidential // Set the proxies mysqlnd_uh_set_connection_proxy( new MySQLndPluginConnectionProxy() ); mysqlnd_uh_set_statement_proxy( new MySQLndPluginStatementProxy() ); Set the Proxies
86. • Run the queries! It’s transparent. Using the Proxies
87. Proprietary and Confidential $query = $pdo->prepare(
 "/*MYSQLND_MS_LAST_USED*/ SELECT * FROM user"
 ); Transforms to: /*ms=last_used*/SELECT * FROM user Transformation
88. • Blog Series: Extending MySQL with PHP’s Native Driver: 
 http://ey.io/php-mysqlnd • PECL Extensions: 
 http://ey.io/pecl-mysqlnd • PHP Manual: 
 http://php.net/mysqlnd Resources
89. Feedback & Questions: 
 Feedback: https://joind.in/
 Twitter: @dshafik Email: davey@engineyard.com Slides: http://daveyshafik.com/slides 13739