5

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?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Cyprian
  • 165
  • 1
  • 6

2 Answers2

3

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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
1

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.

Derek Downey
  • 23,568
  • 11
  • 79
  • 104