Abstract:

DRDS (ali cloud distributed relational database services, www.aliyun.com/product/drd.) Version 5.3 was released on April 30, a major annual update. It mainly brings the following features:

  1. Performance improvement. In most scenarios (equivalent queries on split keys, read/write separation, etc.), same-spec throughput (maximum QPS) can be improved to 300%.
  2. Native distributed transactions. Distributed transactions can be performed with no additional cost or subscription and without dependence on third-party components. Flexible transaction and XA are provided.
  3. The Outline. You can change the execution plan of the SQL by creating an Outline, such as specifying indexes, primary or secondary libraries, and so on, without changing the program.
  4. Clear SQL boundary documents. Within SQL boundaries, a large number of random tests were carried out to ensure the stability and reliability of the function.
  5. More powerful distributed query optimizer. Ensure that distributed SQL execution costs are minimized.
  6. Concise and readable execution plan. Provides a new execution plan display format, which makes it very easy to see the execution strategy of SQL.

1. The performance

DRDS 5.3, which uses Plan Cache, coroutine, FastSQL and other technologies, significantly improves throughput, with the maximum QPS increased by 300% under the same specification. For example, for previous versions, the 8C16G DRDS provided up to 2W/s of QPS; For DRDS 5.3, 8C16G DRDS can provide up to 6W+/s QPS.

Test scenario:

2. The test tool is Sysbench 3. 4. Test SQL: equivalent query on split key of single table

SELECT * FROM t1 WHERE partition_key=?Copy the code


5. Continue to increase the concurrency until the DRDS CPU approaches 100% and THE RT is around 5ms

Plan Cache

DRDS 5.3 introduced Plan Cache, which greatly reduced the cost of SQL parsing and query optimization. In DRDS 5.3, there are multiple levels of Plan Cache for different types of SQL. The SQL that matches the first-level Plan Cache has the highest performance. No matter what the parameter value is, the SQL that can be pushed down to a single shard will match the level-1 Plan Cache.

1. Equivalent query on split key of single table, for example:

SELECT * FROM t1 WHERE partition_key=?Copy the code


SQL > select * from table where id = 1; SQL > select * from table where id = 1;

SELECT * FROM t1 JOIN t2 ON t1.partition_key = t2.partition_key WHERE t1.partition_key=?Copy the code


3. Split key equivalent association subquery, and the inner table or outer surface of the split key with equivalent conditions, for example:

SELECT * FROM t1 WHERE EXSITS (SELECT 1 FROM t2 WHERE t1.partition_key = t2.partition_key) AND t1.partition_key=?Copy the code


In the application, more SQL that can match level-1 Plan Cache can be used to improve the system capacity.

coroutines

DRDS 5.3 uses AliJDK’s Wisp coroutine. When the business logic is the same, the system capacity is increased by about 30% using coroutine model compared with using thread model.

Faster Parser: FastSQL

Parser part of DRDS 5.3 changed from Druid (github.com/alibaba/dru…) FastSQL is stripped out. Compared with the old Parser, FastSQL is tens to hundreds of times faster than antLR, JavACC and other automatically generated Parser in SQL parsing, and brings twice the performance improvement compared with the old DRDS Parser. FastSQL will be open source soon.

2. native distributed transactions

DRDS 5.3 provides native distributed transaction functionality with the following features:

  1. Two transaction schemes, flexible transaction and XA transaction, are provided for users to choose in different scenarios.
  2. Independent of any third-party components, capabilities are integrated in DRDS Server, and proprietary clouds do not require additional resources for deployment.
  3. In the case of no hot spot, the performance is linearly scalable and there is no single point bottleneck.
  4. Instances purchased on the public cloud can be used immediately at no additional cost without additional opening.

DRDS 5.3 provides flexible transactions and XA transactions. Generally, XA transactions are recommended when the DRDS backend MySQL is 5.7 or later.

Flexible transaction

The distributed Transactions that DRDS 5.3 provides that ultimately execute in a consistent manner are called Flexible Transactions.

Flexible transactions give up isolation and reduce the granularity of locks in transactions, so that applications can make better use of the concurrency performance of databases and achieve linear expansion of throughput. The asynchronous execution mode can better adapt to the distributed environment and ensure service Availability in the case of network jitter and node failure.

In DRDS 5.3, a single line of code is required to enable flexible transactions:

SET drds_transaction_policy = 'flexible';

SHOW VARIABLES LIKE 'drds_transaction_policy'; 
+-------------------------+----------+
| VARIABLE_NAME           | VALUE    |
+-------------------------+----------+
| drds_transaction_policy | FLEXIBLE |
+-------------------------+----------+
1 row in set (0.07 sec)Copy the code


In addition, DRDS flexible transactions are used in exactly the same way as normal transactions: the application starts a flexible transaction with SET autoCOMMIT = 0 and SET DRDS_transaction_policy = ‘flexible’. The SQL statement of the transaction is then executed in the same session – finally, when the application commits or rollback, DRDS guarantees atomicity of the EXECUTION of these SQL statements: all successful or all failed.

XA transaction

DRDS 5.3 also supports XA transactions, providing strong consistency over flexible transactions. Due to the limitations of the MySQL XA implementation mechanism, we require that XA transactions be enabled only when the DRDS backend is MySQL 5.7 or higher.

SET drds_transaction_policy = 'XA';

SHOW VARIABLES LIKE 'drds_transaction_policy'; 
+-------------------------+-------+
| VARIABLE_NAME           | VALUE |
+-------------------------+-------+
| drds_transaction_policy | XA    |
+-------------------------+-------+
1 row in set (0.07 sec)Copy the code


DRDS XA transactions use the two-phase Commit Protocol (XA Protocol) to protect the commit and rollback of subtransactions, eliminating the asynchronous rollback problem of flexible transactions. Because XA Protocol is always locked during commit and rollback phases, dirty reads and overwrites before the end of transactions are avoided, but performance is greatly affected.

3. Outline

DRDS 5.3 provides an Outline mechanism that allows users to customize the behavior of a particular type of SQL without modifying the program or SQL. In short, Outline can dynamically replace one type of source SQL with another target SQL at execution time, with hints in the target SQL.

Some typical application scenarios:

  • useSLAVE HINTRoute specific SQL to read-only instances for execution:
CREATE OUTLINE O1 ON SELECT * FROM T1 WHERE ID=? TO SELECT /*+TDDL:SLAVE()*/ * FROM T1 WHERE ID=?    Copy the code


  • Use MySQL nativeFORCE INDEXSpecify the index to select for a particular SQL:
CREATE OUTLINE O2 ON SELECT * FROM T1 WHERE ID=? TO SELECT * FROM T1 FORCE INDEX(index_xxx) WHERE ID=?Copy the code


  • HINT uses DRDS to route a particular SQL to the specified shard:
CREATE OUTLINE O3 ON SELECT * FROM T1 WHERE ID=? TO SELECT /*+TDDL:node('0')*/ * FROM T1 WHERE ID=?Copy the code


  • Outline in DRDS can match parameterized SQL as well as SQL with specific parameters. For example, for SQL:
SELECT * FROM T1 WHERE ID=?Copy the code


If the ID is 1, the read-only instance needs to be executed. When ID is set to other values and needs to be executed on the main instance, the following two Outlines can be created:

CREATE OUTLINE O1 ON SELECT * FROM T1 WHERE ID=1 TO SELECT /*+TDDL:SLAVE()*/ * FROM T1 WHERE ID=1; CREATE OUTLINE O2 ON SELECT * FROM T1 WHERE ID=? TO SELECT /*+TDDL:MASTER()*/ * FROM T1 WHERE ID=? ;Copy the code


DRDS will match Outline with specific parameters first.

DRDS Outline: help.aliyun.com/document_de… DRDS Hint: help.aliyun.com/document_de…

4. Support SQL

In terms of SQL compatibility, the biggest feature of DRDS 5.3 is the clarity of SQL boundaries, that is, the ability to specify which SQL is supported and which is not.

DRDS 5.3 SQL Boundary Documentation: help.aliyun.com/document_de… .

Some important SQL types:

  1. Subqueries are supported and Derived Tables are not supported. For more subqueries, see help.aliyun.com/document_de… .
  2. STRAIGHT_JOIN support for distributed joins (no split key required, no JOIN on a split key required). STRAIGHT_JOIN and NATURAL JOIN were not supported for now.
  3. Supports most MySQL functions, except full-text search, XML, space analysis, and JSON functions.
  4. The UPDATE/DELETE statement supports only single table operations. The UPDATE/DELETE statement does not support JOIN and subquery operations.
  5. Aggregation function support COUNT/SUM/MAX/MIN/AVG, GROUP BY does not require FULL_GROUP_BY (dev.mysql.com/doc/refman/…). .
  6. Supports logical SQLKILL 与 SHOW PROCESSLIST:Help.aliyun.com/document_de….
  7. supportCREATE USERCreate more users and useGRANTStatement to authorize user permissions:Help.aliyun.com/document_de…L.
  8. Supports PREPARE, multi-statement, and compression protocols.

5. Optimizer and execution plan

DRDS 5.3 provides rich distributed SQL optimization strategies. Some important ones are:

  1. The optimization of Filter pull-up, push-down and derivation ensures that DRDS can accurately identify the parts of SQL that can be pushed down, which can greatly improve the performance of JOIN and subquery and avoid the performance loss caused by the failure to push down when it should be able to push down.
  2. Semi-join optimization of subqueries. In DRDS, sub-queries are modified to semi-Join for optimization, so that they can reuse a large number of optimization strategies for joins to improve performance and functional stability.
  3. Provides a series of hints that allow you to adjust any node of the execution plan, combined with the Outline mechanism, to optimize SQL performance without changing it.
  4. For different scenarios, optimize sorting and Limit to ensure that as many sorts and limits can be pushed down to storage nodes to ensure the minimum amount of data transferred.

DRDS 5.3 has designed a new execution plan display format with the following features compared to the previous version:

  1. Shrink the display of shards so that execution plans are not bloated with multiple shards.
  2. The execution plan contains a complete execution policy, and there is no ambiguity.
  3. The execution plan uses the semantics of standard operators, making it easy to apply standard database knowledge to query optimization of DRDS.
  4. Execution plans will include both distributed execution plans and execution plans on shards (this feature will be available in June).
  5. Provides the Optimizer Tracing function, which shows the optimization process step by step to facilitate SQL tuning.

  6. Through the execution plan, it can be clearly judged that:

    • On which shards SQL needs to be executed, and whether it needs to be executed across shards
    • Whether operations such as JOIN, subquery, aggregate, and sort can be pushed down
    • What are the algorithms used for JOIN, sort, and so on

For example, an execution plan for the following SQL:

mysql> explain SELECT count(*), name FROM drds GROUP BY name ORDER BY count(*); +----------------------------------------------------------------------------------------------------------------------- ------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------- ------------------------------------+ | Project(count(*)="count(*)", name="name")                                                                                                                 |
|   MemSort(sort="count(*) ASC")                                                                                                                            |
|     Aggregate(group="name", count(*)="SUM(count(*))")                                                                                                     |
|       MergeSort(sort="name ASC")                                                                                                                          |
|         LogicalView(tables="[00-03].drds", shardCount=4, sql="SELECT `name`, COUNT(*) AS `count(*)` FROM `drds` GROUP BY `name` ORDER BY `name`") | +----------------------------------------------------------------------------------------------------------------------- ------------------------------------+ 5 rowsin set (0.13 sec)Copy the code


From this execution plan, we can get the following information:

  1. Physical SQL needs to be executed on 4 shards from 00-03 (LogicalViewOperator) : SELECTname, COUNT(*) AS count(*) FROM drds GROUP BY name ORDER BY name.
  2. The Group By operation is implemented based on sorting and requires thenameSort. Since the Order By operation has been completed on each shard, the distributed layer needs to merge and sort the data of each shard (MergeSortOperator).
  3. SUM (*); SUM (*); SUM (*);AggregateOperator).
  4. Use memory sort to sort count(*) output from the Aggregate node (MemSortOperator).
  5. The final result set outputs the count(*) and name columns (ProjectOperator).

For more information on the DRDS 5.3 implementation plan, check out the following articles.

What’s NEXT

At the end of June, DRDS will release 5.3.2, which will provide the following features:

  1. A DRDS read-only instance with computing power. The highest level of complex SQL execution that can be provided at READ COMMITTED levels (such as joins of tens of millions of tables) can be performed directly on either the RDS master instance or read-only instance, and the response time can scale nearly linearly as the size increases.
  2. Recycle bin. You can flash back the DROP TABLE operation, which helps you quickly recover data in the case of accidentally deleted tables.
  3. Transaction-based broadcast table writes. The broadcast table is no longer dependent on any third party components and can be created and used by itself.
  4. Globally unique primary key services are still guaranteed across instances, rooms, and cells.

The original link