本文主要是介绍PostgreSQL10基础(6)Analyze和Vacuum,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
参考文档
- 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执行不够频繁,可以考虑定时每日清理+大量操作后清理,并通过延迟清理功能降低对生产系统性能影响
这篇关于PostgreSQL10基础(6)Analyze和Vacuum的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!