Re: Vacuum and Reindex hangs - Mailing list pgsql-general

Scott Marlowe wrote:

On Thu, Jan 15, 2009 at 1:28 PM, Jason Long
<mailing.list@supernovasoftware.com> wrote: 

A faster server.
Well the sever is plenty fast.  It has 2 quad core 1600MHz FSB 3.0 GHz Xeon
5472 CPUs and a very light workload.   

A few things.

That doesn't make a fast server.  The disk i/o subsystem makes a fast
server.  And you've mentioned nothing of that yet.  If you've got a 16
SAS Disk RAID -10 array on a fast RAID controller with battery backed
cache, you've got a fast database server.

If you've got a single SATA drive or a mirror set of two SATA drives,
you do not have a fast database server. 

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.
Would a 16 SAS Disk RAID -10 really help me that much?
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.

My statement about the time is that it has never taken that long.  Ever.
Not even close.   

I wonder if you're getting a lot of bloating in your indexes from the
full vacuums.  Is there a reason you're running full vacuums over
regular vacuums?  While there are quite a few circumstances where full
vacuums are the right answer, most of the time they are not, at least
not on a regular basis. 

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.
This is why I set the timeout to 2 minutes.
Now all I get is users pissed about 2 times a day and the problem corrects itself before they finish complaining to me.

I tried to add some more indexes and then tried to vacuum full and reindex the database.

A lot of things can cause your current vacuums to run slow.  Maybe
there's a competing regular autovacuum that's kicked in at the same
time, someone is backing up the database, and so on. 

Autovacuum could be the case, but I have total control of the database an no backups are in progress.

As for :
 

However, this is about the most anal list ever.
I see so many emails on here about people complaining regarding the proper way to reply
or post to the list.   

That's because many of us receive hundreds of emails a week, and if
everyone starts sending html email,using bouncing email addresses, or
sending emails to 5 lists at once, things can get out of hand pretty
quickly.

Since your email agent is sending multi-part mime email with regular
text and html email, there's no real reason to complain, as any agent
worth its salt can be set to show only the text part.  I'm pretty sure
the email archive process also lops off the html part before storing
it. 

I totally understand and will limit my use of HTML in the future.

Busy lists tend to be anal.  Wanna get a bunch of people mad at once?
Break the rules on the lkml.  We're a bunch of fuzzy little kittens
playing with balls of yarn by comparison.  :)