Một vài thủ thuật cơ bản để tăng tốc PostgreSQL

PostgreSQL là một hệ quản trị cơ sở dữ liệu với tính ổn định, độ tin cậy và hiệu suất cao, đặc biệt là khi nó được thường xuyên tối ưu hóa bằng những kỹ thuật thiết yếu.

Upload image

Giới thiệu

Một trong những công việc thường xuyên nhất của anh em Data Engineer là tối ưu hóa hiệu năng và giảm chi phí của các hệ thống dữ liệu, đồng thời giảm thiểu thời gian truy vấn, tăng khả năng mở rộng hệ thống và tiết kiệm tài nguyên. Hiện nay, PostgreSQL là một trong những database được sử dụng nhiều nhất. PostgreSQL vốn có tốc độ xử lý nhanh ngay từ đầu, tuy nhiên, việc tối ưu hóa phù hợp có thể góp phần cải thiện đáng kể hiệu năng cho các workload cụ thể. Chính vì vậy, trong bài viết này, chúng ta sẽ cùng tham khảo một số cách tối ưu hóa hiệu năng của PostgreSQL thông qua các biện pháp như indexing, tăng work_mem, PREPARE và EXPLAIN.

1. Sử dụng Indexing để tăng tốc các câu queries

Việc thêm index vào cột được lọc trong các điều kiện WHERE hoặc thường xuyên JOIN có thể giảm đáng kể thời gian truy vấn. Ví dụ, ở đây chúng ta sử dụng trên một tập dữ liệu với hơn 1 triệu dòng trong mỗi lần truy vấn.

-- Truy vấn mất 15 giây nếu không có index
EXPLAIN ANALYZE SELECT * FROM events WHERE created_at >= '2020-01-01';

-- Thêm index vào created_at 
CREATE INDEX events_created_at_idx ON events(created_at);

-- Bây giờ truy vấn mất 0.12 giây nhờ sử dụng index  
EXPLAIN ANALYZE SELECT * FROM events WHERE created_at >= '2020-01-01';

2. Tăng bộ nhớ trong work_mem để xử lý các In-Memory Operation lớn

Việc sắp xếp và hash cơ sở dữ liệu lớn sẽ nhanh hơn khi ta cấp nhiều bộ nhớ hơn cho work_mem. Dưới đây là một ví dụ giảm thời gian hash aggregation từ 60s xuống 0.8s với chung tập dữ liệu chúng ta sử dụng phía trên.

-- Bắt đầu với  work_mem ở mức thấp
SET work_mem='1MB';
-- Hash aggregation hết 60s
EXPLAIN ANALYZE SELECT city, SUM(value) FROM data GROUP BY city;

-- Tăng work_mem
SET work_mem='1GB';
-- Bây giờ aggregation mất 0.8s
EXPLAIN ANALYZE SELECT city, SUM(value) FROM data GROUP BY city;

3. Tinh chỉnh effective_cache_size để có kế hoạch truy vấn tốt hơn

Thiết lập effective_cache_size một cách thích hợp sẽ cải thiện chất lượng kế hoạch, kết hợp cùng EXPLAIN để so sánh từng bước, sau đó điều chỉnh mức phù hợp với hệ thống của mình.

-- Bắt đầu với 50-75% lượng RAM
SHOW effective_cache_size;
SET effective_cache_size='16GB';

-- Sau đó kiểm tra nếu index scan đáp ứng đủ dung lượng bộ nhớ 
EXPLAIN SELECT * FROM events WHERE event_type='click';

4. Sử dụng PREPARE cho các câu queries được lặp đi lặp lại

Việc sử dụng PREPRARE sẽ giúp tránh lặp lại việc phân tích cú pháp của các câu queries mà chúng ta sử dụng, qua đó cải thiện được tốc độ. Trong ví dụ này, thời gian thực thi được giảm tới hơn 95%.

-- Quét một lần
PREPARE get_user AS SELECT * FROM users WHERE id=$1; 

-- Thực thi nhiều lần  
EXECUTE get_user(1);
EXECUTE get_user(2); 
EXECUTE get_user(3);

5. Điều chỉnh random_page_cost để sử dụng Index tốt hơn

Giảm random_page_cost làm cho việc quét index được ưu tiên hơn, vì vậy hãy luôn luôn thực hiện việc scan index thay vì scan toàn bộ table. Đối với các table lớn hàng triệu dòng thì việc này thực sự hữu ích.

-- Mặc định là 4.0, giảm xuống to 2.0
SET random_page_cost=2.0;

-- Bây giờ hệ thống sẽ quét theo index thay vì toàn bộ các bảng
EXPLAIN SELECT * FROM events WHERE event_id=123456;

Việc đặt các tham số chi phí như random_page_cost và cpu_tuple_cost một cách thích hợp sẽ giúp ta lựa chọn các kế hoạch truy vấn nhanh hơn. Trong ví dụ này, thời gian thực thi giảm từ 2 phút xuống 125 ms với các tùy chọn phù hợp.

Kết luận

Còn rất nhiều kỹ thuật tối ưu hiệu năng nâng cao khác nhưng hy vọng bài viết trên có thể cung cấp và minh họa một số phương pháp tối ưu hiệu quả cho PostgreSQL. Bên cạnh đó, bạn hãy ghi nhớ luôn luôn sử dụng EXPLAIN để đo lường các sự thay đổi trước và sau khi tối ưu để đảm bảo rằng cơ sở dữ liệu của mình đang hoạt động ở hiệu suất tốt nhất nhé.

Invalid date