6.0 Release Notes
MemSQL 6.0 includes enhancements to query processing with up to 80X performance improvement for group-by/aggregate queries, and broader SQL support. It also introduces extensibility features, and enhanced manageability and resiliency.
MemSQL 6.0 contains the following new capabilities:
MemSQL 6 introduces extensibility features as part of the new MemSQL Procedural SQL (MPSQL) language. MemSQL developers can create user-defined:
- Stored procedures (SPs)
- Scalar-valued functions (UDFs)
- Table-valued functions (TVFs)
- Aggregate functions (UDAFs)
SPs, UDFs, and UDAFs are compiled to machine code for high performance. Array and record types are supported in SPs and UDFs. SPs and UDFs also support exception handling. The MPSQL language will be familiar and straightforward to learn for database developers who have written functions and stored procedures in other database languages.
Query Language Features
The following new query language features are included in the release:
- Window functions with complex frames like “between 5 preceding and current row.” These are useful for moving averages needed for financial applications, electric utilities applications, and more.
- New window functions, including first_value, last_value, nth_value, percentile_disc, percentile_cont
- Cross-database joins and cross-database INSERT … SELECT
- UPDATE and DELETE through join
- UPDATE with subselect in SET clause
- Unenforced unique constraints
- INTERSECT, EXCEPT and MINUS
- Automatic cardinality statistics for columnstore
- Improved cost-based join optimization
- Improved hash join selection
- Improved optimization of UPDATE and DELETE queries
- Improved optimization of LEFT JOIN
- Enhanced join cardinality estimation
Query execution improvements include:
- Enhanced operations on encoded (compressed) data
- Single-Instruction Multiple Data (SIMD) support using AVX-2
- Queries like “select a, count(*) from t group by a” on columnstore tables speed up by as much as 80X
- Best-case performance for group-by/count(*) queries can exceed one billion rows per second per core on the latest processors
- Improvements of up to 30X on queries that:
- Filter on run-length- and dictionary-encoded data
- Group-by on encoded data, with one or more sum/avg/min/max or other aggregates
- Perform hash joins of small dimension tables to fact tables on encoded columns, through improved Bloom filter join acceleration
- More aggressive use of dictionary and run length encoding to benefit query execution
- Improved columnstore update performance
- Improved performance for access to JSON fields in columnstore tables
- Improved columnstore segment elimination based on hash joins
Improvements to the columnstore include:
- Computed columns can be added to columnstore tables via
- The default columnstore segment size has been increased from 102,400 to 1,024,000.
Data loading enhancements include:
- Variable and expression support in LOAD DATA
- Pipelines scheduling improvements
Manageability and Resiliency
Manageability and resiliency has been improved for the 6.0 release, including:
- Enhanced fault tolerance for leaf failover, master aggregator failover, and ALTER TABLE.
- Reduced blocking for backups, ALTER TABLE, leaf failover, and system management operations.
Benefits include fewer situations that require manual intervention, simplified application development, and improved availability.
Maintenance Release Changelog
2017-11-15 Version 6.0.9 (LATEST)
- Fixed bug that caused errors to occur when replicating the cluster internal database on clusters with more than 26 leaf nodes
2017-11-07 Version 6.0.8
- Fixed an issue with synchronously replicated databases where snapshots could sometimes cause a substantial replication slowdown. Asynchronous databases were not affected.
- Fixed an issue where MemSQL Pipelines crashed if an error message contained certain special characters
- Added cross-cluster replication between clusters with more than 63 nodes
- Added an additional check to remove invalid partition metadata upon upgrade to MemSQL 6
- Added an update to disallow using the CALL syntax on built-in functions
- Fixed a bug determining the definer of a UDF or stored procedure in cases where the user who created the UDF or stored procedure was logged in using a grant with a wildcard in it
2017-10-25 Version 6.0.7
- Improved MemSQL Pipelines to support using keywords as column names
- Fixed an issue with MemSQL Pipelines where pipeline metadata was not being copied properly during upgrade