Thiết lập bảng phân vùng (Partitioned Tables) cho dữ liệu lịch sử OLAP
Đối với mô hình Hybrid, dữ liệu lịch sử (OLAP) cần được tổ chức theo thời gian để tối ưu hóa việc loại bỏ dữ liệu cũ và truy vấn theo khoảng thời gian. Chúng ta sẽ sử dụng tính năng Range Partitioning của Greenplum.
Bước này tạo một bảng mẹ (parent table) và các con (partitions) tự động dựa trên cột timestamp. Việc này giúp Query Planner loại bỏ các phân vùng không liên quan (Partition Pruning), giảm đáng kể I/O.
psql -U gpadmin -d gpadmin -c "
CREATE TABLE sales_history (
transaction_id BIGINT,
sale_date DATE,
amount NUMERIC(12,2),
region TEXT,
customer_id BIGINT
)
PARTITION BY RANGE (sale_date) (
START (DATE '2023-01-01')
END (DATE '2024-01-01')
EVERY (INTERVAL '1 month')
)
DISTRIBUTED BY (region);
"
Kết quả mong đợi: Bảng sales_history được tạo thành công với các partition con tự động sinh ra cho từng tháng từ 01/2023 đến 12/2023. Dữ liệu sẽ được phân phối theo cột region để đảm bảo cân bằng khi JOIN với bảng khách hàng.
Verify kết quả bảng phân vùng
Thực hiện lệnh sau để kiểm tra cấu trúc phân vùng và đảm bảo các partition con đã được tạo đúng khoảng thời gian.
psql -U gpadmin -d gpadmin -c "\dp sales_history"
Kết quả mong đợi: Danh sách các bảng con xuất hiện với tên dạng sales_history_prt_YYYYMMDD, xác nhận cơ chế phân vùng đã hoạt động.
Thiết lập chỉ mục GIN và BRIN cho tối ưu hóa truy vấn
Trong môi trường Hybrid, chúng ta cần sự cân bằng giữa tốc độ ghi (OLTP) và tốc độ đọc (OLAP). Chỉ mục B-tree truyền thống có thể gây nghẽn cổ chai khi ghi dữ liệu lớn.
Chúng ta sẽ áp dụng BRIN (Block Range INdex) cho các cột có tính thứ tự (như ngày tháng) trên bảng lớn để tiết kiệm không gian và tăng tốc quét, và GIN (Generalized Inverted Index) cho các cột tìm kiếm văn bản hoặc JSON nếu có.
Tạo chỉ mục BRIN cho cột thời gian trên bảng phân vùng đã tạo ở trên. BRIN rất hiệu quả cho dữ liệu đã được sắp xếp (sorted data) trong các segment của Greenplum.
psql -U gpadmin -d gpadmin -c "
CREATE INDEX sales_history_date_brin_idx
ON sales_history (sale_date)
USING brin;
"
Kết quả mong đợi: Chỉ mục BRIN được tạo. Kích thước của chỉ mục này sẽ nhỏ hơn rất nhiều so với B-tree (thường chỉ vài MB cho hàng triệu dòng) và không ảnh hưởng đáng kể đến tốc độ INSERT.
Tiếp theo, tạo chỉ mục GIN cho trường region hoặc các trường tìm kiếm tự do (full-text search) nếu cần thiết cho các truy vấn OLAP phức tạp.
psql -U gpadmin -d gpadmin -c "
CREATE INDEX sales_history_region_gin_idx
ON sales_history (region)
USING gin;
"
Kết quả mong đợi: Chỉ mục GIN được tạo thành công, hỗ trợ các phép toán tìm kiếm trên kiểu dữ liệu Text hoặc các cấu trúc phức tạp với tốc độ cao.
Verify kết quả chỉ mục
Kiểm tra kích thước và loại chỉ mục đã tạo để đảm bảo chiến lược lưu trữ đúng ý muốn.
psql -U gpadmin -d gpadmin -c "
SELECT indexname, indexdef, pg_size_pretty(pg_relation_size(indexname)) as size
FROM pg_indexes
WHERE tablename = 'sales_history';
"
Kết quả mong đợi: Xuất hiện 2 dòng chỉ mục với loại brin và gin, kích thước phù hợp với dữ liệu hiện có.
Cấu hình độ phân phối (Distribution Key) để cân bằng dữ liệu
Trong kiến trúc MPP (Massively Parallel Processing) của Greenplum, hiệu năng phụ thuộc vào việc dữ liệu được phân bố đều (balanced) trên tất cả các Segment Nodes. Nếu một node nhận quá nhiều dữ liệu (skew), toàn bộ cluster sẽ bị chậm do nút đó.
Chúng ta sẽ kiểm tra lại cách phân phối và điều chỉnh nếu cần thiết. Đối với bảng sales_history, việc chọn region làm Distribution Key là hợp lý nếu số lượng vùng (region) lớn và phân bố đều. Nếu dữ liệu không đều, ta có thể chuyển sang Hash Distribution hoặc Random Distribution.
Để kiểm tra sự cân bằng của dữ liệu hiện tại trên các segment, ta sử dụng hàm gp_segment_configuration và bảng pg_stat_user_tables.
psql -U gpadmin -d gpadmin -c "
SELECT
datname,
relname,
distmethod,
distkey,
total_rows,
max_rows,
min_rows,
(max_rows - min_rows) * 100.0 / NULLIF(min_rows, 0) as skew_pct
FROM
gp_toolkit.gp_table_size
WHERE
datname = 'gpadmin' AND relname = 'sales_history';
"
Kết quả mong đợi: Xuất hiện thông tin phân phối. Nếu skew_pct lớn hơn 10%, dữ liệu bị lệch. Trường hợp này, ta cần thay đổi Distribution Key hoặc sử dụng DISTRIBUTED RANDOMLY nếu không có key phù hợp.
Trường hợp cần thay đổi phân phối key (ví dụ: từ region sang customer_id để cân bằng hơn), ta thực hiện lệnh ALTER TABLE. Lưu ý: Lệnh này sẽ trigger quá trình làm lại toàn bộ dữ liệu (rescan) nên chỉ thực hiện trên dữ liệu đã ổn định.
psql -U gpadmin -d gpadmin -c "
ALTER TABLE sales_history SET DISTRIBUTED BY (customer_id);
"
Kết quả mong đợi: Command chạy xong, Greenplum tự động di chuyển dữ liệu giữa các segment để đảm bảo cân bằng theo key mới. Quá trình này có thể mất thời gian tùy vào lượng dữ liệu.
Verify sự cân bằng sau khi điều chỉnh
Chạy lại query kiểm tra skew để đảm bảo chênh lệch giữa node có nhiều dữ liệu nhất và ít dữ liệu nhất nằm trong ngưỡng chấp nhận được (< 5%).
psql -U gpadmin -d gpadmin -c "
SELECT
node_name,
content,
disk_size,
row_count
FROM
gp_toolkit.gp_table_segments
WHERE
datname = 'gpadmin' AND relname = 'sales_history'
ORDER BY
row_count DESC
LIMIT 10;
"
Kết quả mong đợi: Các dòng row_count ở các node khác nhau có giá trị xấp xỉ nhau, chứng tỏ dữ liệu đã được cân bằng (balanced) trên toàn cluster.
Thực hiện lệnh ANALYZE để cập nhật thống kê cho Optimizer
Sau khi tạo bảng, chỉ mục và phân phối dữ liệu, Optimizer của Greenplum cần thông tin thống kê chính xác về phân bố giá trị, số lượng dòng và mật độ dữ liệu để tạo kế hoạch thực thi (Execution Plan) tối ưu.
Thống kê cũ hoặc thiếu sẽ khiến Greenplum chọn sai thuật toán (ví dụ: chọn Nested Loop thay vì Hash Join), dẫn đến hiệu năng kém. Lệnh ANALYZE sẽ thu thập thông tin này.
Thực hiện phân tích toàn bộ bảng sales_history bao gồm cả các chỉ mục mới tạo. Đối với bảng phân vùng, lệnh này tự động chạy trên tất cả các partition con.
psql -U gpadmin -d gpadmin -c "
ANALYZE VERBOSE sales_history;
"
Kết quả mong đợi: Console hiển thị dòng ANALYZE kèm theo số lượng mẫu (sample) đã quét và thời gian thực hiện. Thông tin thống kê được cập nhật vào catalog pg_statistic.
Đối với dữ liệu OLAP lớn, việc chạy ANALYZE toàn bộ có thể nặng. Ta có thể cấu hình chỉ phân tích một phần (sample) nếu dữ liệu quá lớn, tuy nhiên với dữ liệu vừa và nhỏ, ANALYZE VERBOSE là bắt buộc để có kế hoạch tối ưu nhất.
Nếu bạn đã thực hiện thay đổi lớn về chỉ mục hoặc phân phối dữ liệu, cần chạy thêm lệnh VACUUM ANALYZE để dọn dẹp các tuple chết (dead tuples) và cập nhật thống kê cùng lúc.
psql -U gpadmin -d gpadmin -c "
VACUUM ANALYZE sales_history;
"
Kết quả mong đợi: Bảng được dọn dẹp (vacuum) và thống kê được cập nhật (analyze). Số lượng dead tuple giảm về 0 hoặc mức thấp.
Verify thống kê đã cập nhật
Kiểm tra xem Optimizer có đã nắm bắt được số lượng dòng thực tế và phân bố dữ liệu chưa.
psql -U gpadmin -d gpadmin -c "
SELECT
relname,
reltuples as estimated_rows,
(SELECT count(*) FROM sales_history) as actual_rows,
(SELECT count(*) FROM sales_history) - reltuples as diff
FROM
pg_class
WHERE
relname = 'sales_history';
"
Kết quả mong đợi: Giá trị estimated_rows (dòng ước tính) phải gần bằng actual_rows (dòng thực tế). Chênh lệch diff phải rất nhỏ hoặc bằng 0, chứng tỏ Optimizer đã có dữ liệu thống kê chính xác để tối ưu query.
Điều hướng series:
Mục lục: Series: Triển khai Database Hybrid OLTP/OLAP với Greenplum trên Ubuntu 24.04
« Phần 5: Tối ưu hóa cấu hình Greenplum cho mô hình Hybrid OLTP/OLAP
Phần 7: Tích hợp ETL và tải dữ liệu hiệu quả vào Greenplum »