EmergencyEMERGENCY? Get 24/7 Help Now!

Accessing Percona XtraDB Cluster nodes in parallel from PHP using MySQL asynchronous queries


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button
Percona XtraDB Cluster

Accessing Percona XtraDB Cluster nodes in parallel with MySQL asynchronous calls

This post is followup to Peter’s recent post, “Investigating MySQL Replication Latency in Percona XtraDB Cluster,” in which a question was raised as to whether we can measure latency to all nodes at the same time. It is an interesting question: If we have N nodes, can we send queries to nodes to be executed in parallel?

To answer it, I decided to try a new asynchronous call to send a query to MySQL using a new MySQLnd driver for PHP. In this post I’ll only show how to make these calls, and in following posts how to measure latency to all nodes.


PHP does not provide a way for parallel execution, so this is where an asynchronous call helps. What does this call do? Basically we send a query to MySQL, and we do not wait for result but rather get response later.

The MySQLnd driver has been available since PHP 5.3, and in most part it mimics the standard MySQLi driver and functions it provides. But in addition to that, it also provides a function, mysqli_poll, which unfortunately is marked as “not documented,” however we still can use it — using an example from PHP docs.

So there is my example on how to access Percona XtraDB Cluster nodes in parallel:

As conclusion, we see that using PHP with MySQLnd drivers we can execute 5 MySQL asynchronous queries in parallel against 5 different nodes of Percona XtraDB Cluster.

Share Button
PREVIOUS POST
NEXT POST


Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.



Tags:

, , , , , ,

Categories:
MySQL, XtraDB Cluster


Comments
  • This reminds me of Cassandra’s multiget() and non-blocking socket with select()

    Will be interesting for what practical purpose this can be used.

    Reply

  • If MySQLnd was coupled with a UI that allowed for data to be pushed via websockets, then it would be a very interesting mechanism for a single request to initiate multiple different fetches and actions, all of which could be returned asynchronously over the socket to a waiting handler.

    Reply

  • Great article. We’ve been looking for a way to do this, I wonder if this is available with PDO interface for php/mySQL?

    It would seem this would be great for de-aggregating queries of the following type where the final number of rows is small, but the aggregation is terribly slow and could be better done in an array to avoid filesorts and to execute the sub-queries in parallel.

    Reply

Leave a Reply

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.