Analytics

MariaDB SkySQL includes MariaDB Platform for Analytics (ColumnStore) on GCP.

This service is designed for OLAP, DSS, and cloud data warehousing workloads.

Component View

MariaDB Platform for Analytics (ColumnStore)

In this service:

  • MariaDB Enterprise Server provides a modern SQL RDBMS with high availability, pluggable storage engines, hot online backups, and audit logging.

  • MariaDB Enterprise ColumnStore provides a columnar storage engine for ES that delivers real-time analytics at scale to perform interactive, ad hoc queries on hundreds of billions of rows with standard SQL.

  • MariaDB MaxScale provides load balancing, HA, and automatic failover.

Service Details

MariaDB Platform for Analytics on SkySQL delivers the following features:

Topology

ColumnStore

Storage Engine

ColumnStore

Number of ES Instances

1 or 3

Number of MaxScale Instances

1 to 2

Columnar Storage

Yes

Object Storage

Yes (infinite growth)

Statistical Functions

Yes

Production-Ready

Yes

Development/UAT-Ready

Yes

For additional information on ES instance options, see "Analytics Instance Sizes".

For additional information on MaxScale instance options, see "MaxScale Redundancy".

With this topology, enforce_storage_engine is set so this service only supports the Enterprise ColumnStore storage engine.

Want both transactions and analytics? MariaDB Enterprise ColumnStore is also available in a hybrid transactional/analytical processing (HTAP) configuration through MariaDB Platform for Smart Transactions (HTAP).

For additional information about MariaDB Enterprise ColumnStore on-premises and in the cloud, including use cases and features, see MariaDB Platform for Analytics: Modern Analytics Made Easy.

Storage

MariaDB Platform for Analytics configures Enterprise ColumnStore to use object storage to store its data in a highly available manner on GCP.

  • "Unlimited" on-demand capacity without downtime for upgrades

  • Highly available on resilient cloud infrastructure

  • Cost effective

Additionally, MariaDB Platform for Analytics configures Enterprise ColumnStore to use reliable shared storage in the cloud to store its metadata in a highly available manner on GCP.

Efficient Data Loading

MariaDB Platform for Analytics on SkySQL leverages the data loading architecture of MariaDB Enterprise ColumnStore.

MariaDB Enterprise ColumnStore supports very efficient bulk data loads.

MariaDB Platform for Analytics can also benefit from the features of cpimport, a bulk loading tool which bypasses the SQL layer to decrease the overhead of bulk data loading. cpimport efficiently performs the bulk data load by appending the new rows to the table in the order in which the rows are read from the source file. This approach reduces the I/O requirements, so that larger data sets can be loaded very efficiently.

MariaDB Platform for Analytics configures Enterprise ColumnStore to automatically translate LOAD DATA INFILE statements into bulk data loads. It translates the statement into a bulk data load that uses cpimport.bin, which is an internal wrapper around the cpimport tool.

Enterprise ColumnStore only requires a write metadata lock on the table to perform a bulk data load, so that the bulk data load does not block read-only queries.

OLAP Workloads

MariaDB Enterprise ColumnStore enables MariaDB Platform for Analytics on SkySQL to perform analytical or online analytical processing (OLAP) workloads.

OLAP workloads are generally characterized by ad hoc queries on very large data sets. Some other typical characteristics are:

  • Each query typically reads a subset of columns in the table

  • Most activity typically consists of read-only queries that perform aggregations, window functions, and various calculations

  • Analytical applications typically require only a few concurrent queries

  • Analytical applications typically require scalability of large, complex queries

  • Analytical applications typically require the ability to efficiently bulk load new data

Example Workloads

OLAP workloads are typically required for:

  • Business intelligence (BI)

  • Health informatics

  • Historical data mining

Columnar Benefits

Row-based storage engines face a disadvantage with OLAP workloads. Indexes are not usually very useful for OLAP workloads, because the large size of the data set and the ad hoc nature of the queries preclude the use of indexes to optimize queries.

Columnar storage engines are much better suited for OLAP workloads. MariaDB Enterprise ColumnStore is a columnar storage engine that is designed for OLAP workloads:

  • If a query reads a subset of columns in the table, Enterprise ColumnStore is able to reduce I/O by reading those columns and ignoring all others, because each column is stored separately

  • If most activity consists of read-only queries that perform aggregations, window functions, and various calculations, Enterprise ColumnStore is able to efficiently execute those queries using extent elimination, distributed query execution, and massively parallel processing (MPP) techniques

  • If only a few concurrent queries are required, Enterprise ColumnStore is able to maximize parallelism for each query by allocating more resources to each one

  • If scalability of large, complex queries is required, Enterprise ColumnStore is able to achieve horizontal and vertical scalability using distributed query execution and massively parallel processing (MPP) techniques

  • If the ability to efficiently bulk load new data is required, Enterprise ColumnStore is able to bulk load new data without affecting existing data using automatic partitioning with the extent map

High Availability and Failover

MariaDB Platform for Analytics on SkySQL leverages the high availability features of MariaDB Enterprise ColumnStore and MariaDB MaxScale.

MariaDB Enterprise ColumnStore is designed to provide highly available production analytics with automatic failover.

MariaDB Platform for Analytics uses MaxScale to achieve high availability and automatic failover. MaxScale's MariaDB Monitor (mariadbmon) monitors the Enterprise ColumnStore nodes. When the monitor notices the primary node fail, it promotes a replica node to become the new primary node, and it re-configures all replica nodes to replicate from the new primary node. The MaxScale Redundancy option enables high availability load balancing.

Architecture

MariaDB Enterprise ColumnStore uses a sophisticated storage architecture designed to provide a columnar storage engine for production analytics with high availability, fault tolerance, compression, and automatic partitioning.

MariaDB Platform for Analytics configures Enterprise ColumnStore to use MariaDB Replication to synchronize various database objects on multiple nodes for high availability:

  • Replication synchronizes the schemas for all ColumnStore tables on all nodes.

  • Replication synchronizes the schemas and data for all non-ColumnStore tables (such as system tables) on all nodes.

  • Replication synchronizes all other databases objects (stored procedures, stored functions, user accounts, and other objects) on all nodes.

Additionally, if scalability of large, complex queries is required, Enterprise ColumnStore is able to achieve horizontal and vertical scalability using distributed query execution and massively parallel processing (MPP) techniques.

For additional information, see "MariaDB Enterprise ColumnStore" in MariaDB Enterprise Documentation.