ApsaraDB RDS:Create a DuckDB-based analytical read-only instance

Product introduction

Comparison between DuckDB-based analytical read-only instances, read-only instances, and OLAP databases

Compared with using primary instances, read-only instances, or OLAP databases for complex queries, DuckDB-based analytical read-only instances have the following advantages:

• High performance: Delivers two orders of magnitude better performance for complex queries compared to the InnoDB engine.

• High compatibility: 100% compatible with the MySQL protocol and data types, and highly compatible with SQL syntax and Data Definition Language (DDL).

• Efficient and stable data synchronization: Uses a native binlog replication channel for a more stable and efficient synchronization link. No additional data synchronization fees are charged.

• Resource isolation: DuckDB-based analytical read-only instances handle complex analytic queries, while primary and regular read-only instances handle transaction processing. This isolates resources for each workload, preventing them from affecting each other.

Scenarios

To create a DuckDB-based analytical read-only instance, the attached ApsaraDB RDS for MySQL primary instance must meet the following requirements:

• Major version: MySQL 8.0

• Series: High-availability Series

• Storage type: Premium ESSD or enterprise SSD (ESSD)

• Storage capacity: 10 GB to 16,000 GB

• The data archiving feature is disabled.

Billing

The billing for DuckDB-based analytical read-only instances is the same as for regular read-only instances. The cost is affected by factors such as the product series, instance type, storage type, and storage capacity. The actual cost is displayed on the purchase page.

Notes

• Instance creation time: When a DuckDB-based analytical read-only instance is created, the engines of all tables in the instance are automatically converted to the DuckDB engine. Therefore, it takes longer to create an analytic read-only instance than a regular read-only instance. The creation time depends on the data volume of the primary instance.

• Foreign key deletion: Foreign keys are automatically deleted during the creation of a DuckDB-based analytical read-only instance. DuckDB-based analytical read-only instances do not have foreign key constraints.

• Table schema limits: DuckDB-based analytical read-only instances support only tables that use the UTF8 or UTF8MB4 character set. Partitioned tables and tables without a business primary key are not supported. A business primary key is a defined primary key or a UNIQUE constraint on a non-null field.

  Note

  For unsupported tables, the engine remains the InnoDB engine in the DuckDB-based analytical read-only instance. You can run the following SQL statement to query the list of incompatible tables.

  SELECT table_schema, table_name, engine 
  FROM information_schema.tables 
  WHERE table_schema NOT IN ('mysql', 'sys') AND engine = 'InnoDB';

• Binlog subscription limits: DuckDB-based analytical read-only instances do not support enabling the binary logging feature. Therefore, you cannot subscribe to their binlogs. To subscribe to binlogs, select the primary instance.

• Compatibility limits: For more information, see Compatibility of DuckDB analytic instances.

Create a DuckDB-based analytical read-only instance

1. Log on to the ApsaraDB RDS console. In the top navigation bar, select a region. Then, click the ID of the target instance.

2. On the Basic Information page, go to the Instance Distribution section. Click Add to the right of DuckDB Analytic Instance.

   

3. Configure the DuckDB-based analytical read-only instance. The following table describes the main parameters.

   Parameter	Description
   Billing Method	Subscription and pay-as-you-go are supported. Serverless is not supported.
   Series	Only High-availability Series is supported.
   Product Type	Only Standard Edition is supported.
   Primary Zone	You can select only a zone in the same region as the primary instance.
   Deployment Method	Single-zone Deployment: The primary and secondary nodes of the analytic read-only instance are in the same zone. Multi-zone Deployment: The primary and secondary nodes of the analytic read-only instance are in different zones within the same region. This provides cross-zone disaster recovery at no extra charge. If you select this option, you must also configure the zone for the secondary node.
   Instance Type	Dedicated instance types are supported. For more information about the instance types, see DuckDB-based analytical read-only instance types.
   Database Proxy	After you enable the General-purpose database proxy (free of charge), the system can automatically distribute hybrid transactional and analytical processing (HTAP) requests. Online analytical processing (OLAP) query requests are routed to the DuckDB-based analytical read-only instance, and online transactional processing (OLTP) requests are routed to the primary instance or a regular read-only instance.
   Storage Capacity	The storage capacity of the analytic read-only instance must be at least half the storage capacity of the primary instance.

4. Click Next: Instance Configurations. Configure the network and resource group for the instance. The following table describes the main parameters.

   Parameter	Description
   VPC	The VPC is the same as that of the primary instance by default and cannot be changed.
   VSwitch of Primary Node	Use the default primary vSwitch or select one as needed.
   Resource Group	The resource group is the same as that of the primary instance by default and cannot be changed.

5. Click Next: Confirm Order.

6. Confirm the Parameter Settings, select a Quantity, click Confirm Order, and complete the payment.

   If you select the subscription billing method, you must also select a Subscription Duration. We recommend that you select Auto-renewal to prevent service interruptions that are caused by an overdue payment.

7. After a period of time, find the primary instance on the Instances page. Click the arrow to the left of the instance ID to view the DuckDB-based analytical read-only instance that is attached to the primary instance.

   

Connect to a DuckDB-based analytical read-only instance

References and API operations

• Compatibility of DuckDB analytic instances

• DuckDB-based analytical read-only instance types

• Automatic request distribution for HTAP

• ApsaraDB RDS for MySQL read-only instances

• Related API operations

  • Create a DuckDB-based analytical read-only instance by calling the CreateReadOnlyDBInstance operation.

  • Query the details of a specific DuckDB-based analytical read-only instance by calling the DescribeDBInstanceAttribute operation.

  • Query the basic information about all ApsaraDB RDS instances, including DuckDB-based analytical read-only instances, that belong to your account by calling the DescribeDBInstances operation.

Appendix: Performance test for a DuckDB-based analytical read-only instance

The following section uses the standard TPC-H benchmark test, which is used to evaluate the performance of complex database queries, to demonstrate the analytic query performance of a DuckDB-based analytical read-only instance.