How DongSQL’s New Kernel Boosts E‑commerce DB Performance by Up to 215%

Introduction

JD Retail Database Team has spent years refining database technology and released the self‑developed DongSQL V1.1.0 kernel, which introduces deep optimizations in syntax, concurrency control, and query execution tailored for e‑commerce scenarios.

1. Syntax Extensions

1.1 RETURNING Clause

DongSQL adds a RETURNING clause to standard SQL, allowing INSERT, UPDATE, DELETE, and REPLACE statements to return affected rows without a separate SELECT. This eliminates extra network round‑trips in high‑concurrency environments.

-- INSERT returning auto‑increment ID
INSERT INTO orders (customer_id, order_date) VALUES (1001, NOW()) RETURNING order_id;

-- UPDATE returning updated rows
UPDATE products SET price = price * 1.1 WHERE category = 'electronics' RETURNING product_id, name, old_price, price;

-- DELETE returning deleted rows
DELETE FROM expired_sessions WHERE expire_time < NOW() RETURNING session_id, user_id, expire_time;

Performance impact (tested):

Fixed‑row update at 16 concurrency: TPS +61%, latency –44%.

Random‑row update at 128 concurrency: TPS +18%.

Large‑scale update (20 M ops): average TPS +5‑10%.

1.2 Hint Syntax Extension

DongSQL introduces a rich Hint system, including an Inventory Hint designed for e‑commerce stock‑deduction scenarios. The hint can enforce target‑row count, automatic commit on success, and rollback on failure.

-- Inventory deduction with hint
UPDATE /*+ TARGET_AFFECT_ROW(1) COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ inventory
SET stock = stock - 5
WHERE product_id = 1001 AND stock >= 5;

Performance impact: 215% TPS increase in a 16‑concurrency inventory‑deduction test.

2. Concurrency Control

2.1 CCL (Concurrency Control Layer)

DongSQL implements multi‑dimensional throttling via CCL, converting unordered high‑concurrency requests into ordered processing, which mitigates lock contention in flash‑sale scenarios.

Field‑based throttling: ccl_queue_field(column_name, concurrency) Value‑based throttling: ccl_queue_value(value, concurrency) SQL‑digest throttling:

ccl_queue_digest(concurrency)

-- Limit hot product ID 999 to 5 concurrent queries
SELECT /*+ ccl_queue_value(999, 5) */ * FROM products WHERE product_id = 999;

-- Throttle inventory deduction per product ID
UPDATE /*+ ccl_queue_field(product_id, 8) */ inventory SET stock = stock - 1 WHERE product_id = ?;

-- Global limit for identical SQL pattern
SELECT /*+ ccl_queue_digest(10) */ * FROM hot_products WHERE status = 1;

Performance breakthrough: in a flash‑sale test with 4096 concurrent requests, TPS rises from 573 to 1,337 (+133%). The mechanism also prevents system avalanche and reduces hotspot lock contention.

3. Query Execution Optimizations

3.1 Statement Outline

Provides plan stability by fixing execution plans for critical SQL, preventing performance regression due to data changes. DBAs can inject custom hints in emergencies.

-- Add outline for a critical query
CALL dbms_outln.add_index_outline('test_db', '', 1, 'USE INDEX', 'idx_status', '',
  'SELECT * FROM orders WHERE status = "PAID"');

-- Add CCL hint to a specific query
CALL dbms_outln.add_optimizer_outline('test_db', '', 1, '/*+ ccl_queue_digest(2) */',
  'SELECT * FROM orders WHERE customer_id = 1001');

Ensures stable performance for core SQL.

Supports manual and automatic throttling based on SQL fingerprint.

Offers production‑grade plan management.

3.2 Single‑Point Query Bypass

For primary‑key lookups, DongSQL bypasses part of the SQL layer and accesses the storage engine directly, dramatically reducing CPU usage.

Container environment: query latency ↓20%.

Physical machines: latency ↓30%.

Under CPU bottleneck, QPS improves 20‑28%.

3.3 Thread‑Pool Redesign

Enterprise‑grade thread pool reuses threads, balances load, and supports priority scheduling, eliminating the overhead of creating a thread per connection.

Read‑only workload: 27.6% QPS increase (32 threads → 141,261 vs 110,658).

High‑concurrency read: 114% QPS increase (512 threads → 131,939 vs 61,580).

TP99 latency reduced by 60% (297.9 ms → 118.9 ms).

Write workload: up to 97% QPS boost at 512 threads.

Mixed read/write: up to 60% QPS increase and 19% latency reduction.

3.4 Additional Engine Optimizations

Improvements include operator tuning, memory‑management enhancements, parallel execution, and a revamped buffer‑pool strategy that reduces I/O lock conflicts.

4. Benchmark Summary

Standard OLTP benchmark (16 CPU × 32 GB, 16 tables × 1 M rows each) shows:

Read‑only: TPS 19,484, QPS 311,745, TP99 21.5 ms.

Write‑only: TPS 17,004, QPS 102,025, TP99 29.7 ms.

Insert: TPS 25,614, QPS 25,614, TP99 15.8 ms.

Mixed read/write: TPS 9,795, QPS 195,908, TP99 33.1 ms.

Point query: TPS 560,933, QPS 560,933, TP99 0.18 ms.

5. E‑commerce Specific Gains

RETURNING clause: TPS ↑61% (925 → 1,490) in fixed‑row updates.

CCL concurrency control: TPS ↑133% (573 → 1,337) in flash‑sale.

Inventory Hint: TPS ↑215% (1,537 → 4,843) in stock‑deduction.

Single‑point query bypass: QPS ↑28% (76,432 → 98,470) for primary‑key lookups.

6. Future Roadmap

Continuous syntax extensions driven by business needs.

Machine‑learning‑guided execution‑plan selection.

Kernel‑level technical support for deeper business integration.

Further cloud‑native separation of compute and storage.