2

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.

Improve this question
1
  • 1
    You should take a look at "sharding" (data partioning) and shared nothing arquitecture.
    – Ochoto
    Aug 9, 2011 at 10:19

3 Answers 3

Reset to default
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.

Improve this answer
1

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

Improve this answer
1
  • Might want to read this before you start down the MMM route: xaprb.com/blog/2011/05/04/whats-wrong-with-mmm ...and in the comments, the author of MMM (Alexey Kovyrin) wrote: "I’m the original author of MMM and I completely agree with you. Every time I try to add HA to my clusters I remember MMM and want to stab myself because I simply could not trust my data to the tool and there is nothing else available on the market to do the job reliably."
    – Aaron Brown
    Oct 12, 2011 at 14:28
1

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

  1. ServerA (Write Master) does not get bogged down performing Replication tasks
  2. 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
Improve this answer
1
  • 1
    Thanks for the such a detail explanation. However, i still cant see with 26 SQL server, how is the write speed is going to be better since there is only 1 write master?
    – Reusable
    Aug 10, 2011 at 1:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .