Amazon Redshift 性能优化指南：VACUUM、ANALYZE 与运维最佳实践

-- 对单表执行完整 VACUUM
VACUUM sales;

-- 对整个数据库执行（谨慎使用）
VACUUM;

-- 强制完全排序（不跳过）
VACUUM sales TO 100 PERCENT;

-- 当 75% 以上已排序时跳过
VACUUM sales TO 75 PERCENT;

VACUUM DELETE ONLY sales;

VACUUM SORT ONLY sales;

VACUUM REINDEX listing;

VACUUM RECLUSTER listing;

-- 加速 RECLUSTER
VACUUM RECLUSTER listing BOOST;

-- 加速 FULL
VACUUM FULL sales BOOST;

               ┌─────────────────────────────────────┐
               │         需要维护的表                │
               └──────────────┬──────────────────────┘
                              │
              ┌───────────────┼───────────────┐
              ▼               ▼               ▼
        大量删除后       大量插入后      交错排序键表
              │               │               │
              ▼               ▼               ▼
        DELETE ONLY      SORT ONLY        REINDEX
              │               │               │
              └───────┬───────┘               │
                      ▼                       │
              两者都需要？                    │
                      │                       │
                      ▼                       │
                    FULL ◄────────────────────┘

SELECT
    "table",
    size AS size_mb,
    pct_used,
    tbl_rows,
    unsorted,
    vacuum_sort_benefit
FROM SVV_TABLE_INFO
WHERE unsorted > 5  -- 未排序比例超过 5%
ORDER BY size_mb DESC;

SELECT * FROM SVV_VACUUM_PROGRESS;

SELECT
    table_name,
    elapsed_time/1000000 AS elapsed_seconds,
    sort_partitions,
    row_delta,
    block_delta
FROM SVV_VACUUM_SUMMARY
ORDER BY xid DESC
LIMIT 10;

SELECT * FROM SVL_VACUUM_PERCENTAGE ORDER BY xid DESC;

-- 分析单表
ANALYZE sales;

-- 分析整个数据库
ANALYZE;

-- 仅更新谓词列的统计信息
ANALYZE PREDICATE COLUMNS sales;

SELECT
    "table",
    stats_off
FROM SVV_TABLE_INFO
WHERE stats_off > 10
ORDER BY stats_off DESC;

-- 按月分表示例
CREATE TABLE sales_2024_01 (LIKE sales);
CREATE TABLE sales_2024_02 (LIKE sales);
-- ...

-- 1. 创建新表
CREATE TABLE sales_new (LIKE sales);

-- 2. 批量插入（自动排序）
INSERT INTO sales_new SELECT * FROM sales;

-- 3. 重命名替换
ALTER TABLE sales RENAME TO sales_old;
ALTER TABLE sales_new RENAME TO sales;

-- 4. 删除旧表
DROP TABLE sales_old;

-- 查看所有表的详细信息
SELECT
    "table",
    size AS size_mb,
    pct_used,
    tbl_rows,
    encoded,
    diststyle,
    sortkey_num,
    sortkey1,
    unsorted,
    stats_off
FROM SVV_TABLE_INFO
ORDER BY size_mb DESC;

-- 分析表的最佳压缩编码
ANALYZE COMPRESSION sales;

-- 查看 COPY 加载错误
SELECT * FROM STL_LOAD_ERRORS ORDER BY starttime DESC LIMIT 20;

-- 查看查询执行错误
SELECT * FROM STL_ERROR ORDER BY recordtime DESC LIMIT 20;

-- 查看最近的慢查询
SELECT
    query,
    substring(querytxt, 1, 100) AS query_text,
    elapsed/1000000 AS elapsed_seconds,
    queue_time/1000000 AS queue_seconds
FROM STL_QUERY
WHERE elapsed > 60000000  -- 超过 60 秒
ORDER BY endtime DESC
LIMIT 20;

python analyze-vacuum-schema.py \
    --db mydb \
    --db-user admin \
    --db-host mycluster.xxx.redshift.amazonaws.com \
    --schema-name public \
    --vacuum-flag true \
    --analyze-flag true