Changes in MySQL HeatWave 8.0.26 (2021-07-23, General Availability)

• Advisor

• Auto Parallel Load

• Auto Scheduling

• Functionality Added or Changed

Advisor

• The new MySQL HeatWave Advisor provides string column encoding and data placement key recommendations based on machine learning models, data analysis, and MySQL HeatWave query history. Implementing MySQL HeatWave Advisor recommendations can improve query performance and reduce the amount of memory required on MySQL HeatWave nodes.

  The MySQL HeatWave Advisor also provides a Query Insights feature, which provides runtimes for successfully executed queries, and runtime estimates for EXPLAIN queries, queries canceled using Ctrl+C, and queries that fail due to out of memory errors. Runtime data is useful for query optimization, troubleshooting, and estimating the cost of running a particular query or workload.

  The MySQL HeatWave Advisor is implemented as a stored procedure named heatwave_advisor, which resides in the MySQL sys schema. Running Advisor involves issuing a CALL statement for the stored procedure with optional arguments.

  CALL sys.heatwave_advisor (options);

  For more information about the MySQL HeatWave Advisor, see Optimize Workloads for OLAP. (WL #14328, WL #14510, WL #14431, WL #14328, WL #14651)

Auto Parallel Load

• The new MySQL HeatWave Auto Parallel Load utility automates the process of preparing and loading tables into MySQL HeatWave and loads data using an optimized number of parallel load threads.

  The MySQL HeatWave Auto Parallel Load utility is implemented as a stored procedure named heatwave_load, which resides in the MySQL sys schema. Running Auto Parallel Load involves issuing a CALL statement for the stored procedure, which takes a list of schemas and options as arguments.

  CALL sys.heatwave_load (db_list,[options]);

  For more information about the MySQL HeatWave Auto Parallel Load utility, see Load Data Using Auto Parallel Load. (WL #14149)

Auto Scheduling

• The MySQL HeatWave query scheduling algorithm was improved. The revised algorithm prioritizes queries based on estimated cost and wait time in the queue, which enables dynamic, workload-aware query prioritization. Previously, queries were prioritized using a static cost-based prioritization model. (WL #14608)

Functionality Added or Changed

• DATE_ADD() and DATE_SUB() functions now support precision INTERVAL values (DECIMAL, DOUBLE, and FLOAT). (Bug #32725985, Bug #32438123)

• Support was added for multiple instances of COUNT(DISTINT) in a query. (Bug #32422984)

• Query compilation and processing was improved to permit combining aggregate operators into a single task in the physical query plan, which avoids fully materializing intermediate result sets. This enhancement reduces memory allocation and deallocation operations, memory usage, and execution time for affected queries. (WL #14614)

• The cost model that estimates MySQL HeatWave query runtimes can now use statistics from previously executed queries, which improves the accuracy of query runtime estimates. (WL #14546)

• MySQL HeatWave now supports CREATE TABLE ... SELECT statements where the SELECT query is offloaded to MySQL HeatWave and the table is created on the MySQL Database Service instance. This feature improves CREATE TABLE ... SELECT performance in cases where the SELECT portion of the statement is a long running, complex query. For more information, see CREATE TABLE ... SELECT Statements. (WL #14516)

• Support was added for REGEXP_REPLACE() and REGEXP_SUBSTR() regular expression functions, and error messaging was improved for REGEXP() function syntax mismatches, expression errors, and input argument errors. (WL #14641)