Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Hi, this is a great question, I love the the effort you put into writing it. The only problem though is with all three of those sub-questions together, this is a very broad topic. Could you split each of those points into their own questions to get better focused answers? I'd recommend linking to the other 2 questions in the comments to give context, though. –  Derek Downey Jul 25 '12 at 16:29
    
Good point. I'll do that in a little bit. Thanks for the input! –  tnosaj Jul 26 '12 at 6:59

2 Answers 2

up vote 8 down vote accepted

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 "name=whateveroption,value=whatevervalue,method=immediate"
  • 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.

share|improve this answer
    
The great money aspect of RDS is definitely a consideration. I have also been looking into XtraDB and have a call setup with percona: it does look like a very viable and powerful option. Thanks! –  tnosaj Jul 26 '12 at 6:58
    
If you decide to go with XtraDB Cluster, please let me know. I'm trying to get my employer to consider hosting XtraDB Cluster Setups. –  RolandoMySQLDBA Jul 26 '12 at 16:25

Well @tnosaj, regarding your first question about Read Slaves- Normally a master can replicate to 5-6 slaves seamlessly. However, evaluating whether read slave is working properly it is important to know the type of applications you have. For example, the number of “Likes” in a social application doesn’t have to be updated in real-time as oppose to an e-commerce site, where if an item has sold out, then it must be updated in immediately. So this type of read slave would be suitable in the former case but not so in the latter.

Regarding your second question, about multiple datacenters and replication- Best practice is to implement master-master replication within the same datacenter, and ideally it should be done on dedicated cluster hosts. Running master-master replication on multiple datacenters is not a common practice. The reason for this is that the biggest challenge master-master replication (similar to all clusters) can present is a situation referred to as “split brain”. In such a case, each master is disconnected, and then after restart begins to work independently. Repairing this type of “split brain” issue usually requires downtime and manual processing. (Check out this Wiki page on split brain).

As for a DB vendor to go with, in addition to Percona XtraDB Cluster and RDS that where mentioned by @Rolando, I recommend that you also check out Xeround’s offering (I think it’s presents a fair alternative to RDS).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.