Auto Parallel Load is a feature of MySQL HeatWave that automates many of the steps for loading data, which include the following:
Excluding schemas, tables, and columns that cannot be loaded.
Defining
RAPID
as the secondary engine for tables that are to be loaded.Verifying that there is sufficient memory available for the data.
Optimizing load parallelism based on machine learning models.
Loading data into MySQL HeatWave.
As of MySQL 8.4.0, Auto Parallel Load adds the functionality to use Autopilot to collect statistics about frequently used DB System tables. Auto Parallel Load then automatically loads these tables into MySQL HeatWave. Conversely, if an automatically loaded table becomes cold due to not being used, then Auto Parallel Load automatically unloads it. Also, if there is not enough memory for a manually loaded table due to automatically loaded tables taking up too much memory, Auto Unload unloads automatically loaded tables to allow the manually loaded table to load.
As of MySQL 9.3.1, this feature extends to partitions. Auto Parallel Load automatically loads frequently used partitions, and automatically unloads partitions that are not being used.
Auto Parallel Load automatically loads any table that does not have the
secondary engine set, or the secondary engine is set to
RAPID
.
Auto Parallel Load has a 24-hour wait interval between checking the table statistics, and only checks them during a quiet period. Therefore, within 24 hours of the first query that accesses a table, Auto Parallel Load automatically loads the table into MySQL HeatWave if there is sufficient memory.
You can verify if a table is automatically loaded or loaded by
a user by querying the
performance_schmea.rpd_tables
table and the
LOAD_TYPE
column. A value of
SELF
indicates the table was automatically
loaded, and a value of USER
indicates the
table was loaded by a user. Learn more about the
rpd_tables
table.
To verify the load type of a table, query the
performance_schmea.rpd_tables
and
performance_schmea.rpd_table_id
tables and use an INNER JOIN
clause
to combine the ID
of the table you want to
query.
Run the following commands:
mysql> USE performance_schema;
mysql> SELECT rpd_tables.ID, rpd_table_id.NAME, rpd_tables.LOAD_TYPE
FROM rpd_tables INNER JOIN rpd_table_id ON rpd_table_id.ID=rpd_tables.ID
WHERE NAME='table_name';
Replace table_name
with the fully
qualified name of the table you want to query
(schema_name.table_name)
.
For example:
mysql> USE performance_schema;
mysql> SELECT rpd_tables.ID, rpd_table_id.NAME, rpd_tables.LOAD_TYPE
FROM rpd_tables INNER JOIN rpd_table_id ON rpd_table_id.ID=rpd_tables.ID
WHERE NAME='schema_1.table_1';
+-----+------------------+-----------+
| ID | NAME | LOAD_TYPE |
+-----+------------------+-----------+
| 303 | schema_1.table_1 | USER |
+-----+------------------+-----------+
To review all DB System tables, omit WHERE
NAME='
'.
table_name
You have the option to ensure that a table is not considered
for automatic loading by setting the
SECONDARY_ENGINE
of a table to
NO_AUTO
.
Run the following command:
mysql> ALTER TABLE table_name SECONDARY_ENGINE=NO_AUTO;
Replace table_name
with the
appropriate table name.
If the table is not currently loaded to MySQL HeatWave and
SECONDARY_ENGINE
is not currently set, you
can run the single command. For example:
mysql> ALTER TABLE data_files_1 SECONDARY_ENGINE=NO_AUTO;
If the table is currently loaded to MySQL HeatWave and
SECONDARY_ENGINE
is set to
RAPID
, do the following:
-
Unload the table from MySQL HeatWave.
mysql> ALTER TABLE table_name SECONDARY_UNLOAD;
Replace
table_name
with your value.The following example unloads the
data_files_1
table.mysql> ALTER TABLE data_files_1 SECONDARY_UNLOAD;
-
Set the
SECONDARY_ENGINE
of the table toNULL
.mysql> ALTER TABLE table_name SECONDARY_ENGINE=NULL;
The following example sets the
SECONDARY_ENGINE
of the table toNULL
.mysql> ALTER TABLE data_files_1 SECONDARY_ENGINE=NULL;
-
Set the
SECONDARY_ENGINE
of the table toNO_AUTO
. This is the setting that removes the table from consideration for automatic loading.mysql> ALTER TABLE table_name SECONDARY_ENGINE=NO_AUTO;
The following example sets the
SECONDARY_ENGINE
of the table toNO_AUTO
.mysql> ALTER TABLE data_files_1 SECONDARY_ENGINE=NO_AUTO; Records: 0 Duplicates: 0 Warnings: 1 Warning (code 1105): HeatWave Self-Load feature is enabled. Removing `data_files_db`.`data_files_1` from Self-Load consideration because its secondary engine is not RAPID or NULL.
A message confirms that the table is removed from automatic loading.
Learn how to load data using Auto Parallel Load.