[DutchPHP Conference 2015] Extending MySQL with PHP's Native Driver

[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.

Fee39f0c0ffb29d9ac21607ed188be6b?s=128

Davey Shafik

June 26, 2015
Tweet

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