Solved

MySQL database setup - DataWarehouse

Posted on 2008-10-15
12
408 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
[X]
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
12 Comments
 
LVL 62

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 62

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 62

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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
 
LVL 62

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 62

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

690 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