Connection pool: HikariCP, max_connections và PgBouncer

shop-app đã có schema tốt và index đúng. Lượng user tăng, giờ nút thắt chuyển sang chỗ ít ai để ý: kết nối tới database.

1. Bài toán

Giờ cao điểm, shop-app có 1k user hoạt động cùng lúc. Mỗi request cần vài query. Nếu mỗi query mở một connection mới:

Request → TCP handshake → TLS → xác thực → PostgreSQL fork process mới → query → đóng
           <--------------- vài ms tới vài chục ms ----------------->   <1ms

2. Vấn đề

• Mở connection đắt hơn chạy query đơn giản rất nhiều.
• PostgreSQL dùng mô hình một process cho mỗi connection (xem bài 6.2). Mỗi process tốn RAM, và có giới hạn max_connections (mặc định 100).
• Nhiều connection hơn không có nghĩa là nhanh hơn: CPU chỉ có vài core, ổ đĩa có giới hạn. Quá nhiều connection active → context switch, tranh lock, tất cả cùng chậm.

3. Giải pháp: connection pool

Mở sẵn một số connection, giữ lại, cho mượn rồi trả. Spring Boot mặc định dùng HikariCP.

Công thức sizing (từ wiki của HikariCP, gốc từ PostgreSQL):

connections = (core_count × 2) + effective_spindle_count

DB 4 core, SSD (coi spindle ≈ 1)  →  4 × 2 + 1 ≈ 9–10 connection

Đây là điểm khởi đầu, không phải chân lý. Ý chính: pool nhỏ thường nhanh hơn pool lớn. Đo rồi chỉnh.

Ràng buộc quan trọng nhất: tổng connection của mọi instance phải nhỏ hơn max_connections:

số instance × maximumPoolSize + (admin, migration, monitoring)  <  max_connections

4. Code từng bước

Bước 1: Dependency: spring-boot-starter-data-jpa đã kéo theo HikariCP, thêm driver org.postgresql:postgresql.

Bước 2: Config

spring:
  datasource:
    url: jdbc:postgresql://db.internal:5432/shop
    username: ${DB_USER}
    password: ${DB_PASSWORD}
    hikari:
      pool-name: shop-pool
      maximum-pool-size: 10
      minimum-idle: 10
      connection-timeout: 3000        # fail nhanh thay vì treo 30s
      max-lifetime: 1500000           # 25 phút
      leak-detection-threshold: 20000 # 20s mà chưa trả → log
  jpa:
    open-in-view: false               # không giữ connection suốt lúc render response
management:
  endpoints.web.exposure.include: health,metrics,prometheus

Bước 3: Code Java, tránh giữ connection lâu

@Service
public class CheckoutService {

    // BAD: giữ connection trong lúc gọi payment 3 giây
    @Transactional
    public void checkoutBad(Long orderId) {
        Order order = orderRepository.findById(orderId).orElseThrow();
        paymentClient.charge(order);          // HTTP chậm, connection vẫn bị mượn
        order.markPaid();
    }

    // GOOD: transaction ngắn, gọi bên ngoài ở ngoài transaction
    public void checkout(Long orderId) {
        OrderSnapshot snapshot = orderTx.loadForPayment(orderId);  // tx 1, vài ms
        PaymentResult result = paymentClient.charge(snapshot);     // không giữ connection
        orderTx.markPaid(orderId, result);                         // tx 2, vài ms
    }
}

Bước 4: Test và quan sát

curl -s localhost:8080/actuator/metrics/hikaricp.connections.active
curl -s localhost:8080/actuator/metrics/hikaricp.connections.pending
curl -s localhost:8080/actuator/metrics/hikaricp.connections.acquire

-- phía DB: ai đang giữ connection, đang làm gì
SELECT application_name, state, count(*)
FROM pg_stat_activity GROUP BY 1, 2;
-- nhiều 'idle in transaction' = app mượn connection, mở tx rồi không làm gì

5. Cách hoạt động

          HikariCP (pool = 10)
Thread 1 ──borrow──→ [C1][C2][C3]...[C10] ──→ PostgreSQL (10 backend process)
Thread 2 ──borrow──→   ↑ trả lại sau khi commit
...
Thread 50 ─wait────→ hàng chờ (pending)
                      quá connectionTimeout →
                      SQLTransientConnectionException:
                      shop-pool - Connection is not available, request timed out after 3000ms

Triệu chứng pool exhaustion:

• Latency tăng vọt, nhưng CPU của DB lại thấp (DB không bận, app đang xếp hàng).
• hikaricp.connections.pending > 0 kéo dài, active = max.
• Log có Connection is not available, request timed out.
• Thread dump: nhiều thread đứng ở HikariPool.getConnection.

Nguyên nhân thường gặp: transaction giữ lâu (gọi HTTP trong @Transactional), query chậm, connection leak (mượn mà không trả, ví dụ dùng JDBC tay không có try-with-resources), open-in-view bật.

6. So sánh

7. Khi nào nên dùng / không nên dùng

• Luôn dùng pool trong app. Bắt đầu nhỏ (~10), đo metric rồi mới tăng.
• Bật leakDetectionThreshold ở môi trường test/staging; ở production đặt cao hơn query dài nhất hợp lệ.
• Thêm PgBouncer khi số instance × pool vượt xa số connection DB chịu nổi (autoscaling, nhiều microservice, serverless).
• Không "chữa" pool exhaustion bằng cách tăng pool size trước khi tìm transaction dài.

Chưa đúng, chỗ này rất hay bị hiểu nhầm. Tăng max_connections chỉ cho phép mở nhiều connection hơn, không làm DB xử lý nhanh hơn.

20 instance × pool 10 = 200 connection
20 instance × pool 50 = 1000 connection  → 1000 process PostgreSQL
DB 8 core chỉ chạy thật sự ~8 việc cùng lúc
→ còn lại tranh CPU, tranh lock, RAM mỗi process (work_mem) nhân lên → chậm hơn

Cách làm đúng:

1. Giữ pool mỗi instance nhỏ, tính tổng: instances × pool < max_connections, chừa chỗ cho admin và migration.
2. Khi số instance lớn hoặc autoscale: đặt PgBouncer transaction mode. 1000 client connection phía app → 50 server connection tới DB.
3. Read nhiều thì chuyển read sang replica (bài 6.9).

20 app instance (pool 20 mỗi cái = 400)
          │
     PgBouncer (pool_mode = transaction, default_pool_size = 40)
          │
     PostgreSQL (40 connection thật)

Bạn nghi đúng hướng. Traffic thấp mà pool cạn thường là leak hoặc transaction treo. Các bước:

1. Bật leak-detection-threshold: 20000. Hikari sẽ log Connection leak detection triggered kèm stack trace chỗ mượn connection, chỉ thẳng vào dòng code.
2. Trên DB, tìm session idle in transaction lâu:

   SELECT pid, application_name, state, now() - xact_start AS tx_age, left(query, 80)
   FROM pg_stat_activity
   WHERE state = 'idle in transaction'
   ORDER BY tx_age DESC;

3. Đặt chốt an toàn phía DB: idle_in_transaction_session_timeout = '60s' để DB tự cắt session treo.
4. Rà code: JDBC tay phải dùng try-with-resources; @Transactional không gọi HTTP/queue; stream từ repository phải được đóng.

Q1. Vì sao cần connection pool?

🇻🇳 Tiếng Việt:

Mở một connection tới PostgreSQL rất đắt: TCP, TLS, xác thực và DB phải tạo một process mới. Pool mở sẵn một số connection và cho các thread mượn rồi trả, nên request không phải trả chi phí đó. Pool còn giới hạn số connection đồng thời, bảo vệ database khỏi quá tải.

🇬🇧 English:

Opening a PostgreSQL connection is expensive: TCP, TLS, authentication, and the database forks a new backend process. A pool keeps connections open and lends them to threads, so requests do not pay that cost. It also caps concurrent connections, which protects the database from overload.

Q2. Chọn pool size thế nào?

🇻🇳 Tiếng Việt:

Em bắt đầu từ công thức số core nhân hai cộng số ổ đĩa, thường ra khoảng 10, rồi đo bằng metric active, pending và thời gian acquire. Pool nhỏ thường nhanh hơn pool lớn vì DB chỉ chạy song song được vài việc. Và phải tính tổng: số instance nhân pool size phải nhỏ hơn max_connections.

🇬🇧 English:

I start from the formula core count times two plus the number of disks, which usually gives about ten, then tune with metrics such as active, pending and acquire time. A small pool is often faster than a large one because the database can only run a few things in parallel. And I check the total: number of instances times pool size must stay below max_connections.

Q3. Pool exhaustion có triệu chứng gì, xử lý thế nào?

🇻🇳 Tiếng Việt:

Latency tăng mạnh nhưng CPU của DB thấp, pending connection lớn, log báo timeout khi lấy connection. Nguyên nhân thường là transaction giữ lâu, ví dụ gọi HTTP bên trong Transactional, query chậm hoặc connection leak. Em bật leak detection để lấy stack trace, xem pg_stat_activity tìm idle in transaction, rồi rút ngắn transaction trước khi nghĩ tới tăng pool.

🇬🇧 English:

Latency spikes while database CPU stays low, pending connections grow, and logs show timeouts when acquiring a connection. Common causes are long transactions, such as HTTP calls inside a Transactional method, slow queries or connection leaks. I enable leak detection to get stack traces, check pg_stat_activity for idle in transaction sessions, and shorten transactions before considering a bigger pool.

Q4. PgBouncer dùng khi nào?

🇻🇳 Tiếng Việt:

Khi có rất nhiều instance hoặc autoscaling, tổng connection vượt xa mức DB chịu được. PgBouncer ở transaction mode cho nhiều client connection dùng chung một số ít server connection. Đổi lại không dùng được các tính năng gắn với session như SET hay advisory lock theo session.

🇬🇧 English:

When there are many instances or autoscaling, and the total number of connections far exceeds what the database can handle. PgBouncer in transaction mode lets many client connections share a small number of server connections. The trade-off is that session-level features such as SET or session advisory locks do not work reliably.

Cách nhớ ngắn gọn

• PostgreSQL: 1 connection = 1 process → đắt, có giới hạn.
• Pool nhỏ, đo rồi chỉnh; instances × pool < max_connections.
• Pool cạn = tìm transaction dài/leak trước, tăng pool sau.
• Nhiều instance → PgBouncer transaction mode.