Galera and proper checking of status from load balancer.

[Ilias Bertsimas]

Hello,

I am trying to figure out when a galera node is able to serve requests so the load balancer can direct traffic to it.

Right now I am using a check to get the galera status number and if it is 4 (SYNCED) I consider it able to serve requests.

The issue I am having is when I run the daily backup using xtrabackup. In the last 5 mins of the backup when everything gets locked and the transaction log is streamed it seems the other nodes do not serve requests either although there is nothing logged about it and if they are affected by locked node.

Kind Regards,

Ilias.

[Henrik Ingo]

On Wed, Oct 10, 2012 at 11:30 PM, Ilias Bertsimas <awar...@gmail.com> wrote:
> Hello,
>
> I am trying to figure out when a galera node is able to serve requests so
> the load balancer can direct traffic to it.
> Right now I am using a check to get the galera status number and if it is 4
> (SYNCED) I consider it able to serve requests.

This is an ok way to do it.

Percona XtraDB Cluster FAQ provides a different approach, both are good:

http://www.percona.com/doc/percona-xtradb-cluster/faq.html#q-how-can-i-check-the-galera-node-health

In theory the approach recommended by Percona is arguably better,
because then you'd be checking the thing you actually care about:
whether you can access tables in InnoDB. In theory you could have a
situation where the node claims to be synced to the cluster but is
malfunctioning in other ways, like due to an InnoDB bug or such. In
practice if that where the case, this would also affect Galera (if it
couldn't write to InnoDB tables anymore) and the cluster would quickly
become not-synced anyway.

I hope the above makes sense and I didn't confuse anyone :-)

> The issue I am having is when I run the daily backup using xtrabackup. In
> the last 5 mins of the backup when everything gets locked and the
> transaction log is streamed it seems the other nodes do not serve requests
> either although there is nothing logged about it and if they are affected by
> locked node.

This sounds like something is not quite right. Xtrabackup should not
lock for that long.

Could you provide a bit more detail around this? Like a sequence of
steps you do and what symptoms do you see. (If this might be an
xtrabackup issue, of course percona-discuss mailing list could be a
better place to ask.)

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

[Ilias Bertsimas]

Hi Henrik,

What you mentioned is pretty clear to me.

I have a 3 nodes galera cluster and I run innobackupex --galera-info --tmpdir=/shared/backup/galera-data/tmp -user $SQL_USER --password=$SQL_PASSWORD --stream=tar /shared/backup/galera-data/tmp | gzip - > ${BACKUP_DIR}/${THIS_SERVER}_bdd_all.tar.gz

On one of them. Although the load balancer is not even using actively that node (it is a failover) the other ones seem to stall serving requests for about 2 mins when the backup is in the last phase of locking and flushing to disk all tables. All the nodes are shown up on the load balancer for the whole procedure even in the end when it locks until it streams the transaction log which is quite big as we have a heavy write workload.

Kind Regards,

Ilias.

[Henrik Ingo]

Do you have some MyISAM tables then? Xtrabackup is of course only
non-blocking for InnoDB tables?

You can check this with
SHOW CREATE TABLE [table]
on specific tables, or
mysqldump --no-data --all-databases
for all tables.

That the whole cluster blocks when there is a lock is actually correct
behavior (as far as I can tell). If you issue a LOCK TABLE then that
is what happens. Since the replication is synchronous, all nodes will
wait for the lock to be released.

henrik

[Alex Yurchenko]

Well, if the node is SYNCED, then it is... right, synced. And if you
block it - it blocks the whole cluster indeed. (And yes, it appears
xtrabackup can block for quite some time).

Your options here:

1) act according to this:
http://www.codership.com/wiki/doku.php?id=data_backup - essentially this
requires a slightly modified wsrep_sst_xtrabakup script.

2) on the backuped node set wsrep_provider_options="gcs.fc_limit=1M" -
to relax flow control on this node. But it can get ugly in some other
ways, so not recommended.

Regards,
Alex

> Kind Regards,
> Ilias.

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[Ilias Bertsimas]

There are no MyISAM tables in the DB and seems what Alexey is talking about is what actually seems to be happening.

Are there any scripts available for doing a backup like an sst transfer ?

[Ilias Bertsimas]

I am also considering using the --no-lock option on xtrabackup as stated in the config it will skip the flush tables with read lock at the end but it will not get you an accurate binlog position of the backup which I do not need anyways.

Are there any dangers with that option combined with galera ?

Thanks!

[Henrik Ingo]

I would guess that likewise you will then not get reliable Galera
state information, as supported by xtrabackup 2.0
(http://www.mysqlperformanceblog.com/2012/04/04/announcing-percona-xtrabackup-2-0-0-ga/).
This is analogous to the binlog info.

Probably you mainly plan to use your backup in case of disaster, when
you would have lost all data from all nodes in the cluster. In this
case you don't need the galera-info. (It is only useful if you'd want
to manually provision a node from a backup.)

henrik

> --

[Ilias Bertsimas]

Your assumption is correct. I do not need the --galera-info either. If I need to setup a new node I take a snapshot from another live one or let it do it automatically with a full SST.

Ilias.