Percona XtraDB Cluster: Setting up a simple cluster | Hacker News Reader

Percona XtraDB Cluster: Setting up a simple cluster

mysqlperformanceblog.com

19 pointstdieds17 comments

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).

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.

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.

>> I believe e-mail is in profile if you have more questions, but this should cover the big stuff.

It is not. `email` field is not publicly visible, you need to add it to `about` text field.

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?

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.

Thanks for the reply.

We have a high-write application and while individual writes aren't really that slow we have A LOT of them (we receive our writes off the network from a 20+ hadoop-machine cluster so they can send quite a bit of junk our way).

What's wrong with the complexity of scaling Cluster? From what I've experienced it's been pretty easy to add machines, take individual data nodes offline while cluster is running, etc..

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

Well while this question doesn't really apply to me.. I can at least say one reason not to use Gizzard is that it's deprecated now.

Didn't know it had been deprecated, it seemed like a great tool for scaling databases. Anyone know why?

We've used master/master shards in the past and they've been very difficult to manage. I'm not fully aware of Gizzard's capabilities so I guess the answer is no, because we weren't aware of it.

We also needed something that would speed up writes much more than just 2x (and the way ndbcluster does writes was the key).

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?

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

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.

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

Your wish, is my command: https://github.com/justicel/selinux.git

thanks!

Reply on news.ycombinator.com