Search Postgresql Archives

Re: Vacuum and Reindex hangs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Jan 15, 2009 at 2:24 PM, Jason Long
<mailing.list@xxxxxxxxxxxxxxxxxxxxx> 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.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux