ETL Writeback Acceleration

TDSQL-C for MySQL can use the read-only analysis engine to accelerate data queries and write the results back to the read-write instance. By using this feature, you can use columnar storage in the read-only analysis engine to accelerate the SELECT query in an INSERT…SELECT… statement, transmit the data back to the read-write instance through the internal network, and write the data into the target table. For a detailed feature description, see ETL Writeback Acceleration.
Application Scenario
Note:
The read-only analysis engine adopts the asynchronous replication mode. Therefore, a certain time delay may exist between the query results and those in the read-write instance when there is a latency in the read-only analysis engine. This feature applies only to scenarios that are not sensitive to data latency.
Currently, only INSERT...SELECT... statements can be accelerated. UPDATE...SELECT..., DELETE...SELECT..., and other statements cannot be accelerated.
This feature is recommended in scenarios where the query conditions are complex, the SQL statement execution time is long, and the data volume of the query result set is small. In such scenarios, this feature can significantly improve performance because the read-only analysis engine will accelerate SELECT queries.
This feature does not bring performance benefits in all scenarios. Instead, the performance may deteriorate in some scenarios. For example:
When the SELECT query in an INSERT…SELECT… statement is relatively simple, reading data from the read-only analysis engine and writing the data back to the read-write instance will incur additional network overhead. The strengths are not obvious compared with the method of directly reading data from the read-write instance.
When the result set of the SELECT query in an INSERT…SELECT… statement contains a large volume of data, the main performance bottleneck lies in the process of transmitting the result set over the network and writing it to the read-write instance. In this scenario, this feature cannot be used to improve performance.
Prerequisites
Both the read-only analysis engine instance and the read-write instance are running normally.
The user should have the INSERT permission for the corresponding objects.
Only the execution of INSERT…SELECT… can be accelerated.
Feature Parameter Description
Parameter Name
Description
Default Value
Parameter Value
libra_etl_to_rw
Controls whether to enable the writeback feature.
off
on: Enable the writeback feature. It means that writeback will be performed.
off: Disable the writeback feature. When the value is off, an error of insufficient permission will be reported during INSERT…SELECT… execution.
libra_concurrent_etl
Controls whether to enable concurrent writeback to the read-write instance.
off
on: Enable concurrent writeback. Concurrent writeback can improve the efficiency of result writeback, but transaction consistency among multiple threads cannot be guaranteed.
off: Disable concurrent writeback. When the value is off, data writeback to the read-write instance will be completed in one transaction.
libra_etl_concurrency
Controls the number of concurrent threads for data writeback to the read-write instance. This parameter takes effect only when libra_concurrent_etl is set to on.
1
Value range: [1–number of read-write instance CPU cores]. The value should be an integer, and the maximum value is the number of CPU cores of the read-write instance.
Note:
The writeback speed is high if multiple concurrent threads are used. However, a large number of concurrent threads can damage the performance of the read-write instance because it cannot withstand the write load.
Example
1. Use a client tool to log in to the read-only analysis engine. For example, you can use the MySQL client to log in to the read-only analysis engine with the database account and password.
2. Execute the following SQL statements to use the feature of ETL writeback acceleration (writing data back to the read-write instance from the read-only analysis engine).
Set parameters in the session:
set libra_etl_to_rw=on;
set libra_concurrent_etl=on;
set libra_etl_concurrency=4;
INSERT INTO database1.table1 select a.t1,b.t2,a.t2,b.t3 from a,b where a.t1=b.t1 and a.t5='x';
set libra_concurrent_etl=off;
set libra_etl_to_rw=off;
Add a hint with parameters to the SQL statement:
INSERT /*+ SET_VAR(libra_etl_to_rw=ON) SET_VAR(libra_concurrent_etl=ON) SET_VAR(libra_etl_concurrency=4)*/ INTO database1.table1 select a.t1,b.t2,a.t2,b.t3 from a,b where a.t1=b.t1 and a.t5='x';
Note:
If you add a hint with parameters in the MySQL client, you need to add the -c parameter when logging in to the read-only analysis engine in the MySQL client. Otherwise, the hint will not take effect.
Performance Comparison
Test Environment
Read-write instance: 16-core 64 GB.
Read-only analysis engine: 16-core 64 GB.
Test Data
Use the TPC-H dataset with 10 GB of data.
Complex SQL Statement Test and Result
set libra_etl_to_rw=on;
set libra_concurrent_etl=off;
set libra_etl_concurrency=1;
﻿
create table t1 (
    supp_nation VARCHAR(50),
    cust_nation VARCHAR(50),
    l_year INT,
    revenue DECIMAL
);
﻿
INSERT INTO t1 SELECT
supp_nation, cust_nation, l_year, sum(volume) as revenue 
from (select n1.n_name as supp_nation, n2.n_name as cust_nation, 
             extract(year from l_shipdate) as l_year, 
             l_extendedprice * (1 - l_discount) as volume 
      from supplier, lineitem, orders, customer, nation n1, nation n2 
      where s_suppkey = l_suppkey 
        and o_orderkey = l_orderkey 
        and c_custkey = o_custkey 
        and s_nationkey = n1.n_nationkey 
        and c_nationkey = n2.n_nationkey 
        and ((n1.n_name = 'JAPAN' and n2.n_name = 'INDIA') 
              or (n1.n_name = 'INDIA' and n2.n_name = 'JAPAN')) 
        and l_shipdate between '1995-01-01' and '1996-12-31') as shipping 
group by supp_nation, cust_nation, l_year 
order by supp_nation, cust_nation, l_year; 
When the query result contains 4 rows of data, the test results are as follows (unit: seconds).
Duration of SELECT Execution on Read-Only Analysis Engine
Writeback Feature Disabled
Duration of INSERT...SELECT... Execution on Read-Write Instance
Writeback Feature Enabled
Duration of INSERT...SELECT... Execution on Read-Only Analysis Engine
0.57
232.77
0.61
set libra_etl_to_rw=on;
set libra_concurrent_etl=off;
set libra_etl_concurrency=1;
﻿
CREATE TABLE t2
 (
    p_brand VARCHAR(10),
    p_type VARCHAR(25),
    p_size INTEGER,
    supplier_cnt INTEGER
);
﻿
INSERT INTO t2 SELECT
    p_brand,
    p_type,
    p_size,
    count(distinct ps_suppkey) as supplier_cnt
from
    partsupp,
    part
where
    p_partkey = ps_partkey
    and p_brand <> 'Brand#45'
    and p_type not like 'MEDIUM POLISHED%'
    and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
    and ps_suppkey not in (
        select
            s_suppkey
        from
            supplier
        where
            s_comment like '%Customer%Complaints%'
    )
group by
    p_brand,
    p_type,
    p_size
order by
    supplier_cnt desc,
    p_brand,
    p_type,
    p_size;
When the query result contains 27,840 rows of data, the test results are as follows (unit: seconds).
Duration of SELECT Execution on Read-Only Analysis Engine
Writeback Feature Disabled
Duration of INSERT...SELECT... Execution on Read-Write Instance
Writeback Feature Enabled
Duration of INSERT...SELECT... Execution on Read-Only Analysis Engine
0.15
8.77
0.61
Simple SQL Statement Test and Result
set libra_etl_to_rw=on;
set libra_concurrent_etl=on;
set libra_etl_concurrency=8;
﻿
CREATE TABLE IF NOT EXISTS lineitem ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL , primary key(L_ORDERKEY, L_LINENUMBER));
﻿
INSERT INTO lineitem_t SELECT
    *
from
    lineitem;
When the query result contains 59,986,051 rows of data, the test results are as follows (unit: seconds).
Number of Concurrent Threads
Writeback Feature Disabled
Duration of INSERT...SELECT... Execution on Read-Write Instance
Writeback Feature Enabled
Duration of INSERT...SELECT... Execution on Read-Only Analysis Engine
Concurrent writeback disabled
622.7
1441.02
8 concurrent threads
﻿
259.20
16 concurrent threads
﻿
181.79
32 concurrent threads
﻿
185.64

Was this page helpful?

You can also Contact Sales or Submit a Ticket for help.

Yes

Parameter Name	Description	Default Value	Parameter Value
libra_etl_to_rw	Controls whether to enable the writeback feature.	off	on: Enable the writeback feature. It means that writeback will be performed. off: Disable the writeback feature. When the value is off, an error of insufficient permission will be reported during `INSERT…SELECT…` execution.
libra_concurrent_etl	Controls whether to enable concurrent writeback to the read-write instance.	off	on: Enable concurrent writeback. Concurrent writeback can improve the efficiency of result writeback, but transaction consistency among multiple threads cannot be guaranteed. off: Disable concurrent writeback. When the value is off, data writeback to the read-write instance will be completed in one transaction.
libra_etl_concurrency	Controls the number of concurrent threads for data writeback to the read-write instance. This parameter takes effect only when libra_concurrent_etl is set to on.	1	Value range: [1–number of read-write instance CPU cores]. The value should be an integer, and the maximum value is the number of CPU cores of the read-write instance. Note: The writeback speed is high if multiple concurrent threads are used. However, a large number of concurrent threads can damage the performance of the read-write instance because it cannot withstand the write load.

Duration of SELECT Execution on Read-Only Analysis Engine	Writeback Feature Disabled Duration of INSERT...SELECT... Execution on Read-Write Instance	Writeback Feature Enabled Duration of INSERT...SELECT... Execution on Read-Only Analysis Engine
0.57	232.77	0.61

Number of Concurrent Threads	Writeback Feature Disabled Duration of INSERT...SELECT... Execution on Read-Write Instance	Writeback Feature Enabled Duration of INSERT...SELECT... Execution on Read-Only Analysis Engine
Concurrent writeback disabled	622.7	1441.02
8 concurrent threads			259.20
16 concurrent threads			181.79
32 concurrent threads			185.64

tencent cloud

Sign Up

Log in

Compute

Microservice

Data Migration

Database SaaS Tool

Data Security

Application Security

Big Data

Tencent Big Model

Internet of Things

Stream Services

Cloud Real-time Rendering

Management and Audit Tools

Edge Computing

Serverless

Relational Database

Networking

Business Security

Domains & Websites

Face Recognition

AI Platform Service

Middleware

Media On-Demand

Game Services

Developer Tools

Container

Essential Storage Service

Enterprise Distributed DBMS

CDN and Acceleration

Security Services

Enterprise Applications

Voice Technology

Natural Language Processing

Communication

Media Process Services

Education Sevices

Monitor and Operation

Distributed cloud

Data Process and Analysis

NoSQL Database

Network Security

Cloud Security

Office Collaboration

Image Creation

Optical Character Recognition

Interactive Video Services

Media SDK

Cloud Resource Management

More

Application Scenario

Prerequisites

Feature Parameter Description

Example

Performance Comparison

Test Environment

Test Data

Complex SQL Statement Test and Result

Simple SQL Statement Test and Result