TXSQL Internals@2018: Tencent MySQL Architecture and Optimization

This article presents a technical分享 from ACMUG Nanjing 2018 by Tencent's database kernel team expert engineer Wang Shaohua, focusing on TXSQL (Tencent MySQL) internals and how it supports full-scenario business operations.

CDB (Cloud Database) is Tencent Cloud's largest relational database service platform, providing native MySQL services for both internal business and Tencent Cloud customers. Originally focused on social and gaming scenarios, it now covers gaming, mobile internet, and finance industries. TXSQL serves as the core kernel of CDB, providing database engine and replication functionalities.

TXSQL Availability Features:

1. Asynchronous Table Deletion : When deleting large tables (e.g., 2GB files), the implementation renames the file to a temporary name, truncates it in fixed sizes (128MB) in the background, and deletes when small enough. This prevents IO spikes that could cause database unresponsiveness.

2. DDL NO_WAIT and WAIT Options : Added to ALTER TABLE, DROP TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, RENAME TABLE, CREATE INDEX, CREATE FULLTEXT INDEX, CREATE SPATIAL INDEX, and DROP INDEX statements. NO_WAIT returns failure immediately if MDL lock cannot be acquired; WAIT [n] waits for n seconds before failing.

3. MDL Information Schema View : Provides visibility into metadata lock usage and waiting information for system diagnostics.

TXSQL Optimization Features:

1. Slave Lock Splitting : The Lock_log mutex was split to reduce contention between SQL thread and IO thread, resulting in stable throughput under high load.

2. Slave IO Optimization : Events are cached and written together after receiving all events of a transaction, then synced to disk together, improving throughput by over 50%.

3. Parallel Replication : Beyond MySQL 5.6's database-based distribution, TXSQL implements table-based distribution for single-database scenarios, achieving performance comparable to logic clock-based distribution.

4. Query Optimization : Pagination and aggregation operations (SUM, COUNT) are pushed down to the storage engine, reducing server-engine interaction and data format conversion overhead.

5. InnoDB Read View Optimization : Deep optimization of transaction system including mutex and read view, achieving 2x write performance improvement.

TXSQL Enterprise Features:

1. Audit Functionality : Independent audit plugin writes records to buffer, flushed by a dedicated thread to disk. An agent reads and sends records to Tencent Cloud's CTSDB (time-series database). Performance impact as low as 5%.

2. Encryption : Integrated with Tencent Cloud's KMS and CAM through keyring_kms plugin, providing secure key management beyond MySQL's default keyring_file plugin.

TXSQL powers WeChat, QQ internally, and is externally used across gaming, e-commerce, logistics, finance, and retail industries.