PostgreSQL10基础（6）Analyze和Vacuum

爱被打了一巴掌 2023-07-10 12:44 8阅读 0赞

> 参考文档
> 
>  *  https://www.postgresql.org/docs/10/sql-analyze.html
>  *  https://www.postgresql.org/docs/10/routine-vacuuming.html
>  *  https://www.postgresql.org/docs/10/sql-vacuum.html
>  *  https://www.postgresql.org/docs/10/runtime-config-autovacuum.html
>  *  https://www.postgresql.org/docs/10/runtime-config-resource.html\#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

# Analyze #

Analyze命令用于统计数据库表数据，统计结果存储到pg\_statistic系统表中。数据库进行基于成本的优化（CBO）时通过统计数据优化SQL语句的解释计划。

#### 命令 ####

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

*  VERBOSE：显示处理信息
 *  table\_name：指定分析的表，如果未指定将分析当前数据库（逻辑库）中所有常规表、分区表、物化视图。分区表及其子表将被分析
 *  column\_name：指定分析的列名，可以用逗号分割多列，默认对所有列分析

#### 权限说明 ####

*  表的所有者或者超级用户可以执行analyze命令
 *  数据库所有者也可以分析库中的所有表
 *  不具备权限的表将被跳过分析

#### 影响 ####

Analyze只需要获取一个read锁，不会影响表的正常读写。

#### 统计量 ####

analyze默认统计most\_common\_vals（最常见值）和histogram\_bounds（区间内含有相似数据条数的值列表）100个

可以通过设置全局变量default\_statistics\_target修改统计信息量(默认值100)

可以通过alter table XX alter column XX set STATISTICS 来设置每个列的统计量，值在0-10000之间，-1表示使用default\_statistics\_target值

#### 建议 ####

大量读的数据库可以每天在低负载时运行Analyze（大量更新活动将不够频繁）

# Vacuum #

Vacuum用于清理死亡元组占用的存储空间，默认删除或因更新过期（为了MVVC）的元组不会被物理删除。因此需要周期性的进行Vacuum，尤其是频繁更新的表

#### 命令 ####

VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE | DISABLE_PAGE_SKIPPING } [, ...] ) ] [ table_name [ (column_name [, ...] ) ] ]
    VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table_name ]
    VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table_name [ (column_name [, ...] ) ] ]

*  FULL:
    
     *  不加full时，Vacuum标记过期磁盘空间为可用，用于该表以后重用，但磁盘不会释放给操作系统，执行Vacuum操作不会影响表的读写。
     *  加full时，Vacuum将表数据复制到另外一块磁盘空间，负责完成后删除老的磁盘空间。老空间将被释放给操作系统，但需要足够的磁盘空间才能完成操作。且操作执行时添加exclusive lock在表上，表将无法正常读写。
 *  ANALYZE：同时执行Vacuum和analyze
 *  VERBOSE：显示处理信息
 *  table\_name：如果未指定将清扫当前数据库（逻辑库）中所有常规表、分区表、物化视图。分区表及其子表将被清扫。
 *  column\_name：指定分析的列名，可以用逗号分割多列，默认对所有列分析
 *  FREEZE和DISABLE\_PAGE\_SKIPPING在此不做详细介绍

#### 权限说明 ####

*  表的所有者或者超级用户可以执行vacuum命令
 *  数据库所有者也可以清扫库中的所有表
 *  不具备权限的表将被跳过清扫

#### 影响 ####

FULL会影响表的正常读写。

#### 建议 ####

*  生产数据库建议频繁Vacuum（至少每晚）以清理死亡行。
 *  大量添加或删除行后建议进行VACUUM ANALYZE
 *  FULL不建议日常使用，因为会缩表，但可以降低磁盘占用
 *  Vacuum会消耗IO，可以使用基于消耗的Vacuum延迟功能

#### Cost-based Vacuum Delay ####

当执行Vacuum和analyze时，系统维护一个内部计数器记录消耗的IO。当消耗达到acuum\_cost\_limit时，将停止执行命令vacuum\_cost\_delay毫秒，然后重新计数。

此功能的目的是降低Vacuum和Analyze操作对系统的性能影响。默认功能关闭，可以设置vacuum\_cost\_delay大于0开启

*  vacuum\_cost\_delay：单位毫秒，vacuum休眠时长，默认为0，将禁用此功能。设置为大于0值将开启功能。建议设置为10或20.
 *  vacuum\_cost\_page\_hit：vacuum命中shared buffer缓存，并锁定缓存的成本，默认为1
 *  vacuum\_cost\_page\_miss：当Vacuum必须读取磁盘时的成本，默认为10
 *  vacuum\_cost\_page\_dirty: 当vacuum修改block的成本，默认20
 *  vacuum\_cost\_limit：vacuum触发休眠的成本，默认200。

# 自动清理和自动分析 #

#### 参数 ####

*  autovacuum：布尔值，表示是否启用自动清扫进程，默认打开。但当track\_count（默认开启）也被开启时才能启用
 *  log\_autovacuum\_min\_duration: 整型，自动扫描被记录的最少耗时（毫秒），设置为0将记录所有自动清扫操作。默认为-1，禁用日志记录。
 *  autovacuum\_max\_workers:设置自动清扫进程的最大数量，默认为3.
 *  autovacuum\_naptime：设置在一个数据库上执行两次自动清扫动作的最小间隔时间，单位为秒，默认60
 *  autovacuum\_vacuum\_threshold：设置在一张表上触发Vacuum操作的最小更新或删除元组数，默认50
 *  autovacuum\_analyze\_threshold：设置在一张表上触发analyze操作的最小更新或删除元组数，默认50
 *  autovacuum\_vacuum\_scale\_factor：设置在一张表上触发Vacuum操作的最小变更百分比，默认0.2（表有20%的变动）,可以设置系统级参数，也可以为每张表设置独立值。
 *  autovacuum\_analyze\_scale\_factor：设置在一张表上触发analyze操作的最小变更百分比，默认0.1（表有10%的变动）,可以设置系统级参数，也可以为每张表设置独立值。
 *  autovacuum\_vacuum\_cost\_delay：设置基于成本的延迟，单位毫秒，如果值为-1，则使用vacuum\_cost\_delay值，默认为20。可以设置系统级参数，也可以为每张表设置独立值。
 *  autovacuum\_vacuum\_cost\_limit：触发延迟的成本数，默认为-1，表示使用vacuum\_cost\_limit值。可以设置系统级参数，也可以为每张表设置独立值。

另外自动清理还将清理事务ID，防止其超过最大值。该清理无法被关闭。

#### 触发条件 ####

*  autovacuum和track\_count都被打开
 *  清扫条件：元组增删改数量>autovacuum\_analyze\_threshold + autovacuum\_vacuum\_scale\_factor \* 总元祖数
 *  自动分析条件： 元组增删改数量>autovacuum\_analyze\_threshold + autovacuum\_analyze\_scale\_factor \* 总元祖数

#### 进程 ####

数据库将自动每隔autovacuum\_naptime秒在每一个逻辑库启动一个进程，但总数不能大于autovacuum\_max\_workers，如果有等待处理的数据库，进程将在处理完一个库后立即处理下一个。

每个进程都将检查数据库中每张表，判断是否需要执行vacuum和/或analyze

# 执行情况 #

#### 历史执行视图pg\_stat\_all\_tables ####

select relid,schemaname,relname,last_vacuum,vacuum_count,last_autovacuum,autovacuum_count,last_analyze,analyze_count,last_autoanalyze,autoanalyze_count from pg_stat_all_tables

每张表一条记录

*  last\_vacuum：上次手动vacuum时间
 *  vacuum\_count：总计手动vacuum次数
 *  last\_autovacuum：上次自动vacuum时间
 *  autovacuum\_count：总计自动vacuum次数
 *  last\_analyze：上次手动analyze时间
 *  analyze\_count：总计手动analyze次数
 *  last\_autoanalyze：上次自动analyze时间
 *  autoanalyze\_count：总计自动analyze次数

该视图还有其他有助于性能分析的字段

*  seq\_scan：顺序扫描次数
 *  seq\_tup\_read：顺序扫描读取的存活行数
 *  idx\_scan：索引扫描次数
 *  idx\_tup\_read：索引扫描读取的存活行数
 *  n\_tup\_ins：插入的行数
 *  n\_tup\_upd：更新的行数
 *  n\_tup\_del：删除的行数
 *  n\_live\_tup：存活行数
 *  n\_dead\_tup：死亡行数
 *  n\_mod\_since\_analyze：上次分析以来修改的行数

#### 执行过程视图pg\_stat\_progress\_vacuum ####

> 9.6版本新增  
> 字段说明：

*  pid：进程ID
 *  datid：数据库OID
 *  datname: 数据库名称
 *  relid：当前Vacuum的表ID
 *  phrase：处理阶段，见下文
 *  heap\_blks\_total：表中总heap block数量
 *  heap\_blks\_scanned：被扫描的数量，可用性视图会协助跳过一部分block
 *  heap\_blks\_vacuumed：完成清扫的数量
 *  index\_vacuum\_count：完成索引清扫次数
 *  max\_dead\_tuples：执行一次索引清扫前遇到的最大死亡元组数量，基于maintenance\_work\_mem.
 *  num\_dead\_tuples：上次索引清扫后找到的死亡元组数量

阶段说明

*  initializing：准备扫描heap
 *  scanning heap：扫描heap，会对每个页进行修剪和整理，可能执行冻结操作。heap\_blks\_scanned列可以观察执行进度。如果维护内存不足，可能执行多次
 *  vacuuming indexes：清扫索引。如果表有索引，将最少执行一次本动作。
 *  vacuuming heap：清扫heap，每次清扫索引后进行
 *  cleaning up indexes：清理索引。在所有heap扫描完成，所有索引和heap被vacuum完成后执行
 *  truncating heap：缩减heap以归还处于表最后位置的空页面到操作系统。磁盘空闲将增大，但只有空页面位于最后位置才会被归还
 *  performing final cleanup：执行最后的清理，此阶段将清理free space map，更新statistics视图

graph LR
    初始化-->扫描堆
    扫描堆-->清理索引
    清理索引-->清理堆
    清理堆-->扫描堆
    清理堆-->最终清理索引
    最终清理索引-->缩减堆
    缩减堆-->清理完成

# 总结 #

通过pg\_stat\_all\_tables视图发现默认设置下Vacuum和Analyze执行不够频繁，可以考虑定时每日清理+大量操作后清理，并通过延迟清理功能降低对生产系统性能影响