Thread: Clarification required: autovacuum and VACUUM command mode
Hello,
I need a clarification on auto-vacuum. Appreciate any clarification/help.
As explained in page 1093 of postgresql-8.2-US.pdf, VACUUM command works either in plain mode (if we do not specify FULL) or in FULL mode. When autovacuum is enabled, what mode does it run the VACUUM command (in plain mode or in FULL mode)?
postgresql-8.2-US.pdf manual is available at http://www.postgresql.org/files/documentation/pdf/8.2/postgresql-8.2-US.pdf
Regards, Narasimha Murthy
Cell +91 95814 98895, +91-94940 62794 | 040-2347 2025 (O) | x2025 (O)
Hi, Le 14/05/2010 14:52, Narasimha Murthy-VRFX87 a écrit : > [...] > I need a clarification on auto-vacuum. Appreciate any > clarification/help. > > As explained in page 1093 of postgresql-8.2-US.pdf, VACUUM command works > either in plain mode (if we do not specify FULL) or in FULL mode. When > autovacuum is enabled, what mode does it run the VACUUM command (in > plain mode or in FULL mode)? In plain mode. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Hi Guillaume, Thanks for a quick response. How do I check this? Please clarify. Regards, Narasimha Murthy Cell +91 95814 98895, +91-94940 62794 | 040-2347 2025 (O) | x2025 (O) -----Original Message----- From: Guillaume Lelarge [mailto:guillaume@lelarge.info] Sent: Friday, May 14, 2010 6:34 PM To: Narasimha Murthy-VRFX87 Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Clarification required: autovacuum and VACUUM command mode Hi, Le 14/05/2010 14:52, Narasimha Murthy-VRFX87 a écrit : > [...] > I need a clarification on auto-vacuum. Appreciate any > clarification/help. > > As explained in page 1093 of postgresql-8.2-US.pdf, VACUUM command > works either in plain mode (if we do not specify FULL) or in FULL > mode. When autovacuum is enabled, what mode does it run the VACUUM > command (in plain mode or in FULL mode)? In plain mode. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
"Narasimha Murthy-VRFX87" <VRFX87@motorola.com> wrote: > Guillaume Lelarge <guillaume@lelarge.info> wrote: >> Le 14/05/2010 14:52, Narasimha Murthy-VRFX87 a écrit : >>> VACUUM command works either in plain mode (if we do not specify >>> FULL) or in FULL mode. When autovacuum is enabled, what mode >>> does it run the VACUUM command (in plain mode or in FULL mode)? >> >> In plain mode. > How do I check this? Please clarify. http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY Since VACUUM FULL is not recommended for routine use, it would hardly make sense to use it for autovacuum. If you want to confirm, try selecting from a table while autovacuum is vacuuming it -- since VACUUM FULL locks the table against all other uses, the ability to select proves it's not VACUUM FULL. By the way, I hope you're not considering scheduling regular VACUUM FULL runs against your database. You would wind up regretting that. If you don't believe that, I recommend that you reread the above-cited section until you're convinced. -Kevin
Hi Kevin, Thanks for the clarification. I have planned to schedule Lazy Vacuum (, not FULL Vacuum) using auto-vacuum daemon. Another query: I do not want to run Analyze. However, auto-vacuum runs both both VACUUM and ANALYZE. I do not see any optionto disable running ANALYZE during auto-vacuum. Is there any way to achive this? Pl clarify. Regards, Narasimha Murthy Cell +91 95814 98895, +91-94940 62794 | 040-2347 2025 (O) | x2025 (O) -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Friday, May 14, 2010 7:10 PM To: Narasimha Murthy-VRFX87 Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Clarification required: autovacuum and VACUUM command mode "Narasimha Murthy-VRFX87" <VRFX87@motorola.com> wrote: > Guillaume Lelarge <guillaume@lelarge.info> wrote: >> Le 14/05/2010 14:52, Narasimha Murthy-VRFX87 a écrit : >>> VACUUM command works either in plain mode (if we do not specify >>> FULL) or in FULL mode. When autovacuum is enabled, what mode does it >>> run the VACUUM command (in plain mode or in FULL mode)? >> >> In plain mode. > How do I check this? Please clarify. http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY Since VACUUM FULL is not recommended for routine use, it would hardly make sense to use it for autovacuum. If you want toconfirm, try selecting from a table while autovacuum is vacuuming it -- since VACUUM FULL locks the table against all otheruses, the ability to select proves it's not VACUUM FULL. By the way, I hope you're not considering scheduling regular VACUUM FULL runs against your database. You would wind up regrettingthat. If you don't believe that, I recommend that you reread the above-cited section until you're convinced. -Kevin
On Fri, May 14, 2010 at 8:20 AM, Narasimha Murthy-VRFX87 <VRFX87@motorola.com> wrote: > Hi Kevin, > > Thanks for the clarification. > > I have planned to schedule Lazy Vacuum (, not FULL Vacuum) using auto-vacuum daemon. > > Another query: I do not want to run Analyze. However, auto-vacuum runs both both VACUUM and ANALYZE. I do not see any optionto disable running ANALYZE during auto-vacuum. Is there any way to achive this? Why would you NOT want to analyze? Are you considering doing your own analyzes at the end of a series of updates?
"Narasimha Murthy-VRFX87" <VRFX87@motorola.com> wrote: > Another query: I do not want to run Analyze. However, auto-vacuum > runs both both VACUUM and ANALYZE. I do not see any option to > disable running ANALYZE during auto-vacuum. Is there any way to > achive this? Usually it's best to run ANALYZE more often than VACUUM, since the statistics generated by ANALYZE are used to choose the most efficient plan, and ANALYZE runs faster because it just takes a random sample of rows. Out-of-date statistics are one of the most common causes of slow queries. That's not to say there couldn't be some very unusual circumstances under which it might make sense to disable ANALYZE, but I'm highly skeptical. Anyway, if you *really* want to shoot that fly off the end of your toe (to stretch a metaphor), you could adjust the autovacuum settings related to analyze to really high values. -Kevin
Hi Kevin, Thanks again for a quick response. I am using a 3rd party application, which in turn using PostgreSQL DB. I can not change/tune the SQL queries, performed by the 3rd party application. Hence, the statistics generated by ANALYZE, is of no use to me. That is the reason, I have planned not to run ANALYZE. Regards, Narasimha Murthy Cell +91 95814 98895, +91-94940 62794 | 040-2347 2025 (O) | x2025 (O) -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Friday, May 14, 2010 8:18 PM To: Narasimha Murthy-VRFX87 Cc: pgsql-admin@postgresql.org Subject: RE: [ADMIN] Clarification required: autovacuum and VACUUM command mode "Narasimha Murthy-VRFX87" <VRFX87@motorola.com> wrote: > Another query: I do not want to run Analyze. However, auto-vacuum runs > both both VACUUM and ANALYZE. I do not see any option to disable > running ANALYZE during auto-vacuum. Is there any way to achive this? Usually it's best to run ANALYZE more often than VACUUM, since the statistics generated by ANALYZE are used to choose the most efficient plan, and ANALYZE runs faster because it just takes a random sample of rows. Out-of-date statistics are one of the most common causes of slow queries. That's not to say there couldn't be some very unusual circumstances under which it might make sense to disable ANALYZE, but I'm highly skeptical. Anyway, if you *really* want to shoot that fly off the end of your toe (to stretch a metaphor), you could adjust the autovacuum settings related to analyze to really high values. -Kevin
You misunderstand the purpose of ANALYZE. Postgres needs the results in order to perform queries efficiently. Whether you have the ability to change or tune the queries is beside the point. - Lewis On 5/14/2010 11:01 AM, Narasimha Murthy-VRFX87 wrote: > Hi Kevin, > > Thanks again for a quick response. > > I am using a 3rd party application, which in turn using PostgreSQL DB. I > can not change/tune the SQL queries, performed by the 3rd party > application. Hence, the statistics generated by ANALYZE, is of no use to > me. That is the reason, I have planned not to run ANALYZE. > > Regards, Narasimha Murthy > Cell +91 95814 98895, +91-94940 62794 | 040-2347 2025 (O) | x2025 (O) > > -----Original Message----- > From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] > Sent: Friday, May 14, 2010 8:18 PM > To: Narasimha Murthy-VRFX87 > Cc: pgsql-admin@postgresql.org > Subject: RE: [ADMIN] Clarification required: autovacuum and VACUUM > command mode > > "Narasimha Murthy-VRFX87"<VRFX87@motorola.com> wrote: > >> Another query: I do not want to run Analyze. However, auto-vacuum runs > >> both both VACUUM and ANALYZE. I do not see any option to disable >> running ANALYZE during auto-vacuum. Is there any way to achive this? > > Usually it's best to run ANALYZE more often than VACUUM, since the > statistics generated by ANALYZE are used to choose the most efficient > plan, and ANALYZE runs faster because it just takes a random sample of > rows. Out-of-date statistics are one of the most common causes of slow > queries. That's not to say there couldn't be some very unusual > circumstances under which it might make sense to disable ANALYZE, but > I'm highly skeptical. > > Anyway, if you *really* want to shoot that fly off the end of your toe > (to stretch a metaphor), you could adjust the autovacuum settings > related to analyze to really high values. > > -Kevin >
"Narasimha Murthy-VRFX87" <VRFX87@motorola.com> wrote: > I am using a 3rd party application, which in turn using PostgreSQL > DB. I can not change/tune the SQL queries, performed by the 3rd > party application. Hence, the statistics generated by ANALYZE, is of no > use to me. That is the reason, I have planned not to run ANALYZE. I think perhaps you're misunderstanding what ANALYZE does. Did that vendor specifically tell you to disable ANALYZE? Unless they're doing some analysis of the distribution of data and creating their own statistics (remotely possible but highly unusual), the statistics generated by ANALYZE will be required to allow the queries they've written to execute efficiently. -Kevin
Hi Lewis, Thanks for the clarification. Referring to the statement in postgresql-8.2-US.pdf document "ANALYZE collects statistics about the contents of tables in the database ... query planner uses these statistics to help determine the most efficient execution plans for queries." From your response, I now understood that "Query planner" is part of PostgreSQL DB. Regards, Narasimha Murthy Cell +91 95814 98895, +91-94940 62794 | 040-2347 2025 (O) | x2025 (O) -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Lewis Kapell Sent: Friday, May 14, 2010 8:34 PM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Clarification required: autovacuum and VACUUM command mode You misunderstand the purpose of ANALYZE. Postgres needs the results in order to perform queries efficiently. Whether you have the ability to change or tune the queries is beside the point. - Lewis On 5/14/2010 11:01 AM, Narasimha Murthy-VRFX87 wrote: > Hi Kevin, > > Thanks again for a quick response. > > I am using a 3rd party application, which in turn using PostgreSQL DB. > I can not change/tune the SQL queries, performed by the 3rd party > application. Hence, the statistics generated by ANALYZE, is of no use > to me. That is the reason, I have planned not to run ANALYZE. > > Regards, Narasimha Murthy > Cell +91 95814 98895, +91-94940 62794 | 040-2347 2025 (O) | x2025 (O) > > -----Original Message----- > From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] > Sent: Friday, May 14, 2010 8:18 PM > To: Narasimha Murthy-VRFX87 > Cc: pgsql-admin@postgresql.org > Subject: RE: [ADMIN] Clarification required: autovacuum and VACUUM > command mode > > "Narasimha Murthy-VRFX87"<VRFX87@motorola.com> wrote: > >> Another query: I do not want to run Analyze. However, auto-vacuum >> runs > >> both both VACUUM and ANALYZE. I do not see any option to disable >> running ANALYZE during auto-vacuum. Is there any way to achive this? > > Usually it's best to run ANALYZE more often than VACUUM, since the > statistics generated by ANALYZE are used to choose the most efficient > plan, and ANALYZE runs faster because it just takes a random sample of > rows. Out-of-date statistics are one of the most common causes of > slow queries. That's not to say there couldn't be some very unusual > circumstances under which it might make sense to disable ANALYZE, but > I'm highly skeptical. > > Anyway, if you *really* want to shoot that fly off the end of your toe > (to stretch a metaphor), you could adjust the autovacuum settings > related to analyze to really high values. > > -Kevin > -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Yes, the query planner is part of Postgres itself. Here are some pages of the manual that talk about the planner and how it works: http://www.postgresql.org/docs/8.3/static/using-explain.html http://www.postgresql.org/docs/8.3/static/planner-stats.html And this talks about configuration options to control (or at least influence) what the planner does: http://www.postgresql.org/docs/8.3/static/runtime-config-query.html - Lewis On 5/14/2010 11:17 AM, Narasimha Murthy-VRFX87 wrote: > Hi Lewis, > > Thanks for the clarification. > > Referring to the statement in postgresql-8.2-US.pdf document "ANALYZE > collects statistics about the contents of tables in the database ... > query planner uses these statistics to help determine the most efficient > execution plans for queries." > > From your response, I now understood that "Query planner" is part of > PostgreSQL DB. > > Regards, Narasimha Murthy > Cell +91 95814 98895, +91-94940 62794 | 040-2347 2025 (O) | x2025 (O) > > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Lewis Kapell > Sent: Friday, May 14, 2010 8:34 PM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Clarification required: autovacuum and VACUUM > command mode > > You misunderstand the purpose of ANALYZE. Postgres needs the results in > order to perform queries efficiently. Whether you have the ability to > change or tune the queries is beside the point. > > - Lewis > > > On 5/14/2010 11:01 AM, Narasimha Murthy-VRFX87 wrote: >> Hi Kevin, >> >> Thanks again for a quick response. >> >> I am using a 3rd party application, which in turn using PostgreSQL DB. > >> I can not change/tune the SQL queries, performed by the 3rd party >> application. Hence, the statistics generated by ANALYZE, is of no use >> to me. That is the reason, I have planned not to run ANALYZE. >> >> Regards, Narasimha Murthy >> Cell +91 95814 98895, +91-94940 62794 | 040-2347 2025 (O) | x2025 (O) >> >> -----Original Message----- >> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] >> Sent: Friday, May 14, 2010 8:18 PM >> To: Narasimha Murthy-VRFX87 >> Cc: pgsql-admin@postgresql.org >> Subject: RE: [ADMIN] Clarification required: autovacuum and VACUUM >> command mode >> >> "Narasimha Murthy-VRFX87"<VRFX87@motorola.com> wrote: >> >>> Another query: I do not want to run Analyze. However, auto-vacuum >>> runs >> >>> both both VACUUM and ANALYZE. I do not see any option to disable >>> running ANALYZE during auto-vacuum. Is there any way to achive this? >> >> Usually it's best to run ANALYZE more often than VACUUM, since the >> statistics generated by ANALYZE are used to choose the most efficient >> plan, and ANALYZE runs faster because it just takes a random sample of > >> rows. Out-of-date statistics are one of the most common causes of >> slow queries. That's not to say there couldn't be some very unusual >> circumstances under which it might make sense to disable ANALYZE, but >> I'm highly skeptical. >> >> Anyway, if you *really* want to shoot that fly off the end of your toe > >> (to stretch a metaphor), you could adjust the autovacuum settings >> related to analyze to really high values. >> >> -Kevin >> > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >