44
Setup
689
It is strongly recommended to read the reference sections in addition to the quickstart. The quickstart tries
to avoid discussing theoretical concepts and limitations. Instead, it will link to the reference sections. It is
safe to begin with the quickstart. However, before using the plugin in mission critical environments we urge
you to read additionally the background information from the reference sections.
11.4.1 Setup
Copyright 1997-2014 the PHP Documentation Group.
The plugin is implemented as a PHP extension. See also the installation instructions to install this
extension.
Compile or configure the PHP MySQL extension (API) (mysqli, PDO_MYSQL, mysql). That extension must
use the mysqlnd library as because mysqlnd_memcache is a plugin for the mysqlnd library. For additional
information, refer to the mysqlnd_memcache installation instructions.
Then, load this extension into PHP and activate the plugin in the PHP configuration file using the PHP
configuration directive named mysqlnd_memcache.enable.
Example 11.1 Enabling the plugin (php.ini)
; On Windows the filename is php_mysqnd_memcache.dll
; Load the extension
extension=mysqlnd_memcache.so
; Enable it
mysqlnd_memcache.enable=1
Follow the instructions given in the MySQL Reference Manual on installing the Memcache plugins for the
MySQL server. Activate the plugins and configure Memcache access for SQL tables.
The examples in this quickguide assume that the following table exists, and that Memcache is configured
with access to it.
Example 11.2 SQL table used for the Quickstart
CREATE TABLE test(
id CHAR(16),
f1 VARCHAR(255),
f2 VARCHAR(255),
f3 VARCHAR(255),
flags INT NOT NULL,
cas_column INT,
expire_time_column INT,
PRIMARY KEY(id)
) ENGINE=InnoDB;
INSERT INTO test (id, f1, f2, f3) VALUES (1, 'Hello', 'World', '!');
INSERT INTO test (id, f1, f2, f3) VALUES (2, 'Lady', 'and', 'the tramp');
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 (
'plugin_test', 'test', 'test', 'id', 'f1,f2,f3',
'flags', 'cas_column', 'expire_time_column', 'PRIMARY KEY');
46
Usage
690
11.4.2 Usage
Copyright 1997-2014 the PHP Documentation Group.
After associating a MySQL connection with a Memcache connection using mysqnd_memcache_set
the plugin attempts to transparently replace SQL SELECT statements by a memcache access. For that
purpose the plugin monitors all SQL statements executed and tries to match the statement string against
MYSQLND_MEMCACHE_DEFAULT_REGEXP. In case of a match, the mysqlnd memcache plugin checks
whether the SELECT is accessing only columns of a mapped table and the WHERE clause is limited to a
single key lookup.
In case of the example SQL table, the plugin will use the Memcache interface of the MySQL server to fetch
results for a SQL query like SELECT f1, f2, f3 WHERE id = n.
Example 11.3 Basic example.
<?php
$mysqli = new mysqli("host", "user", "passwd", "database");
$memc = new Memcached();
$memc->addServer("host", 11211);
mysqlnd_memcache_set($mysqli, $memc);
/*
This is a query which queries table test using id as key in the WHERE part
and is accessing fields f1, f2 and f3. Therefore, mysqlnd_memcache
will intercept it and route it via memcache.
*/
$result = $mysqli->query("SELECT f1, f2, f3 FROM test WHERE id = 1");
while ($row = $result->fetch_row()) {
print_r($row);
}
/*
This is a query which queries table test but using f1 in the WHERE clause.
Therefore, mysqlnd_memcache can't intercept it. This will be executed
using the MySQL protocol
*/
$mysqli->query("SELECT id FROM test WHERE f1 = 'Lady'");
while ($row = $result->fetch_row()) {
print_r($row);
}
?>
The above example will output:
array(
[f1] => Hello
[f2] => World
[f3] => !
)
array(
[id] => 2
)
43
Installing/Configuring
691
11.5 Installing/Configuring
Copyright 1997-2014 the PHP Documentation Group.
11.5.1 Requirements
Copyright 1997-2014 the PHP Documentation Group.
PHP: this extension requires PHP 5.4+, version PHP 5.4.4 or never. The required PHP extensions are
PCRE (enabled by default), and the memcached extension version 2.0.x.
The mysqlnd_memcache Memcache plugin supports all PHP applications and all available PHP MySQL
extensions (mysqli, mysql, PDO_MYSQL). The PHP MySQL extension must be configured with mysqlnd
support.
For accessing InnoDB tables, this PHP extension requires MySQL Server 5.6.6 or newer with the
InnoDB Memcache Daemon Plugin enabled.
For accessing MySQL Cluster NDB tables, this PHP extension requires MySQL Cluster 7.2 or newer
with the NDB Memcache API nodes enabled.
11.5.2 Installation
Copyright 1997-2014 the PHP Documentation Group.
This PECL extension is not bundled with PHP.
Information for installing this PECL extension may be found in the manual chapter titled Installation of
PECL extensions. Additional information such as new releases, downloads, source files, maintainer
information, and a CHANGELOG, can be located here: http://pecl.php.net/package/mysqlnd_memcache
A DLL for this PECL extension is currently unavailable. See also the building on Windows section.
11.5.3 Runtime Configuration
Copyright 1997-2014 the PHP Documentation Group.
The behaviour of these functions is affected by settings in php.ini.
Table 11.1 Mysqlnd_memcache Configure Options
Name
Default
Changeable
Changelog
mysqlnd_memcache.enable
1
PHP_INI_SYSTEM
Available since 1.0.0
Here's a short explanation of the configuration directives.
mysqlnd_memcache.enable
integer
Enables or disables the plugin. If disabled, the extension will not plug
into mysqlnd to proxy internal mysqlnd C API calls.
Note
This option is mainly used by developers to build
this extension statically into PHP. General users
are encouraged to build this extension as a
46
Predefined Constants
692
shared object, and to unload it completely when
it is not needed.
11.6 Predefined Constants
Copyright 1997-2014 the PHP Documentation Group.
The constants below are defined by this extension, and will only be available when the extension has either
been compiled into PHP or dynamically loaded at runtime.
MySQL Memcache Plugin related
MYSQLND_MEMCACHE_DEFAULT_REGEXP
(string)
Default regular expression (PCRE style) used for matching SELECT
statements that will be mapped into a MySQL Memcache Plugin access
point, if possible.
It is also possible to use mysqlnd_memcache_set, but the default
approach is using this regular expression for pattern matching.
Assorted
The version number of this plugin can be obtained by using MYSQLND_MEMCACHE_VERSION or
MYSQLND_MEMCACHE_VERSION_ID. MYSQLND_MEMCACHE_VERSION is the string representation of
the numerical version number MYSQLND_MEMCACHE_VERSION_ID, which is an integer such as 10000.
Developers can calculate the version number as follows.
Version (part)
Example
Major*10000
1*10000 = 10000
Minor*100
0*100 = 0
Patch
0 = 0
MYSQLND_MEMCACHE_VERSION_ID
10000
MYSQLND_MEMCACHE_VERSION
(string)
Plugin version string, for example, “1.0.0-alpha” ” .
MYSQLND_MEMCACHE_VERSION_ID
(integer)
Plugin version number, for example, 10000.
11.7 Mysqlnd_memcache Functions
Copyright 1997-2014 the PHP Documentation Group.
11.7.1 mysqlnd_memcache_get_config
Copyright 1997-2014 the PHP Documentation Group.
• mysqlnd_memcache_get_config
Returns information about the plugin configuration
Description
array mysqlnd_memcache_get_config(
mixed connection);
55
mysqlnd_memcache_get_config
693
This function returns an array of all mysqlnd_memcache related configuration information that
is attached to the MySQL connection. This includes MySQL, the Memcache object provided via
mysqlnd_memcache_set, and the table mapping configuration that was automatically collected from the
MySQL Server.
Parameters
connection
A handle to a MySQL Server using one of the MySQL API extensions
for PHP, which are PDO_MYSQL, mysqli or ext/mysql.
Return Values
An array of mysqlnd_memcache configuration information on success, otherwise FALSE.
The returned array has these elements:
Table 11.2 mysqlnd_memcache_get_config array structure
Array Key
Description
memcached
Instance of Memcached associated to this MySQL
connection by mysqlnd_memcache_set. You
can use this to change settings of the memcache
connection, or directly by querying the server on this
connection.
pattern
The PCRE regular expression used to match the
SQL query sent to the server. Queries matching
this pattern will be further analyzed to decide
whether the query can be intercepted and sent
via the memcache interface or whether the query
is sent using the general MySQL protocol to the
server. The pattern is either the default pattern
(MYSQLND_MEMCACHE_DEFAULT_REGEXP) or it is
set via mysqlnd_memcache_set.
mappings
An associative array with a list of all configured
containers as they were discovered by this plugin.
The key for these elements is the name of the
container in the MySQL configuration. The value is
described below. The contents of this field is created
by querying the MySQL Server during association
to MySQL and a memcache connection using
mysqlnd_memcache_set.
mapping_query
An SQL query used during
mysqlnd_memcache_set to identify the available
containers and mappings. The result of that query is
provided in the mappings element.
Table 11.3 Mapping entry structure
Array Key
Description
prefix
A prefix used while accessing data via memcache.
With the MySQL InnoDB Memcache Deamon
plugin, this usually begins with @@ and ends with a
configurable separator. This prefix is placed in front
of the key value while using the memcache protocol.
52
mysqlnd_memcache_get_config
694
Array Key
Description
schema_name
Name of the schema (database) which contains the
table being accessed.
table_name
Name of the table which contains the data
accessible via memcache protocol.
id_field_name
Name of the database field (column) with the
id used as key when accessing the table via
memcache. Often this is the database field having a
primary key.
separator
The separator used to split the different field values.
This is needed as memcache only provides access
to a single value while MySQL can map multiple
columns to this value.
Note
The separator, which can
be set in the MySQL Server
configuration, should not be
part of any value retrieved
via memcache because
proper mapping can't be
guaranteed.
fields
An array with the name of all fields available for this
mapping.
Examples
Example 11.4 mysqlnd_memcache_get_config example
<?php
$mysqli = new mysqli("host", "user", "passwd", "database");
$memc = new Memcached();
$memc->addServer("host", 11211);
mysqlnd_memcache_set($mysqli, $memc);
var_dump(mysqlnd_memcache_get_config($mysqli));
?>
The above example will output:
array(4) {
["memcached"]=>
object(Memcached)#2 (0) {
}
["pattern"]=>
string(125) "/^\s*SELECT\s*(.+?)\s*FROM\s*`?([a-z0-9_]+)`?\s*WHERE\s*`?([a-z0-9_]+)`?\s*=\s*(?(?=["'])["']([^"']*)["']|([0-9e\.]*))\s*$/is"
["mappings"]=>
array(1) {
["mymem_test"]=>
array(6) {
["prefix"]=>
52
mysqlnd_memcache_set
695
string(13) "@@mymem_test."
["schema_name"]=>
string(4) "test"
["table_name"]=>
string(10) "mymem_test"
["id_field_name"]=>
string(2) "id"
["separator"]=>
string(1) "|"
["fields"]=>
array(3) {
[0]=>
string(2) "f1"
[1]=>
string(2) "f2"
[2]=>
string(2) "f3"
}
}
}
["mapping_query"]=>
string(209) " SELECT c.name,
CONCAT('@@', c.name, (SELECT value FROM innodb_memcache.config_options WHERE name = 'table_map_delimiter')) AS key_prefix,
c.db_schema,
c.db_table,
c.key_columns,
c.value_columns,
(SELECT value FROM innodb_memcache.config_options WHERE name = 'separator') AS sep
FROM innodb_memcache.containers c"
}
See Also
mysqlnd_memcache_set
11.7.2 mysqlnd_memcache_set
Copyright 1997-2014 the PHP Documentation Group.
• mysqlnd_memcache_set
Associate a MySQL connection with a Memcache connection
Description
bool mysqlnd_memcache_set(
mixed mysql_connection,
Memcached memcache_connection,
string pattern,
callback callback);
Associate mysql_connection with memcache_connection using pattern as a PCRE regular
expression, and callback as a notification callback or to unset the association of mysql_connection.
While associating a MySQL connection with a Memcache connection, this function will query the MySQL
Server for its configuration. It will automatically detect whether the server is configured to use the InnoDB
Memcache Daemon Plugin or MySQL Cluster NDB Memcache support. It will also query the server
to automatically identify exported tables and other configuration options. The results of this automatic
configuration can be retrieved using mysqlnd_memcache_get_config.
Parameters
45
mysqlnd_memcache_set
696
mysql_connection
A handle to a MySQL Server using one of the MySQL API extensions
for PHP, which are PDO_MYSQL, mysqli or ext/mysql.
memcache_connection
A Memcached instance with a connection to the MySQL Memcache
Daemon plugin. If this parameter is omitted, then mysql_connection
will be unassociated from any memcache connection. And if a previous
association exists, then it will be replaced.
pattern
A regular expression in Perl Compatible Regular Expression syntax
used to identify potential Memcache-queries. The query should have
three sub patterns. The first subpattern contains the requested field list,
the second the name of the ID column from the query and the third the
requested value. If this parameter is omitted or os set to NULL, then a
default pattern will be used.
callback
A callback which will be used whenever a query is being sent to
MySQL. The callback will receive a single boolean parameter telling if a
query was sent via Memcache.
Return Values
TRUE if the association or disassociation is successful, otherwise FALSE if there is an error.
Examples
Example 11.5 mysqlnd_memcache_set example with var_dump as a simple debugging callback.
<?php
$mysqli = new mysqli("host", "user", "passwd", "database");
$memc = new Memcached();
$memc->addServer("host", 11211);
mysqlnd_memcache_set($mysqli, $memc, NULL, 'var_dump');
/* This query will be intercepted and executed via Memcache protocol */
echo "Sending query for id via Memcache: ";
$mysqli->query("SELECT f1, f2, f3 FROM test WHERE id = 1");
/* f1 is not configured as valid key field, this won't be sent via Memcache */
echo "Sending query for f1 via Memcache: ";
$mysqli->query("SELECT id FROM test WHERE f1 = 1");
mysqlnd_memcache_set($mysqli);
/* Now the regular MySQL protocol will be used */
echo "var_dump won't be invoked: ";
$mysqli->query("SELECT f1, f2, f3 WHERE id = 1");
?>
The above example will output:
Sending query for id via Memcache: bool(true)
Sending query for f1 via Memcache: bool(false)
var_dump won't be invoked:
Documents you may be interested
Documents you may be interested
