Date: Tue, 23 Oct 2007 09:52:52 -0700
From: Ron St-Pierre <ron.pgsql@shaw.ca>
Subject: Re: 12 hour table vacuums
In-reply-to: <87y7dtokfu.fsf@oxford.xeocode.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Message-id: <471E26E4.7040804@shaw.ca>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <471E18ED.3090704@shaw.ca> <87y7dtokfu.fsf@oxford.xeocode.com>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)

Gregory Stark wrote:
> "Ron St-Pierre" <ron.pgsql@shaw.ca> writes:
>
>   
>> We vacuum only a few of our tables nightly, this one is the last one because it
>> takes longer to run. I'll probably re-index it soon, but I would appreciate any
>> advice on how to speed up the vacuum process (and the db in general).
>>     
> ...
>   
>> vacuum_cost_delay = 200
>>     
>
> Well speeding up vacuum isn't really useful in itself. In fact you have vacuum
> configured to run quite slowly by having vacuum_cost_delay set so high. You
> have it set to sleep 200ms every few pages. If you lower that it'll run faster
> but take more bandwidth away from the foreground tasks.
>   
It's okay if it uses a lot of resources, because it's scheduled to run 
during the night (our slow time). Because most of the important queries 
running during the day use this table, I want the vacuum analzye 
finished ASAP.
>   
>> Here's the table information:
>> The table has 140,000 rows, 130 columns (mostly NUMERIC), 60 indexes. 
>>     
>
> For what it's worth NUMERIC columns take more space than you might expect.
> Figure a minimum of 12 bytes your rows are at about 1.5k each even if the
> non-numeric columns aren't large themselves. What are the other columns?
>   
The NUMERIC columns hold currency related values, with values ranging 
from a few cents to the billions, as well as a few negative numbers.
>   
>> We were running autovacuum but it interfered with the updates to we shut it
>> off. 
>>     
>
> Was it just the I/O bandwidth? I'm surprised as your vacuum_cost_delay is
> quite high. Manual vacuum doesn't do anything differently from autovacuum,
> neither should interfere directly with updates except by taking away
> I/O bandwidth.
>
>   
I don't know what the problem was. I tried to exclude certain tables 
from autovacuuming, but it autovacuumed anyway.

>> We vacuum this table nightly, and it currently takes about 12 hours to
>> vacuum it. Not much else is running during this period, nothing that should
>> affect the table.
>>     
>
> Is this time increasing over time? If once a day isn't enough then you may be
> accumulating more and more dead space over time. In which case you may be
> better off running it during prime time with a large vacuum_cost_delay (like
> the 200 you have configured) rather than trying to get to run fast enough to
> fit in the off-peak period.
>
>   
>> deadlock_timeout = 10000
>>     
>
> I would not suggest having this quite this high. Raising it from the default
> is fine but having a value larger than your patience is likely to give you the
> false impression that something is hung if you should ever get a deadlock.
>
>   
Good point. I'll look into this.

Thanks

Ron