[DutchPHP Conference 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. ext/mysql PHP & MySQL — libmysqlclient libmysqlclient PHP ext/mysqli ext/pdo_mysql
6. libmysqlclient (GPL) mysqlnd (5.3+) ext/mysql ext/mysql (5.5+) PHP & MySQL (mysqlnd/PHP 5.5+) PHP ext/mysqli ext/pdo_mysql
7. PHP & MySQL (PHP 7) PHP ext/mysqli ext/pdo_mysql pecl/mysqlnd_memcache pecl/mysqlnd_ms pecl/mysqlnd_qc pecl/mysqlnd_uh mysqlnd (5.3+) mysqlnd
8. MySQL Native Driver mysqlnd
9. • 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)
10. • Simply do not specify the path to libmysqlclient when compiling
 
 --with-mysqli
 --with-pdo-mysql • Debian/Ubuntu have a php5-mysqlnd package that replaces php5-mysql
 
 sudo apt-get install php5-mysqlnd Installation
11. MySQLnd Plugins
12. • 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
13. Read/Write Splitting mysqlnd_ms
14. • For Master/Slave(s) replication topologies • Allows transparent simple auto-routing of read and write queries • Allows custom routing for more complex needs •pecl install mysqlnd_ms •extension=mysqlnd_ms.so •mysqlnd_ms.enable=1 •mysqlnd_ms.config_file=/path/to/mysqlnd_ms.json Read/Write Splitting
15. 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
16. Proprietary and Confidential "master": { "master_0": { "host": "master.mysql.host", "port": "3306", "user": "dbuser", "password": "dbpassword", "db": "dbname" } }, Read/Write Splitting
17. Proprietary and Confidential "slave": { "slave_0": { "host": "slave.mysql.host", "port": "3306" "user": "dbuser", "password": "dbpassword", "db": "dbname" }, } Read/Write Splitting
18. Automatic Routing
19. • 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
20. Manual Routing
21. • 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
22. Proprietary and Confidential $sql = sprintf( "/*%s*/ SELECT * FROM table_name;",
 MYSQLND_MS_MASTER_SWITCH ); Manual Routing: Send to Master
23. 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
24. Proprietary and Confidential if ($request->isPost() && $user->isValid()) { $user->save(); } $sql = "SELECT 
 * 
 FROM user 
 WHERE user_id = :user_id"; Manual Routing: Last Used
25. Proprietary and Confidential if ($request->isPost() && $user->isValid()) { $user->save(); } $sql = sprintf(
 "/*%s*/SELECT 
 * 
 FROM user 
 WHERE user_id = :user_id", MYSQLND_MS_LAST_USED_SWITCH
 ); Manual Routing: Last Used
26. Proprietary and Confidential { "appname": { "master": { ... }, "slave": { ... } "master_on_write": 1 } } Replication Lag & Transparent Master on Write
27. Load Balancing
28. • 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
29. Proprietary and Confidential { "appname": { "master": { … }, "slave": { … } }, "filters": { "random": 1 } } Load Balancing: Random
30. Proprietary and Confidential { … "filters": { "random": { "sticky": 1 } } } Load Balancing: Random Once
31. Proprietary and Confidential { … "filters": { "random": { "weights": { "slave1": 2 "slave2": 2 "slave3": 1 "master": 1 } } } } Load Balancing: Weighted Random
32. Proprietary and Confidential { … "filters": { "roundrobin" } } Load Balancing: Weighted Random
33. 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
34. Failover
35. • 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
36. Proprietary and Confidential { … "failover": { "strategy": "loop_before_master" } } Failover
37. • 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
38. Proprietary and Confidential { … "failover": { "strategy": "loop_before_master", "remember_failed": true, "max_retries": 1 } } Failover
39. Proprietary and Confidential During transactions failover is automatically disabled Failover
40. Transactions
41. • 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
42. 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)
43. 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)
44. Memcache mysqlnd_memcache
45. • 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
46. • 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";
47. • 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
48. 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)
49. • 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
50. 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
51. 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
52. 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
53. • 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
54. mysqlnd_memcache
55. ; 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
56. •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
57. Query Caching mysqlnd_qc
58. Proprietary and Confidential Query Caching in MySQL Pros Cons Easy to use Simplistic Transparent Easy to invalidate Single threaded
59. mysqlnd_qc
60. • 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
61. 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
62. • 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
63. • 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
64. • SQL Hint: MYSQLND_QC_DISABLE_SWITCH $sql = sprintf( "/*%s*/SELECT * FROM table”, MYSQLND_QC_DISABLE_SWITCH ); /*qc=off*/SELECT * FROM table Cache Opt-Out
65. • 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
66. // 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
67. • 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
68. 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
69. Custom Plugins
70. Installation
71. 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
72. Proxies
73. • 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
74. 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
75. Transforming Queries
76. • 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
77. Proprietary and Confidential class MySQLndPluginQueryTransformer { static public transform($sql) { … The Query Transformer
78. 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()
79. 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
80. 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
81. Proprietary and Confidential // Set the proxies mysqlnd_uh_set_connection_proxy( new MySQLndPluginConnectionProxy() ); mysqlnd_uh_set_statement_proxy( new MySQLndPluginStatementProxy() ); Set the Proxies
82. • Run the queries! It’s transparent. Using the Proxies
83. Proprietary and Confidential $query = $pdo->prepare(
 "/*MYSQLND_MS_LAST_USED*/ SELECT * FROM user"
 ); Transforms to: /*ms=last_used*/SELECT * FROM user Transformation
84. • 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
85. Feedback & Questions: 
 Feedback: https://joind.in/
 Twitter: @dshafik Email: davey@engineyard.com Slides: http://daveyshafik.com/slides 14230