Since XtraDB cluster supports synchronous replication, is there any impact on write query performance?
We plan to use XtraDB cluster with 3 nodes, with only one node used for writing. Would we see any write performance change compared to standard replication using one master and two slave (read-only) servers?
Write performance will be impacted by using synchronous replication at the
COMMIT phase of a transaction. Keep in mind that Galera uses parallel
apply. This improves performance since all slaves apply the changes in
parallel and concurrent requests can commit in parallel without blocking
each other.
Geographically distributed replication will also work just fine as long as
your clients can deal with additional latency on commit.
MySQL semi-sync replication won't work in a geographically distributed
system because commits are serialized (not true for XtraDB cluster with
Galera replication).
On Tue, Oct 23, 2012 at 12:04 PM, Nikola Savić <nik...@osic.rs> wrote:
> Hi all,
> Since XtraDB cluster supports synchronous replication, is there any
> impact on write query performance?
> We plan to use XtraDB cluster with 3 nodes, with only one node used for
> writing. Would we see any write performance change compared to standard
> replication using one master and two slave (read-only) servers?
> Thanks,
> Nikola
> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To post to this group, send email to percona-discussion@googlegroups.com.
On Tuesday, October 23, 2012 10:09:10 PM UTC+2, Justin Swanhart wrote:
> Hi,
> Write performance will be impacted by using synchronous replication at the > COMMIT phase of a transaction. Keep in mind that Galera uses parallel > apply. This improves performance since all slaves apply the changes in > parallel and concurrent requests can commit in parallel without blocking > each other.
> Geographically distributed replication will also work just fine as long as > your clients can deal with additional latency on commit.
> MySQL semi-sync replication won't work in a geographically distributed > system because commits are serialized (not true for XtraDB cluster with > Galera replication).
> --Justin
> On Tue, Oct 23, 2012 at 12:04 PM, Nikola Savić <nik...@osic.rs<javascript:>
> > wrote:
>> Hi all,
>> Since XtraDB cluster supports synchronous replication, is there any >> impact on write query performance?
>> We plan to use XtraDB cluster with 3 nodes, with only one node used for >> writing. Would we see any write performance change compared to standard >> replication using one master and two slave (read-only) servers?
>> Thanks,
>> Nikola
>> -- >> You received this message because you are subscribed to the Google Groups >> "Percona Discussion" group.
>> To post to this group, send email to percona-d...@googlegroups.com<javascript:>
>> .
Hi,
so basically, it is a bit like NDB, it benefits from a higher
concurrency to overcome the network latency. What's a sane setting for the
parallel apply concurrency?
Regards,
Yves
On Tue, Oct 23, 2012 at 4:09 PM, Justin Swanhart <
> Write performance will be impacted by using synchronous replication at the
> COMMIT phase of a transaction. Keep in mind that Galera uses parallel
> apply. This improves performance since all slaves apply the changes in
> parallel and concurrent requests can commit in parallel without blocking
> each other.
> Geographically distributed replication will also work just fine as long as
> your clients can deal with additional latency on commit.
> MySQL semi-sync replication won't work in a geographically distributed
> system because commits are serialized (not true for XtraDB cluster with
> Galera replication).
> --Justin
> On Tue, Oct 23, 2012 at 12:04 PM, Nikola Savić <nik...@osic.rs> wrote:
>> Hi all,
>> Since XtraDB cluster supports synchronous replication, is there any
>> impact on write query performance?
>> We plan to use XtraDB cluster with 3 nodes, with only one node used for
>> writing. Would we see any write performance change compared to standard
>> replication using one master and two slave (read-only) servers?
>> Thanks,
>> Nikola
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Percona Discussion" group.
>> To post to this group, send email to percona-discussion@googlegroups.com.
> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To post to this group, send email to percona-discussion@googlegroups.com.
On Oct 23, 2012, at 4:36 PM, Yves Trudeau <y...@percona.com> wrote:
> so basically, it is a bit like NDB, it benefits from a higher concurrency to overcome the network latency. What's a sane setting for the parallel apply concurrency?
There are some issues with parallel apply, so the current default is 1 thread. There is a show status variable called wsrep_cert_deps_distance which helps you determine what an optimal amount of parallel threads might be by measuring the: "Average distance between highest and lowest seqno that can be possibly applied in parallel (potential degree of parallelization)."
> Hi,
> so basically, it is a bit like NDB, it benefits from a higher
> concurrency to overcome the network latency. What's a sane setting > for the
> parallel apply concurrency?
Hi Yves,
You're absolutely right that one way to counter COMMIT latency is increasing client concurrency (the other is making transactions longer - fewer commits). However in this case it is client concurrency and is unrelated to apply concurrency. The latter is to overcome IO and single core bottlenecks on a slave.
The issues with parallel applying that Jay mentioned happen with DELETEs on tables without primary/unique keys which people seem to love so much nowadays. There also have been issues with foreign key constraints, but they have been fixed as of the current source head. Other than that there is a 64K limit on a number of parallel slave threads. The sane setting depends on the load and you won't know it until you run it and check wsrep_cert_deps_distance. So as rule of thumb we'd recommend 4 per proper CPU core. Another consideration is that if your cluster has N nodes each serving M clients then you may want to have (N-1)*M slave threads per node to make sure it can handle all of the load. At least thats a clear upper bound.
Regards,
Alex
-- Alexey Yurchenko,
Codership Oy, www.codership.com Skype: alexey.yurchenko, Phone: +358-400-516-011
