Serveral issue with 50GB data

[Gea-Suan Lin]

Hello all,

I've setup a Galera cluster (using percona apt) trying to migrate our database to Galera cluster:

ii  percona-xtradb-cluster-galera-2.x   112.squeeze                  Galera components of Percona XtraDB Cluster

ii  percona-xtradb-cluster-server-5.5   5.5.23-23.5-333.squeeze      Percona Server database server binaries

There are three nodes in this cluster currently, 2 mysqld (called node1 & node2), 1 garbd (called node3). Server hardware of mysqld is E5620*2 + 64GB RAM + 8*15KRPM 300GB SAS (running RAID1+0), and of garbd is Linux vserver. This cluster is about 50GB InnoDB data (ROW_FORMAT is COMPRESSED). All system running on Debian 6 + XFS (noatime & nodiratime).

Here is my questions,

1. When node2 shutdown, and then back, sometimes I'll see node2 needs to run rsync with node1. Is this behavior correct ?

2. As 1. question, when node1 & node2 running rsync to sync data, do both read and write operations be blocked ?

Thanks,

[Henrik Ingo]

On Fri, May 25, 2012 at 4:12 PM, Gea-Suan Lin <darkk...@gmail.com> wrote:
> 1. When node2 shutdown, and then back, sometimes I'll see node2 needs to run
> rsync with node1. Is this behavior correct ?

Yes. If there were only a few changes on node1, then node2 can request
Incremental State Transfer and just copy those changes and catch up.
If there were too many changes on node1, then node2 will request a
full State Snapshot Transfer, which in your case is configured to use
rsync. It takes a full copy of the node1 database.

> 2. As 1. question, when node1 & node2 running rsync to sync data, do both
> read and write operations be blocked ?

For rsync, yes. To fix:

Use more than 2 nodes, then you will still have one left that is not blocked.

or

In Percona XtraDB Cluster, you can use xtrabackup for sst method. (I
haven't used it, but it might work just by changing
wsrep_sst_method=xtrabackup). Xtrabackup is a non-blocking way of
copying the database, so node1 will be able to continue to operate.

henrik

--
henri...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

[Alexey Yurchenko]

On Friday, May 25, 2012 4:45:23 PM UTC+3, Henrik Ingo wrote:

On Fri, May 25, 2012 at 4:12 PM, Gea-Suan Lin wrote:
> 1. When node2 shutdown, and then back, sometimes I'll see node2 needs to run
> rsync with node1. Is this behavior correct ?

Yes. If there were only a few changes on node1, then node2 can request
Incremental State Transfer and just copy those changes and catch up.
If there were too many changes on node1, then node2 will request a
full State Snapshot Transfer, which in your case is configured to use
rsync. It takes a full copy of the node1 database.

The point at which SST becomes needed depends on the gcache.size Galera option. By default it is 128M, which may be not so much for the hardware like this. For example setting

wsrep_provider_options="gcache.size=10G"

should be more appropriate for the database of such size and reduce the number of occurrences when full SST is needed.

> 2. As 1. question, when node1 & node2 running rsync to sync data, do both
> read and write operations be blocked ?

For rsync, yes. To fix:

Use more than 2 nodes, then you will still have one left that is not blocked.

or

In Percona XtraDB Cluster, you can use xtrabackup for sst method. (I
haven't used it, but it might work just by changing
wsrep_sst_method=xtrabackup). Xtrabackup is a non-blocking way of
copying the database, so node1 will be able to continue to operate.

yes, wsrep_sst_method=xtrabackup shall do the trick but you also need to install Xtrabackup 2.0 for that. Also note that even though it is non-blocking on donor, datadir recovery on joiner will still take quite a lot of time (yet I found that it can be quite faster than recovery after rsync).
 
Regards,
Alex

[Gea-Suan Lin]

On Saturday, May 26, 2012 3:41:00 AM UTC+8, Alexey Yurchenko wrote:

On Friday, May 25, 2012 4:45:23 PM UTC+3, Henrik Ingo wrote:

On Fri, May 25, 2012 at 4:12 PM, Gea-Suan Lin wrote:
> 1. When node2 shutdown, and then back, sometimes I'll see node2 needs to run
> rsync with node1. Is this behavior correct ?

Yes. If there were only a few changes on node1, then node2 can request
Incremental State Transfer and just copy those changes and catch up.
If there were too many changes on node1, then node2 will request a
full State Snapshot Transfer, which in your case is configured to use
rsync. It takes a full copy of the node1 database.

The point at which SST becomes needed depends on the gcache.size Galera option. By default it is 128M, which may be not so much for the hardware like this. For example setting

wsrep_provider_options="gcache.size=10G"

should be more appropriate for the database of such size and reduce the number of occurrences when full SST is needed.

I've set this to 64G (due to large number of write operations), and then it looks fine now. Unless I shutdown node1 for a long time, it will not do full re-sync.

 

> 2. As 1. question, when node1 & node2 running rsync to sync data, do both
> read and write operations be blocked ?

For rsync, yes. To fix:

Use more than 2 nodes, then you will still have one left that is not blocked.

or

In Percona XtraDB Cluster, you can use xtrabackup for sst method. (I
haven't used it, but it might work just by changing
wsrep_sst_method=xtrabackup). Xtrabackup is a non-blocking way of
copying the database, so node1 will be able to continue to operate.

yes, wsrep_sst_method=xtrabackup shall do the trick but you also need to install Xtrabackup 2.0 for that. Also note that even though it is non-blocking on donor, datadir recovery on joiner will still take quite a lot of time (yet I found that it can be quite faster than recovery after rsync).

percona-xtrabackup is included (as dependency) from percona-xtradb-cluster, so I just need to change my.cnf :-)

And yes, xtrabackup will not block query according to test result. Just remember to setup wsrep_sst_auth in my.cnf... this costs me serveral hours to find out :-)

Another issue is percona-xtrabackup is 2.0.0 currently, and in this version we cannot dump >8GB InnoDB files. This bug has been fixed in 2.0 bzr branch (will be included in 2.0.1 according to 2.0.1 milestone page), so just download and build/install it, then everything works great.

Thanks the help to Henrik and Alex.