How transparent does master slave load balancing support for mysqlnd have to be? Transparency, when driven to the extremes, has three limiting factory: power users not needing it, increase of messages send to MySQL, time intensive SQL monitoring and parsing. The PECL/mysqlnd_ms proof-of-concept mysqlnd plugin is almost transparent to make it easy to use. Almost because there are some design limits. Please let us know how you rate the severity of these limits. For example, can we ignore transactions? Your feedback on the previous blog post has been most helpful – thanks!

Give me four paragraphs, one minute, for the introduction and background.

A foggy day. Somewhat transparent.

Change: a connection handle represents a pool

By default PECL/mysqlnd_ms hides what it does from the application developer. PHP application open a connection using any of the existing PHP MySQL APIs – mysqli, mysql, PDO_MySQL – and get a connection handle. Traditionally the connection handle represents one physical network connection to one server (1:1 relationship). Every MySQL query executed with the connection handle ends up on the same server.

/* all of those will be load balanced */
$mysqli = new mysqli("second_best_forum", "user", "password");
$mysql = mysql_connect("second_best_forum", "user", "password");
$pdo = new PDO("mysql:host=second_best_forum", "user", "password");

You made 50% of the introduction….

With the master slave load balancing plugin a connection handle represents a pool of physical network connections to the master and slave servers (1:n relationship). Although semantics have changed, the API is still the same. A connection handle shall behave like ever to make using the master slave load balancing plugin as easy as possible.

´

Connections have a state

The trouble is that every connection in the pool has its own state. Whenever the state of a connection changes one needs to decide if the change shall be reflected in the other connections from the pool. For example, if you change the character set when connected to Slave 1, do you expect Slave n, which may be running your next load balanced query, to use the character set you have just set? When it comes to character sets there is not much of a discussion. Character set play into string escaping (e.g. mysqli_real_escape_string()) and thus the change of the character set must be dispatched to all connections in the pool, must it?

You made it….

We are now at the hearth of the discussion. You can change the character set either via API calls (e.g. mysqli_set_charset()) or using SQL statements such as SET NAMES. It is strongly recommended to use the API calls. The client does the string escaping and the client needs to know the current charset. If you use the SQL command, the client will not recognize the new charset … welcome my dear website hacker.

Ouch: transactions

The client cannot recognize the connection state change, if done trough SQL statements unless the client monitors all SQL. But parsing all SQL statements on the client is not much of an option for performance reasons. Unfortunately there is no MySQL C API and PHP mysql, mysqli extension API counterpart to a very common set of SQL statements changing the state of a connection: START TRANSACTION, COMMIT and ROLLBACK. Ouch… PECL/mysqlnd_ms cannot handle it.

Incomplete list of issues

Question: Connector/J is using autocommit setting to as kind of a switch disable/enable load balancing, you want the same? There are API calls in the mysqli and PDO_MySQL extension to monitor it. The old mysql extension, as usual, is out. No matching API call.

Workaround: let the application handle it!

Many of the limitations can be lifted, if the application hints PECL/mysqlnd_ms. In other words: the load balancer is no longer transparent :-/. The workaround is easy most of the time. The hinting can be done using SQL hints or introducing additional API calls provided by PECL/mysqlnd_ms. For example, if you want to run a transaction on a slave, here is how you do.

$mysqli = new mysqli("second_best_forum", "user", "password");
/* enforce use of slave */
$mysqli->query(sprintf("/*%s*/START TRANSACTION", MYSQLND_MS_SLAVE_SWITCH));
/* disable load balancing using SQL hint */
$mysqli->query(sprintf("/*%s*/SET @myvar=1", MYSQLND_MS_LAST_USED_SWITCH));
$mysqli->query(sprintf("/*%s*/COMMIT", MYSQLND_MS_SLAVE_SWITCH));

My personal take is that moving some responsibilities, such as taking care of SQL transactions, SQL user variables, SQL syntax of prepared statements, … is perfectly valid. To handle those things in a transparent – from an applications point of view – way, the mysqlnd plugin would have to do time consuming SQL parsing. Most of you, who are using replication today, are aware of these issues. Thus, no big deal?

Thanks again for you feedback!