postgresql 刷新实体化视图突然需要更多时间才能完成

41ik7eoe  于 2022-12-12  发布在  PostgreSQL
关注(0)|答案(3)|浏览(590)

我们在Postgres DB(11.12,由AWS RDS管理)中有一个物化视图。我们有一个使用REFRESH MATERIALIZED VIEW <view_name>每5分钟更新一次的计划任务。在上周的某个特定时间点,刷新视图所需的时间突然从约1秒增加到约20秒。该视图包含约70 k行,约15列,所有列都是整数、布尔值或UUID。
在此之前:

  • 服务器配置没有更改。
  • 视图本身没有任何变化。实际上,运行EXPLAIN ANALYZE <expression used to create the view>返回的查询仍然在不到一秒的时间内执行。如果使用Postico这样的客户端运行查询,则需要大约20秒的时间来获取所有结果(与具体化所需的时间有点一致,尽管我们假设这是由于网络传输所需的时间)。
  • 架构中没有任何更改,计算视图所需的表的内容中也没有任何显著的记录增加。
  • RDS Performance Insights指示查询主要使用CPU资源

我知道这可能不足以得到一个解决方案,但是:

  • 是否有任何服务器性能指标或日志可以帮助我们更好地了解这种情况?
  • 这只是服务器将视图持久化到磁盘所需的正常时间吗?如果是,是否知道最近开始花费这么长时间的可能原因?

Here是指向执行计划的链接。
EDIT:使用相同的JOINS创建另一个实体化视图,但选择的列数较少,性能与预期一致(~ 1 s)。
编辑2:设置enable_nestloop = false极大地提高了REFRESH操作的速度(与以前的性能相同)。这是否表明重构底层查询可以解决这个问题?

wwodge7n

wwodge7n1#

每次更新materialized view(或每5分钟)这不是一个很好的刷新物化的方式,那么使用materialized view的意义就不存在了,我给大家解释一下我根据自己的经验用自己的逻辑找到的一种方式,这样大家以后可以找到一个更优的方式,假设我们在我们的materialized view中使用了两个表,在更新或删除表时,我们必须向表(例如refresh_materialized表)中插入一条记录(也可以使用触发器),通过该记录将执行刷新materialized view
例如:

insert into refresh_materialized 
(
    refresh_status, 
    insert_date, 
    executed_date 
)
values (
    false,
    now(), 
    null 
)

因此,在我们的调度任务中,我们可以使用以下查询:

select count(*) from refresh_materialized 
where refresh_status = false

如果count(*) will be > 0,则必须刷新materialized view,否则不执行任何操作。刷新实体化视图后,必须更新此表:

update refresh_materialized 
set 
    refresh_status = true, 
    executed_date = now() 
where 
    refresh_status = false;
yhxst69z

yhxst69z2#

请尝试并发刷新实体化视图。刷新实体化视图的数据时,PostgreSQL将锁定整个表,因此无法查询该表的数据。要避免此问题,可以使用CONCURRENTLY选项。

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

使用CONCURRENTLY选项,PostgreSQL创建示例化视图的临时更新版本,比较两个版本,并仅对差异执行INSERT和UPDATE。
您可以在更新具体化视观表时查询具体化视观表。使用CONCURRENTLY选项的其中一个需求是具体化视观表必须有UNIQUE索引。

2jcobegt

2jcobegt3#

这里是原始海报。这是一年多的历史,但这里发生了什么,我们最终如何修复它。
TLDR:

  • REFRESH MATERIALIZED VIEW <query>开始比执行用于构造视图的查询花费更长的时间(~ 1秒对~ 20秒)。
    几个星期后,这个问题被问到了,查询本身的行为开始类似(需要大约20秒才能完成)。此时,EXPLAIN ANALYZE开始显示查询的性能问题。因此,我们最终优化了底层查询(最大的性能增益是用CTE替换一些JOINS)。
    在此之后,REFRESH MATERIALIZED VIEW <query>和独立查询的性能表现正常(执行时间〈1秒)。
    这里仍然存在一个未解决的问题:为什么REFRESH MATERIALIZED VIEW <query>和独立的query在某个时间点具有不同的性能?DB查询计划器是否根据是否要物化视图来选择不同的查询计划?我想如果有人知道这种情况是否可能,请发表评论。

相关问题