Các tham số quan trọng nhất trong Postgresql cấu hình trong file postgresql.conf

Để cấu hình thay đổi các tham số này, chúng ta có thể sửa trực tiếp trong file postgresql.conf nằm trong thư mục data hoặc thực hiện alter system trong psql.

Xem các tham số trong psql:

select name, setting, context from pg_settings order by context;

shared_buffers:

• shared_buffers nó rất quan trọng, xác định dung lượng bộ nhớ được sử dụng để lưu trữ dữ liệu đệm (buffer) của PostgreSQL. Dữ liệu được đọc từ đĩa hoặc ghi vào đĩa sẽ được lưu trữ trong shared buffers trước khi được trả về cho các ứng dụng hoặc ghi vào đĩa. Giá trị mặc định của shared_buffers là 128MB. Tham số này sẽ ảnh hưởng trực tiếp đến hiệu năng của CSDL.
• Thông thường, giá trị shared_buffers nên được đặt thành khoảng 25% đến 35% tổng dung lượng RAM của hệ thống, nhưng điều này có thể thay đổi tùy thuộc vào nhu cầu và cấu hình cụ thể của ứng dụng. (Ví dụ: RAM = 100GB => SB = 100*0.35 = 35GB)
• Khi điều chỉnh shared_buffers, hãy nhớ rằng nó cũng ảnh hưởng đến sự sử dụng bộ nhớ của hệ thống và các tham số khác như work_mem và maintenance_work_mem.

huge_pages: là một tùy chọn cấu hình cho phép sử dụng huge pages trong hệ thống bộ nhớ của Linux. Huge_pages là các trang bộ nhớ có kích thước lớn hơn so với trang bình thường, thường là 2 MB hoặc 1 GB. Sử dụng Huge_pages có thể cải thiện hiệu suất và giảm bớt overhead của bộ nhớ, đặc biệt là cho các ứng dụng có yêu cầu bộ nhớ lớn như cơ sở dữ liệu.

max_prepared_transactions là một tham số cấu hình trong PostgreSQL xác định số lượng kết nối mà có thể chuẩn bị trước (prepare) các giao dịch. Khi tăng max_prepared_transactions, PostgreSQL sẽ có thể quản lý nhiều giao dịch chuẩn bị trước hơn, nhưng điều này cũng tăng sử dụng tài nguyên hệ thống, đặc biệt là bộ nhớ.

work_mem trong PostgreSQL xác định bộ nhớ được sử dụng bởi mỗi kết nối hệ thống khi thực hiện các thao tác sắp xếp (sort) hoặc thực hiện các thao tác trộn (hash join) trong bộ nhớ.

• Giá trị mặc định của work_mem là 4MB.
• Khi kết nối tăng lên thì work_mem cũng tăng theo: total = work_mem x connection
• Dung lượng khuyến nghị là 10-25% dung lượng RAM tổng
• Công thức tính Work_mem = RAM * 0.2/max_connection (vi du: RAM = 100GB, max_connection = 50 => work_mem = 100*0.2/50 = 0.4GB khoảng 400MB)

maintenance_work_mem trong PostgreSQL xác định kích thước bộ nhớ được sử dụng trong quá trình thực hiện các hoạt động bảo trì, chẳng hạn như tạo chỉ mục mới, reindex, vacuum, và vacuum full.

• Giá trị mặc định của maintenance_work_mem là 64MB.
• Nên đặt tối đa là 15-25% RAM

autovacuum_work_mem trong PostgreSQL xác định kích thước bộ nhớ được sử dụng trong quá trình thực hiện các hoạt động autovacuum.

• Giá trị mặc định của autovacuum_work_mem là -1, nghĩa là nó sẽ sử dụng giá trị của work_mem cho autovacuum.
• Autovacuum là quá trình tự động thực hiện các hoạt động vacuum để giảm thiểu sự phân mảnh và tái sử dụng không gian trong cơ sở dữ liệu PostgreSQL. Kích thước bộ nhớ được sử dụng cho autovacuum có thể ảnh hưởng đến hiệu suất của cơ sở dữ liệu.

temp_file_limit xác định giới hạn kích thước tối đa cho các tệp tạm thời (temporary files) được tạo bởi mỗi phiên làm việc (session). Giới hạn này áp dụng cho các tệp tạm thời được tạo trong quá trình thực thi của các câu lệnh SQL.

• Giá trị mặc định của temp_file_limit là -1, cho phép tạo các tệp tạm thời mà không có giới hạn kích thước.
• Nếu temp_file_limit được đặt là một số dương, nó sẽ giới hạn kích thước của các tệp tạm thời được tạo bởi mỗi phiên làm việc.

vacuum_cost_delay là một tham số cấu hình liên quan đến hoạt động VACUUM. VACUUM là một quá trình bảo trì cơ sở dữ liệu cần thiết để loại bỏ các hàng chết (dead tuples) và làm sạch không gian trống, giúp cơ sở dữ liệu hoạt động hiệu quả hơn. Tuy nhiên, VACUUM có thể tiêu tốn nhiều tài nguyên hệ thống và ảnh hưởng đến hiệu suất của các hoạt động khác.

Tham số “vacuum_cost_delay” giúp kiểm soát tác động của quá trình VACUUM bằng cách thiết lập thời gian trì hoãn giữa các hoạt động VACUUM. Điều này giúp giảm tải trên hệ thống khi VACUUM đang chạy, đặc biệt là trên các hệ thống đang hoạt động nặng nề. Cụ thể, “vacuum_cost_delay” xác định số lượng thời gian (tính bằng milliseconds) mà quá trình VACUUM sẽ tạm dừng sau khi đạt đến một ngưỡng chi phí nhất định.

Cách hoạt động của vacuum_cost_delay

1. vacuum_cost_delay: Thời gian trì hoãn sau khi đạt đến ngưỡng chi phí, mặc định là 0 milliseconds (nghĩa là không trì hoãn).
2. vacuum_cost_page_hit: Chi phí cho mỗi lần đọc một trang từ bộ nhớ (cache), mặc định là 1.
3. vacuum_cost_page_miss: Chi phí cho mỗi lần đọc một trang không có trong bộ nhớ (cache miss), mặc định là 10.
4. vacuum_cost_page_dirty: Chi phí cho mỗi lần ghi một trang bẩn vào đĩa, mặc định là 20.
5. vacuum_cost_limit: Ngưỡng chi phí tổng hợp mà khi đạt đến thì quá trình VACUUM sẽ tạm dừng và thực hiện trì hoãn, mặc định là 200.

bgwriter_delay là một tham số cấu hình liên quan đến hoạt động của background writer (bgwriter), một tiến trình tự động giúp cải thiện hiệu suất của hệ thống cơ sở dữ liệu bằng cách ghi các trang bẩn (dirty pages) từ bộ nhớ đệm (shared buffer) ra đĩa theo chu kỳ. Việc này giúp giảm tải ghi đĩa đột ngột khi các trang bẩn cần được ghi ra đĩa trong các hoạt động khác, như khi thực hiện các lệnh CHECKPOINT.

Cách hoạt động của bgwriter_delay

“bgwriter_delay” xác định khoảng thời gian nghỉ (tính bằng milliseconds) giữa các chu kỳ ghi của bgwriter. Giá trị mặc định là 200 milliseconds, có nghĩa là bgwriter sẽ nghỉ 200 milliseconds sau mỗi lần kiểm tra và ghi các trang bẩn.

Các tham số liên quan

Ngoài “bgwriter_delay”, còn có một số tham số khác liên quan đến hoạt động của bgwriter mà bạn có thể cần quan tâm:

1. bgwriter_lru_maxpages: Số trang tối đa mà bgwriter sẽ ghi ra đĩa trong mỗi chu kỳ. Giá trị mặc định là 100.
2. bgwriter_lru_multiplier: Hệ số nhân để xác định số trang cần ghi dựa trên số trang bẩn hiện có. Giá trị mặc định là 2.0.

max_worker_processes xác định số lượng tối đa các tiến trình làm việc (worker processes) mà cơ sở dữ liệu có thể sử dụng. Tham số này ảnh hưởng đến khả năng mở rộng và thực hiện các tác vụ song song trong PostgreSQL, chẳng hạn như parallel query execution (thực thi truy vấn song song), logical replication workers (các tiến trình sao chép logic), và các background worker processes khác.

Chi tiết về max_worker_processes

• Mặc định: Giá trị mặc định của “max_worker_processes” thường là 8.
• Giới hạn tối đa: Giới hạn tối đa có thể được đặt phụ thuộc vào hệ điều hành và cấu hình cụ thể của PostgreSQL, nhưng thông thường là khá lớn (hàng trăm hoặc hơn).

Ý nghĩa và ứng dụng

• Parallel Query Execution: Khi chạy các truy vấn phức tạp, PostgreSQL có thể phân chia công việc thành nhiều tiến trình con (worker processes) để thực hiện song song, cải thiện hiệu suất tổng thể.
• Logical Replication: Số lượng tiến trình sao chép logic có thể chạy đồng thời.
• Background Workers: Một số tiến trình nền khác như autovacuum workers hoặc tiến trình được định nghĩa bởi người dùng có thể cần sử dụng worker processes.

Write Ahead Log (WAL) là một cơ chế quan trọng trong PostgreSQL để đảm bảo tính nhất quán và khả năng phục hồi dữ liệu. WAL ghi lại mọi thay đổi đối với dữ liệu vào một log trước khi các thay đổi đó được áp dụng lên dữ liệu thực tế trên đĩa. Điều này đảm bảo rằng, trong trường hợp hệ thống gặp sự cố (như mất điện hoặc lỗi phần mềm), cơ sở dữ liệu có thể khôi phục về trạng thái nhất quán cuối cùng bằng cách phát lại các bản ghi trong WAL.

Cách hoạt động của Write Ahead Log

1. Ghi trước khi thực hiện (Write Ahead): Trước khi thực hiện bất kỳ thay đổi nào lên dữ liệu chính, PostgreSQL ghi lại các thay đổi này vào WAL. Điều này đảm bảo rằng luôn có một bản ghi của các thay đổi dự kiến ngay cả khi hệ thống gặp sự cố trước khi ghi các thay đổi đó lên dữ liệu chính.
2. Phát lại log (Replay Log): Trong trường hợp phục hồi sau sự cố, PostgreSQL có thể sử dụng các bản ghi WAL để phát lại và áp dụng lại các thay đổi chưa được ghi lên dữ liệu chính, đảm bảo cơ sở dữ liệu trở về trạng thái nhất quán.

Lợi ích của Write Ahead Log

• Tính nhất quán: WAL đảm bảo rằng các thay đổi đối với cơ sở dữ liệu được ghi lại một cách tuần tự và đầy đủ, giúp duy trì tính nhất quán của dữ liệu.
• Khả năng phục hồi: Nếu hệ thống gặp sự cố, cơ sở dữ liệu có thể sử dụng WAL để khôi phục về trạng thái nhất quán cuối cùng bằng cách phát lại các thay đổi đã được ghi lại trong log.
• Hiệu suất: Việc ghi log tuần tự thường nhanh hơn so với việc ghi ngẫu nhiên lên dữ liệu chính. WAL cho phép PostgreSQL trì hoãn việc ghi các thay đổi lên dữ liệu chính, giúp cải thiện hiệu suất.

Cấu hình Write Ahead Log

Có một số tham số cấu hình trong PostgreSQL liên quan đến WAL mà quản trị viên cơ sở dữ liệu có thể điều chỉnh để tối ưu hóa hiệu suất và đảm bảo tính toàn vẹn của dữ liệu.

Tham số cấu hình quan trọng

• wal_level: Xác định mức độ thông tin được ghi vào WAL. Các giá trị có thể là minimal, replica, và logical.
• max_wal_size: Xác định kích thước tối đa của tất cả các tệp WAL.
• min_wal_size: Xác định kích thước tối thiểu của tất cả các tệp WAL.
• wal_buffers: Kích thước bộ đệm WAL.
• checkpoint_timeout: Thời gian tối đa giữa các lần tạo checkpoint.
• checkpoint_completion_target: Xác định phần trăm thời gian từ khi bắt đầu checkpoint đến khi checkpoint phải hoàn thành.

checkpoints đóng vai trò quan trọng trong quá trình ghi dữ liệu xuống ổ đĩa để đảm bảo tính nhất quán và hiệu suất của cơ sở dữ liệu. Checkpoint là quá trình ghi lại tất cả các thay đổi từ bộ nhớ đệm (cache) xuống ổ đĩa và cập nhật các thông tin điểm kiểm tra (checkpoint record), từ đó giảm thiểu việc phải phục hồi dữ liệu sau khi hệ thống bị sự cố.

Dưới đây là một số khái niệm liên quan đến checkpoints trong PostgreSQL:

1. Checkpoint Parameters: Có một số tham số trong PostgreSQL điều chỉnh cách hoạt động của checkpoints, bao gồm checkpoint_timeout, checkpoint_completion_target, và max_wal_size. Các tham số này quyết định tần suất và cách thức thực hiện checkpoints.
2. WAL (Write-Ahead Logging) Checkpoint: WAL checkpoint là một loại checkpoint trong đó PostgreSQL ghi các thông tin về transaction log (WAL) và cập nhật các thông tin WAL trong quá trình ghi dữ liệu xuống ổ đĩa.
3. Asynchronous Checkpoint: Asynchronous checkpoint là loại checkpoint mà PostgreSQL không chờ đợi quá trình ghi dữ liệu xuống ổ đĩa hoàn tất trước khi tiếp tục các hoạt động khác.
4. Synchronous Checkpoint: Synchronous checkpoint là loại checkpoint mà PostgreSQL chờ đợi quá trình ghi dữ liệu xuống ổ đĩa hoàn tất trước khi tiếp tục các hoạt động khác. Điều này đảm bảo rằng dữ liệu đã được ghi xuống ổ đĩa trước khi tiếp tục thực hiện các giao dịch tiếp theo.
5. Triggering a Checkpoint: Checkpoints có thể được kích hoạt bởi một số sự kiện như thời gian (timeout), kích thước của transaction log (WAL), hoặc theo yêu cầu của người quản trị cơ sở dữ liệu.

checkpoint_timeout là một trong các tham số cấu hình quan trọng được sử dụng để điều chỉnh tần suất thực hiện checkpoints. Tham số này xác định khoảng thời gian giữa các checkpoints tự động, tính bằng giây. Khi checkpoint_timeout được đặt, PostgreSQL sẽ thực hiện checkpoints sau mỗi khoảng thời gian được chỉ định, dù có hay không có các hoạt động ghi dữ liệu đến bộ nhớ đệm trong thời gian đó. Điều này giúp đảm bảo rằng dữ liệu được ghi xuống ổ đĩa định kỳ, giảm thiểu thời gian cần thiết để phục hồi dữ liệu sau khi xảy ra sự cố.

Giá trị mặc định của checkpoint_timeout trong PostgreSQL là 5 phút. Tuy nhiên, giá trị này có thể được điều chỉnh để phù hợp với nhu cầu cụ thể của ứng dụng và môi trường sử dụng.

max_wal_size là một trong các tham số cấu hình quan trọng liên quan đến việc quản lý và điều chỉnh kích thước của transaction log (WAL – Write-Ahead Logging). WAL là một cơ chế quan trọng để đảm bảo tính nhất quán của dữ liệu trong PostgreSQL và cũng là cơ sở cho quá trình phục hồi sau sự cố. max_wal_size xác định kích thước tối đa mà các tập tin WAL có thể đạt được trước khi PostgreSQL tự động thực hiện một checkpoint để ghi lại dữ liệu từ bộ nhớ đệm xuống ổ đĩa. Khi kích thước tổng của các tập tin WAL vượt qua ngưỡng này, PostgreSQL sẽ bắt đầu thực hiện checkpoints để giữ cho kích thước của các tập tin WAL trong phạm vi chấp nhận được.

min_wal_size xác định kích thước tối thiểu mà các tập tin WAL (Write-Ahead Logs) phải đạt được trước khi PostgreSQL bắt đầu tiến hành quá trình checkpoint.

Khi kích thước tổng của các tập tin WAL giảm xuống dưới ngưỡng này, PostgreSQL sẽ bắt đầu thực hiện checkpoint để đảm bảo rằng có đủ dữ liệu được ghi xuống ổ đĩa để đảm bảo tính nhất quán của hệ thống.

Tại sao min_wal_size lại quan trọng?

• Ngăn ngừa xóa WAL đột ngột: Giả sử hệ thống của bạn không hoạt động trong một khoảng thời gian, WAL sẽ tiếp tục được ghi trong thời gian đó. Nếu min_wal_size quá nhỏ, có khả năng các segment cũ hơn checkpoint sẽ bị xóa sạch trước khi hệ thống hoạt động trở lại. Điều này có thể dẫn đến việc PostgreSQL không thể phục hồi dữ liệu đầy đủ trong trường hợp xảy ra sự cố.
• Đảm bảo đủ WAL cho hoạt động đột ngột: min_wal_size giúp đảm bảo rằng luôn có đủ dung lượng WAL để ghi ngay cả khi có đột biến về lượng dữ liệu được ghi. Điều này ngăn ngừa tình trạng ghi WAL bị tràn và potentially data loss (mất dữ liệu tiềm ẩn).

archive_mode là một trong các tham số cấu hình quan trọng liên quan đến việc quản lý và điều chỉnh cách thức lưu trữ transaction log (WAL – Write-Ahead Logging). Khi archive_mode được kích hoạt, PostgreSQL sẽ tự động ghi các bản sao của các tập tin WAL đã được ghi vào một vị trí được chỉ định, thường là trong một thư mục hoặc một hệ thống lưu trữ ngoài (như Amazon S3 hoặc Azure Blob Storage). Việc lưu trữ các bản sao WAL này là quan trọng trong việc sao lưu và phục hồi dữ liệu, đặc biệt là trong trường hợp cần phục hồi từ một sự cố hoặc thảm họa. Nếu archive_mode được kích hoạt, bạn cũng cần cấu hình thêm archive_command, đây là lệnh hoặc tập lệnh được PostgreSQL sử dụng để ghi các bản sao của các tập tin WAL vào vị trí lưu trữ đã chỉ định.

autovacuum là một tính năng quan trọng được thiết kế để tự động quản lý và thực hiện các hoạt động vacuum trên các bảng trong cơ sở dữ liệu. Vacuum là quá trình loại bỏ các hàng đã bị xóa hoặc đã bị cập nhật từ bảng và giải phóng không gian đã được chiếm dụng bởi các hàng không sử dụng.

Autovacuum hoạt động dựa trên các tham số cấu hình và các mức độ hoạt động của bảng. Nó sẽ tự động quét các bảng và quyết định khi nào cần thiết phải thực hiện hoạt động vacuum để giảm thiểu sự phân mảnh và tiêu tốn không gian lưu trữ không cần thiết. Các hoạt động vacuum được thực hiện bởi autovacuum bao gồm:

1. VACUUM: Loại bỏ các hàng đã bị xóa và giải phóng không gian đã chiếm dụng bởi các hàng đã bị xóa.
2. ANALYZE: Cập nhật thống kê của các bảng để giúp PostgreSQL tạo ra kế hoạch thực hiện truy vấn hiệu quả hơn.

Autovacuum làm việc ẩn sau hậu cảnh và không yêu cầu sự can thiệp của người quản trị cơ sở dữ liệu để kích hoạt hoặc điều chỉnh nó. Tuy nhiên, người quản trị có thể cấu hình các tham số autovacuum như autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor, autovacuum_naptime, và autovacuum_vacuum_cost_delay để điều chỉnh cách thức hoạt động của autovacuum tùy thuộc vào môi trường và yêu cầu cụ thể của hệ thống.