Sql-server – MySQL rapid Data Replication from multiple Databases

MySQLoracleperformancereplicationsql server

I am a Freelance Web Developer but not a Database Administrator at all except basic Database Operations like DML etc. Today I got a client asking me to develop a Web based application but there is a Problems concerning Databases and I don't know what strategy would best suit.

Problem Explanation

My Client got 6 different MS SQL and Oracle databases and all these databases are frequently updated with 3 to 4 seconds each updated with 6 different applications. Now I have to develop a web based single application to show data from all these 6 different databases.

Question

My Problem is that what strategy should I choose for my web based application specially when it's about performance

1) Replicate Database from all these 6 different databases into single MySQL database keeping in mind that these are updated every 3 to 4th second?

2) Or my application should directly access these 6 databases?

My Client says that my web application will be generating many different graphs which sometimes can take 30 seconds to generate single graph.

I would request my honor DBAs that please suggest me what would best suit for my application? My client doesn't want that his current 6 databases get slow down or unresponsive when application is accessing to generate graphs or something like that. Client is willing to let me make my choice to choose from above options. Web application should show the most recent data from database so if I choose to replicate then I will have to replicate every 3rd second.

Please kindly also tell that is it possible to tell database to replicate new data to other database every 3rd second and if yes then will it be resource intensive specially when 6 different database will do the same on single database?

Best Answer

Some might say depends how big the databases are; I think regardless of the size, accessing the databases directly instead of having a "local" mySQL database to query would be best, for the following reasons:

No need for disk/memory/maintenance of the mySQL database
If the updates to the various databases happen every few seconds, and the users may only use the web page every few minutes/hours/days, you are executing a lot of queries that are not needed most of the time, which in turn places additional load on the six databases. In other words, I think the client doesn't realize the impact of having a replication scheme. Even if just a few tables from each database is involved, the synchronization code running every few seconds would be much worse than occasional specific queries (occasional being something running every few minutes/hours).
The code to replicate data from 6 separate databases would be quite complex to do correctly.

Related Solutions

Mysql – What can we do in MySQL 5.0 Replication to address bandwidth concerns

SUGGESTION #1 : Use Distribution Masters

A Distribution Master is a mysql slave with log-bin enabled, log-slave-updates enabled and contains only tables with the BLACKHOLE Storage Engine. You can apply replicate-do-db to the Distribution Master and create binary logs at the Distribution Master that contains only the DB schema(s) you want binlogged. In this way you reduce the size of outgoing binlogs from the Distribution Master.

You can setup a Distribution Master as follows:

mysqldump your database(s) using --no-data option to generate a schema-only dump.
Load the schema-only dump to the Distribution Master.
Convert every table in the Distribution Master to the BLACKHOLE storage engine.
Setup replication to the Distribution Master from a master with real data.
Add replicate-do-db option(s) to /etc/my.cnf of the Distribution Master.

For steps 2 and 3 you could also edit the schema-only dump and replace ENGINE=MyISAM and ENGINE=InnoDB with ENGINE=BLACKHOLE and then load that edited schema-only dump into the Distribution Master.

In step 3 only, if you want to script the conversion of all MyISAM and InnoDB tables to BLACKHOLE in the Distribution Master, run the following query and output it to a text file:

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ENGINE=BLACKHOLE;') BlackholeConversion FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine <> 'BLACKHOLE'" > BlackholeMaker.sql

An added bonus to scripting the conversion of table to the BLACKHOLE storage engine is that MEMORY storage engine tables are converted as well. While MEMORY storage engine table do not take up disk space for data storage, it will take up memory. Converting MEMORY tables to BLACKHOLE will keep memory in the Distribution Master uncluttered.

As long as you do not send any DDL into the Distribution Master, you can transmit any DML (INSERT,UPDATE,DELETE) you so desire before letting clients replicate just the DB info they want.

I already wrote a post in another StackExchange site that discusses using a Distribution Master.

SUGGESTION #2 : Use Smaller Binary Logs and Relay Logs

If you set max_binlog_size to something ridiculously small, then binlogs can be collected and shipped out in smaller chunks. There is also a separate option to set the size of relay logs, max_relay_log_size. If max_relay_log_size = 0, it will default to whatever max_binlog_size is set to.

SUGGESTION #3 : Use Semisynchronous Replication (MySQL 5.5 only)

Setup your main database and multiple Distribution Masters as MySQL 5.5. Enable Semisynchronous Replication so that the main database can quickly ship binlogs to the Distribution Master. If ALL your slaves are Distribution Masters, you may not need Semisynchronous Replication or MySQL 5.5. If any of the slaves, other than Distribution Masters, have real data for reporting, high availability, passive standby or backup purposes, then go with MySQL 5.5 in conjunction with Semisynchronous Replication.

SUGGESTION #4 : Use Statement-Based Binary Logging NOT Row-Based

If an SQL statement updates multiple rows in a table, Statement-Based Binary Logging (SBBL) stores only the SQL statement. The same SQL statement using Row-Based Binary Logging (RBBL) will actual record the row change for each row. This makes it obvious that transmitting SQL statements will save space on binary logs doing SBBL over RBBL.

Another problem is using RBBL in conjunction with replicate-do-db where table name has the database prepended. This cannot be good for a slave, especially for a Distribution Master. Therefore, make sure all DML does not have a a database and a period in front of any table names.

Replicating only changes made by a specific database user

I agree with Phil⁺¹ that this is not readily possible. Furthermore, it is very unclear how this could even work. Imagine the following simple table is being replicated.

 C1   C2
  1    'Apple'
  2    'Orange'
  3    'Pear'

Scenario 1

 batchjobaccount : INSERT INTO T1 VALUES (4,'Peach');
 appserveraccount: UPDATE T1 SET C2='Grape' WHERE C1=4;

What should happen in the replication? Should the record be created so that the appserveraccount action can take place or should the update be ignored?

Scenario 2

 batchjobaccount : UPDATE T1 SET C1=5 WHERE C1=2;
 appserveraccount: DELETE FROM T1 WHERE C1=5;

What should happen in the replication? Should the record be deleted or not?

Scenario 3

 batchjobaccount : DELETE FROM T1 WHERE C1=3;
 appserveraccount: UPDATE T1 SET C2='Strawberry' WHERE C1=3;

What should happen? Should the update fail on the local database but succeed on the replicated database or should it not run on the remote database because it failed on the local database?

If you don't want changes by batchjobaccount to replicate, then why give them permission to make the changes on the local database? If they need a local playground, then can you just make a local copy of the tables and only give batchjobaccount access to those?

Best Answer

Related Solutions

Mysql – What can we do in MySQL 5.0 Replication to address bandwidth concerns

Replicating only changes made by a specific database user

Related Question