Thread: best way to run maintenance script
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
-----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-----
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
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
-----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-----
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
> > 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
"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!
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.)