Thread

1. auto-vacuum questions

   John Gateley <gateley@jriver.com> — 2008-05-08T14:43:02Z

   Hi, I have a couple of questions on the auto-vacuum daemon:
   
   1) How do I know it is running. I suspect it has not been, and
   I carefully checked the documentation and found a variable that
   wasn't set (stats_row_level defaults to off in 8.1, should be
   "on"). So I changed that and restarted, but how do I *know* that
   is or isn't vacuuming?
   
   2) Every night I do 
   
   pg_dump -c mydb | psql -d mydbtest
   
   so I create a copy of the database in a different name for testing
   purposes. But the new database, mydbtest, always has slow queries.
   I run an analyze and they speed up. There are NO transactions in
   mydbtest until I come in and start testing, does this mean
   auto-vacuum won't analyze it? Is this maybe related to question
   1 where I think auto-vacuum actually wasn't running?
   
   Thanks,
   
   j
   
   -- 
   John Gateley <gateley@jriver.com>
   
   

2. Re: auto-vacuum questions

   Alvaro Herrera <alvherre@commandprompt.com> — 2008-05-08T14:54:32Z

   John Gateley wrote:
   > Hi, I have a couple of questions on the auto-vacuum daemon:
   > 
   > 1) How do I know it is running. I suspect it has not been, and
   > I carefully checked the documentation and found a variable that
   > wasn't set (stats_row_level defaults to off in 8.1, should be
   > "on"). So I changed that and restarted, but how do I *know* that
   > is or isn't vacuuming?
   
   Open a session and run "SHOW autovacuum".  If it says "on", it's
   vacuuming.  Keep an eye on the autovacuum_naptime setting too.
   
   > 2) Every night I do 
   > 
   > pg_dump -c mydb | psql -d mydbtest
   > 
   > so I create a copy of the database in a different name for testing
   > purposes. But the new database, mydbtest, always has slow queries.
   > I run an analyze and they speed up. There are NO transactions in
   > mydbtest until I come in and start testing, does this mean
   > auto-vacuum won't analyze it? Is this maybe related to question
   > 1 where I think auto-vacuum actually wasn't running?
   
   It might mean it hasn't been analyzed _yet_.  If there are too many
   databases, it may take a while.
   
   -- 
   Alvaro Herrera                                http://www.CommandPrompt.com/
   The PostgreSQL Company - Command Prompt, Inc.
   
   

3. Re: auto-vacuum questions

   John Gateley <gateley@jriver.com> — 2008-05-08T15:04:20Z

   On Thu, 8 May 2008 10:54:32 -0400
   Alvaro Herrera <alvherre@commandprompt.com> wrote:
   
   > John Gateley wrote:
   > > Hi, I have a couple of questions on the auto-vacuum daemon:
   > > 
   > > 1) How do I know it is running.
   > 
   > Open a session and run "SHOW autovacuum".  If it says "on", it's
   > vacuuming.  Keep an eye on the autovacuum_naptime setting too.
   
   Thank you,
   
   j
   
   -- 
   John Gateley <gateley@jriver.com>
   
   

4. Re: auto-vacuum questions

   Alan Hodgson <ahodgson@simkin.ca> — 2008-05-08T17:58:47Z

   On Thursday 08 May 2008, John Gateley <gateley@jriver.com> wrote:
   > But the new database, mydbtest, always has slow queries.
   > I run an analyze and they speed up. 
   
   Do the query plans actually change, or are you just seeing caching effects 
   from running the analyze?
   
   -- 
   Alan
   

5. Re: auto-vacuum questions

   John Gateley <gateley@jriver.com> — 2008-05-08T18:10:12Z

   On Thu, 8 May 2008 10:58:47 -0700
   Alan Hodgson <ahodgson@simkin.ca> wrote:
   
   > On Thursday 08 May 2008, John Gateley <gateley@jriver.com> wrote:
   > > But the new database, mydbtest, always has slow queries.
   > > I run an analyze and they speed up. 
   > 
   > Do the query plans actually change, or are you just seeing caching effects 
   > from running the analyze?
   
   The query plans actually change. I'm pretty sure that the auto-vacuum
   wasn't happening, I'm going to test this tomorrow morning with
   a fresh copy.
   
   Thanks,
   
   j
   -- 
   John Gateley <gateley@jriver.com>
   
   

6. Re: auto-vacuum questions

   Scott Marlowe <scott.marlowe@gmail.com> — 2008-05-09T07:17:23Z

   On Thu, May 8, 2008 at 12:10 PM, John Gateley <gateley@jriver.com> wrote:
   >
   > On Thu, 8 May 2008 10:58:47 -0700
   >  Alan Hodgson <ahodgson@simkin.ca> wrote:
   >
   >  > On Thursday 08 May 2008, John Gateley <gateley@jriver.com> wrote:
   >  > > But the new database, mydbtest, always has slow queries.
   >  > > I run an analyze and they speed up.
   >  >
   >  > Do the query plans actually change, or are you just seeing caching effects
   >  > from running the analyze?
   >
   >  The query plans actually change. I'm pretty sure that the auto-vacuum
   >  wasn't happening, I'm going to test this tomorrow morning with
   >  a fresh copy.
   
   Well, if you're testing on different values, then it should change
   based on changes in selectivity and range.  But if the where clause is
   the same, the query plan probably isn't changing between runs.