4.2.4 About Auto Parallel Load

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.

Requirements and Privileges for Auto Parallel Load

To run Auto Parallel Load in normal mode, the MySQL HeatWave Cluster must be active.

To use Auto Parallel Load, ask the admin user to grant you the following privileges. Replace user_name and database_name in the commands with the appropriate user name and database name.

The PROCESS privilege on the appropriate database in the DB System.

mysql> GRANT PROCESS ON database_name.* TO 'user_name'@'%';

The EXECUTE privilege on the sys schema.

mysql> GRANT EXECUTE ON sys.* TO 'user_name'@'%';

The SELECT privilege.

mysql> GRANT SELECT ON performance_schema.* TO 'user_name'@'%';

Automatic Loading and Unloading of DB System Tables and Partitions

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.

Check Load Status of a Table

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'.

Remove Table from Automatic Loading

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:

1. 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;

2. Set the SECONDARY_ENGINE of the table to NULL.

   mysql> ALTER TABLE table_name SECONDARY_ENGINE=NULL;

   The following example sets the SECONDARY_ENGINE of the table to NULL.

   mysql> ALTER TABLE data_files_1 SECONDARY_ENGINE=NULL;

3. Set the SECONDARY_ENGINE of the table to NO_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 to NO_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.

What's Next

Learn how to load data using Auto Parallel Load.