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
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.