Search Postgresql Archives

Re: is autovacuum recommended?

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

 



Hi,

On Thursday 09 July 2009 19:25:15 Willy-Bas Loos wrote:
> Whenever i start a big action, like inserting millions of recs or doing a
> large update, the autovacuum fires on top of that.
You can configure autovacuum to use less resources.
http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html

> It has some adverse effects on performance when i need it most. More than
> once a postgres service crashed on me because of it.
> Sure, it had too little memory, but it wouldn't have happened if it wasn't
> for the autovacuum.
That sounds like misconfiguration. PG got OOM killed? 

It is also possible that your max_fsm_pages were to small, so autovacuum hat 
to start all over.

> My database is growing, so really freeing up space isn't such an issue.
> What I want is optimal performance.
Well, not doing vacuum regulary can lead to significant bloat which makes 
lookups (and thus updates/deletes) way much slower.

> Of course autovacuum is more generic, but for me i think the daily scheme
> will be sufficient.
That may be. Hard to say without knowing more though. How fast are your tables 
changing? Inserts only or also updates/deletes?

> A connection told me it would be better to enable the autovacuum, because
> it does more than the above script. Can anyone verify that?
I dont think it really does more.


> vacuumdb -azf (once a week)
Using -f is normally not a good idea. It takes an exclusive lock on the tables 
- so you cannot do anything in those tables.

Which version of PG are you using?

Another possibility would be to upgrade to 8.4 - keeping track of free space 
works quite different there.

Andres

-- 
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