We want to build a data warehouse to distribute valid information across the organization (reporting) and get some insights into our data (analytics). After some research we found MariaDB using Column Store an attractive option. However, we are all non-professionals in the field of data warehousing. That’s why I am would like to hear some opinions on the applicability and hardware required.
The data from the source systems (Oracle, IBM DB2, MariaDBs) sum up to 1 TB (including PK/FK and indexes) containing 36 months of data history. The data consist of tables with mostly integer keys, a mix of integers/doubles (65%), mostly shorter VARCAHRs (30%) and 5% of GEO-data. The data comes from 15-20 different topics with different weights. Meaning some make 50GB some 5GB in the data warehouse. Not all topics (tables) can and will be joined, because there are simply no common keys. For the reporting case, the different topics will mostly be queried isolated (max. five topics will be joined). To query all time points (months) will be used seldomly. I think it is reasonable to assume up to 17 months of data will be quered for product performance comparison. New data will be written overnight in batch jobs. The MariaDB database server is planned to run on a 8 phy. core machine with 1,25 TB of SSD storage and 64GB of RAM. In total, we expect about 50 people querying the data, 45 of which use aggregated reporting tables and 5 are heavy analytics users – joining tables, grouping, exporting stuff to other ML tools. Complex string searches are not in our scope for the data warehouse. We do not expect the 45 people to query the data at the same time, but up to 20 concurrent users can be possible. We do not want basic reporting queries to take 10 seconds or longer. More complex queries for the 5 analytical users can take longer.
Some considerations we had and I would be happy to get your opinions on:
- Do commercial vendors offer dramatically better solutions than MariaDB column store, which we should consider for our case? Or is it possible to do it with an open source RDBMS?
- Does it make sense to have one stronger machine, or 2-3 weaker ones balancing the load a little better? Would they then need a full copy of all data? (so 1,25 TB SSDs x 3 Workers?).
- Does it make sense to not have one big SSD, but five (256 GB each) smaller ones to increase IO? (Saving topics of similar tables on the same SSD, because they will be joined more frequently.)
- Does it make sense to have 64GB or RAM, or is more needed? What are the determinants of RAM needed?
As you can see, our lack of experience causes some fundamental questions of the hardware required. Is it even possible with the information provided to draw conclusions on the possible performance? If not, what kind of information is needed?