Database performance — query optimization — Performance Testing with JMeter and k6

Mở đầu — vì sao bài này quan trọng

Nếu bạn đã chạy vài chục lần load test và bắt đầu để ý các con số, bạn sẽ sớm nhận ra một sự thật hơi phũ phàng: trong đại đa số hệ thống web thương mại, database mới là nơi hệ thống "gãy" đầu tiên. Application server có thể scale ngang dễ dàng — thêm một container, thêm một pod, thêm một instance là xong. Nhưng database, đặc biệt là database quan hệ (MySQL, PostgreSQL, SQL Server), thường là một điểm tập trung khó nhân bản, nơi mọi request cuối cùng đều đổ về. Khi load tăng, chính cái điểm tập trung đó nghẹt lại.

Vấn đề là: rất nhiều kỹ sư performance dừng lại ở việc đọc số liệu phía client — p95 latency tăng, throughput chững lại, error rate nhích lên — rồi kết luận "hệ thống chịu được khoảng 800 user". Đó là một báo cáo mô tả triệu chứng, không phải chẩn đoán bệnh. Người phỏng vấn hay khách hàng sẽ hỏi ngay: "Tại sao nghẽn ở 800? Nghẽn ở đâu? Nếu tôi muốn lên 2000 thì phải sửa cái gì?". Nếu bạn không nhìn được vào bên trong database trong lúc test, bạn sẽ không có câu trả lời.

Bài này dạy bạn cách quan sát và mổ xẻ database ngay trong lúc load test đang chạy, tìm ra query nào đang giết hệ thống, và tối ưu nó. Đây là kỹ năng phân biệt một QA "chạy được tool" với một Performance Engineer thực thụ. Lưu ý: bài này không dạy cách đọc report tổng quát (Bài 24, 38) hay cách xác định điểm nghẽn nói chung (Bài 25) — chúng ta đi thẳng vào tầng database và query.

Khái niệm cốt lõi

Vì sao database dễ thành bottleneck

Khác với application layer thường "stateless" (mỗi request độc lập, không giữ trạng thái), database phải giữ tính nhất quán dữ liệu. Nó có connection pool giới hạn, có lock khi ghi, có I/O đĩa khi đọc dữ liệu không nằm trong bộ nhớ, và có CPU khi phải sắp xếp/nhóm/join dữ liệu lớn. Bất kỳ tài nguyên nào trong số này cạn kiệt trước là bottleneck của bạn.

Bốn "thủ phạm" phổ biến nhất khi load test đụng database:

Slow query: một câu SQL thiếu index, buộc database quét toàn bảng (full table scan). Ở 10 user thì 50ms, ở 500 user đồng thời thì 5 giây vì I/O và CPU cạnh tranh.
Connection pool cạn: app chỉ mở tối đa (ví dụ) 20 kết nối tới DB. Khi 500 request đồng thời cùng chờ, 480 request kia xếp hàng — latency tăng nhưng DB trông vẫn "nhàn".
Lock contention: nhiều transaction cùng ghi vào một hàng/bảng, phải chờ nhau. Kinh điển trong tình huống flash sale trừ tồn kho.
N+1 query: một request gọi 1 query lấy danh sách, rồi lặp thêm N query cho từng phần tử. Ở màn hình có 50 item, một page-view = 51 query. Nhân với 500 user = thảm họa.

Ba tầng quan sát bắt buộc

Khi test, bạn cần nhìn database ở ba tầng song song:

Tầng kết nối/hoạt động tức thời: ai đang chạy query gì ngay bây giờ? (SHOW PROCESSLIST của MySQL, pg_stat_activity của PostgreSQL).
Tầng thống kê tích lũy: query nào chạy nhiều lần nhất, tốn tổng thời gian nhiều nhất? (slow query log, performance_schema, pg_stat_statements).
Tầng tài nguyên hệ điều hành: CPU, RAM, disk I/O của server database có bão hòa không? (top, iostat, vmstat).

Nguyên tắc mentor muốn bạn khắc cốt: con số latency phía client chỉ nói CÓ vấn đề; ba tầng trên mới nói vấn đề Ở ĐÂU.

Đọc EXPLAIN — công cụ số một

EXPLAIN (và EXPLAIN ANALYZE) cho bạn biết database dự định thực thi query như thế nào. Những tín hiệu cần soi:

type: ALL (MySQL) hoặc Seq Scan (PostgreSQL) trên bảng lớn → thiếu index, đang quét toàn bảng.
rows ước lượng cao bất thường (quét hàng trăm nghìn dòng để trả về 10 dòng).
Using filesort, Using temporary → database phải tạo bảng tạm hoặc sắp xếp trên đĩa, rất tốn kém dưới tải.

Tình huống thực tế

Ví dụ 1 — Tiki: trang danh mục "sập" ở 600 user do thiếu một index

Một đội QA (bối cảnh giả định lấy cảm hứng từ các sàn TMĐT Việt như Tiki) chạy load test cho trang danh mục sản phẩm bằng k6, tăng dần đến 600 VU (virtual user). Kết quả: p95 nhảy từ 180ms lên 4.200ms, error rate 3% do timeout. Nhìn phía client, họ định kết luận "giới hạn 550 user".

Người mentor bảo mở terminal thứ hai, SSH vào DB server và chạy lặp SHOW FULL PROCESSLIST mỗi 2 giây trong lúc test. Họ thấy hàng chục dòng cùng một câu:

SELECT * FROM products WHERE category_id = 12 AND status = 'active' ORDER BY sold_count DESC LIMIT 40;

với cột State: Sorting result và Time: 3-5. Chạy EXPLAIN cho query đó:

EXPLAIN SELECT * FROM products WHERE category_id = 12 AND status = 'active' ORDER BY sold_count DESC LIMIT 40;

Kết quả: type: ALL, rows: 480000, Extra: Using where; Using filesort. Database quét toàn bộ 480 nghìn sản phẩm và sắp xếp trên đĩa cho mỗi request. Ở 10 user nó vẫn nhanh vì cache và ít cạnh tranh; ở 600 user, disk I/O bão hòa.

Giải pháp: thêm composite index:

CREATE INDEX idx_cat_status_sold ON products (category_id, status, sold_count);

Chạy lại test: p95 còn 210ms ở 600 user, và họ đẩy tiếp lên 1.500 user vẫn ổn. Bài học: giới hạn "550 user" là ảo — nó là giới hạn của một câu query thiếu index, không phải của kiến trúc. Nếu không nhìn vào DB, cả team đã đi mua thêm server một cách vô ích.

Ví dụ 2 — Ngân hàng số: connection pool cạn khiến DB trông "nhàn" nhưng app "chết"

Một fintech ở TP.HCM load test API tra cứu số dư. Ở 300 user, latency tăng vọt nhưng CPU của DB server chỉ 25%, disk I/O thấp. "DB nhàn mà sao chậm?" — một câu hỏi bẫy kinh điển.

Kỹ sư đếm số kết nối active bằng:

SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

và thấy nó luôn dính trần đúng 20. Kiểm tra cấu hình HikariCP của app: maximum-pool-size: 20. Nghĩa là 300 request đồng thời phải chia nhau 20 "làn đường" tới DB; 280 request còn lại xếp hàng chờ trong pool. Latency mà k6 đo được phần lớn là thời gian chờ lấy connection, không phải thời gian query thực sự (mỗi query chỉ 8ms).

Họ nâng pool lên 60 và điều chỉnh max_connections phía PostgreSQL tương ứng — throughput tăng gấp gần 3 lần. Bài học: khi DB "nhàn" mà app chậm, gần như chắc chắn nghẽn ở connection pool hoặc lock, không phải ở query. Đây là lý do phải nhìn số connection active, không chỉ CPU.

Ví dụ 3 — Sàn giáo dục: N+1 query lộ diện qua slow query log

Một nền tảng học trực tuyến (bối cảnh gần gũi với chính dự án này) test API tải trang khóa học. Mỗi request tưởng như 1 query, nhưng slow query log — sau khi hạ long_query_time = 0 để log mọi query trong lúc test — cho thấy một request tạo ra 1 query lấy danh sách 50 bài học + 50 query lấy tiến độ từng bài. Đó là N+1.

Ở 200 user, DB nhận 200 × 51 = hơn 10.000 query/giây cho một thao tác lẽ ra chỉ cần vài query. Giải pháp: gộp bằng một JOIN hoặc WHERE lesson_id IN (...). Số query mỗi request giảm từ 51 xuống 2, throughput tăng vọt. Bài học: N+1 hầu như vô hình ở tầng application dưới tải thấp; chỉ load test kết hợp đọc query log mới lôi nó ra ánh sáng.

Hướng dẫn từng bước

Dưới đây là quy trình chuẩn để inspect database trong lúc load test. Mở hai terminal: một chạy test (JMeter/k6), một quan sát DB.

Bước 1 — Chuẩn bị dữ liệu đủ lớn. Test trên bảng 1.000 dòng là vô nghĩa; production có hàng triệu. Thiếu index vẫn nhanh khi bảng nhỏ. Hãy seed dữ liệu sát production (kỹ thuật sinh test data ở Bài 26).

Bước 2 — Reset thống kê trước khi test. Với PostgreSQL:

SELECT pg_stat_statements_reset();

Với MySQL, ghi lại mốc thời gian để lọc slow query log về sau.

Bước 3 — Bật quan sát tức thời. Chạy một vòng lặp trong terminal quan sát:

MySQL — chụp processlist mỗi 2 giây, lọc các query đang chạy
watch -n 2 "mysql -e \"SHOW FULL PROCESSLIST;\" | grep -v Sleep | head -30"

PostgreSQL — các query active, sắp theo thời gian chạy giảm dần
watch -n 2 "psql -c \"SELECT pid, now()-query_start AS dur, state, left(query,80) FROM pg_stat_activity WHERE state='active' ORDER BY dur DESC;\""

Bước 4 — Theo dõi tài nguyên OS song song. Trên DB server:

CPU, RAM
top -b -n 100 -d 2
Disk I/O — cột %util gần 100 nghĩa là đĩa bão hòa
iostat -x 2

Bước 5 — Chạy load test và quan sát 3 tầng cùng lúc. Trong lúc test, ghi lại: query nào xuất hiện lặp đi lặp lại trong processlist, số connection active có chạm trần không, %util đĩa và CPU có bão hòa không.

Bước 6 — Sau test, khai thác thống kê tích lũy. Đây là bước "vàng":

-- PostgreSQL: top query tốn tổng thời gian nhất
SELECT calls, round(mean_exec_time::numeric,2) AS avg_ms,
       round(total_exec_time::numeric,2) AS total_ms, left(query,90)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- MySQL (performance_schema): tương tự
SELECT count_star AS calls, round(sum_timer_wait/1e12,2) AS total_s, left(digest_text,90)
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Chú ý cột total time, không phải avg time. Một query 5ms chạy 1 triệu lần tốn nhiều hơn một query 2 giây chạy 10 lần.

Bước 7 — EXPLAIN các thủ phạm hàng đầu. Lấy 3 query đứng đầu, chạy EXPLAIN ANALYZE, tìm full scan / filesort / temporary.

Bước 8 — Tối ưu và test lại (lặp). Các đòn bẩy theo thứ tự ưu tiên:

Thêm/sửa index (đòn bẩy lớn nhất, chi phí nhỏ nhất).
Viết lại query: bỏ SELECT *, gộp N+1, tránh hàm trên cột trong WHERE (làm index vô dụng).
Điều chỉnh connection pool cân bằng với max_connections của DB.
Thêm cache cho query đọc nhiều (Bài 32 sẽ đào sâu caching).
Cuối cùng mới nghĩ tới scale phần cứng / read replica.

Chạy lại và so sánh với baseline. Ghi lại "trước — sau" để đưa vào report (Bài 38).

Lỗi thường gặp & mẹo

Chỉ nhìn latency phía client rồi kết luận. Đây là lỗi nghiêm trọng nhất. Luôn mở terminal quan sát DB song song.
Test trên dataset nhỏ. Index thiếu vẫn nhanh trên vài nghìn dòng vì DB cache hết vào RAM. Bug chỉ lộ khi dữ liệu lớn hơn bộ nhớ. Seed đủ.
Quên bảng đã "ấm" (warm cache). Lần chạy đầu chậm vì đĩa, các lần sau nhanh vì cache. Hãy warm-up rồi mới đo, và ghi rõ điều kiện.
Bật full query log rồi quên tắt trên production. long_query_time = 0 sinh log khổng lồ và làm chậm chính DB. Chỉ dùng khi test, tắt ngay sau đó.
Thêm index bừa bãi. Mỗi index làm chậm INSERT/UPDATE và tốn dung lượng. Chỉ thêm index có bằng chứng từ EXPLAIN, và cân nhắc tải ghi.
Nhầm nghẽn query với nghẽn pool. Mẹo phân biệt: nếu CPU/I/O của DB bão hòa → nghẽn query/tài nguyên. Nếu DB nhàn nhưng connection active chạm trần → nghẽn pool/lock. Hai bệnh, hai thuốc.
Mẹo vàng: chạy pg_stat_statements_reset() / ghi mốc thời gian ngay trước mỗi lần test để thống kê chỉ phản ánh đúng lần chạy đó, không lẫn nhiễu từ quá khứ.
Mẹo lock: với PostgreSQL, xem pg_locks join pg_stat_activity để bắt query đang chờ lock. Với MySQL, SHOW ENGINE INNODB STATUS có mục "TRANSACTIONS" chỉ ra ai đang chờ ai.

Bài tập thực hành

Dựng sân tập. Tạo một bảng orders với ít nhất 500.000 dòng dữ liệu giả (cột user_id, status, created_at, amount). Viết một API/endpoint đơn giản trả về đơn hàng của một user, sắp theo created_at DESC, cố tình không tạo index.

Chạy baseline. Dùng k6 (hoặc JMeter) tăng dần 10 → 300 VU. Ghi lại p95 latency và throughput. Song song, chạy vòng lặp SHOW PROCESSLIST / pg_stat_activity và chụp lại câu query xuất hiện nhiều nhất cùng cột State/dur.

Chẩn đoán. Chạy EXPLAIN ANALYZE cho query đó. Xác định: có full scan không? có filesort không? DB quét bao nhiêu dòng để trả về bao nhiêu dòng? Đồng thời chạy iostat -x 2 — %util đĩa có gần 100 không?

Tối ưu. Thêm composite index phù hợp (user_id, created_at). Chạy lại đúng kịch bản test. So sánh p95 trước/sau và số dòng EXPLAIN quét.

Thử bệnh thứ hai. Giữ nguyên index tốt, nhưng hạ connection pool của app xuống 5. Chạy lại 300 VU. Quan sát: bây giờ CPU DB thấp nhưng latency vẫn cao, connection active chạm trần 5. Viết 3–4 câu giải thích vì sao đây là nghẽn pool chứ không phải nghẽn query — kèm bằng chứng số liệu.

Viết mini-report. Tóm tắt: bottleneck ở đâu, bằng chứng nào (EXPLAIN, processlist, iostat, connection count), đã sửa gì, kết quả trước–sau. Đây chính là kỹ năng bạn sẽ dùng trong công việc thật.

Tóm tắt

Database thường là bottleneck số một trong load test, nhưng nó vô hình nếu bạn chỉ nhìn số liệu phía client. Kỹ năng cốt lõi của bài này là quan sát database ở ba tầng song song trong lúc test: hoạt động tức thời (SHOW PROCESSLIST / pg_stat_activity), thống kê tích lũy (pg_stat_statements / performance_schema / slow query log), và tài nguyên OS (top, iostat). Khi bắt được thủ phạm, dùng EXPLAIN ANALYZE để hiểu vì sao nó chậm, rồi tối ưu theo thứ tự đòn bẩy: index trước, viết lại query, chỉnh connection pool, cache, cuối cùng mới tới phần cứng.

Điều quan trọng nhất cần mang theo: một con số như "hệ thống chịu được 600 user" hầu như luôn là giới hạn của một câu query hoặc một cấu hình cụ thể, không phải của cả kiến trúc. Người biết nhìn vào database sẽ biến báo cáo "mô tả triệu chứng" thành "chẩn đoán và kê thuốc" — và đó chính là giá trị của một Performance Engineer thực thụ.