Re: [PERFORM] Aggressive autovacuuming ?
On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe scott.marl...@gmail.com wrote: The largest consequence I can see at the moment is that when I get a full vacuum (for preventing transaction-id wraparound) it would be I assume you mean the automatic database wide vacuum. I don't think 8.4 and above need that anymore. I thnk 8.3 does that too, but I'm not 100% sure. 8.4 (and 9.0) do still need to do vacuums to freeze tuples before transaction ID wraparound occurs. This is not to be confused with VACUUM FULL, which is something else altogether. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Aggressive autovacuuming ?
On Wed, Jun 23, 2010 at 1:58 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe scott.marl...@gmail.com wrote: The largest consequence I can see at the moment is that when I get a full vacuum (for preventing transaction-id wraparound) it would be I assume you mean the automatic database wide vacuum. I don't think 8.4 and above need that anymore. I thnk 8.3 does that too, but I'm not 100% sure. 8.4 (and 9.0) do still need to do vacuums to freeze tuples before transaction ID wraparound occurs. This is not to be confused with VACUUM FULL, which is something else altogether. My point was that modern pgsql doesn't need db wide vacuum to prevent wrap around anymore, but can vacuum individual relations to prevent wraparound. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Aggressive autovacuuming ?
On Wed, Jun 23, 2010 at 2:20 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Jun 23, 2010 at 1:58 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe scott.marl...@gmail.com wrote: The largest consequence I can see at the moment is that when I get a full vacuum (for preventing transaction-id wraparound) it would be I assume you mean the automatic database wide vacuum. I don't think 8.4 and above need that anymore. I thnk 8.3 does that too, but I'm not 100% sure. 8.4 (and 9.0) do still need to do vacuums to freeze tuples before transaction ID wraparound occurs. This is not to be confused with VACUUM FULL, which is something else altogether. My point was that modern pgsql doesn't need db wide vacuum to prevent wrap around anymore, but can vacuum individual relations to prevent wraparound. Oh, I see. I didn't realize we used to do that. Looks like that change was committed 11/5/2006. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Aggressive autovacuuming ?
Excerpts from Scott Marlowe's message of dom jun 20 16:13:15 -0400 2010: On Sun, Jun 20, 2010 at 11:44 AM, Jesper Krogh jes...@krogh.cc wrote: Hi. I have been wondering if anyone has been experimenting with really agressive autovacuuming. I have been using moderately aggressive autovac, with 6 or more threads running with 1ms sleep, then keeping track of them to see if they're being too aggresive. Basically as long as io utilization doesn't hit 100% it doesn't seem to have any negative or even noticeable effect. Keep in mind that autovacuum scales down the cost limit the more workers there are. So if you have 10ms sleeps and 1 worker, it should roughly use a similar amount of I/O than if you have 10ms sleeps and 10 workers (each worker would sleep 10 times more frequently). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Aggressive autovacuuming ?
Jesper Krogh jes...@krogh.cc wrote: My thought was that if I tuned autovacuum to be really aggressive then I could get autovacuum to actually vacuum the tuples before they get evicted from the OS cache thus effectively saving the IO-overhead of vacuuming. Interesting concept. That might be a way to avoid the extra disk I/O to set hint bits, and then some. I haven't tried it, but I'm going to make a note to take a look when (if???) I get some free time. If you give it a try, please post the results. If you're I/O bound (rather than CPU bound) and you choose *extremely* aggressive settings, the multiple writes to pages *might* collapse in cache and significantly reduce I/O. I don't think I'd try it on a release prior to 8.4, however. Nor would I consider trying this in a production environment without a good set of tests. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Aggressive autovacuuming ?
Hi. I have been wondering if anyone has been experimenting with really agressive autovacuuming. The database I'm adminstrating rarely have long running transactions (over several minutes). And a fair amount of buffercache and an OS cache of (at best 64GB). A lot of the OS cache is being used for read-caching. My thought was that if I tuned autovacuum to be really aggressive then I could get autovacuum to actually vacuum the tuples before they get evicted from the OS cache thus effectively saving the IO-overhead of vacuuming. The largest consequence I can see at the moment is that when I get a full vacuum (for preventing transaction-id wraparound) it would be run with the same aggressive settings, thus giving a real performance hit in that situation. Has anyone tried to do similar? What is your experience? Is the idea totally bogus? Jesper -- Jesper Krogh -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Aggressive autovacuuming ?
On Sun, Jun 20, 2010 at 11:44 AM, Jesper Krogh jes...@krogh.cc wrote: Hi. I have been wondering if anyone has been experimenting with really agressive autovacuuming. I have been using moderately aggressive autovac, with 6 or more threads running with 1ms sleep, then keeping track of them to see if they're being too aggresive. Basically as long as io utilization doesn't hit 100% it doesn't seem to have any negative or even noticeable effect. I head more in the direction of running a few more threads than I absolutely need to keep up with bloat. If I'm set for 5 threads and I always have five threads running, I go to 6, 7, 8 or wherever they're never all active. But you need the IO capability to use aggresive vacuuming. The database I'm adminstrating rarely have long running transactions (over several minutes). And a fair amount of buffercache and an OS cache of (at best 64GB). A lot of the OS cache is being used for read-caching. My thought was that if I tuned autovacuum to be really aggressive then I could get autovacuum to actually vacuum the tuples before they get evicted from the OS cache thus effectively saving the IO-overhead of vacuuming. But vacuuming by design has to write out and that's the real resource you're likely to use up first. The largest consequence I can see at the moment is that when I get a full vacuum (for preventing transaction-id wraparound) it would be I assume you mean the automatic database wide vacuum. I don't think 8.4 and above need that anymore. I thnk 8.3 does that too, but I'm not 100% sure. run with the same aggressive settings, thus giving a real performance hit in that situation. Has anyone tried to do similar? What is your experience? Is the idea totally bogus? Cranking up autovacuum is not a bogus idea, but it directly impacts your IO subsystem, and if you get too aggressive (zero naptime is way aggressive) you have to back off on the number of threads to keep things sane. If your IO subsystem is one 7200RPM SATA drive with write cache disabled / fsync properly working, you're not gonna be able to get very aggresive before you make your IO subsystem bog down. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
