Обсуждение: How to write a script to analyze and vacuum all the tables in the system catalog?Thanks.
Hi Guys. Please help me about this. For postgres database, it looks like we need analyze and vacuum all the tables periodly. I need to write a script which can be executed in crontab. I don't have any clues about that. I only know the command : analyze tablename; vacuum tablename; Please helps. Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-write-a-script-to-analyze-and-vacuum-all-the-tables-in-the-system-catalog-Thanks-tp5709059.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: How to write a script to analyze and vacuum all the tables in the system catalog?Thanks.
От
Steve Crawford
Дата:
On 05/17/2012 11:30 AM, leaf_yxj wrote: > Hi Guys. Please help me about this. > > For postgres database, it looks like we need analyze and vacuum all the > tables periodly. I need to write a script which can be executed in crontab. > I don't have any clues about that. I only know the command : > analyze tablename; > vacuum tablename; Leave off the tablename to do the whole db. But (unless you are on a really old version of PG) you really shouldn't need to manually vacuum things as that is the job of the autovacuum process. There are unusual situations that suggest a manual analyze. It should be run right after a cluster for instance. But they are limited special cases. What indicates that you need to vacuum or analyze via cron? Cheers, Steve
Hi Steve,
Thanks for your reply.
My Postgresql version is 8.2. I amn't sure we have the autovacuum job running or not? How can I check it ?
Thanks.
Grace
At 2012-05-18 02:53:15,"Steve Crawford" <scrawford@pinpointresearch.com> wrote: >On 05/17/2012 11:30 AM, leaf_yxj wrote: >> Hi Guys. Please help me about this. >> >> For postgres database, it looks like we need analyze and vacuum all the >> tables periodly. I need to write a script which can be executed in crontab. >> I don't have any clues about that. I only know the command : >> analyze tablename; >> vacuum tablename; >Leave off the tablename to do the whole db. But (unless you are on a >really old version of PG) you really shouldn't need to manually vacuum >things as that is the job of the autovacuum process. > >There are unusual situations that suggest a manual analyze. It should be >run right after a cluster for instance. But they are limited special cases. > >What indicates that you need to vacuum or analyze via cron? > >Cheers, >Steve >
Thanks. Steve
On 05/17/2012 11:30 AM, leaf_yxj wrote:
> Hi Guys. Please help me about this.
>
> For postgres database, it looks like we need analyze and vacuum all the
> tables periodly. I need to write a script which can be executed in crontab.
> I don't have any clues about that. I only know the command :
> analyze tablename;
> vacuum tablename;
Leave off the tablename to do the whole db. But (unless you are on a
really old version of PG) you really shouldn't need to manually vacuum
things as that is the job of the autovacuum process.
There are unusual situations that suggest a manual analyze. It should be
run right after a cluster for instance. But they are limited special cases.
What indicates that you need to vacuum or analyze via cron?
Cheers,
Steve
--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalIf you reply to this email, your message will be added to the discussion below:http://postgresql.1045698.n5.nabble.com/How-to-write-a-script-to-analyze-and-vacuum-all-the-tables-in-the-system-catalog-Thanks-tp5709059p5709066.htmlTo unsubscribe from How to write a script to analyze and vacuum all the tables in the system catalog?Thanks., click here.
NAML
View this message in context: Re:Re: How to write a script to analyze and vacuum all the tables in the system catalog?Thanks.
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: How to write a script to analyze and vacuum all the tables in the system catalog?Thanks.
От
Steve Crawford
Дата:
On 05/18/2012 06:47 AM, yxj wrote: > Hi Steve, > Thanks for your reply. > My Postgresql version is 8.2. I amn't sure we have the autovacuum job > running or not? How can I check it ? > Unless something is broken it is already running. You can connect to the db and run "show autovacuum;". It should be on. Alternately, run "show all;" and scroll down to look at all the autovacuum settings. You can also check the running processes. On *nix: if you run "ps -ef | grep postgres" (assuming typical setups where PostgreSQL is running as the postgres user) you will see something like: postgres 14698 1 0 Jan10 ? 01:20:57 /usr/bin/postgres -p 5432 -D /var/lib/pgsql/data postgres 14700 14698 0 Jan10 ? 00:07:20 postgres: writer process postgres 14701 14698 0 Jan10 ? 00:03:09 postgres: wal writer process postgres 14702 14698 0 Jan10 ? 00:00:21 postgres: autovacuum launcher process postgres 14703 14698 0 Jan10 ? 00:51:48 postgres: stats collector process ... The "autovacuum launcher process" is the one that handles automatically running vacuum when it is appropriate. Cheers, Steve