pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matthew T. O'Connor <matthew@zeut.net>
To: David Parker <dparker@tazznetworks.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff <threshar@torgo.978.org>
Cc: Russell Smith <mr-russ@pws.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: query plan question
Date: Wed, 17 Nov 2004 14:01:48 -0500
Message-ID: <419BA01C.7000903@zeut.net> (raw)
In-Reply-To: <07FDEE0ED7455A48AC42AC2070EDFF7C26BA10@corpsrv2.tazznetworks.com>
References: <07FDEE0ED7455A48AC42AC2070EDFF7C26BA10@corpsrv2.tazznetworks.com>

Well based on the autovacuum log that you attached, all of those tables 
are insert only (at least during the time period included in the log.   
Is that correct?  If so, autovacuum will never do a vacuum (unless 
required by xid wraparound issues) on those tables.  So this doesn't 
appear to be an autovacuum problem.  I'm not sure about the missing 
pg_statistic entries anyone else care to field that one?

Matthew


David Parker wrote:

>Thanks. The tables I'm concerned with are named: 'schema', 'usage',
>'usageparameter', and 'flow'. It looks like autovacuum is performing
>analyzes:
>
>% grep "Performing: " logs/.db.tazz.vacuum.log
>[2004-11-17 12:05:58 PM] Performing: ANALYZE
>"public"."scriptlibrary_library"
>[2004-11-17 12:15:59 PM] Performing: ANALYZE
>"public"."scriptlibraryparm"
>[2004-11-17 12:15:59 PM] Performing: ANALYZE "public"."usageparameter"
>[2004-11-17 12:21:00 PM] Performing: ANALYZE "public"."usageproperty"
>[2004-11-17 12:21:00 PM] Performing: ANALYZE "public"."route"
>[2004-11-17 12:21:00 PM] Performing: ANALYZE "public"."usageparameter"
>[2004-11-17 12:21:00 PM] Performing: ANALYZE
>"public"."scriptlibrary_library"
>[2004-11-17 12:26:01 PM] Performing: ANALYZE "public"."usage"
>[2004-11-17 12:26:01 PM] Performing: ANALYZE "public"."usageparameter"
>[2004-11-17 12:31:04 PM] Performing: ANALYZE "public"."usageproperty"
>[2004-11-17 12:36:04 PM] Performing: ANALYZE "public"."route"
>[2004-11-17 12:36:04 PM] Performing: ANALYZE "public"."service_usage"
>[2004-11-17 12:36:04 PM] Performing: ANALYZE "public"."usageparameter"
>
>But when I run the following:
>
>select * from pg_statistic where starelid in 
>(select oid from pg_class where relname in
>('schema','usageparameter','flow','usage'))
>
>it returns no records. Shouldn't it? It doesn't appear to be doing a
>vacuum anywhere, which makes sense because none of these tables have
>over the default threshold of 1000. Are there statistics which only get
>generated by vacuum?
>
>I've attached a gzip of the pg_autovacuum log file, with -d 3.
>
>Thanks again.
>
>- DAP
>
>
>  
>
>>-----Original Message-----
>>From: Matthew T. O'Connor [mailto:matthew@zeut.net] 
>>Sent: Wednesday, November 17, 2004 11:41 AM
>>To: David Parker
>>Cc: Tom Lane; Jeff; Russell Smith; pgsql-performance@postgresql.org
>>Subject: Re: [PERFORM] query plan question
>>
>>David Parker wrote:
>>
>>    
>>
>>>We're using postgresql 7.4.5. I've only recently put pg_autovacuum in 
>>>place as part of our installation, and I'm basically taking the 
>>>defaults. I doubt it's a problem with autovacuum itself, but rather 
>>>with my configuration of it. I have some reading to do, so 
>>>      
>>>
>>any pointers 
>>    
>>
>>>to existing autovacuum threads would be greatly appreciated!
>>>
>>>      
>>>
>>Well the first thing to do is increase the verbosity of the 
>>pg_autovacuum logging output.  If you use -d2 or higher, 
>>pg_autovacuum will print out a lot of detail on what it thinks 
>>the thresholds are and 
>>why it is or isn't performing vacuums and analyzes.   Attach 
>>some of the 
>>log and I'll take a look at it.
>>
>>    
>>




view thread (13+ messages)  latest in thread

Message-ID: <419BA01C.7000903@zeut.net>
Permalink:  ../419BA01C.7000903@zeut.net/
Also on:    postgresql.org/message-id/419BA01C.7000903@zeut.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: matthew@zeut.net, dparker@tazznetworks.com, tgl@sss.pgh.pa.us, threshar@torgo.978.org, mr-russ@pws.com.au
  Subject: Re: query plan question
  In-Reply-To: <419BA01C.7000903@zeut.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox