Thread: unreasonable run time for vacuum analyze?

Hello -

I'm running 8.2.3 on an 8-processor Linux box with a fiber-optic SAN.

One table in the database contains 22.6M rows of 71 fields with an average length of about 500 bytes.  The table is in BCNF.  The table is queried in a wide variety of ways.  There are 37 btree and Gin indexes defined on the table.  The GIN indexes are on array fields.  The rest are btree.

I started running vacuum analyze on this table over 44 hours ago, and it is still running.  The load on the machine has been modest.  There have been no times when all eight processors were being used.

1) is this amount of time reasonable?
2) if I abort the vacuum analyze, will I corrupt the table, its indexes, or the database?
3) are there parameters that I can set to improve the performance of the vacuum analyze process?

Thank you,
David

--- David Monarchi <david.e.monarchi@gmail.com> wrote:

> Hello -
>
> I'm running 8.2.3 on an 8-processor Linux box with a fiber-optic SAN.
>
> One table in the database contains 22.6M rows of 71 fields with an average
> length of about 500 bytes.  The table is in BCNF.  The table is queried in a
> wide variety of ways.  There are 37 btree and Gin indexes defined on the
> table.  The GIN indexes are on array fields.  The rest are btree.
>
> I started running vacuum analyze on this table over 44 hours ago, and it is
> still running.  The load on the machine has been modest.  There have been no
> times when all eight processors were being used.
>
> 1) is this amount of time reasonable?
> 2) if I abort the vacuum analyze, will I corrupt the table, its indexes, or
> the database?
> 3) are there parameters that I can set to improve the performance of the
> vacuum analyze process?

If you don't get an answer very quickly, you might want to repost this message on the postgresql
performance list.

Regards,
Richard Broersma Jr.

"David Monarchi" <david.e.monarchi@gmail.com> writes:
> One table in the database contains 22.6M rows of 71 fields with an average
> length of about 500 bytes.  The table is in BCNF.  The table is queried in a
> wide variety of ways.  There are 37 btree and Gin indexes defined on the
> table.  The GIN indexes are on array fields.  The rest are btree.

37 indexes?  Yoi.  You should think harder about whether every one of
them really pulls its weight, because VACUUM is when they cost you.

> I started running vacuum analyze on this table over 44 hours ago, and it is
> still running.  The load on the machine has been modest.  There have been no
> times when all eight processors were being used.

I/O bound no doubt; have you been watching I/O status?  Or, if you have
vacuum_cost_delay enabled, maybe it's set too high.

> 3) are there parameters that I can set to improve the performance of the
> vacuum analyze process?

What is maintenance_work_mem set to?

            regards, tom lane