Thread: best way to run maintenance script

best way to run maintenance script

From
Vinubalaji Gopal
Date:
Hi all,
  I have been searching for the best way to run maintenance scripts
which does a vacuum, analyze and deletes some old data. Whenever the
maintenance script runs - mainly the pg_maintenance --analyze script -
it slows down postgresql inserts and I want to avoid that. The system is
under constant load and I am not interested in the time taken to vacuum.
Is there a utility or mechanism in postgresql which helps in reducing
priority of maintenance queries?

Is writing a postgresql C function and setting the priority of process
the only way to change the priority of the maintenance script or is
there a better way.
http://weblog.bignerdranch.com/?p=11

I tried using the nice command (Linux system) on the maintenance script
- it did not have any effect - guess it does not change the niceness of
the postgresql vacuum process.

(I am running Postgresql 8.0 on a Linux)

--
Vinu

Re: best way to run maintenance script

From
"Joshua D. Drake"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 14 Mar 2008 17:00:21 -0800
Vinubalaji Gopal <vgopal@abaca.com> wrote:

> Hi all,
>   I have been searching for the best way to run maintenance scripts
> which does a vacuum, analyze and deletes some old data. Whenever the
> maintenance script runs - mainly the pg_maintenance --analyze script -
> it slows down postgresql inserts and I want to avoid that. The system
> is under constant load and I am not interested in the time taken to
> vacuum. Is there a utility or mechanism in postgresql which helps in
> reducing priority of maintenance queries?

You can use parameters such as vacuum_cost_delay to help this... see
the docs:

http://www.postgresql.org/docs/8.3/static/runtime-config-autovacuum.html

> 
> Is writing a postgresql C function and setting the priority of process
> the only way to change the priority of the maintenance script or is
> there a better way.
> http://weblog.bignerdranch.com/?p=11
> 
> I tried using the nice command (Linux system) on the maintenance
> script
> - it did not have any effect - guess it does not change the niceness
> of the postgresql vacuum process.
> 
> (I am running Postgresql 8.0 on a Linux)

If you are truly running 8.0 and not something like 8.0.15 vacuum is
the least of your worries.

Sincerely,

Joshua D. Drake


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
      PostgreSQL political pundit | Mocker of Dolphins

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH2xkkATb/zqfZUUQRAsFxAJ422xFUGNwJZZVS47SwM9HJEYrb/gCePESL
YZFM27b93ylhy5TuE2MCcww=
=2Zpp
-----END PGP SIGNATURE-----

Re: best way to run maintenance script

From
Vinubalaji Gopal
Date:
Hi Joshua,

> You can use parameters such as vacuum_cost_delay to help this... see
> the docs:
>
> http://www.postgresql.org/docs/8.3/static/runtime-config-autovacuum.html

I am checking it out. Seems to be a nice option for vacuum - but wish
there was a way to change the delete priority or I will try to use the C
based priority hack.


> If you are truly running 8.0 and not something like 8.0.15 vacuum is
> the least of your worries.
Its 8.0.4.

Thanks.

--
Vinu

Re: best way to run maintenance script

From
Tom Lane
Date:
Vinubalaji Gopal <vgopal@abaca.com> writes:
>> If you are truly running 8.0 and not something like 8.0.15 vacuum is
>> the least of your worries.

> Its 8.0.4.

That's only a little bit better.  Read about all the bug fixes you're
missing at
http://www.postgresql.org/docs/8.0/static/release.html
and then consider updating ...

            regards, tom lane

Re: best way to run maintenance script

From
"Joshua D. Drake"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 14 Mar 2008 17:51:52 -0800
Vinubalaji Gopal <vgopal@abaca.com> wrote:

> Hi Joshua,
> 
> > You can use parameters such as vacuum_cost_delay to help this... see
> > the docs:
> > 
> > http://www.postgresql.org/docs/8.3/static/runtime-config-autovacuum.html
> 
> I am checking it out. Seems to be a nice option for vacuum - but wish
> there was a way to change the delete priority or I will try to use
> the C based priority hack.

I think you will find if you do it the right way, which is to say the
way that it is meant to be done with the configurable options, your
life will be a great deal more pleasant than some one off hack.

Sincerely,

Joshua D. Drake

- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
      PostgreSQL political pundit | Mocker of Dolphins

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH2zG0ATb/zqfZUUQRAtmeAKCpKUbZP63qmiAPI6x4i9sLaf3LfwCfTPwb
mdS3L7JzlwarEjuu3WGFdaE=
=V7wn
-----END PGP SIGNATURE-----

Re: best way to run maintenance script

From
Vinubalaji Gopal
Date:
On Fri, 2008-03-14 at 18:37 -0700, Tom Lane wrote:
> That's only a little bit better.  Read about all the bug fixes you're

Sure - will eventually upgrade it sometime - but it has to wait for
now :(


--
Vinu

Re: best way to run maintenance script

From
Vinubalaji Gopal
Date:
>
> I think you will find if you do it the right way, which is to say the
> way that it is meant to be done with the configurable options, your
> life will be a great deal more pleasant than some one off hack.
>

yeah I agree. The pg_maintanence script which calls vacuum and analyze
is the one of the thing that is causing more problems. I am trying out
various vacuum options (vacuum_cost_limit, vacuum_cost_delay) and
finding it hard to understand the implications of the variables. What
are the optimal values for the vacuum_* parameters - for a really active
database (writes at the rate of ~ 50 rows/seconds).

I started with
vacuum_cost_delay = 200
vacuum_cost_limit = 400

and that did not help much.

--
Vinu


Re: best way to run maintenance script

From
Gregory Stark
Date:
"Vinubalaji Gopal" <vgopal@abaca.com> writes:

> On Fri, 2008-03-14 at 18:37 -0700, Tom Lane wrote:
>> That's only a little bit better.  Read about all the bug fixes you're
>
> Sure - will eventually upgrade it sometime - but it has to wait for
> now :(

Waiting for one of those bugs to bite you is a bad plan.

We're not talking about an upgrade to 8.1, 8.2, or 8.3. We're talking about
taking bug-fixes and security fixes for the release you're already using.

Normally it's just a shutdown and immediate restart. There are exceptions
listed in the 8.0.6 release notes which would require a REINDEX but they don't
affect most people.


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

Re: best way to run maintenance script

From
Peter Eisentraut
Date:
Vinubalaji Gopal wrote:
> I tried using the nice command (Linux system) on the maintenance script
> - it did not have any effect - guess it does not change the niceness of
> the postgresql vacuum process.

You are probably looking for the command ionice.  nice only affects the CPU
priority, and that is usually not the primary problem for vacuum.  (And yes,
you need to nice the server process, not the client script.)