patternsqlMinor
Is it a good idea to split MySQL DB into two servers
Viewed 0 times
ideaserversintomysqlsplittwogood
Problem
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:
The relation b/w servers would be Master-Master.
If my reasoning is correct, can you shed some light on how I might accurately do this?
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?
Solution
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
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
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
Replication is a little more robust and at least 1 Slave is More Closely Sync'd to the 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
- Convert all tables to MyISAM
- 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.
Context
StackExchange Database Administrators Q#3855, answer score: 3
Revisions (0)
No revisions yet.