postgresql中的大量活动/死元组/ Vacuum不起作用

25-02-28 12

如果您想了解postgresql中的大量活动/死元组/Vacuum不起作用的相关知识，那么本文是一篇不可错过的文章，我们将为您提供关于2024-01-18-【数据库】PostgreSQL中的VACUU

如果您想了解postgresql中的大量活动/死元组/ Vacuum不起作用的相关知识，那么本文是一篇不可错过的文章，我们将为您提供关于2024-01-18-【数据库】 PostgreSQL中的VACUUM作用、postgres--vacuum、postgresql / vacuum中大量活动/死元组不起作用、PostgreSQL 13 配置参数vacuum_cost_delay的有价值的信息。

本文目录一览：

postgresql中的大量活动/死元组/ Vacuum不起作用
2024-01-18-【数据库】 PostgreSQL中的VACUUM作用
postgres--vacuum
postgresql / vacuum中大量活动/死元组不起作用
PostgreSQL 13 配置参数vacuum_cost_delay

postgresql中的大量活动/死元组/ Vacuum不起作用

有一个表，其中有200行。但是显示的活动元组的数量不止于此（约60K）。

select count(*) from subscriber_offset_manager; count -------   200(1 row) SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname=''subscriber_offset_manager'' ORDER BY n_dead_tup; schemaname |          relname          | n_live_tup | n_dead_tup ------------+---------------------------+------------+------------ public     | subscriber_offset_manager |      61453 |          5(1 row)

但是从pg_stat_activity和pg_locks可以看出，我们无法跟踪任何打开的连接。

SELECT query, state,locktype,modeFROM pg_locksJOIN pg_stat_activity  USING (pid)WHERE relation::regsubscriber_offset_manager''::regclass  ; query | state | locktype | mode -------+-------+----------+------(0 rows)

我也在这张桌子上尝试了全真空，结果如下：

一直没有删除任何行
有时所有的活动元组变成死元组。

这是输出。

vacuum FULL VERBOSE ANALYZE subscriber_offset_manager;INFO:  vacuuming "public.subscriber_offset_manager"INFO:  "subscriber_offset_manager": found 0 removable, 67920 nonremovable row versions in 714 pagesDETAIL:  67720 dead row versions cannot be removed yet.CPU 0.01s/0.06u sec elapsed 0.13 sec.INFO:  analyzing "public.subscriber_offset_manager"INFO:  "subscriber_offset_manager": scanned 710 of 710 pages, containing 200 live rows and 67720 dead rows; 200 rows in sample, 200 estimated total rowsVACUUM SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname=''subscriber_offset_manager'' ORDER BY n_dead_tup; schemaname |          relname          | n_live_tup | n_dead_tup ------------+---------------------------+------------+------------ public     | subscriber_offset_manager |        200 |      67749

10秒后

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname=''subscriber_offset_manager'' ORDER BY n_dead_tup; schemaname |          relname          | n_live_tup | n_dead_tup ------------+---------------------------+------------+------------ public     | subscriber_offset_manager |      68325 |        132

我们的应用程序如何查询此表。

我们的应用程序通常选择一些行，并根据一些业务计算来更新该行。

选择查询 -根据一些ID选择

选择* from subscription_offset_manager其中shard_id = 1;

更新查询 -为此选定的分片ID更新其他一些列

大约有20个线程并行执行此操作，而一个线程仅在一行上工作。
应用程序是用Java编写的，我们正在使用hibernate进行数据库操作。
PostgreSQL版本是9.3.24

另一个有趣的发现：
-当我停止我的Java应用程序，然后完全吸尘时，它工作正常（行数和活动元组变为相等）。因此，如果我们从java应用程序中连续选择并更新，则会出问题。–

问题/问题

这些活动元组有时会变成死元组，过了一段时间又重新变得活着。

由于上述行为，请从表中进行选择，这会花费一些时间并增加服务器的负载，因为那里有很多实时/重复数据。

答案1

小编典典

我知道VACUUM无法完成工作的三件事：

长期交易。
未提交的准备好的事务。
过时的复制插槽。

有关详细信息，请参见我的博客文章。

2024-01-18-【数据库】 PostgreSQL中的VACUUM作用

VACUUM命令，它可以有效地清理和整理数据库中的数据，提高查询性能和存储效率。

VACUUM的作用

回收空闲空间：VACUUM可以回收已经释放的空闲空间，使得这些空间可以被重新使用，从而提高数据库的存储效率。
压缩数据：VACUUM可以对数据库中的数据进行压缩，减少数据的占用空间，进一步提高存储效率。
更新统计信息：VACUUM还可以更新数据库的统计信息，帮助优化器更好地选择执行计划，提高查询性能。
维护数据完整性：VACUUM可以确保数据库中的数据完整性，避免因为删除或更新数据而导致的数据不一致问题。

VACUUM的优点

VACUUM在PostgreSQL中具有以下优点：

自动回收空间：VACUUM可以自动回收已经释放的空闲空间，无需手动干预，减少了数据库管理员的工作负担。
提高查询性能：通过回收空闲空间和压缩数据，VACUUM可以提高查询性能，使得数据库能够更快地响应用户请求。
减少存储成本：VACUUM可以提高数据库的存储效率，减少存储空间的使用，从而降低企业的存储成本。
保持数据一致性：VACUUM可以确保数据库中的数据完整性，避免因为删除或更新数据而导致的数据不一致问题，保证数据的可靠性。
支持多种工作负载：VACUUM适用于各种工作负载，无论是OLTP还是OLAP，都可以从VACUUM中受益。
灵活的配置选项：VACUUM提供了丰富的配置选项，可以根据不同的需求进行定制，满足不同场景下的性能要求。
与PostgreSQL集成：VACUUM是PostgreSQL的一部分，与其他PostgreSQL功能紧密集成，可以充分利用PostgreSQL的强大功能。

VACUUM的工作方式

VACUUM的工作方式可以分为两种：自动VACUUM和手动VACUUM。

自动VACUUM：PostgreSQL会自动运行VACUUM来回收空闲空间和压缩数据。默认情况下，PostgreSQL会每隔一段时间（由autovacuum_vacuum_scale_factor参数控制）自动运行一次VACUUM。此外，当数据库中的数据发生变化时，PostgreSQL也会触发自动VACUUM。
手动VACUUM：除了自动VACUUM外，用户还可以手动运行VACUUM来回收空闲空间和压缩数据。手动VACUUM的命令格式为：VACUUM [ ( option [, ... ] ) ] table [, ... ]。其中，option可以是以下选项之一：

VERBOSE：显示详细的运行信息。
FREEZE n：冻结n个事务之前的数据。这可以确保在执行VACUUM时不会修改这些事务中的数据。
SKIP TOUCHED：跳过已经被其他进程修改过的数据。这可以提高VACUUM的效率。
REINDEX：在回收空间后重新创建索引。这可以提高索引的性能。
DISABLE_WAL：禁用WAL日志记录。这可以减少I/O操作，提高VACUUM的速度。但是，禁用WAL日志记录可能会导致数据丢失，因此需要谨慎使用。
ANALYZE：更新统计信息。这可以帮助优化器更好地选择执行计划，提高查询性能。
VERIFY：验证数据完整性。这可以确保数据库中的数据没有被损坏。
QUICK：快速模式，只回收最少的空间。这可以提高VACUUM的速度，但是可能会回收较少的空间。
EXTENDED：扩展模式，提供更详细的输出信息。这可以帮助用户了解VACUUM的运行情况。
ALL：同时启用所有选项。这可以提供最全面的输出信息和最高的性能。

VACUUM的应用场景

VACUUM适用于各种应用场景，以下是一些常见的应用场景：

定期清理：对于经常写入数据的数据库，建议定期运行VACUUM来回收空闲空间和压缩数据，以提高查询性能和存储效率。
数据迁移：在进行数据迁移时，可以先运行VACUUM来回收空闲空间和压缩数据，以减少迁移过程中的I/O操作和存储成本。
数据库扩容：在扩容数据库时，可以先运行VACUUM来回收空闲空间和压缩数据，以充分利用新增的存储空间。
系统维护：在进行系统维护时，可以先运行VACUUM来回收空闲空间和压缩数据，以减少维护过程中的I/O操作和存储成本。
性能调优：在对数据库进行性能调优时，可以通过运行VACUUM来回收空闲空间和压缩数据，以提高查询性能和存储效率。

mudouzuo1

postgres--vacuum

vacuum的功能

回收空间

数据库总是不断地在执行删除，更新等操作。良好的空间管理非常重要，能够对性能带来大幅提高。

postgresql中执行delete操作后，表中的记录只是被标示为删除状态，并没有释放空间，在以后的update或insert操作中该部分的空间是不能够被重用的。

在postgresql中用于维护数据库磁盘空间的工具是VACUUM，其重要的作用是删除那些已经标示为删除的数据并释放空间。经过vacuum清理后，空间才能得到释放。

VACUUM回收已删除元组占据的存储空间。在一般的PostgreSQL操作里，那些已经DELETE的元组或者被UPDATE过后过时的元组是没有从它们所属的表中物理删除的；在完成VACUUM之前它们仍然存在。因此我们有必须周期地运行VACUUM，特别是在常更新的表上。

冻结tuple的xid

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比任何事务都要老。这样就不会出现上面的这种情况了。

更新统计信息

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

更新visibility map

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

执行语法

sql语句

VACUUM语法结构

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

系统命令

vacuumdb --help

Full Vacuum

full vacuum与单纯的vacuum的区别

vacuum只是将删除状态的空间释放掉，转换到能够重新使用的状态，但是对于系统来说该数据块的空闲空间并没有反应到系统的元数据中，并不进行空间合并。
vacuum full除了vacuum，还进行空间合并，会锁表和索引，而且是“AccessExclusiveLock”级别的，其实vacuum full会重建整个表。
vacuum full将会使空间释放的信息表现在系统级别，其实质是将当前删除记录后面的数据进行移动，使得整体的记录连贯起来。

vacuum full的执行流程

建临时表

PG会新建一个临时表，临时表继承老表所有属性。
这个阶段会对pg_class申请“RowExclusiveLock”锁，因为需要插入条目。

拷贝数据

将原来的数据copy到temp表中。
对临时表，老表以及索引都以“AccessExclusiveLock”模式打开。
另外对于toast，只是lock，不打开。
在这个过程中完成Dead Tuple的清理。

表交换

新表将老表替换掉。会对pg_class再次申请“RowExclusiveLock”锁。

重建索引

是在交换之后完成的，重建索引时，会更新一些统计信息。对表申请“ShareLock”锁。

删除临时表

索引重建完成后，将带有老物理文件的新临时表进行删除。

注意

执行过程中锁表，大部分时间无法访问，对业务影响很大
执行过程消耗时间长，消耗大量IO、内存、CPU等
对于大表和业务繁忙时间最后不要执行
一般合理设置vacuum参数，进行常规vacuum也就够了。

Vacuum analyze

analyze的功能是更新统计信息，使得优化器能够选择更好的方案执行sql。
统计信息收集和更新对于系统性能来说非常重要，与oracle维护类似，通常可以通过采用手动或者定制任务的方式。也有不同，oracle在进行imp后自动的对相应数据对象进行统计信息的收集和更新，而postgresql的恢复过程还没有集成到里面，需要手动去执行。

autovacuum

autovacuum定时自动进行vacuum。

对于有大量update的表，vacuum full是没有必要的，因为它的空间还会再次增长，所以vacuum就足够了。

参数

autovacuum：默认为on，表示是否开起autovacuum。
    默认开起。特别的，当需要冻结xid时，尽管此值为off，PG也会进行vacuum。

autovacuum_naptime：检查执行vacuum的间隔，默认1min。 
    该值会被vacuum launcher分配到每个DB上。autovacuum_naptime/num of db。

log_autovacuum_min_duration：当vacuum动作执行超时，记录日志。
    -1关闭vacuum的日志记录，0表示所有log。正整数表示超时时间。

autovacuum_max_workers：最大同时运行的worker数，不包含launcher本身。
    默认值为3。配置主要依据系统当前负载和资源。
    对于系统负载较重的情况，建议开启少量的进程为好，反之，空闲时间可以采用较大值的方式。

postgresql / vacuum中大量活动/死元组不起作用

有一个表，其中有200行。但是显示的活动元组的数量不止于此（约60K）。

select count(*) from subscriber_offset_manager; count -------   200(1 row) SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname=''subscriber_offset_manager'' ORDER BY n_dead_tup; schemaname |          relname          | n_live_tup | n_dead_tup ------------+---------------------------+------------+------------ public     | subscriber_offset_manager |      61453 |          5(1 row)

但是从pg_stat_activity和pg_locks可以看出，我们无法跟踪任何打开的连接。

SELECT query, state,locktype,modeFROM pg_locksJOIN pg_stat_activity  USING (pid)WHERE relation::regsubscriber_offset_manager''::regclass  ; query | state | locktype | mode -------+-------+----------+------(0 rows)

我也在这张桌子上尝试了全真空，结果如下：

一直没有删除任何行
有时，所有的活动元组都变成死元组。

这是输出。

vacuum FULL VERBOSE ANALYZE subscriber_offset_manager;INFO:  vacuuming "public.subscriber_offset_manager"INFO:  "subscriber_offset_manager": found 0 removable, 67920 nonremovable row versions in 714 pagesDETAIL:  67720 dead row versions cannot be removed yet.CPU 0.01s/0.06u sec elapsed 0.13 sec.INFO:  analyzing "public.subscriber_offset_manager"INFO:  "subscriber_offset_manager": scanned 710 of 710 pages, containing 200 live rows and 67720 dead rows; 200 rows in sample, 200 estimated total rowsVACUUM SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname=''subscriber_offset_manager'' ORDER BY n_dead_tup; schemaname |          relname          | n_live_tup | n_dead_tup ------------+---------------------------+------------+------------ public     | subscriber_offset_manager |        200 |      67749

10秒后

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname=''subscriber_offset_manager'' ORDER BY n_dead_tup; schemaname |          relname          | n_live_tup | n_dead_tup ------------+---------------------------+------------+------------ public     | subscriber_offset_manager |      68325 |        132

我们的应用程序如何查询此表。

我们的应用程序通常选择一些行，并根据一些业务计算来更新该行。

选择查询 -根据一些ID选择

选择* from subscription_offset_manager其中shard_id = 1;

更新查询 -为此选定的分片ID更新其他一些列

大约20个线程并行执行此操作，而一个线程仅在一行上工作。
应用程序是用Java编写的，我们正在使用hibernate进行数据库操作。
PostgreSQL版本是9.3.24

另一个有趣的观察：
-当我停止我的Java应用程序然后完全吸尘时，它可以正常工作（行数和活动元组变为相等）。因此，如果我们从Java应用程序中连续选择并更新，则会出问题。–

问题/问题

这些活动元组有时会变成死元组，过了一段时间又复活了。

由于上述行为，请从表中进行选择，因为要花费大量时间并增加服务器的负载，因为那里有很多实时/重复数据。

答案1

小编典典

我知道VACUUM无法完成工作的三件事：

长期交易。
未提交的准备好的事务。
过时的复制插槽。

有关详细信息，请参见我的博客文章。

PostgreSQL 13 配置参数vacuum_cost_delay

参数名称	vacuum_cost_delay
数据类型	Real
默认值	0，零代表禁用了基于成本的vacuum delay特性。
取值范围	0到100
参数单位	毫秒
参数含义	vacuum_cost_limit参数值是一个累积vacuum io操作的成本值，当vacuum操作达到vacuum_cost_limit参数值之后，vacuum操作会停下来休眠一段时间，该段时间是vacuum_cost_delay参数值指定的，然后，PostgreSQL会重置计数器并继续vacuum操作。本参数的参数值建议尽量小，大值是没有帮助的。
是否可session级修改	是
修改后何时生效	session修改会立即生效；非session级修改reload即可生效。

关于postgresql中的大量活动/死元组/ Vacuum不起作用的问题就给大家分享到这里，感谢你花时间阅读本站内容，更多关于2024-01-18-【数据库】 PostgreSQL中的VACUUM作用、postgres--vacuum、postgresql / vacuum中大量活动/死元组不起作用、PostgreSQL 13 配置参数vacuum_cost_delay等相关知识的信息别忘了在本站进行查找喔。

本文标签：