'Storage engine optimization for datawarehousing

We've been deploying a midsize datawarehouse database with daily updates, a few fact tables, many dimensions and even more ondemand reports programmed in a custom build php framework. We've optimized indexes to performance optimal levels.

Now we wonder if moving selectively to different storage engines for fact and dimension tables would help. Most if the tables are in InnoDB, some log tables in CSV. Would it be beneficial to move dimensions to Aria? Fact tables are fairly sized, yet not larger than 200 billion records in size, dimensions smaller than 1000 records. We are happy with performance now but the (fact)data is growing daily.

Any general thoughts?

mariadbdata-warehouse


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions

Does Primary key in Dimension table have to be part of the Primary key in the Fact table?

Data warehouse design for parking lot - date and time dimensions

ECLAT Algorithm to find maximal and closed frequent sets

Distinct aggregation in pre-calculated measure (MDX)

Redshift design or configuration issue? - My Redshift datawarehouse seems much slower than my mysql database

A simple left join query taking lot of time for output

Data warehouse, could a dimention be a fact?

SCD Start and End Date

ClickHouse TTL settings with WHERE condition

SCD2 on nested table in GCP big query

Pentaho spoon search and replace especial character in rows

Pentaho spoon search and replace especial character in rows

MariaDB - Cannot connect to MariaDB instance from Dbeaver using correct password

Debian 10 MariaDB 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'

Get full tree of parent/child relationships in mysql of any node in the tree with all parents