Slide 1
Slide 1 text
Extending MySQL with PHP's
MySQL Native Driver
Slide 2
Slide 2 text
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
Slide 3
Slide 3 text
Let’s start a conversation about
mental health in tech
mhprompt.org
Slide 4
Slide 4 text
PHP & MySQL
Slide 5
Slide 5 text
PHP & MySQL
Slide 6
Slide 6 text
PHP & MySQL
PHP
Slide 7
Slide 7 text
ext/mysql
PHP & MySQL
PHP
ext/mysqli ext/pdo_mysql
Slide 8
Slide 8 text
ext/mysql
PHP & MySQL
libmysqlclient
PHP
ext/mysqli ext/pdo_mysql
Slide 9
Slide 9 text
libmysqlclient (GPL)
ext/mysql
PHP & MySQL
PHP
ext/mysqli ext/pdo_mysql
Slide 10
Slide 10 text
libmysqlclient (GPL)
mysqlnd (5.3+)
ext/mysql
PHP & MySQL
PHP
ext/mysqli ext/pdo_mysql
Slide 11
Slide 11 text
libmysqlclient (GPL)
mysqlnd (5.3+)
ext/mysql
ext/mysql (5.5+)
PHP & MySQL
PHP
ext/mysqli ext/pdo_mysql
Slide 12
Slide 12 text
PHP & MySQL
mysqlnd
PHP
ext/mysqli ext/pdo_mysql
Slide 13
Slide 13 text
PHP & MySQL
mysqlnd
PHP
ext/mysqli ext/pdo_mysql
pecl/mysqlnd_memcache
pecl/mysqlnd_ms pecl/mysqlnd_qc pecl/mysqlnd_uh
Slide 14
Slide 14 text
MySQL Native Driver
mysqlnd
Slide 15
Slide 15 text
• 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)
Slide 16
Slide 16 text
• 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
Slide 17
Slide 17 text
MySQLnd Plugins
Slide 18
Slide 18 text
• Installed from PECL
• pecl install mysqlnd_
• 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
Slide 19
Slide 19 text
Read/Write Splitting
mysqlnd_ms
Slide 20
Slide 20 text
• 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
Slide 21
Slide 21 text
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
Slide 22
Slide 22 text
Proprietary and Confidential
"master": {
"master_0": {
"host": "master.mysql.host",
"port": "3306",
"user": "dbuser",
"password": "dbpassword",
"db": "dbname"
}
},
Read/Write Splitting
Slide 23
Slide 23 text
Proprietary and Confidential
"slave": {
"slave_0": {
"host": "slave.mysql.host",
"port": "3306"
"user": "dbuser",
"password": "dbpassword",
"db": "dbname"
},
}
Read/Write Splitting
Slide 24
Slide 24 text
Automatic Routing
Slide 25
Slide 25 text
• 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
Slide 26
Slide 26 text
Manual Routing
Slide 27
Slide 27 text
• 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
Slide 28
Slide 28 text
Proprietary and Confidential
$sql = sprintf(
"/*%s*/ SELECT * FROM table_name;",
MYSQLND_MS_MASTER_SWITCH
);
Manual Routing: Send to Master
Slide 29
Slide 29 text
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
Slide 30
Slide 30 text
Proprietary and Confidential
{
"appname": {
"master": {
...
},
"slave": {
...
}
"master_on_write": 1
}
}
Replication Lag & Transparent Master on Write
Slide 31
Slide 31 text
Load Balancing
Slide 32
Slide 32 text
• 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
Slide 33
Slide 33 text
Proprietary and Confidential
{
"appname": {
"master": {
…
},
"slave": {
…
}
},
"filters": {
"random": 1
}
}
Load Balancing: Random
Slide 34
Slide 34 text
Proprietary and Confidential
{
…
"filters": {
"random": {
"sticky": 1
}
}
}
Load Balancing: Random Once
Slide 35
Slide 35 text
Proprietary and Confidential
{
…
"filters": {
"random": {
"weights": {
"slave1": 2
"slave2": 2
"slave3": 1
"master": 1
}
}
}
}
Load Balancing: Weighted Random
Slide 36
Slide 36 text
Proprietary and Confidential
{
…
"filters": {
"roundrobin"
}
}
Load Balancing: Weighted Random
Slide 37
Slide 37 text
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
Slide 38
Slide 38 text
Failover
Slide 39
Slide 39 text
• 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
Slide 40
Slide 40 text
Proprietary and Confidential
{
…
"failover": {
"strategy": "loop_before_master"
}
}
Failover
Slide 41
Slide 41 text
• 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
Slide 42
Slide 42 text
Proprietary and Confidential
{
…
"failover": {
"strategy": "loop_before_master",
"remember_failed": true,
"max_retries": 1
}
}
Failover
Slide 43
Slide 43 text
Proprietary and Confidential
During transactions failover is automatically disabled
Failover
Slide 44
Slide 44 text
Transactions
Slide 45
Slide 45 text
• 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
Slide 46
Slide 46 text
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)
Slide 47
Slide 47 text
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)
Slide 48
Slide 48 text
Memcache
mysqlnd_memcache
Slide 49
Slide 49 text
• 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
Slide 50
Slide 50 text
• 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";
Slide 51
Slide 51 text
• 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
Slide 52
Slide 52 text
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)
Slide 53
Slide 53 text
• 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
Slide 54
Slide 54 text
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
Slide 55
Slide 55 text
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
Slide 56
Slide 56 text
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
Slide 57
Slide 57 text
• 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
Slide 58
Slide 58 text
mysqlnd_memcache
Slide 59
Slide 59 text
; 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
Slide 60
Slide 60 text
•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
Slide 61
Slide 61 text
Query Caching
mysqlnd_qc
Slide 62
Slide 62 text
Proprietary and Confidential
Query Caching in MySQL
Pros Cons
Easy to use Simplistic
Transparent Easy to invalidate
Single threaded
Slide 63
Slide 63 text
mysqlnd_qc
Slide 64
Slide 64 text
• 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
Slide 65
Slide 65 text
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
Slide 66
Slide 66 text
• 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
Slide 67
Slide 67 text
• 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
Slide 68
Slide 68 text
• SQL Hint: MYSQLND_QC_DISABLE_SWITCH
$sql = sprintf(
"/*%s*/SELECT * FROM table”,
MYSQLND_QC_DISABLE_SWITCH
);
/*qc=off*/SELECT * FROM table
Cache Opt-Out
Slide 69
Slide 69 text
• 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
Slide 70
Slide 70 text
// 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
Slide 71
Slide 71 text
• 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
Slide 72
Slide 72 text
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
Slide 73
Slide 73 text
Custom Plugins
Slide 74
Slide 74 text
Installation
Slide 75
Slide 75 text
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
Slide 76
Slide 76 text
Proxies
Slide 77
Slide 77 text
• 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
Slide 78
Slide 78 text
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
Slide 79
Slide 79 text
Transforming Queries
Slide 80
Slide 80 text
• 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
Slide 81
Slide 81 text
Proprietary and Confidential
class MySQLndPluginQueryTransformer {
static public transform($sql)
{
…
The Query Transformer
Slide 82
Slide 82 text
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()
Slide 83
Slide 83 text
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
Slide 84
Slide 84 text
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
Slide 85
Slide 85 text
Proprietary and Confidential
// Set the proxies
mysqlnd_uh_set_connection_proxy(
new MySQLndPluginConnectionProxy()
);
mysqlnd_uh_set_statement_proxy(
new MySQLndPluginStatementProxy()
);
Set the Proxies
Slide 86
Slide 86 text
• Run the queries! It’s transparent.
Using the Proxies
Slide 87
Slide 87 text
Proprietary and Confidential
$query = $pdo->prepare(
"/*MYSQLND_MS_LAST_USED*/ SELECT * FROM user"
);
Transforms to:
/*ms=last_used*/SELECT * FROM user
Transformation
Slide 88
Slide 88 text
• 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
Slide 89
Slide 89 text
Feedback & Questions:
Feedback: https://joind.in/
Twitter: @dshafik
Email: [email protected]
Slides: http://daveyshafik.com/slides
13739