Gần đây, tôi đã tìm hiểu về cài đặt bộ nhớ PostgreSQL, tìm kiếm cách để tinh chỉnh chúng và tăng cường hiệu suất. Sau khi xem xét nhiều tài liệu và thực hiện một số thử nghiệm, tôi đã tìm ra một vài cài đặt quan trọng có thể tạo ra sự khác biệt lớn.

Nếu bạn đang chạy PostgreSQL và muốn tối ưu hóa cách nó sử dụng bộ nhớ, đây là một vài cài đặt quan trọng mà bạn nên chú ý.

Cài Đặt Bộ Nhớ Chính Cần Tinh Chỉnh

Hãy cùng phân tích những cài đặt bộ nhớ PostgreSQL quan trọng nhất mà bạn nên xem xét điều chỉnh.

  1. shared_buffers

Hãy nghĩ về shared_buffers như là kho bộ nhớ riêng của PostgreSQL, nơi nó lưu trữ dữ liệu thường xuyên được truy cập. Ý tưởng là lưu trữ càng nhiều dữ liệu trong bộ nhớ càng tốt để giảm thiểu việc đọc đĩa tốn kém.

Cài Đặt Được Khuyến Nghị:

Hầu hết các chuyên gia gợi ý đặt giá trị này khoảng 25% tổng RAM của hệ thống.

Ví dụ, nếu bạn có 16GB RAM, đặt nó khoảng 4GB là một điểm khởi đầu tốt.

  1. work_mem

Mỗi khi PostgreSQL xử lý các truy vấn liên quan đến sắp xếp, kết hợp hoặc nhóm dữ liệu, nó sẽ sử dụng work_mem. Cài đặt này kiểm soát lượng bộ nhớ mà mỗi hoạt động nhận được trước khi bắt đầu ghi các tệp tạm thời vào đĩa.

Ví dụ:

  • Nếu work_mem = 50MB và truy vấn của bạn có 5 tác vụ sắp xếp, nó có thể sử dụng 50MB x 5 = 250MB bộ nhớ.
  • Bây giờ hãy tưởng tượng có 10 người dùng chạy các truy vấn tương tự – đó là 250MB x 10 = 2.5GB bộ nhớ đang được sử dụng.

Mẹo:

  • Đặt nó quá thấp, và các truy vấn có thể chậm lại do sử dụng đĩa.
  • Đặt nó quá cao, và bạn có thể nhanh chóng hết bộ nhớ, đặc biệt là với nhiều người dùng.

Đôi khi, các giá trị work_mem lớn hơn thực sự có thể làm chậm mọi thứ vì PostgreSQL phải quản lý các khối bộ nhớ lớn.

  1. effective_cache_size

Cài đặt này thì hơi khác – nó không thực sự phân bổ bộ nhớ mà chỉ cho PostgreSQL biết nó nghĩ nó có thể sử dụng bao nhiêu bộ nhớ từ bộ nhớ đệm của hệ điều hành. PostgreSQL sử dụng giá trị này để quyết định xem nó có nên sử dụng chỉ mục hay thực hiện quét tuần tự.

Cài đặt sai:

  • Quá thấp? PostgreSQL có thể tránh sử dụng chỉ mục, nghĩ rằng bộ nhớ đang khan hiếm.
  • Quá cao? PostgreSQL có thể trở nên quá lạc quan và đưa ra quyết định sai lầm.

Một quy tắc chung: Đặt giá trị này khoảng 50-75% tổng RAM của bạn, đặc biệt nếu PostgreSQL là khối lượng công việc chính trên máy.

  1. maintenance_work_mem

Cài đặt này liên quan đến lượng bộ nhớ mà PostgreSQL có thể sử dụng cho các tác vụ như VACUUM, CREATE INDEX và các tác vụ bảo trì khác. Đặt giá trị này cao hơn có thể tăng tốc các hoạt động này mà không làm chậm hoạt động cơ sở dữ liệu bình thường.

Cài Đặt Được Đề Xuất:

Nếu bạn có đủ không gian, hãy tăng lên 1GB hoặc hơn để các tác vụ bảo trì diễn ra nhanh chóng.

Cách Thay Đổi Các Cài Đặt Bộ Nhớ

PostgreSQL cho phép thay đổi các cài đặt bộ nhớ theo nhiều cách khác nhau, tùy thuộc vào yêu cầu và mức độ chấp nhận rủi ro của bạn. Dưới đây là những cách hiệu quả nhất:

  1. Thay Đổi Cài Đặt Bộ Nhớ Toàn Cục

Để thay đổi vĩnh viễn trên tất cả các phiên và truy vấn, hãy chỉnh sửa tệp cấu hình PostgreSQL (postgresql.conf) hoặc sử dụng ALTER SYSTEM:


ALTER SYSTEM SET work_mem = '500MB';
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '20GB';
SELECT pg_reload_conf();  -- Áp dụng thay đổi mà không cần khởi động lại
  1. Thay Đổi Cài Đặt Theo Phiên

Để điều chỉnh tạm thời chỉ ảnh hưởng đến kết nối hiện tại:

SET work_mem = '100MB';
SET effective_cache_size = '16GB';

Ví Dụ Thực Tế Của Tôi Về Tuning Bộ Nhớ PostgreSQL

Scenario ##o

Một cơ sở dữ liệu PostgreSQL 10 chạy trên một máy chủ 30GB RAM, với thường xuyên 15GB RAM khả dụng, đang gặp phải hiệu suất truy vấn xuất CSV chậm khi xử lý các giao dịch tài chính. Một truy vấn phức tạp kết hợp bốn bảng lớn mất 33 giây để tạo ra kết quả:


SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;

Kích thước bảng:

  • finance_transaction (~1 triệu hàng)
  • transaction_log (~1 triệu hàng)
  • transaction_audit_history (~4 triệu hàng)
  • billing_record (~1 triệu hàng)

Cài đặt bộ nhớ mặc định:

Cài đặtGiá trị Mặc định
shared_buffers128MB
work_mem4MB
maintenance_work_mem64MB
effective_cache_size4GB

Kế Hoạch Thực Thi Truy Vấn (EXPLAIN ANALYZE)

Gather  (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=26603.645..33465.648 rows=4847517 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Hash Join  (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=26817.703..29038.177 rows=969503 loops=5)
        Hash Cond: (ft.id = tah.finance_transaction)
        ->  Hash Join  (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=18471.362..19081.998 rows=259931 loops=5)
              Hash Cond: (br.id = ft.billing_record)
              ->  Parallel Seq Scan on billing_record br  (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..42.297 rows=268393 loops=5)
              ->  Hash  (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=18468.587..18468.589 rows=1299656 loops=5)
                    Buckets: 4096  Batches: 512  Memory Usage: 708kB
                    ->  Hash Join  (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=9094.933..15042.241 rows=1300948 loops=5)
                          Hash Cond: (tl.id = ft.transaction_log)
                          ->  Seq Scan on transaction_log tl  (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.019..133.507 rows=1564453 loops=5)
                          ->  Hash  (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=9092.767..9092.768 rows=1300948 loops=5)
                                Buckets: 16384  Batches: 128  Memory Usage: 2199kB
                                ->  Seq Scan on finance_transaction ft  (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.005..143.568 rows=1300948 loops=5)
        ->  Hash  (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=8267.360..8267.360 rows=4852654 loops=5)
              Buckets: 32768  Batches: 512  Memory Usage: 2059kB
              ->  Seq Scan on transaction_audit_history tah  (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.020..789.242 rows=4852654 loops=5)
Planning time: 0.346 ms
Execution time: 33586.541 ms

Phân Tích Kế Hoạch Thực Thi Truy Vấn:

Vấn ĐềQuan Sát Từ EXPLAIN ANALYZENguyên Nhân Có ThểGiải Pháp
Tràn Đĩa Trong Hash JoinsBuckets: 4096 → Batches: 512 Memory Usage: 708kBwork_mem quá thấp (mặc định 4MB)Tăng work_mem lên 300MB ALTER SYSTEM SET work_mem = '300MB';
I/O Cao Từ Quét Tuần TựQuét Tuần Tự Song Song trên billing_record, finance_transaction và transaction_logshared_buffers quá thấp (mặc định 128MB)Tăng shared_buffers lên 8GB ALTER SYSTEM SET shared_buffers = '8GB';
Trình Lập Kế Hoạch Truy Vấn Đánh Giá Thấp RAM Có SẵnSử dụng nhiều quét tuần tự thay vì quét chỉ mụceffective_cache_size quá thấp (mặc định 4GB)Tăng effective_cache_size lên 20GB ALTER SYSTEM SET effective_cache_size = '20GB';

Sau khi tăng cường cài đặt bộ nhớ, đây là kết quả EXPLAIN ANALYZE:

Gather  (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=16228.246..23884.145 rows=4847517 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Hash Join  (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=17226.338..20072.376 rows=969503 loops=5)
        Hash Cond: (ft.id = tah.finance_transaction)
        ->  Hash Join  (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=14289.482..15561.150 rows=259931 loops=5)
              Hash Cond: (br.id = ft.billing_record)
              ->  Parallel Seq Scan on billing_record br  (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..37.096 rows=268393 loops=5)
              ->  Hash  (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=14287.210..14287.212 rows=1299656 loops=5)
                    Buckets: 524288  Batches: 8  Memory Usage: 46914kB
                    ->  Hash Join  (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=3728.706..6986.704 rows=1300948 loops=5)
                          Hash Cond: (tl.id = ft.transaction_log)
                          ->  Seq Scan on transaction_log tl  (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.018..123.814 rows=1564453 loops=5)
                          ->  Hash  (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=3704.309..3704.309 rows=1300948 loops=5)
                                Buckets: 2097152  Batches: 2  Memory Usage: 151889kB
                                ->  Seq Scan on finance_transaction ft  (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.018..139.211 rows=1300948 loops=5)
        ->  Hash  (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=2927.466..2927.466 rows=4852654 loops=5)
              Buckets: 2097152  Batches: 4  Memory Usage: 246002kB
              ->  Seq Scan on transaction_audit_history tah  (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.025..630.852 rows=4852654 loops=5)
Planning time: 0.341 ms
Execution time: 24013.565 ms

So Sánh Hiệu Suất: Trước và Sau Khi Tăng Cường Cài Đặt Bộ Nhớ

Chỉ SốTrước (Cài Đặt Mặc Định)Sau (Cài Đặt Bộ Nhớ Tối Ưu)Cải Thiện
Tổng Thời Gian Thực Thi33.58s24.01s⬇ ~28.5% Nhanh Hơn (~9.5s tiết kiệm)
Hash Join (ft.id = tah.finance_transaction)29.03s20.07s⬇ ~9s Nhanh Hơn
Hash Join (br.id = ft.billing_record)19.08s15.56s⬇ ~3.5s Nhanh Hơn
Batches Hash (ft.transaction_log)128 Batches2 Batches✅ Giảm Đáng Kể Trong Tràn Đĩa
Batches Hash (transaction_audit_history)512 Batches4 Batches✅ Giảm Sử Dụng Đĩa

Chọn Cài Đặt Bộ Nhớ Đúng: Cách Tiếp Cận Từng Trường Hợp

Tuning cài đặt bộ nhớ PostgreSQL không phải là một giải pháp phù hợp cho tất cả. Nó phụ thuộc vào kích thước cơ sở dữ liệu của bạn, số lượng người dùng đồng thời và mẫu truy vấn. Tìm kiếm work_mem, shared_buffers, và effective_cache_size phù hợp.

  • Nếu máy chủ của bạn có nhiều RAM trống, bạn có thể phân bổ nhiều bộ nhớ hơn cho PostgreSQL.
  • Nếu bạn có nhiều người dùng đồng thời, việc phân bổ quá nhiều bộ nhớ cho mỗi truy vấn có thể gây ra sự không ổn định cho hệ thống.
  • Chạy EXPLAIN ANALYZE trên các truy vấn chậm để xác định tràn đĩa và sự không hiệu quả trong hash join.

Nếu bạn muốn kiểm tra các thay đổi một cách an toàn mà không ảnh hưởng đến toàn bộ hệ thống, áp dụng cài đặt cho từng truy vấn thay vì toàn cục:

✅ Thay đổi cài đặt cho một truy vấn nặng:

SET LOCAL work_mem = '300MB';
SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;

Kết Luận

Tuning cài đặt bộ nhớ PostgreSQL có thể tạo ra một sự khác biệt lớn trong hiệu suất, giúp các truy vấn của bạn chạy nhanh hơn và giảm thiểu I/O đĩa không cần thiết. Chìa khóa là tìm ra sự cân bằng đúng dựa trên tải cơ sở dữ liệu của bạn, RAM có sẵn và cách mà các truy vấn của bạn hoạt động.

Các Điểm Chính:

  • Hiểu rõ khối lượng công việc của bạn – Đừng chỉ tăng cường cài đặt bộ nhớ một cách mù quáng; hãy phân tích cách mà các truy vấn của bạn đang chạy trước.
  • Sử dụng EXPLAIN ANALYZE – Điều này sẽ giúp bạn phát hiện các vấn đề như tràn đĩa và các phép nối không hiệu quả.
  • Tinh chỉnh work_mem một cách cẩn thận – Giá trị cao hơn có thể làm cho các phép nối và sắp xếp nhanh hơn, nhưng đặt quá cao có thể làm tiêu tốn hết RAM của bạn.
  • Thay đổi cài đặt ở mức độ phù hợp – Sử dụng cài đặt toàn cầu cho những cải thiện tổng thể, cài đặt theo phiên cho các điều chỉnh tạm thời, và cài đặt theo truy vấn cho các hoạt động thực sự nặng.
  • Theo dõi tác động – Kiểm tra nhật ký, theo dõi hiệu suất và điều chỉnh khi cần.

Cuối cùng, tuning PostgreSQL là một phần khoa học, một phần thử nghiệm. Bắt đầu từ những thay đổi nhỏ, theo dõi kết quả và điều chỉnh khi cần. Với các cài đặt đúng, cơ sở dữ liệu của bạn sẽ chạy mượt mà, nhanh hơn và hiệu quả hơn rất nhiều. 🚀