Thread: More Autovacuum Questions
Thanks to those who answered my first question. I am looking at implementing autovacuuming on our new production 8.1.3 systems. I am reading the documentation on the various configuration parameters. The autovacuum_analyze_threshold and autovacuum_vacuum_threshold talk about the number of updated and deleted tuples. Is there anyway to calculate these numbers in a running database?
The reason I'm asking is that we are having some sporadic slowness issues with our databases, and I'm wanting to see how the database looks during these times. I know that vacuuming some specific tables tends to fix the problem. So, if I can see what autovacuum might see, I could set the parameters correctly to try and fix the issue.
Anyway, are these number available to me?
Thanks,
Chris
The reason I'm asking is that we are having some sporadic slowness issues with our databases, and I'm wanting to see how the database looks during these times. I know that vacuuming some specific tables tends to fix the problem. So, if I can see what autovacuum might see, I could set the parameters correctly to try and fix the issue.
Anyway, are these number available to me?
Thanks,
Chris
U can install pgstattuple module which is available in the contrib directory and run it on production as
psql> \x
psql> select * from pgstattuple('public'.'tablename');
it displays the table_len, tuple_count, tuple_len, tuple_percent, dead_tuple_count, dead_tuple_len,dead_tuple_percent,free_space,free_percent for the table .
On 5/16/06, Chris Hoover <revoohc@gmail.com> wrote:
Thanks to those who answered my first question. I am looking at implementing autovacuuming on our new production 8.1.3 systems. I am reading the documentation on the various configuration parameters. The autovacuum_analyze_threshold and autovacuum_vacuum_threshold talk about the number of updated and deleted tuples. Is there anyway to calculate these numbers in a running database?
The reason I'm asking is that we are having some sporadic slowness issues with our databases, and I'm wanting to see how the database looks during these times. I know that vacuuming some specific tables tends to fix the problem. So, if I can see what autovacuum might see, I could set the parameters correctly to try and fix the issue.
Anyway, are these number available to me?
Thanks,
Chris
--
Best,
Gourish Singbal
On Tue, May 16, 2006 at 02:00:21PM -0400, Chris Hoover wrote: > Thanks to those who answered my first question. I am looking at > implementing autovacuuming on our new production 8.1.3 systems. I am > reading the documentation on the various configuration parameters. The > autovacuum_analyze_threshold and autovacuum_vacuum_threshold talk about the > number of updated and deleted tuples. Is there anyway to calculate these > numbers in a running database? > > The reason I'm asking is that we are having some sporadic slowness issues > with our databases, and I'm wanting to see how the database looks during > these times. I know that vacuuming some specific tables tends to fix the > problem. So, if I can see what autovacuum might see, I could set the > parameters correctly to try and fix the issue. Your best bet is to turn up your logging verbosity, so that you actually get more detailed info from autovacuum. Short of that, you can monitor those tables in pg_stat_user_tables; in particular you need to watch n_tup_upd and n_tup_del. As for the autovacuum settings, I've found that the default threasholds and scale factors are too big by at least a factor of 2. By default, it will allow a relation to grow to 40% dead rows before vacuuming, which is a pretty decent amount of waisted space. Finally, there are certain tables that aren't well suited to autovacuum, namely any tables that see a very high rate of updates or deletes. For these tables I usually schedule vacuums via crontab. For tables that should be kept small (ie: queue tables), I'll even go as far as vacuuming once a minute, or more often, since these tables should stay small enough for vacuum to be very fast. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461