How to scale out mySQL with many Database Server (say 20 database servers)
To scale from 1 MySQL Database server to 4-5 server is very clear from the documentation by MySQL official developer website: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-scaleout.html
What about scale out from 4 servers to 20 servers? do we just add it as salves as well? Meaning 19 slaves with only 1 master? That means the insert speed will be the same regardless of how many DB server we put in.
Is there a better way to scale out for MySQL, where by, the more server we put in, the faster the read speed and write speed. We see it is a need because this is a system for heavy transaction company (A Trading website)
Oh yah, avoid SAN storage if possible. If SAN is required, might as well migrate MySQL to Cassandra.
Solution 1:
There are several solutions.
To get god insert performance with minimal impact on your code, have a look at mysql clusters. These go well beyond replication and transparently implement sharding. I believe (but would need to dig about to verify) that a mysql cluster can act as a master in master/slave replication. So, eg. you might have a 4 node cluster handling writes replicating to a dozen or so slaves.
Note that you can implement master/master replication - you can effectively have any number of nodes arranged in a ring - which will also give you the benefit of insert performance - but with such a large number of nodes, there is increased risk of delays in propagation of updates.
If you've got a complex schema, then you might get big benefits by using the federated storage engine to split the data, although mysql does not always optimize queries as much as it could in this scenario.
You should definitely be looking at mysqlproxy or some other abstraction layer if you're going down most of these routes.
Solution 2:
Check out mysql-mmm or ndb clustering if you're dealing with that many nodes, however be aware that if you do use MySQL Cluster (ndb) then you will need to alter your code accordingly.
MySQL-MMM can be found at http://mysql-mmm.org/ and the ndb stuff is part of MySQL Cluster Server from mysql.com
Solution 3:
You may want to look into a Distribution Master setup.
This would involve creatiing a Slave (called the Distribution Master) which has three(3) characteristics:
- log-bin Enabled
- log-slave-updates Enabled
- Every database (except information_schema and mysql) has BLACKHOLE tables only
What good would that do ?
Picture this scenario
- 26 MySQL Instances
- ServerA is Write Master
- ServerB is Distribution Master
- ServerC ... ServerZ are Read Slaves of ServerB
Here is what happens when an INSERT executes in ServerA
- ServerA records Entry for the INSERT to its Current Binary Log
- ServerB's I/O Thread imports INSERT from ServerA's Binary Log
- ServerB's I/O Thread records INSERT in its Relay Logs
- ServerB's SQL Thread reads INSERT from its Relay Logs
- ServerB processes the SQL
- ServerB records Entry for the INSERT to its Current Binary Log
- ServerB serves the INSERT from its Binary Log to the Relay Log of ServerC ... ServerZ
This provides the following benefits
- ServerA (Write Master) does not get bogged down performing Replication tasks
- ServerB (Distribution Master) stores no data locally. It only provides a conduit for passing binary log entries to all reads slaves. Thus, no heavy write I/O.
This has been tried by others. In fact, I answered a question for someone in the DBA StackExchange and StackOVerflow. It is a viable option for someone willing to do the leg work but have a decent spread of read I/O across two or more slaves.
If you are concerned about High Availability, no problem. You have two options:
OPTION 1
Redo the setup as follows
- 26 MySQL Instances
- ServerA is Active Write Master
- ServerB is Passive Write Master
- ServerC is Distribution Master
- ServerD ... ServerZ are Read Slaves of ServerC
- ServerA and ServerB are Circular Replication pair
- Backups for Data can be Done in ServerB
OPTION 2 : Use MySQL and DRBD
Introduce Disk-Level Redundancy via DRBD and ucarp
- 26 MySQL Instances
- ServerA is DRBD Primary with MySQL Running as Write Master
- ServerB is DRBD Secondary with MySQL Down
- ServerB provides disk level replica of ServerA's data volumne
- Run ucarp for DB VIP pointing to DRBD Primary
- ServerC is Distribution Master whose Master is the DRBD Primary
- ServerD ... ServerZ are Read Slaves of ServerC