In response to Willy-Bas Loos <willybas@xxxxxxxxx>: > > Whenever i start a big action, like inserting millions of recs or doing a > large update, the autovacuum fires on top of that. > 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. If autovacuum is interfering with performance, you have two basic choices: disable autovac or add hardware (assuming your system is already optimally tuned). > Should I keep autovacuum on, or is it better to run a script like: > vacuumdb -az (daily except one day) > vacuumdb -azf (once a week) VACUUM FULL is seldom a good choice for a scheduled activity. That being said, there are some corner use cases where a regular VACUUM FULL is a good idea. Also, it's likely that a daily vacuum won't be enough. If that were the case, then autovac wouldn't be kicking off as often as it does. Once you start vacuuming multiple times per day, you're back to interfering with performance again. However, if you're doing it on a controlled schedule, it's possible that you can schedule it in such a way that it's less intrusive. Overall, however, you're probably going to have to solve the problem by adding hardware, or better tuning your system. > My database is growing, so really freeing up space isn't such an issue. What > I want is optimal performance. If you _only_ do inserts, then vacuum isn't required at all. If you do _ANY_ UPDATE or DELETE operations, then you still need vacuum or your filesystem will fill up with data that's no longer used. If autovacuum is kicking off, then you need to do vacuums, as autovac isn't random, it uses statistics on the usage of tables to determine if it's needed. You can also manually vacuum as part of your application. For example, vacuum a table manually after a bunch of UPDATEs. If you do this intelligently, you can leave autovacuum enabled, and it will only pick vacuum tables that aren't done manually. > Of course autovacuum is more generic, but for me i think the daily scheme > will be sufficient. Unlikely. Unless your database sees very few updates, daily vacuum probably isn't often enough. However, your usage pattern will dictate that. > A connection told me it would be better to enable the autovacuum, because it > does more than the above script. Can anyone verify that? Autovacuum is smarter -- it won't vacuum tables that don't need it, whereas the above script vacuums everything, even if it doesn't need it. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
