auto-vacuum questions
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>
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.
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>
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
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>
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.