---
title: "Technical Introduction to MemSQL"
description: "Whitepaper | 33 pages"
canonical: "https://memsql.relayto.com/e/technical-introduction-to-memsql-unxxyc42u2ey8"
last_updated: "2020-02-06"
---

# Technical Introduction to MemSQL

> Whitepaper | 33 pages

- Interactive version: https://memsql.relayto.com/e/technical-introduction-to-memsql-unxxyc42u2ey8
- Format: PDF document, 33 pages
- Published by: [memSQL](https://memsql.relayto.com/)
- Last updated: 2020-02-06
- More from this site: https://memsql.relayto.com/llms.txt

## Page 1

TECHNICAL WHITEPAPER
—
Introduc on to MemSQL

2019

## Page 2

I ntroduc on to MemSQL

Table of Contents
Introduc on 3
D ata Pla orm Landscape 3
H ow MemSQL Modernizes Data Pla orms 5
Core MemSQL Technical Concepts 7
Code Genera on and Compiled Plans 7
Lock-Free data structures 9
Mul -Version Concurrency Control 10
Disk-op mized columnstores and memory-op mized rowstore tables 11
Distributed Query Processing 13
Core Architecture 16
Key Components of a MemSQL Cluster 17
Database Par  ons and Sharding 18
Sharded and Reference tables 19
Parallel Data Ingest with Pipelines 20
Cluster Management 21
Dynamic Cluster resizing 22
Data Replica on 22
Feedback-driven Workload Manager 23
MemSQL Security 24
MemSQL Studio 25
Cloud-na ve Support and Managed Service 26
MemSQL Innova on History 27
Conclusion 28
Appendix: MemSQL Capability Checklist 29
2

## Page 3

I ntroduc on to MemSQL

Introduc on
This whitepaper introduces MemSQL as a modern data pla orm with the speed, scale
and cost eﬃciencies to generate business value and insights from opera onal data.
You’ll learn how MemSQL is designed and built to ingest data at high speeds, scale-out
eﬃciently and deliver record-breaking query performance with familiar rela onal SQL.

Data Pla orm Landscape
Historically, databases ﬁt into one of two categories: those op mized for online
transac onal processing (OLTP) and those op mized for online analy cal processing
(OLAP). Transac onal systems are tradi onally separate from Analy cal processing
systems, even though they could poten ally be combined into a single system.
Transac on systems are o en revenue genera ng, have strict availability requirements,
and are viewed as mission cri cal.
OLAP running on Data warehouse systems are used to analyze large amounts of data
and require a lot of compu ng resources. They are not generally as mission cri cal.
Combining data warehouse and transac on systems in a single database generally results
in the transac on workload suﬀering and thus aﬀec ng the business adversely. Hence,
separa on of the two has become standard.
A third type of data pla orm, called an opera onal data store (ODS), is used to support
opera onal analy cs. This data pla orm allows the business to have near real- me
visibility into rapidly changing events, such as orders and/or customer interac on.
Success of the opera onal data store is driven by the ability to handle streaming ingest
of data with a high number of concurrent analy cal queries.
3

## Page 4

I ntroduc on to MemSQL

An ODS receives transac ons from an OLTP system in a minimally intrusive manner
using techniques such as extract transform and load(ETL) or change data capture. It also
serves as a source for the data warehouse. Tradi onally, an ODS would serve as an
up-to-date replica of opera onal data for a variety of analy c requirements. However,
over the years, more data sources have driven a massive increase in data volume and
velocity crea ng a series of challenges for legacy ODS technologies to keep up.

Figure 1. Legacy mul - ered ODS architecture

4

## Page 5

I ntroduc on to MemSQL
How MemSQL Modernizes Data Pla orms
First released in 2011, MemSQL is a third genera on RDBMS wri en in C/C++.
MemSQL is designed to run eﬃciently on modern systems; both mul -core systems with
a big memory footprint and lower-powered edge compu ng devices.
MemSQL is ANSI SQL-Compa ble and na vely supports structured, semi-structured, and
unstructured (full-text search) data. With built-in connectors to Ka a, Spark, S3, and
Hadoop, as well as legacy transac onal systems, MemSQL easily integrates with a broad
ecosystem to cover both real- me streaming and batch workloads.

Figure 2. Modernizing legacy data pla orms with MemSQL
5

## Page 6

I ntroduc on to MemSQL

With support for JSON and Documents, MemSQL can ingest data from modern sources
such as mobile phones, social media, and smart devices and provide both transac onal
and analy cal capabili es on a single pla orm with the ease and familiarity of SQWith
MemSQL, all enterprise features, such as par  oning, security, high availability (HA), and
disaster recovery (DR), are included in the product and not licensed separately. As a
modern and eﬃcient pla orm, workloads o en run on less hardware when compared to
legacy systems. Thus cost savings are realized in reduced so ware spend, the reduc on
of data silos through decoupling transac ons from analy c systems, the cost of
deployment, and maintenance costs in comparison to legacy vendors.
MemSQL can run legacy transac onal workloads and serve as the ODS or data hub that
performs as an opera onal analy c backbone to power real- me decisions across
reports, interac ve dashboards, data science, and more.

6

## Page 7

I ntroduc on to MemSQL

Core MemSQL Technical Concepts
In this sec on we will go over some key technical concepts that underpin MemSQL’s
architecture. It’s these key technical diﬀerences that diﬀeren ate it from other solu ons
and make it a solu on of choice for customers.
Code Genera on and Compiled Plans
When a query is submi ed to a database, the query is interpreted, an execu on plan
generated and the query is executed. Op mizing this process is cri cal for performance.
MemSQL embeds an industrial compiler (LLVM) for low-level op miza ons along hot
code paths - op miza ons that are not possible when execu ng via interpreta on. This
approach also takes advantage of newer instruc on sets that are available with modern
cpus.
When a MemSQL server encounters a query shape for the ﬁrst  me, it generates a
just-in- me execu on plan, wri en in C++, which is incrementally compiled to machine
code as it processes the query. This has two-fold beneﬁts allowing op mal query
performance with ﬁrst run queries while oﬀering extreme fast response  me to repeat
queries. Each compiled plan is cached to prepare for future invoca ons of the given
query. When future queries match an exis ng parameterized query plan template,
MemSQL bypasses code genera on and executes the query immediately using the
cached plan.

7

## Page 8

I ntroduc on to MemSQL

Figure 3. Compiled plans in MemSQL u lizing and bypassing the code generator

A er code genera on, the compiled plans are saved for later use in a plan cache. Each
MemSQL node has its own plans and plan cache. A plan cache consists of two layers: the
in-memory plan cache and the on-disk plan cache. Plans stored in the in-memory plan
cache remain un l they expire, or un l the memSQL node restarts. When a plan expires,
it stays put in the on-disk plan cache, and is loaded back into memory the next  me the
query is executed. By interpre ng SQL statements and implemen ng compiled query
plans, MemSQL removes interpreta on overhead and minimizes code execu on paths.

Another key feature of MemSQL’s compiled plans is that they do not pre-specify values
for the parameters. Query parameters are dynamically extracted from a query template,
producing a normalized query that is then transformed into a specialized na ve
representa on (MemSQL Plan Language, or MPL). The generated execu on plan is
wri en in C++ and compiled to machine code. When queries that match the query
template are executed, MemSQL subs tutes the parameter values, allowing the request
to reuse already-compiled plans and run quickly. Addi onally, compiled plans are also
reused across server restarts, so they need to be only compiled once in an applica on’s
life me.
8

## Page 9

I ntroduc on to MemSQL

Lock-Free data structures
Tradi onal databases use locks (latches and enqueues) to manage serializa on and they
run into issues such as deadlocks or priority inversion, when processes block each other
as they complete execu on. This results in performance and scalability issues with
increasing volumes of concurrent read and write opera ons.
Lock-free data structures that enable be er scalability and performance are at the core
of MemSQL’s engine. Every component of the engine is built using lock-free data
structures including queues, stacks, hash tables, skip lists, and linked lists. For superior
memory management and eﬃciently managing transac on state, lock-free queues and
stacks are used throughout the system. In the area of code genera on, lock-free hash
tables are used to map query shapes to the compiled plans in the plan cache. MemSQL
also implements lock-free skip lists and hash tables that are kept in memory for fast,
random access to data. Lock-free skip lists are the primary index-backing data structure
in MemSQL. Compared to B-trees for disk-based databases, skip lists perform extremely
well in-memory and under high concurrency, thereby delivering be er scalability.

Figure 4. Skiplist index

9

## Page 10

I ntroduc on to MemSQL

Mul -Version Concurrency Control
MemSQL favors parallelism and uses mul -version concurrency control (MVCC) to
prevent queries from blocking each other in mul -threaded applica ons. Today’s
real- me applica ons - especially those with high volumes of streaming data, or mixed
read and write workloads - cannot tolerate the performance loss that comes with
database locking.
By having a concept of versioning, and preserving older versions, MVCC allows the
database to reduce the number of read-write conﬂicts among opera ons. Versions in
MemSQL are implemented as a lock-free linked list. Each  me a transac on modiﬁes a
row, MemSQL creates a new version that sits on top of the exis ng one. The new
version is visible only to the transac on that performed the write - when accessing the
same row, read queries see the old version.
Modiﬁed rows are queued for garbage collec on “behind the scenes,” so that old
versions are eﬃciently cleaned up, without the need for a full-table scan. MVCC delivers
eﬃciency and consistency across transac ons. A lock in MemSQL only occurs in the case
of a write-write conﬂict on the same row. MemSQL takes a row level lock in this case
because it’s easier to program around - the alterna ve would be to fail the second
transac on, which requires the programmer to resolve the failure.
Implemen ng lock-free data structures with MVCC enables MemSQL to avoid locking on
both reads and writes when upda ng tables. As a result, writes can operate at greater
throughput, while a large number of concurrent reads happen simultaneously. Since
reads and writes never block one another, this minimizes query stalls and allows for
greater parallelism - concurrent threads can modify the same object, and even if one
thread stalls or stops in the middle of an opera on, the remaining threads can carry on
processing data.
10

## Page 11

I ntroduc on to MemSQL

Mul -version concurrency control summary:

● Every write creates a new version of row
● Commits are atomic
● Old versions are garbage-collected
● Reads are never blocked
● Row-level locking for writes include
DELETE
● Allows for online ALTER TABLE

Figure 5. MVCC control
summary

Disk-op mized columnstores and memory-op mized rowstore tables
MemSQL supports storing and processing data using two types of data stores: a
completely in-memory rowstore and a disk-backed columnstore. Rowstores and
columnstores diﬀer both in storage format (row vs. column) and in storage medium (RAM
vs. disk). MemSQL allows querying rowstore and columnstore data together in the same
query.
The rowstore is typically used for highly concurrent online transac on processing (OLTP)
and mixed OLTP/analy cal workloads. The whole data set is kept in memory - providing
fast writes and suppor ng thousands of concurrent queries.
Rowstores uses a transac on log to avoid disk I/O bo lenecks on writes. Transac ons
are commi ed to disk as logs and periodically compressed as snapshots of the en re
database.
11

## Page 12

I ntroduc on to MemSQL
Figure 6. MemSQL designed for durability

To restore a database, MemSQL loads the most recent snapshot and replays remaining
transac ons from the log. The granularity of logging and frequency of snapshots are both
conﬁgurable. Because MemSQL only writes logs and snapshots to disk, all disk I/O is
sequen al. In-memory writes are serialized into a transac on buﬀer. A background
process pulls groups of transac ons and persists them to disk.
MemSQL’s disk-based columnstore features up to 80% compression and is capable of
storing petabytes of data. Columnstores are op mized for complex queries over large
data sets that don’t ﬁt in memory. The user determines whether tables are stored as
rowstores or columnstores at table deﬁni on  me.

Figure 7. Rowstores and columnstores in MemSQL
12

## Page 13

I ntroduc on to MemSQL

MemSQL supports indexing on Rowstore (Skiplist and Hash indexes) and Columnstore
(Hash indexes) to eﬃciently retrieve rows as needed.

Distributed Query Processing
MemSQL supports fast distributed query processing, with a query op mizer that is fully
aware of data distribu on, and a query execu on system that takes advantage of
compila on and vectoriza on , achieving 10X to 100X performance gains. The following
diagram illustrates MemSQL query processing at a high level.
Query Op miza on
The MemSQL Query Op mizer uses search and
heuris cs, driven by cost models based on
sta s cs, to ﬁnd high-quality distributed query
execu on plans. The op mizer is fully aware of
data distribu on and can use broadcast, shuﬄe,
local-global aggrega on, s emi-join reduc on,
and co-located join

Figure8. Query op miza on
opera ons to solve queries with limited and judicious use of data movement across the
cluster. Sta s cs and summary informa on available to the op mizer include dis nct
count informa on, histograms, and high-quality random samples of data from both row
store and column store tables.
The MemSQL Query Op mizer is a modular component in the database engine. The
op mizer framework is divided into three major modules:
13

## Page 14

I ntroduc on to MemSQL

(1) Rewriter: The Rewriter applies SQL-to-SQL rewrites on the query. Depending on the
characteris cs of the query and the rewrite itself, the Rewriter decides whether to apply
the rewrite using heuris cs or cost; the cost being the distributed cost of running the
query. The Rewriter intelligently applies certain rewrites in a top-down fashion while
applying others in a bo om-up manner, and also interleaves rewrites that can mutually
beneﬁt from each other.
(2) Enumerator: The Enumerator is a central component of the op mizer, which
determines the distributed join order and data movement decisions as well as local join
order and access path selec on. It considers a wide search space of various execu on
alterna ves and selects the best plan, based on the cost models of the database
opera ons and the network data movement opera ons. The Enumerator is also invoked
by the Rewriter to cost transformed queries when the Rewriter wants to perform a
cost-based query rewrite.
(3) Planner: The Planner converts the chosen logical execu on plan to a sequence of
distributed query and data movement opera ons. The Planner uses SQL extensions
called RemoteTables and ResultTables to represent a series of Data Movement
Opera ons and local SQL Opera ons using a SQL-like syntax and interface, making it
easy to understand, ﬂexible, and extensible.
Query Execu on
MemSQL query execu on technology tends to be superior overall to query execu on
technology in legacy database systems, some mes by up to a factor of 10 or more.
MemSQL's query execu on technology is thus o en a mo va ng factor to move
applica ons to MemSQL to get lower TCO, a be er user experience, or enable
applica ons that were not feasible before. MemSQL parameterizes queries, compiles
them, and stores them in a plan cache. On subsequent execu ons, MemSQL takes a
query plan from the cache and runs it so it need not be compiled again.
14

## Page 15

I ntroduc on to MemSQL

Unlike established database products, MemSQL compila on translates a query all the
way to machine code. This, combined with in-memory row store storage structures
designed with code genera on in mind, allows query processing rates on the order of 20
million rows per second per core against an in-memory skip list row store table. That is
about 10X faster than the per-core processing rate for scans of the B-tree indexes found
in most legacy databases, in many cases.
For columnstore tables, MemSQL uses a high-performance vectorized query execu on
engine that can operate on blocks of 4K rows at a  me, very eﬃciently. This vectorized
execu on engine also makes use of single-instruc on, mul ple-data (SIMD) instruc ons
available on Intel and compa ble processors that support the AVX-2 instruc on set.
Processing rates on columnstore tables are o en over 100 million rows per second per
core, and some mes as much as 2 billion rows per second per core when using SIMD
and opera ons on encoded (compressed) data.
MemSQL also supports high-performance data movement for broadcast and shuﬄe
opera ons. This implementa on gets is speed by sending data over the wire in its na ve
in-memory format, so it does not have to be serialized on the sending side or deserialized
on the receiving side. Rather, it can be operated on directly a er it is received, saving
CPU instruc ons, and thus total execu on  me.
Query Processing Summary
Together, compiled query plans, ﬂexible storage op ons, lock-free data structures, MVCC
and a mature op mizer allows for be er performance on mul ple cores with high data
accessibility, even under high concurrency. This is part of the "secret sauce" that makes
MemSQL diﬀerent than other data pla orms out there.

15

## Page 16

I ntroduc on to MemSQL
Core Architecture
MemSQL u lizes a distributed, shared-nothing architecture that runs on a cluster of
servers, and leverages memory and disk infrastructure for high throughput on concurrent
workloads. No two nodes in a MemSQL cluster share CPU, memory, or disk.

Figure 9. MemsSQL architecture

Our architecture is built for horizontal scalability on commodity hardware, in your data
center or in the cloud. MemSQL enables high performance and fault tolerance on large
data sets and high-velocity data.

16

## Page 17

I ntroduc on to MemSQL

Key Components of a MemSQL Cluster
As shown in Figure 5, a MemSQL cluster consists of aggregator nodes and leaf nodes.
The aggregator serves as a query interceptor and router, manages cluster metadata and
is responsible for cluster monitoring and failover. A leaf node is a MemSQL server
instance that stores data and executes queries issued by the aggregator.
In typical deployments, the aggregator-to-leaf node ra o is generally 1:5. Increasing the
number of aggregators can improve opera ons like data loading and can allow for
MemSQL to process more client requests concurrently. Applica ons serving many clients
have a higher aggregator-to-leaf ra o, and those with more demanding storage
requirements need more leaves per aggregator.
Client applica ons connect to an aggregator, which serves as the query router in the
cluster. When the client sends a SQL query, the aggregator will parse, compile and
distribute the query across the leaf nodes. In the leaf node, MemSQL may further
op mize the query as needed and execute on the local store of data. This allows
MemSQL to maintain high query performance even with rapidly changing data. The leaf
nodes quickly compute the query results and send them back to the aggregator. The
aggregator then aggregates the results from each leaf and sends the ﬁnal result back to
the client.
Figure 10. Massively parallel processing for query execu on
17

## Page 18

I ntroduc on to MemSQL

Database Par  ons and Sharding
When a user creates a database in MemSQL, it is always par  oned (a minimum of 2
par  ons).
As seen below , a database is a sum of all of its par  ons. Par  ons reside on the leaf
nodes.

Figure 11. Using leaf nodes as par  ons with MemSQL

Each par  on in-itself is implemented as a database on a leaf. When a sharded table is
created, it is split according to the number of par  ons of its encapsula ng database.
Each par  on will hold a slice of the table.
Figure 12. Leveraging shard keys for distributed tables
18

## Page 19

I ntroduc on to MemSQL

Sharded and Reference tables
MemSQL supports both distributed (or sharded) and reference (or duplicated) tables.
Both table formats can be as rowstore or columnstore tables. For sharded tables, the
primary key acts as the hash and each shard is stored on the respec ve leaf nodes. For
reference tables, the table is replicated to all nodes (including aggregators) and is well
suited for smaller, slowly changing tables.
Figure 13. Sharded vs Reference Table designsData Types

MemSQL supports a variety of data types, including integers,  mestamp, string types like
CHAR and VARCHAR, and compound types such as computed columns, ENUM and SET.
Addi onal complex data types that are supported include geospa al, full text (search
capability), and semi-structured JSON data.

Parallel Data Ingest with Pipelines
MemSQL Pipelines is a MemSQL database feature that ingests data from external
sources in a con nuous manner. As a built-in component of the database, Pipelines can
extract, transform, and load external data without the need for third-party tools or
middleware.
19

## Page 20

I ntroduc on to MemSQL

Pipelines are robust, scalable, highly performant, and supports fully distributed
workloads.
Pipelines scales with MemSQL clusters as well as distributed data sources like Ka a,
Amazon S3 and HDFS. Pipelines data is loaded in parallel from the data source to
MemSQL leaves, which improves throughput by bypassing the aggregator. Addi onally,
Pipelines has been op mized for low lock conten on and concurrency.
The architecture of Pipelines ensures that transac ons are processed exactly once, even
in the event of failover. Pipelines makes it easier to debug each step in the ETL process
by storing exhaus ve metadata about transac ons, including stack traces and stderr
messages.
MemSQL Pipelines Data Flow

Figure 14. Parallel data ingest in MemSQL using Pipelines

20

## Page 21

I ntroduc on to MemSQL
Cluster Management
In this sec on, we’ll go over the key cluster management aspects of MemSQL, including
the workload manager, distributed storage, and security.
MemSQL’s distributed system allows clusters to be scaled out at any  me to provide
increased storage capacity and processing power. Sharding occurs automa cally and the
cluster re-balances data and workload distribu on. Data remains highly available and
nodes can go down with li le eﬀect on performance. As Data is distributed and the
cluster is self-healing and elas c, it allows for scale-out/in data processing.
With  ered storage, you can take advantage of MemSQL’s memory-op mized rowstore
tables for high-speed query processing or inges on, or disk-op mized columnstore
tables for analy cs. MemSQL has a SQL query op mizer that runs on both row-based
and column-based tables. This gives you the ability to do transac onal processing,
analy c processing, or both at once, using the best table structure for each workload.
Dynamic Cluster resizing
MemSQL features powerful but simple cluster management with dynamic cluster
opera ons and no single point of failure. You can add or remove nodes - leaves or
aggregators - to the cluster at any  me while keeping the cluster online, even while
running a workload.
Figure 15. Scaling-out a MemSQL cluster
21

## Page 22

I ntroduc on to MemSQL

Data Replica on
A MemSQL cluster is resilient to failure with automa c failover and self-healing
capabili es. MemSQL allows you to store a redundant copy of data within a cluster. Leaf
nodes are organized into availability groups such that each node is paired with a node in
the other availability group. Each leaf node has a pair that replicates its data, and can be
conﬁgured to do so synchronously or asynchronously. In case of node failure, MemSQL
restores data and promotes replica par  ons to put the cluster back online.

Figure 16. Replicas in MemSQL are promoted to master

MemSQL also supports fully automa c cross-data center replica on that can be
provisioned with a single command. The replica cluster stores a read-only copy of data
asynchronously replicated from the primary cluster. In the event of a major failure in the
primary cluster, MemSQL can promote the secondary cluster, immediately making it a
"full" MemSQL cluster. In addi on to providing disaster recovery assurance, the
secondary cluster can also be used for heavy read-only workloads.

22

## Page 23

I ntroduc on to MemSQL

Feedback-driven Workload Manager
MemSQL automa cally manages cluster workloads func ons that limit execu on of
queries that require fully-distributed execu on to ensure that they are matched with
available system resources. Using built-in ML func ons, workload management
intelligently es mates the number of connec ons and threads needed to execute queries
that require reshuﬄe and broadcast opera ons, and admits the query only if workload
management can assign the necessary resources.
Workload management also es mates the amount of memory required to execute
queries and only runs queries if suﬃcient memory is expected to be available.
Queries that are not immediately executed are queued and are executed when system
resources become available. Workload management improves overall query execu on
eﬃciency and prevents workload surges from overwhelming the system. It allows queries
to run successfully when the system is low on connec ons, threads, or memory.
Resource pools include the following:
●Memory Percentage: This is the percentage of memory resources allocated to the
pool
●Query Timeout : The number of seconds specifying the  me a er which a query
running in the pool will be automa cally terminated
●So  and Hard CPU Limit Percentage: This is the percentage of CPU resources
allocated to the pool
●Maximum Concurrency: The maximum number of concurrent SQL queries that are
allowed to run cluster-wide across all aggregators

23

## Page 24

I ntroduc on to MemSQL

MemSQL Security
Security is an important aspect of any data pla orm. To meet regulatory and compliance
requirements, MemSQL supports several security features in the areas of authen ca on,
authoriza on, audi ng, and encryp on.
Exis ng account access can be easily managed via PAM (Pluggable Authen ca on
Module), SAML, or GSSAPI (Kerberos) authen ca on support. MemSQL also implements
RBAC to protect sensi ve data for tens of thousands of dis nct users and their speciﬁc
access roles.

MemSQL’s audi ng feature provides conﬁgurable database logging to a secure external
loca on to support informa on security tasks such as tracking user access. Data can be
encrypted at ingest  me and is distributed across nodes over TLS.

24

## Page 25

I ntroduc on to MemSQL

MemSQL Studio
The MemSQL Studio interface lets you monitor, debug and interact with all of your
MemSQL Clusters. Designed to be lightweight, easy to deploy, and easy to upgrade,
MemSQL Studio provides the tools you need to maintain cluster health without the
overhead of complex, heavyweight, and error-prone client so ware. The built-in query
proﬁler delivers historical usage sta s cs to shed light on what queries and resources are
u lizing the most  me. You can visualize and diagnose query bo lenecks and compute
resources to ensure op mal performance and availability.

Figure 17. MemSQL Studio tool
MemSQL Studio turns user ac ons into standard SQL queries that are run against your
MemSQL Cluster. Results are then displayed back to you in the form of tables and
graphics that help you understand your cluster be er. Conceptually, MemSQL Studio is a
UI on top of the MemSQL database engine itself, pairing the stability and security
guarantees of the command line with the ease of use of a visual UI. MemSQL Studio also
comes with an in-built visual SQL client, so it can be used safely alongside other tools
such as MemSQL Ops.
25

## Page 26

I ntroduc on to MemSQL

Cloud-na ve Support and Managed Service
As a cloud-na ve database, MemSQL can deploy across hybrid, mul -cloud, and
on-premises environments. The MemSQL Kubernetes Operator provides an easy way to
deploy and manage data infrastructure on private or public clouds.
Managing a cluster is simple with the Kubernetes Operator. With the Kubernetes
command-line interface (CLI) and the Kubernetes API, the Operator can be used the
same way as other standard Kubernetes tools. The commands and opera ons are the
same across all the major clouds, public and private, as well as in on-premises
environments. You describe the state of the cluster that you want; Kubernetes creates it,
and then maintains that state for you.
MemSQL also provides Managed Services on AWS/Azure/GCP Public Cloud pla orms.
MemSQL manages all management aspects of the cluster freeing the customer of the
necessity to maintain a dedicated staﬀ for administra on of the cluster.

26

## Page 27

I ntroduc on to MemSQL
MemSQL Innova on History
MemSQL oﬀers a full featured database pla orm through 6 plus years of innova ve
engineering. The chart below describes some of the notable advances over  me.
MemSQL History and Roadmap

Figure 18. MemSQL innova on history

27

## Page 28

I ntroduc on to MemSQL
Conclusion
Modern enterprises requires a data pla orm that is versa le, cost eﬃcient, and
performant. Not only does the data pla orm needs to support and improve legacy
workloads, but also be able to deliver on new business requirements.
MemSQL is a modern data pla orm that is well suited to meet today’s demanding
requirements. It oﬀers an easy migra on path from legacy pla orms, is cloud-friendly,
and supports modern workloads seamlessly.
MemSQL allows for infrastructure convergence, simplicity, and support for predic ve
capabili es in a cost-eﬀec ve and highly performant manner.

28

## Page 29

I ntroduc on to MemSQL

Appendix: MemSQL Capability Checklist

MemSQL Capabili es
MemSQL
ARCHITECTURE
Distributed Shared Nothing Scale out Architecture ⬤
ACID transac ons ⬤
High Availability and Disaster Recovery ⬤
Lock-free synchroniza on ⬤
Mul -version concurrency control ⬤
Distributed Query execu on ⬤
Deployment ﬂexibility (Mul -Cloud and On-Premises) ⬤
Compressed columnar table format on disk ⬤
Row store in memory ⬤
QUERY
SQL-92 ⬤
SQL-99 OLAP Extensions ⬤
SQL-2003 extensions ⬤
Mul -statement transac on ⬤
29

## Page 30

I ntroduc on to MemSQL
SELECT FOR UPDATE ⬤
Procedural Language with support for Procedures, UDF, TVF and
⬤
UDAF
Full text search ⬤
Vectoriza on and single instruc on, mul ple data (SIMD) ⬤
Opera ons on encoded data ⬤
Bloom ﬁlter pushdown for hash join ⬤
Local join support (hash, merge, nested loop) ⬤
Distributed join support (broadcast and reshuﬄe) ⬤
Query op miza on and Auto Sta s cs ⬤
Oracle compa bility extensions ⬤
Adap ve query compila on and execu on ⬤
Just-in- me (JIT) compila on ⬤
STORAGE
Rowstore In-Memory (with compression) ⬤
Rowstore skiplist indexes In-Memory ⬤
Columnstore on Disk ⬤
Columnstore Hash indexes on Disk ⬤
Sharded and Reference tables ⬤
Temporary tables ⬤
DATA TYPES
30

## Page 31

I ntroduc on to MemSQL
JSON ⬤
Rela onal ⬤
Full text ⬤
Geospa al ⬤
INGESTION
Na ve pipeline for data inges on ⬤
LOAD DATA enabling bulk loads ⬤
Na ve Parallel Ingest from many data sources (Linux File System,
⬤
S3, Azure Blob Store, HDFS, Ka a)
Supports most popular formats (CSV, Avro, JSON) ⬤
Load to stored procedures (ELT) ⬤
Pipelines with transform scripts ⬤
CLUSTER OPERATIONS
Zero down me node management (add/remove) ⬤
Automa c recovery from node failures ⬤
Rolling produc on upgrades ⬤
Automated full backup ⬤
Database Replica on across Geographies to enable disaster
⬤
recovery
Management and Monitoring tools ⬤
Online opera onal capabili es (add/remove nodes/re-balance
⬤
etc.)
31

## Page 32

I ntroduc on to MemSQL
Monitoring UI with Visual Explain plan ⬤
Troubleshoo ng UI ⬤
Automated Workload management ⬤
SECURITY
Audi ng ⬤
Strict Mode ⬤
Encryp on ⬤
RBAC ⬤
Authen ca on (GSSAPI/Kerberos) ⬤
ECOSYSTEM
Looker ⬤
Zoomdata ⬤
⬤
Tableau
⬤
Streamsets
⬤
SAS Access
⬤
Informa ca
⬤
Data Virtuality pipes
⬤
Talend
⬤
Power BI
CLIENT DRIVERS
⬤
MariaDB command-line client
32

## Page 33

I ntroduc on to MemSQL
⬤
MariaDB C/C++ connector
⬤
JDBC
⬤
ODBC
⬤
MySQL Connector
⬤
DBD-MariaDB Perl library
⬤
MySQL Connector/NET (C# and other .NETlanguages)

33
