You are viewing an older version of this section. View current production version.
5.5 Release Notes
MemSQL 5.5 introduces MemSQL Pipelines, a native mechanism for scalable real-time data ingestion from a wide variety of streaming and static data sources. In addition, this release delivers 3x-5x faster query performance driven by a novel hash-table design, use of Bloom filters, native support for semi-joins and anti-joins, and improved concurrency management for distributed joins. MemSQL 5.5 also enhances ease of use with new functionality like Query Profiling, Workload Management, a more scalable new MemSQL Ops version, and faster recovery for row stores.
MemSQL Pipelines
MemSQL Pipelines is a native database feature that supports real-time data ingestion from Kafka streams with exactly-once semantics. Pipelines provides a robust, scalable, and highly performant way of extracting, transforming, and loading external data for distributed workloads. Learn more about Pipelines here.
Faster Hash Joins
MemSQL 5.5 delivers a new hash table design which combined with Bloom filters delivers up to 3x-5x performance improvements for hash joins.
Distributed Joins with High Concurrency
More efficient utilization of threads and connections delivers increased performance for distributed joins and enables higher levels of concurrent queries.
Support for Semi-Joins and Anti-Joins
MemSQL now delivers native support for executing certain correlated subqueries as semi-joins and anti-joins.
Query Profiler
The query profiling feature enables customers to run a query with the PROFILE
option in order to examine the execution details for each step of the query in terms of number of rows processed and the actual processing time. The query profiler can be helpful when identifying performance issues down to particular steps in query execution. Learn more about PROFILE here.
Workload Manager
System administrators have the challenging task of coordinating query workloads from their large user base. The workload manager prevents system overload by limiting the resource usage of queries executing at any given time. The workload manager queues up queries that can not be run immediately and runs them later when capacity becomes available. Learn more about Workload Management here.
MemSQL Ops
MemSQL Ops provides administration of MemSQL clusters. MemSQL 5.5 Ops now offers support for clusters with more than 100 nodes and beyond. Additionally MemSQL Ops provides UX for the newly introduced MemSQL Pipelines. For more information, see MemSQL Ops Releases.
Faster Recovery for Row Stores
In the event of a node failure, MemSQL now offers faster recovery for row stores with secondary indexes.
Columnstore Reference Tables
Creating reference tables obviates the need to reshuffle data and creates co-located joins. MemSQL 5.5 adds support for reference tables in the column store.
Enhanced Load Data Functionality
It is now possible to debug LOAD DATA
processes in MemSQL, leading to increased developer productivity. The LOAD DATA... IGNORE
command stores errors it sees during execution. Errors can be displayed using a new SHOW LOAD ERRORS
command, and can be used subsequently for corrective actions. Errors are stored in an in-memory buffer that is deleted when a subsequent query is run.
Additional New or Updated Functionality and Fixed Issues
New Functionality
- The
CHAR()
function is now supported. For example:
memsql> SELECT CHAR(77,101,109,83,81,'76');
+-----------------------------+
| CHAR(77,101,109,83,81,'76') |
+-----------------------------+
| MemSQL |
+-----------------------------+
1 row in set (0.07 sec)
- The
MONTHS_BETWEEN
function is now supported. For example:SELECT MONTHS_BETWEEN(DATE('2016-10-31 00:00:00'), DATE('2016-07-31 00:00:00'));
- The
TRUNCATE
clause is now supported for columnstore tables. - The
secure_file_priv
global variable is now supported, which either limits any import and export operations to the specified directory or disables import and export entirely if set toNULL
. - Table statistics can now be deleted using the
ANALYZE TABLE mytable DROP;
syntax. - The
enable_multipartition_queries
global variable is now supported, which disables parallel query execution for leaf nodes. - Concurrent update and delete operations to columnstore tables are now supported.
- User password policies can now be configured, including password expiration, password history counts, and other variables. For more information, see Configuring a Password Policy.
- MemSQL nodes can now be configured with a list of allowed TLS versions for client connections. Valid TLS versions must be specified in a node’s memsql.cnf file before the node is started. To see the list of allowed TLS versions, execute the
SHOW GLOBAL VARIABLES LIKE '%tls_version%'
command.
Updated or Changed Functionality
- The
ALTER TABLE RENAME
statement for rowstore tables now only affects table metadata; the table’s contents are no longer copied to a temporary table during renaming. - The
INSERT
statement now has full expression support, including arithmetic. For example:INSERT INTO mytable VALUES(1 + 2 * 3 / 4);
- The
EXPLAIN
clause now supportsUPDATE
andDELETE
statements. - The
LOAD DATA IGNORE
statement now records errors for rows that were not inserted. - The
ALTER TABLE
operation now supports renaming a column. For example:ALTER TABLE mytable CHANGE col1 col2
. - The
OPTIMIZE TABLE
operation has been improved for rowstore tables. Prior to MemSQL v5.5, only garbage collection was performed on the target rowstore table. In MemSQL v5.5 and newer, the target rowstore table is also compacted. - The
ALTER TABLE
operation now automatically creates a snapshot of the table after the operation completes. - The
ALTER TABLE
operation no longer drops statistics, but instead updates the statistics using the altered attributes. - The
ANALYZE TABLE
operation can now drop statistics about the table. For example:ANALYZE TABLE mytable DROP;
- The
PROFILE
clause is now more accurate for column store scans. - Users with
GRANT
andSUPER
privileges can now grant any privilege level. Prior to MemSQL v5.5, users withGRANT
orSUPER
privileges could only grant the same privileges they themselves had already been granted. - Reference tables are now supported for columnstore tables.
- Window functions will no longer excessively sort if an existing index matches the sorting order or if multiple
OVER
clauses specify the same order. - Recovery of rowstore tables is now much faster due to more efficient building of secondary keys.
- The default value for the
max_connection_threads
system variable on aggregators has been increased from 128 to 192. - For MemSQL deployments prior to v5.5 that were not managed by MemSQL Ops, the default value for the
maximum_memory
system variable was automatically set to 100% of host physical memory. In MemSQL v5.5 and newer, defaultmaximum_memory
is now set to the result of one of the following calculations, whichever is larger: - 90% of physical memory, or
- Physical memory minus 10 GB.
Fixed Issues
- Fixed an issue with
ALTER TABLE CHANGE
when renaming a column to address possible table corruption on recovery.
Maintenance Release Changelog
The changelog below contains MemSQL Database improvements and bug fixes introduced in maintenance or revision releases. For a similar list for MemSQL Ops, see MemSQL Ops Releases.
2017-04-10 Version 5.5.13 (LATEST)
- Fixed an issue with the
BACKUP
command that could fail with anUnknown thread id
error. - Fixed an issue with the
PROFILE
command that could cause a deadlock.
2017-01-05 Version 5.5.12
- Fixed a deadlock issue that could occur in rare situations for distributed join queries with both wide rows and skew in the data distribution.
2016-12-12 Version 5.5.11
- Improved columnstore table replication performance.
- Fixed an instability issue that could occur when adding JSON columns to a columnstore table.
2016-12-05 Version 5.5.10
- Fixed a MemSQL startup issue that affects older hardware without SSE4 support.
- Fixed a backup failure issue for columnstore tables that occurs if an
ALTER ADD COLUMN
statement is executed just prior to backup. - Fixed a performance issue with
MIN
andMAX
aggregate functions. - Fixed an instability issue when executing distributed join queries that use certain table aliases.
2016-11-14 Version 5.5.9
- The plan cache now records a query’s CPU execution time in milliseconds. For example, the
SHOW PLANCACHE;
command’s results include aCpuTime
column for each query. - Fixed a performance issue when executing a
RESTORE DATABASE
command. - Fixed a performance issue when executing an
ANALYZE TABLE
command.
2016-10-31 Version 5.5.7
- Fixed a metadata corruption issue that could occur after executing an
ALTER TABLE
command. - Fixed an issue where distributed join (broadcast) operations could hang after cluster failover.
- Fixed an issue where users with correct permissions could encounter “access denied” error messages during pipeline creation or pipeline updates.
- Enable multi-partition queries for first statement of a multi-statement transaction to reduce network traffic overheads in some cases.
- Fixed an error message for
CREATE TABLE AS ... SELECT NULL
statements, which could cause cluster instability. - Fixed an issue when recovering a cluster with a
redundancy_level
set to 2. Now when auto-attaching a leaf, the cluster will not rebalance until after the leaf has recovered.
2016-10-19 Version 5.5.6
- Columnstore tables now replicate much faster.
- Pipelines transforms now execute much faster.
- Performance has been improved during cluster failover by reducing the duration of partition locks.
- Fixed an issue where broadcast operations on temporary tables could fail with an error in some cases.
- Fixed an issue with queries that require many seek operations.
2016-10-03 Version 5.5.5
- Fixed an issue with
ALTER TABLE CHANGE
when renaming a column to address possible table corruption on recovery. PROFILE
is now more accurate for column store scans.- Added a global variable that disables parallel query execution for leaf nodes:
enable_multipartition_queries = false
. - Fixed an instability and accuracy issue that could occur for users upgrading query optimizer statistics from 5.1 to 5.5.