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.

I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate:

I would like to split the MySQL DB server into two separate servers as follows:

  • Server A (Master) would receive the statistic data for computation.
  • Server B (Master) would keep
    • Aggregated (pre-calculated data) the Master on-demand
    • application
    • user data.

The relation b/w servers would be Master-Master.

  • Server A -> Server B : sending aggregated (calculated) data
  • Server B -> Server A : sending new application and user data so that aggregation will also be performed for newly tracked applications.

If my reasoning is correct, can you shed some light on how I might accurately do this?

share|improve this question
    
I like this question a lot because these are the types of questions that should be entertained in DBA.SE. +1 for this one !!! –  RolandoMySQLDBA Jul 14 '11 at 15:16
add comment

2 Answers

up vote 3 down vote accepted

OPTION #1 : Use Master/Master

You could use Master/Master only under one condition: If you write to a DB on ServerA, DO NOT ISSUE WRITES TO ServerB, and vice versa. In this way, you split writes cleanly. Splitting writes to the same DB in Master/Master can be a little clumsy if you depend on looking up rows by IDs that have the AUTO_INCREMENT property. If you look up rows by unique keys that never change from server to server, such as Social Security Number, Driver License, a HashKey and so forth, the splitting writes to the same DB between two Masters in Master/Master is fine.

OPTION #2 : Use Slave Servers

@DTest already described this, so I will add nothing additional to his suggestion (He get's a +1 for it).

OPTION #3 : Use MyISAM in the Slave Servers

When using read-only slaves that are not being used as a master for other servers, you should do two things to the data in that MySQL Instance

  1. Convert all tables to MyISAM
  2. Run ALTER TABLE tblname ROW_FORMAT=Fixed on all MyISAM tables

This should add 20% increase in speed for reads.

OPTION #4 : Use MySQL 5.5 for Semisynchronous Replication

Before MySQL 5.5, when a Master has Multiple Slaves, this is how an SQL statament is processed among the slaves (Example will be a Master with two slaves):

SQL Statement is

  • Executed in Master
  • Recorded in Master's Binary Logs
  • Passed to Slave1 Relay Logs
  • Executed in Master in Slave1
  • Passed to Slave2 Relay Logs
  • Executed in Master in Slave2

In MySQL 5.5, when a Master has Multiple Slaves, this is how an SQL statament is processed among the slaves (Example will be a Master with two slaves) using Semisynchronous Replication:

SQL Statement is

  • Executed in Master
  • Recorded in Master's Binary Logs
  • Passed to Slave1 Relay Logs
  • Acknowledged to Master of Receipt By Slave1
  • Passed to Slave2 Relay Logs
  • Acknowledged to Master of Receipt By Slave2

Replication is a little more robust and at least 1 Slave is More Closely Sync'd to the Master.

share|improve this answer
    
You are very very very helpful :-) Thank you for your answer. –  Cyprian Jul 14 '11 at 15:37
    
a lot of good info here! –  Derek Downey Jul 14 '11 at 16:20
add comment

Assuming I understand correctly, that server B actually holds all the basic info (precalculated data, application data, etc), I would set it up in a Master-Slave-Slave environment:

Master Server: This is the DB you write everything, tracking, statistics, application data, user data...you get the idea.

Slave Server 1: This can also be the server the main application reads from. This server is optional, and can actually run on the master, depending on the load of your application.

Slave Server 2: This would be a reporting server, set up as read-only. Basically any reports on the raw statistical data would be run here. This would keep the load of the reads off the normal server(s)

As for HOW to do the setup, the documentation would be a good place to get the bigger points of setting up replciation.

share|improve this answer
    
thank you for your answer. –  Cyprian Jul 14 '11 at 17:56
add comment

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.