Xử lý Chịu tải và Chống Nghẽn Cổ chai cho Database Quan hệ MySQL 8.4 LTS với ProxySQL, GTID Replication và Sharding (Chuẩn 2026)

1. Khi nào Kiến trúc Mở rộng Dọc (Scale-Up) Chạm Trần Vật lý trên Hệ thống MySQL Chịu tải Cao?

Sau khi đội ngũ Kỹ sư Cơ sở dữ liệu (DBA) đã hoàn tất việc tinh chỉnh bộ nhớ đệm trang và thiết kế chỉ mục trên một máy chủ đơn lẻ theo hướng dẫn tại bài tối ưu hiệu năng MySQL 8.0 và InnoDB Buffer Pool, hệ thống sẽ đạt tới giới hạn vật lý của kiến trúc Mở rộng Dọc (Vertical Scale-Up) khi quy mô lưu lượng vượt ngưỡng 10,000+ kết nối TCP đồng thời hoặc dung lượng bảng giao dịch vượt quá 2–5 TB. Ở quy mô này, ngay cả máy chủ sở hữu 128 vCPU và 512 GB RAM trên nền triển khai cụm Kubernetes High Availability trên hạ tầng Bare Metal cũng sẽ đối mặt với bốn điểm nghẽn cổ chai cấp hệ thống phân tán:

1. Bão Chuyển Ngữ cảnh Luồng Kết nối (Connection Thread Thrashing): Mô hình xử lý mặc định một luồng trên mỗi kết nối (one-thread-per-connection) của nhân mysqld khiến hàng nghìn kết nối nhàn rỗi (Sleep) từ hàng trăm Pod ứng dụng trong kiến trúc Microservices với Docker và Kubernetes thực chiến tiêu tốn hàng chục Gigabyte bộ nhớ ngăn xếp luồng (Thread Stack) và gây tranh chấp quyết liệt trên các khóa nội bộ của nhân (Kernel Futex / Mutex contention).
2. Nghẽn Băng thông Ghi và Đọc Chéo trên Nút Primary Duy nhất: Trong hầu hết các ứng dụng thương mại điện tử và tài chính số, tỷ lệ truy vấn đọc (SELECT) chiếm từ 80% đến 95% tổng lưu lượng, trong khi truy vấn ghi (INSERT, UPDATE, DELETE) chỉ chiếm 5% đến 20%. Nếu dồn toàn bộ truy vấn đọc báo cáo và tìm kiếm vào chung nút Primary đang xử lý giao dịch ghi ACID, hàng đợi I/O và khóa dòng (Row-level Locks) sẽ làm tê liệt luồng thanh toán.
3. Độ trễ Sao chép Bất đồng bộ (Replication Lag) và Lỗi Đọc Dữ liệu Cũ (Stale Reads): Khi tách truy vấn đọc sang các máy chủ Replica, nếu một người dùng vừa cập nhật số dư ví trên nút Primary (Hostgroup 10) rồi lập tức tải lại trang để đọc từ nút Replica (Hostgroup 20) trước khi bản ghi Binary Log (GTID) kịp truyền sang, người dùng sẽ thấy số dư cũ—gây lỗi nghiệp vụ nghiêm trọng.
4. Hiệu ứng Phình to Bảng Lịch sử Giao dịch (Monolithic Table Bloat): Khi một bảng orders hoặc ledger_entries vượt ngưỡng hàng tỷ dòng, thời gian chạy ALTER TABLE, sao lưu vật lý (Percona XtraBackup) và xây dựng lại chỉ mục tăng theo cấp số nhân, đòi hỏi phải phân vùng bảng (Declarative Partitioning) hoặc phân mảnh ngang qua nhiều cụm máy chủ (Horizontal Sharding với Vitess).

Bài viết chuyên sâu này tập trung hoàn toàn vào Kiến trúc Mở rộng Ngang (Horizontal Scale-Out) và Chống Nghẽn Cổ chai Phân tán trên MySQL 8.4 LTS theo chuẩn Production năm 2026: từ thiết lập tầng trung gian ProxySQL v2.7+ (Connection Multiplexing & Read/Write Splitting), đồng bộ nhất quán nhân quả với GTID (WAIT_FOR_EXECUTED_GTID_SET), tự động chuyển đổi dự phòng bằng InnoDB Group Replication / Orchestrator, cho tới chiến lược Horizontal Sharding với UUIDv7 (RFC 9562) và chẩn đoán tranh chấp khóa trực tiếp qua performance_schema.data_locks.

2. Kiến trúc Tầng Trung gian ProxySQL v2.7+: Connection Multiplexing và Phân tách Đọc/Ghi (Read/Write Splitting)

Thay vì để 200 Pod ứng dụng mở trực tiếp 200 x 50 = 10,000 kết nối TCP vào nút MySQL Primary, việc đặt cụm ProxySQL v2.7+ ở tầng L7 SQL Proxy giúp tách rời hoàn toàn Kết nối Phía trước (Frontend Connections) từ ứng dụng và Kết nối Phía sau (Backend Connections) tới mysqld.

flowchart TB
    subgraph AppTier["Tầng Ứng Dụng Microservices (10,000+ Frontend Connections)"]
        SVC1["Order Service Pods (Go 1.24+)"]
        SVC2["Catalog & Search Service Pods"]
        SVC3["Billing & Analytics Workers"]
    end

    subgraph ProxyTier["Tầng Điều Phối L7 ProxySQL v2.7+ Cluster"]
        MUX["Connection Multiplexer & Query Cache<br/>(Tỷ lệ gộp kết nối 100:1 -> Giữ < 150 Backend Conns)"]
        ROUTER["Bộ Phân Tích Cú Pháp SQL & Regex/Digest Router<br/>(transaction_persistent = 1)"]
        MON["Health & Replication Lag Monitor<br/>(max_replication_lag = 2s)"]
    end

    subgraph MySQLCluster["Tầng Cụm Cơ Sở Dữ Liệu MySQL 8.4 LTS (GTID + WRITESET MTS)"]
        WRITER["Hostgroup 10 (Writer / Primary)<br/>10.20.1.10:3306 (read_only = 0)"]
        READER1["Hostgroup 20 (Reader Replica 01)<br/>10.20.1.11:3306 (super_read_only = 1)"]
        READER2["Hostgroup 20 (Reader Replica 02)<br/>10.20.1.12:3306 (super_read_only = 1)"]
    end

    SVC1 -->|"TCP :6033"| MUX
    SVC2 -->|"TCP :6033"| MUX
    SVC3 -->|"TCP :6033"| MUX
    MUX --> ROUTER
    ROUTER -->|"BEGIN / INSERT / UPDATE / DELETE<br/>SELECT ... FOR UPDATE"| WRITER
    ROUTER -->|"SELECT (Read-Only Queries)"| READER1
    ROUTER -->|"SELECT (Read-Only Queries)"| READER2
    WRITER -->|"GTID Binary Log Stream (WRITESET Parallel)"| READER1
    WRITER -->|"GTID Binary Log Stream (WRITESET Parallel)"| READER2
    MON -.->|"Kiểm tra read_only & Seconds_Behind_Source"| MySQLCluster

2.1. Cơ chế Connection Multiplexing và Điều kiện Bắt buộc để Không Bị Khóa Ghép Kênh

Trên ProxySQL v2.7+, một kết nối vật lý tới MySQL Backend có thể phục vụ luân phiên hàng trăm kết nối ứng dụng Frontend theo từng câu lệnh đơn lẻ (Statement-level Multiplexing). Nhờ đó, 10,000 kết nối từ ứng dụng được nén xuống chỉ còn 100–200 kết nối thực sự mở trên mysqld, giải phóng hoàn toàn áp lực cấp phát bộ nhớ luồng của nhân Linux (kết hợp cùng kỹ thuật tinh chỉnh hàng đợi socket trong bài tuning Linux Kernel cho máy chủ Web phục vụ hàng triệu kết nối đồng thời).

Tuy nhiên, ProxySQL sẽ tự động vô hiệu hóa Multiplexing (ghim chặt kết nối Backend vào riêng 1 kết nối Frontend) nếu phát hiện ứng dụng sử dụng các trạng thái gắn liền với phiên (Session-pinned state):

• Đang nằm bên trong một khối giao dịch mở (BEGIN / START TRANSACTION chưa COMMIT hoặc ROLLBACK).
• Sử dụng biến người dùng cấp phiên (SET @user_var = ...).
• Tạo bảng tạm (CREATE TEMPORARY TABLE) hoặc gọi GET_LOCK().
• Thực thi FOUND_ROWS() hoặc LAST_INSERT_ID().

Vì vậy, quy tắc vàng khi viết mã ứng dụng đi qua ProxySQL là: không bao giờ dùng biến @var trong truy vấn thông thường và luôn bật transaction_persistent = 1 cho tài khoản ứng dụng để mọi câu lệnh SELECT nằm bên trong cùng một khối BEGIN ... COMMIT luôn được định tuyến cố định vào Hostgroup 10 (Writer), tuyệt đối không bị xé lẻ sang Hostgroup 20 (Reader).

2.2. Cấu hình Khai báo /etc/proxysql.cnf và Luật Định tuyến Read/Write Splitting Chuẩn Production

Tệp cấu hình /etc/proxysql.cnf dưới đây thiết lập đầy đủ hai nhóm máy chủ (Hostgroup 10 cho Primary Writer và Hostgroup 20 cho Reader Replicas), tự động cô lập nút Replica nếu độ trễ sao chép vượt quá 2 giây (max_replication_lag = 2), và phân luồng chính xác các truy vấn đọc/ghi:

# File: /etc/proxysql.cnf
# Cấu hình ProxySQL v2.7+ chuẩn Production cho cụm MySQL 8.4 LTS (1 Primary + 2 Replicas)
datadir = "/var/lib/proxysql"
errorlog = "/var/lib/proxysql/proxysql.log"

admin_variables = {
    admin_credentials = "radmin:${PROXYSQL_ADMIN_PASSWORD}"
    mysql_ifaces = "127.0.0.1:6032"
    cluster_username = "radmin"
    cluster_password = "${PROXYSQL_ADMIN_PASSWORD}"
}

mysql_variables = {
    threads = 8
    max_connections = 15000
    default_query_delay = 0
    default_query_timeout = 30000
    have_compress = true
    poll_timeout = 2000
    interfaces = "0.0.0.0:6033;/var/run/proxysql/proxysql.sock"
    default_schema = "information_schema"
    stacksize = 1048576
    server_version = "8.4.3-Infoteck-ProxySQL"
    connect_timeout_server = 1000
    monitor_username = "proxysql_monitor"
    monitor_password = "${PROXYSQL_MONITOR_PASSWORD}"
    monitor_history = 600000
    monitor_connect_interval = 2000
    monitor_ping_interval = 1000
    monitor_read_only_interval = 1000
    monitor_read_only_timeout = 500
    ping_interval_server_msec = 10000
    ping_timeout_server = 500
    commands_stats = true
    sessions_sort = true
    multiplexing = true
}

# Danh sách máy chủ MySQL 8.4 LTS Backend
mysql_servers = (
    # Hostgroup 10: Primary Writer (Vừa nhận ghi, vừa làm dự phòng đọc khi Replicas quá tải)
    {
        address = "10.20.1.10"
        port = 3306
        hostgroup = 10
        max_connections = 250
        max_replication_lag = 0
        weight = 1000
        comment = "mysql84-primary-01 (Writer)"
    },
    # Hostgroup 20: Reader Replicas (ự động rút khỏi pool nếu trễ quá 2 giây)
    {
        address = "10.20.1.11"
        port = 3306
        hostgroup = 20
        max_connections = 400
        max_replication_lag = 2
        weight = 1000
        comment = "mysql84-replica-01 (Reader)"
    },
    {
        address = "10.20.1.12"
        port = 3306
        hostgroup = 20
        max_connections = 400
        max_replication_lag = 2
        weight = 1000
        comment = "mysql84-replica-02 (Reader)"
    }
)

# Cấu hình tài khoản ứng dụng: Mặc định vào Hostgroup 10 (Writer) và giữ nguyên Hostgroup khi mở Transaction
mysql_users = (
    {
        username = "app_order_svc"
        password = "${APP_MYSQL_PASSWORD}"
        default_hostgroup = 10
        transaction_persistent = 1
        fast_forward = 0
        active = 1
        max_connections = 8000
    }
)

# Bộ quy tắc phân luồng truy vấn (Đánh giá theo thứ tự rule_id từ nhỏ đến lớn)
mysql_query_rules = (
    # Rule 10: Bắt buộc giữ mọi câu lệnh SELECT ... FOR UPDATE / LOCK IN SHARE MODE ở Hostgroup 10 (Writer)
    {
        rule_id = 10
        active = 1
        match_digest = "^SELECT .* FOR UPDATE"
        destination_hostgroup = 10
        apply = 1
        comment = "Route locking reads to Writer Hostgroup 10"
    },
    # Rule 15: Các câu truy vấn có gợi ý /*+ HOSTGROUP=10 */ (Đọc nhất quán tức thời sau khi ghi) đi vào Writer
    {
        rule_id = 15
        active = 1
        match_pattern = "/*+ HOSTGROUP=10 */"
        destination_hostgroup = 10
        apply = 1
        comment = "Explicit hint for strongly consistent reads on Writer"
    },
    # Rule 20: Tất cả các câu lệnh SELECT thông thường còn lại được đẩy sang Hostgroup 20 (Reader Replicas)
    {
        rule_id = 20
        active = 1
        match_digest = "^SELECT "
        destination_hostgroup = 20
        apply = 1
        comment = "Offload read-only SELECT queries to Reader Hostgroup 20"
    }
)

# Tự động quan sát biến read_only của từng máy chủ để chuyển đổi Hostgroup 10 <-> 20 khi Failover
mysql_replication_hostgroups = (
    {
        writer_hostgroup = 10
        reader_hostgroup = 20
        check_type = "innodb_read_only"
        comment = "Auto-detect Primary vs Replica via super_read_only / innodb_read_only"
    }
)

3. Triệt tiêu Độ trễ Sao chép trên MySQL 8.4 LTS (WRITESET Parallel Replication) và Đọc Nhất quán Nhân quả (GTID)

3.1. Tinh chỉnh Sao chép Đa luồng Dựa trên Tập Ghi (WRITESET) trên MySQL 8.4 LTS

Trong các phiên bản MySQL cũ, tiến trình SQL Thread trên máy chủ Replica áp dụng lại sự kiện từ Relay Log một cách tuần tự đơn luồng, khiến Replica luôn bị tụt lại phía sau Primary mỗi khi có giao dịch ghi hàng loạt. Trên MySQL 8.4 LTS (phiên bản Long-Term Support mới nhất năm 2026, trong đó các thuật ngữ và biến hệ thống cũ đã được chuẩn hóa sang source/replica và bật mặc định theo dõi phụ thuộc bằng WRITESET), máy chủ Primary tính toán mã băm của từng khóa dòng bị thay đổi. Nhờ vậy, bất kỳ hai giao dịch nào không chạm vào cùng một dòng dữ liệu đều được thực thi song song hoàn toàn trên 16–32 luồng replica_parallel_workers ở phía Replica, ngay cả khi chúng không chạy đồng thời trên Primary:

# File: /etc/mysql/conf.d/90-replication-scaleout-84.cnf
# Cấu hình MySQL 8.4 LTS tối ưu hóa GTID Replication & WRITESET Parallel Applier
[mysqld]
server_id                                = 102
gtid_mode                                = ON
enforce_gtid_consistency                 = ON

# Bật khóa bảo vệ tuyệt đối trên các nút Replica để ngăn ghi nhầm dữ liệu
read_only                                = ON
super_read_only                          = ON

# Tối ưu hóa tốc độ áp dụng Relay Log đa luồng trên MySQL 8.4 LTS
replica_parallel_workers                 = 16
replica_preserve_commit_order            = ON
transaction_write_set_extraction         = XXHASH64

# Sử dụng Semi-Synchronous Replication để đảm bảo ít nhất 1 Replica đã nhận được sự kiện trước khi trả kết quả COMMIT
plugin_load_add                          = "semisync_source.so;semisync_replica.so"
rpl_semi_sync_source_enabled             = 1
rpl_semi_sync_source_timeout             = 1000
rpl_semi_sync_source_wait_for_replica_count = 1
rpl_semi_sync_replica_enabled            = 1

3.2. Đảm bảo Đọc Nhất quán Sau Khi Ghi (Read-Your-Own-Writes) bằng WAIT_FOR_EXECUTED_GTID_SET

Khi kết hợp tầng bộ nhớ đệm trong bài chiến lược Caching đa tầng với Redis Cluster và Cloudflare CDN với Read/Write Splitting qua ProxySQL, làm thế nào để một giao dịch vừa COMMIT trên nút Writer (Hostgroup 10) có thể đọc tiếp từ nút Reader (Hostgroup 20) mà không bao giờ gặp dữ liệu cũ (Stale Read) nhưng cũng không cần dồn mọi truy vấn đọc về Writer?

Giải pháp chuẩn kiến trúc MySQL 8.4 LTS là sử dụng hàm nội tại WAIT_FOR_EXECUTED_GTID_SET(gtid_set, timeout):

1. Sau khi thực thi lệnh ghi trên Writer, ứng dụng đọc mã session_track_gtids = OWN_GTID trả về ngay trong gói tin OK_Packet của giao thức MySQL (ví dụ: 3e11fa47-71ca-11e1-9e33-c80aa9429562:10492).
2. Khi thực hiện truy vấn đọc kế tiếp trên Replica, ứng dụng gửi kèm SELECT WAIT_FOR_EXECUTED_GTID_SET('3e11fa47-...:10492', 0.5). Nút Replica sẽ chờ tối đa vài phần nghìn giây cho tới khi chính giao dịch GTID đó vừa được áp dụng xong rồi mới chạy lệnh SELECT, hoặc nếu quá 0.5s thì ứng dụng tự động đọc dự phòng từ Writer.

sequenceDiagram
    autonumber
    participant App as Order Service (Go 1.24+)
    participant Proxy as ProxySQL v2.7+
    participant Primary as MySQL 8.4 Writer (HG 10)
    participant Replica as MySQL 8.4 Reader (HG 20)

    App->>Proxy: INSERT INTO orders (...) [session_track_gtids=OWN_GTID]
    Proxy->>Primary: Chuyển tiếp lệnh ghi vào Hostgroup 10
    Primary-->>App: OK_Packet kèm GTID = "uuid-A:5012"
    Primary->>Replica: Truyền luồng Binlog bất đồng bộ (GTID uuid-A:5012)
    App->>Proxy: SELECT * FROM orders WHERE id = ? (Kèm yêu cầu chờ GTID uuid-A:5012)
    Proxy->>Replica: Định tuyến sang Hostgroup 20
    Replica->>Replica: Thực thi WAIT_FOR_EXECUTED_GTID_SET("uuid-A:5012", 1) -> Trả về 0 (Đã đồng bộ)
    Replica-->>App: Trả về bản ghi đơn hàng mới nhất (Đảm bảo 100% Causal Consistency)

4. Chiến lược Phân vùng Bảng (Declarative Partitioning) và Phân mảnh Ngang (Horizontal Sharding) với UUIDv7

Khi một bảng giao dịch vượt quá dung lượng bộ nhớ vật lý của một máy chủ đơn lẻ, kỹ sư kiến trúc cần phân biệt rõ hai cấp độ phân tách dữ liệu:

4.1. Cấp độ 1: Phân vùng Bảng theo Thời gian (PARTITION BY RANGE) trên Cùng Một Máy chủ

Nếu nút thắt cổ chai đến từ việc bảng nhật ký giao dịch (audit_logs, payment_events) chứa dữ liệu của 36 tháng nhưng 95% truy vấn chỉ đọc dữ liệu của 30 ngày gần nhất, việc áp dụng Declarative Range Partitioning giúp trình tối ưu hóa tự động loại bỏ các phân vùng cũ (Partition Pruning) và cho phép xóa hàng trăm triệu dòng cũ trong 0.01 giây bằng lệnh ALTER TABLE ... DROP PARTITION p2025_10 thay vì chạy lệnh DELETE gây khóa bảng hàng giờ liền:

-- File: migrations/20261105_partitioned_ledger_events.sql
-- Thiết kế bảng phân vùng theo tháng trên MySQL 8.4 LTS phục vụ Partition Pruning & Instant Archival
CREATE TABLE IF NOT EXISTS wallet_ledger_entries (
    entry_id        BINARY(16)    NOT NULL COMMENT 'UUIDv7 nhị phân sắp xếp theo thời gian (RFC 9562)',
    account_id      BIGINT        NOT NULL,
    amount_vnd      BIGINT        NOT NULL,
    balance_after   BIGINT        NOT NULL,
    created_at      DATETIME(6)   NOT NULL,
    -- Trong MySQL 8.4, mọi cột dùng làm biểu thức phân vùng (created_at) bắt buộc phải nằm trong PRIMARY KEY
    PRIMARY KEY (entry_id, created_at),
    KEY idx_account_created (account_id, created_at DESC)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p2026_09 VALUES LESS THAN ('2026-10-01 00:00:00'),
    PARTITION p2026_10 VALUES LESS THAN ('2026-11-01 00:00:00'),
    PARTITION p2026_11 VALUES LESS THAN ('2026-12-01 00:00:00'),
    PARTITION p2026_12 VALUES LESS THAN ('2027-01-01 00:00:00'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- Quy trình xoay vòng phân vùng không gây gián đoạn (Zero-Downtime Partition Rotation):
-- 1. Tách p_future để thêm phân vùng tháng mới trước ngày đầu tháng
ALTER TABLE wallet_ledger_entries REORGANIZE PARTITION p_future INTO (
    PARTITION p2027_01 VALUES LESS THAN ('2027-02-01 00:00:00'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

4.2. Cấp độ 2: Phân mảnh Ngang Đa Máy chủ (Horizontal Sharding với Vitess / Application-Level Routing)

Khi cường độ ghi (INSERT/UPDATE) vượt quá khả năng IOPS của một nút Primary duy nhất, dữ liệu phải được chia nhỏ ra $N$ cụm MySQL độc lập (Shards). Sai lầm phổ biến nhất khi thiết kế Sharding là chọn order_id làm khóa phân mảnh (Shard Key) trong khi 90% truy vấn của khách hàng lại tìm theo customer_id (SELECT * FROM orders WHERE customer_id = ?), khiến tầng định tuyến (như Vitess VTGate) phải gửi truy vấn tán xạ tới toàn bộ các Shard (Scatter-Gather Query).

Nguyên tắc thiết kế Shard Key chuẩn Production:

1. Chọn Thực thể Gốc của Ngữ cảnh Truy vấn (customer_id hoặc tenant_id) làm Shard Key: Đảm bảo mọi đơn hàng, hóa đơn và lịch sử giao dịch của cùng một khách hàng luôn nằm trên cùng một Shard vật lý (Co-located Tables).
2. Sử dụng Định danh UUIDv7 (RFC 9562) thay cho AUTO_INCREMENT Đơn lẻ: Trong môi trường nhiều Shard, việc dùng AUTO_INCREMENT dễ gây trùng khóa khi gộp dữ liệu, còn UUIDv4 ngẫu nhiên làm phân mảnh trang chỉ mục B+Tree. Chuẩn UUIDv7 kết hợp 48-bit dấu thời gian Unix millisecond ở đầu với 74-bit ngẫu nhiên mật mã ở sau, vừa đảm bảo tính duy nhất toàn cục không cần máy chủ cấp số tập trung, vừa giữ tốc độ chèn nối tiếp tuần tự trên từng Shard:

// File: internal/sharding/mysql_shard_router.go
// Go 1.24+: Bộ định tuyến Horizontal Sharding nhất quán kết hợp Causal GTID Wait trên MySQL 8.4 LTS
package sharding

import (
    "context"
    "database/sql"
    "fmt"
    "hash/crc32"
    "time"
)

type ShardCluster struct {
    ShardID  int
    WriterDB *sql.DB // Kết nối qua ProxySQL Hostgroup 10 của Shard tương ứng
    ReaderDB *sql.DB // Kết nối qua ProxySQL Hostgroup 20 của Shard tương ứng
}

type ShardedOrderRepository struct {
    shards []*ShardCluster
}

func NewShardedOrderRepository(shards []*ShardCluster) *ShardedOrderRepository {
    if len(shards) == 0 {
        panic("at least one MySQL 8.4 shard is required")
    }
    return &ShardedOrderRepository{shards: shards}
}

// ResolveShard định tuyến xác định (Deterministic) theo tenantOrCustomerID
func (r *ShardedOrderRepository) ResolveShard(tenantOrCustomerID string) *ShardCluster {
    checksum := crc32.ChecksumIEEE([]byte(tenantOrCustomerID))
    idx := int(checksum) % len(r.shards)
    return r.shards[idx]
}

// ReadConsistentAfterWrite đảm bảo đọc từ Replica nhưng chỉ sau khi Replica đã nhận kịp minGTID
func (r *ShardedOrderRepository) ReadConsistentAfterWrite(
    ctx context.Context,
    customerID string,
    orderID string,
    minGTID string,
) (string, error) {
    shard := r.ResolveShard(customerID)
    targetDB := shard.ReaderDB

    if minGTID != "" {
        var waitStatus int
        // Chờ tối đa 0.3 giây trên Replica cho tới khi GTID vừa ghi được áp dụng xong
        err := shard.ReaderDB.QueryRowContext(
            ctx,
            "SELECT WAIT_FOR_EXECUTED_GTID_SET(?, 0.3)",
            minGTID,
        ).Scan(&waitStatus)

        // Nếu Replica đang trễ quá 300ms (waitStatus == 1) hoặc lỗi, tự động chuyển hướng về WriterDB
        if err != nil || waitStatus != 0 {
            targetDB = shard.WriterDB
        }
    }

    queryCtx, cancel := context.WithTimeout(ctx, 2*time.Second)
    defer cancel()

    var status string
    err := targetDB.QueryRowContext(
        queryCtx,
        "SELECT status FROM orders WHERE customer_id = ? AND order_id = ?",
        customerID,
        orderID,
    ).Scan(&status)
    if err != nil {
        return "", fmt.Errorf("shard %d query failed: %w", shard.ShardID, err)
    }
    return status, nil
}

5. Chẩn đoán Trực tiếp Sự cố Tranh chấp Khóa (data_locks), Deadlock và Nghẽn Truy vấn Chậm trên Production

Khi hệ thống cảnh báo trong bài giám sát hệ thống toàn diện với Prometheus và Grafana phát hiện số lượng luồng đang chạy (Threads_running) tăng đột biến từ 15 lên 300+, nguyên nhân thường không phải do thiếu RAM mà do Hiệu ứng Đoàn tàu Khóa (Lock Convoy): một giao dịch giữ khóa dòng (X,REC_NOT_GAP) quá lâu khiến hàng trăm giao dịch sau xếp hàng chờ dây chuyền.

5.1. Truy vấn Bảng performance_schema.data_locks và data_lock_waits trên MySQL 8.4 LTS

Trên MySQL 8.0/8.4, các bảng cũ trong information_schema đã được thay thế hoàn toàn bằng performance_schema.data_locks và performance_schema.data_lock_waits. Đoạn truy vấn SQL dưới đây giúp kỹ sư trực chiến chỉ mặt đặt tên chính xác PROCESSLIST_ID, câu lệnh SQL đang gây chặn (Blocking Transaction) và thời gian đã giữ khóa để xử lý tức thời:

-- File: scripts/diagnose_live_lock_convoy_mysql84.sql
-- Truy vấn tìm chính xác Giao dịch Gốc (Blocker) đang làm treo các Giao dịch Khác (Waiters) trên MySQL 8.4 LTS
SELECT
    r.trx_id                                             AS waiting_trx_id,
    r.trx_mysql_thread_id                                AS waiting_thread_pid,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW())     AS wait_seconds,
    r.trx_query                                          AS waiting_query,
    b.trx_id                                             AS blocking_trx_id,
    b.trx_mysql_thread_id                                AS blocking_thread_pid,
    b.trx_query                                          AS blocking_current_query,
    t_block.PROCESSLIST_STATE                            AS blocker_state,
    esh_block.SQL_TEXT                                   AS blocker_last_executed_sql,
    dl.OBJECT_SCHEMA                                     AS locked_schema,
    dl.OBJECT_NAME                                       AS locked_table,
    dl.INDEX_NAME                                        AS locked_index,
    dl.LOCK_MODE                                         AS lock_mode,
    dl.LOCK_DATA                                         AS locked_key_value
FROM performance_schema.data_lock_waits dlw
INNER JOIN information_schema.innodb_trx r
    ON r.trx_id = dlw.REQUESTING_ENGINE_TRANSACTION_ID
INNER JOIN information_schema.innodb_trx b
    ON b.trx_id = dlw.BLOCKING_ENGINE_TRANSACTION_ID
INNER JOIN performance_schema.data_locks dl
    ON dl.ENGINE_LOCK_ID = dlw.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads t_block
    ON t_block.THREAD_ID = dlw.BLOCKING_THREAD_ID
LEFT JOIN performance_schema.events_statements_current esh_block
    ON esh_block.THREAD_ID = dlw.BLOCKING_THREAD_ID
ORDER BY wait_seconds DESC;

[!TIP]
Tại sao blocking_current_query thường hiển thị NULL? Khi một giao dịch trong mã ứng dụng mở BEGIN, chạy lệnh UPDATE inventory SET stock = stock - 1 WHERE sku_id = 99, sau đó lại gọi một HTTP API bên thứ ba mất 15 giây trước khi gọi COMMIT, tại thời điểm bạn kiểm tra, MySQL đã chạy xong lệnh UPDATE nên trx_query hiện tại của Blocker là NULL (trạng thái Sleep). Nhờ kết nối thêm bảng performance_schema.events_statements_current (esh_block.SQL_TEXT) như trong truy vấn trên, bạn sẽ thấy ngay câu lệnh cuối cùng mà phiên Sleep đó vừa thực thi trước khi giữ khóa! Để triệt tiêu tận gốc lỗi này, hãy áp dụng mẫu khóa phân tán và tách rời lời gọi mạng ra ngoài giao dịch theo hướng dẫn tại bài thiết kế kiến trúc Distributed Locking và Idempotency Key với Redis & PostgreSQL.

5.2. Kịch bản Kiểm định Hiệu quả Ghép kênh ProxySQL và Phân tích Slow Query bằng pt-query-digest

#!/usr/bin/env bash
# File: /usr/local/sbin/verify-mysql-scaleout-health.sh
# Mô tả: Kiểm tra tỷ lệ ghép kênh trên ProxySQL Admin, độ trễ GTID Replica và phân tích Top Slow Queries
set -euo pipefail

PROXYSQL_ADMIN_HOST="127.0.0.1"
PROXYSQL_ADMIN_PORT="6032"
PROXYSQL_ADMIN_USER="radmin"

echo "=== [1/3] Kiểm tra trạng thái Backend Hostgroups (10 = Writer, 20 = Reader) và số kết nối thực tế ==="
mysql -h "${PROXYSQL_ADMIN_HOST}" -P "${PROXYSQL_ADMIN_PORT}" -u "${PROXYSQL_ADMIN_USER}" -p"${PROXYSQL_ADMIN_PASSWORD}" -t -e "
SELECT
    hostgroup,
    srv_host,
    status,
    ConnUsed,
    ConnFree,
    ConnOK,
    ConnERR,
    MaxConnUsed,
    Queries,
    Latency_us
FROM stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;
"

echo "=== [2/3] Kiểm tra Top 10 mẫu truy vấn chiếm tổng thời gian thực thi cao nhất qua ProxySQL Digest ==="
mysql -h "${PROXYSQL_ADMIN_HOST}" -P "${PROXYSQL_ADMIN_PORT}" -u "${PROXYSQL_ADMIN_USER}" -p"${PROXYSQL_ADMIN_PASSWORD}" -t -e "
SELECT
    hostgroup,
    schemaname,
    count_star,
    ROUND(sum_time / 1000000, 2) AS total_time_sec,
    ROUND(sum_time / count_star / 1000, 2) AS avg_latency_ms,
    SUBSTRING(digest_text, 1, 90) AS query_pattern
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 10;
"

echo "=== [3/3] Phân tích tệp nhật ký Slow Query Log bằng công cụ Percona pt-query-digest ==="
if command -v pt-query-digest >/dev/null 2>&1 && [ -f /var/log/mysql/mysql-slow.log ]; then
    pt-query-digest --limit=5 /var/log/mysql/mysql-slow.log
fi

6. Tài liệu Tham khảo & Nguồn Đối chiếu Chính thống (Primary References)

1. MySQL 8.4 LTS Official Reference Manual — Global Transaction Identifiers (GTID) & WAIT_FOR_EXECUTED_GTID_SET Functions
2. MySQL 8.4 LTS Official Reference Manual — Performance Schema data_locks and data_lock_waits Tables
3. ProxySQL v2.7+ Official Documentation — Connection Multiplexing & Read/Write Split Configuration
4. Vitess CNCF Graduated Project Documentation — Horizontal Sharding & VIndex Design on MySQL
5. Percona Toolkit Documentation — pt-query-digest Slow Query Analysis Reference
6. IETF RFC 9562 — Universally Unique IDentifiers (UUIDv7 Time-Ordered Shard Key Specification)