Percona XtraDB Cluster: Setting up a simple cluster

http://www.mysqlperformanceblog.com/2013/09/23/percona-xtradb-cluster-setting-simple-cluster/

by tdieds • 13 years ago

19 17 13 years ago

druiid6• 13 years ago

Just an FYI. We use XtraDB Cluster in a relatively high-transaction, high-performance configuration, so feel free to ask me anything about it!

It's a great setup (although you have to work with it, just like most sql clustering setups out there, you can't just throw anything on it and hope it sticks).

x3c• 13 years ago

How are you handling backups? LVM snapshots? Incremental/differential backups? What is the disaster recovery procedure? What monitoring tools do you recommend? Is clustering as advanced and complicated as it seems? Should one explore traditional slave-master read/write segregation, sharding, partitioning, archiving etc. and exhaust those options before going for clustering or is it better to just jump to clustering?

I'm a mysql newbie but am very fascinated with scaling relational databases. It's just seems so challenging and exciting.

druiid6• 13 years ago

Well some of those questions fall outside of what you're really going to be directly doing with XtraDB. I'll answer the questions that would be more directly related to the story though.

For backups we use another Percona tool: http://www.percona.com/software/percona-xtrabackup It's free and works great.

Disaster recovery is standard stuff. Backups/hot backups to external and recover as necessary (generally we try to keep that to never).

Monitoring is outside of the scope of XtraDB, but take a look at MONYog.

Now clustering is more XtraDB related. The clustering is nearly dead simple. If you use the (technically deprecated) wsrep_urls configuration option you can spin up a cluster from scratch without needing to worry about master location. Not sure WHY they deprecated this, I guess some people had issues with it, but it has worked fine since the beginning for us.

You don't need a master/slave relationship unless your writes are really sky-high, or same with reads. Basically make sure you have a good number of wsrep_slave_threads defined (double CPU cores seems to work well for starters).

Sharding, partitioning and archiving are kind of outside the direct scope of XtraDB. You'd be better served looking up documentation online for more info than I'd be able to provide in a short post here. Insofar as it ties in to XtraDB though, I think there's nothing wrong with jumping straight from a single DB node to this solution. You just really have to have the additional CPU/memory resources to add more nodes (you need at least three to keep the cluster alive if one dies... two will get you split brain if one dies). I believe e-mail is in profile if you have more questions, but this should cover the big stuff.

jdjb3• 13 years ago

How high is your write load? We considered using it recently but used ndbcluster instead since XtraDB Cluster doesn't actually shard writes (iirc it only returns a write as "complete" after enough nodes report the write as being finished so it's actually slower than writing to a single master). Is there a way to use it to linearly partition writes?

druiid6• 13 years ago

Essentially, no. This is perhaps the one drawback with current Galera implementation. We run about 32 slave threads per node though and it has never been a real problem for us. It WILL be a problem for you though if you have 32 of those filled with write requests that take 20-30 seconds to complete. You'll end up with waiting sessions. I'd say that's not really an issue for Galera to directly resolve though. If this is a highly transactional web application you really don't want writes taking more than a few seconds in the majority of cases.

It's an entirely different scenario for a data-warehouse type situation obviously, and having 30+ second writes with enough threads won't be an issue then either. Basically though, this is one area where I'd like to see Galera change in one way and it's kind of the 'one' valid criticism to have for the platform. If your workload falls within the good use-cases for it though, I'd recommend it well before NDB/MySQL Cluster due to if nothing else, the sheer complexity of scaling Cluster.

jmngomes2• 13 years ago

Did you consider Gizzard for sharding writes? Can you share why not?

DougBarth• 13 years ago

Nice. We'll be doing the same shortly.

How many nodes did you deploy? Are they within a single DC or cross WAN? Do your clients write to all nodes, or just one? Basically, how much can you share about your topology?

taf23• 13 years ago

Disabling iptables - which is unfortunate that's the part that IMO can be tricky, maybe someone here has set things up with iptables?

druiid6• 13 years ago

Iptables is easy. You just need the following ports open: 3306, 4567, 4444, 4568.

I can say that getting it working with selinux is a pain. I have a TE file available if someone needs to get it working which I can upload somewhere.

taf23• 13 years ago

That is easy - why is it disabled in the howto I wonder... And yeah, would be awesome if you shared your selinux te files

Percona XtraDB Cluster: Setting up a simple cluster

Related Stories