mysqldump causes entire cluster to slow down

[Kane Davis]

Hey,

We have a 3 node cluster setup, currently all traffic runs to node 1, node 2 and 3 are idle.  Node 1 receives around 500 requests per minute, fair mix of read and write.

When we run mysqldump on node 2 or 3 (on a 50 million row table), it instantly slows down the processing on node 1. 

Likewise the same thing occurs when restoring a large table to node 2 or 3, it slows down node 1 to unacceptable levels (queries that would typically complete in a fraction of a second instead take minutes).

Any help would be appreciated.

Cheers.

[alexey.y...@galeracluster.com]

On 2014-07-01 16:22, Kane Davis wrote:
> Hey,
>
> We have a 3 node cluster setup, currently all traffic runs to node 1,
> node
> 2 and 3 are idle. Node 1 receives around 500 requests per minute, fair
> mix
> of read and write.
>
> When we run mysqldump on node 2 or 3 (on a 50 million row table), it
> instantly slows down the processing on node 1.

Well, for mysqldump temporarily setting wsrep_desync=ON on that node
should help.

> Likewise the same thing occurs when restoring a large table to node 2
> or 3,
> it slows down node 1 to unacceptable levels (queries that would
> typically
> complete in a fraction of a second instead take minutes).

You do understand that inserting a row on node3 automatically inserts it
on nodes 1 and 2, right? That's the whole point of the cluster, isn't
it? So if you load one node with a huge amount of data to write, other
nodes have to do it too, right? Don't load it so much (and for that
regular standalone mysql tips and tricks should help) ;)

Regards,
Alex