Re: autovacuum suggestions for 500,000,000+ row tables?

From: Alex Stapleton <alexs(at)advfn(dot)com>
To: Jacques Caron <jc(at)directinfos(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: autovacuum suggestions for 500,000,000+ row tables?
Date: 2005-06-20 15:05:56
Message-ID: C59BA512-5630-4F6E-82DD-BFC9F87046E0@advfn.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


On 20 Jun 2005, at 15:59, Jacques Caron wrote:

> Hi,
>
> At 16:44 20/06/2005, Alex Stapleton wrote:
>
>> We never delete
>> anything (well not often, and not much) from the tables, so I am not
>> so worried about the VACUUM status
>>
>
> DELETEs are not the only reason you might need to VACUUM. UPDATEs
> are important as well, if not more. Tables that are constantly
> updated (statistics, session data, queues...) really need to be
> VACUUMed a lot.

We UPDATE it even less often.

>
>> but I am wary of XID wraparound
>> nuking us at some point if we don't sort vacuuming out so we VACUUM
>> at least once every year ;)
>>
>
> That would give you a maximum average of 31 transactions/sec...
> Don't know if that's high or low for you.

It's high as far as inserts go for us. It does them all at the end of
each minute.

>
>> However not running ANALYZE for such huge
>> periods of time is probably impacting the statistics accuracy
>> somewhat, and I have seen some unusually slow queries at times.
>> Anyway, does anyone think we might benefit from a more aggressive
>> autovacuum configuration?
>>
>
> ANALYZE is not a very expensive operation, however VACUUM can
> definitely be a big strain and take a looooong time on big tables,
> depending on your setup. I've found that partitioning tables (at
> the application level) can be quite helpful if you manage to keep
> each partition to a reasonable size (under or close to available
> memory), especially if the partitioning scheme is somehow time-
> related. YMMV.
>
> Jacques.

That's not currently an option as it would require a pretty large
amount of work to implement. I think we will have to keep that in
mind though.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message John Arbash Meinel 2005-06-20 15:20:37 Re: autovacuum suggestions for 500,000,000+ row tables?
Previous Message Jacques Caron 2005-06-20 14:59:29 Re: autovacuum suggestions for 500,000,000+ row