8

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

Zillo
  • 111
  • 2
  • 4
  • seems like you are stuck with the same problem like me :( You could consider multi-master replication or try to use a different database solution for tables with heavy writes. I am currently evaluating voltdb, i am considering to partially move tables to voltdb instead of mysql. – Niko S P Feb 19 '12 at 22:36
  • Could you add more details around "I have tried with MySQL slave servers but it was not helpful at all". In what way did you change your architecture, what were you expecting to happen that didn't? – nickgrim Feb 20 '12 at 14:39
  • 1
    The software we are using is already designed to use MySQL slave servers if you want it but despite of that it did not help at all. I think that memcache do almost all of the MySQL slave work (most of the reads). I think that MySQL slave was more a problem than something positive but anyway it depends on each case, probably on some cases is useful while in others is not. – Zillo Feb 20 '12 at 20:23

4 Answers4

4

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.

gWaldo
  • 11,887
  • 8
  • 41
  • 68
  • could you give an example of how memcached would help to reduce write load on mysql servers? – Niko S P Feb 19 '12 at 22:31
  • 1
    Apologies; I didn't see that part. – gWaldo Feb 19 '12 at 22:33
  • Updated answer to address write-latency concerns. – gWaldo Feb 19 '12 at 22:40
  • Your SSD suggestion is on the money, and SSDs are not necessarily expensive for such a small database. Even with a larger database, ZFS makes it possible to cache all writes directly to SLC flash. – Skyhawk Mar 22 '12 at 08:10
  • You're right; SSD's for a 12GB DB isn't expensive at all. I was thinking more of the general-case than the OP's specific parameters. – gWaldo Mar 22 '12 at 14:47
3

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.

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
1

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.

Not Now
  • 3,532
  • 17
  • 18
1

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.

Skyhawk
  • 14,149
  • 3
  • 52
  • 95