MemSQL is a distributed, relational database that handles both transactions and real-time analytics at scale. It is accessible through standard SQL drivers and supports ANSI SQL syntax including joins, filters, and analytical capabilities (e.g. aggregates, group by, and windowing functions).

MemSQL scales horizontally on cloud instances or industry-standard hardware, providing high throughput across a wide range of platforms. The MemSQL database maintains broad compatibility with common 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. MemSQL also features a data ingestion technology called MemSQL Pipelines that streams large amounts of data at high throughput into the database with exactly-once semantics.

Try MemSQL using our Deployment guide.

More detail about MemSQL is described in the sections below.

Data Ingestion

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

For more information, see:

• LOAD DATA

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 an in-memory rowstore or a disk-based columnstore. The in-memory rowstore provides optimum real-time performance for transactional workloads. The disk-based 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 supports high concurrency for simultaneous users. 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, with MemSQL using 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:

• Distributed Architecture

• Code Generation

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:

• True shared-nothing horizontal scaling and distributed parallel query execution

• Columnstore query processing for fast aggregations and optimized disk consumption

• Query compilation for faster execution

Deploy anywhere

MemSQL can be deployed anywhere in the cloud or on-premises without requiring 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:

• Deployment Guide

• System Requirements

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.

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:

• How to Connect to MemSQL

• Spark Connector

Was this Article Helpful?

Thanks For Your Feedback!