解释真空冗长 [英] Interpreting vacuum verbosity

问题描述

如果我看到这样的VACUUM ANALYZE VERBOSE输出......


信息： - 关于public.foo--

INFO：索引idx_foo_bar：第219213页;元组28007：已删除9434.

CPU 17.05s / 4.58u秒已过3227.62秒。


....我正确读到这个说花了超过53分钟

（3227秒）来获得17秒的CPU时间？这是否是可能的I / O争用？
可能的I / O争用？如果我的CPU显然不是很忙，还有什么可以解释这个呢？


TIA。

----- ----------------------（播出结束）----------------------- ----

提示8：解释分析是你的朋友

If I see VACUUM ANALYZE VERBOSE output like this...

INFO: --Relation public.foo--
INFO: Index idx_foo_bar: Pages 219213; Tuples 28007: Deleted 9434.
CPU 17.05s/4.58u sec elapsed 3227.62 sec.

....am I correct in reading this to say that it took more than 53 minutes
(3227 secs) to get 17 seconds of CPU time? Is this an indicator of
possible I/O contention? What else would account for this if my CPUs are
clearly not very busy?

TIA.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

推荐答案

" Ed L. " < PG *** @ bluepolka.net>写道：

"Ed L." <pg***@bluepolka.net> writes:

如果我看到这样的VACUUM ANALYZE VERBOSE输出...
INFO： - Relation public.foo--
INFO：Index idx_foo_bar：Pages 219213;元组28007：删除9434.
CPU 17.05s / 4.58u秒过去3227.62秒。
...我是否正确地读到这一点，说需要超过53分钟（3227秒）才能获得17秒的CPU时间？这是否是可能的I / O争用的指标？

If I see VACUUM ANALYZE VERBOSE output like this... INFO: --Relation public.foo--
INFO: Index idx_foo_bar: Pages 219213; Tuples 28007: Deleted 9434.
CPU 17.05s/4.58u sec elapsed 3227.62 sec. ...am I correct in reading this to say that it took more than 53 minutes
(3227 secs) to get 17 seconds of CPU time? Is this an indicator of
possible I/O contention?

更像是你的磁盘驱动器被砸到了地面 ？


如果不知道同时在你的

系统中发生了什么，很难对此进行评估。一般来说，一个纯粹的VACUUM流程*应该*是
I / O绑定。但是没有任何额外的数据，很难说200：1

CPU与I / O之比是否合理。还有其他事情发生在

的同时，如果是这样的话，他们似乎陷入了困境？什么样的

硬件是这个呢？


问候，汤姆巷


----- ----------------------（播出结束）----------------------- ----

提示2：您可以使用取消注册命令一次性取消所有列表

（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql.org ）

More like "your disk drives are being pounded into the ground" ?

It''s hard to evaluate this without knowing what else is going on in your
system at the same time. In general a pure VACUUM process *ought* to be
I/O bound. But without any additional data it''s hard to say if 200:1
CPU vs I/O ratio is reasonable or not. Were other things happening at
the same time, and if so did they seem bogged down? What sort of
hardware is this on anyway?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

2004年5月6日星期四10:30，Tom Lane写道：

On Thursday May 6 2004 10:30, Tom Lane wrote:

" Ed L." < PG *** @ bluepolka.net>写道：

"Ed L." <pg***@bluepolka.net> writes:

如果我看到这样的VACUUM ANALYZE VERBOSE输出......

信息： - 关系public.foo--
信息：索引idx_foo_bar：Pages 219213;元组28007：已删除9434.
CPU 17.05s / 4.58u秒已过3227.62秒。

...我正确地读到这个说它花了超过53
分钟（3227秒）获得17秒的CPU时间？这是否是可能的I / O争用的指示？

If I see VACUUM ANALYZE VERBOSE output like this...

INFO: --Relation public.foo--
INFO: Index idx_foo_bar: Pages 219213; Tuples 28007: Deleted 9434.
CPU 17.05s/4.58u sec elapsed 3227.62 sec.

...am I correct in reading this to say that it took more than 53
minutes (3227 secs) to get 17 seconds of CPU time? Is this an
indicator of possible I/O contention?

更像是你的磁盘驱动器正在被撞到地上。 ？

如果不知道同时在你的
系统中发生了什么，很难对此进行评估。一般来说，纯VACUUM流程*应该*是I / O绑定的。但是没有任何额外的数据，很难说200：1的CPU与I / O比是否合理。
同时发生了其他事情，如果是这样的话，他们似乎陷入了困境？无论如何，这是什么类型的硬件？

More like "your disk drives are being pounded into the ground" ?

It''s hard to evaluate this without knowing what else is going on in your
system at the same time. In general a pure VACUUM process *ought* to be
I/O bound. But without any additional data it''s hard to say if 200:1
CPU vs I/O ratio is reasonable or not. Were other things happening at
the same time, and if so did they seem bogged down? What sort of
hardware is this on anyway?

还有大量的其他活动;每秒发生数十到数百次插入和删除

。很多陷入困境，可笑的慢查询：

在ANALYZE结束后立即在500行桌上选择

表，荒谬的长插入等等。是一款SmartArray 5i / 32 RAID5

设备，带有某种戴尔RAID控制器，我相信，160mb / s，双b / b 3.2GHz xeons，大量内存。 />

一些s / w重新设计非常显着地削减了I / O，但它仍然是非常缓慢的b / b
。看到

最麻烦的慢表后面的VACUUM ANALYZE VERBOSE输出，并注意到那里有2.5M未使用的元组，我们

决定删除/重新创建/重新加载该表以重新开始空间和

预感它可能是相关的。我们在没有任何客户停机时间的交易中做到了这一点，并且在重新加载时，系统再次快速燃烧。

Joy。那很酷。


我猜这个活动完全超出了autovac跟上的能力。

我的印象是未使用的元组只是一个磁盘空间问题

并没有出现这样的性能问题，但也许现场数据刚好如此支持
支离破碎，在这么多页面上执行小扫描需要花费很长时间？

---------------------------（广播结束）------------- --------------

提示9：如果您的

加入专栏，计划员将无视您选择索引扫描的愿望s数据类型不匹配

There was a ton of other activity; tens to hundreds of inserts and deletes
occurring per second. Lots of bogged down, ridiculously slow queries:
30-second selects on a 500-row table immediately after ANALYZE finished on
the table, absurdly long inserts, etc. This is a SmartArray 5i/32 RAID5
device with some sort of Dell RAID controller, I believe, 160mb/s, dual
3.2GHz xeons, plenty of RAM.

Some s/w redesign cut the I/O very signficantly, but it was still
ridiculously slow. After seeing the VACUUM ANALYZE VERBOSE output for the
most troublesomely slow table, and noticing 2.5M unused tuples there, we
decided to drop/recreate/reload that table to reclaim the space and on the
hunch that it might be related. We did that in a transaction without any
customer downtime, and upon reloading, the system was blazing fast again.
Joy. That was cool.

I guess the activity just totally outran the ability of autovac to keep up.
I was under the impression that unused tuples were only a diskspace issue
and not such a performance issue, but maybe the live data just got so
fragmented that it took forever to perform small scans over so many pages?
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match

" Ed L." < PG *** @ bluepolka.net>写道：

"Ed L." <pg***@bluepolka.net> writes:

我猜这个活动完全超出了autovac跟上的能力。

I guess the activity just totally outran the ability of autovac to keep up.

你能不能用autovac''误读''3e6''作为''3'的错误？

如果你没有最新的版本，它很可能无法真空吸尘

表通常就够了。


问候，汤姆小巷


---------------- -----------（播出结束）---------------------------

提示5：您是否检查了我们广泛的常见问题解答？

http://www.postgresql.org/docs/faqs/FAQ.html

Could you have been bit by autovac''s bug with misreading ''3e6'' as ''3''?
If you don''t have a recent version it''s likely to fail to vacuum large
tables often enough.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

这篇关于解释真空冗长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！