5 ms·

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 set

by druiid 13y 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 13y 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.
- druiid 13y 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 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.
  - daemon13 13y ago
    >> 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.
jdjb 13y 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?
- druiid 13y 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.
  - jdjb 13y ago
    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..
- jmngomes 13y ago
  Did you consider Gizzard for sharding writes? Can you share why not?
DougBarth 13y 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?

by druiid 13y ago

x3c 13y 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.

druiid 13y 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 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.
- daemon13 13y ago
  >> 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.

jdjb 13y 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?

druiid 13y 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.
- jdjb 13y ago
  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..
jmngomes 13y ago
Did you consider Gizzard for sharding writes? Can you share why not?

DougBarth 13y 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?