Internet Super Hero

PECL/mysqlnd_qc: query cache statistics log

Is it worth the efforts to cache the results of a MySQL query at the client? In most cases the answer is: try it, measure it! Install the development version of the mysqlnd query cache plugin, which can be used with PDO_MySQL, mysqli and mysql. Set three PHP directives and find the answer in a log file.

While updating the query cache plugin to support PHP 5.4, the latest versions of APC and Memcached for cache storage, I virtually stumbled upon an undocumented feature I had long forgotten. The plugin can periodically dump statistics into a log file. The plugin collects tons of statistics and query traces to find cache candidates and for measuring cache efficiency. Details can be found in the quickstart.

Quick and dirty evaluation

A quick and dirty evaluation of the maximum performance gain client-side query caching can give you is obtained by caching all statements. The first PHP directive to set is mysqlnd_qc.cache_by_default = 1. Ignore the fact that the cache may serve stale data because its default invalidation strategy is Time-to-Live (TTL). Quick and dirty…

Weiter lesen »

PHP mysqlnd query cache plugin quickstart is online!

New in the PHP manual: a quickstart for the mysqlnd query cache plugin. PECL/mysqlnd_qc, the mysqlnd query cache plugin, is transparent and ease to use. But, how? Some pointers have been given in assorted presentations, here on my blog and in some, few examples from the manual. Fixed. You can now browse a quickstart to gain a quick overview.

• Architecture and Concepts
• Setup
• Setting the TTL
• Pattern based caching
• Slam defense
• Finding cache candidates
• Measuring cache efficiency
• Beyond TTL: user-defined storage

The PECL/mysqlnd_qc manual is well worth 15 minutes of your attention if you either have a remote MySQL server or, you cannot use the query cache built-in to the MySQL server but want to use query caching. If that’s not the case, you may still want to check its query traces to abuse them for performance monitoring. More on that below.

Caching for the web always follows the same pattern. First, you move caches as close to the client as possible. Then, you select an appropriate granularity for cache contents on all layers for which you plan to use caching. The MySQL server query cache is neat: it never serves stale data and, it comes with MySQL. But, it is not as close to the client as possible. This adds latency and makes scale-by-client a bit more difficult. Unfortunately, there is still no way to connect it to a client-side cache. Thus, PECL/mysqlnd_qc.

Simple monitor from the source distributions web/ directory.

The mysqlnd query cache plugin with its various storage handlers (process memory, APC, Memcache, SQLite, user-defined) has a slide edge over an application centric solution:

• No or minimal application changes
  • no hassle when updating 3rd party solutions
  • can be used even if code change is not possible but auto_prepend can be set
  • compatible with all PHP MySQL APIs (mysqli, PDO_MySQL, mysql)
  • plugs into to the driver: no extra PHP library/software to install
• Flexible storage
  • process scope: process memory
  • single machine scope: APC, Memcache, SQLite (memory)
  • multiple machines scope: Memcache
• Solid monitoring

Whether your cache solution shall have a granularity of individual SQL statements or, for example, should cache rendered HTML fragments is a different story… A nice aspect of PECL/mysqlnd_qc is that you can use it to evaluate the impact of database caching pretty quickly. Turn on caching of all statements, run a benchmark. Whatever database cache solution you end up with, results won’t be better than that. Based on the figures you can decide if its worth the efforts.

The story about monitoring

Even if the caching aspect is not for you, mysqlnd_qc_get_query_trace_log() may appeal to you. To help finding cache candidates the plugin can be instructed to collect a query trace log. The trace contains a statements string, the statements run time, its store time and a backtrace to its origin in the source. Each and every query inspected by the plugin is listed. Because PECL/mysqlnd_qc operated on the driver level, you see all queries from every PHP MySQL extension. The background is described in more detail here.

mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_query_trace=1

/* connect to MySQL */
$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");
	
/* dummy queries to fill the query trace */
for ($i = 0; $i < 2; $i++) {
  $res = $mysqli->query("SELECT 1 AS _one FROM DUAL");
  $res->free();
}
	
/* dump trace */
var_dump(mysqlnd_qc_get_query_trace_log());

array(2) {
  [0]=>
  array(8) {
    ["query"]=>
    string(26) "SELECT 1 AS _one FROM DUAL"
    ["origin"]=>
    string(102) "#0 qc.php(7): mysqli->query('SELECT 1 AS _on...')
#1 {main}"
    ["run_time"]=>
    int(0)
    ["store_time"]=>
    int(25)
    ["eligible_for_caching"]=>
    bool(false)
    ["no_table"]=>
    bool(false)
    ["was_added"]=>
    bool(false)
    ["was_already_in_cache"]=>
    bool(false)
  }
  [1]=>
  array(8) {
    ["query"]=>
    string(26) "SELECT 1 AS _one FROM DUAL"
    ["origin"]=>
    string(102) "#0 qc.php(7): mysqli->query('SELECT 1 AS _on...')
#1 {main}"
    ["run_time"]=>
    int(0)
    ["store_time"]=>
    int(8)
    ["eligible_for_caching"]=>
    bool(false)
    ["no_table"]=>
    bool(false)
    ["was_added"]=>
    bool(false)
    ["was_already_in_cache"]=>
    bool(false)
  }
}

Happy hacking!

Follow me on Twitter - @Ulf_Wendel

PHP mysqli quickstart is online!

New in the PHP manual: a mysqli quickstart. You are new to PHP but you know how to code, you know SQL, you know relational databases and MySQL? Then, I hope, this is for you. All you need is a quick overview on the concepts? The rest is in the reference section! Here you go.

The quickstart contains:

• Dual procedural and object-oriented interface:
  something pioneerd by MySQL when PHP learned OOP at 5.0 times.
• Connections: how, options, persistent/pooled connections.
• Executing statements: buffered, unbuffered, impact of MySQL Client Server protocol flavour used
• Prepared Statements: what, how, pros and cons
• Stored Procedures: how, parameters, prepared statements
• Multiple Statements: what, security considerations
• API support for transactions
• Metadata

In case you prefer listening over reading there is a PHP MySQL web seminar series on PHP for you (hint: search "On Demand" for more). To please everybody, we are giving a webinar summary in german as well on 18.01.2012, register now.

Please, note that I am inpatient and linking to the PHP documentation teams server: http://docs.php.net/manual/en/mysqli.quickstart.php. The php.net mirrors will need a while to catch up.

Happy hacking!

@Ulf_Wendel

PECL/mysqlnd_*: CCC - cloud, cluster, caching!

We are giving PECL/mysqlnd_qc a second chance. PECL/mysqlnd_qc is a query cache plugin for mysqlnd. It can cache any query issued by any PHP MySQL extension using storage handler for process memory, APC, Memcache and SQLlite. Its default invalidation strategy is Time to Live (TTL). Using a more sophisticated invalidation strategy is possible. Of course, its transparent to use and inherits all the other advantages of a driver based approach.

Cloud and Cluster

Albeit quite powerful the plugin came late and is faced with stiff competition from application-based solutions. With everybody talking about cloud and database clusters, there is a new use case for client-side caching.

No all-in-one cluster solution for all purposes exsits. MySQL users, for example, can choose between MySQL Replication, MySQL Cluster and an emerging number of third party solutions, which proof MySQL to be alive and rocking. The CAP theorem makes me assume that MySQL users will continue to have multiple choices.

Whatever cluster solution will become the dominating force, applications should be enabled to see a cluster as a service. Accordingly, PECL/mysqlnd_ms 1.2 has introduced an API to set the required service level. In version 1.2 the service level option focusses on consistency:

• Eventual consistency - stale data allows, e.g. when reading from a MySQL replication slave
• Session consistency - read your writes
• Strong consistency - every client sees all writes

Whatever type of cluster is used, PECL/mysqlnd_ms will try to deliver the requested consistency by selecting appropriate nodes.

Caching

If the application hints the database driver that eventual consistency is sufficient but data returned shall not be more than five seconds behind, the database driver can replace a slow, eventually remote database access with a fast, local cache access. That’s the second chance PECL/mysqlnd_qc will be given.

Documentation updates

Before we begin with it, I have given the PECL/mysqlnd_qc a second chance as well. You can expect to find an improved Quickstart and Examples section at http://docs.php.net/manual/en/book.mysqlnd-qc.php soon. Have a look during the next days, its a cool plugin thing even without the cloud buzzword bingo…

Happy New Year and happy hacking!

@Ulf_Wendel

PHP MySQL documentation updates

The MySQL part of the PHP reference manual is currently being restructured: new landing and overview page, mysqli quickstart prepared. Ten years ago, there was the mysql extension and that was it. Today, beginners are faced with three MySQL APIs/extensions, two libraries and more than three library plugins. MySQL support by PHP has never been better. But, where to start: web search for tutorials, a book? The results one gets tend to be flawed: outdated, incomplete, flawed… Thus, the update.

A landing and overview page

The MySQL documentation staging server already shows the new landing and overview page "MySQL Drivers and Plugins". Its introduction makes the PHP reference manuals "Vendor Specific Database Extensions" section less cluttered by grouping all MySQL information under this new overview page.

Under the address http://docs.php.net/manual/en/mysql.php (later on php.net/mysql may work) users find an overview of the MySQL PHP drivers:

• Terminology overview
• Choosing an API
• Choosing a library

After an overview on terminology the three PHP MySQL APIs/extensions mysql, mysqli and PDO_MySQL are compared. A comprehensive feature matrix helps to get started. Next, it is explained how the extensions connect to MySQL by help of a client library. Again, a comprehensive feature matrix compares the two choices, the MySQL Client Library (AKA libmysql) and mysqlnd.

The landing page continues with links to the mysql, mysqli APIs/extensions, followed by mysqlnd and its various plugins.

Hands-on mysqli quickstart prepared

Many years ago when Zak Greant and Georg Richter developed the mysqli extension they made sure to have examples for each and every function. That’s fantastic but meanwhile there is a logical and good trend to include hands-on quickstarts and background concepts information to PHP manual. We all know, a software is worth nothing without adequate easy to use documentation.The documentation must cover the needs of beginners, intermediate users and experts who need little more than reference materials. A bit like with the ~70 pages of PECL/mysqlnd_ms documentation, which begins with a quickstart and a concepts section before the reference section.

Therefore, I’ve written a mysqli quickstart. You have seen some excerpts of it already in my blog:

• Using MySQL with PHP mysqli: Connections, Options, Pooling
• Executing MySQL queries with PHP mysqli
• Using MySQL prepared statements with PHP mysqli
• Using MySQL multiple statements with PHP mysqli
• Using MySQL stored procedures with PHP mysqli

I hope Philip will be able to review and add the quickstart to the mysqli extension documentation soon. That’s it? Come on, its not yet christmas eve and, do we really need chrismas for making presents…

If you would like to comment on the new contents, please do so on the php.net documentation mailing lists. That’s the appropriate forum for discussions on the PHP reference manual. I’m sure there are still some edges in the new materials that need to be cut off.

.oO( If Andrey and I continued our current pace, where would we be next christmas… )

Happy hacking, happy holidays!

@Ulf_Wendel

Load balancing for PHP and MySQL

A single MySQL server is a single point of failure. A single MySQL server can only be scaled vertically by increasing hardware size, which has its limits. That’s two good reasons to migrate from a single MySQL server to a cluster of MySQL servers. However, in cloudy white christmas times, few appreciate the extra work that using a cluster causes. For example, MySQL connections must be load balanced. Please, find a comparison of different load balancing architectures in the short presentation. Choose the one that’s best for you - maybe it is PECL mysqlnd_ms 1.2, the mysqlnd replication and load balancing plugin…

Seasons greetings!

@Ulf_Wendel

Welcome PECL/mysqlnd_ms 1.2.0-alpha with global transaction ID support

Christmas time, time for presents! Version 1.2.0-alpha of the free and open source PHP mysqlnd replication and load balancing plugin has been made available on PECL. PECL/mysqlnd_ms makes using any kind of MySQL database cluster easier featuring:

• Read-write splitting: automatic, SQL hints, can be disabled
• Load balancing: random, round robin, user defined
• Fail over
• Global transaction ID support: client-side emulation
• Service levels: eventual consistency, session consistency, strong consistency

The last two features are new. The motto/theme of the 1.2 series is: Global Transaction ID injection and quality-of-service concept.

For many years MySQL has adviced PHP developers to implement all kinds of logic needed to use MySQL Replication or MySQL Cluster in the application. PECL/mysqlnd_ms is changing this. Take any PHP MySQL application using any PHP MySQL extension (mysql, mysqli, PDO_MySQL), compile PHP to use the mysqlnd library, install the PECL module, drop it in and, be ready to migrate the application from a single database to a database cluster. At any time, it shall be possible to overrule all automatic decisions of the plugin, whenever needed.

Service levels

One of the biggest challenges when switching from a single database to an asynchronous database cluster, such as a MySQL Replication cluster, is the change in data consistency. A single server delivers strong consistency: all clients see each others changes. A MySQL replication cluster defaults to eventual consistency for a slave access: a client may or may not see its own changes, which is a significantly lower service level. However, in many cases its OK, why else would have an asynchronous approach have gained such popularity.

Version 1.2.0-alpha of mysqlnd_ms introduces a quality of service filter. The application says what service level it needs from the cluster and the plugin delivers:

• Eventual consistency
  • optional parameter: maximum age, don’t read from slaves that lag too far behind
• Session consistency (read your writes)
  • optional parameter: global transaction ID for reading from "up-to-date" slaves
• Strong consistency

A simple function call - mysqlnd_ms_set_qos() - sets the service level.

Further readings:

• Blog posting PECL/mysqlnd_ms: quality of service filter
• New service level concepts section in the PHP mysqlnd_ms manual (more coming)

Global Transaction ID support

In its most basic form a global transaction ID can be described as a transaction counter maintained on the master. If the master fails, the most current slave for master promotion can easily be found by searching the slave with highest transaction ID. The MySQL 5.6 Development Release contains this feature, however, it is neither production ready nor fully functional yet. And, so far, master fail over remains a manual task of the database administrator. Whenever the server solution is ready, mysqlnd_ms users are prepared to make the most out of it.

Beginning with version 1.2.0-alpha, PECL/mysqlnd_ms can to a client-side emulation of the feature and transparently maintain a global transaction ID table on the master. Client-side emulation is a second best solution but one of the few options one has in heterogenous environments with servers that do not have the functionality built-in.

A global transaction ID can also be used for improving session consistency (read your writes) load balancing for MySQL replication. Without a global transaction ID a client cannot say for sure in advance if a slave has already replicated a change. To read the own writes, the client must query the master. By help of a global transaction ID one can find if a certain write has been replicated already by a slave. Read your writes becomes possible on slaves. Read load is taken away from the master.

Further reading:

• Blog posting PECL/mysqlnd_ms global transaction ID injection status
• New global transaction ID concepts section in the PHP mysqlnd_ms manual (more coming)

There is more to say about the release. Please, stay tuned. We started updating the manual. It will take a couple of days to complete the work and until the latest changes have made their way from the PHP manual staging area to the PHP manual mirrors. More blogs are coming as well.

Happy hacking!

@Ulf_Wendel

PS: Pierre, it should build fine on Windows.

PECL/mysqlnd_ms: quality of service filter

What if your PHP application could tell the mysqlnd library what service quality you need when using a MySQL replication cluster? If you wanted read-your-writes, the driver would select replication nodes for you which can offer it. If you can allow replication lag but no more than three seconds, the driver would select… One function call and you get the service you need. That’s what version 1.2 of PECL/mysqlnd_ms is about.

The quality of service filter

In the world of PECL/mysqlnd_ms, the free and Open Source replication and load balancing plugin for mysqlnd, a so-called filter is responsible for choosing nodes for statement execution. A filter looks at the SQL statement to be executed and picks a capable server. The current production ready 1.1 release has three filters. Two load balancing filter (random, round-robin) and a user hook filter (user).

For example, if you run a SELECT statement, the filters of PECL/mysqlnd_ms ensure it ends up on a slave.

PECL/mysqlnd_ms 1.2 brings a new quality-of-service filter. The quality of service filter can be configured in the plugins configuration file but also at runtime. The latter is new and unique to this filter.

Reading from slaves no more than n seconds behind

Certain elements on a web site qualify for simple TTL based caching. Others don’t. One could say, that the quality of service demands for cacheable contents are lower than for non-cacheable. The same goes for SQL statements. Stale data may be OK, but it may not be older than n seconds, you may want to set a time-to-live, so to say.

MySQL Replication is asynchronous. It takes some time until a write operation on the master has been replicated to all slaves. Slaves may not always serve current data. The MySQL administrative statement SHOW SLAVE STATUS give a hint how many seconds a slave is behind the master (Seconds_Behind_Master). It is a rather rough estimation that MySQL makes but its the best built-in logic I am aware of. Please, check the MySQL reference manual for details.

Say your application is fine with reading from slaves that lag no more than three seconds behind the master. With PECL/mysqlnd_ms 1.2 it will be one line to set the required service quality.

mysqlnd_ms_set_qos($link,
  MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL,
  MYSQLND_MS_QOS_OPTION_AGE,
  3);

After the function call, PECL/mysqlnd_ms checks for every statement nodes quality for executing the statement. If the statement is a write, the quality-of-service (qos) filter, returns a list of all masters. If the statement is a read, the qos filter returns all masters and all slaves for which Seconds_Behind_Master <= 3 is true. In the example, a SELECT statement would be run on the master, slave 1 or slave 2, depending on the choice of the load balancing filter. Slave 3 is not used because its replication lag is too high.

To be very clear here: the qos filter may execute SHOW SLAVE STATUS on all slaves. This is an expensive and slow operation. But it is the only option availablewith todays MySQL replication server features. One may try to cache the SHOW SLAVE STATUS results but there’s no fundamental change until the MySQL replication cluster can tell clients which nodes to use.

Don’t drop and forget the idea after this warning. The cool thing is, that if you tell PECL/mysqlnd_ms it may replace the slave access with a local TTL cache access… allow disabling the slow status query, focus on the caching idea, and … But that’s for sure not for 1.2.

Reading from slaves which have replicated a global transaction ID

Setting the maximum age using the qos filter gives you eventual consistency. Using global transaction ids, the qos filter can also offer session consistency or read-your-wrtites. PECL/mysqlnd_ms 1.2 can do global transaction id injection and the MySQL replication team has published a global transaction id feature preview release in October. Because the server preview is not fully functional, 1.2 focusses on its client-side emulation and injection.

$link->query("INSERT INTO test(id) VALUES (123)");
$gtid = mysqlnd_ms_get_last_gtid($link);

Whoever does maintain the global transaction ids, some SQL exists to check if a node has replicated a certain id or not. The qos filter knows the SQL and checks if a node has replicated the transaction in question. If so, the node can be used to achieve read-your-writes.

mysqlnd_ms_set_qos(
  $link,
  MYSQLND_MS_QOS_CONSISTENCY_SESSION,
  MYSQLND_MS_QOS_OPTION_GTID,
  27263);
$link->query("SELECT * FROM test ");

Is it worth it?

The price of the qos filter is high. Checking nodes for every statement is slow. However, if you hit scalability limits of your master because of too many read-your-write requests, there is no choice . PECL/mysqlnd_ms, a driver-based solution, does not do worse than an application-based solution. But, it takes work from you, the application developer.

If that’s not convincing, OK, but what about the caching idea…

Happy hacking!

@Ulf_Wendel

PS: Do you speak portuguese? If so, you may want to check out this PHP Conference Brasil presentation from Airton Lastori.

Waiting for table metadata lock and PECL/mysqlnd_ms

The MySQL administration SQL command SHOW PROCESSLIST may read "Waiting for table metadata lock" in its "State" column for a statement. The statement in question is waiting for another transaction to release a metadata lock. Its a state that may appear when using the global transaction ID injection feature of PECL/mysqlnd_ms 1.2.0-alpha. But only in case of errors and if not using default settings of the plugin. In the worst case, during testing only, I experienced a deadlock with MySQL 5.5.3 or higher which never got resolved automatically.

Provoking metadata lock

Let a transaction update a record in a table. In my specific case it was an failing UPDATE. It failed because the table did not exist. Let a second transaction run a DDL statement, for example, DROP TABLE on the same table. The second transaction is now in waiting for the first transaction to release a metadata lock. Yes, the second transaction is waiting for a lock hold on a non-existing table, if you are using MySQL 5.5.3 or higher… The SQL is syntactically correct, thus the lock is acquired.

$link = new mysqli($host, $user, $passwd, $db, $port, $socket);
$link->autocommit(false);
$link->query(\"UPDATE foo SET bar = bar + 1\");
	
$link2 = new mysqli($host, $user, $passwd, $db, $port, $socket);
$link2->query(\"DROP TABLE IF EXISTS foo\");

mysql> show processlist;
+------+------+--------------------+------+---------+------+---------------------------------+--------------------------+
| Id   | User | Host               | db   | Command | Time | State                           | Info                     |
+------+------+--------------------+------+---------+------+---------------------------------+--------------------------+
| 5754 | root | 192.168.78.1:44307 | test | Sleep   |  160 |                                 | NULL                     |
| 5755 | root | 192.168.78.1:44308 | test | Query   |  160 | Waiting for table metadata lock | DROP TABLE IF EXISTS foo |
| 5756 | root | localhost:60751    | test | Query   |    0 | NULL                            | show processlist         |
+------+------+--------------------+------+---------+------+---------------------------------+--------------------------+

This behaviour is different from previous MySQL versions and it is documented in the MySQL 5.5.3 Changes as an incompatible change: A table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends. . The change fixed issues with the binary log. So far, so good - though surprising.

Under normal circumstances the server will release the lock when the clients commit their transactions or disconnect. If they don’t end their transactions nicely but die, the wait_timeout should detect failing TCP/IP clients and solve the deadlock.

How its related to the plugin

What I was doing here was simlating a failure of the plugin to inject a global transaction ID because the transaction table was not set up. The injection, the UPDATE fails, when the client is not in autocommit mode. My initial idea was that the plugin would then report and error to the application and the application could create the transaction id table, if it wanted. In my test I tried catching the error and did DROP TABLE IF EXISTS trx_table, CREATE TABLE trx_table to recreate the table. My test timed out. And, that day it was time for me to call it a day.

The other day, I checked the processlist and saw the deadlock. My PHP test had stopped and disconnected but the deadlock still existed and borked numerous other test runs. It was necessary to KILL the sleeping lock holder manually.

Error handling in the plugin

The plugin offers two ways of dealing with injection errors. By default the injection error is ignored and the function called by the application is executed nonetheless. If, for example, we are running in transaction mode (auto commit off), the user calls the commit() function and injection of the UPDATE statement for increasing the transaction counter fails, the plugin commits anyway. The transaction ends. Other transactions should not be blocked because the metadata lock on the transaction table is released by ending the transaction. No matter if using MySQL 5.5.3 or earlier.

user_commit()
   if (!inject_trx_id() && report_error)
       return false;
	
  return commit()

Optionally, users can tell the plugin to bubble up the injection error. If so, users should roll back the current transaction calling rollback() to prevent the metadata lock issue. Alternatively, you can check for the error code and try to detect cases when its required to recreate the transaction id table.

Moral

As said, if you go for plugin default settings, no locking issue but possibly gaps and holes in your transaction id table. If you know what you do, if you can adapt your application and code changes are possible, you can manually handle the error and recreate the transaction id table on demand.

PECL/mysqlnd_ms global transaction ID injection status

Baby PECL/mysqlnd_ms 1.2.0-alpha, the PHP MySQL replication and load balancing plugin for mysqlnd, has done its first steps into the direction of global transaction id injection: injection happens, IDs can be queried. A simulated global transaction ids can be described as a transaction counter in a table on a MySQL replication master. Slaves connected to the master replicate the transaction table. If the master fails, its easy to find the most current slave to promote it as the new master. Also, if an application wants to read its writes it can loop over the slaves to check which one has replicated the transaction id of the wrtite in question and read from those “synchronous” slaves. The dream here is: you tell the database cluster what service you need and it delivers (more dreaming).

An early status report

Client-side global transaction ID injection is always a second best solution. Any client-side solution can create gaps and holes. The mysqlnd plugin based solution is for you, if you have only PHP clients accessing your MySQL replication cluster. If there is a single other client, go for a MySQL Proxy based solution or, stay tuned…

Today, the mysqlnd plugin can increment the transaction counter for prepared statement, non-prepared statement in autocommit mode and in transaction mode - short: any statement. Transaction boundaries are detected based on API calls. The plugin does not monitor SQL statements to detect SQL like “COMMIT”. API monitoring is possible since PHP 5.4. Please find a detailed description of the limits in the manual.

"myapp":{
  "master":{
    "master1":{
      "host":"127.0.0.1",
      "port":3306
    }
  },
  "slave":{
    "slave1":{
      "host":"192.168.78.137",
      "port":3307
    }
  },
  "global_transaction_id_injection":{
    "on_commit":"UPDATE test.trx SET trx_id = trx_id + 1",
    "fetch_last_gtid":"SELECT MAX(trx_id) FROM test.trx",
    "check_for_gtid":"SELECT trx_id FROM test.trx WHERE trx_id >= #GTID"
  }
 }
}

The plugin increments the global transaction id:

• … in auto commit mode and…
• … when switching from transaction mode to auto commit mode without explicitly calling commit()
  • before executing a non-prepared statement, in query()
  • before executing a prepared statement, in execute()
• … in transaction mode (auto commit off)
  • before the commit() call

Injection basics

The plugin looks up the “on_commit” entry from the global transaction id section of the plugins configuration file. Then, the plugin executes the statement Users can choose between two different kinds of error handling. By default, any error executing the SQL from “on_commit” is ignored. This is done to allow using the plugin as a drop-in solution. A drop-in solution must not change the original behaviour, it must be transparent. Optionally, this is recommended with new applications, the error can be forwarded to the user “as is”. More on that in a future post.

Database administrators must take care that all masters have a ready-to-use global transaction id counter table. For development we use this very inefficient table. More on better approaches in a future post: Giuseppe, the Data Charmer, has given fascinating tipps earlier this year.

CREATE TABLE `trx` (
  `trx_id` int(11) DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Accessing the transaction ID

The last global transaction ID can be obtained with the new mysqlnd_ms_get_last_gtid() function. The function looks up “fetch_last_gtid” in the plugin configuration and executes the SQL. With many concurrent users you may not get exactly the transaction id of your statement but something reasonable close, something that is likely a bit higher. The function returns a string, in the hope that it gives you some freedom writing fancy SQL for the task.

/* works with mysqli, PDO_MYSQL and mysql */
$link = new mysqli("myapp", "root", "", "test");
$link->query("DROP TABLE IF EXISTS test");
printf("GTID '%s'\n", $ret = mysqlnd_ms_get_last_gtid($link));

Read your writes

PECL/mysqlnd_ms 1.1, the current production version, lets you read your writes when setting the configuration option “master_on_write”. As the name says, all statements will be run on the master. With 1.2.0-alpha and global transaction IDs the plugin can identify slaves ready for reading the just written. This gives you better read load balancing.

/* say what you expect from the cluster... and it delivers! */
mysqlnd_ms_set_qos($link, MYSQLND_MS_QOS_CONSISTENCY_SESSION, MYSQLND_MS_QOS_OPTION_GTID, $gtid);

Tell the plugin what quality-of-service (qos) you need. Set the consistency requirement to session and give the plugin a hint how to find nodes that can deliver session consistency. Use the new function mysqlnd_ms_set_qos() for it. In our initial implementation we loop over the slave list and search for all slaves that have replicated the transaction id. If no slave has caught up, the plugin tells the load balancer to use the configured master (servers) only.

This looks sooo complicated compared to 1.1? Yes. BUT, what if you knew that your query may serve stale result no older than three seconds and you wanted to read from a node which is no more than 3 seconds behind the master? There’s one-liner in version 1.2 for the task:

/* say what you expect from the cluster... and it delivers! */
mysqlnd_ms_set_qos($link, MYSQLND_MS_QOS_CONSISTENCY_SESSION, MYSQLND_MS_QOS_OPTION_AGE, 3);

For early adopters

There are edges in the implementation that need to be cut off. All over. Many edges. Injection seems to work reasonable well. The new read-your-writes (mysqlnd_ms_set_qos()) works as well. However, our initial implementation is neither very efficient nor do we have good error handling for all cases yes: alpha quality.

Given that, should you look at it? If you like the plugin idea, please do. Please, comment on the API as early as possible.

Happy hacking!

@Ulf_Wendel

Global transaction ID support for PECL/mysqlnd_ms

The catchy theme/motto of the PECL/mysqlnd_ms 1.2 release will be Global Transaction ID support. Hidden behind the buzzword are two features. We will allow users to request a certain level of service from the replication cluster (keyword: consistency) and we will do basic global transaction ID injection to help with master failover. Failover refers to the procedure of electing a new master in case of a master failure.

Global Transaction ID support is the 1.2 motto/theme

The two features are somewhat related, thus the theme. In very basic words, the idea of a global transaction ID is to have a sequential number in a table on the master. Whenever a client inserts data, the ID/counter gets incremented. The table is replicated to the slaves. If the master fails, the database administrator checks the slaves to find the one with the hightest global transaction ID. Please find details, for example, in Wonders of Global Transaction ID injection.
What the plugin will do is inject a user-provided SQL statement with every transaction to increment the global transaction counter.

However, there is also a client-side benefit to global transactions IDs. If you want to read-your-writes from a replication cluster, you usually query the master. You won’t go to the slaves, because you do not know if they have replicated your writes already. In case you need read-your-writes, set the master_on_write config setting in version 1.1. In version 1.2 we can offer more, if you want and need it. We can search for a slave who has replicated the global transaction ID of your write to reduce the read-your-write load on the master. The keyword here is consistency and the background posting is Consistency, cloud and the PHP mysqlnd replication plugin. However, consistency is not nearly as nice as a motto as the catchy global transaction ID theme.

Of course, the day the MySQL Server has built-in Global Transaction IDs, we don’t need to do the injection any more. Meanwhile, we give it a try… a report from the hacks of the past two days. Feedback is most welcome.

Warning: this now becomes a posting for hackers, not users. If you are not after implementation details, stop reading. The big news is the theme, nothing else. If you don’t trust any software you have not developed yourself but you like the idea of a replication and load balancing plugin, continue reading.

Weiter lesen »

Consistency, cloud and the PHP mysqlnd replication plugin

Elastic, fantastic: click here to add a MySQL replication database cluster to your cloud configuration. Click - yes, we can! Just one little thing, you need to update your application: consistency model changed. Plan for it. Some thoughts for PECL/mysqlnd_ms 1.x, the PHP mysqlnd replication plugin.

Problem: C as in ACID is no more

A MySQL replication cluster is eventual consistent. All writes are to be send to the master. A write request is considered successful once the master has performed it.

Weiter lesen »

Executing MySQL queries with PHP mysqli

The mysqli quickstart series is coming to an end. Today, the post is about non-prepared statements. You may also want to check out the following related blog posts:

• Using MySQL prepared statements with PHP mysqli
• Using MySQL multiple statements with PHP mysqli
• Using MySQL stored procedures with PHP mysqli

Using mysqli to execute statements

Statements can be executed by help of the mysqli_query(), mysqli_real_query() and mysqli_multi_query() function. The mysqli_query() function is the most commonly used one. It combines executing statement and doing a buffered fetch of its result set, if any, in one call. Calling mysqli_query() is identical to calling mysqli_real_query() followed by mysqli_store_result.

The mysqli_multi_query() function is used with Multiple Statements and is described here.

$mysqli = new mysqli("example.com", "user", "password", "database");
	
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)") ||
    !$mysqli->query("INSERT INTO test(id) VALUES (1)"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

Buffered result sets

After statement execution results can be retrieved at once to be buffered by the client or by read row by row. Client-side result set buffering allows the server to free resources associated with the statement results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets. mysqli_query() combines statement execution and result set buffering.

PHP applications can navigate freely through buffered results. Nagivation is fast because the result sets is hold in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.

$mysqli = new mysqli("localhost", "root", "", "test");
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)") ||
    !$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
	
$res = $mysqli->query("SELECT id FROM test ORDER BY id ASC");
	
echo "Reverse order...\n";
for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) {
  $res->data_seek($row_no);
  $row = $res->fetch_assoc();
  echo " id = " . $row['id'] . "\n";
}
	
echo "Result set order...\n";
$res->data_seek(0);
while ($row = $res->fetch_assoc())
 echo " id = " . $row['id'] . "\n";

Reverse order...
 id = 3
 id = 2
 id = 1
Result set order...
 id = 1
 id = 2
 id = 3

Unbuffered result sets

If client memory is a short resource and freeing server resources as early as possible to keep server load low is not needed, unbuffered results can be used. Scrolling through unbuffered results is not possible before all rows have been read.

$mysqli->real_query("SELECT id FROM test ORDER BY id ASC");
$res = $mysqli->use_result();
	
echo "Result set order...\n";
while ($row = $res->fetch_assoc())
 echo " id = " . $row['id'] . "\n";

Result set values data types

The mysqli_query(), mysqli_real_query() and mysqli_multi_query() functions are used to execute non-prepared statements. At the level of the MySQL Client Server Protocol the command COM_QUERY and the text protocol are used for statement execution. With the text protocol, the MySQL server converts all data of a result sets into strings before sending. This conversion is done regardless of the SQL result set column data type. The mysql client libraries receive all column values as strings. No further client-side casting is done to convert columns back to their native types. Instead, all values are provided as PHP strings.

$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
$mysqli->real_connect("localhost", "root", "", "test");
	
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
	
$res = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
$row = $res->fetch_assoc();
	
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));

id = 1 (string)
label = a (string)

It is possible to convert integer and float columns back to PHP numbers by setting the MYSQLI_OPT_INT_AND_FLOAT_NATIVE connection option, if using the mysqlnd libary. If set, the mysqlnd library will check the result set meta data column types and convert numeric SQL columns to PHP numbers, if the PHP data type value range allows for it. This way, for example, SQL INT columns are returned as integers.

$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
$mysqli->real_connect(\"localhost\", \"root\", \"\", \"test\");
	
if (!$mysqli->query(\"DROP TABLE IF EXISTS test\") ||
    !$mysqli->query(\"CREATE TABLE test(id INT, label CHAR(1))\") ||
    !$mysqli->query(\"INSERT INTO test(id, label) VALUES (1, 'a')\"))
    echo \"Table creation failed: (\" . $mysqli->errno . \") \" . $mysqli->error;
	
$res = $mysqli->query(\"SELECT id, label FROM test WHERE id = 1\");
$row = $res->fetch_assoc();
	
printf(\"id = %s (%s)\n\", $row['id'], gettype($row['id']));
printf(\"label = %s (%s)\n\", $row['label'], gettype($row['label']));

id = 1 (string)
label = a (string)

Happy hacking!

@Ulf_Wendel

Using MySQL with PHP mysqli: Connections, Options, Pooling

Opening a database connection is a boring tasks. But do you know how defaults are determined, if values are omitted? Or, did you know there are two flavours of persistent connections in mysqli? Of course you, as a german reader, know it. I blogged about it in 2009 over at phphatesme.com (Nimmer Ärger mit den Persistenten Verbindungen von MySQL? ) …

Database connections with mysqli

The MySQL server supports the use of different transport layers for connections. Connections use TCP/IP, Unix domain sockets or Windows named pipes.

The hostname localhost has a special meaning. It is bound to the use of Unix domain sockets. It is not possible to open a TCP/IP connection using the hostname localhost you must use 127.0.0.1 instead.

$mysqli = new mysqli("localhost", "root", "", "test");
echo $mysqli->host_info . "\n";
	
$mysqli = new mysqli("127.0.0.1", "root", "", "test", 3306);
echo $mysqli->host_info . "\n";

Localhost via UNIX socket
127.0.0.1 via TCP/IP

Connection parameter defaults

Depending on the connection function used, assorted parameters can be omitted. If a parameter is not given the extension attempts to use defaults values set in the PHP configuration file.

mysqli.default_host=192.168.2.27
mysqli.default_user=root
mysqli.default_pw=""
mysqli.default_port=3306
mysqli.default_socket=/tmp/mysql.sock

The resulting parameter values are then passed to the client library used by the extension. If the client library detects empty or unset parameters, it may default to library built-in values.

Built-in connection library defaults

If the host value is unset or empty, the client library will default to a Unix socket connection on localhost. If socket is unset or empty and a Unix socket connection is requested, a connection to the default socket on /tmp/mysql.sock is attempted.

On Windows systems the host name . is interpreted by the client library as an attempt to open a Windows named pipe based connection. In this case the socket parameter is interpreted as the pipes name. If not given or empty, the socket (here: pipe name) defaults to \\.\pipe\MySQL.

If neither a Unix domain socket based nor a Windows named pipe based connection is to be bestablished and the port parameter value is unset, the library will default to TCP/IP and port 3306.

The mysqlnd library and the MySQL Client Library (libmysql) implement the same logic for determining defaults.

Connection options

Various connection options are available, for example, to set init commands which are executed upon connect or, for requesting use of a certain charset. Connection options must be set before a network connection is established.

For setting a connection option the connect operation has to be performed in three steps: creating a connection handle with mysqli_init(), setting the requested options using mysqli_options() and establishing the network connection with mysqli_real_connect().

Connection pooling

The mysqli extension supports persistent database connections, which are a special kind of pooled connections. By default every database connection opened by a script is either explicitly closed by the user during runtime or released automatically at the end of the script. A persistent connection is not. Instead it is put into a pool for later reuse, if a connection to the same server using the same username, password, socket, port and default database is used. Upon reuse connection overhead is saved.

Every PHP process is using its own mysqli connection pool. Depending on the web server deployment model a PHP process may serve one or multiple requests. Therefore, a pooled connection may be used by one or more scripts subsequently.

Persistent connections

If no unused persistent connection for a given combination of host, username, password, socket, port and default database can be found in the connection pool, mysqli opens a new connection. The use of persistent connections can be enabled and disabled using the PHP directive mysqli.allow_persistent. The total number of connections opened by a script can be limited with mysqli.max_links. The maximum number of persistent connections per PHP process can be restricted with mysqli.max_persistent. Please note, that the web server may spawn many PHP processes.

A common complain about persistent connections is that their state is not reset before reuse. For example, open, unfinished transactions are not automatically rolled back. But also, authorization changes which happened in the time between putting the connection into the pool and reusing it are not reflected. This may be seen as an unwanted side-effect. On the contrary, the name persistent may be understood as a promise that the state is persisted.

The mysqli extension supports both interpretations of a persistent connection: state persisted and state reset before reuse. The default is reset. Before a persistent connection is reused, the mysqli extension implicitly calls mysqli_change_user() to reset the state. The persistent connection appears to the user as if it was just opened. No artefacts from previous usages are visible.

The mysqli_change_user() function is an expensive operation. For best performance, users may want to recompile the extension with the compile flag MYSQLI_NO_CHANGE_USER_ON_PCONNECT being set.

It is left to the user to choose between safe behaviour and best performance. Both are valid optimization goals. For ease of use, the safe behaviour has been made the default at the expense of maximum performance. Please, run your own benchmarks to measure the performance impact for your work load.

Using MySQL multiple statements with PHP mysqli

The series Using X with PHP mysqli continues. After notes on calling stored procedures and using prepared statements, its time for a multiple statement quickstart. A mighty tool, if used with care…

Using Multiple Statements with mysqli

MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

Multiple statements or multi queries must be executed with mysqli_multi_query(). The individual statements of the statement string are seperated by semicolon. Then, all result sets returned by the executed statements must be fetched.

The MySQL server allows having statements that do return result sets and statements that do not return result sets in one multiple statement.

$mysqli = new mysqli("localhost", "root", "", "test");
	
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
	
$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";
	
if (!$mysqli->multi_query($sql))
 echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
	
do {
 if ($res = $mysqli->store_result()) {
  var_dump($res->fetch_all(MYSQLI_ASSOC));
  $res->free();
 }
} while ($mysqli->more_results() && $mysqli->next_result());

array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "0"
  }
}
array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "1"
  }
}

Security considerations

The API functions mysqli_query() and mysqli_real_query() do not set a connection flag for activating multi queries in the server. An extra API call is used for multiple statements to reduce the likeliness of accidental SQL injection attacks. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli_multi_query() is not used, the server will not execute the second, injected and malicious SQL statement.

Prepared statements

Use of the multiple statement with prepared statements is not supported.

Happy hacking!

@Ulf_Wendel

1.1.2-*stable* release of the replication and load balancing plugin for PHP!

PECL/mysqlnd 1.1.2-stable has been released. The mysqlnd replication and load balancing plugin for PHP 5.3/5.4 finally got the download label it deserves: stable, ready for production use! PECL/mysqlnd_ms makes using any kind of MySQL database cluster easier.

• Download PECL/mysqlnd from pecl.php.net
• Documentation at the PHP Reference Manual

Key features

The release motto of the 1.1 series is “cover MySQL Replication basics with production quality”, which shows that the plugin is optimized for supporting MySQL replication cluster. But with its feature set it is not limited to. MySQL Cluster users will also profit from it.

• Automatic read/write splitting
  • can be controlled with SQL hints
  • can be replaced providing callback
  • can be disabled for MySQL Cluster use
• Load Balancing
  • random (pick for every statement or once per request, latter is default)
  • round robin (iterate per statement)
  • can be replaced providing callback
  • can be controlled with SQL hint
• Fail over
  • optional, automatic connect fail over
• Connection pooling
  • Lazy connections (don’t open before use, default)

The plugin can be used with any PHP MySQL API/extension (mysql, mysqli, PDO_MySQL), if the extension is compiled to use the mysqlnd library. Whatever framework, whatever API you use, it should work out-of-the box. As a library plugin, it operates on its own layer below your application. No or very little application-level changes are required.

From 1.0 to 1.1

The 1.1 version has been significantly re-factored and extended. Many pitfalls on connection state changes have been removed. Connection state changes can happen when switching from one cluster node to another, either for load balancing or for read-write splitting. If you are new to developing software for MySQL replication clusters, please check the concepts section of the manual.

The plugins configuration format is now JSON-based. This was done to prepare for hierarchical and nested configurations. A new filter concept has been introduced. Filters works like small Unix utilities which can be stacked. The manual, which has been extended significantly, explains both in great depth. If you prefer blog posts, check out Replication plugin | filter | conquer = 1.1.0 coming.

What’s next?

Tell us! With the 1.1.0 series we have laid necessary foundations in the code base. From here, we can drive in many directions . We can start to look into Global Transaction IDs, coming to the server soon, or we look into replication table filter rule support, or we refine load balancing rules, or….

A minor, though time-intensive thing we are planning is updating the PHP MySQL documentation.

Happy hacking!

Using MySQL prepared statements with PHP mysqli

Starting with PHP mysqli is easy, if one has some SQL and PHP skills. To get started one needs to know about the specifics of MySQL and a few code snippets. Using MySQL stored procedures with PHP mysqli has found enough readers to begin with a “quickstart” or “how-to” series. Take this post with a grain of salt. I have nothing against Prepared Statements as such, but I dislike unreflected blind use.

Using prepared statements with mysqli

The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

Basic workflow

The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is send to the database server. The server performs a syntax check and initializes server internal resources for later use.

The MySQL server supports using anonymous, positional placeholder with ?.

Weiter lesen »

Using MySQL stored procedures with PHP mysqli

A couple of weeks ago a friend of mine asked me how to use MySQL stored procedures with PHP’s mysqli API. Out of curiosity I asked another friend, a team lead, how things where going with their PHP MySQL project, for which they had planned to have most of their business logic in stored procedures. I got an email in reply stating something along the lines: "Our developers found that mysqli does not support stored procedures correctly. We use PDO.". Well, the existing documentation from PHP 5.0 times is not stellar, I confess. But still, that’s a bit too much… it ain’t that difficult. And, it works.

Using stored procedures with mysqli

The MySQL database supports stored procedures. A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure.

Parameter

Stored procedures can have IN, INOUT and OUT parameters. The mysqli interface has no special notion for the different kinds of parameters.

IN parameter

Input parameters are provided with the CALL statement. Please, make sure values are escaped correctly.

$mysqli = new mysqli("localhost", "root", "", "test");
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)"))
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
	
if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
    !$mysqli->query("CREATE PROCEDURE p(IN id_val INT) BEGIN INSERT INTO test(id) VALUES(id_val); END;"))
   echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
	
if (!$mysqli->query("CALL p(1)"))
 echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
	
if (!($res = $mysqli->query("SELECT id FROM test")))
 echo "SELECT failed: (" . $mysqli->errno . ") " . $mysqli->error;
	
var_dump($res->fetch_assoc());

array(1) {
  ["id"]=>
  string(1) "1"
}

Weiter lesen »

PHP and MySQL Cluster: Load Balancing without R/W split

The free Mysqlnd replication and load balancing plugin now offers load balancing and lazy connections independent of read write splitting. This makes the plugin attractive for MySQL Cluster users. All nodes participating in a MySQL Cluster can serve all requests, they all accept read and write requests. No statement redirection needs to be done. An application using MySQL Cluster has only one task: load balance requests over MySQL frontends (SQL Nodes).

If using the new configuration setting mysqlnd_ms.disable_rw_split=1, the plugin will load balance requests over the list of configured master servers. The term master is borrowed from the primary usage scenario of the plugin, which is MySQL Replication. Don’t get confused by it. Master, slave, MySQL frontend - after all those are just servers participating in a certain kind of database cluster. All three terms refer to nodes in a MySQL database cluster. The closest analogy to a MySQL Cluster SQL node is a MySQL replication master, thus you have to configure a list of "masters", if you want to use load balancing without read write splitting. We didn’t introduce a new name for the server list, that’s all. Remember, that MySQL replication is the primary focus of the plugin.

The example plugin configuration file has two MySQL Cluster SQL nodes configured in the "master" list. The "slave" list is intentionally left empty. The load balancing policy is round robin. The plugin will iterate over the server list whenever a statement is to be executed.

lb_only_for_mysql_cluster.ini

{
 "myapp": {
  "master": {
   "master_0":{"host":"localhost"},
   "master_1":{"host":"192.168.78.136"}
  },
  "slave":{
  },
  "filters":{
   "roundrobin":[]
  }
 }
}

Because the plugins primary usage scenario is MySQL replication, we have to set two PHP configuration settings to make it accept more than one master and to disable read write splitting.

mysqlnd_ms.enable=1
mysqlnd_ms.ini_file=lb_only_for_mysql_cluster.ini
mysqlnd_ms.multi_master=1
mysqlnd_ms.disable_rw_split=1

PECL/mysqlnd_ms can be used together with any PHP MySQL API (extension) compiled to use the mysqlnd library. The test script show the use with the two recommended choices, which are mysqli and PDO_MySQL.

lb.php

printf("\nUsing mysqli\n\n");
$mysqli = new mysqli("myapp", "root", "", "test");
var_dump($mysqli->query("SELECT VERSION()")->fetch_assoc());
var_dump($mysqli->query("SELECT VERSION()")->fetch_assoc());
	
printf("\nUsing PDO\n\n");
$pdo = new PDO("mysql:host=myapp;dbname=test", "root", "");
var_dump($pdo->query("SELECT VERSION()")->fetchAll());
var_dump($pdo->query("SELECT VERSION()")->fetchAll());

Finally, putting things together to see them in action…

nixnutz@linux-fuxh:~/php/php-src/branches/PHP_5_3> sapi/cli/php -dmysqlnd_ms.multi_master=1 -d mysqlnd_ms.disable_rw_split=1 -dmysqlnd_ms.enable=1 -dmysqlnd_ms.ini_file=lb_only.ini lb.php
	
Using mysqli
	
array(1) {
  ["VERSION()"]=>
  string(16) "5.1.45-debug-log"
}
array(1) {
  ["VERSION()"]=>
  string(12) "5.6.2-m5-log"
}
	
Using PDO
	
array(1) {
  [0]=>
  array(2) {
    ["VERSION()"]=>
    string(16) "5.1.45-debug-log"
    [0]=>
    string(16) "5.1.45-debug-log"
  }
}
array(1) {
  [0]=>
  array(2) {
    ["VERSION()"]=>
    string(12) "5.6.2-m5-log"
    [0]=>
    string(12) "5.6.2-m5-log"
  }
}

This is the final feature addition before the 1.1.x production ready release, planned still this week. The new feature has been documented but it will take a couple of days until the php.net reference manual shows the latest edits.

Free MySQL web seminar Building High Performance and High Traffic PHP Applications with MySQL - Part 3: Succeed with Plugins on Wednesday, October 26, 2011.

Uh, uh… SQL injection for auto EXPLAIN

Would you like to see the EXPLAIN output for all MySQL queries of any PHP application without changing the application much? Easy-peasy: compile PHP to use the mysqlnd library, install PECL/mysqlnd_uh and paste 22 lines of evil code into your auto_prepend_file .

class conn_proxy extends MysqlndUhConnection {
 public function query($conn, $query, $self = false) {
  if (!$self) {
   $this->query($conn, "EXPLAIN " . $query, true);
   if ($this->getFieldCount($conn)) {
    printf("\tAuto EXPLAIN for '%s'\n", $query);
    $res = $this->storeResult($conn);
    $r = new MysqlndUhresult();
    do {
      $row = NULL;
      $r->fetchInto($res, $row, 2, 1);
      if (is_array($row))
        printf("\t\t%s\n", implode("  ", $row));
    } while (!empty($row));
    $r->freeResult($res, false);
   }
  }
	
  return parent::query($conn, $query);
 }
}
mysqlnd_uh_set_connection_proxy(new conn_proxy());

Not being a PHP hero, I don’t have a PHP application in the cloud to demo how it works. Thus, I wrote a very basic application who serves no other purpose creating a table, inserting some rows and fetching them to demo that the auto EXPLAIN SQL insertion works with any PHP MySQL API: PDO_MySQL, mysqli, mysql. All of them.

$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
$res = $mysqli->query("SELECT t1.id, 'foo', t2.id + 1 FROM test AS t1, test AS t2");
printf("MySQLi has found %d results\n", $res->num_rows);
	
$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "");
$stmt = $pdo->query("SELECT t1.id, 'foo', t2.id + 1 FROM test AS t1, test AS t2");
printf("PDO has found %d results\n", $stmt->rowCount());

        Auto EXPLAIN for 'SELECT t1.id, 'foo', t2.id + 1 FROM test AS t1, test AS t2'
                1  SIMPLE  t1  ALL          3
                1  SIMPLE  t2  ALL          3  Using join buffer (BNL, incremental buffers)
MySQLi has found 9 results
        Auto EXPLAIN for 'SELECT t1.id, 'foo', t2.id + 1 FROM test AS t1, test AS t2'
                1  SIMPLE  t1  ALL          3
                1  SIMPLE  t2  ALL          3  Using join buffer (BNL, incremental buffers)
PDO has found 9 results

If you want to learn more about PECL/mysqlnd_uh, please browse the blog archive and check out the manual. Also, don’t miss the MySQL “Succeed with Plugins” webinar next week. Credits go to Mayflower OpenSource Labs for the original development of this mysqlnd plugin!

Unfortunately I am cheating a bit when writing “easy-peasy”. The basics are really easy. But… please, note that the above is bloody, fresh meat. To toy around with SQL injection that requires fetching result sets, you have to build the development version of PECL/mysqlnd_uh. Please, keep also in mind that PECL/mysqlnd_uh exposes the internal mysqlnd C library interface to the PHP user. The mysqlnd library has been written to be used by C developers. Giving PHP hackers, which are used to garbage collection and other safety belts, access to it was never planned. Inappropriate use may cause memory leaks and crashes.

Use PECL/mysqlnd_uh for debugging and prototyping mysqlnd plugins. Test well before using in mission critical environments. Generally speaking: if your proxy script works once, it should always do. Good enough for your development machine. In production environments, you will usually want to write your mysqlnd plugins in C for performance reasons anyway. Using C has the additional advantage of gaining access to all feature not just those made available by an Internet Super Hero, to have nice examples for a web seminar.

Having said all this, who is the first to identify the line that needs to be changed to make the example leak memory? Give me one line, dear reader. Just one line.

Happy hacking!

@Ulf_Wendel