* 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.
– 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 :
service mysql bootstrap-pxc
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
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:
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.
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)).
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.