The Next Generation of SQL Database Technology

MemSQL's distributed database is an enterprise solution that has been proven in high velocity Big Data environments. MemSQL is an order of magnitude faster than traditional databases, doesn't require batch-loading, and becomes even more powerful as you scale out horizontally on commodity hardware.

“MemSQL adding true SQL-based access to JSON promises to bring analytical response to multi-structured datasets in a way that will be difficult to match.”

– John Myers, Senior Analyst, Enterprise Management Associates

Lab-Tested Performance.

Read the ESG Lab benchmark report to see MemSQL's unmatched speed and scalability on commodity hardware.

  Download the Report

Simple Tiered Storage Architecture.

MemSQL's tiered storage architecture features an in-memory row store and a flash or disk-based column store. With policy-driven storage management, MemSQL automatically moves data from memory to flash to disk. MemSQL simplifies the data life cycle, eliminating the need for separate databases and processes that require ETL and introduce latency.

Accelerated Query Execution.

MemSQL uses patented code generation technology to create a query execution plan that eliminates the need for interpretation along hot code paths. Each time MemSQL receives a new query, it compiles the SQL to machine code and then executes it. Each subsequent time the query runs, it immediately executes the compiled plan, accelerating query execution.

Massive Concurrency. Highly Parallelized.

MemSQL's distributed query optimizer enables highly parallelized query execution. MemSQL uses Multiversion Concurrency Control and lock-free data structures to enable highly concurrent data access without locking or sacrificing consistency. This is how MemSQL achieves its amazing scan performance and maintains high throughput during mixed read and write workloads.

Key MemSQL Advantages

  In-Memory Transactional Speeds By storing data in memory, MemSQL can serve query results from billions of records in seconds. Insert latency is less than a millisecond and data is persisted to disk for durability.
  Columnar Compression MemSQL's column store efficiently compresses data using a variety of techniques including run-length, dictionary, and block level compression.
  ANSI SQL Support MemSQL uses ANSI SQL. This enables easy integration with existing applications without costly query rewrites or custom connectors. In addition, MemSQL is MySQL wire compatible.
  Massively Parallel Execution MemSQL uses all available CPU for every type of workload. In a distributed system, you can potentially leverage thousands of cores for extremely fast data loading and analytic workloads.
  Lock-free Data Structures MemSQL leverages lock-free skip lists and lock-free hash tables to optimize for fast in-memory access to data. MemSQL does not lock on inserts or reads, enabling highly concurrent workloads.
  Multiversion Concurrency Control With MVCC, writes do not block reads and reads do not block writes. This means you can query MemSQL even as real-time data streams into the system. MemSQL's isolation level is read committed.
  Code Generation and Auto-Parameterization The first time a SQL statement is submitted, MemSQL generates, compiles, and caches a query execution plan. Future queries are stripped of their parameters and matched against cached query plan templates. Code generation removes interpretation along hot code paths and minimizes the number of CPU instructions per query.
  ACID Compliant Transactions are always committed to disk and later compressed into full-database snapshots. If a server crashes and needs to be restarted, it can read these files to recover the database.
  High Availability MemSQL replicates data across a network of leaf nodes to guard against server failure. The cluster engages the CPU of every server instead of wasting resources on passive backups. MemSQL also supports synchronous or asynchronous replication across data centers for extra protection.
  Online Replication In the middle of a continuous write workload, MemSQL can start replication on a slave without having to configure or pause the master. MemSQL will provision the slave database by downloading and replaying the most recent database snapshot and then streaming the log file.
  Flexible Schema MemSQL supports altering table schemas while the database is online. In addition, MemSQL natively supports JSON and even allows users to create JSON indexes online.
  Online Backup and Restore MemSQL offers online backup and restore utilities so ensuring durability does not require downtime.

Relational and JSON Data Together

MemSQL supports JSON as a data type, allowing developers to use different data formats in a single table and analyze JSON through an ANSI SQL interface.

How to Use SQL to Query JSON Data

Most Big Data solutions designed for JSON or JSON-like processing lack the consistency and analytical capabilities of a relational database management system. MemSQL is the best of both worlds because it allows users to store, query, and manipulate JSON in a relational environment.

  • Use standard SQL over JSON data including built-ins, GROUP BY, JOINs, and more.
  • Create JSON indexes online.
  • Client drivers require no changes to support JSON.
  • JSON properties are updatable.
  • JSON columns can contain ‘null’, ‘true’, ‘false’, double-precision floating-point values, quoted strings, heterogeneous arrays, and objects.

Access JSON Properties Through SQL

SELECT * FROM accounts WHERE click_stream::%is_active AND JSON_LENGTH(click_stream::clicked) > 1;

Results Returned

| id     | email             | name         | click_stream                                     |
| 1561   | | steven jones | {"id":45,"is_active":true,"latitude":85.189408,  |
|        |                   |              | "longitude":45.897608, "tags":["memory",         |
|        |                   |              | "database","query","performance","shard"],       |
|        |                   |              | "clicked":[{"id":67,URI:"/photos"},{"id":89,     |
|        |                   |              | "URI":"/profile"}]}                              |
| 4652   |  | peter johns  | {"id":84,"is_active":true,"latitude":80.456821,  |
|        |                   |              | "longitude":65.846521, "tags":["memory",         |
|        |                   |              | "database","query","performance","shard"],       |
|        |                   |              | "clicked":[{"id":67,URI:"/profile"},{"id":89,    |
|        |                   |              | "URI":"/product"}]}                              |