Re: Vacuum and Reindex hangs

On Thu, Jan 15, 2009 at 2:24 PM, Jason Long
<mailing(dot)list(at)supernovasoftware(dot)com> wrote:
> Scott Marlowe wrote:

> You got me. I have a set of mirrored raptors. I am not sure the disk i/o
> subsystem is a bottleneck.
> The whole DB is 50 mb with minimal users.

Then you're only ever writing to the db, and 50Meg is teeny tiny.
Even my laptop can write out at 50Megs in about 5 seconds.

> Would a 16 SAS Disk RAID -10 really help me that much?

Depends on your usage pattern. We use a 12 disk one with 15k5
seagates to handle a couple of 30G databases running 2000 to 5000
requests per minute, 97% or so being reads.

> The dataset is small, but contains a complex data structure with many joins
> between tables.
> I would appreciate any advice on the effect of a high end disk setup for my
> case.

Given how small your dataset is, a simple caching RAID controller
should offer enough throughput that you don't need more drives.

> I used to use full vacuum and reindex ever night just before I did a dump
> backup. Then I started to try the autovacuum.
> The reason for the vacuum now it that I have a dynamic query that sometimes
> brings the server to a grinding halt.

You might be better served by a cluster command than a vacuum full.
It rewrites the table much like a vacuum full, but it's faster,
doesn't bloat the index, and results in a table who's order follows
that of the index you clustered on. We have a large table that went
from 5 to 300 seconds to .5 to 3 seconds avg query speed because of a
cluster command. Took 80 minutes to cluster the first time, but it
was well worth it.