Solved

MySQL database setup - DataWarehouse

Posted on 2008-10-15
12
393 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
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…
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.

705 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

11 Experts available now in Live!

Get 1:1 Help Now