Ways to auto scale MySQL servers?

I run a site which has high traffic surges and because of that auto scaling solutions is very profitable for this case. Currently the web server is able to horizontally auto scale but the bottleneck is on the MySQL server.

• I have tried with Amazon RDS Multi-AZ but it takes like 15 minutes for the 12 GB database to upgrade with some minutes of downtime. It has helped a lot when I already knew that a traffic surge was going to happen in some specific moment.
• I have also considered Xeround. This is probably the best solution although it is quite expensive for databases of this size. Anyway it is not an option because I legally need the database to be in the European Union.
• I have read about Scalr but not sure if that could be helpful and how.
• I have seen that many cloud hosting providers offer vertical scaling solutions which I think it has 0 downtime (not sure if that is really possible, as far as I know they use Xen hypervisor). That could be a solution but I wonder if it has not downtime and how the MySQL config (and many other things on the OS) are able to upgrade also without downtime.
• I have tried with MySQL slave servers but it was not helpful at all.
• I am using memcache which helps a lot but it is not enough. I need to upgrade because of writes, not just because of reads.

Any suggestions? Thank you in advance

Solution 1:

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.

Solution 2:

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.