Data Warehouse: Staging Area, DWH databases and ETL tool on one server? 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 &amp; 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 &amp; 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!
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.