Ways to auto scale MySQL servers?

Actually, a simpler solution would be to try adding Memcached to your stack to save on DB load. This can drastically save load, and is much simpler than trying to solve the problem of standing up servers quickly (low difficulty), and then figuring a rapid MySQL sync (much higher difficulty.)

http://toblender.com/?s=memcached

To solve the problem of too many writes, the most common fix is adding memory to the server (s a larger working set can be kept in RAM), putting your DB on faster disks (SSDs are a good solution, but expensive), or sharding (which is expensive in additional servers and complexity).

Another way to reduce DB write load would be incorporating a an in-memory data store (such as Redis) to handle frequently-changing data, and if needed periodically write changes back to your main DB.

You should consider using a Star Topology

Here is what I am proposing

• One Write Master (aka WM)
• One Distribution Master (aka DM)
• Five(5) Read Slave Server (aka RSS)

Prepare the Topology Like This

Step 01 : Setup 5 RSS with these common options

[mysqld]
skip-innodb
key_buffer_size=1G

This will cause all tables to be created loaded as MyISAM Storage Engine

Step 02 : Setup DM and all RS Servers

• mysqldump the schema of all tables from WM to a schemadump file
• load the schemadump file into DM and all 5 RSS
• run ALTER TABLE tblname ROW_FORMAT=Fixed; on all tables in RSS
• run ALTER TABLE tblname ENGINE=BLACKHOLE; on all tables in DM
• mysqldump data only (using --no-create-info) to a datadump
• load datadump in all 5 RSS

Step 03 : Setup Replication From DM to all 5 RSS

Step 04 : Setup Replication From WM to DM

END OF SETUP

Here is how your Read/Write mechanism works

• All your writes (INSERTs, UPDATEs, DELETEs) occur at the WM
• SQL is recorded in the binary logs of the DM (No actual data resides in DM)
• Each RSS is a Read Slave to the DM
• All your reads occur at the RSS

Now here is the catch...

• You use RSS 1-4 for reads initially
• Use the 5th RSS to spin up other RSS
  • You run service mysql stop at the 5th RSS
  • Spin Up another RSS
  • Copy /var/lib/mysql and /etc/my.cnf of 5th RSS to the newly spun-up RSS
  • You run service mysql stop at the 5th RSS
  • You run service mysql stop at the new RSS

You can use RSS #5 to spin up new servers over and over again

On a sidenote, please do not use XEROUND for the WM or DM because they do not support the InnoDB or BLACKHOLE storage engine.

I hope these ideas help.

If you use Innodb, you should consider Mysql multi-masters managed by Galera. It makes setting up mysql multi-master easier, and should make it easier for you to "semi" auto-scale.

If this is a application that you or you company is writing, you can consider moving to a sharded(partitioned) design for the application. But sharding can get complicated. Here is a link to get you started.

I am assuming you have "tuned" the mysql config files, as in allocated appropriate memory etc.

Is this in a rack that you control, or is it in the cloud? 12GB is a very small database relative to the size of disks that are available. Put it on a RAID1 or RAID10 array of small SLC SSDs and your write latency will disappear.

The Intel 311 series 20GB SLC SSD ($120 each) would do the job brilliantly.

If the database were larger, you could achieve similarly spectacular results by moving your database to an iSCSI target on a ZFS SAN server (built on commodity server hardware using Nexenta, OpenIndiana, FreeNAS, or whatever) and setting up a mirror of similar SSDs for your ZIL write cache. In all but the most extraordinary circumstances, Gigabit Ethernet is more than adequate to move database iSCSI traffic.