Обсуждение: 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.