Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


MySQL over Internet or Master-Master duplicate?
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.

MySQL over Internet or Master-Master duplicate?

dnwkdnwk Member

I have setup my site on two different server and try to do load-balance and/or fail over between. So the question is database. Shall I put my MySQL on a central server and ask each site to query MySQL over Internet? Or have MySQL on both site and do a Master-Master duplicate?

Comments

  • publiopublio Member

    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?

  • dnwkdnwk Member

    Things like Google Cloud SQL or Amazon RDS, they all query over Internet, right?

  • nerouxneroux Member

    @dnwk said:
    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?

  • ricardoricardo Member
    edited May 2014

    MySQL over the internet will be too slow

    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.

  • amhoabamhoab Member

    dnwk said: Things like Google Cloud SQL or Amazon RDS, they all query over Internet, right?

    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.

  • geekalotgeekalot Member
    edited May 2014

    @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:

    • You will have multiple copies of your data
    • The servers will be able to operate independently WHEN replication breaks (it does, for various reasons), or the link between servers go down
    • No having to manually sync/re-create any activity that occurred on your "Slave" (failover approach) after you've repaired replication or network downtime
    • You will be able to do loadbalancing + failover + high availability if you have the infrastructure to support that (and therefore potentially split your traffic and potentially reduce the load on each server). Obviously, the more geographically diverse servers, the better.



      What you may have to worry about though is session cookies + shopping carts, etc if you do it via DNS, for example
  • dnwkdnwk Member

    I have some problem with replication. They are too easy to break.

  • @dnwk said:
    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.

  • leapswitchleapswitch Patron Provider, Veteran

    Take a look at Galera.

  • jmginerjmginer Member, Patron Provider
  • raindog308raindog308 Administrator, Veteran

    dnwk said: Things like Google Cloud SQL or Amazon RDS, they all query over Internet, right?

    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.

Sign In or Register to comment.