MemSQL 2.5 Release Notes

Upgrading to 2.5

MemSQL supports various methods for upgrading to version 2.5 depending on you current installation. For more information see: Upgrading MemSQL.

JSON Data Type

MemSQL 2.5 supports JSON as a column type, which enables developers to store and query structured and semi-structured data together in one database. MemSQL JSON integration provides the following features:

  • Use standard SQL functions with JSON, 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/or nested JSON objects.

Here is an example of querying JSON data using SQL: SELECT * FROM accounts WHERE click_stream::%is_active AND JSON_LENGTH(click_stream::clicked) > 1;

This query returns the following result:

+----+-----------------+-------------+----------------------------------------------------------+
| id | email           | name        | click_stream                                             |
+----+-----------------+-------------+----------------------------------------------------------+
|  8 | sandy@gmail.com | sandy beach | {"clicked":[{"URI":"/photos","id":67},{"URI":"/profile", |
|    |                 |             |  "id":89}], "id":45,"is_active":true,"tags":["customer", |
|    |                 |             |  "lifeguard"]}                                           |
+----+-----------------+-------------+----------------------------------------------------------+
1 row in set (0.01 sec)

For more information on MemSQL JSON support see: Javascript Object Notation (JSON).

Improved Index and Scan Performance

MemSQL 2.5 performs nearly 3x faster over large datasets, enabling scans of over 30 million rows per second per core.

Online ALTER TABLE

MemSQL 2.5 supports online ALTER TABLE operations (ADD [COLUMN], DROP [COLUMN], and ADD {INDEX|KEY}) on distributed tables. The table is available for reads and writes while ALTER TABLE executes. The memory overhead of online ALTER TABLE is only a few megabytes, regardless of the table size.

Online Backup and Restore

MemSQL 2.5 offers single commands to backup and restore a cluster.

  • BACKUP takes a point-in-time snapshot of data across the cluster while your read and write workloads continue to run.
  • RESTORE restores an entire distributed database. The new cluster can have a different number of nodes or different redundancy level from the original.

For more information, see Backing Up and Restoring Data.

SQL Support

MemSQL 2.5 supports the following operations on both standalone and distributed tables:

  • Scalar subselects, subselects with EXISTS and IN
  • Constants are parameterized in projections, as well as ORDER BY and GROUP BY clauses
  • UNION ALL, GROUP_CONCAT, various built-in functions, and expression performance improvements
  • Persisted computed columns on arbitrary SQL expressions