Need advises for a new MySQL architecture

Question

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?

Answer 1

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).

Answer 2

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.