0

I am thinking about a DWH server layout and I'd like to ask you if you can spot any downsides to this layout. The basic idea is to have Staging Area & Data Warehouse DBs and the ETL Tool for the transformation from stg to dwh running on one single machine because of reduced hardware cost and less administration effort:

  • sda: 512 GB SDD -> OS (Centos 7), Applications, Swap space, MySQL Database application (but no data), csv files from data sources, ETL tool (Pentaho)
  • sdb: 960 GB SDD -> MySQL tablespace for staging area tables
  • sdc: 960 GB SDD -> MySQL tablespace for data warehouse tables
  • sdd: 4 TB HDD -> backup & archive disk
  • Memory: 160 GB
  • Prozessors: 2x Intel Xeon E5-2620v3 - 12 cores

Tasks:

  • During stg loading: LOAD DATA INFILE from csv to stg
  • During transformation: Pentaho would SELECT from stg and INSERT into dwh in parallel for multiple tables. I thought having stg and dwh on separate disks will avoid read / write bottlenecks
  • During working hours: Reporting tool or Analysts do SELECT on dwh

From your point of view or experience is there any reason why I should not use this layout and instead split stg and dwh on two different servers like I did in the past? (e.g. MySQL performance might suffer from parallel SELECT and INSERT, etc.)

Thanks for your feedback!

closed as primarily opinion-based by mustaccio, Erik Darling, Vérace, hot2use, Marco Jan 8 '18 at 12:39

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

0

Your "all your eggs in one basket" scenario seems ripe for unintended consequences. I try to decouple processes so that something that runs amok doesn't propagate a negative effect to other parts of your overall DW architecture. Using separate disks is definitely good practice and even splitting , bear in mind that memory and CPU constraints are just as important points of contention.

Here's a scenario that I've seen in designs like yours ----

A sql plan changes (due to data quantity or quality changes) and you come back in the next morning and realize the ETL's still going and the additional CPU and memory draw is affecting query performance. This puts you in the unsavory situation wherein you're wondering whether it's far enough along to finish the load or you should terminate it and do a catch up load later (which may have even more data) to let users get at their data.

Decoupling some of your architecture gives you the freedom to get your staged data a second look to review issues, run validation checks, and most importantly give you the flexibility of troubleshooting during the work day without worrying about sharing resources.

In any case, if you do decide to run via the single box route (which I'd highly recommend against), consider:

  • Making sure the right tables use the right engine for the appropriate requirement
  • CPU limiting certain processes so that they don't starve the server
  • Ensuring alerts and notifications are properly set up to allow you to keep track of errors, completions, and long running processes
  • Test, test, and.... TEST. test with different data sizes. test with a variety of data. and test by simulating issues like memory pressure, etc.

It should be self-evident that what I've said is a conjunction of a few best practice recommendations and my personal experience. You should account for your business requirements and its capacity for risk. i.e.: In some industries, having a data warehouse loaded with the previous day's data is a highly critical requirement. In others, just having access to the data warehouse is the most critical requirement.

  • Thank you for this comprehensive answer and for sharing you knowledge and experience! – Cpt_Jauchefuerst Jan 7 '18 at 20:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.