Ulf Wendel

PECL/mysqlnd_ms global transaction ID injection status

2011/11/29 by admin

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

Comments are closed.