How MemSQL Works

MemSQL is a distributed, relational database that handles mixed transactions and real-time analytics at scale. It is accessible through standard SQL drivers and syntax and supports a broad ecosystem of drivers and applications.

It can scale horizontally on commodity hardware, which allows it to provide high throughput across a wide range of platforms. It is very compatible with other technologies in the modern data processing ecosystem (e.g. orchestration platforms, developer IDEs, and BI tools), so you can easily integrate it in your existing environment. It features an in-memory rowstore and an on-disk columnstore to handle both highly concurrent operational and analytical workloads. It also features data ingestion technology called MemSQL Pipelines that can stream large amounts of data at high throughput into MemSQL with exactly-once semantics.

image

Try MemSQL using our Quick Start guides.

More detail about MemSQL is described in the sections below.

Data Ingestion

MemSQL can load data continuously or in bulk from a variety of sources. Popular loading sources come from files, a Kafka cluster, cloud repositories like Amazon S3, HDFS, or from other databases. The highly distributed system can ingest data streams using parallel loading to maximize throughput.

MemSQL Pipelines is an easy-to-use built-in capability that extracts, transforms, and loads external data using sources such as Kafka, S3, Azure Blob, and filesystems. It is ideal for scenarios where data from a supported source must be ingested and processed in real-time. This makes MemSQL Pipelines a good alternative to third-party middleware for basic ETL operations that must be executed as fast as possible, thus eliminating traditional long-running processes such as overnight batch jobs.

For bulk data load operations, MemSQL offers a LOAD DATA function that imports files in parallel to maximize performance.

For more information, see:

High-Performance for OLTP and OLAP Workloads

MemSQL is a highly-scalable distributed system. Data is sharded automatically amongst nodes in a cluster. Sharding optimizes query performance for both distributed aggregate queries and filtered queries with equality predicates. You can add nodes and redistribute shards (also known as partitions) as needed to scale your workload.

MemSQL also supports storing and processing data using a completely-in-memory rowstore or a disk-backed columnstore. The in-memory rowstore provides optimum real-time performance for transactional workloads. The disk-baked columnstore is best for analytical workloads across large historical datasets. Rowstores are the default table type, but columnstores can be created by specifying a columnstore index type. A combination of the MemSQL rowstore and columnstore engines simplify your technology stack by allowing you to merge real-time and historical data in a single query.

In addition to handling mixed workloads, MemSQL is designed to be highly concurrent. A distributed query optimizer evenly divides the processing workload to maximize the efficiency of CPU usage. Query plans are compiled to machine code and cached to expedite subsequent executions. A key feature of these compiled query plans is they do not pre-specify values for the parameters. This allows MemSQL to substitute the values upon request, which enables subsequent queries of the same structure to run quickly. Moreover, due to MemSQL’s use of Multi-Version Concurrency Control (MVCC) and lock-free data structures, data remains highly accessible, even amidst a high volume of concurrent reads and writes.

For more information, see:

Analyzing Data

MemSQL queries data using scalable standard SQL. The JDBC/OBDC interface enables widely-available BI tools such as Looker, Tableau, and Microstrategy to query and interact with data. Users or applications can connect and write SQL queries for custom visualizations or embedded analytic requirements.

MemSQL delivers a number of query performance capabilities to accelerate response time. These include:

Deploy anywhere

MemSQL does not require specialty hardware to perform effectively; however, it will take advantage of single-instruction/multiple-data (SIMD) CPU instructions, specifically AVX2, and it is NUMA-aware to increase performance on newer machines. It can be deployed on bare metal, in VMs, and in the cloud.

For more information, see:

Durable and Redundant

In addition to being fast, consistent, and scalable, MemSQL is also durable. Transactions are committed to disk as log records and periodically compressed as snapshots of the entire database. If any node goes down, it can restart and use its logs to recover committed transactions.

MemSQL also supports high availability through the use of availability groups where nodes in the cluster share copies of data amongst each other in paired configurations. Nodes can failover as needed without the cluster going offline.

For more information, see:

Highly Compatible

MemSQL is an ODBC-compatible database. It is wire protocol compatible with MySQL so that applications that use a MySQL driver can connect to and use MemSQL transparently. MemSQL supports a subset of the MySQL syntax, plus extensions to support advanced features not in MySQL such as Distributed SQL, Geospatial, JSON, and Window Functions.

In addition to MySQL wire compatibility, MemSQL also integrates well with the stream processing framework Apache Spark to provide a simple way to create and manage real-time data pipelines.

For more client compatibility and integration information, see:

Was this article useful?