Downloads

Blog

Investigating MySQL Replication Latency in Percona XtraDB Cluster

March 4, 2013

Author

Share this Post:

I was curious to check how Percona XtraDB Cluster behaves in terms of replication latency (or data propagation latency). Specifically, I wanted to see if stale reads could occur on other nodes immediately after a write.

To test this, I wrote a simple script (included at the end) that:

Writes to one node
Immediately reads from another node
Retries until data propagates
Measures latency

The setup included 3 cluster nodes (DPE1, DPE2, DPE3) connected via a 1Gbit network, with tests run from a separate client server.

Baseline (No Load)

Summary: 94 out of 10000 rounds (0.94%)  Delay distribution: Min: 0.71 ms; Max: 2.16 ms Avg: 0.89 ms

1	Summary: 94 out of 10000 rounds (0.94%) Delay distribution: Min: 0.71 ms; Max: 2.16 ms Avg: 0.89 ms

Key observations:

Replication is asynchronous from a propagation standpoint
Less than 1% inconsistency observed
Average delay under 1ms

With Load on Write Node (DPE1)

sysbench --test=oltp --mysql-user=root --mysql-password="" --oltp-table-size=1000000 --num-threads=32 --init-rng=on --max-requests=0 --oltp-auto-inc=off --max-time=3000 run

1	sysbench --test=oltp --mysql-user=root --mysql-password="" --oltp-table-size=1000000 --num-threads=32 --init-rng=on --max-requests=0 --oltp-auto-inc=off --max-time=3000 run

Summary: 3901 out of 10000 rounds (39.01%) Delay distribution: Min: 0.66 ms; Max: 201.36 ms Avg: 3.81 ms
Summary: 3893 out of 10000 rounds (38.93%) Delay distribution: Min: 0.66 ms; Max: 42.9 ms Avg: 3.76 ms

1 2	Summary: 3901 out of 10000 rounds (39.01%) Delay distribution: Min: 0.66 ms; Max: 201.36 ms Avg: 3.81 ms Summary: 3893 out of 10000 rounds (38.93%) Delay distribution: Min: 0.66 ms; Max: 42.9 ms Avg: 3.76 ms

Results:

~40% inconsistency rate
Average delay still only a few milliseconds

Load on Read Node (DPE2)

Summary: 3747 out of 10000 rounds (37.47%) Delay distribution: Min: 0.86 ms; Max: 108.15 ms Avg: 8.62 ms
Summary: 3721 out of 10000 rounds (37.21%) Delay distribution: Min: 0.81 ms; Max: 291.81 ms Avg: 8.54 ms

1 2	Summary: 3747 out of 10000 rounds (37.47%) Delay distribution: Min: 0.86 ms; Max: 108.15 ms Avg: 8.62 ms Summary: 3721 out of 10000 rounds (37.21%) Delay distribution: Min: 0.81 ms; Max: 291.81 ms Avg: 8.54 ms

Observation:

Similar inconsistency frequency
Higher latency → read-side load impacts propagation more

Write-Heavy Workload

sysbench --test=oltp --oltp-test-mode=nontrx --oltp-nontrx-mode=update_key --mysql-user=root --mysql-password="" --oltp-table-size=1000000 --num-threads=32 --init-rng=on --max-requests=0 --max-time=3000 run

1	sysbench --test=oltp --oltp-test-mode=nontrx --oltp-nontrx-mode=update_key --mysql-user=root --mysql-password="" --oltp-table-size=1000000 --num-threads=32 --init-rng=on --max-requests=0 --max-time=3000 run

Summary: 1062 out of 10000 rounds (10.62%) Delay distribution: Min: 0.71 ms; Max: 285.07 ms Avg: 3.21 ms
Summary: 1113 out of 10000 rounds (11.13%) Delay distribution: Min: 0.81 ms; Max: 275.94 ms Avg: 5.06 ms

1 2	Summary: 1062 out of 10000 rounds (10.62%) Delay distribution: Min: 0.71 ms; Max: 285.07 ms Avg: 3.21 ms Summary: 1113 out of 10000 rounds (11.13%) Delay distribution: Min: 0.81 ms; Max: 275.94 ms Avg: 5.06 ms

Surprisingly better than mixed workload (~11% inconsistency).

Write Load on Read Node

Summary: 5349 out of 10000 rounds (53.49%) Delay distribution: Min: 0.81 ms; Max: 519.61 ms Avg: 5.02 ms
Summary: 5355 out of 10000 rounds (53.55%) Delay distribution: Min: 0.81 ms; Max: 526.95 ms Avg: 5.06 ms

1 2	Summary: 5349 out of 10000 rounds (53.49%) Delay distribution: Min: 0.81 ms; Max: 519.61 ms Avg: 5.02 ms Summary: 5355 out of 10000 rounds (53.55%) Delay distribution: Min: 0.81 ms; Max: 526.95 ms Avg: 5.06 ms

Worst-case scenario:

Over 50% inconsistency
Outliers up to 500ms+

Load on Unused Node (DPE3)

Summary: 833 out of 10000 rounds (8.33%) Delay distribution: Min: 0.66 ms; Max: 353.61 ms Avg: 2.76 ms

1	Summary: 833 out of 10000 rounds (8.33%) Delay distribution: Min: 0.66 ms; Max: 353.61 ms Avg: 2.76 ms

Minimal impact as expected.

Synchronous Reads Option

SET GLOBAL wsrep_causal_reads=1;

1	SET GLOBAL wsrep_causal_reads=1;

This ensures reads wait for replication, providing full consistency.

Large Transaction Impact

UPDATE sbtest2 SET k=k+1;

1	UPDATE sbtest2 SET k=k+1;

Summary: 452 out of 10000 rounds (4.52%) Delay distribution: Min: 0.66 ms; Max: 46526.7 ms Avg: 104.28 ms

1	Summary: 452 out of 10000 rounds (4.52%) Delay distribution: Min: 0.66 ms; Max: 46526.7 ms Avg: 104.28 ms

Key issue:

Large transactions cause massive stalls (up to ~45 seconds)
Due to certification + replication overhead

Observed Stall Behavior

per-request statistics:
min: 0.69ms
avg: 1.12ms
max: 52334.76ms

per-request statistics:

min: 0.69ms

avg: 1.12ms

max: 52334.76ms

Certification can stall unrelated operations across nodes.

Summary

Excellent performance for small transactions
Low latency under normal conditions
Synchronous reads available when needed
Large transactions can cause severe stalls

Recommendation: Understand transaction size and tolerance for latency when designing applications on Percona XtraDB Cluster.

Appendix: Test Script

$writer_host="dpe01";
$reader_host="dpe02";
$user="test";
$password="test";
$table="test.sbtest";

$increment=2;
$offset=1;
$max_id=1000;
$rounds=10000;

$writer=new mysqli($writer_host,$user,$password);
$reader=new mysqli($reader_host,$user,$password);

$total_delay=0;
$min_delay=100000000;
$max_delay=0;
$delays=0;
$sum_delay=0;

for($val=0; $val<$rounds;$val++) {
  $id=rand(1,$max_id);
  $id=floor($id/$increment)*$increment+$offset;
  $writer->query("UPDATE $table set k=$val where id=$id");

  $tw=microtime(true);
  $retries=0;

  while(true) {
    $result=$reader->query("SELECT k from $table where id=$id");
    $row=$result->fetch_row();

    if ($row[0]!=$val)
      $retries++;
    else {
      $tr=microtime(true);
      break;
    }
    $result->close();
  }

  if ($retries!=0) {
    $delay=round(($tr-$tw)*1000,2);
    $delays++;
    $sum_delay+=$delay;
    $min_delay=min($min_delay,$delay);
    $max_delay=max($max_delay,$delay);
  }
}

$writer_host="dpe01";

$reader_host="dpe02";

$user="test";

$password="test";

$table="test.sbtest";

$increment=2;

$offset=1;

$max_id=1000;

$rounds=10000;

$writer=new mysqli($writer_host,$user,$password);

$reader=new mysqli($reader_host,$user,$password);

$total_delay=0;

$min_delay=100000000;

$max_delay=0;

$delays=0;

$sum_delay=0;

for($val=0; $val<$rounds;$val++) {

$id=rand(1,$max_id);

$id=floor($id/$increment)*$increment+$offset;

$writer->query("UPDATE $table set k=$val where id=$id");

$tw=microtime(true);

$retries=0;

while(true) {

$result=$reader->query("SELECT k from $table where id=$id");

$row=$result->fetch_row();

if ($row[0]!=$val)

$retries++;

else {

$tr=microtime(true);

break;

}

$result->close();

}

if ($retries!=0) {

$delay=round(($tr-$tw)*1000,2);

$delays++;

$sum_delay+=$delay;

$min_delay=min($min_delay,$delay);

$max_delay=max($max_delay,$delay);

}

Appendix: PXC Configuration

wsrep_node_address=10.9.9.1
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://10.9.9.1,10.9.9.2,10.9.9.3
wsrep_slave_threads=8
wsrep_sst_method=xtrabackup
wsrep_cluster_name=DPE
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1