Posted inDatabase

Mở rộng PostgreSQL với Connection Pooling bằng PgBouncer khi tải cao

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Bắt đầu nhanh: Chặn đứng sự cố bùng nổ kết nối lúc 2 giờ sáng

Hệ thống giám sát của bạn kích hoạt cảnh báo lúc 2 giờ sáng. Latency p99 của API vừa tăng vọt từ 45ms lên 12.000ms. Trong log, bạn thấy thông báo lỗi đáng sợ: FATAL: remaining connection slots are reserved for non-replication superuser connections.

Instance PostgreSQL của bạn đã chạm trần max_connections. Do mỗi worker process ở backend tiêu tốn từ 5MB đến 10MB RAM cơ sở, server của bạn bắt đầu paging ra ổ đĩa. Các truy vấn bị đình trệ trên toàn bộ mọi service.

Đừng vội tăng gấp đôi max_connections trong postgresql.conf. Nâng từ 100 lên 1.000 kết nối chẳng khác nào mời Out-Of-Memory (OOM) killer của Linux đến dừng tiến trình cơ sở dữ liệu khi chịu tải cao. Đặt PgBouncer trước PostgreSQL hoạt động như một van giảm áp, giúp ổn định bộ nhớ và độ trễ chỉ trong vài phút.

1. Cài đặt PgBouncer

# Debian / Ubuntu
sudo apt-get update && sudo apt-get install -y pgbouncer

# RHEL / Rocky Linux
sudo dnf install -y pgbouncer

2. Cấu hình tối thiểu cho Production

Mở file /etc/pgbouncer/pgbouncer.ini và cấu hình một proxy gọn nhẹ:

[databases]
# Điều hướng lưu lượng ứng dụng đến PostgreSQL trên localhost
app_production = host=127.0.0.1 port=5432 dbname=app_production

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres, pgbouncer_admin

# Các tham số pooling
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 5

3. Cấu hình thông tin xác thực và khởi chạy dịch vụ

PgBouncer xác thực thông tin đăng nhập của người dùng qua một file tra cứu cục bộ. Bạn có thể trích xuất bản ghi người dùng hiện có trực tiếp từ PostgreSQL:

sudo -u postgres psql -d app_production -Atc \
  "SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow WHERE usename = 'app_user';" \
  | sudo tee -a /etc/pgbouncer/userlist.txt

Phân quyền chặt chẽ cho file và kích hoạt daemon:

sudo chown -R postgres:postgres /etc/pgbouncer
sudo chmod 0600 /etc/pgbouncer/userlist.txt
sudo systemctl restart pgbouncer

Chuyển cổng kết nối database của ứng dụng từ 5432 sang 6432. 5.000 kết nối từ ứng dụng gửi đến giờ đây sẽ xếp hàng trật tự và được phân phối mượt mà qua một pool cố định gồm 25 backend worker. Độ trễ lập tức giảm về mức bình thường.

Đi sâu vào chi tiết: Vì sao PostgreSQL suy giảm hiệu năng khi concurrency cao

PostgreSQL hoạt động theo kiến trúc mỗi kết nối là một tiến trình riêng biệt (process-per-connection) thay vì dùng thread. Mỗi socket từ client sẽ khiến hệ điều hành fork một tiến trình postgres chuyên trách.

Điều này tạo ra hai điểm nghẽn nghiêm trọng khi mở rộng quy mô:

  • Phình bộ nhớ (Memory bloat): 600 kết nối client chiếm khoảng 4GB đến 6GB RAM chỉ để duy trì bộ đệm socket và thông tin catalog của từng tiến trình. Lượng RAM đó bị hao hụt ngay trước khi các truy vấn kịp cấp phát work_mem cho hash join hoặc sắp xếp dữ liệu.
  • Chuyển đổi ngữ cảnh CPU (CPU context switching): Khi hàng trăm tiến trình cùng tranh chấp 8 hoặc 16 nhân CPU, Linux kernel phải tiêu tốn chu kỳ CPU để hoán đổi trạng thái tiến trình nhiều hơn là dành cho việc thực thi SQL.

Lựa chọn chế độ Pool phù hợp

PgBouncer đứng giữa ứng dụng và Postgres, đóng vai trò như một bộ dồn kênh kết nối (multiplexer) thông minh. Nó hoạt động với ba chế độ pooling riêng biệt:

  • Session Pooling: PgBouncer liên kết một kết nối server với client ngay khi đăng nhập và giữ nguyên cho đến khi ngắt kết nối. Chế độ này hỗ trợ 100% tính năng của Postgres, bao gồm temporary table và session lock. Tuy nhiên, nó hầu như không cải thiện được khả năng đồng thời nếu hàng nghìn client cùng ở trạng thái rảnh rỗi (idle).
  • Transaction Pooling (Khuyên dùng): PgBouncer chỉ mượn một kết nối database trong suốt thời gian diễn ra một transaction (BEGIN ... COMMIT). Ngay khi transaction kết thúc, PgBouncer bàn giao backend worker đó cho client tiếp theo đang chờ trong hàng đợi. Hàng nghìn client web có thể dễ dàng chia sẻ 25 đến 50 tiến trình PostgreSQL đang hoạt động. Lưu ý: các thay đổi trạng thái ở cấp session (chẳng hạn như SET timezone) sẽ không được lưu giữ qua các truy vấn.
  • Statement Pooling: Kết nối được trả về pool sau mỗi câu lệnh truy vấn đơn lẻ. Không hỗ trợ transaction nhiều câu lệnh (multi-statement). Chỉ nên sử dụng chế độ này cho các tác vụ thuần tra cứu key-value hoặc luồng phân tích dữ liệu auto-commit.

Quy trình xử lý truy vấn trong thực tế

Ở chế độ transaction pooling, các yêu cầu từ client sẽ đi qua bốn giai đoạn rõ ràng:

  1. Client mở một kết nối TCP socket duy trì dài hạn đến PgBouncer trên cổng 6432.
  2. Nếu toàn bộ 25 kết nối PostgreSQL đều đang bận, PgBouncer sẽ giữ client trong hàng đợi trên bộ nhớ RAM.
  3. Ngay khi một kết nối backend hoàn thành transaction, PgBouncer sẽ gán nó cho client đang chờ.
  4. Postgres thực thi truy vấn, trả về tập kết quả, và PgBouncer lập tức thu hồi kết nối trở lại pool.

Tinh chỉnh cho môi trường Multi-Tenant và Production

Chỉ cần một batch job hoạt động bất thường hoặc một truy vấn thiếu index cũng có thể độc chiếm toàn bộ kết nối trong pool. Bạn cần cơ chế định tuyến chi tiết để ngăn chặn tình trạng cạn kiệt kết nối (connection starvation).

Phân chia tải làm việc trên các Pool chuyên dụng

Tách biệt các lệnh gọi API người dùng nhạy cảm về độ trễ khỏi các batch worker bằng cách định nghĩa các database ảo trong /etc/pgbouncer/pgbouncer.ini:

[databases]
# Lưu lượng REST API ưu tiên cao
app_live = host=127.0.0.1 port=5432 dbname=app_production pool_mode=transaction pool_size=40

# Các background job chạy lâu dài sử dụng session pooling
app_analytics = host=127.0.0.1 port=5432 dbname=app_production pool_mode=session pool_size=5

[users]
# Giới hạn các script báo cáo tự động
report_bot = pool_mode=transaction max_user_connections=5

Xử lý Prepared Statement trong PgBouncer 1.21+

Vì transaction pooling hoán đổi các kết nối server giữa những truy vấn, trước đây prepared statement thường gây ra lỗi như prepared statement "s1" does not exist. Các ORM hiện đại (bao gồm Prisma, ActiveRecord và SQLAlchemy) thường mặc định bật tính năng này.

Bắt đầu từ phiên bản PgBouncer 1.21, bạn có thể kích hoạt prepared statement trong suốt ở cấp giao thức:

[pgbouncer]
max_prepared_statements = 100

Nếu bạn đang chạy các phiên bản PgBouncer cũ hơn, hãy tắt prepared statement trong cấu hình ORM (ví dụ: đặt pgbouncer=true trong chuỗi kết nối Prisma hoặc prepare_threshold: nil trong ActiveRecord).

Kiểm tra chỉ số Pool theo thời gian thực

PgBouncer tích hợp sẵn một console quản trị nội bộ có thể truy cập thông qua psql:

psql -p 6432 -U pgbouncer_admin -d pgbouncer

Các lệnh chẩn đoán quan trọng cần chạy khi lưu lượng tăng đột biến:

-- Theo dõi độ dài hàng đợi client so với server đang rảnh (idle)
SHOW POOLS;

-- Kiểm tra các client đang kết nối và trạng thái transaction hiện tại
SHOW CLIENTS;

-- Theo dõi lưu lượng truy vấn, lượng dữ liệu truyền tải và thời gian chờ
SHOW STATS;

Quan sát cột cl_waiting trong kết quả của SHOW POOLS. Nếu cl_waiting liên tục lớn hơn 0 trong khi sv_idle duy trì ở mức 0 suốt thời gian dài, hãy tăng default_pool_size theo từng nấc từ 5 đến 10.

Kinh nghiệm thực chiến trong môi trường Production

1. Khớp kích thước Pool với giới hạn Core CPU

Cấu hình default_pool_size = 500 trên một máy chủ 8 core sẽ dẫn đến xung đột khóa (lock contention) nghiêm trọng. Nhiều kết nối hơn hiếm khi đồng nghĩa với thông lượng cao hơn. Một công thức kinh nghiệm đã được chứng minh để tính số backend hoạt động tối ưu là:

Max Active Backends = ((CPU_cores * 2) + effective_spindle_count)

Đối với một database server chuyên dụng 16 core với ổ lưu trữ NVMe tốc độ cao, duy trì 35 đến 50 tiến trình PostgreSQL hoạt động sẽ mang lại độ trễ p99 thấp hơn đáng kể so với 400 kết nối không được kiểm soát đang tranh giành bộ nhớ và các đường cache L3.

2. Làm sạch an toàn dữ liệu payload nhập vào

Các đợt hotfix và vá lỗi cơ sở dữ liệu khẩn cấp thường yêu cầu tải lên lượng dữ liệu lớn hoặc kiểm tra payload. Khi chuyển đổi các bản dump CSV sang JSON trước khi chạy cập nhật hàng loạt có chủ đích, hãy sử dụng tiện ích chạy trên trình duyệt như toolcraft.app/vi/tools/data/csv-to-json. Nhờ cơ chế phân tích cú pháp hoàn toàn cục bộ bằng WebAssembly, dữ liệu định danh cá nhân (PII) nhạy cảm của khách hàng sẽ không bao giờ rời khỏi trình duyệt của bạn, giúp ngăn ngừa rò rỉ dữ liệu trong các bước di chuyển dữ liệu thủ công.

3. Các giới hạn Timeout và Giới hạn File Descriptor thiết yếu

Ngăn chặn các socket mồ côi âm thầm làm cạn kiệt tài nguyên hệ điều hành:

  • server_idle_timeout = 600: Đóng các kết nối server đã ở trạng thái idle trong 10 phút, giải phóng bộ nhớ trả lại cho hệ điều hành.
  • query_timeout = 30: Tự động hủy các truy vấn bị treo quá 30 giây, bảo vệ backend worker khỏi những thao tác quét tuần tự (sequential scan) mất kiểm soát.
  • Giới hạn file descriptor: PgBouncer quản lý hàng nghìn socket đồng thời. Thêm các dòng sau vào /etc/security/limits.conf:
postgres soft nofile 65536
postgres hard nofile 65536

Áp dụng thay đổi bằng lệnh sysctl -p để đảm bảo daemon xử lý tốt các đợt bùng nổ lưu lượng mà không chạm ngưỡng giới hạn descriptor của hệ điều hành.

Share:
Tags: