MySQL database setup - DataWarehouse

Posted on 2008-10-15
Last Modified: 2013-12-27

I currently have a simple setup where an application server connects to a database server (both used for live system). The latter replicates on to a data warehouse server (as seen in File0.pdf). The issue is that we have three databases running on three separate MySQL instances on the live server which makes me replicate the biggest db on the datawarehouse instance and dump and import all the other databases daily. Given that this approach is quite crude and time consuming, I am redisigning the setup (always keeping my datawarehouse necessities in mind) in order to remove the daily dump / load in order to get the up to date data.

One of my concerns is that I want to be able to disconnect any live db (for restart or whatever) without impacting the other two live databases.

I am attaching File0.pdf which is the current setup, File1.pdf and File2.pdf which are the two options being considered.

Which setup would you go for?

PS 1 There is already a disaster recovery site being replicated using mysql's replication and in hot standby for shift in case of emergency.
Question by:ixarissysadmin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 62

Expert Comment

ID: 22757719
Given you have "JBoss" written in picture let me recommend using C-JDBC( driver and bringing down only one database or server in cluster for unloading. C-JDBC handles situation, database becomes updated after restarting and nobody even notices.

Your solutions IMO contain complicated database peering, which somehow undermines diagnosing simple and easy problems.
LVL 62

Expert Comment

ID: 22757761
I wonder if C-JDBC is same as Sequoia in your PDF... It does not need server, all features are in JDBC driver...
LVL 62

Expert Comment

ID: 22757795
I am running C-JDBC in production environment with MySQL. I assure there is no specific MySQL setup done, and for some short emergency it even used XP as one db server while production Linux-es were replaced with Solaris-es. If you have questions regarding C-JDBC i'm happy to answre them.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 22805914
Maybe it is time for the company to consider an enterprise db server that has none of these "problems", limitations or complications.  Three databases Plus the data warehouse in one single server instance; no replication except for the DR.  With the current config, you are replicating yourself silly.

If your databases are normalised, there is no technical reason for replicating between each of them or between them and the DW.
LVL 62

Expert Comment

ID: 22807743
Why the hell enterprise DB server???
There are replication etc features of MySQL still unused, no other software will fix that.

Accepted Solution

IncisiveOne earned 250 total points
ID: 22809105
Maybe you are reading too much into my statement.  This is not a marketing exercise.  Take a look at the diags, there is a level of complication, duplication and replication that is completely unnecessary.  When you get something free, you tend to overuse it; put up more rather than less instances; that grows on you, so you replicate; then you want private copies, so you replicate more ... end result is the diagram.  Certainly it is deployment, but it is not architecture.  Three instances, actually four (not counting DR) and replicating the hell out of themselves.  Running into problems as a result of ordinary growth is no surprise.

Now when you have to pay for something, you treat it with a bit more respect, and replicate the least possible. It kind of forces you to think in architectural terms, how do I support the actual load and dbs on the least no of boxes and licences.

Perhaps I should have said: redraw the diagram using architectural and engineering principles.  Paper is cheap.  Have it vetted out here with the experts.  Then implement the solution using MySQL or whatever.  

And set aside this talk of instances here and instances there.  The database is defined as the central corporate data repository.  There should be only one version of the truth, and it lies in one place.  Of course a testing copy and a DR site are necessary, but no other copies.  Remember copies need to be kept up to date, which means replication, which means more grunt and more boxes; splitting up databases to overcome replication load issues.  Mushroom farm.

Finally, it is more than likely that the three (four) databases have not been normalised.  Fixing that problem will yield the biggest performance benefits (you will need less boxes to do the same job; less data being replicated).  Your symptomatic problem disappears.
LVL 62

Assisted Solution

gheist earned 250 total points
ID: 22810487
Given schemes in first post it koojs like our asker is in need of obvious diagrams since his superiors will not take Mr Ellisons or Mr Gates word that data are replicated. Thus straightforward and obvious solution that gives no interruptions, but keeps DR-related titual intact ;)

Expert Comment

ID: 23026569
Since the seeker did not ask further questions and indicate direction, I suggest an even split between gheist and me.

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create a MySQL table as easily as possible 6 71
clicking to find my folders on wamp 8 42
Delete image(s) associated with record(s) 16 45
php hashing methods 3 45
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question