With the last Percona XtraDB Cluster (PXC) release, two major features were added:

* a new command to bootstrap the cluster was added to the init script
* SST via Xtrabackup now supports Xtrabackup 2.1 features

In this post, I want to explain the benefits of these added features and how to use them.

If you follow the mysqlperformanceblog regularly, you’ve already noticed that there are several ways to start a node and that it’s not always easy to start the node that is supposed to bootstrap the entire cluster.

See :
– https://www.percona.com/blog/2013/01/29/how-to-start-a-percona-xtradb-cluster/
– https://www.percona.com/doc/percona-xtradb-cluster/manual/bootstrap.html
– http://www.lefred.be/node/167
– new undocumented option –wsrep-new-cluster

With the new option, bootstrap-pxc, there is no need to modify my.cnf or add parameters (when supported) to the init script. Now you can simply bootstrap (start the first node that will initiate the cluster) using one of these commands :

or

Much easier, isn’t it ?

Now the second added feature is the support of xbstream from Xtrabackup 2.1.

This is useful as it will help to speed up SST operations as the backup could be performed in parallel, compressed and compacted (without copying the secondary indexes).

To configure this, you just need to add two sections to my.cnf: [sst] and [xtrabackup] like this:

Some example to compare the effect of these parameters.

We have one database with 4 InnoDB tables of 480M, and we test Xtrabackup SST with the following settings:

Blue:Xtrabackup with tar (default)
Red: Xtrabackup with xbstream and 2 threads (like the configuration above)
Yellow: Xtrabackup with xbstream and 4 threads

SST_XBSTREAM

This quick benchmark was realized on a small dataset with tables not having secondary indexes (no benefits from compact). I will prepare a more detailed benchmark for a future blog post.

But we can see that the new settings helps to “free” the donor faster, but the process on the joiner takes more time.
So it depends on what are your constraints to choose the best options that fit your workload’s requirements.

Remember: to be able to use Xtrabackup as SST you need to add in my.cnf:
wsrep_sst_method=xtrabackup
wsrep_sst_auth=user:password

Important: to be able to use xbstream with compression, you need to have qpress installed on the system and in the mysql user’s path (like /usr/bin for example).

Also the MySQL datadir must be empty before starting the SST process (see lp:1193240)

If you want to get familiar with these new options, I’ve updated my puppet recipes available on github.

Note:
For rebuild-threads, the default is $(grep -c processor /proc/cpuinfo) and for qpress, the decompression is also done in parallel (with number of qpress instances == $(grep -c processor /proc/cpuinfo)).

6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Alex

Hi Fred,
It also would be very interesting to see the results for multiple threads but without compact and compress options. The best donor result shows about ~50 seconds – for 480Mb data it is ~10Mb/sec, if compressed, then even less. This is far from saturation of 1Gb network. This suggests that the bottleneck is CPU – as it normally is in SST, even with rsync. What I’m insinuating is that all you need is parallel threads, and without compact option you’re likely to get far better results on joiner, and without compress – on both donor and joiner.

Regards.

0
Frederic Descamps

Hi Alex,

Yes sure, I’m planning to benchmark all options on decent machines with a realistic database (multiple secondary indexes, …)

Cheers,

0
Peter Zaitsev

Fred,

Why would you use “compact” for state transfer ? I can understand compressed and multi thread which can be helpful when transferring over slow network. Compact may make sense in theory for even slower network but I doubt someone would run cluster with extremely poor network speed.

0
Raghavendra

Regarding compact, compacting the backups, especially when there
are multiple large secondary indexes (which is common), should
really reduce the size of payload transferred among nodes during
SST. For a table with only the primary key it won’t have any
visible impact.

0
Nick

https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1222122

Does the datadir still have to be empty?

0
Laurent

Hi Fred,

Thanks for the post ! I have to replicate a Galera node to an Australian AWS EC2 instance and Amazon caps the bandwidth to 8 Mbits/s per connection, so the parallel threads are really required.

I have been looking at the XtraDB Cluster 5.6 doc and cannot find anything about the [xtrabackup] allowed parameters.

I guess some of the xtrabackup options (incremental, move-back, etc) are not relevant for SST. Where can I find a list of all available params ?

0