PostgreSQL Vacuum & Bloat Index: Phát hiện, Giảm thiểu và Tinh chỉnh Tự động

Bạn đang theo dõi hiệu năng ứng dụng của mình. Một API endpoint cốt lõi, từng rất nhanh nhạy, giờ đây thường xuyên vượt ngưỡng SLO về độ trễ. Câu truy vấn bên dưới, vốn truy cập vào bảng users hoặc transactions, trông có vẻ đơn giản. EXPLAIN ANALYZE cho thấy một kế hoạch thực thi hợp lý, nhưng thời gian I/O lại tăng không ngừng. Biểu đồ sử dụng đĩa của nhà cung cấp đám mây cho thấy sự tăng trưởng đều đặn, tuyến tính, vượt xa tốc độ ghi dữ liệu thực tế của bạn. Rất có thể bạn đang là nạn nhân của kẻ thù hiệu năng nguy hiểm và thường bị hiểu lầm nhất của PostgreSQL: tình trạng phình to (bloat) của bảng và index.

Đây không phải là một lỗi; đó là một hệ quả cơ bản của kiến trúc tinh vi Multi-Version Concurrency Control (MVCC) của PostgreSQL. Mặc dù MVCC rất xuất sắc - cho phép đọc và ghi không bị chặn - nó để lại dấu vết là các "tuple chết" (dead tuples) mà nếu không được quản lý, sẽ biến cơ sở dữ liệu gọn gàng của bạn thành một khối monolith ì ạch và ngốn dung lượng lưu trữ. Hướng dẫn này cung cấp một phân tích sâu, dưới góc độ kỹ thuật về vấn đề này, từ cấp độ nguyên tử của khả năng hiển thị tuple đến các chiến lược cấp production để phát hiện, giảm thiểu và khắc phục tự động.

Nguồn gốc của Bloat: MVCC và Dead Tuples

Để đánh bại bloat, trước tiên bạn phải hiểu nguồn gốc của nó. PostgreSQL đạt được tính đồng thời bằng cách không bao giờ sửa đổi một hàng tại chỗ. Khi bạn thực hiện một lệnh UPDATE, Postgres thực chất thực hiện một lệnh DELETE và một lệnh INSERT. Phiên bản cũ của hàng (một "tuple" theo thuật ngữ của Postgres) không bị xóa ngay lập tức; nó được đánh dấu là "chết", và một phiên bản mới được chèn vào. Một lệnh DELETE chỉ đơn giản là đánh dấu tuple đó là đã chết.

Mỗi tuple có hai cột hệ thống ẩn để quản lý khả năng hiển thị của nó: xmin và xmax.

• xmin: ID giao dịch đã tạo ra tuple này.
• xmax: ID giao dịch đã xóa tuple này. Ban đầu là 0.

Khi một giao dịch T1 cập nhật một hàng, nó sẽ đặt xmax của tuple cũ thành T1 và chèn một tuple mới với xmin được đặt thành T1. Bất kỳ giao dịch nào khác bắt đầu trước khi T1 được commit vẫn có thể thấy tuple cũ vì T1 chưa có trong "snapshot" trạng thái cơ sở dữ liệu của nó. Đây là cách Postgres tránh khóa đọc (read locks).

Một "dead tuple" là một phiên bản hàng có xmax được đặt thành một ID giao dịch mà tất cả các giao dịch đang chạy hiện tại đều có thể thấy. Điều này có nghĩa là không có giao dịch nào đang hoạt động có thể cần xem phiên bản này nữa, khiến nó sẵn sàng để được thu hồi.

Quá trình này diễn ra như sau:

Không gian "chết" này không được trả lại cho hệ điều hành. Nó vẫn nằm trong tệp "heap" của bảng, tạo ra các túi rỗng. Đây chính là bloat. Khi đủ số lượng dead tuple tích tụ, bảng và các index của bạn sẽ phình to về mặt vật lý trên đĩa so với dữ liệu sống mà chúng chứa. Điều này dẫn đến:

1. Tăng I/O: Các truy vấn phải đọc nhiều trang 8KB hơn từ đĩa để tìm dữ liệu sống mà chúng cần.
2. Hiệu quả cache kém: Bảng bị bloat tiêu thụ một lượng không tương xứng trong shared buffers (bộ nhớ cache của Postgres), đẩy ra các dữ liệu hữu ích khác.
3. Quét chậm hơn: Cả quét tuần tự (sequential scans) và quét index (index scans) đều trở nên chậm hơn vì chúng phải duyệt qua nhiều trang hơn, trong đó nhiều trang chứa đầy rác. Bloat index đặc biệt tai hại, vì nó có thể vô hiệu hóa chính mục đích của việc có index.

Chế độ lỗi cuối cùng mà VACUUM ngăn chặn là Transaction ID Wraparound (Tràn số định danh giao dịch). ID giao dịch là các số nguyên 32-bit. Sau khoảng 2 tỷ giao dịch ghi, chúng sẽ quay vòng. Nếu các tuple chết cũ không được "đóng băng" (đánh dấu là có thể nhìn thấy bởi tất cả các giao dịch trong tương lai), cơ sở dữ liệu phải tắt để ngăn chặn hỏng dữ liệu. Việc vacuum thường xuyên không chỉ để cải thiện hiệu năng; nó là một yêu cầu vận hành bắt buộc.

Phát hiện và Đo lường Bloat

Bạn không thể sửa những gì bạn không thể đo lường. Các công cụ giám sát chung chung sẽ không chỉ ra được bloat. Bạn cần truy vấn các số liệu thống kê nội bộ của Postgres. Câu truy vấn sau, được điều chỉnh từ nhiều nguồn cộng đồng khác nhau, cung cấp một ước tính đáng tin cậy mà không cần các extension như pgstattuple. Nó hoạt động bằng cách so sánh kích thước ước tính thống kê của các tuple sống với kích thước thực tế trên đĩa của bảng.

Câu truy vấn phát hiện Bloat kinh điển

Câu truy vấn này được thiết kế để chạy bởi một superuser hoặc một role có đủ quyền hạn để kiểm tra số liệu thống kê của bảng.

-- Detects table and index bloat.
-- Works on PostgreSQL 9.5+
-- Note: The accuracy of this query depends on the freshness of table statistics.
-- Run ANALYZE on your tables before trusting this output for the first time.

WITH constants AS (
  SELECT
    current_setting('block_size')::numeric AS bs,
    23 AS hdr,
    4 AS ma
),
tables AS (
  SELECT
    ns.nspname AS schema_name,
    tbl.relname AS table_name,
    hdr,
    ma,
    bs,
    tbl.reltuples,
    tbl.relpages,
    (
      (tbl.reltuples * (
        -- Average row width, accounting for overhead
        AVG(stat.stanullfrac) * (
          -- Average width of nullable columns
          SELECT SUM(stynsp) FROM pg_type typ JOIN pg_attribute attr ON typ.oid = attr.atttypid WHERE attr.attrelid = tbl.oid AND NOT attr.attnotnull AND attr.attnum > 0
        ) + (1 - AVG(stat.stanullfrac)) * (
          -- Average width of non-nullable columns
          SELECT SUM(stynsp) FROM pg_type typ JOIN pg_attribute attr ON typ.oid = attr.atttypid WHERE attr.attrelid = tbl.oid AND attr.attnotnull AND attr.attnum > 0
        )
      ) + ma - (
        -- Null bitmap overhead
        CASE WHEN tbl.reltuples > 0 THEN ((COUNT(DISTINCT stat.staattnum) + 7) / 8) ELSE 0 END
      ))
      -- Add tuple header overhead
      + hdr
    ) AS avg_row_width
  FROM
    pg_class tbl
  JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
  JOIN pg_attribute attr ON attr.attrelid = tbl.oid
  JOIN pg_statistic stat ON stat.starelid = tbl.oid AND stat.staattnum = attr.attnum
  WHERE
    tbl.relkind = 'r' -- only tables
    AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
    AND tbl.reltuples > 0
  GROUP BY
    ns.nspname, tbl.relname, hdr, ma, bs, tbl.reltuples, tbl.relpages
),
table_bloat AS (
  SELECT
    schema_name,
    table_name,
    reltuples AS live_tuples,
    relpages AS page_count,
    bs,
    -- Estimated pages needed for live data
    CEIL((reltuples * avg_row_width) / (bs - hdr)) AS estimated_pages,
    relpages AS actual_pages,
    -- Actual size
    pg_size_pretty(relpages * bs) AS actual_size
  FROM
    tables
),
table_bloat_summary AS (
  SELECT
    schema_name,
    table_name,
    live_tuples,
    -- Bloat in pages and bytes
    (actual_pages - estimated_pages) AS bloat_pages,
    pg_size_pretty((actual_pages - estimated_pages) * bs) AS bloat_size,
    -- Bloat percentage
    ROUND(100 * (actual_pages - estimated_pages) / actual_pages, 2) AS bloat_percentage
  FROM
    table_bloat
  WHERE
    -- Filter for significant bloat
    (actual_pages - estimated_pages) > 0
),
index_bloat AS (
  SELECT
    ns.nspname AS schema_name,
    tbl.relname AS table_name,
    idx.relname AS index_name,
    idx.reltuples,
    idx.relpages,
    -- Estimated index size
    ROUND((idx.reltuples * (
      -- Average index entry size
      SELECT
        -- Index header + item identifier + data
        16 + AVG(pg_column_size(indexdatacolumns))
      FROM
        pg_index i
      JOIN pg_stats s ON s.tablename = tbl.relname
      AND s.attname = ANY(string_to_array(i.indkey::text, ' ')::text[])
      WHERE i.indexrelid = idx.oid
    )) / current_setting('block_size')::numeric, 1) AS estimated_pages,
    idx.relpages AS actual_pages,
    pg_size_pretty(idx.relpages * current_setting('block_size')::numeric) AS actual_size
  FROM
    pg_class idx
  JOIN pg_index i ON i.indexrelid = idx.oid
  JOIN pg_class tbl ON tbl.oid = i.indrelid
  JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
  WHERE
    idx.relkind = 'i' -- only indexes
    AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
    AND idx.reltuples > 0
),
index_bloat_summary AS (
  SELECT
    schema_name,
    table_name,
    index_name,
    reltuples,
    (actual_pages - estimated_pages) AS bloat_pages,
    pg_size_pretty((actual_pages - estimated_pages) * current_setting('block_size')::numeric) AS bloat_size,
    ROUND(100 * (actual_pages - estimated_pages) / actual_pages, 2) AS bloat_percentage
  FROM
    index_bloat
  WHERE
    (actual_pages - estimated_pages) > 10 -- Filter for significant bloat
)
-- Combine and present the results
SELECT
  'table' AS object_type,
  schema_name,
  table_name AS object_name,
  bloat_percentage,
  bloat_size
FROM
  table_bloat_summary
WHERE bloat_percentage > 20.0
UNION ALL
SELECT
  'index' AS object_type,
  schema_name,
  index_name AS object_name,
  bloat_percentage,
  bloat_size
FROM
  index_bloat_summary
WHERE bloat_percentage > 30.0
ORDER BY
  bloat_size DESC;

Diễn giải kết quả: Chạy câu truy vấn này sẽ cho bạn một danh sách các bảng và index bị bloat nhiều nhất.

Một quy tắc kinh nghiệm:

• Table Bloat > 20%: Đáng để điều tra.
• Index Bloat > 30-40%: Thường là một gánh nặng hiệu năng đáng kể và là ứng cử viên hàng đầu để khắc phục.

Tuyến phòng thủ đầu tiên: Tinh chỉnh Autovacuum

autovacuum là dịch vụ dọn dẹp tích hợp sẵn của PostgreSQL. Nó là một tiến trình daemon định kỳ chạy các hoạt động VACUUM và ANALYZE trên các bảng có số lượng INSERT, UPDATE hoặc DELETE đáng kể.

Tuy nhiên, các cài đặt mặc định của autovacuum nổi tiếng là quá thận trọng. Chúng được thiết kế để giảm thiểu tiêu thụ tài nguyên trên phần cứng cấu hình thấp và hoàn toàn không đủ cho các workload ghi từ trung bình đến cao. Dựa vào chúng là một con đường phổ biến dẫn đến suy giảm hiệu năng trên production.

Tại sao mặc định lại thất bại

Autovacuum quyết định kích hoạt VACUUM trên một bảng khi số lượng dead tuple vượt quá một ngưỡng được tính bằng công thức này: vacuum_threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number_of_tuples

Giá trị mặc định là:

• autovacuum_vacuum_threshold = 50
• autovacuum_vacuum_scale_factor = 0.20 (tức là 20% của bảng)

Hãy xem xét bảng event_logs với 100 triệu hàng. Ngưỡng sẽ là 50 + 0.20 * 100,000,000 = 20,000,050. Autovacuum sẽ đợi cho đến khi 20 triệu dead tuple tích tụ trước khi nó xem xét việc dọn dẹp. Đến thời điểm đó, bảng đã bị bloat nghiêm trọng. Hệ số scale factor là thủ phạm chính đối với các bảng lớn.

Tinh chỉnh Autovacuum cho Môi trường Production

Giải pháp là thoát khỏi các cài đặt mặc định toàn cục và đặt các cấu hình mạnh mẽ, theo từng bảng cho các bảng có lượng ghi cao của bạn.

Chiến lược

1. Giảm mạnh autovacuum_vacuum_scale_factor.
2. Đặt một autovacuum_vacuum_threshold tĩnh, hợp lý để đảm bảo bảng được vacuum thường xuyên, ngay cả khi nó nhỏ.
3. Tùy chọn, làm cho các worker của vacuum hoạt động mạnh mẽ hơn bằng cách tinh chỉnh giới hạn chi phí của chúng.

Công thức cho một bảng có lượng ghi cao

Hãy tinh chỉnh bảng event_logs của chúng ta.

-- Tune a specific high-write table.
-- A scale factor of 0.02 means vacuum triggers after 2% of the table has changed.
-- A threshold of 1000 ensures it runs even if the table is temporarily small.
ALTER TABLE public.event_logs SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 500
);

-- For extremely high-throughput tables, you might also make the vacuum worker
-- faster by reducing its sleep time between operations.
-- This table will now be vacuumed more often and more quickly.
ALTER TABLE public.event_logs SET (
  autovacuum_vacuum_cost_delay = '10ms' -- Default is 20ms on some versions
);

Bằng cách đặt các tham số lưu trữ này trực tiếp trên bảng, bạn sẽ ghi đè các cài đặt toàn cục trong postgresql.conf chỉ cho quan hệ đó. Đây là cách tiếp cận chính xác và có mục tiêu. Bạn không muốn đặt các cài đặt toàn cục quá mạnh mẽ, vì điều đó có thể thêm tải I/O không cần thiết vào các bảng nhỏ, tĩnh.

Để tìm các bảng có lượng ghi cao, bạn có thể truy vấn pg_stat_user_tables:

SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze
FROM
  pg_stat_user_tables
ORDER BY
  n_tup_upd + n_tup_del DESC
LIMIT 20;

Điều này cho bạn thấy bảng nào đang trải qua nhiều thay đổi nhất và do đó là ứng cử viên hàng đầu cho việc tinh chỉnh autovacuum tùy chỉnh.

Vũ khí hạng nặng: Xây dựng lại không downtime với pg_repack

Điều gì sẽ xảy ra nếu bạn đã tinh chỉnh autovacuum, nhưng một bảng đã bị thừa 12 GB với 65% bloat? Tiến trình VACUUM tiêu chuẩn của Autovacuum không thu nhỏ tệp trên đĩa hoặc trả lại không gian cho HĐH; nó chỉ đánh dấu không gian trong tệp là trống để cho các lệnh INSERT trong tương lai. Để thực sự thu hồi không gian và thu nhỏ tệp, bạn cần viết lại bảng.

Lệnh VACUUM FULL gốc làm điều này, nhưng nó yêu cầu một khóa ACCESS EXCLUSIVE trên bảng trong toàn bộ thời gian hoạt động, chặn tất cả các lượt đọc và ghi. Đối với một bảng production lớn và quan trọng, điều này tương đương với việc đưa ứng dụng của bạn vào chế độ ngoại tuyến trong vài phút hoặc thậm chí vài giờ. Đó là một lựa chọn không khả thi.

Hãy đến với pg_repack, extension cộng đồng tiêu chuẩn vàng để xây dựng lại bảng và index trực tuyến.

Cách pg_repack hoạt động

pg_repack xây dựng lại một bảng và các index của nó với việc khóa tối thiểu, cho phép loại bỏ bloat thực sự mà không có thời gian chết.

1. Bảng Log & Trigger: Nó tạo một bảng log và cài đặt một trigger trên bảng mục tiêu của bạn để ghi lại tất cả các lệnh INSERT, UPDATE và DELETE xảy ra trong quá trình.
2. Tạo Bảng Mới: Nó tạo một bảng mới, được đóng gói hoàn hảo với cấu trúc giống như bảng gốc.
3. Sao chép Dữ liệu: Nó sao chép hiệu quả tất cả các hàng từ bảng gốc bị bloat sang bảng mới.
4. Xây dựng lại Index: Nó xây dựng các index mới, gọn gàng trên bảng mới.
5. Áp dụng Thay đổi Delta: Nó áp dụng tất cả các thay đổi được ghi lại trong bảng log vào bảng mới, đưa nó về trạng thái cập nhật.
6. Bước Hoán đổi: Đây là phần kỳ diệu. Trong một giao dịch duy nhất, giữ một khóa ACCESS EXCLUSIVE ngắn gọn, nó:
   • Xóa các index và constraint của bảng gốc.
   • Đổi tên các bảng (bảng mới trở thành cũ, bảng cũ trở thành ứng cử viên tạm thời để xóa).
   • Tạo lại các constraint trên bảng production mới được đổi tên.
7. Dọn dẹp: Xóa tệp bảng cũ, bị bloat.

Phần việc nặng nhọc (bước 3-5) diễn ra trực tuyến. Khóa độc quyền (bước 6) thường chỉ kéo dài từ vài mili giây đến vài giây, ngay cả đối với các bảng quy mô terabyte, làm cho nó an toàn để sử dụng trên production.

Sử dụng pg_repack

Đầu tiên, bạn cần cài đặt extension trên máy chủ cơ sở dữ liệu và trong cơ sở dữ liệu của bạn.

-- Trong psql, kết nối với cơ sở dữ liệu mục tiêu của bạn
CREATE EXTENSION pg_repack;

Sau đó, từ một terminal trên máy chủ cơ sở dữ liệu hoặc bất kỳ máy nào có quyền truy cập mạng và file thực thi pg_repack:

# Ví dụ: Repack bảng 'event_logs' trong cơ sở dữ liệu 'mydb'
# -k, --no-order: Bỏ qua việc sắp xếp theo khóa chính. Nhanh hơn, nhưng bạn mất trật tự vật lý.
# -j 4: Sử dụng 4 job song song để tạo index (nếu được hỗ trợ).
# --wait-timeout=60: Chờ tối đa 60 giây để có được khóa cuối cùng. Hủy bỏ nếu một truy vấn chạy dài chặn nó.

pg_repack -d mydb -h myhost.db.example.com -U db_admin_user -k -j 4 --wait-timeout=60 --table public.event_logs

Điều kiện tiên quyết:

• Bạn phải có đủ không gian đĩa trống để tạm thời chứa một bản sao thứ hai của bảng và các index của nó.
• Bảng phải có một PRIMARY KEY hoặc một index UNIQUE trên một cột NOT NULL.
• Bạn cần có quyền CREATE trên schema.

Vai trò của Visibility Map

Một mảnh ghép cuối cùng của câu đố là Visibility Map (VM). Đây là một cấu trúc dữ liệu bitmap nhỏ gọn theo dõi những trang nào trong tệp heap của bảng chỉ chứa các tuple "all-visible" - nghĩa là chúng có thể nhìn thấy được đối với tất cả các giao dịch hiện tại và tương lai.

Khi VACUUM chạy, nó sẽ cập nhật VM. Điều này có ý nghĩa hiệu năng rất lớn đối với một loại truy vấn cụ thể: index-only scans (quét chỉ dùng index).

Nếu bạn truy vấn một cột được chứa hoàn toàn trong một index (ví dụ: SELECT user_id FROM event_logs WHERE processed_at > NOW() - '1 day'), và VM xác nhận rằng tất cả các tuple trên các trang mà index trỏ tới đều là all-visible, Postgres có thể trả lời truy vấn chỉ bằng cách đọc index. Nó không bao giờ phải truy cập vào heap của bảng chính. Điều này cực kỳ nhanh.

Nếu VM đã lỗi thời do việc vacuum không thường xuyên, Postgres không thể chắc chắn về khả năng hiển thị của tuple. Nó buộc phải thực hiện một index scan sau đó là heap fetch cho mỗi hàng để kiểm tra giá trị xmin/xmax của nó. Điều này hoàn toàn vô hiệu hóa lợi ích của một index-only scan và là một nguồn phổ biến của sự chậm chạp bí ẩn trong truy vấn.

Việc tinh chỉnh autovacuum một cách mạnh mẽ không chỉ để thu hồi không gian; nó còn để giữ cho Visibility Map luôn mới mẻ để cho phép các tối ưu hóa mạnh mẽ nhất của bộ lập kế hoạch truy vấn.

Kiểm tra kiến thức

FAQ tương tác

Kết luận: Một Kế hoạch Hành động cho Cơ sở dữ liệu không bị Bloat

Tình trạng bloat cơ sở dữ liệu không phải là vấn đề "nếu", mà là "khi nào". Đối với bất kỳ hệ thống nào có workload ghi động, một cách tiếp cận thụ động đảm bảo sự suy giảm hiệu năng cuối cùng. Một chiến lược chủ động, đa lớp là cần thiết cho sức khỏe lâu dài.

Kế hoạch hành động của bạn:

1. Giám sát: Sử dụng câu truy vấn phát hiện bloat được cung cấp ở đây. Lên lịch chạy hàng tuần và đưa kết quả vào hệ thống giám sát của bạn để cảnh báo về các bảng vượt ngưỡng bloat (ví dụ: 25%).
2. Tinh chỉnh chủ động: Xác định 10-20 bảng có lượng ghi cao nhất của bạn bằng cách sử dụng pg_stat_user_tables. Áp dụng ngay lập tức các cài đặt autovacuum mạnh mẽ, theo từng bảng. Đừng đợi bloat trở thành một vấn đề.
3. Khắc phục an toàn: Khi bạn tìm thấy một bảng đã bị bloat đáng kể, hãy lên lịch một hoạt động pg_repack trong thời gian lưu lượng truy cập thấp. Thời gian chết là tối thiểu, nhưng vẫn là thực hành tốt nhất để thực hiện các hoạt động lớn một cách cẩn thận.
4. Đào tạo: Đảm bảo nhóm của bạn hiểu tác động của các giao dịch chạy dài. Triển khai các cài đặt connection pooler như idle_in_transaction_session_timeout trong PgBouncer để tự động chấm dứt các giao dịch nhàn rỗi nhưng vẫn đang mở.

Bằng cách nắm vững chu trình phát hiện, phòng ngừa và khắc phục này, bạn chuyển từ một người lính cứu hỏa phản ứng thành một kỹ sư cơ sở dữ liệu có chiến lược, đảm bảo cơ sở hạ tầng PostgreSQL của bạn vẫn nhanh, hiệu quả và đáng tin cậy ở quy mô lớn.

Đọc thêm

• Tài liệu PostgreSQL: The Autovacuum Daemon
• Tài liệu PostgreSQL: VACUUM
• Tài liệu chính thức của pg_repack
• The Visible Internals of PostgreSQL: A Deep Dive into MVCC (bài viết liên quan giả tưởng - Phân tích sâu về nội tại của MVCC trong PostgreSQL)