精华内容
下载资源
问答
  • PostgreSQL-Vacuum

    2021-07-10 15:27:25
    查询auto vacuum文档,发现该功能气功需要以下3个条件: (1)autovacuum = on (2)track_counts = on (3)/etc/hosts #能 ping通 localhost 所以查看一下,/etc/hosts文件127.0.0.1是否存在或者被注释,修改使其...

    Postgresql表中隐藏字段

            -oid: 行的对象标识符,创建表的时候使用了with oid,或者是设置了default_with_oids 配置参数时出现;

            -tableoid:包含本行的表的 OID。

            -xmin:插入该行版本的事务的标识(事务 ID);

            -xmax:删除事务的标识(事务ID),如果不是被删除的行版本,那么是零;

            -cmin:插入事务内部的命令标识,从0开始;

            -cmax:删除事务内部的命令标识,从0开始;

            -ctid:一个行版本在它所处的表内的物理位置。

     Postgresql执行update操作

     MVCC实现

    --ACID要素

            -A:原子性——事务的不可分割性

            -C:一致性——事务的完整性

            -I:隔离性——并发事务的隔离性

            -D:持久性——事务的持久性,WAL机制

    --事务隔离级别

    隔离级别

    脏读

    不可重复读

    幻读

    读未提交(read uncommitted

    读已提交(read committed

    避免

    可重复读(repeatable read

    避免

    避免

    可串行化(serializable

    避免

    避免

    避免

    --MVCC三种实现方式

            -写新数据时,把旧的数据放到一个单独的空间内,如回滚段,其他事务读取时,从回滚段的去读,如oracle&mysq中的innodb引擎。

            -写数据时,把旧的数据放到一个临时表空间,如sql server(tempdb)。

            -写数据时,旧数据不删除,插入新的数据,如postgresql。

    Vacuum的功能

    --回收空间

            -vacuum:删除数据将其所占空间重新置为可用,新数据优先插入这些可用空间;

    执行效率较高并且通过共享锁可以和其他操作并行,对插入会有一定的影响。

            -vacuum full:删除数据时会先将原来可用数据导入一个新的数据文件中,再删除

    原来的数据,同时还要重建索引,整个过程耗时较长,执行效率低,但执行后会对性

    能有很大的提升;整个执行过程通过排他锁保障其他事务均不可访问该表。

    Vacuum异常
        第一种:number of page slots needed (1277312) exceeds max_fsm_pages (819200)
        第二种:relation "XXX" contains more than "max_fsm_pages" pages with useful free space
        第三种:could not read block 512591 of relation 1663/16396/16768: Result too large

    原因分析:fsm(free space map),fsm是记录数据页上有因为vacuum而存在的是空闲空间,方便能够快速的插入数据,而max_fsm_pages是存在一定的上限的,所以这是明显超出上限报错。
    
    处理:
    方法一:vacuum full,尽量选择在低谷期
    方法二:修改max_fsm_pages,max_fsm_relations的值,但是需要重启数据库
         gpconfig -c max_fsm_relations -v 200
         gpconfig -c max_fsm_pages -v 5000

    --冻结tuple的xid-freeze

            PG会在每条记录(tuple)的header中,存放xmin,xmax信息(增删改事务ID)。transactionID的最大值为2的32次,即无符整形来表示。当transactionID超过此最大值后,会循环使用。这会带来一个问题:就是最新事务的transactionID会小于老事务的transactionID。如果这种情况发生后,PG就没有办法按transactionID来区分事务的先后,也没有办法实现MVCC了。因此PG用vacuum后台进程,按一定的周期和算法触发vacuum动作,将过老的tuple的header中的事务ID进行冻结。冻结事务ID,即将事务ID设置为“2”(“0”表示无效事务ID;“1”表示bootstrap,即初始化;“3”表示最小的事务ID)。PG认为被冻结的事务ID比任何事务都要老。这样就不会出现上面的这种情况了。

    注意点:PG在9.4版本以前执行freeze时IO会很高,FROZEN是通过一个等于2的XID来表示的。从9.4开始改成了通过tuple 头部的t_infomask中的两个互斥的比特位来表示HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID。9.6对这块做了改进,当数据页中的所有记录已经是FROZEN状态时,在发起vacuum freeze时会跳过这个页的扫描,从而大幅提升静态数据的freeze操作,减少IO扫描。xid如果不超过21亿是正常比较,超过21亿的需要取模进行比较,如果行上的t_infomask中HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID同时存在就会被认为是FrozenTransactionId。

    Freeze异常
      ERROR:  found xmin 2088747257 from before relfrozenxid 2810153180
    原因分析:因为每次freeze之后,在pg_class中都会记录下最后一次的freeze的事务号,该报错信息显示发现一个比表的冻结年龄更早的数据记录。
    Freeze异常处理
        a.如果不是单表无法进行处理,可以查看pg_stat_activity中是否有正在执行尚未提交的长事务,如果存在可以选择继续等待事务执行完毕,或者杀死该事务select pg_terminate_backend(pid)
        b.relfrozenxid 是存储在pg_class,pg会把一些关键系统表的元数据信息存储在relcache中,rd_isnailed=true就是表明这个表的relcache的relcache是不会去进行更新的,关键的系统表一般也不会去进行表元数据的变更,所以系统表不去更新relcache也是正常的。所以问题的根因就是vacuum是读到的relfrozexid来自relcache的,更新catalog中的relfrozexid不会去刷新relcache,导致一直读到的是错误的relfrozexid,所以出现vacuum freeze报错的问题。10.2,9.6.7,9.5.11,9.4.16到修复版本之间的版本的pg实例都会存在相关问题。10.5, 9.6.10, 9.5.14, 9.4.19对这个问题进行了修复。
    修复方式:
        1.重启数据库,重启后会重新读入新数据内容到relcache中,相当于刷新relcache。
        2.删除$PGDATA/global/pg_internal.init,这个文件就是存储的relcache的内容,有新的连接连
    入会创建新的pg_internal.init文件。

    数据库异常

    ERROR:  database is not accepting commands to avoid wraparound data loss in database “mydb"

    HINT:  Stop the postmaster and vacuum that database in single-user mode.

    在👆的报错之前,我们可能会看到👇的警告

    WARNING:  database "mydb" must be vacuumed within 177009986 transactions

    HINT:  To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

    PG中规定:

    当旧的XID达到1000万时,发出警告,忽略警告后,系统将在距离重叠小于100万次的时候关闭,拒绝提供任何新的事务。这个100万的事务安全边界留下来用于让管理员在不丢失数据的情况下进行恢复,方法是手工执行所需要的VACUUM命令。不过,因为一旦进入了安全关闭模式,系统就不能再执行命令,做这件事情的唯一的方法是停止主服务器, 使用单用户模式来执行VACUUM。关闭模式不会强制于单用户模式。

    --更新统计信息

            vacuum analyze时,会更新统计信息,让PG的planner能够算出更准确的执行计划。autovacuum_analyze_threshold和autovacuum_analyze_scale_factor参数可以控制analyze的触发的频率。

    --更新visibility map

            在PG中,有一个visibility map用来标记那些page中是没有dead tuple的。这有两个好处,一是当vacuum进行scan时,直接可以跳过这些page。二是进行index-only scan时,可以先检查下visibility map。这样减少fetch tuple时的可见性判断,从而减少IO操作,提高性能。另外visibility map相对整个relation,还是小很多,可以cache到内存中。

    autovacuum

            尽管我们看到autovacuum已经被#注释掉,但是这个功能还是默认开启的,触发条件有两种

    第一种:表上(update,delte 记录) >= autovacuum_vacuum_scale_factor* reltuples(表上记录数) +autovacuum_vacuum_threshold;

    第二种:指定表上事务的最大年龄配置参数autovacuum_freeze_max_age,默认为2亿,达到这个阀值将触发 autovacuum进程,从而避免 wraparound。

    autovacuum查询执行记录

    select schemaname,relname,last_autovacuum,last_autoanalyze from pg_stat_all_tables;

    autovacuum无法启动异常

            正常情况下,通过ps -ef | grep auto可以看到autovacuum处在运行的过程中过,但是也有些情况无法正常启动,检查配置autovacuum=on处于开启状态,同时日志中会出现类似一下情况:

    11:14:15 CST,,0,LOG,00000,"could not resolve ""localhost"": Temporary failure in name resolution”,,,,,,,,,”"
    11:14:15 CST,,0,LOG,00000,"disabling statistics collector for lack of working socket”,,,,,,,,,”"
    11:14:15 CST,,0,WARNING,01000,"autovacuum not started because of misconfiguration",,"Enable the ""track_counts"" option.",,,,,,,""

            通过日志我们可以看到貌似是track_counts参数没有打开,show track_counts;显示该参数确实处于off状态,但是postgresql.conf显示该参数on,在往上可以看到"could not resolve“ ”localhost"";查询auto vacuum文档,发现该功能气功需要以下3个条件:

    (1)autovacuum = on

    (2)track_counts = on

    (3)/etc/hosts #能 ping通 localhost

    所以查看一下,/etc/hosts文件127.0.0.1是否存在或者被注释,修改使其生效重启数据库即可。

    autovacuum占用CPU过高问题处理

            如上图在autovacuum中可能会偶出现CPU变的异常高的情况,在其官方文档中有这么一段解释:

            Vacuuming (whether automatic or manual) stops at the oldest transaction id that is still in use. Otherwise it would be vacuuming active transactions, which is not sensible at all.

            简而言之,长事务会导致vacuum进程一直处于运行状态,并且会占据很高的CPU;所以在设置autovacuum时一定要注意选择规避可能出现长事务的时间,选择系统较为空闲的时间。                        

            autovacuum可以直接使用pg_terminate_backend() 的方式直接kill该进程,它会自动重启。

    展开全文
  • PostgreSQL 自动Vacuum配置

    千次阅读 2019-02-19 15:33:12
    PostgreSQL的Vacuum由于以下原因需要定期执行。 释放,再利用因更新或者删除更新而占用的磁盘空间。 更新PostgreSQL 查询计划用的统计数据。 避免事务ID的重置而引起非常老的数据丢失。 VACUUM 的标准SQL文的执行和...

    PostgreSQL的Vacuum由于以下原因需要定期执行。

    释放,再利用因更新或者删除更新而占用的磁盘空间。
    更新PostgreSQL 查询计划用的统计数据。

    避免事务ID的重置而引起非常老的数据丢失。

    VACUUM 的标准SQL文的执行和其他的对数据库的实际操作可以并行处理。 SELECT 、INSERT 、UPDATE 、DELETE 等命令和同通常一样继续能够执行。但是,VACUUM处理中的时候, ALTER TABLE ADD COLUMN等等的命令不能够对表进行重新定义。 还有,由于执行VACUUM 的时候,有大量的I/O操作,其他的操作可能性能比较低,比如查询的反应非常慢。为了较少对性能的影响,可以通过参数来调整。

    autovacuum (boolean ):
    数据库服务器是否设置为自动vacuum。默认为 自动vacuum。 但是如果要让vacuum能够正常运转,必须使 track_counts 有效。 track_counts这个参数在 postgresql.conf配置文件内,或者通过命令来设置。

    即使设置不是自动vacuum。系统发现有防止事务ID的重置的必要的时候也会自动启动

    log_autovacuum_min_duration (integer ):
    设置执行时间超过多长 的vacuum才输出log。时间单位毫秒。

    如果这个参数设置为0的话,所有vacuum相关 的log都输出。

    如果这个参数设置为-1的话,这个也是默认设置。log的输出无效,也就是所有vacuum相关 的log都不输出。

    这个参数的设置可以修改postgresql.conf配置文件,也可以通过命令来设置。

    autovacuum_max_workers (integer ):
    设置能够同时执行的vacuum最大进程数。 默认是3个。

    这个参数的设置可以修改postgresql.conf配置文件,也可以通过命令来设置。

    autovacuum_naptime (integer ):
    设置数据库执行vacuum的最小延迟。时间单位是分。默认是1分钟。
    这个参数的设置可以修改postgresql.conf配置文件,也可以通过命令来设置。

    autovacuum_vacuum_threshold (integer ):
    设置任何一张表内触发VACUUM 的 更新,删除tuple的最小数。默认为50。

    这个参数的设置可以修改postgresql.conf配置文件,也可以通过命令来设置。这个设定通过 pg_autovacuum的项目可以覆盖每张表。

    autovacuum_analyze_threshold (integer ):
    设置任何一张表内触发ANALYZE 的更新,删除tuple的最小数。默认为50。

    这个参数的设置可以修改postgresql.conf配置文件,也可以通过命令来设置。这个设定通过 pg_autovacuum的项目可以覆盖每张表。

    autovacuum_vacuum_scale_factor (floating point ):
    是否触发VACUUM的判断的时候 、设置 autovacuum_vacuum_threshold 追加的表容量断片。默认是0.2(即20%)。

    这个参数的设置可以修改postgresql.conf配置文件,也可以通过命令来设置。这个设定通过 pg_autovacuum的项目可以覆盖每张表。

    autovacuum_analyze_scale_factor (floating point ):
    是否触发 ANALYZE 的判断的时候 、设置 autovacuum_vacuum_threshold 追加的表容量断片。默认是0.1(即10%)。

    这个参数的设置可以修改postgresql.conf配置文件,也可以通过命令来设置。这个设定通过 pg_autovacuum的项目可以覆盖每张表。

    autovacuum_freeze_max_age (integer ):
    为了防止事务ID的重置,VACUUM强制操作前,设置表的 pg_class .relfrozenxid字段的最大值。默认是2亿。

    这个参数只能够在启动的时候设置。

    autovacuum_vacuum_cost_delay (integer ):
    设置自动vacuum操作中cost延迟。默认是20毫秒。设置的为-1的话,使用vacuum_cost_delay的值。

    这个参数的设置可以修改postgresql.conf配置文件,也可以通过命令来设置。这个设定通过 pg_autovacuum的项目可以覆盖每张表。

    autovacuum_vacuum_cost_limit (integer ):
    设置自动vacuum操作中cost的最大界限值。默认是-1,这时候用vacuum_cost_limit的值。 这个参数的设置可以修改postgresql.conf配置文件,也可以通过命令来设置。这个设定通过 pg_autovacuum的项目可以覆盖每张表。


    展开全文
  • PostgreSQL10基础(6)Analyze和Vacuum

    千次阅读 2020-03-02 15:37:37
    参考文档 https://www.postgresql.org/docs/10/sql-analyze.html ...https://www.postgresql.org/docs/10/sql-vacuum.html https://www.postgresql.org/docs/...

    参考文档

    • 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执行不够频繁,可以考虑定时每日清理+大量操作后清理,并通过延迟清理功能降低对生产系统性能影响

    展开全文
  • 以下来自于pg 10官方文档24章,有删改,完整版...官方文档对VACUUM 的定义是数据库垃圾回收及可选的数据库统计信息收集(VACUUM — garbage-collect and optionally analyze a database) 命令语法如下 VACUU...

    以下来自于pg 10官方文档24章,有删改,完整版参考:https://www.postgresql.org/docs/10/routine-vacuuming.html

    官方文档对VACUUM 的定义是数据库垃圾回收及可选的数据库统计信息收集(VACUUM — garbage-collect and optionally analyze a database)

    命令语法如下

    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 [, ...] ) ] ]

    VACUUM有两种:标准VACUUM和VACUUM FULL。

    • 标准VACUUM基本可以online操作(DML运行正常,但不能执行ALTER TABLE),但能回收的磁盘空间很少。
    • VACUUM FULL能回收更多的磁盘空间,但运行速度要慢得多;它需要对表加独占锁,因此不能与该表的其他操作并发进行;此外还需要额外空间存储表副本。

    标准VACUUM

    Fig. 6.8. An example showing the disadvantages of (concurrent) VACUUM.

    VACUUM FULL

    Fig. 6.9. Outline of Full VACUUM mode.

    因此,通常管理员应使用标准VACUUM,避免使用VACUUM FULL。VACUUM会产生大量IO,这可能会导致其他活动会话性能下降。有一些参数可以调整以减少vacuum对性能的影响,参考 https://www.postgresql.org/docs/10/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

     

    pg数据库需要定期维护,即所谓的清理。许多情况下,让autovacuum守护程序执行vacuum就足够了,一些DBA会希望用手动管理的VACUUM命令来补充或替换autovacuum(通常利用cron或Task Scheduler执行脚本)。为了正确设置手动管理的vacuum,必须理解接下来几个小节中讨论的问题。依靠自autovacuum的管理员也可阅读帮助理解和调整autovacuum。

     

    VACUUM命令必须定期维护每个表,原因(vacuum的作用)如下:

    • 释放或重用死元组占用的磁盘空间。
    • 更新统计信息。
    • 更新可见性映射,加快仅索引扫描。
    • 防止事务ID或multixact ID回卷造成数据丢失。

    每种作用都要求不同频率和范围的VACUUM操作,如以下小节所述。

     

    ① 释放磁盘空间

    在pg中,由于MVCC需要,UPDATEDELETE某行时不会立即删除该行的旧版本。但是当被删除的行版本已不再被任何事务需要时,必须回收它占用的空间以供新行重用,避免磁盘空间无限制地增长。

    标准VACUUM可清理表和索引中的死元组,并将其标记为可重用空间。但是,它不会将空间返回给操作系统(除非表末尾的一个或多个页面变得完全空闲,并且可以轻松获得表独占锁)。相反,VACUUM FULL通过写全新版本的表文件主动压缩表,可以最大程度地减少表的大小,但是会花费很长时间。在操作完成之前,需要对表加独占锁,并且表的新副本还额外需要约等于表大小的磁盘空间。

    通常选择经常做标准vacuum来避免需要做VACUUM FULL。autovacuum守护程序就尝试以这种方式工作,它永远不会发出VACUUM FULL。这种方法的思想不是将表保持在最小大小,而是保持磁盘空间的稳定使用——尽管VACUUM FULL可以用来将表缩到最小并将磁盘空间返回给操作系统,但是如果表将来会再次增长,则没有什么意义。因此,对于大量更新的表,中度频繁地运行标准VACUUM是比不频繁地运行VACUUM FULL更好的方法。

    一些管理员更喜欢自己安排vacuum作业,例如在负载低的晚上进行所有工作。根据固定的时间进行清理的困难在于,如果表的更新意外大量增加,它可能会膨胀到真正有必要使用VACUUM FULL回收空间的程度。使用autovacuum可以缓解此问题,因为它会根据更新情况动态进行清理。除非您的工作负载非常可预测,否则完全禁用autovacuum是不明智的。一种折衷的办法是设置autovacuum的参数,使其仅对异常繁重的更新活动做出反应,避免事情失控,而在通常负载下,则使用计划的VACUUM完成大部分工作。

     

    ② 更新统计信息

    pg执行计划生成器依赖于有关表内容的统计信息。统计信息由ANALYZE命令收集,该命令可以单独调用,也可以作为VACUUM中的可选步骤。拥有合理准确的统计信息很重要,执行计划选择不当可能降低数据库性能。

    如果启用了autovacuum,当更改的表元组数达到阈值时,将自动发出ANALYZE命令。但是,管理员可能更喜欢依靠手动计划的ANALYZE操作,尤其是在已知表上的更新活动不会影响“ 感兴趣 ”列统计信息的情况下。autovacuum严格根据更改的元组数进行ANALYZE调度,而会不管统计信息的收集对业务是否有意义。

    即使对于大量更新的表,如果数据的统计分布变化不大,也可能不需要更新统计信息。一个简单的经验法则是考虑表中列的最小值和最大值有多少变化。可以仅在特定表甚至仅在表的特定列上运行ANALYZE,因此,可以按需更频繁地进行更新某些统计信息。但是实际上通常最好只分析整个数据库,因为这是一个快速的操作。ANALYZE对表进行随机抽样,而不是读取每一行。

    Tip

    autovacuum不会对外部表发出ANALYZE命令,因为它无法确定可能有用的频率。如果您的查询需要有关外部表的统计信息,最好手动或定期运行ANALYZE命令。

     

    ③ 更新可见性映射

    Vacuum为每个表维护一份可见性映射,以跟踪哪些页面仅包含对所有活动事务及未来事务可见的元组。

    这有两个目的:

    • vacuum可以在下一次运行时跳过此类页面,因为没有什么需要清理的。
    • 允许pg仅使用索引进行查询,而无需引用基础表(不用回表)。

    由于pg索引不包含元组可见性信息,普通索引扫描需要对每个匹配的元组回表检查它们对当前事务是否可见。而仅索引扫描会首先检查可视性映射,如果知道页面上的所有元组都是可见的,则可以跳过回表,减少大量磁盘访问。另外可见性映射远小于堆表,因此即使堆表很大,也可以轻松将其缓存。

     

    ④ 防止事务ID回卷失败

    参考  https://blog.csdn.net/Hehuyi_In/article/details/102869893

     

    ⑤ autovacuum守护程序

    pg中有一个可选的但强烈推荐的功能,称为autovacuum,即自动执行VACUUM and ANALYZE。启用后,自动清理将检查具有大量dml操作的表。这些检查使用统计信息收集工具,因此必须将track_counts设置为true,才能使用autovacuum。默认已启用autovacuum,并已正确设置了相关参数。

    autovacuum守护程序实际上是多个进程——一个autovacuum launcher与多个autovacuum worker进程。

    持久性守护进程autovacuum launcher(autovacuum启动器),负责启动所有数据库的autovacuum worker进程。

    • 启动器进程定时工作,尝试在autovacuum_naptime秒内在每个数据库中都启动一个worker进程(如果有n个数据库,则每autovacuum_naptime/n秒会启动一个新的worker进程)。
    • 最多autovacuum_max_workers个worker进程被允许在同一时间运行。如果db数超过autovacuum_max_workers,第一个worker进程完成工作后将立即处理下一个db。
    • 每个worker进程将检查其数据库中的每个表,并根据需要执行VACUUM和/或ANALYZE操作。可以设置log_autovacuum_min_duration监视autovacuum worker的活动。如果几个大表在短时间内都符合vacuum的条件,可能所有的autovacuum worker都会被这些大表的vacuum操作长期占用,这将导致其他表和数据库在worker可用之前不会被清理。
    • 单个数据库中可以有多少worker没有限制,但是worker会尝试避免重复其他worker已经完成的工作。
    • 正在运行的worker数不计入max_connections或superuser_reserved_connections限制。
    • relfrozenxid值超过autovacuum_freeze_max_age的表一定会被vacuum(也适用于已经通过存储参数修改最大冻结年龄的表,见下文)。
    • 如果自上次VACUUM后过期的元组数量超过了vacuum阈值,表也会被vacuum。

     

    vacuum阈值被定义为:

    vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

    其中vacuum基本阈值为autovacuum_vacuum_threshold,vacuum比例因子为autovacuum_vacuum_scale_factor,元组数为pg_class.reltuples。过期的元组数从统计信息收集器中获得,它是在每次UPDATE和DELETE操作时会被更新的半精确计数(只是半精确,因为某些信息可能会在高负载下丢失)。如果表的relfrozenxid值大于vacuum_freeze_table_age,将执行vacuum操作以冻结旧元组并使relfrozenxid前进;否则,仅扫描自上次vacuum以来被修改过的页面。

     

    分析阈值定义为:

    analyze threshold = analyze base threshold + analyze scale factor * number of tuples

    它会与自上次ANALYZE之后的更改的总元组数进行比较。

    默认阈值和比例因子取自postgresql.conf,也可以针对每个表设置,以覆盖全局参数。

    当有多个worker在运行时,autovacuum成本延迟参数(请参见第19.4.4)在所有正在运行的worker之间是平衡的 。因此,无论实际运行的worker数量如何,对系统的总IO影响都是相同的。但已覆盖全局参数设置了autovacuum_vacuum_cost_delayautovacuum_vacuum_cost_limit参数的表不在平衡算法考虑之中。

    另外注意临时表不能通过autovacuum访问,需要SQL执行适当的vacuum和analyze操作。

     

    参考

    https://www.postgresql.org/docs/10/routine-vacuuming.html

    https://www.postgresql.org/docs/10/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

    https://www.postgresql.org/docs/10/storage-vm.html

    https://blog.csdn.net/Hehuyi_In/article/details/102925756

    https://blog.csdn.net/Hehuyi_In/article/details/102926799

    http://www.interdb.jp/pg/pgsql06.html

    展开全文
  • 为什么PostgreSQL数据库管理工作中,定期vacuum是一个重要的工作. 原因在于以下3点: 释放,再利用 更新/删除的行所占据的磁盘空间. 更新PostgreSQL查询计划中使用的统计数据. 防止因事务ID的重置而使非常老的数据丢失....
  • 弄清楚POSTGRESQL 的VACUUM对于维护好POSTGRESQL 和 理解一些在基于POSTGRESQL 设计中的"点" 是有必要性的. 虽然数据库是有包容性的...
  • 问题是这样的,回答一个关于vacuum操作的问题的时候,由于学艺不精,知识不扎实,选择了错误的答案,有幸于马上有人指出错误。才不至于将错误的理解延续,所以的写一篇来将错误的理解纠正,并加...
  • # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of ...
  • postgresql vacuum 垃圾整理

    千次阅读 2009-12-01 10:44:22
        简单的说,这个命令就是垃圾整理,另外一个是vacuumdb     具体中文说明 ...VACUUMName VACUUM -- 垃圾收集以及可选地分析一个数据库 Synopsis VACUUM [ FULL | FREEZE ] [ VERBOS...
  • PostgreSQL , track_counts , 统计信息 , pg_stat_reset 背景 PostgreSQL数据库的statstic模块有一些计数器,用于统计每个表被插入、更新、删除的记录数。 通过这些视图,可以查看计数器统计到的一些计数: ...
  • — Postgres is able to track and adjust this data structure without user intervention. A few critical features of the new FSM are: * Now a binary tree structure * Constructed using 1 byte per ...
  • Dataset之ImageNet:ImageNet数据集简介、下载、使用方法之详细攻略 目录 lmageNet 数据集简介 1、ImageNet数据集的意义 2、ImageNet的数据结构——层次结构及其1000个类别 ...lmag...
  • 本节简单介绍了PostgreSQL手工执行vacuum的处理流程,主要分析了ExecVacuum->vacuum->vacuum_...
  • how many dml tuples will trigger auto vacuum or analyze
  •  They had walked from the Nightfort to Deep Lake, and from Deep Lake to Queensgate, following a narrow track from one castle to the next, never out of sight of the Wall. A day and a half from Castle ...
  • 相关参数:track_activities、track_counts、track_functions、track_io_timing。 27.2.2 查看统计信息 表27.1列出了查看数据库系统的当前状态的视图;表27.2列出了查看统计信息收集结果的视图。 27.2.3. pg_stat_...
  • #------------------------------------------------------------------------------ ...#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for autovacuum, -1 means use vacuum_cost_limit
  • 日常数据库维护工作定期备份,定期”清理“数据库,周期性的日志文件管理check_postgres可用于检测数据库的...PostgreSQL的VACUUM命令出于几个原因必须定期处理每一个表:1. 恢复或重用被已更新或已删除行所占用的磁...
  •  # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and  # their durations, > 0 logs only  # actions running at least this number ...
  • where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is au- tovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples. The number of obsolete ...
  •  78064 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.07 s 仅track_io_timing启动时,才能显示这样的日志。 连接日志 log_connections/log_disconnections开启时,会记录connection/...
  • 同许多其他数据库一样,PostgreSQL也需要定期执行一些任务以维护最佳运行。 主要需要定期进行的任务: ...也可以使用VACUUM命令进行人工管理,然后使用cron等工具定期执行。 24.1.1vacuum基础 基于以下原因,...
  • Postgresql - Vacuuming

    2018-09-30 11:06:45
    vacuum是PG中很重要的知识点,值得好好学习,自己写的没有官方文档的详细,最后还是选择翻译。   以下内容翻译自官方文档 https://www.postgresql.org/docs/11/static/routine-vacuuming.html   ************...
  • 19.10. Automatic Vacuuming

    2021-02-23 20:57:52
    19.10 自动vacuum 以下参数控制autovacuum特性。更多信息请参加第24.1.6节。请注意,以下参数大部分都可以在表级别单独定义,请参见 CREATE TABLE的存储参数部分。 autovacuum(boolean) 是否运行autovacuum守护...
  • 19.6. Replication

    2021-02-23 15:48:26
    vacuum_defer_cleanup_age(integer) 指定VACUUM和HOT更新将延迟清除已删除行版本的事务的数量。默认为0,即立马清理。 19.6.3 备节点 以下设置控制备节点行为。 primary_conninfo(string) 连接主节点的连接信息。...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 659
精华内容 263
关键字:

trackvacuum