Tối ưu hóa bộ nhớ và tham số hiệu năng trong PostgreSQL
Bước đầu tiên là điều chỉnh bộ nhớ đệm và bộ nhớ làm việc để cân bằng giữa khả năng ghi (OLTP) và đọc phân tích (OLAP). Mục tiêu là tối đa hóa lượng dữ liệu được lưu trong RAM, giảm I/O đĩa.
Tham số shared_buffers xác định lượng bộ nhớ hệ thống dành riêng cho việc cache dữ liệu. Đối với HTAP, nên đặt ở 25% tổng RAM, tối thiểu 4GB. Tham số work_mem là bộ nhớ cho các thao tác sắp xếp và join; cần tăng cao hơn mức mặc định để xử lý các truy vấn phức tạp nhanh chóng, nhưng phải cẩn thận để không gây tràn RAM khi có nhiều session.
Chỉnh sửa file cấu hình chính của PostgreSQL tại đường dẫn /etc/postgresql/16/main/postgresql.conf. Nội dung dưới đây giả định server có 32GB RAM.
sudo nano /etc/postgresql/16/main/postgresql.conf
# Nội dung cần sửa/thêm vào file:
shared_buffers = 8GB
work_mem = 256MB
maintenance_work_mem = 2GB
effective_cache_size = 24GB
max_connections = 300
wal_buffers = 64MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
Khởi động lại dịch vụ PostgreSQL để áp dụng các thay đổi này. Lệnh khởi động lại sẽ đảm bảo kernel cấp phát đúng lượng bộ nhớ đã khai báo.
sudo systemctl restart postgresql@16-main
Kết quả mong đợi: Dịch vụ khởi động thành công, không báo lỗi về bộ nhớ. Kiểm tra bằng lệnh psql để xem giá trị hiện hành.
sudo -u postgres psql -c "SHOW shared_buffers; SHOW work_mem;"
Tạo Database và User chuyên biệt cho HTAP
Chúng ta cần tách biệt môi trường phát triển và production, đồng thời tạo một user có quyền hạn cụ thể để kết nối với Flink. Việc này giúp bảo mật và tránh xung đột quyền truy cập.
Thực hiện đăng nhập vào shell của user postgres và tạo database mới cùng với user flink_admin có quyền tạo bảng, chỉ mục và thực thi các thao tác cần thiết cho pipeline ETL.
sudo -u postgres psql
CREATE DATABASE htap_db WITH ENCODING = 'UTF8' TEMPLATE = template0;
CREATE USER flink_admin WITH PASSWORD 'SecureFlinkPass123' CREATEDB CREATEROLE;
GRANT ALL PRIVILEGES ON DATABASE htap_db TO flink_admin;
\c htap_db
GRANT ALL ON SCHEMA public TO flink_admin;
\q
Kết quả mong đợi: Database htap_db được tạo thành công. User flink_admin có thể đăng nhập vào database này và thực hiện các thao tác DDL (Create, Drop, Alter) cũng như DML (Insert, Update, Delete).
sudo -u postgres psql -d htap_db -c "SELECT current_database(), current_user;"
Cài đặt và cấu hình pg_stat_statements để giám sát
Để phân tích hiệu năng trong mô hình HTAP, chúng ta cần biết câu lệnh nào tốn nhiều thời gian nhất. Extension pg_stat_statements là công cụ tiêu chuẩn để thu thập thống kê thực thi của các câu lệnh SQL.
Đầu tiên, đảm bảo extension này được liệt kê trong danh sách mở rộng cho phép trong file postgresql.conf (tham số shared_preload_libraries). Nếu chưa có, cần thêm vào và khởi động lại server.
sudo nano /etc/postgresql/16/main/postgresql.conf
# Tìm dòng shared_preload_libraries và sửa thành:
shared_preload_libraries = 'pg_stat_statements'
# Nếu dòng này đang trống hoặc không có, hãy tạo mới hoặc thêm vào dấu phẩy
sudo systemctl restart postgresql@16-main
Sau khi server chạy lại, kích hoạt extension cho database htap_db mà chúng ta vừa tạo. Lệnh này sẽ tạo các bảng hệ thống để lưu trữ thống kê.
sudo -u postgres psql -d htap_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
Kết quả mong đợi: Extension được tạo thành công. Bạn có thể truy vấn bảng pg_stat_statements để xem top các câu lệnh chậm nhất hoặc tốn nhiều I/O nhất.
sudo -u postgres psql -d htap_db -c "SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
Chiến lược Chỉ mục (Index) và Phân vùng (Partitioning)
Trong mô hình HTAP, dữ liệu vừa được ghi liên tục (OLTP) vừa bị quét để phân tích (OLAP). Việc sử dụng chỉ mục sai cách sẽ làm chậm tốc độ ghi, trong khi thiếu phân vùng sẽ làm chậm tốc độ đọc các bảng lớn.
Áp dụng kỹ thuật Partitioning theo thời gian (Time-based) để chia nhỏ dữ liệu lịch sử. Điều này giúp các truy vấn OLAP chỉ quét các phân vùng cần thiết (Partition Pruning) thay vì toàn bộ bảng.
sudo -u postgres psql -d htap_db
-- Tạo bảng gốc (Parent table) sử dụng partition by range theo ngày
CREATE TABLE htap_events (
event_id BIGINT GENERATED ALWAYS AS IDENTITY,
event_time TIMESTAMPTZ NOT NULL,
user_id BIGINT,
event_type VARCHAR(50),
payload JSONB
) PARTITION BY RANGE (event_time);
Tiếp theo, tạo các phân vùng con (Child tables) cho từng tháng hoặc ngày. Ở đây ví dụ tạo 3 phân vùng cho 3 tháng liên tiếp. Sử dụng BRIN Index (Block Range Index) cho cột thời gian vì nó cực kỳ nhẹ và hiệu quả với dữ liệu có thứ tự lớn, phù hợp cho OLAP.
-- Tạo phân vùng cho tháng 1, 2, 3 năm 2024
CREATE TABLE htap_events_2024_01 PARTITION OF htap_events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE htap_events_2024_02 PARTITION OF htap_events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE htap_events_2024_03 PARTITION OF htap_events
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
-- Tạo chỉ mục BRIN cho cột thời gian trên bảng gốc (áp dụng tự động cho con)
CREATE INDEX idx_htap_events_time ON htap_events USING BRIN (event_time);
-- Tạo chỉ mục B-Tree cho các cột thường dùng trong điều kiện WHERE (OLTP)
CREATE INDEX idx_htap_events_user ON htap_events (user_id);
Kết quả mong đợi: Bảng htap_events được tạo với cấu trúc phân vùng. Khi chèn dữ liệu, PostgreSQL tự động định tuyến vào phân vùng tương ứng. Truy vấn lọc theo thời gian sẽ nhanh hơn đáng kể nhờ BRIN index.
-- Kiểm tra cấu trúc phân vùng
SELECT schemaname, tablename, partitionbound FROM pg_partitioned_table WHERE tablename = 'htap_events';
Cấu hình PgBouncer để quản lý kết nối
Apache Flink có thể tạo hàng trăm kết nối đồng thời tới PostgreSQL, vượt quá giới hạn max_connections đã thiết lập (300). PgBouncer đóng vai trò là Connection Pooler, giúp giảm tải cho PostgreSQL bằng cách chia sẻ một số lượng kết nối vật lý nhỏ cho nhiều client.
Cài đặt PgBouncer từ kho lưu trữ của Ubuntu và cấu hình file /etc/pgbouncer/pgbouncer.ini để hoạt động ở chế độ transaction (mỗi transaction giữ 1 kết nối) hoặc session (giữ kết nối lâu hơn). Với Flink, chế độ transaction thường hiệu quả hơn cho throughput.
sudo apt update
sudo apt install pgbouncer -y
sudo nano /etc/pgbouncer/pgbouncer.ini
Thay thế nội dung trong file cấu hình bằng các tham số tối ưu dưới đây. Lưu ý sửa auth_file để trỏ đến file mật khẩu user PostgreSQL.
# Nội dung /etc/pgbouncer/pgbouncer.ini
[databases]
htap_db = host=127.0.0.1 port=5432 dbname=htap_db
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/postgresql/16/main/pg_hba.conf
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool = 5
reserve_pool_timeout = 3
min_pool_size = 5
Để PgBouncer có thể xác thực user flink_admin, ta cần tạo file pgbouncer_userlist.txt chứa thông tin user và password mã hóa. Công cụ htpasswd hoặc lệnh create_user trong psql có thể dùng, nhưng cách chuẩn nhất là trích xuất từ pg_shadow.
sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename = 'flink_admin';" > /etc/pgbouncer/userlist.txt
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt
Cấu hình systemd để PgBouncer tự động khởi động và chạy như một service. Chỉnh sửa file service để đảm bảo nó chạy đúng user pgbouncer.
sudo systemctl daemon-reload
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
Kết quả mong đợi: PgBouncer đang lắng nghe trên cổng 6432. Bạn có thể kết nối vào cổng này thay vì cổng 5432 của PostgreSQL trực tiếp. Kiểm tra trạng thái bằng lệnh pgbouncer show.
sudo -u pgbouncer psql -h 127.0.0.1 -p 6432 -d pgbouncer -c "SHOW pools;"
Để test kết nối từ Flink (hoặc bất kỳ ứng dụng nào), bạn sẽ cấu hình JDBC URL trỏ về jdbc:postgresql://localhost:6432/htap_db thay vì cổng 5432. Điều này giúp hệ thống chịu tải tốt hơn khi Flink scale up các task manager.
sudo -u postgres psql -h 127.0.0.1 -p 6432 -d htap_db -c "SELECT 1 as connection_test;"
Điều hướng series:
Mục lục: Series: Triển khai Database HTAP với Apache Flink và PostgreSQL trên Ubuntu 24.04
« Phần 1: Chuẩn bị môi trường Ubuntu 24.04 và cài đặt phần mềm nền tảng
Phần 3: Triển khai cụm Apache Flink và cấu hình cluster »