Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to deploy a MySQL-based application with the following requirements and would love to receive some guidance (any) as to how I could set things up:

  1. Scale of deployment

    5 servers with a latency of 200-400ms between them.

  2. Database content/structure

    Running InnoDB. 1GB of data split across 50 tables which are categorized as follows:

    -user tables: about 200MB across 10 tables which store information on users (e.g. user, user_favorites, user_votes...).
    -content tables: about 800MB across 40 tables which store information about everything else (these tables don't have any relationships with the user tables).

  3. Content creation/access

    -user tables: each server should have READ-WRITE access to these tables. The frequency of writes depends on users' activity on the website (e.g. people creating account, updating it, voting and/or putting items in favorites...).

    -content tables: each server should have READ ONLY access to these tables which would be updated once a week when the webmaster updates the website's content (and on some occasions maybe on a daily basis to perform urgent content updates).

And here is the setup I've come up with so far:

  • each server would have its own local copy of the database for READ-ONLY access.
  • WRITE queries for user tables would be forwarded to 1 MASTER server using MySQL splitting techniques.
  • WRITE queries for content tables would be performed directly on the MASTER server.
  • The MASTER server would be replicated to the other servers operating as SLAVES.

And here are my questions:

Q1: Do you see anything fundamentally wrong with my setup / can you recommend something better?

Q2: What tool would you use to split READ and WRITE queries?

Q3: WRITE queries for user tables are going to be delayed because of the latency of writing to the MASTER: is there anything that can be done to mitigate that?

share|improve this question
add comment

2 Answers

1GB -- not very big.

Latency -- good enough to get to the other side of the earth. How wide is your network? For example the US is less than 100ms wide.

Multiple read-only slaves, etc -- sure.

"each server should have READ-WRITE access to these tables" -- What do you mean? Perhaps you meant client?

"MySQL splitting techniques" -- Be careful! If you write to the Master, then read from the Slave, expecting it to be there, well, it might not be there yet! This is the "critical read" problem. There are numerous kludgy solutions. We can discuss further.

I much prefer the application decide what query (or set of queries) need to go to the Master and connect directly (without any proxy). That puts the onus on you to get it right, and not to let yourself be lulled into assuming that the splitting will work. Because when it does not work (because of a slowdown in replication), the bug will be difficult to track down.

A mitigation technique... Create Stored Procedure(s) on the Master for critical read cases and multi-write cases. That may limit the latency to one round-trip to the Master. We can discuss further.

share|improve this answer
    
Thanks for your answer Rick. By "each server should have READ-WRITE access to these tables" I mean that the application can execute UPDATE, INSERT, and SELECT queries. In the case of content tables, the application only needs to execute SELECT queries. Thanks for the warnings with splitting techniques. I am thinking of using PHP MySQLnd_ms (php.net/manual/en/book.mysqlnd-ms.php) which allows me to force read queries to the master if needed. –  user359650 Nov 20 '12 at 8:29
    
I think I am going to go for a partial replication setup: the user tables won't be replicated (which means each website will have its own set of users) whereas the content tables will. Are there any issues to bear in mind while doing partial MySQL replication? –  user359650 Nov 21 '12 at 15:54
add comment

This sounds like a good use case for Galera. http://codership.com/content/using-galera-cluster

You could also use something like this: Each server is a master for it's copy of the user table (I assume the user table is sharded?). Then there could be a central server with MariaDB or MySQL + Tungsten Replicator which is a slave of all the other servers.

And then you could create another master (extra instance on one of the servers?) which hosts the master copy of the content tables.

CM = Content Master U = User Master UC = User Combined

Server 1: CM + UC (2 Instances) Server 2-5: U

For the content tables (1 master, 4 slaves): CM -> U1 -> U2 -> U3 -> U4

For the user tables (4 masters, 1 fan-in slave): U1 -> UC U2 -> U3 -> U4 ->

Galera and MySQL Cluster provide synchronous cluster where you can write to any server. MySQL Cluster needs 2 data nodes and 2 SQL nodes as a minimal setup (the mgmt node can be combined with a SQL node). MySQL Cluster does not use InnoDB or MyISAM. Galera needs 3 nodes and does support InnoDB.

Investigate Galera, Tungsten (and MySQL Cluster aka NDB?).

Read MySQL High Availability (O'Reilly).

share|improve this answer
    
Thanks for clusters terrify me (I've heard and read feedback from sysadmins on clustering issues that I definitely don't want to run into). I think I'm going to go with a basic replicated setup for now. If my website is successful and I make a lot of money I'll hire guys like you to setup clustering :) –  user359650 Nov 21 '12 at 15:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.