Cấu hình và phân tích hiệu năng với pg_stat_statements
Bước đầu tiên để tối ưu hóa là xác định các truy vấn SQL đang tiêu tốn tài nguyên nhất trong Object Store.
Chúng ta cần kích hoạt extension pg_stat_statements để PostgreSQL ghi lại thống kê thực thi của từng câu lệnh SQL.
Truy cập vào terminal của server PostgreSQL và thực thi lệnh sau để mở extension này:
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
Kết quả mong đợi: Xuất hiện thông báo CREATE EXTENSION. Nếu đã tồn tại, nó sẽ báo NOTICE: extension "pg_stat_statements" already exists.
Tiếp theo, cần đảm bảo cấu hình shared_preload_libraries trong file postgresql.conf đã chứa module này nếu bạn chưa làm ở phần trước.
Sửa file cấu hình tại đường dẫn /etc/postgresql/16/main/postgresql.conf (chỉnh version 16 cho Ubuntu 24.04 mới nhất).
Dùng nano hoặc vi để tìm dòng shared_preload_libraries và cập nhật nội dung như sau:
shared_preload_libraries = 'pg_stat_statements'
Khởi động lại dịch vụ PostgreSQL để áp dụng thay đổi:
sudo systemctl restart postgresql
Kết quả mong đợi: Dịch vụ khởi động lại thành công, không có lỗi trong journalctl -u postgresql.
Để phân tích, truy vấn vào bảng thống kê để tìm 10 câu lệnh tốn thời gian nhất:
sudo -u postgres psql -c "SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;"
Kết quả mong đợi: Một bảng hiển thị các câu lệnh SQL, số lần gọi (calls) và thời gian thực thi trung bình. Các câu lệnh có mean_exec_time cao là ứng viên để tối ưu hóa.
Verify kết quả
Chạy lại lệnh query trên sau 5 phút hoạt động hệ thống. Nếu thấy các dòng query liên quan đến việc SELECT * FROM objects hoặc INSERT INTO buckets có thời gian thực thi giảm hoặc ổn định, hệ thống đang ghi log thống kê chính xác.
Xử lý sự cố: Database Full và Connection Refused
Sự cố database is full thường xảy ra khi dung lượng disk của partition chứa data directory bị đầy, hoặc giới hạn max_wal_size bị vượt quá.
Đầu tiên, kiểm tra dung lượng disk đang sử dụng:
df -h /var/lib/postgresql
Kết quả mong đợi: Nếu cột Use% đạt 100%, đây là nguyên nhân. Cần giải phóng disk hoặc mở rộng partition.
Để xử lý tạm thời khi disk đầy nhưng vẫn cần hoạt động, hãy xóa các file WAL (Write-Ahead Log) cũ hoặc archive log không cần thiết. Tuy nhiên, cách an toàn nhất là tăng dung lượng.
Nếu lỗi là FATAL: database is full do giới hạn WAL, kiểm tra và tăng tham số trong postgresql.conf:
max_wal_size = 4GB
Khởi động lại dịch vụ sau khi chỉnh sửa:
sudo systemctl restart postgresql
Kết quả mong đợi: PostgreSQL chấp nhận các transaction mới, lỗi database is full biến mất.
Sự cố connection refused thường do PostgreSQL không lắng nghe (listen) trên cổng mặc định hoặc bị firewall chặn.
Kiểm tra xem PostgreSQL có đang lắng nghe trên cổng 5432 không:
sudo ss -tlnp | grep 5432
Kết quả mong đợi: Thấy dòng LISTEN 0 128 0.0.0.0:5432 hoặc 127.0.0.1:5432. Nếu không có gì, dịch vụ đang tắt hoặc cấu hình sai.
Chỉnh sửa file /etc/postgresql/16/main/postgresql.conf để lắng nghe trên mọi địa chỉ IP:
listen_addresses = '*'
Cập nhật file /etc/postgresql/16/main/pg_hba.conf để cho phép kết nối từ mạng nội bộ (ví dụ 192.168.1.0/24):
host all all 192.168.1.0/24 md5
Khởi động lại dịch vụ:
sudo systemctl restart postgresql
Kết quả mong đợi: Lệnh psql -h -U postgres từ máy client có thể kết nối thành công.
Nếu vẫn bị chặn, kiểm tra firewall UFW (mặc định trên Ubuntu):
sudo ufw allow 5432/tcp
Kết quả mong đợi: Thông báo Rules updated.
Verify kết quả
Thử tạo một bucket mới hoặc upload một object nhỏ từ ứng dụng Object Store. Nếu không còn lỗi database is full và kết nối ổn định, sự cố đã được khắc phục.
Tối ưu hóa chỉ mục (Index) cho Object Store
Object Store thực hiện rất nhiều truy vấn tìm kiếm object theo bucket_name, object_key, hoặc content_type. Chỉ mục mặc định có thể không đủ.
Xác định các trường thường dùng trong điều kiện WHERE của ứng dụng của bạn. Giả sử bảng objects có cấu trúc: (id, bucket_id, key, size, created_at).
Tạo composite index (chỉ mục kết hợp) cho các truy vấn tìm kiếm object theo bucket và key:
sudo -u postgres psql -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_objects_bucket_key ON objects (bucket_id, key);"
Điểm quan trọng: Dùng CONCURRENTLY để tránh khóa (lock) bảng khi tạo index trên môi trường production đang chạy.
Kết quả mong đợi: Thông báo CREATE INDEX. Quá trình có thể mất vài giây đến vài phút tùy lượng dữ liệu, nhưng không làm treo hệ thống.
Tối ưu hóa chỉ mục cho các truy vấn thống kê theo thời gian (ví dụ: liệt kê object mới nhất):
sudo -u postgres psql -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_objects_created_at ON objects (created_at DESC);"
Kết quả mong đợi: Chỉ mục được tạo thành công, hỗ trợ sắp xếp giảm dần theo thời gian.
Sử dụng lệnh VACUUM ANALYZE để cập nhật thống kê cho bộ tối ưu hóa truy vấn (Query Planner) sau khi tạo index:
sudo -u postgres psql -c "VACUUM ANALYZE objects;"
Kết quả mong đợi: Thông báo VACUUM. PostgreSQL đã cập nhật metadata về bảng objects.
Để kiểm tra xem index có đang được sử dụng không, chạy lệnh EXPLAIN ANALYZE cho một truy vấn mẫu:
sudo -u postgres psql -c "EXPLAIN ANALYZE SELECT * FROM objects WHERE bucket_id = '123' AND key = 'file.txt';"
Kết quả mong đợi: Trong output, thấy dòng Index Scan hoặc Index Only Scan sử dụng idx_objects_bucket_key thay vì Seq Scan (quét toàn bộ bảng).
Verify kết quả
So sánh thời gian thực thi của cùng một truy vấn trước và sau khi tạo index. Nếu dùng EXPLAIN, hãy nhìn vào giá trị Planning Time và Execution Time. Thời gian thực thi nên giảm đáng kể (từ hàng chục ms xuống dưới 1ms cho các object nhỏ).
Cấu hình nâng cao để mở rộng hệ thống (Scaling)
Khi lượng dữ liệu tăng lên hàng triệu object, một bảng đơn sẽ trở nên chậm. Cần áp dụng kỹ thuật Table Partitioning (chia bảng) theo thời gian.
Giả sử bạn muốn chia bảng objects theo năm. Đầu tiên, tạo bảng con cho năm 2024:
sudo -u postgres psql -c "CREATE TABLE objects_2024 (LIKE objects INCLUDING ALL) PARTITION OF objects FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');"
Kết quả mong đợi: Thông báo CREATE TABLE. Lưu ý: Lệnh này chỉ chạy được nếu bảng objects đã được định nghĩa là PARTITION BY RANGE (created_at) từ đầu. Nếu chưa, bạn cần dùng ALTER TABLE objects SET PARTITION BY RANGE (created_at) trước khi tạo partition, hoặc tạo bảng mới và migrate dữ liệu.
Để tối ưu hóa bộ nhớ đệm (Shared Buffers), điều chỉnh tham số shared_buffers trong postgresql.conf. Khuyến nghị là 25% tổng RAM vật lý.
Giả sử server có 16GB RAM, hãy thiết lập:
shared_buffers = 4GB
Tăng bộ nhớ cho bộ đệm viết (Write Buffer) để tăng tốc độ nhập liệu hàng loạt:
wal_buffers = 64MB
Khởi động lại PostgreSQL:
sudo systemctl restart postgresql
Kết quả mong đợi: Dịch vụ khởi động lại. Các tham số mới được áp dụng.
Tối ưu hóa bộ đếm bộ nhớ (Effective Cache Size) để Query Planner biết hệ thống có nhiều RAM để cache dữ liệu:
effective_cache_size = 12GB
Tham số này không cấp phát RAM thực tế mà chỉ là gợi ý cho bộ tối ưu hóa. Nên đặt bằng 75% tổng RAM.
Khởi động lại dịch vụ:
sudo systemctl restart postgresql
Kết quả mong đợi: Không có lỗi khởi động. Các truy vấn phức tạp sẽ nhanh hơn do PostgreSQL ưu tiên sử dụng index scan thay vì table scan.
Cấu hình số lượng worker để thực hiện các tác vụ nền (như VACUUM) không làm ảnh hưởng hiệu năng chính:
autovacuum_vacuum_scale_factor = 0.05
Giảm hệ số này xuống 0.05 (mặc định là 0.2) để VACUUM chạy thường xuyên hơn, giữ cho bảng luôn gọn nhẹ khi có nhiều update/delete object.
Khởi động lại dịch vụ:
sudo systemctl restart postgresql
Kết quả mong đợi: Quá trình tự động dọn dẹp (autovacuum) hoạt động tích cực hơn, ngăn chặn bảng bị bloat.
Verify kết quả
Chạy lệnh EXPLAIN (ANALYZE, BUFFERS) cho một truy vấn tìm kiếm object. Quan sát dòng Buffers trong output. Nếu thấy shared hit cao hơn shared read, nghĩa là dữ liệu đang được cache hiệu quả trong RAM. Kiểm tra lại pg_stat_activity để đảm bảo không có query nào bị treo quá lâu.
Điều hướng series:
Mục lục: Series: Triển khai Database Object Store với PostgreSQL và Ubuntu 24.04
« Phần 6: Cấu hình replication và backup dữ liệu Object Store