Re: Vacuum and Reindex hangs

Scott Marlowe wrote:
> On Thu, Jan 15, 2009 at 1:28 PM, Jason Long
> <mailing(dot)list(at)supernovasoftware(dot)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. :)
>