Обсуждение: Vacuuming technique doubt

Having a doubt, we want to vacuum and reindex some 50 most used tables daily on specific time. Is it best to have a function in postgres and call it in cron or is there any other good way to do the two process for specified tables at specified time?
-Arvind S


"Many of lifes failure are people who did not realize how close they were to success when they gave up."
-Thomas Edison

On Sun, May 31, 2009 at 10:26 PM, S Arvind <arvindwill@gmail.com> wrote:
> Having a doubt, we want to vacuum and reindex some 50 most used tables daily
> on specific time. Is it best to have a function in postgres and call it in
> cron or is there any other good way to do the two process for specified
> tables at specified time?

Just write a SQL script with the appropriate commands and run it using
psql from cron.  Set up your .pgpass and/or pg_hba.conf as
appropriate.

-Dave

On Mon, 1 Jun 2009, S Arvind wrote:

> Having a doubt, we want to vacuum and reindex some 50 most used tables daily on specific time. Is it best to have a
functionin 
> postgres and call it in cron or is there any other good way to do the two process for specified tables at specified
time?

If you haven't been using VACUUM properly, it's possible to get into a
position where you need to REINDEX your tables and go through the sort of
giant cleanup step you describe.  If you think you need to do that daily,
though, you probably should take a look at tuning autovacuum rather than
trying to fix the problem manually all the time.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Hi Smith,
The reason why we need it manually is , we don't need any  performance drop in our production hours. So we figured out the most less usage working time, most freq used tables and want to perform that  on daily . so in weekends we can vaccum and reindex entire db.. Is the model is not more efficient Smith?

-Arvind S


"Many of lifes failure are people who did not realize how close they were to success when they gave up."
-Thomas Edison

S Arvind <arvindwill@gmail.com> wrote:

> The reason why we need it manually is , we don't need any
> performance drop in our production hours. So we figured out the most
> less usage working time, most freq used tables and want to perform
> that on daily . so in weekends we can vaccum and reindex entire db..

By the time you get to your mass reindex the bloat will be harming
your performance much more than the autovacuum needs to do.  Check the
documentation here:

http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

I hope this helps.

-Kevin

On Mon, Jun 1, 2009 at 8:35 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> S Arvind <arvindwill@gmail.com> wrote:
>
>> The reason why we need it manually is , we don't need any
>> performance drop in our production hours. So we figured out the most
>> less usage working time, most freq used tables and want to perform
>> that on daily . so in weekends we can vaccum and reindex entire db..
>
> By the time you get to your mass reindex the bloat will be harming
> your performance much more than the autovacuum needs to do.  Check the
> documentation here:
>
> http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST
>
> I hope this helps.

But before you try that, try just using the default settings and see
if you actually have a problem.

...Robert