Thread: auto vacuuming
we are using postgresql 8.1.3 and wanted to enable autovacuuming for only one of the many databases on the same postgresql cluster.
pg_autovacuum seems to allow to ignore only a particular table and not the database since column vacrelid is OID from pg_class table Are there any suggestions to accomplish this tasks?.
--
Best,
Gourish Singbal
On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote: > we are using postgresql 8.1.3 and wanted to enable autovacuuming for only > one of the many databases on the same postgresql cluster. > pg_autovacuum seems to allow to ignore only a particular table and not the > database since column vacrelid is OID from pg_class table Are there any > suggestions to accomplish this tasks?. Currently there is no way to exclude entire databases from autovacuum. I'm curious, why do you want to do this anyway? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote: > > we are using postgresql 8.1.3 and wanted to enable autovacuuming for only > > one of the many databases on the same postgresql cluster. > > pg_autovacuum seems to allow to ignore only a particular table and not the > > database since column vacrelid is OID from pg_class table Are there any > > suggestions to accomplish this tasks?. > > Currently there is no way to exclude entire databases from autovacuum. IIRC in one of the original patches I had added a way to do it. It was rejected however; it was argued that there was another mechanism to disable it. I don't remember what it was though. The only possibility that comes to mind is "ALTER DATABASE SET autovacuum TO off" but I don't see why would it work. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Jim C. Nasby wrote: >> On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote: >>> we are using postgresql 8.1.3 and wanted to enable autovacuuming for only >>> one of the many databases on the same postgresql cluster. >>> pg_autovacuum seems to allow to ignore only a particular table and not the >>> database since column vacrelid is OID from pg_class table Are there any >>> suggestions to accomplish this tasks?. >> Currently there is no way to exclude entire databases from autovacuum. > > IIRC in one of the original patches I had added a way to do it. It was > rejected however; it was argued that there was another mechanism to > disable it. I don't remember what it was though. The only possibility > that comes to mind is "ALTER DATABASE SET autovacuum TO off" but I don't > see why would it work. I think the closest approximation of disabling autovacuum on a per database basis is to connect to the database in question and perform: update pg_autovacuum set enabled = 'false'; This will prevent autovacuum from vacuuming or analyzing any of the tables in the database, but will still check for XID wraparound. Matt
On Fri, Mar 31, 2006 at 04:36:10PM -0500, Matthew T. O'Connor wrote: > Alvaro Herrera wrote: > >Jim C. Nasby wrote: > >>On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote: > >>>we are using postgresql 8.1.3 and wanted to enable autovacuuming for only > >>>one of the many databases on the same postgresql cluster. > >>>pg_autovacuum seems to allow to ignore only a particular table and not > >>>the > >>>database since column vacrelid is OID from pg_class table Are there any > >>>suggestions to accomplish this tasks?. > >>Currently there is no way to exclude entire databases from autovacuum. > > > >IIRC in one of the original patches I had added a way to do it. It was > >rejected however; it was argued that there was another mechanism to > >disable it. I don't remember what it was though. The only possibility > >that comes to mind is "ALTER DATABASE SET autovacuum TO off" but I don't > >see why would it work. > > > I think the closest approximation of disabling autovacuum on a per > database basis is to connect to the database in question and perform: > > update pg_autovacuum set enabled = 'false'; > > This will prevent autovacuum from vacuuming or analyzing any of the > tables in the database, but will still check for XID wraparound. Problem with that is any time you add a table you'd need to re-run that command. Wouldn't be so bad if we had DDL triggers, but... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>> I think the closest approximation of disabling autovacuum on a per >> database basis is to connect to the database in question and perform: >> >> update pg_autovacuum set enabled = 'false'; >> >> This will prevent autovacuum from vacuuming or analyzing any of the >> tables in the database, but will still check for XID wraparound. >> > > Problem with that is any time you add a table you'd need to re-run that > command. Wouldn't be so bad if we had DDL triggers, but... > Probably a dumb comment, but can you set a default value for the enabled column?
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Jim C. Nasby wrote:
>> Currently there is no way to exclude entire databases from autovacuum.
> IIRC in one of the original patches I had added a way to do it. It was
> rejected however; it was argued that there was another mechanism to
> disable it. I don't remember what it was though.
I can't think of one either. Real question is, why would you want to?
Disabled autovac would for instance be unable to protect you against XID
wraparound, which IMHO is one of the major advantages of running it at
all.
The handwriting on the wall says that autovac will soon be on by
default, and perhaps become not-disablable some day after that
(like the second or third time we hear from someone who's lost
their data to XID wraparound after disabling it).
So if there's a really convincing use-case for locking autovac out
of specific databases, we'd better hear it soon.
regards, tom lane
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> I think the closest approximation of disabling autovacuum on a per
> database basis is to connect to the database in question and perform:
> update pg_autovacuum set enabled = 'false';
Not really gonna help unless you insert a row into pg_autovacuum for
each table in the database.
As I just commented in another reply, I don't actually believe in the
value of disabling autovac entirely --- it should at least be able to
fire when you are risking XID wraparound. What could make sense is to
push the thresholds up to very large values, such that autovac won't
fire until you've forgotten manual vacuums for a very long time.
And that you can already do on a per-database basis, using ALTER
DATABASE SET. (Or at least, it *should* work to do that; if the autovac
process fails to absorb per-db values for its GUC variables, then we
ought to fix it. I'm too lazy to test it right now...)
regards, tom lane
>
> The handwriting on the wall says that autovac will soon be on by
> default, and perhaps become not-disablable some day after that
> (like the second or third time we hear from someone who's lost
> their data to XID wraparound after disabling it).
>
> So if there's a really convincing use-case for locking autovac out
> of specific databases, we'd better hear it soon.
Sure.. a database that has over 2500 connections per a directly
connected Java rich client... I don't want autovac running on it from
8-8 but after that I will run my own vacuum process.
I am smart enough to manage my data. If I want autovac I will turn it on.
Joshua D. Drake
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Tom Lane wrote: > The handwriting on the wall says that autovac will soon be on by > default, and perhaps become not-disablable some day after that > (like the second or third time we hear from someone who's lost > their data to XID wraparound after disabling it). I don't think we will see much people losing data to Xid wraparound anymore, now that the server refuses to work when the wraparound is imminent. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> The handwriting on the wall says that autovac will soon be on by
>> default, and perhaps become not-disablable some day after that
>> (like the second or third time we hear from someone who's lost
>> their data to XID wraparound after disabling it).
> I don't think we will see much people losing data to Xid wraparound
> anymore, now that the server refuses to work when the wraparound is
> imminent.
Well, how about the second or third time we get sued by someone whose
"must be up 24x7" database shuts down for lack of proper vacuuming?
I do think autovac is the wave of the future. The only reason it's
disablable now is that we don't think we've got all the bugs out.
If you read the old Berkeley Postgres papers, you'll see that a
"vacuum daemon" was always part of the system's basic design. (Hey
Elein, or anyone else who was there then --- was there ever a working
vacuum daemon, or was it just on paper?)
regards, tom lane
On Mar 31, 2006, at 5:45 PM, Matthew T. O'Connor wrote: >>> I think the closest approximation of disabling autovacuum on a >>> per database basis is to connect to the database in question and >>> perform: >>> >>> update pg_autovacuum set enabled = 'false'; >>> >>> This will prevent autovacuum from vacuuming or analyzing any of >>> the tables in the database, but will still check for XID wraparound. >>> >> >> Problem with that is any time you add a table you'd need to re-run >> that >> command. Wouldn't be so bad if we had DDL triggers, but... >> > > Probably a dumb comment, but can you set a default value for the > enabled column? As Tom pointed out, that wouldn't help because rows aren't automatically added to pg_autovacuum. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Mar 31, 2006, at 9:29 PM, Tom Lane wrote: > "Matthew T. O'Connor" <matthew@zeut.net> writes: >> I think the closest approximation of disabling autovacuum on a per >> database basis is to connect to the database in question and perform: >> update pg_autovacuum set enabled = 'false'; > > Not really gonna help unless you insert a row into pg_autovacuum for > each table in the database. True. > As I just commented in another reply, I don't actually believe in the > value of disabling autovac entirely --- it should at least be able to > fire when you are risking XID wraparound. What could make sense is to > push the thresholds up to very large values, such that autovac won't > fire until you've forgotten manual vacuums for a very long time. > And that you can already do on a per-database basis, using ALTER > DATABASE SET. (Or at least, it *should* work to do that; if the > autovac > process fails to absorb per-db values for its GUC variables, then we > ought to fix it. I'm too lazy to test it right now...) The problem with that is unless I missed a change in 8.1, autovac knows absolutely nothing about when manual vacuums have been run. To do that I'm pretty sure we'd need a catalog table that captured the statistic counts on each table when vacuum ran (and ideally an XID and a timestamp, too). If we end up with some kind of dirty page bitmap that might remove the need for that. Even if tweaking the thresholds did work you still can't do it at a per-database level. It doesn't seem unreasonable to support different autovac settings at the database level. -- Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Apr 2, 2006, at 11:39 PM, Tom Lane wrote: >> I don't think we will see much people losing data to Xid wraparound >> anymore, now that the server refuses to work when the wraparound is >> imminent. > > Well, how about the second or third time we get sued by someone whose > "must be up 24x7" database shuts down for lack of proper vacuuming? > I do think autovac is the wave of the future. The only reason it's > disablable now is that we don't think we've got all the bugs out. Hopefully the could not sue if it is on by default but they turned it off. But who knows :). My reason for wanting to turn it off is that every time it would run the fans on my development system would rev up. Very annoying in a quiet environment where you are working on throw away tests that don't need to eat CPU for vacuuming. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Jim Nasby <jnasby@pervasive.com> writes: > The problem with that is unless I missed a change in 8.1, autovac > knows absolutely nothing about when manual vacuums have been run. It knows perfectly well about full-database vacuums (because those set pg_database.datvacuumxid), and by the end of the week or so it'll know about single-table vacuums too: http://archives.postgresql.org/pgsql-patches/2006-04/msg00009.php regards, tom lane
John DeSoi <desoi@pgedit.com> writes:
> My reason for wanting to turn it off is that every time it would run
> the fans on my development system would rev up.
Well, I have an equally lame reason for wanting to not run autovac:
it'll interfere with profiling because the autovac daemon will drop a
gmon.out file once a minute or so, and maybe overwrite a profiling run's
results before you can print them out.
But AFAICS both of these wishes can be served pretty well just by
increasing autovacuum_naptime to some large value, once a day maybe.
They don't strike me as compelling reasons why autovac should be turned
off entirely.
regards, tom lane