Solved

MySQL database setup - DataWarehouse

Posted on 2008-10-15
12
397 Views
Last Modified: 2013-12-27
Hi,

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.
File0.pdf
File1.pdf
File2.pdf
0
Comment
Question by:ixarissysadmin
  • 5
  • 3
12 Comments
 
LVL 61

Expert Comment

by:gheist
ID: 22757719
Given you have "JBoss" written in picture let me recommend using C-JDBC(http://c-jdbc.objectweb.org/) 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.
0
 
LVL 61

Expert Comment

by:gheist
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...
0
 
LVL 61

Expert Comment

by:gheist
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.
0
 
LVL 6

Expert Comment

by:IncisiveOne
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.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 61

Expert Comment

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

Accepted Solution

by:
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.
0
 
LVL 61

Assisted Solution

by:gheist
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 ;)
0
 
LVL 6

Expert Comment

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

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

943 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now