We compare a) a single database node with b) a multi-node setup consisting of a master node (actual data only), one replica node of the master for running OLAP transactions, and a cold node for historical data. Both setups have an equal total amount of cores and main memory. The usage of the replica node and aggregate caches can be switched on and off. The workload consists of three types of transactions (ratio configurable): invoice postings (sFIN-adapted), read-only transactions incl. OLTP queries (incl. BKPF-BSEG-joins), and OLAP transactions incl. read-heavy analytical queries.
With the partitioning into actual and historical and replication of the actual data, we see the following improvements (90% actual-only OLAP transactions, 100% actual-only OLTP transactions, one of 100 queries being analytical):
Transactional processing is improved even without the use of a replica due to the smaller data set. Activating the replica, the multi-node setup is faster by a factor of ~4 for mixed workloads.
The higher the skew is towards an actual-only workload, the more the new architecture outperforms the traditional setup.
- When adding analytical users to the system, a replica of the actual master node significantly lowers the latency of OLTP transactions due to better load distribution.
Historical data can be purged and better compressed to decrease the memory footprint and require less main memory.
Overall system costs decrease as smaller servers can be deployed, hence avoiding disproportional prices for large server systems.