Thread: VACUUM ANALYZE taking a long time, %I/O and %CPU very low

While VACUUMing a large table, why aren't the CPU and/or I/O
percentages pegged?

I kicked off a VACUUM ANALYZE on a database containing a 20 million
row table (~250 bytes/row). It's been running for > 2 hours now, with
%CPU and %I/O rarely exceeding 1% (as reported by top), e.g.:

  Tasks: 120 total,   1 running, 117 sleeping,   2 stopped,   0 zombie
  Cpu0  :  0.0% us,  0.0% sy,  0.0% ni, 99.9% id,  0.0% wa,  0.1% hi,
0.0% si
  Cpu1  :  0.3% us,  0.0% sy,  0.0% ni, 99.6% id,  0.1% wa,  0.0% hi,
0.0% si
  Cpu2  :  0.0% us,  0.0% sy,  0.0% ni, 99.9% id,  0.0% wa,  0.1% hi,
0.0% si
  Cpu3  :  0.3% us,  0.0% sy,  0.0% ni, 99.5% id,  0.1% wa,  0.0% hi,
0.0% si
  Mem:   5977256k total,  5952096k used,    25160k free,    13604k
buffers
  Swap:  4192924k total,      532k used,  4192392k free,  5827812k
cached

The most recent log entry was:
  INFO:  vacuuming "public.jimbo"

Output of strace (see below) does show blocks of 8K reads, so I
imagine the VACUUM is proceeding, but my real question is why isn't
one of CPU or I/O usage pegged to 100%?

select(0, NULL, NULL, NULL, {0, 70000}) = 0 (Timeout)
read(158, "\2\0\0\0\30!\32\307\1\0\0\0`\0h\1\0 \3 \230\236\312\2 "...,
8192) = 8192
read(158, "\2\0\0\0 B\32\307\1\0\0\0`\0h\1\0 \3 x\236\n\3\330\234"...,
8192) = 8192
read(158, "\2\0\0\0\10c\32\307\1\0\0\0`\0\210\1\0 \3 \230\236\312"...,
8192) = 8192
read(158, "\2\0\0\0(\204\32\307\1\0\0\0`\0P\1\0 \3 \210\236\352\2"...,
8192) = 8192
read(158, "\2\0\0\0\230\245\32\307\1\0\0\0d\0 \1\0 \3 @\236z
\3\200"..., 8192) = 8192
read(158, "\2\0\0\0\310\306\32\307\1\0\0\0`\0@\1\0 \3 @\236z
\3\200"..., 8192) = 8192
read(158, "\2\0\0\0\310\350\32\307\1\0\0\0d\0\220\0\0 \3 \230\236"...,
8192) = 8192
read(158, "\2\0\0\0\330\t\33\307\1\0\0\0`\0`\1\0 \3
\200\236\372\2"..., 8192) = 8192
read(158, "\2\0\0\0\20+\33\307\1\0\0\0`\0008\1\0 \3 @\236z
\3\310\234"..., 8192) = 8192
read(158, "\2\0\0\0\240K\33\307\1\0\0\0`\0\340\1\0 \3
\240\236\272"..., 8192) = 8192
read(158, "\2\0\0\0@l\33\307\1\0\0\0`\0\320\1\0 \3 @\236z
\3\330\234"..., 8192) = 8192
read(158, "\2\0\0\0H\215\33\307\1\0\0\0`\0h\1\0 \3 @\236z
\3\200\234"..., 8192) = 8192
read(158, "\2\0\0\0h\256\33\307\1\0\0\0`\0P\1\0 \3 @\236z
\3\300\234"..., 8192) = 8192
read(158, "\2\0\0\0\10\317\33\307\1\0\0\0`\0\320\1\0 \3 P\236Z
\3\220"..., 8192) = 8192
read(158, "\2\0\0\0X\360\33\307\1\0\0\0`\0 \1\0 \3 @\236z
\3\310\234"..., 8192) = 8192
read(158, "\2\0\0\0\340\20\34\307\1\0\0\0`\0\350\1\0 \3
\230\236\312"..., 8192) = 8192
read(158, "\2\0\0\0\3702\34\307\1\0\0\0d\0x\0\0 \3 x\236\n
\3\250\234"..., 8192) = 8192
read(158, "\2\0\0\0\0T\34\307\1\0\0\0`\0h\1\0 \3 @\236z\3\310\234"...,
8192) = 8192
read(158, "\2\0\0\0ht\34\307\1\0\0\0`\0\10\2\0 \3 `
\236:\3\270\234"..., 8192) = 8192
read(158, "\2\0\0\0\200\225\34\307\1\0\0\0`\0X\1\0 \3 @\236z
\3\200"..., 8192) = 8192
select(0, NULL, NULL, NULL, {0, 70000}) = 0 (Timeout)

Never mind.

I found "vacuum_cost_delay" in the docs, I had it set to 70. I set it
to 0 and watched CPU and I/O% peg to 100%.

On Jan 29, 2007, at 3:14 PM, shakahshakah@gmail.com wrote:
> Never mind.
>
> I found "vacuum_cost_delay" in the docs, I had it set to 70. I set it
> to 0 and watched CPU and I/O% peg to 100%.

FWIW, my experience is that if you're going to use that, a number
between 10 and 20 is usually best.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)