Best Practices for TaurusDB Large Table Optimization

Scenarios

In database O&M, large tables usually refer to tables that contain tens of millions of rows of data and occupy hundreds of GB or even TB of space. As your workloads grow, large tables pose challenges in three aspects:

• Storage bottleneck: The disk capacity of a single node is insufficient, requiring frequent capacity expansion or data sharding, which has a significant impact on services.
• Performance deterioration: Index maintenance is costly, write performance deteriorates, and query response becomes slow, which has a significant impact on services.
• Difficult O&M: DDL operations are extremely time-consuming. Adding fields or indexes may block services for several hours, which has a significant impact on services.

When processing large tables, the traditional MySQL Community Edition is limited by its standalone architecture design and often requires sharding to distribute the load. However, this brings new challenges: The application layer needs to refactor SQL statements, introduce middleware, and maintain sharding routing rules, significantly increasing the overall architecture complexity.

TaurusDB uses a decoupled storage and compute architecture and supports TB-level data storage in a single table, effectively addressing storage and performance bottlenecks in large table scenarios while avoiding the increased complexity caused by application layer refactoring. This section will detail how to efficiently manage and optimize large tables without the need for data sharding.

Storage Performance Optimization

When the service volume exceeds expectations, with single-table data volume rapidly swelling from millions of rows to billions of rows, local disks frequently trigger capacity alarms. In this scenario, we have two solutions. One is vertical scaling, which involves replacing servers with larger-capacity ones, but this method is costly and has a limit on how much capacity can be added. The other is database and table sharding, which requires refactoring application code and introducing sharding middleware, but this method has high development and O&M costs.

TaurusDB uses a compute node + distributed storage cluster architecture:

• Compute layer: It is responsible for SQL parsing, execution plan generation, and transaction processing. It can be flexibly scaled based on the load.
• Storage layer: Built on Huawei Data Function Virtualization (DFV) storage, it provides distributed, strongly consistent, and high-performance storage. By pooling storage cluster deployments, it optimizes resource utilization and underpins a data-centric, full-stack data service solution.

  Table 1 Architecture comparison

  Feature	Traditional Standalone MySQL	TaurusDB
  Max. storage capacity	Limited by the disk capacity of a single node (usually several TB)	128 TB, scalable on demand
  Scaling method	Hardware replacement or data migration during downtime	Online automatic scaling, transparent to workloads
  Cost model	Fixed capacity pre-purchased, prone to waste	Billed based on actual usage

Compute Performance Optimization

In the InnoDB storage engine, redo logs are a key mechanism for ensuring transaction durability. Each data modification generates redo logs, and these logs must be persisted to disk before a transaction can be committed. When large tables encounter high-concurrency writes (such as batch inserts and frequent UPDATE operations), the write pressure on redo logs increases sharply.

• Traditional method: single-thread sequential writes, making the log buffer prone to bottlenecks
• Performance impact: increased log write latency, slower transaction commits, and restricted overall throughput

The multi-thread parallel write mechanism for redo logs distributes log write tasks across multiple threads for parallel processing, fully utilizing the high throughput capability of the underlying distributed storage.

DDL Performance Optimization

• Parallel DDL

  Traditional DDL is designed based on a single core and traditional disks. It takes a long time to perform DDL operations on large tables and the latency is too high. For example, when creating secondary indexes, DDL operations with high latency block subsequent DML queries that depend on new indexes.

  TaurusDB supports parallel index creation. When database hardware resources are idle, you can use parallel index creation to accelerate DDL execution. This prevents subsequent DML operations from being blocked and shortens the DDL operation window.

  Table 3 Parallel DDL performance data

  Data Scale	Traditional Single-Thread DDL	Parallel DDL (32 Threads)	Comparison
  100 million rows	About 30 minutes	About 1.5 minutes	20x faster
  1 billion rows	About 5 hours	About 15 minutes	20x faster
  6 billion rows (5 TB)	About 15 hours	About 1 hour	15x faster

• Adding columns in seconds with DDL

  TaurusDB supports ALGORITHM=INSTANT to quickly add columns, preventing lock waiting from affecting workloads or SQL statement execution timeout.

  The execution duration is irrelevant to the table size. The time required for 100 million rows is the same as that for 10 billion rows. No extra disk space is occupied (no temporary table is required), and DML operations are not blocked.

  For details about the usage and constraints, see Adding Columns in Seconds with DDL.

• Quickly truncating or dropping large tables

  When you run the TRUNCATE or DROP statement, MySQL Community Edition needs to remove all data pages of the table from the LRU linked list of the buffer pool and remove dirty pages from the flush list to release the tablespace file.

  When the buffer pool is large (for example, 256 GB or 512 GB), scanning the entire buffer pool is time-consuming. In addition, dropping a large table may cause the instance to freeze for dozens of seconds or even minutes, affecting the query response speed.

  TaurusDB supports fast Truncate/Drop, which is enabled by default using the innodb_rds_fast_truncate_or_drop_tablespace parameter. This function deeply optimizes the buffer pool page management policy. Instead of using the traditional global scanning mechanism, it accurately locates pages to be cleared based on the tablespace ID and performs asynchronous, deferred cleaning. This greatly reduces the impact of TRUNCATE or DROP operations on the buffer pool.

• DDL fast timeout

  In MySQL Community Edition, all DDL operations require an MDL lock. If a DDL operation keeps waiting for such a lock, subsequent DML operations will be blocked.

  TaurusDB allows you to set an MDL lock wait timeout for ALTER TABLE, CREATE INDEX, and DROP INDEX operations. If a DDL operation cannot acquire the MDL lock within the specified time, it will be automatically canceled to prevent subsequent DML operations from being blocked for a long time.

  For details, see DDL Fast Timeout.

• Non-blocking DDL

  When you execute a DDL statement on a table with uncommitted long-running transactions or large queries, the DDL statement keeps waiting for an MDL or MDL-X lock. TaurusDB gives MDL or MDL-X locks the highest priority. When a DDL statement is waiting for an MDL or MDL-X lock, new transaction requests are queued behind the DDL statement. As a result, all new transactions on the table are blocked. This can lead to connection congestion and even cause the entire service system to break down.

  Non-blocking DDL allows new transactions to enter the table even if the MDL or MDL-X lock cannot be acquired, ensuring the stability of the entire service system.

  You can also use the open-source tool gh-ost to perform online DDL operations. This tool prevents long table locks caused by traditional DDL operations and allows concurrent read and write operations on the table, maximizing service continuity and availability.