Blog

Thủ Thuật Cải Thiện Hiệu Suất PostgreSQL [Tất Tần Tật]

    Tham khảo các thủ thuật cải thiện hiệu suất PostgreSQL. Giúp bạn tối ưu hóa từ cấu hình hệ thống, chỉ mục, bộ nhớ, đến chiến lược tối ưu truy vấn SQL. PostgreSQL là một trong những hệ quản trị cơ sở dữ liệu mã nguồn mở phổ biến nhất. Nó được sử dụng rộng rãi trong các hệ thống yêu cầu tính toàn vẹn dữ liệu cao, khả năng mở rộng và hiệu suất mạnh mẽ. Tuy nhiên, nếu không được cấu hình và tối ưu đúng cách; thì PostgreSQL có thể gặp phải tình trạng truy vấn chậm, sử dụng tài nguyên không hiệu quả. Hãy cùng Gdata tìm hiểu ngay trong bài viết dưới đây nhé!

    Cải thiện hiệu suất PostgreSQL-min

    1. Tối ưu hóa cấu hình – Cải thiện hiệu suất PostgreSQL

    1.1 Điều chỉnh tham số work_mem

    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. Tham số work_mem quy định dung lượng bộ nhớ dành cho một thao tác sắp xếp hoặc join. Nếu giá trị quá thấp, PostgreSQL sẽ phải sử dụng disk swap, làm giảm tốc độ xử lý.

    – 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;

    Cách thiết lập để cải thiện hiệu suất PostgreSQL:

    – Xác định mức tài nguyên RAM có sẵn
    – Đặt giá trị work_mem dựa trên số lượng kết nối đồng thời
    – Cập nhật cấu hình trong postgresql.conf

    1.2 Điều chỉnh shared_buffers nhằm cải thiện hiệu suất PostgreSQL

    shared_buffers quyết định lượng bộ nhớ PostgreSQL sử dụng để lưu trữ dữ liệu trong RAM. Giá trị này nên được thiết lập từ 25-40% tổng RAM hệ thống để tăng hiệu suất.

    Cách thiết lập:

    SHOW shared_buffers;
    ALTER SYSTEM SET shared_buffers = '4GB';
    SELECT pg_reload_conf();

    1.3 Tăng tốc độ ghi với wal_buffers và checkpoint_segments

    – wal_buffers giúp cải thiện hiệu suất ghi dữ liệu bằng cách lưu trữ WAL (Write Ahead Log) trong RAM.

    – checkpoint_segments kiểm soát tần suất PostgreSQL ghi dữ liệu từ bộ nhớ vào ổ đĩa.

    Cách thiết lập:

    ALTER SYSTEM SET wal_buffers = '16MB';
    ALTER SYSTEM SET checkpoint_timeout = '10min';
    SELECT pg_reload_conf();

    2. Tối ưu hóa truy vấn SQL – Cải thiện hiệu suất PostgreSQL

    2.1 Sử dụng EXPLAIN ANALYZE kiểm tra hiệu suất truy vấn

    Lệnh EXPLAIN ANALYZE giúp phân tích chi tiết kế hoạch thực thi của một truy vấn, xác định bottleneck để tối ưu.

    Ví dụ:

    EXPLAIN ANALYZE
    SELECT * FROM orders WHERE customer_id = 1001;

    Bạn sẽ thấy thời gian thực thi, loại quét (Sequential Scan, Index Scan, Bitmap Scan) và thông tin giúp cải thiện truy vấn.

    2.2 Tạo Index để tăng tốc độ tìm kiếm – Cải thiện hiệu suất PostgreSQL

    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';

    Kiểm tra hiệu suất trước và sau khi tạo Index:

    EXPLAIN ANALYZE SELECT * FROM orders WHEREcreated_at >= '2020-01-01';

    2.3 Tránh SELECT * không cần thiết

    Sử dụng **SELECT *** sẽ khiến PostgreSQL tải toàn bộ dữ liệu, gây lãng phí tài nguyên.

    Cách tối ưu:

    SELECT order_id, order_date FROM orders WHERE customer_id = 1001;

    2.4 Sử dụng VACUUM và ANALYZE để cải thiện hiệu suất PostgreSQL

    PostgreSQL không tự động giải phóng không gian trống sau khi xóa hoặc cập nhật dữ liệu, dẫn đến fragmentation và giảm hiệu suất.

    Thực hiện VACUUM để dọn dẹp dữ liệu:

    VACUUM ANALYZE;

    Tự động hóa VACUUM bằng autovacuum:

    ALTER SYSTEM SET autovacuum = 'on';
    SELECT pg_reload_conf();

    2.5 Tinh chỉnh effective_cache_size để 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 truy vấn. 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';

    2.6 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 được sử dụng. Nhờ đó mà 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);

    2.7 Đ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 lựa chọn các 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.

    3. Quản lý Connection hiệu quả – Cải thiện hiệu suất PostgreSQL

    3.1 Giới hạn số kết nối đồng thời

    Quá nhiều kết nối đồng thời có thể làm cạn kiệt tài nguyên PostgreSQL.

    Giới hạn số kết nối:

    ALTER SYSTEM SET max_connections = '200';
    SELECT pg_reload_conf();

    3.2 Sử dụng Connection Pooling giúp cải thiện hiệu suất PostgreSQL

    Connection Pooling giúp tái sử dụng kết nối, giảm tải cho hệ thống.

    – Công cụ hỗ trợ Connection Pooling:

    +) PgBouncer

    +) PgPool-II

    – Cấu hình PgBouncer:

    [databases]
    mydb = host=127.0.0.1 port=5432 dbname=mydb    [pgbouncer]

    max_client_conn = 100

    default_pool_size = 20

    4. Tối ưu hóa Hardware và Storage nhằm cải thiện hiệu suất PostgreSQL

    4.1 Sử dụng SSD thay vì HDD

    SSD giúp tăng tốc độ đọc/ghi dữ liệu, đặc biệt quan trọng với các truy vấn có khối lượng lớn.

    4.2 Cấu hình RAID để tăng tốc độ đọc/ghi

    – RAID 0: Tăng tốc độ nhưng không bảo vệ dữ liệu

    – RAID 10: Cân bằng giữa hiệu suất và bảo mật

    5. Sao lưu và phục hồi dữ liệu – Cải thiện hiệu suất PostgreSQL

    5.1 Sao lưu bằng pg_dump

    pg_dump -U postgres -F c -b -v -f "backup.dump" mydb

    5.2 Phục hồi dữ liệu bằng pg_restore

    pg_restore -U postgres -d mydb "backup.dump"

    Lời kết

    Tối ưu hóa PostgreSQL không chỉ giúp cải thiện hiệu suất truy vấn; mà còn giúp giảm tải tài nguyên, tăng khả năng mở rộng và đảm bảo tính ổn định của hệ thống. Bạn có thể áp dụng ngay các thủ thuật trên để PostgreSQL hoạt động nhanh và hiệu quả hơn. Đừng quên theo dõi các kênh social của Gdata để được cập nhật thông tin – kiến thức công nghệ mới nhất nhé!

    Thông tin liên hệ GDATA

    Hotline: 0904 299 668

    Tổng đài: 1800 4814 – Phím 2

    Email: [email protected]

    Website: www.gdata.com.vn

    Facebook:https://www.facebook.com/gdata.com.vn

    CÔNG TY CP DỮ LIỆU TOÀN CẦU

    Địa chỉ: Tầng 03 Tòa Lạc Hồng/ 27 Lê Văn Lương, Thanh Xuân, Hà Nội

    Gdata – Tăng cường bảo mật cho doanh nghiệp của bạn! 

    Đăng ký ngay
    Nhận vàng liền tay
    Chương trình khuyến mãi
    ✖
    Hộp quà
    Ưu đãi nhận vàng

    Liên hệ với chúng tôi

    ✔

    Đăng ký thành công!

    1. Đăng ký tư vấn
    2. Zalo chat
    3. Gọi miễn phí