Xiaohongshu's MySQL Kernel Optimizations: Merge Seckill, Thread Pool & Nonblocking DDL
Xiaohongshu details four MySQL kernel optimizations—merge seckill for hotspot row updates, thread pool for resource isolation, DDL improvements including second-level column addition and nonblocking DDL, and flashback for historical data recovery—addressing high concurrency, reliability, agile schema changes, and burst traffic stability.
Background and Goals
Xiaohongshu's storage and database ecosystem covers MySQL, RedKV, Redis, ROS, with DMS and RedDTS providing unified control and data transmission. MySQL serves as the core relational database for online transaction processing, supporting business demands for consistency, stability, and access performance. As business scale grows, scenarios such as flash sales, hotspot updates, frequent schema changes, and large transactions demand higher MySQL performance and stability. Xiaohongshu addresses these pain points through kernel-level optimizations in concurrency control, DDL execution, transaction processing, and resource management to improve throughput, reduce execution overhead, and minimize impact on online services.
The most representative requirements concentrate in four areas:
Hotspot high concurrency: Improve hotspot row update efficiency. Scenarios like popular product flash sales, hot note interactions, and livestream commerce generate concentrated access and updates in short periods. Lock contention on hotspot rows creates throughput bottlenecks, requiring kernel-level concurrency optimization to support short-term burst traffic.
Core data reliability: Meet RPO=0 requirements. Financial transactions demand that committed data is not lost during failover, imposing higher requirements on replication mechanisms, persistence guarantees, and high availability.
Business agile delivery: Faster DDL changes with less impact. New business launches often require adding columns or adjusting table structures. Traditional DDL on large tables takes a long time and may block online requests due to lock waits. Xiaohongshu continuously optimizes second-level column addition, Fast DDL, and Nonblocking DDL to shorten change time and reduce read/write blocking.
Burst traffic stability: Reduce performance jitter under high load. Traffic spikes amplify lock contention, resource contention, and request queuing, leading to increased latency or service unavailability. Beyond peak throughput improvements, key paths and resource management are optimized to keep database response more predictable under high load.
RedSQL Kernel Optimizations
2.1 Merge Seckill (合并秒杀)
Open-source MySQL hotspot row update bottlenecks lie in deadlock detection, row lock holding, redo log updates, and binlog updates. Queue seckill reduced deadlock detection overhead and prevented performance degradation but could not further improve performance due to serialized updates and row lock mutual exclusion. Merge seckill goes further by merging multiple transactions' SQL into a single transaction commit, changing MySQL's original transaction execution model and requiring coordinated modifications in the transaction system, lock system, and binlog system.
Key optimizations:
Cache visibility and concurrency consistency: Introduces a global shared cache so participating threads access consistent data state, solving data visibility issues during multi-threaded collaboration. A queuing mechanism coordinates concurrent update execution order, preventing unordered modifications to the same data, providing consistency guarantees for high-concurrency hotspot row updates.
Row lock and execution flow optimization: Splits seckill transaction execution into "update" and "commit" phases, optimizing the storage engine lock system around different phases' lock usage to reduce serialized waiting on hotspot rows. By coordinating inter-thread execution and handoff, multiple seckill threads advance in parallel, alleviating lock contention throughput bottlenecks and improving hotspot update efficiency.
Parallel log commit and recovery consistency: Supports parallel log commit by multiple seckill threads, reducing wait in the log commit phase and further improving high-concurrency processing capacity. Accompanying crash recovery flow adjustments make log recovery logic match the new concurrent execution mechanism, ensuring data consistency after failure recovery and between primary and replica nodes while gaining performance.
2.2 Thread Pool (线程池)
MySQL lacks SQL awareness and classification capabilities; slow or large transactions can easily drag down the system, causing drastic performance drops. The solution is resource isolation: thread pools divide system resources into multiple groups with isolation between groups, preventing slow/large transactions from affecting the entire system.
Resource allocation optimization: Divides independent thread groups by CPU core count, assigns connections to corresponding groups for execution, and reduces thread creation, destruction, and context-switching overhead through worker thread reuse and dynamic scaling.
Priority scheduling optimization: Identifies different SQL types, places them into different priority queues, and combines classified concurrency limits to coordinate resource allocation, constraining high-overhead queries and reducing abnormal load interference with normal business requests.
Online hot migration: Supports online switching of thread scheduling modes. Existing connections migrate when command execution ends, no active transaction exists, and no locks are held; otherwise migration is deferred and retried, minimizing impact on business continuity.
Statistics and diagnostics enhancement: Collects queue backlog, execution wait, and thread group health status, combined with SQL template execution cost statistics, providing basis for high-load throttling, resource scheduling optimization, and fault diagnosis.
Main benefits: Through thread reuse, classified throttling, and online switching, alleviates connection storms and high-overhead SQL impact, improves service stability under high load, and reserves dedicated channels for emergency operations.
2.3 DDL Optimization (DDL优化)
Business iterations frequently require adding columns, creating indexes, or adjusting table structures. As data volume and instance scale grow, DDL problems become prominent: some operations scan or rebuild entire tables, taking long execution time; buffer pool traversal and lock contention interfere with normal reads/writes; when encountering long transactions, DDL waits for metadata locks and may block subsequent requests, causing connection pile-up. Xiaohongshu optimizes from three dimensions—execution cost, cache management, and lock waiting—to make changes faster with less business impact.
Second-level column addition (秒级加列): Reduces full table rewrite. Traditional column addition may require table rebuild, with duration growing with data volume. Second-level column addition updates metadata to record the new column and default value; when reading old records, the column semantics are filled in without rewriting historical data row by row. In supported scenarios and when locks are acquired smoothly, column addition can be shortened to seconds, reducing disk overhead and shortening business delivery cycles.
Fast DDL: Optimizes cache management. Addresses DDL traversing the entire buffer pool and lock contention causing jitter. Without changing the existing DDL process, it reduces unrelated page scans and optimizes lock usage, lowering contention overhead during cache management. These improvements make DDL cache processing cost no longer grow directly with the entire buffer pool size, improving change efficiency while reducing impact on concurrent business, especially suitable for small-table changes under large buffer pools.
Nonblocking DDL: Alleviates lock wait blocking. When long transactions hold locks, waiting DDL may block subsequent reads/writes. Long waits are changed to short waits with intermittent retries; during retry intervals, the exclusive metadata lock request is released, allowing business to continue access. It mainly reduces connection pile-up and timeouts, still requires acquiring the lock, and DDL completion time may be extended. This avoids transaction pile-up caused by long transactions holding locks.
2.4 Flashback (闪回)
After business accidentally executes UPDATE or DELETE, even if only a small amount of data is modified incorrectly, recovery typically requires restoring backups and replaying binlog to retrieve original values—complex and time-consuming. Xiaohongshu designed a flashback solution: by periodically saving transaction visibility snapshots and extending undo history version retention, users can directly query pre-mistake data via SQL, track changes, and export old values for verification and repair.
This solution shortens the data retrieval process but must be enabled in advance and incurs additional storage overhead; it only supports historical queries within the retention window, does not handle DROP/TRUNCATE, and does not automatically overwrite current data.
Historical data query: Supports querying old data by time point and viewing record history versions, helping locate accidental deletions/modifications, and exporting historical data to a new table for business verification and repair.
Snapshot and version rollback: Periodically saves transaction visibility snapshots, combined with undo version chains to read historical data without copying entire business data, providing a foundation for data traceback within short time windows.
History retention and resource control: Uses two-phase cleanup: secondary indexes are recycled normally, while primary key old records and undo are cleaned with delay; under resource pressure, the history window is shortened, prioritizing instance stability.
Main benefits: Within the queryable window, a few SQL statements retrieve pre-mistake data, reducing backup restoration and data extraction wait times.
Future Plans
Xiaohongshu MySQL kernel iteration continues, focusing on four directions:
Cloud-native elasticity and resource utilization: Achieve independent resource scaling and on-demand elasticity through storage-compute separation and serverless, improving utilization and reducing costs.
HTAP fusion of transactions and real-time analytics: Fuse transaction processing with real-time analytics, reduce data movement, and let businesses obtain analytical results faster.
Performance optimization breaking high-concurrency and DDL bottlenecks: Logical prefetch, column compression, parallel DDL, buffer pool resize, etc., to increase throughput, reduce data space, and shorten DDL time.
Security audit enhancing data protection and observability: Combine national cryptography, transparent encryption, and multi-dimensional monitoring to strengthen data protection, output full SQL and second-level observability metrics, supporting fault diagnosis and intelligent operations.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
