Querying millions of records in a source database or a logical data warehouse can be slow. This puts pressure on the source database and affects its performance. The accelerated query feature of the logical data warehouse significantly increases the speed of SQL query and analysis. In complex scenarios, performance can improve by more than 10 times. This feature also reduces the pressure on the source database.
Background information
The accelerated query feature of the logical data warehouse periodically synchronizes table data from the source database to a dedicated storage space in DMS. This enables you to quickly perform SQL analysis and queries in the logical data warehouse.
Prerequisites
The destination instance is added to DMS. For more information, see Add an ApsaraDB database or Add a third-party cloud or self-managed database.
DBLink is enabled for the instance.
Limits
This feature is available only for RDS MySQL instances and self-managed MySQL databases in the China (Hangzhou) region.
Each DMS tenant can store a maximum of 100 GB of accelerated data.
Billing
The accelerated query feature and the dedicated storage are free of charge.
Step 1: Configure an acceleration task
This step writes the source table data to the dedicated storage. You can complete this step in one of the following two ways:
Method 1: Configure an acceleration task for a database
Log on to Data Management (DMS) 5.0.
In the navigation pane on the left, click Database Instance. In the panel that appears, click Logical Data Warehouse.
On the Logical Data Warehouse page, on the Data Source tab, expand the target DBLink (data source) and the database to find the target table.
Right-click the table name and click Accelerate Table.
In the Precheck step, check whether the table is already accelerated and whether it supports batch acceleration. If it is not accelerated, click Next.
In the Create Logical View step, enter a View Name, select a Virtual Database to save the view, and click Create.
NoteYou can select the built-in `public` database as the virtual database.
In the Configure Acceleration step, keep the acceleration switch on and select an Acceleration Frequency. The following frequencies are available:
One-time acceleration: Performs a one-time full synchronization of the table data to the dedicated storage. Subsequent queries for incremental data will not be accelerated.
Continuous acceleration: Synchronizes table data to the dedicated storage based on the configured scheduling period. You can schedule updates by Hour, Day, Week, or Month.
Set Scheduling Cycle to Hour.
If you set Timed Scheduling to Specify time range: Set the time range and interval in hours for the schedule. You must select a start time, interval, and end time.
NoteIf the start time is empty, the task starts immediately.
For example, if the start time is 02:00, the interval is 2 hours, and the end time is 06:00, the data is synchronized at 02:00 and 04:00. The sync task ends at 06:00.
If you set Timed Scheduling to Specify time: Select the specific times for synchronization. You can select multiple times.
For example, you can select 02:00, 05:00, and 20:00. If a task that starts at 02:00 is not finished by 05:00, the 05:00 scheduled task is skipped.
If you set Scheduling Period to Day
Set a Specific time for the daily synchronization. For example, start the data sync at 05:00 every day.
If you set Scheduling Period to Week
Set the day of the week (multiple selections are allowed) and a Specific time. For example, start the data sync at 05:00 every Tuesday and Saturday.
If you set Scheduling Period to Month
Set the day of the month (multiple selections are allowed) and a Specific time. For example, start the data sync at 05:00 on the 5th and 15th of each month.
Click Submit. If the configuration is correct, a message appears indicating that the acceleration is configured successfully.
Method 2: Configure an acceleration task for a view
To use this method, make sure you have created a logical view for the table you want to accelerate. For more information about how to create a logical view, see Create a logical view.
On the Logical Data Warehouse page, on the Virtual DB tab, expand the database and logical views to find the logical view you want to accelerate.
Right-click the name of the logical view and select Accelerate View.
Configure acceleration for the table.
In the Accelerate View dialog box, you can see the tables that the system automatically detects for the view. You can configure acceleration for each table independently. For more information about how to configure acceleration, see Accelerate Table.
Step 2: Use accelerated query
This step reads data from the dedicated storage.
After you configure the acceleration task, go to the SQL Console page of the logical data warehouse and select Use Accelerated Data.
ImportantIf the acceleration task is configured for scheduled batch synchronization, the query uses the data that was synchronized to the dedicated storage in the previous period. The data is not updated in real time. To query the latest data, do not select Use Accelerated Data. The system will read and query data directly from the source database.
Enter the SQL query and click Execute.
The system automatically checks whether the query uses an accelerated table. If it does, a message that says Accelerated data used appears below the execution result. You can click the message to view the accelerated tables.
Other operations
View query duration, operational logs, and other information.
Right-click the target accelerated table and click the Acceleration Task tab. On the Acceleration Task page, view the Acceleration Task Name, task execution Duration, and Operational Log.
Check whether accelerated query is configured for a database.
On the Data Source tab in the logical data warehouse, click the
icon.In the filter dialog box, select Yes to filter for databases that have acceleration enabled.
View the names of the accelerated Database and Table Name.
Right-click the target accelerated table and view the details on the Acceleration Details tab.
Modify the acceleration task configuration.
Right-click the target accelerated table and click the Acceleration Details tab.
In the Acceleration Information section, click Modify. After you make changes, click Confirm.