Database MySQL và PostgreSQL chậm: 6 nguyên nhân thường gặp

Trả lời nhanh: Sáu nguyên nhân theo thứ tự hay gặp: thiếu chỉ mục khiến truy vấn quét toàn bảng, bộ nhớ đệm cấu hình quá nhỏ so với RAM có sẵn, ổ đĩa chậm khiến mỗi lần đọc dữ liệu đều tốn thời gian, truy vấn viết kém như lấy tất cả cột hoặc lồng nhiều tầng, quá nhiều kết nối đồng thời, và cuối cùng mới tới phần cứng yếu. Luôn kiểm tra theo đúng thứ tự này, vì nâng cấp máy khi nguyên nhân là thiếu chỉ mục chỉ tốn tiền mà không giải quyết gì.

Trang web chậm, nhìn vào thấy database mất mấy giây mới trả kết quả. Phản xạ đầu tiên của nhiều người là nâng cấu hình máy. Nhưng thống kê thực tế cho thấy nguyên nhân hàng đầu lại là thứ không tốn đồng nào để sửa.

1. Thiếu chỉ mục

Đây là nguyên nhân phổ biến nhất. Không có chỉ mục, database phải đọc từng dòng trong bảng để tìm kết quả. Bảng một triệu dòng nghĩa là một triệu lần đọc cho mỗi truy vấn.

Kiểm tra bằng cách đặt từ khoá EXPLAIN trước truy vấn chậm:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Thấy chữ ALL ở cột type nghĩa là đang quét toàn bảng. Thêm chỉ mục cho cột hay dùng trong điều kiện lọc và trong phép nối bảng thường rút thời gian từ vài giây xuống vài mili giây.

2. Bộ nhớ đệm quá nhỏ

MySQL với InnoDB dùng vùng đệm để giữ dữ liệu nóng trong RAM. Cấu hình mặc định thường chỉ 128MB, quá nhỏ so với máy chủ có 8GB hay 16GB RAM.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Với máy chuyên chạy database, đặt vùng đệm khoảng 60 tới 70 phần trăm tổng RAM là hợp lý. Khi dữ liệu nóng nằm gọn trong RAM, database gần như không phải chạm ổ đĩa khi đọc.

3. Ổ đĩa chậm

Khi dữ liệu lớn hơn bộ nhớ đệm, mỗi truy vấn đều phải đọc từ ổ. Đây là lúc khác biệt giữa NVMe và ổ thường lộ ra rõ nhất, vì database đọc ghi kiểu ngẫu nhiên chứ không đọc tuần tự.

Ngoài ra mọi thao tác ghi đều phải ghi thêm nhật ký giao dịch để bảo đảm an toàn dữ liệu. Ổ chậm làm mọi lệnh thêm sửa xoá đều chậm theo.

4. Truy vấn viết kém

• Lấy tất cả các cột trong khi chỉ cần vài cột.
• Truy vấn lồng nhiều tầng thay vì dùng phép nối bảng.
• Gọi database trong vòng lặp, mỗi vòng một truy vấn, thay vì gộp lại một lần.
• Sắp xếp trên cột không có chỉ mục với tập dữ liệu lớn.

Bật nhật ký truy vấn chậm để tìm ra thủ phạm thay vì đoán.

5. Quá nhiều kết nối đồng thời

Mỗi kết nối chiếm một luồng và một phần bộ nhớ. Vượt quá ngưỡng, database dành phần lớn thời gian chuyển qua lại giữa các luồng thay vì làm việc thật.

Giải pháp thường là dùng nhóm kết nối dùng lại ở tầng ứng dụng, thay vì mở kết nối mới cho mỗi lượt truy cập.

6. Phần cứng yếu

Sau khi đã loại trừ năm nguyên nhân trên mà vẫn chậm, khi đó mới nghĩ tới nâng cấp. Thứ tự ưu tiên: thêm RAM để mở rộng bộ nhớ đệm, đổi sang ổ NVMe, rồi mới tới CPU xung cao hơn.

Nói thẳng cho đỡ mất thời gian: nếu chưa chạy EXPLAIN trên các truy vấn chậm nhất thì đừng nâng cấp máy vội. Rất nhiều trường hợp một chỉ mục đặt đúng chỗ giải quyết được vấn đề mà không tốn gì.

Tìm truy vấn chậm, lệnh cho từng hệ

-- MySQL: bat nhat ky truy van cham (khong can khoi dong lai)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- ghi truy van tren 1 giay
SHOW VARIABLES LIKE 'slow_query_log_file';
-- Doc file bang: mysqldumpslow -s t -t 10 /duong/dan/file.log

-- MySQL: xem dang co truy van nao chay lau
SELECT id, user, db, time, state, LEFT(info,120)
FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC;

-- PostgreSQL: bat theo doi thong ke truy van
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, ROUND(mean_exec_time::numeric,1) AS tb_ms,
       ROUND(total_exec_time::numeric) AS tong_ms, LEFT(query,100) AS truy_van
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- PostgreSQL: bang nao dang bi quet toan bo
SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 10;

Cột seq_scan cao mà idx_scan thấp là bảng đang thiếu chỉ mục. Đây là cách tìm ra thủ phạm mà không cần đoán.

Tham số cấu hình nên chỉnh, theo dung lượng bộ nhớ máy

Với PostgreSQL, đừng đặt max_connections lên hàng nghìn. Mỗi kết nối là một tiến trình riêng chiếm bộ nhớ. Cách đúng là đặt một bộ gom kết nối phía trước, giữ số kết nối thật xuống thấp.

Đo xem ổ đĩa có phải nút thắt không

iostat -x 2      # cot %util gan 100 va await cao la o dia dang cham
vmstat 2         # cot wa cao la CPU dang ngoi cho o dia

-- MySQL: ty le doc trung vung dem, nen tren 99%
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100

-- PostgreSQL: ty le trung dem theo tung co so du lieu
SELECT datname, ROUND(100.0*blks_hit/NULLIF(blks_hit+blks_read,0),2) AS ty_le_trung
FROM pg_stat_database WHERE datname NOT LIKE 'template%';

Tỉ lệ trúng đệm dưới 95% nghĩa là dữ liệu nóng không nằm vừa trong bộ nhớ, nên mỗi truy vấn đều phải đọc ổ. Hai cách sửa: tăng bộ nhớ, hoặc dùng ổ nhanh. Tăng bộ nhớ hiệu quả hơn, nhưng có ngưỡng: dữ liệu 200 GB thì không thể nhét hết vào bộ nhớ, khi đó tốc độ ổ quyết định.

Bảo trì định kỳ, việc ít người làm

• Cập nhật thống kê. Bộ tối ưu chọn kế hoạch dựa trên thống kê, thống kê cũ là chọn sai. PostgreSQL: kiểm last_autovacuum và last_analyze trong pg_stat_user_tables. MySQL: ANALYZE TABLE ten_bang;.
• Dọn dữ liệu chết trong PostgreSQL. SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;. Số lớn và tăng liên tục nghĩa là quá trình dọn tự động không theo kịp, cần chỉnh tham số dọn cho bảng đó.
• Rà chỉ mục không dùng. Chỉ mục thừa làm chậm mọi thao tác ghi. PostgreSQL: SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;.
• Kiểm tra bản sao lưu khôi phục được. Cơ sở dữ liệu chạy nhanh mà mất dữ liệu thì vô nghĩa. Khôi phục thử vào một máy khác ít nhất mỗi quý.

Câu hỏi thường gặp

Vì sao truy vấn chậm dù máy còn khoẻ?

Thường là do thiếu chỉ mục nên database phải quét toàn bảng. Chạy EXPLAIN để kiểm tra, thấy type là ALL nghĩa là đang quét toàn bộ.

Nên đặt vùng đệm InnoDB bao nhiêu?

Với máy chuyên chạy database, khoảng 60 tới 70 phần trăm tổng RAM. Nếu máy chạy chung với web thì giảm xuống để chừa chỗ cho các dịch vụ khác.

Ổ NVMe giúp database nhanh hơn bao nhiêu?

Rõ nhất khi dữ liệu lớn hơn bộ nhớ đệm nên phải đọc ổ thường xuyên. Nếu toàn bộ dữ liệu nóng đã nằm trong RAM thì khác biệt nhỏ hơn nhiều.

PostgreSQL và MySQL cái nào nhanh hơn?

Không có câu trả lời chung, phụ thuộc kiểu truy vấn và cách cấu hình. Cả hai đều chậm như nhau nếu thiếu chỉ mục và cấu hình bộ nhớ đệm sai.

Bài viết liên quan

• VPS ổ Enterprise U.2 NVMe RAID 10 tại TND
• Bảng giá VPS đầy đủ
• Tổng quan các dòng VPS của TND