New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Comments
Digital Ocean's guide on master-master replication is pretty good.
If you don't want to mess with replication, you could set up a single DB server that all of the web servers use. It lets you scale out the web servers horizontally and you can go pretty far with that configuration.
I have done this on iwstack 3 Instances of maridb master-master replication and 1 Instance for cluster controller and deployed with severalnines.com.
depends on what application you have too. not done it personally, but you can split the tables onto 2 or more servers. means you have to code for different DB connections though
useful info
http://www.tocker.ca/2013/10/24/improving-the-performance-of-large-tables-in-mysql.html
@Bruce Thanks for that - is my vision of a large multi node distributed replicated database a pipe dream? I'm sure it's hard and fraught with difficulties, but surely some people are doing it? Or maybe they just throw resources at one giant monolithic db server?
@SoftGeeks bizarrely enough I'm on IWstack myself and have outgrown their largest 16gb node. Severalnines looks really interesting - thanks.
is my vision of a large multi node distributed replicated database a pipe dream? I'm sure it's hard and fraught with difficulties, but surely some people are doing it?
Guess what happens when your query needs to "join" stuff from different machines... You want to do sharding but also modify your code & data model accordingly.
Why not use two MySQL servers (master & slave) and put them in a roundrobin array?
What would happen if a write query hit the slave?
Presuming it's something that doesn't require write. Or write can be used on a diff file eg admin area
Master-slave replication + load balancers is used when you need to scale your reads. You have to adapt your code accordingly. That said, I don't think that's what the OP is asking (scaling reads only).
You need to decide whether you are scaling reads or writes (as @deadbeef states). If its just reads then master + slaves will help.
What database are you using?
I've been playing around with Cassandra, it's worth checking out.
Interesting article just to have some superficial knowledge for guys who don't deal with NoSQL (me)
http://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis
setup mysql cluster, create data nodes for the heavy lifting and turn the webservers into an "api node" these are interfaces for the data nodes and behave like regular mysql servers.
Here's a starting point: https://cyrenity.wordpress.com/2010/08/12/howto-setup-mysql-cluster-beginners-tutorial/ We were testing these technologies over geographic locations but obviously the closer the server is the better as for example a select query will get parts of the data from each of the data nodes. (we got around this by using multi-master circular replication i.e clustering clusters).
The mySQL Cluster will run in memory as well so that will be a performance gain straight away if you are not already using in memory databases.
Load-balancer/DNS failover + Multi-master mysql replication + ample RAM can go a long way (assuming your apps can work in this configuration)
You'll need to stay on top of replication issues via monitoring scripts, etc
Dependent on various factors (traffic, latency, bandwidth, CPU, disk throughput, etc) you can probably do <= 10 geographically distributed nodes with satisfactory results.
YMMV
Thanks all - some great responses there. I have some reading to do.
Do several servers, one for writes and 1+ slaves for read in a master slave setup. This will allow you to scale your DB servers a lot if you're not in a write heavy environment.
If you use functions for your reads/writes it's easy to change in the code.
check out: http://galeracluster.com/products/ or http://www.percona.com/software/percona-xtradb-cluster
both based on the Galera methodology
@ditlev - that looks really interesting. Thanks.
The reading I'm doing on master-master setups suggests that they don't scale well. Anyone got any experience with master-master or master-slave setups in prduction environments? Don't worry - I won't bug you for setup tips, I'm just interested in anecdotal experiences of how well both setups scale.
@MarkTurner Sorry - missed your question. It's MariaDB supporting a Magento store. As far as I can tell, Magento only supports Master-Slave natively, so master node/writes will be the weak point, but writes should be much rarer than reads.
Use SeveralNine's tool for quick fast deployment:
http://www.severalnines.com/clusterconfigurator
I haven't used it for a few years, but it was good for a fast solution
Also have a look at LiteMage:
http://www.litespeedtech.com/products/litemage-cache/overview
I always thought that a scalable mysql setup (ideally served as a service) is the holy grail. We've tried to buy Xeround and GenieDB in the past, but it either didn't fit, or didn't work - so we're still looking.
Everyone is talking about NOsql, but 97% of all front facing DB's are currently MySQL. While the tech is old, it's the tech being used - and someone needs to solve this problem.
D
@ditlev
Google CAP theorem and keep your hopes low
Why load balance infact. Just scale up