Data Lifecycle in MariaDB using ColumnStore

This presentation was given by Richard Bensley, MariaDB and MySQL consultation at Vettabase, at the company webinar that took place on April 24, 2024.

Transcript

1. Data Lifecycle in MariaDB Using ColumnStore
2. DC-1 OLTP Scalable Workloads DC-2 BKP Column Store Timeseries Metrics Monitoring Batch Scalable Analytics
3. DC-1 DC-2 BKP Column Store Timeseries Metrics Monitoring Batch OLTP Scalable Workloads MariaDB InnoDB Scalable Analytics ~2000 Connections 16 cores / 32 threads 64GB RAM 10GBe 512GB NVMe Transactional SLA and reads at milli/micro-second latency.
4. DC-1 DC-1 OLTP Scaling OLTP with OLAP DC-2 BKP DWH
5. DC-1 OLTP DC-2 BKP DWH OLTP OLTP Scaling OLTP with OLAP
6. DC-1 OLTP OLTP OLTP DWH MariaDB ColumnStore
7. MariaDB Data Warehouse/OLAP: ColumnStore Engine
8. MariaDB Data Warehouse/OLAP: ColumnStore Engine CS1 CS2 CS3 Proxy Storage CS1 CS2 CS3 Proxy Storage S3
9. MariaDB Load mvnw clean verify measurements.txt 1000000000 CSV File 00:05:14 314.141 Load LOAD DATA INFILE 1b_cs 1000000000 ColumnStore 00:14:03 843.37 Load LOAD DATA INFILE 1b_innodb 1000000000 InnoDB 01:21:07 4,866.91 Load INSERT SELECT 1b_cs_small 100000000 ColumnStore 00:01:48 108.339 Load INSERT SELECT 1b_innodb_small 100000000 InnoDB 00:08:00 480.146 1 Billion Row Challenge
10. MariaDB challenge calculate_average_baseline.sh measurements.txt 1000000000 CSV File 00:03:22 201.866 challenge Query 1b_cs 1000000000 ColumnStore 00:00:52 52.196 challenge Query 1b_innodb 1000000000 InnoDB 00:13:41 821.417 challenge Query 1b_cs_small 100000000 ColumnStore 00:00:05 5.322 challenge Query 1b_innodb_small 100000000 InnoDB 00:01:25 85.205 1 Billion Row Challenge
11. Data Warehouse/OLAP: ColumnStore Engine InnoDB Column Store SELECT … INTO OUTFILE SELECT … INTO OUTFILE file.csv Upload to S3 Backup/Archive MariaDB
12. MariaDB Any other options?
13. MariaDB Separation of Concerns: Connect Engine Inventory Sales INV.GOODS_IN INV.GOODS_IN CREATE DATABASE INV; USE INV; CREATE TABLE GOODS_IN ENGINE=CONNECT TABLE_TYPE=mysql CONNECTION=’mysql://user:pass@host/INV/GOODS_IN; SELECT SUM(units) FROM INV.GOODS_IN WHERE warehouse_id=123;
14. MariaDB Data Archiving: S3 Engine Inventory Sales INV.GOODS_IN CREATE TABLE GOODS_IN_ARCHIVE_2023 LIKE GOODS_IN; START TRANSACTION; INSERT INTO GOODS_IN_ARCHIVE_2023 SELECT * FROM GOODS_IN WHERE YEAR(goods_date) = 2023; DELETE FROM GOODS_IN WHERE YEAR(goods_date) = 2023; ALTER TABLE GOODS_IN_ARCHIVE_2023 ENGINE=S3 COMPRESSION_ALGORITHM=zlib; COMMIT INV.GOODS_IN_ARCHIVE_2023 (goods_date > 1 years) INV.GOODS_IN_ARCHIVE_2023 (goods_date > 1 years) INV.GOODS_IN
15. DC-1 InnoDB DC-2 BKP ColumnStore InnoDB InnoDB Scaling OLTP with OLAP
16. Proxy Scaling every • Maxscale • ProxySQL • HAProxy • Backend
17. Proxy Scaling OLTP REPLICA ColumnStore InnoDB INSERT INTO mysql_query_rules (...) VALUES (..., '^SELECT\s+SUM\(',...); INSERT INTO mysql_query_rules (...) VALUES (..., '^SELECT\s+AVG\(',...); Writes Reads Rules