Smart Transactions (HTAP)

Overview

MariaDB SkySQL includes MariaDB Platform for Smart Transactions (HTAP) on GCP.

MariaDB Platform for Smart Transactions (HTAP) optimizes MariaDB Enterprise Server for a combined transactional and analytical workload by using both the MariaDB Enterprise ColumnStore and InnoDB storage engines, and by using HTAP Replication to replicate between them.

Smart transactions are known by many names – augmented transactions, Translytical, Hybrid Transactional-Analytical Processing or HTAP, Hybrid Operational-Analytical Processing or HOAP. Whatever they are called, smart transactions are standard transactions enriched by analytics to provide a better customer experience.

Providing that kind of enriched customer experience, requires both transactional and analytical processing working seamlessly together. By combining row and columnar formats as well as block and object storage, MariaDB Platform for Smart Transactions (HTAP) is the first database built to perform high-performance transactions and low-cost analytics in the cloud – together.

Component View

MariaDB Platform for Smart Transactions (HTAP)

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.

  • InnoDB provides a row-based storage engine for ES that delivers excellent performance for transactional or online transactional processing (OLTP) workloads.

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

Service Details

MariaDB Platform for Smart Transactions (HTAP) on SkySQL delivers the following features:

Topology

HTAP

Storage Engine

InnoDB (Transactions)
ColumnStore (Analytics)

Number of Instances

1

Replication

HTAP Replication

Columnar Storage

Yes (Analytics)

Object Storage

Yes (Analytics)

Production-Ready

Yes

Development/UAT-Ready

Yes

For additional information on instance options, see "Smart Transactions Instance Sizes".

The Bulk Data Loading API is not supported. The maximum size of a single data import is 8 GB. For assistance with loading data, create a support case.

Workload Examples

  • Flagging a fraudulent credit card transaction that is outside the norm for a specific account

  • Using a predictive needs analysis on a checking account to alert customers and help them avoid overdraft fees

  • Using historical data to predict the likelihood of a particular flight being on time and provide that information in real time to the customer who is booking a flight.

Hybrid Workloads

The combination of MariaDB Enterprise Server, MariaDB Enterprise ColumnStore, and the InnoDB storage engine enables MariaDB Platform for Smart Transactions on SkySQL to function as a single-stack solution for hybrid or Hybrid transactional-analytical processing (HTAP) workloads.

Hybrid workloads are characterized by a mix of transactional and analytical queries. Hybrid workloads are also known as "Smart Transactions", "Augmented Transactions" "Translytical", or "Hybrid Operational-Analytical Processing (HOAP)".

Workload Examples

Hybrid workloads are typically required for applications that require real-time analytics that lead to immediate action:

  • Financial institutions may use transactional queries to handle financial transactions and analytical queries to analyze the transactions for business intelligence

  • Insurance companies may use transactional queries to accept/process claims and analytical queries to analyze those claims for business opportunities or risks

  • Health provides may use transactional queries to track electronic health records (EHR) and analytical queries to analyze the EHRs to discover health trends or prevent adverse drug interactions

Component Usage

  • For analytical queries, the Enterprise ColumnStore storage engine can be used.

  • For transactional queries, row-based storage engines, such as InnoDB, can be used.

  • For queries that reference both analytical and transactional data, ES's cross-engine join functionality can be used to join Enterprise ColumnStore tables with InnoDB tables.

Batch Cache

Columnar databases are not optimized for the types of high-frequency single-row inserts commonly performed on row-based transactional databases. Where the HTAP topology needs to account for both transactional and analytical workloads, the MariaDB Enterprise ColumnStore storage engine has been extended to include a batch cache to speed up INSERT statement processing. The batch cache is an always-on background process and requires no configuration.

Best Practices

  • MariaDB Enterprise ColumnStore has been enhanced with a batch cache to speed up INSERT statement processing.

  • Performance of UPDATE and DELETE will vary based on table size, the number of columns and rows. As is typical for analytical use cases, DELETE should generally not be used.

  • As the Enterprise ColumnStore and InnoDB storage engines may support different data types, care should be taken when replicating data.