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
Querying MySQL over the internet will be too slow since sometimes multiple queries are used to respond to a single request.
I'm unfamiliar with MySQL's locking mechanism for two masters. But in any multi-master database, lock contention means decreased performance. This will probably be even greater when done over the internet.
How about a Master/Slave to use as failover?
Things like Google Cloud SQL or Amazon RDS, they all query over Internet, right?
They have very particular setups. Also replication over the Internet is usually asynchronous, providing better performance but also a slightly less reliable storage mechanism.
I assume the intention is to keep query speeds consistent between both your main point of connection as well as your failover utilizing a master-master duplication. If you use a master-slave, you'll still have to go back to the master to get data, which might decrease performance on your main machine. Are you able to setup a type of nginx proxy from the main page to serve everything off of the one site to others?
Actually it ain't that bad, though you'd want a small roundtrip between the servers. Worth noting that data sent over the wire can be compressed. I've 4 OVH boxes that communicate over the wire like that (too cheap to get a LAN setup for them). That setup is for batches of read/writes though, not serving lots of small packets of data i.e. wordpress setup.
You can probably tweak mysql settings to delay when communication over the wire happens to optimise the setup better for it.
It's expected that you at least have a read slave in the same region where your application exists. If there is more than a few ms of latency between your application and database and have more than just a few queries per page, you will feel it. Imagine a 20ms latency with 100 queries per page load (which isn't too crazy nowadays). You do the math.
@dnwk, As long as your servers are capable enough to handle:
Your web/application traffic + your local query/insert/etc operations + your replication insert/delete/etc operations
Then Master-Master replication all the way, because:
What you may have to worry about though is session cookies + shopping carts, etc if you do it via DNS, for example
I have some problem with replication. They are too easy to break.
@dnwk, Master-Master replication is not bad with only 2 servers. Once you get above about 5 or 6, it gets a bit crazier.
Take a look at Galera.
http://mysql-mmm.org/
Yes, but
(a) I'd wager most people using Amazon RDS are doing it from an Amazon VM.
(b) It depends on what your query is doing. If you're doing nightly batch processing or presenting some analytics data, the DB processing may take a lot longer than the network in which case it doesn't matter. For the front page of a high-traffic web site...probably not the best choice.