Over a million developers have joined DZone.
Platinum Partner

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

· Performance Zone

The Performance Zone is brought to you in partnership with New Relic. Quickly learn how to use Docker and containers in general to create packaged images for easy management, testing, and deployment of software.

Percona XtraDB Cluster

Accessing Percona XtraDB Cluster nodes in parallel with MySQL asynchronous calls

This post comes from at the MySQL Performance Blog.

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:

$reader_hosts = array( "192.88.225.243", "192.88.225.242", "192.88.225.240", "192.88.225.160", "192.88.225.159" );

$all_links=array();

# Establish connections
foreach ($reader_hosts as $i) {
        $mysqli = new mysqli($i, 'root', '', 'test');
        if ($mysqli->connect_error) {
                echo 'Connect Error (' . $mysqli->connect_errno . ') '
                        . $mysqli->connect_error;
        }else{ 
                $all_links[]=$mysqli;
                $mysqli->query("SET wsrep_causal_reads=1");
        }
}

# Run queries in parallel:

foreach ($all_links as $linkid => $link) {
 $link->query("SELECT something FROM tableN WHERE ", MYSQLI_ASYNC);
}

$processed = 0;
do {
        $links = $errors = $reject = array();
        foreach ($all_links as $link) {
                $links[] = $errors[] = $reject[] = $link;
        }

    # loop to wait on results
    if (!mysqli_poll($links, $errors, $reject, 60)) {
        continue;
    }
    foreach ($links as $k=>$link) {
        if ($result = $link->reap_async_query()) {
            $res = $result->fetch_row();
            # Handle returned result
            mysqli_free_result($result);
        } else die(sprintf("MySQLi Error: %s", mysqli_error($link)));
        $processed++;
    }
} while ($processed < count($all_links));


In 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.

The Performance Zone is brought to you in partnership with New Relic. Read more about providing a framework that gets you started on the right path to move your IT services to cloud computing, and give you an understanding as to why certain applications should not move to the cloud.

Topics:

Published at DZone with permission of Peter Zaitsev , DZone MVB .

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}