Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

Davey Shafik

May 20, 2015
Tweet

More Decks by Davey Shafik

Other Decks in Programming

Transcript

  1. 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
  2. • 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)
  3. • 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
  4. • 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
  5. • 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
  6. 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
  7. Proprietary and Confidential "master": { "master_0": { "host": "master.mysql.host", "port":

    "3306", "user": "dbuser", "password": "dbpassword", "db": "dbname" } }, Read/Write Splitting
  8. Proprietary and Confidential "slave": { "slave_0": { "host": "slave.mysql.host", "port":

    "3306" "user": "dbuser", "password": "dbpassword", "db": "dbname" }, } Read/Write Splitting
  9. • 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
  10. • 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
  11. Proprietary and Confidential $sql = sprintf( "/*%s*/ SELECT * FROM

    table_name;",
 MYSQLND_MS_MASTER_SWITCH ); Manual Routing: Send to Master
  12. 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
  13. Proprietary and Confidential { "appname": { "master": { ... },

    "slave": { ... } "master_on_write": 1 } } Replication Lag & Transparent Master on Write
  14. • 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
  15. Proprietary and Confidential { "appname": { "master": { … },

    "slave": { … } }, "filters": { "random": 1 } } Load Balancing: Random
  16. Proprietary and Confidential { … "filters": { "random": { "weights":

    { "slave1": 2 "slave2": 2 "slave3": 1 "master": 1 } } } } Load Balancing: Weighted Random
  17. 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
  18. • 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
  19. • 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
  20. • 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
  21. 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)
  22. 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)
  23. • 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
  24. • 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";
  25. • 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
  26. 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)
  27. • 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
  28. 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
  29. 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
  30. 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
  31. • 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
  32. ; 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
  33. •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
  34. Proprietary and Confidential Query Caching in MySQL Pros Cons Easy

    to use Simplistic Transparent Easy to invalidate Single threaded
  35. • 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
  36. 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
  37. • 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
  38. • 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
  39. • SQL Hint: MYSQLND_QC_DISABLE_SWITCH $sql = sprintf( "/*%s*/SELECT * FROM

    table”, MYSQLND_QC_DISABLE_SWITCH ); /*qc=off*/SELECT * FROM table Cache Opt-Out
  40. • 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
  41. // 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
  42. • 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
  43. 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
  44. 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
  45. • 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
  46. 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
  47. • 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
  48. 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()
  49. 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
  50. 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
  51. Proprietary and Confidential // Set the proxies mysqlnd_uh_set_connection_proxy( new MySQLndPluginConnectionProxy()

    ); mysqlnd_uh_set_statement_proxy( new MySQLndPluginStatementProxy() ); Set the Proxies
  52. Proprietary and Confidential $query = $pdo->prepare(
 "/*MYSQLND_MS_LAST_USED*/ SELECT * FROM

    user"
 ); Transforms to: /*ms=last_used*/SELECT * FROM user Transformation
  53. • 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