Tại sao PostgreSQL ngốn quá nhiều RAM — Và cách khắc phục thực sự

Tại sao PostgreSQL ngốn quá nhiều RAM — Và cách khắc phục thực sự 

Tại sao PostgreSQL ngốn quá nhiều RAM — Và cách khắc phục thực sự

Bộ nhớ PostgreSQL tăng dần trong ngày, chạm mức 100%, và kích hoạt Linux OOM Killer. Cơ sở dữ liệu thậm chí không chịu tải nặng. Không có gì bất thường. Nhưng PostgreSQL đã tiêu thụ toàn bộ RAM có sẵn.

Nếu từng quản lý máy chủ PostgreSQL trong môi trường thực tế, khoảnh khắc này sẽ rất quen thuộc và đau đớn.

Với nhiều công ty, đây trở thành cơn ác mộng lặp đi lặp lại - không phải vì PostgreSQL không ổn định, mà vì hệ thống bộ nhớ của nó bị hiểu sai phổ biến. Hầu hết các nhóm chỉ điều trị triệu chứng thay vì giải quyết nguyên nhân thực sự.

Bài viết này phân tích câu chuyện thật đằng sau việc sử dụng RAM của PostgreSQL. Không phải huyền thoại, không phải suy đoán - chỉ là sự thật, các mẫu hình, và giải pháp thực sự hiệu quả trong hệ thống thực tế.

Quan Niệm Sai Lầm: "PostgreSQL Có Rò Rỉ Bộ Nhớ"

Mỗi khi PostgreSQL ngốn nhiều RAM hơn dự kiến, những giả định giống nhau xuất hiện:

"PostgreSQL chắc có rò rỉ bộ nhớ"
"Có lẽ công cụ được thiết kế kém"
"Chúng ta nên nâng cấp RAM"

Sự thật đơn giản hơn nhiều:

PostgreSQL đang làm chính xác những gì được yêu cầu - ngay cả khi không nhận ra điều đó.

Có những vấn đề liên quan đến bộ nhớ trước PostgreSQL 13 (đặc biệt là các nút HashAgg không chuyển sang đĩa đủ sớm), nhưng những vấn đề đó đã được khắc phục lâu rồi. Điều gây ra hầu hết các sự cố ngày nay không phải PostgreSQL - mà là cấu hình và mẫu khối lượng công việc.

PostgreSQL Thực Sự Sử Dụng Bộ Nhớ Như Thế Nào

Hãy nghĩ về PostgreSQL như một nhà hàng.

Bộ nhớ dùng chung (Shared Memory)

Đây là bếp chính: lò nướng, quầy, tủ lạnh. Mọi người đều dùng chung.

Thành phần lớn nhất ở đây:

shared_buffers - bộ đệm dữ liệu tích hợp của PostgreSQL

Mặc định: 128MB (rất thấp cho máy chủ hiện đại)
Khuyến nghị hiện đại: 20-25% RAM, tối đa khoảng 8GB

Bộ nhớ cho mỗi kết nối (Per-Connection Memory)

Đây là không gian làm việc cá nhân của mỗi đầu bếp.

Mỗi kết nối nhận:

work_mem cho sắp xếp, nối bảng và băm
Ngữ cảnh bổ sung cho siêu dữ liệu, lập kế hoạch, bộ đệm
Không gian cho giao dịch chạy lâu

Đây là nơi 90% vấn đề bộ nhớ đến từ.

Tại sao?

Bởi vì một truy vấn có thể sử dụng work_mem nhiều lần.

Nếu work_mem = 64MB và truy vấn sử dụng 8 thao tác sắp xếp:

→ Đó là 512MB chỉ cho một truy vấn.

Nhân với 40 truy vấn đang chạy... Bạn thấy cách chúng ta kết thúc ở 20GB mà không nhận ra.

Lý Do Thực Sự PostgreSQL Ngốn Quá Nhiều RAM

1. work_mem Nhân Lên Im Lặng

Không giống MySQL, PostgreSQL không có giới hạn bộ nhớ toàn cục. Mỗi tiến trình backend độc lập.

Nếu đặt work_mem = 128MB, không phải đã cho PostgreSQL 128MB. Đã cho mọi nút sắp xếp trong mọi kết nối quyền sử dụng 128MB.

Đó là sai lầm phá vỡ hầu hết các hệ thống.

Ví dụ minh họa:

-- Cấu hình
work_mem = 128MB
max_connections = 100

-- Một truy vấn phức tạp
SELECT
c.customer_name,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.amount) AS total_spent,
AVG(o.amount) AS avg_order
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_name
ORDER BY total_spent DESC;

-- Truy vấn này có thể sử dụng:
-- 1 × work_mem cho JOIN
-- 1 × work_mem cho GROUP BY
-- 1 × work_mem cho ORDER BY
-- = 3 × 128MB = 384MB cho MỘT truy vấn

-- Nếu có 50 người dùng chạy truy vấn tương tự cùng lúc:
-- 50 × 384MB = 19.2GB RAM!

2. Quá Nhiều Kết Nối Mở

Một kết nối PostgreSQL rỗi sử dụng khoảng 2-5MB. Hàng trăm microservice đột nhiên trở thành vấn đề nghiêm trọng.

# Kiểm tra số lượng kết nối
SELECT
state,
COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY state
ORDER BY connection_count DESC;

# Kết quả thường thấy:
# state | connection_count
# idle | 450
# active | 12
# idle in transaction | 38

# 450 kết nối rỗi × 4MB = 1.8GB RAM lãng phí
# Chưa có truy vấn nào chạy!

Đây là lý do PgBouncer hoặc Pgcat thiết yếu ngày nay.

3. Giao Dịch Chạy Lâu

Những giao dịch này âm thầm tích lũy bộ nhớ trong ngữ cảnh backend. Một kết nối được giữ mở hàng giờ hoặc hàng ngày sẽ cứ tăng lên.

-- Tìm giao dịch chạy lâu
SELECT
pid,
usename,
application_name,
state,
query_start,
NOW() - query_start AS duration,
query
FROM pg_stat_activity
WHERE state != 'idle'
AND NOW() - query_start > INTERVAL '1 hour'
ORDER BY duration DESC;

-- Kết quả mẫu:
-- pid | usename | duration | query
-- 12345 | app_user | 4:23:15 | BEGIN; SELECT * FROM...
-- (Giao dịch mở 4 giờ!)

-- Dừng giao dịch nguy hiểm
SELECT pg_terminate_backend(12345);

Đây không phải rò rỉ bộ nhớ - đó là PostgreSQL làm những gì được thiết kế.

4. Mục Tiêu Thống Kê Cao

Các cột có số lượng giá trị khác biệt lớn + cài đặt STATISTICS cao = nhiều bộ nhớ bộ lập kế hoạch được tiêu thụ trong mọi tiến trình backend.

-- Kiểm tra cài đặt thống kê hiện tại
SELECT
schemaname,
tablename,
attname,
n_distinct,
correlation
FROM pg_stats
WHERE tablename = 'orders'
ORDER BY n_distinct DESC;

-- Giảm mục tiêu thống kê cho cột có vấn đề
ALTER TABLE orders
ALTER COLUMN customer_notes SET STATISTICS 100;
-- Mặc định là 100, có thể giảm xuống 50 hoặc 10

-- Cập nhật thống kê
ANALYZE orders;

Một số hệ thống thực tế đã giảm sử dụng bộ nhớ 30-35% chỉ bằng cách giảm mục tiêu thống kê trên các cột có vấn đề.

5. Giá Trị Mặc Định Cũ Không Bao Giờ Được Cập Nhật

Giá trị mặc định của PostgreSQL được xây dựng cho phần cứng từ hai thập kỷ trước. Máy chủ ngày nay hoàn toàn khác, nhưng hầu hết cấu hình vẫn không được chạm đến.

# Kiểm tra cấu hình mặc định nguy hiểm
psql -c "SHOW shared_buffers;" # Thường là 128MB
psql -c "SHOW work_mem;" # Thường là 4MB
psql -c "SHOW maintenance_work_mem;" # Thường là 64MB

# Trên máy chủ 32GB RAM:
# shared_buffers = 128MB ← Chỉ 0.4% RAM!
# work_mem = 4MB ← Quá nhỏ, gây spill to disk

Giống như mua siêu xe nhưng không bao giờ chuyển số qua số 2.

Cách Thực Sự Khắc Phục Vấn Đề Bộ Nhớ PostgreSQL

Đây là phương pháp hoạt động nhất quán trong môi trường thực tế.

1. Đo Lường Trạng Thái Hiện Tại Trước

-- Kiểm tra tham số chính
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;
SHOW maintenance_work_mem;
SHOW effective_cache_size;

-- Kiểm tra truy vấn đang chạy
SELECT
pid,
usename,
application_name,
client_addr,
state,
query_start,
NOW() - query_start AS duration,
LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Kiểm tra kết nối theo trạng thái
SELECT
state,
COUNT(*) AS count
FROM pg_stat_activity
GROUP BY state;

# Kiểm tra bộ nhớ Linux
free -h
ps aux | grep postgres | head -20

# Xem tiến trình PostgreSQL ngốn RAM nhất
ps aux --sort=-%mem | grep postgres | head -10

Không thể sửa những gì chưa đo lường.

2. Điều Chỉnh shared_buffers Đúng Cách

Khuyến nghị:

RAM < 2GB → 20% RAM
RAM 2-32GB → 25% RAM
RAM > 32GB → giới hạn khoảng 8GB

Ví dụ cấu hình:

# /etc/postgresql/15/main/postgresql.conf

# Máy chủ 16GB RAM
shared_buffers = 4GB

# Máy chủ 32GB RAM
shared_buffers = 8GB

# Máy chủ 64GB RAM
shared_buffers = 8GB # Không tăng quá 8GB

Lý do giới hạn 8GB:

PostgreSQL cũng dựa vào bộ đệm hệ điều hành. Tăng shared_buffers quá cao có thể phản tác dụng vì làm giảm bộ nhớ cho filesystem cache.

3. Đặt work_mem An Toàn

Quy tắc đơn giản:

work_mem = (RAM khả dụng) / (max_connections × 3)

Ví dụ (máy chủ 32GB, 100 kết nối):

# Tính toán:
# RAM khả dụng cho PostgreSQL: ~24GB (sau shared_buffers)
# 24GB / (100 × 3) = 24GB / 300 = 80MB

work_mem = 80MB

Tăng theo phiên khi cần:

-- Truy vấn nặng cụ thể
BEGIN;
SET work_mem = '256MB';

SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 100
ORDER BY total_spent DESC
LIMIT 1000;

RESET work_mem;
COMMIT;

4. Sử Dụng Connection Pooling (Nghiêm Túc)

PgBouncer là một trong những công cụ tiết kiệm bộ nhớ tốt nhất từng được xây dựng cho PostgreSQL.

Cài đặt PgBouncer:

# Ubuntu/Debian
sudo apt install pgbouncer

# Cấu hình /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=production

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Chế độ pooling
pool_mode = transaction

# Giới hạn kết nối
default_pool_size = 25
max_client_conn = 500
max_db_connections = 100

# Khởi động
sudo systemctl restart pgbouncer

Kết quả:

500 kết nối client
Chỉ 25 kết nối thực đến database
Giảm 95% sử dụng bộ nhớ
Không cần thay đổi ứng dụng

So sánh:

Trước PgBouncer:
500 kết nối × 5MB = 2.5GB RAM

Sau PgBouncer:
25 kết nối × 5MB = 125MB RAM

Tiết kiệm: 2.375GB!

5. Điều Chỉnh maintenance_work_mem

Tham số này hỗ trợ VACUUM, xây dựng INDEX, và ANALYZE.

# /etc/postgresql/15/main/postgresql.conf

# Máy chủ 16GB
maintenance_work_mem = 512MB

# Máy chủ 32GB
maintenance_work_mem = 1GB

# Máy chủ 64GB
maintenance_work_mem = 2GB

Lợi ích:

-- Tạo index nhanh hơn
CREATE INDEX CONCURRENTLY idx_orders_customer
ON orders(customer_id);

-- VACUUM hiệu quả hơn
VACUUM ANALYZE orders;

-- Thời gian giảm 40-60%

6. Theo Dõi Dấu Hiệu Áp Lực Bộ Nhớ

-- Tìm truy vấn ghi file tạm (dấu hiệu work_mem không đủ)
SELECT
userid::regrole,
LEFT(query, 100) AS query_preview,
calls,
total_time / 1000 AS total_seconds,
mean_time / 1000 AS mean_seconds,
temp_blks_written,
temp_blks_written * 8192 / 1024 / 1024 AS temp_mb
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

-- Nếu thấy temp_blks_written cao:
-- → Truy vấn đó cần work_mem lớn hơn

7. Kiểm Tra Kế Hoạch Truy Vấn Như Thám Tử

-- Phân tích truy vấn cụ thể
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
c.name,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total DESC
LIMIT 100;

Tìm kiếm:

Sort Method: external merge Disk: 25360kB

→ PostgreSQL hết bộ nhớ và chuyển sang đĩa.
→ Tăng work_mem cho khối lượng công việc cụ thể đó.

Ví dụ giải pháp:

-- Đặt work_mem cao hơn cho session này
SET work_mem = '256MB';

-- Chạy lại
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

-- Bây giờ thấy:
-- Sort Method: quicksort Memory: 45120kB
-- ✓ Không còn spill to disk!

Mẹo Nâng Cao Cho Sự Ổn Định

1. Nâng Cấp Lên PostgreSQL 13+

Cải thiện HashAgg giảm đáng kể việc sử dụng bộ nhớ vượt kiểm soát.

-- PostgreSQL 13+ có hash_mem_multiplier
hash_mem_multiplier = 2.0 # Cho phép hash ops sử dụng 2x work_mem

2. Bật HugePages (Nếu Trên Máy Chủ Lớn)

# Tính số trang cần
# shared_buffers = 8GB = 8192MB
# Page size = 2MB
# Pages needed = 8192 / 2 = 4096

# /etc/sysctl.conf
vm.nr_hugepages = 4096

# Áp dụng
sudo sysctl -p

# postgresql.conf
huge_pages = on

# Khởi động lại PostgreSQL
sudo systemctl restart postgresql

Cải thiện hiệu suất CPU trên hệ thống bộ nhớ lớn.

3. Giảm STATISTICS Trên Cột Nặng

-- Cột có hàng triệu giá trị khác nhau
ALTER TABLE transactions
ALTER COLUMN transaction_id SET STATISTICS 50;

ALTER TABLE logs
ALTER COLUMN log_message SET STATISTICS 10;

-- Cập nhật
ANALYZE transactions;
ANALYZE logs;

Giảm dấu chân bộ nhớ backend và bộ lập kế hoạch.

Kịch Bản Thực Tế: Từ Sụp Đổ Đến Ổn Định

Trước khi điều chỉnh:

Máy chủ: 32GB RAM
shared_buffers: 128MB (mặc định)
work_mem: 4MB (mặc định)
max_connections: 500
Không có pooling

Tình trạng:
- RAM tăng đến 100% sau 6 giờ
- OOM Killer giết PostgreSQL 2-3 lần/tuần
- Truy vấn chậm, timeout
- 1.5GB RAM cho kết nối rỗi

Sau khi điều chỉnh:

# postgresql.conf
shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 1GB
effective_cache_size = 24GB
max_connections = 100

# PgBouncer
pool_mode = transaction
default_pool_size = 25
max_client_conn = 500

Kết quả:

Bộ nhớ ổn định ở ~60%
Không còn OOM killer
Kết nối rỗi giảm từ 1.5GB xuống 200MB
Hiệu năng truy vấn cải thiện 40%
Không cần thay đổi ứng dụng

Mọi thứ đều quy về một vài tham số - và hiểu cách PostgreSQL suy nghĩ về bộ nhớ.

Bài Học Thực Sự: PostgreSQL Không Phải Vấn Đề

PostgreSQL không "ngốn RAM". Nó sử dụng bộ nhớ dựa trên các quy tắc được đưa ra.

Hầu hết hệ thống sụp đổ vì:

Không có pooling
work_mem quá cao
Quá nhiều kết nối
Giá trị mặc định cũ
Thiếu giám sát

Nhưng một khi điều chỉnh đúng cách, PostgreSQL trở thành một trong những cơ sở dữ liệu ổn định, dự đoán được và hiệu năng cao nhất trong môi trường thực tế.

Checklist cuối cùng:

Đặt shared_buffers = 20-25% RAM (tối đa 8GB)
Tính toán work_mem dựa trên max_connections
Cài đặt PgBouncer/Pgcat
Đặt maintenance_work_mem = 1-2GB
Giám sát temp_blks_written
Kiểm tra giao dịch chạy lâu hàng ngày
Chạy EXPLAIN ANALYZE thường xuyên
Nâng cấp lên PostgreSQL 13+

Vài phút cấu hình chu đáo có thể ngăn ngừa nhiều năm gián đoạn không cần thiết.