Why AliSQL Could Become the Preferred Database Service of Large Enterprises

Image for post
Image for post

In many ways, MySQL has represented the rapid development of open-source databases around the world. Quickly following this, AliSQL was developed at Alibaba Group based on the history of relational database development, from the emergence of lightweight web 2.0 applications such as Wiki and WordPress around 2004, to the popularization of MySQL databases on a large scale for e-commerce and payment scenarios by Alibaba in 2010 in China, and then to the beginning of Alibaba Cloud ApsaraDB RDS for MySQL in 2012. AliSQL provided reliable relational database services for tens of millions of customers and has accumulated many requirements for internal and external functions, performance, and security. With these several years of experience, we at Alibaba have built a strong MySQL research and development team and created an enterprise-oriented AliSQL branch, with a quickly evolving and stable service for internal and external key business scenarios.

Let’s take a look at AliSQL’s enterprise scenario-oriented design and functions.

MySQL 8.0 Compatible

  • Atomic DDL: In MySQL 8.0, the frm file table structure is removed, and metadata information is stored in data dictionary tables. This ensures that DDL operations are processed or rolled back in a clean way, avoiding messy table deletion.
  • Instant Add Column: With the conversion of metadata into a dictionary, when appending additional columns to a table, without defaults, you can modify only the data dictionary without having to modify any data in the table. This allows you to add columns very efficiently.
  • Window Function: Window functions can be used to quickly create complex reports, which can then be used in many enterprise application scenarios.
  • Temp Engine: When executing a particularly complex SQL statement, a temporary space outside the memory is required. In earlier versions, files are dynamically created and deleted. However, in MySQL 8.0, using the centralized Temp Engine avoids the creation and deletion of files, which effectively mitigates the pressure on the file system.
  • Hash Join: Hash Join is implemented in MySQL 8.0 to accelerate the SQL performance of joining multiple tables, specifically three tables. Due to the complicated logic in enterprise scenarios, multi-table joining becomes a norm, making this feature an enterprise-level one.

In 2019, we were able to quickly launch an AliSQL edition that was based on MySQL 8.0, coming equipped with these features.

Dynamic Thread Pool

  • Increasing the number of database connections with limited resources.
  • Supporting greater short connection capabilities.
  • Improving resource usage efficiency and performance with limited resources.
  • Implementing concurrent control or isolation to ensure the stability of core business logic.

Dynamic Thread Pool Can Effectively Solve Industry Challenges

Since each connection does not correspond to a fixed thread, and no thread operation is performed when a new connection is created, the cost of creating a new connection has been greatly reduced, providing greater and more stable connection creation capabilities.

By controlling the number of threads under the condition of limited resources, the number of thread switchover attempts can be reduced, the SYS CPU usage is dramatically reduced, and the CPU resource usage is improved. In the sysbench point select test with high concurrency, Dynamic Thread Pool can improve performance by about 10%. In the case of low concurrency, Dynamic Thread Pool can dynamically switch back to the dedicated mode. This means that each connection is mapped to a dedicated Worker thread to ensure better performance under high concurrency.

A database involves read and write operations. Meanwhile, SQL operations can be divided into simple operations and complex operations. To prevent interference, a Dynamic Thread Pool has multiple queues in design. In this design, SQL operations are classified by SQL operation cost, and each of these operations is sorted into a dedicated queue. In the meantime, concurrent control is implemented in the queues to prevent different types of operations from intervening with each other. For example, in the case that the core business logic involves simple queries, and the preceding feature ensures that complex SQL statements without the review of new applications do not affect the core logic.

Dynamic Thread Pool can be dynamically started and stopped without restarting database instances. This is a major technological innovation and breakthrough of AliSQL Dynamic Thread Pool.

Dynamic Thread Pool is available in RDS 5.6, 5.7, and 8.0, and is enabled by default. It is applicable to all users and customers. The TPC-C model is closest to the core business scenarios of large enterprises. In TPC-C tests with 1,024 or 2,048 concurrent clients, Dynamic Thread Pool can increase TpmC by 50% to 100%. It also reduces CPU resource consumption by 10% to 20%, creating tangible technological benefits for all Alibaba Cloud customers.

Index Mutex Tuning

Image for post
Image for post

This optimization has been simultaneously applied to RDS 5.6, 5.7, and 8.0.

TDE & SM4

Performance Insight

  • table_stats: records the number of rows that are added, deleted, changed, and queried for each table. This allows you to quickly understand the data access behavior in the existing system, determine the core tables of the business, and identify which tables are suitable for caching.
  • index_stats: records the used index entries and their utilization frequencies to help you quickly understand index usage, determine redundant index entries, and identify core index entries.
  • Performance Point: records the fine-grained performance data of each SQL statement, including the time consumed by the SQL statement, as well as its consumption time, execution time, MDL lockout time at the server layer, and transaction lock time, SQL I/O information, including the data read, data write, log generation, memory access, physical read and write volume, and I/O time that result from this SQL statement, and the number and times of kernel-layer locks, including the Mutex and RW locks. With this extension, it is easy to identify SQL statements that need optimization. If you have certain source-code programming capabilities, you can also analyze and improve the MySQL lock mechanism.
  • Performance Agent: uses the built-in daemon to collect key performance data of different dimension, such as CPU, memory, network, disk I/O, server, InnoDB, Linux kernel, and file system at the same time point. It collects the data once every second and records it in the background in formatted text to facilitate subsequent performance analysis. It is also provided as a memory table for access to allow you to use Grafana and other tools to produce the performance curve of the RDS database.
Image for post
Image for post
  • IO_STATISTICS: records the total amount and time of different I/O operations at the instance level. By default, a set of data, which is configurable, is generated every 2 seconds. The data can be directly presented by using Grafana or other tools.

After a careful analysis of the native Performance Schema, these additional performance-related data items are added to AliSQL to accurately reflect the health of the system and facilitate the troubleshooting of performance problems. This also helps you perform SQL performance analysis, and allows third parties to provide better performance optimization services based on the performance data.

Async File Purge

Slow Log Rotate

SQL Outline

  • The database version is upgraded, the optimizer behavior changes, and the SQL computing plan may become better or worse.
  • When a new index entry is created in a DDL operation, the original SQL execution plan may change.
  • After statistical information is changed, re-collected, or manually configured, the SQL execution plan may change.

SQL statements for core businesses need to be solidified by using SQL Outline to avoid the deterioration of SQL execution plans.

AliSQL also provides APIs for SQL Hint editing. That is, you can manually add a Hint to an SQL statement for transparent SQL optimization, allowing you to optimize SQL statements without modifying application code. Additionally, this feature is integrated with other optimization functions of AliSQL, such as Statement Queue and Query Cache, which is currently under development. You can specify a Hint in SQL Outline to enable the concurrent control of Statement Queue. Alternatively, you can enable Query Cache to implement SQL optimization that is transparent to applications.

Binlog XA Tuning

DDL Recycle Bin

Original Source:

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store