Scaling Percona datacenters: setup and replication Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you.

We have already scaled alot of our application stack: we offloaded our high read/write tables for temporary information to a separate Percona server where the tables are running with "Engine=MEMORY", as well as migrated other sections to a cassandra cluster.

Now we are left with a "lean" database where we have a read/write load of 88%/12%. At this point i have a few questions i would like to get some feedback on:

1. Read Slaves

With our read/write setup, a number of (e.g. 2-3) read-slaves should reduce the read load to a minimum on our write master. How scaleable is a read-slave solution: if we double/tripple our load is adding additional read slaves going to continue to offer enough capacity for reads? I read up on this post: What are the limits on the amount of slaves per master? however, not coming out of a scaleablility background this this may seem foolish, but is this a freezable solution? There are alot of people pushing sharding rather than a read-slave solution, however, i really dont see a need at the moment with our read/write load to rewrite large sections of our application... any thoughts?

2. Multiple datacenters and replication

Furthermore, we are looking at serving different geographic locations with datacenters near by to reduce network lag (we deal with mobile applications which dont really like lag). The plan would be to use the much mentioned semisync. replication (see: Is it a good idea to split MySQL DB into two servers and Is MySQL Replication Affected by a High-Latency Interconnect? ) for a master-master replication, where each datacenter has a single master, and multiple read slaves. Again, in my naivety, i am very interested in knowing if this would be within the bounds of "best practices" when scaling.

3. Hardware and Config

I have been busy the past few weeks benchmarking our live system, and i have come to the conclusion that, regardless of the solution we choose for Point 1 and 2, the server we are currently using will not make it for very much longer, could i get some thoughts on our setup:

CPU: Intel(R) Xeon(R) CPU E31275 @ 3.40GHz mit 8 cores (hyperthreading)
RAM: 16GB
Raid 10 with a strip size of 64 KB and controller cache enabled
Software: Percona 5.5
Database size: 83.7GB
Top 5 Tables:
 21302MB  table1
 7656MB  table2
 5477MB  table3
 4352MB  table4
 3663MB  table5


my.cnf settings:

 max_heap_table_size=64M
 tmp_table_size=64M
 default_storage_engine = InnoDB
 innodb_buffer_pool_size = 10G
 innodb_file_per_table   = 1
 innodb_old_blocks_time=1000
 innodb_buffer_pool_instances=10
 innodb_log_file_size=256M
 innodb_flush_method=O_DIRECT
 innodb_read_io_threads=10
 innodb_write_io_threads=10
 join_buffer_size = 67108864 #64M
 expand_fast_index_creation=ON


Will moving to a Percona XtraDB Cluster solution solve some of our issues, e.g. replication stability?

I know that these are alot of questions which are very theoretical, and i appreciate anyone who takes the time to read and comment on my thoughts. As a small startup in Europe we really dont have the venture capital to just "go to the cloud", and we prefer to have more control ourselves. While we are looking into consultants, etc. I thought that stackexchange was the right place to bounce some ideas off.
In this instance, you actually have two choices
CHOICE #1 : Percona XtraDB Cluster
I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but WITH GREAT POWER, THEIR MUST ALSO ALWAYS BE GREAT RESPONSIBILITY (19:16 - 19:25 of the Video).
You ultimately become responsible for

sizing memory requirements and disk configuration for InnoDB
remembering that DDL/DML on MyISAM is not replicated in the Galera Write Set Replicator Libraries. Since GRANT commands is storage-engine neutral, MyISAM table in the mysql schema is handled with no problem. Any DML against mysql.user is not replicated.
adding provisioning new Cluster Nodes for Reads/Writes

CHOICE #2 : Amazon RDS
Amazon RDS makes MySQL Database Cloud Services a snap. You must spend some time deploying Servers with one of 7 server models. By default, all InnoDB log files are 128M. Here are the only options that are unique to each Server Model:
MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

You are not given SUPER privilege and there is no direct access to my.cnf. In light of this, in order to change my.cnf options for startup, you must first create a MySQL-based DB Parameter Option List and use the RDS CLI (Command Line Interface) to change the desired  Options. Then, you must do this to import the new options:

Create a Custom DB Parameter Group (call it MySettings)
Download RDS CLI and setup a config file with your AWS Credentials
Execute the following : ./rds-modify-db-parameter-group MySettings --parameters &quot;name=whateveroption,value=whatevervalue,method=immediate&quot;
Modify using DB Parameter Option List MySettings
Restart the MySQL RDS Instance

As for scaling out to data centers, you have the option to create read replicas. Since the default storage engine is InnoDB, making a read replica becomes seamless because data can be sync'd to Slaves without interrupting the Master.
Higher Server Models means you can have more Memory, more IOPs. Don't forget the cliche I mentioned because when it comes to Amazon RDS, with GREAT POWER COMES GREAT MONEY.