MySQL rapid Data Replication from multiple Databases

Question

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?

Answer 1

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.

Answer 2

I would also say it depends on the volume and what you want to do. Does your graph need to use real time data ? and does it use all data from these 6 databases ? In some situation a classic ETL (extract,transform,load) to a new database supporting heavy queries is the answer

If you need quasi-real time data: Replicating heterogeneous databases is not easy, you could use products such as Oracle DataGuard to achieve this, but it is complex to set up.

Another option is to use slaves: Oracle database replicated to a stand by database (with Active dataguard). You can then run heavy queries on the standby (which also can serve as HA, backup...)- I believe SQL server Mirror does the same thing

Another option is perhaps to test your application (first with low activity) and monitor the effect (perhaps tune the database), and hopefully you dont need anything that complex!