In this instance, you actually have two choices
CHOICE #1 : Percona XtraDB Cluster
I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but WITH GREAT POWER, THEIR MUST ALSO ALWAYS BE GREAT RESPONSIBILITY (19:16 - 19:25 of the Video).
You ultimately become responsible for
- sizing memory requirements and disk configuration for InnoDB
- remembering that DDL/DML on MyISAM is not replicated in the Galera Write Set Replicator Libraries. Since GRANT commands is storage-engine neutral, MyISAM table in the mysql schema is handled with no problem. Any DML against
mysql.user
is not replicated.
- adding provisioning new Cluster Nodes for Reads/Writes
CHOICE #2 : Amazon RDS
Amazon RDS makes MySQL Database Cloud Services a snap. You must spend some time deploying Servers with one of 7 server models. By default, all InnoDB log files are 128M. Here are the only options that are unique to each Server Model:
MODEL max_connections innodb_buffer_pool_size
--------- --------------- -----------------------
t1.micro 34 326107136 ( 311M)
m1-small 125 1179648000 ( 1125M, 1.097G)
m1-large 623 5882511360 ( 5610M, 5.479G)
m1-xlarge 1263 11922309120 (11370M, 11.103G)
m2-xlarge 1441 13605273600 (12975M, 12.671G)
m2-2xlarge 2900 27367833600 (26100M, 25.488G)
m2-4xlarge 5816 54892953600 (52350M, 51.123G)
You are not given SUPER privilege and there is no direct access to my.cnf. In light of this, in order to change my.cnf options for startup, you must first create a MySQL-based DB Parameter Option List and use the RDS CLI (Command Line Interface) to change the desired Options. Then, you must do this to import the new options:
- Create a Custom DB Parameter Group (call it
MySettings
)
- Download RDS CLI and setup a config file with your AWS Credentials
- Execute the following :
./rds-modify-db-parameter-group MySettings --parameters "name=whateveroption,value=whatevervalue,method=immediate"
- Modify using DB Parameter Option List
MySettings
- Restart the MySQL RDS Instance
As for scaling out to data centers, you have the option to create read replicas. Since the default storage engine is InnoDB, making a read replica becomes seamless because data can be sync'd to Slaves without interrupting the Master.
Higher Server Models means you can have more Memory, more IOPs. Don't forget the cliche I mentioned because when it comes to Amazon RDS, with GREAT POWER COMES GREAT MONEY.
Keep in mind most of the work I do is with PostgreSQL so this may or may not be 100% on the money but I think this should be close enough to be helpful.
The basic issue is that in an environment like this you are going to have a great deal of trouble managing the locks. It seems you can either do some sort of conflict resolution or conflict prevention on the lock level. Conflict prevention seems like despite the performance difficulties, it would reduce the level of user frustration significantly.
My approach here would be indeed to do the locking on the central server in a pl/sql stored procedure, which would insert into the locking table if possible, returning a value indicating success, or if that is not possible, either returning a value indicating failure, or raising an exception (what I have done in the past, for example, is to return something identifying who has the lock if it is already locked).
What I would omit is actually checking with the local server. If you have a high read to write ratio and the chance of collisions is otherwise relatively small, you are going to have to check the central server most times anyway, so there isn't much to be gained by checking the local server also. Certainly you wouldn't want to write to both the local and remote servers for locking. Keep locking simple. It is likely to be a source of pain no matter what you do.
The second thing I would suggest here is that I highly recommend expiring locks like this, perhaps after 2 hours or something. There are two major reasons for doing so. The first is that bugs in the application-layer of the code can cause locks not to be released, and secondly if this is over a web interface, HTTP is stateless and therefore you have no real way of knowing that state has dropped. In this way you can give a lock which is valid for a certain period of time, can be renewed pre-emptively (in the background if needed), and times out if the individual closes the browser window and goes home for the day. Some sort of administrative utility to free locks is also something I would recommend.
I share your sense that 7 sec to acquire a lock is a significant performance cost, but in the end I don't see any better way to do this. Your options are limited significantly by the CAP theorem and a single central locking system is probably what's needed.
I suppose another option is that one could have the central server merely lock to branch location, and have the branch location release the lock as soon as no valid locks have been held for a certain period of time. This might have the advantage of allowing faster collaboration by a team, meaning that only the first editor on a team would have to incur that cost.
Best Answer
Every write is performed on every machine, whether it is a Master or a Slave. The corollary to that is that there is a limit to write scaling with traditional Replication. Even Galera and Group Replication are just variants on Master(s) and Slave(s). Each server has all the data, and it is kept as up-to-date as is practical.
Hence, Sharding is the only way to get write scaling.
OK, let me try to answer your question anyway.
With RBR (Row-Based-Replication), the Slave can usually do a write (
INSERT
/UPDATE
/DELETE
/etc) with less effort than the Master.That implies that having multiple Masters will spread the "Master effort" out somewhat. Hence, having more Masters helps in scaling, and moving more reads off to Slaves help, too.
Some configurations, and my comments:
M <-> M "Dual Master" -- If you are writing to both (as implied above), then it is a somewhat fragile system.
M1 -> M2 -> M3 -> M1 "Circular Masters" -- very fragile. If one server goes down, it is a nightmare to repair.
3+ Masters, each talking to each other "Galera Cluster" -- This solves the above fragilities, but suffers if there is large latency between Masters. (latency to get geographic separation to get HA.) This is about the best config for some write scaling today.
"Group Replication" -- a competitor of Galera when it comes to HA. I'm not sure about write scaling.
In all of the above, Slave(s) can hang off any or all Masters to provide read scaling.
For write scaling (and not HA), I would go with 3 Galera nodes in the same server room. If there is enough reads to worry about, then tack some readonly Slaves on.
Note: Going past 3 nodes in Galera (or maybe it is 5), you risk some degraded performance. This is due to the fact that each node must talk to each other node during each
COMMIT
.