PostgreSQL中的长时间更新

Question

我一直在对一个包含250万行和3个索引的表进行UPDATE操作，这个UPDATE操作使用了另一个包含3000万行的表。目前已经运行了大约36小时。我想知道是否有方法可以找出它完成的进度，如果它计划花费100万天来完成它的任务，那么我会终止它；但是如果只需要再过一两天，我将让它继续运行。以下是命令查询：

UPDATE pagelinks SET pl_to = page_id
    FROM page
    WHERE 
        (pl_namespace, pl_title) = (page_namespace, page_title)
        AND
        page_is_redirect = 0
;

这里并不是问题在于EXPLAIN，我只提到大表有多个索引，以此来解释它更新所需的时间。但无论如何，这是EXPLAIN结果:

Merge Join  (cost=127710692.21..135714045.43 rows=452882848 width=57)
  Merge Cond: (("outer".page_namespace = "inner".pl_namespace) AND ("outer"."?column4?" = "inner"."?column5?"))
  ->  Sort  (cost=3193335.39..3219544.38 rows=10483593 width=41)
        Sort Key: page.page_namespace, (page.page_title)::text
        ->  Seq Scan on page  (cost=0.00..439678.01 rows=10483593 width=41)
              Filter: (page_is_redirect = 0::numeric)
  ->  Sort  (cost=124517356.82..125285665.74 rows=307323566 width=46)
        Sort Key: pagelinks.pl_namespace, (pagelinks.pl_title)::text"
        ->  Seq Scan on pagelinks  (cost=0.00..6169460.66 rows=307323566 width=46)

现在我还发送了一个并行查询命令，以便删除其中一个 "pagelinks" 的索引；当然，它正在等待 UPDATE 完成（但我还是想试一试！）。因此，我不能从 "pagelinks" 中选择任何内容，以免破坏数据（除非您认为杀死 DROP INDEX postmaster 进程是安全的？）。

因此，我想知道是否有一个表可以跟踪死元组的数量或其他内容，这样就可以了解 UPDATE 任务完成的速度和进度。

谢谢 （PostgreSQL 不像我想象的那么智能，它需要启发式算法）

- Nicholas Leonard

---

这并不能立即帮助您，但是有人正在为PostgreSQL开发“进度条”。听起来是个好主意。http://wiki.postgresql.org/wiki/Query_progress_indication - A. Rex

它确实如此！我们了解得越多，就能理解得越多。谢谢你提醒。 - Nicholas Leonard

你是否验证了你的UPDATE操作没有等待某些行锁被授予？下次检查pg_locks。 - user330315

Answer 1

您是否阅读了PostgreSQL文档中关于“使用EXPLAIN”的部分，以解释您展示的输出结果？

我不是一个经常使用PostgreSQL的用户，但我刚刚阅读了该文档，并将其与您展示的EXPLAIN输出进行了比较。您的UPDATE查询似乎没有使用索引，并且被迫对page和pagelinks进行表扫描排序。排序无疑足够大，需要临时磁盘文件，我认为这些文件是在您的temp_tablespace下创建的。

然后我看到了预计的数据库页面读取次数。该EXPLAIN输出的顶级部分显示为(cost=127710692.21..135714045.43)。这里的单位是磁盘I/O访问次数。因此，它将访问磁盘超过1.35亿次来执行此UPDATE操作。

请注意，即使是寻道时间为5毫秒的10,000rpm磁盘，在最佳条件下也只能实现每秒200次I/O操作。这意味着即使在此期间可以持续进行饱和的磁盘I/O操作（即连续读/写而没有中断），您的UPDATE也需要188小时（7.8天）的磁盘I/O时间。这是不可能的，我预计实际吞吐量至少相差一个数量级，特别是因为您无疑已经在此期间使用此服务器进行了各种其他工作。因此，我认为您只完成了UPDATE的一小部分。

如果是我，我会在第一天就停止此查询，并找到另一种执行UPDATE的方法，该方法更好地利用索引并且不需要在磁盘上进行排序。您可能无法在单个SQL语句中完成它。

至于您的DROP INDEX，我猜测它只是阻塞了，正在等待对表的独占访问权限，在此状态下，您可以将其终止。

- Bill Karwin

---

Postgres的成本估算是基于它需要访问的内存页面数量。我认为每个页面是1/2 KB。 - Barry Brown

我阅读的文档中写道：“传统做法是以磁盘页面获取单位来衡量成本。” - Bill Karwin

我并不是说你错了，只是我的回答基于文档所说的。 :-) - Bill Karwin

我认为我们两个都是对的，因为在典型的Unix系统中，磁盘块和内存页的大小相同。但是操作系统将能够一次获取多个块。 - Barry Brown

我确实在第二天结束时kill掉了它！我已经等待这张表上的VACUUM FULL进行了最后2天了！感谢有关EXPLAIN的知识；我知道它是一个时间度量，但现在我知道它是磁盘IO。 - Nicholas Leonard

不，这只是需要查看的页面数量的估计，无论它们是否在内存中。 - Barry Brown

Answer 2

这篇文章非常老了，但是如果你想要一种监控更新的方式……请记住，序列会对整个系统造成影响，所以你只需要创建一个序列，在另一个会话中来监控这个更新即可，具体操作如下：

create sequence yourprogress; 

UPDATE pagelinks SET pl_to = page_id
    FROM page
    WHERE 
        (pl_namespace, pl_title) = (page_namespace, page_title)
        AND
        page_is_redirect = 0 AND NEXTVAL('yourprogress')!=0;

然后在另一个会话中只需执行以下操作（不必担心事务，因为序列会全局受到影响）：

select last_value from yourprogress;

这将显示受影响的行数，以便您可以估计需要多长时间。

在结束时重新开始序列以进行另一次尝试：

alter sequence yourprogress restart with 1;

或者直接拖放它：

drop sequence yourprogress;

- Luciano Andress Martini

Answer 3

你需要索引，否则就像Bill指出的那样，它将需要对所有表进行顺序扫描。

CREATE INDEX page_ns_title_idx on page(page_namespace, page_title);
CREATE INDEX pl_ns_title_idx on pagelink(pl_namespace, pl_title);
CREATE INDEX page_redir_idx on page(page_is_redirect);

- Barry Brown

---

我已经有了这些索引。我进入postgresql.conf文件并调整了查询优化器和索引相关变量，以便强烈鼓励QO明智地选择！当表的VACUUM完成后，我将尝试在没有重定向子句的情况下运行它。谢谢。 - Nicholas Leonard