Skip to main content

🔧 Tinh Chỉnh PostgreSQL

📋 Chuẩn Bị​

1. Backup Cấu Hình​

Trước khi thực hiện bất kỳ thay đổi nào, luôn backup cấu hình hiện tại:

# Backup file cấu hình
sudo cp /etc/postgresql/VERSION/main/postgresql.conf /etc/postgresql/VERSION/main/postgresql.conf.backup

# Ví dụ với PostgreSQL 11:
sudo cp /etc/postgresql/11/main/postgresql.conf /etc/postgresql/11/main/postgresql.conf.backup
caution

Thay VERSION bằng phiên bản PostgreSQL bạn đang sử dụng (ví dụ: 11, 12, 13, 14...)

🛠️ Tinh Chỉnh Cấu Hình​

1. Sử Dụng PGTune​

PGTune là công cụ trực tuyến giúp tạo cấu hình PostgreSQL tối ưu dựa trên phần cứng của bạn.

  1. Truy cập PGTune

  2. Nhập các thông số hệ thống:

    • DB Version: Phiên bản PostgreSQL
    • OS Type: Hệ điều hành
    • DB Type: Loại workload
    • Total Memory (RAM): Tổng RAM
    • Number of CPUs: Số CPU
    • Number of Connections: Số kết nối đồng thời
    • Data Storage: Loại ổ đĩa

PGTune Configuration

2. Áp Dụng Cấu Hình​

# Mở file cấu hình PostgreSQL
sudo nano /etc/postgresql/VERSION/main/postgresql.conf

PostgreSQL Configuration

Thêm hoặc cập nhật các tham số sau (giá trị tùy theo kết quả từ PGTune):

# Memory Configuration
shared_buffers = 4GB                  # 25% of RAM
effective_cache_size = 12GB           # 75% of RAM
maintenance_work_mem = 1GB            # RAM/16
work_mem = 10485kB                    # RAM/(max_connections * 4)

# Checkpoint Configuration
checkpoint_completion_target = 0.9
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB

# Network Configuration
listen_addresses = '*'
max_connections = 100

# Query Planning
random_page_cost = 1.1                # SSD = 1.1, HDD = 4.0
effective_io_concurrency = 200        # SSD = 200, HDD = 2

# Parallel Query
max_worker_processes = 8              # Number of CPU cores
max_parallel_workers_per_gather = 4   # max_worker_processes / 2
max_parallel_workers = 8              # Number of CPU cores
parallel_leader_participation = on
tip
  • Điều chỉnh random_page_cost và effective_io_concurrency dựa trên loại ổ đĩa
  • Với SSD: random_page_cost = 1.1, effective_io_concurrency = 200
  • Với HDD: random_page_cost = 4.0, effective_io_concurrency = 2

3. Kiểm Tra và Khởi Động Lại​

# Kiểm tra cú pháp cấu hình
sudo -u postgres pg_ctl -D /var/lib/postgresql/VERSION/main check

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

📊 Kiểm Tra Hiệu Suất​

1. Xem Các Tham Số Hiện Tại​

-- Kiểm tra các tham số cấu hình
SHOW ALL;

-- Kiểm tra một tham số cụ thể
SHOW shared_buffers;
SHOW max_connections;

2. Theo Dõi Hoạt Động​

-- Xem các query đang chạy
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- Xem cache hit ratio
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit)  as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

⚠️ Lưu Ý Quan Trọng​

  1. Theo Dõi Sau Khi Thay Đổi:

    • Giám sát hiệu suất hệ thống
    • Theo dõi log PostgreSQL
    • Đo lường thời gian phản hồi của queries

  2. Tránh Over-Tuning:

    • Không set shared_buffers quá cao
    • Để lại RAM cho OS cache
    • Cân nhắc workload thực tế

  3. Backup và Rollback:

    • Luôn có bản backup cấu hình
    • Sẵn sàng rollback nếu cần
    • Test trên môi trường development trước

📚 Tài Liệu Tham Khảo​