Thread: pg_autovacuum (pg_avd version 2)
I have updated my pg_autovacuum program (formerly pg_avd, the name changed as per discussion on the patches list). This version should be a good bit better. It addresses all the issues pointed out by Neil Conway. Vacuum and Analyze are now handled separately. It now monitors for xid wraparound. The number of database connections and queries has been significantly reduced compared the previous version. I have moved it from bin to contrib. More detail on the changes are in the TODO file. I have not tested the xid wraparound code as I have to let my AthlonXP 1600 run select 1 in a tight loop for approx. two days in order to perform the required 500,000,000 xacts. Side note: in trying to test the xid wrap around code I noted that the select 1 query is nearly 30% slower with stats_row_level = true. I guess this is another reason for a backend integrated autovacuum that not built on top of the stats system. I know the net effect is reduced with more realistic queries but still seems like a lot. As always, any and all feedback is appreciated. Matthew T. O'Connor
Attachment
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> I have not tested the xid wraparound code as I have to let my AthlonXP
> 1600 run select 1 in a tight loop for approx. two days in order to
> perform the required 500,000,000 xacts.
When I want to test such things, I reach in with a debugger and alter
the transaction counter in shared memory. I don't recall exact details
at the moment, but it's a lot easier than waiting a few days ;-)
regards, tom lane
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Matthew T. O'Connor wrote:
> I have updated my pg_autovacuum program (formerly pg_avd, the name
> changed as per discussion on the patches list).
>
> This version should be a good bit better. It addresses all the issues
> pointed out by Neil Conway. Vacuum and Analyze are now handled
> separately. It now monitors for xid wraparound. The number of database
> connections and queries has been significantly reduced compared the
> previous version. I have moved it from bin to contrib. More detail on
> the changes are in the TODO file.
>
> I have not tested the xid wraparound code as I have to let my AthlonXP
> 1600 run select 1 in a tight loop for approx. two days in order to
> perform the required 500,000,000 xacts.
>
> Side note: in trying to test the xid wrap around code I noted that the
> select 1 query is nearly 30% slower with stats_row_level = true. I
> guess this is another reason for a backend integrated autovacuum that
> not built on top of the stats system. I know the net effect is reduced
> with more realistic queries but still seems like a lot.
>
> As always, any and all feedback is appreciated.
>
> Matthew T. O'Connor
>
>
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Did we not tend in the direction of a server-side solution? Bruce Momjian writes: > Your patch has been added to the PostgreSQL unapplied patches list at: > > http://momjian.postgresql.org/cgi-bin/pgpatches > > I will try to apply it within the next 48 hours. > > --------------------------------------------------------------------------- > > > Matthew T. O'Connor wrote: > > I have updated my pg_autovacuum program (formerly pg_avd, the name > > changed as per discussion on the patches list). > > > > This version should be a good bit better. It addresses all the issues > > pointed out by Neil Conway. Vacuum and Analyze are now handled > > separately. It now monitors for xid wraparound. The number of database > > connections and queries has been significantly reduced compared the > > previous version. I have moved it from bin to contrib. More detail on > > the changes are in the TODO file. > > > > I have not tested the xid wraparound code as I have to let my AthlonXP > > 1600 run select 1 in a tight loop for approx. two days in order to > > perform the required 500,000,000 xacts. > > > > Side note: in trying to test the xid wrap around code I noted that the > > select 1 query is nearly 30% slower with stats_row_level = true. I > > guess this is another reason for a backend integrated autovacuum that > > not built on top of the stats system. I know the net effect is reduced > > with more realistic queries but still seems like a lot. > > > > As always, any and all feedback is appreciated. > > > > Matthew T. O'Connor > > > > > > [ Attachment, skipping... ] > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -- Peter Eisentraut peter_e@gmx.net
We did, but he contacted me directly asking if it could be put in /contrib for 7.4 so it would get some testing and usage. Is that valid? --------------------------------------------------------------------------- Peter Eisentraut wrote: > Did we not tend in the direction of a server-side solution? > > Bruce Momjian writes: > > > Your patch has been added to the PostgreSQL unapplied patches list at: > > > > http://momjian.postgresql.org/cgi-bin/pgpatches > > > > I will try to apply it within the next 48 hours. > > > > --------------------------------------------------------------------------- > > > > > > Matthew T. O'Connor wrote: > > > I have updated my pg_autovacuum program (formerly pg_avd, the name > > > changed as per discussion on the patches list). > > > > > > This version should be a good bit better. It addresses all the issues > > > pointed out by Neil Conway. Vacuum and Analyze are now handled > > > separately. It now monitors for xid wraparound. The number of database > > > connections and queries has been significantly reduced compared the > > > previous version. I have moved it from bin to contrib. More detail on > > > the changes are in the TODO file. > > > > > > I have not tested the xid wraparound code as I have to let my AthlonXP > > > 1600 run select 1 in a tight loop for approx. two days in order to > > > perform the required 500,000,000 xacts. > > > > > > Side note: in trying to test the xid wrap around code I noted that the > > > select 1 query is nearly 30% slower with stats_row_level = true. I > > > guess this is another reason for a backend integrated autovacuum that > > > not built on top of the stats system. I know the net effect is reduced > > > with more realistic queries but still seems like a lot. > > > > > > As always, any and all feedback is appreciated. > > > > > > Matthew T. O'Connor > > > > > > > > > > [ Attachment, skipping... ] > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > -- > Peter Eisentraut peter_e@gmx.net > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Right. I am trying to work on server-side solution but can't promise to complete it by June 1. If I (or someone else) gets a server-side solution done before the 7.4 feature freeze, then I think we should pull this out of /contrib. Otherwise, I find it a useful tool, I have it running in production and have received some positive feedback from others who are using it, so /contrib seems safe enough. On Tue, 2003-03-18 at 17:19, Bruce Momjian wrote: > We did, but he contacted me directly asking if it could be put in > /contrib for 7.4 so it would get some testing and usage. Is that valid? > > --------------------------------------------------------------------------- > > Peter Eisentraut wrote: > > Did we not tend in the direction of a server-side solution? > > > > Bruce Momjian writes: > > > > > Your patch has been added to the PostgreSQL unapplied patches list at: > > > > > > http://momjian.postgresql.org/cgi-bin/pgpatches > > > > > > I will try to apply it within the next 48 hours. > > > > > > --------------------------------------------------------------------------- > > > > > > > > > Matthew T. O'Connor wrote: > > > > I have updated my pg_autovacuum program (formerly pg_avd, the name > > > > changed as per discussion on the patches list). > > > > > > > > This version should be a good bit better. It addresses all the issues > > > > pointed out by Neil Conway. Vacuum and Analyze are now handled > > > > separately. It now monitors for xid wraparound. The number of database > > > > connections and queries has been significantly reduced compared the > > > > previous version. I have moved it from bin to contrib. More detail on > > > > the changes are in the TODO file. > > > > > > > > I have not tested the xid wraparound code as I have to let my AthlonXP > > > > 1600 run select 1 in a tight loop for approx. two days in order to > > > > perform the required 500,000,000 xacts. > > > > > > > > Side note: in trying to test the xid wrap around code I noted that the > > > > select 1 query is nearly 30% slower with stats_row_level = true. I > > > > guess this is another reason for a backend integrated autovacuum that > > > > not built on top of the stats system. I know the net effect is reduced > > > > with more realistic queries but still seems like a lot. > > > > > > > > As always, any and all feedback is appreciated. > > > > > > > > Matthew T. O'Connor > > > > > > > > > > > > > > [ Attachment, skipping... ] > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 2: you can get off all lists at once with the unregister command > > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > > > > > -- > > Peter Eisentraut peter_e@gmx.net > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > >
I can help you with any server-side issues --- it is probably easiest
via chat. My chat addresses are:
AIM bmomjian
ICQ 151255111
Yahoo bmomjian
MSN root@candle.pha.pa.us
IRC bmomjian at #postgresql via EFNet or OpenProjects
---------------------------------------------------------------------------
Matthew T. O'Connor wrote:
> Right. I am trying to work on server-side solution but can't promise to
> complete it by June 1. If I (or someone else) gets a server-side
> solution done before the 7.4 feature freeze, then I think we should pull
> this out of /contrib. Otherwise, I find it a useful tool, I have it
> running in production and have received some positive feedback from
> others who are using it, so /contrib seems safe enough.
>
> On Tue, 2003-03-18 at 17:19, Bruce Momjian wrote:
> > We did, but he contacted me directly asking if it could be put in
> > /contrib for 7.4 so it would get some testing and usage. Is that valid?
> >
> > ---------------------------------------------------------------------------
> >
> > Peter Eisentraut wrote:
> > > Did we not tend in the direction of a server-side solution?
> > >
> > > Bruce Momjian writes:
> > >
> > > > Your patch has been added to the PostgreSQL unapplied patches list at:
> > > >
> > > > http://momjian.postgresql.org/cgi-bin/pgpatches
> > > >
> > > > I will try to apply it within the next 48 hours.
> > > >
> > > > ---------------------------------------------------------------------------
> > > >
> > > >
> > > > Matthew T. O'Connor wrote:
> > > > > I have updated my pg_autovacuum program (formerly pg_avd, the name
> > > > > changed as per discussion on the patches list).
> > > > >
> > > > > This version should be a good bit better. It addresses all the issues
> > > > > pointed out by Neil Conway. Vacuum and Analyze are now handled
> > > > > separately. It now monitors for xid wraparound. The number of database
> > > > > connections and queries has been significantly reduced compared the
> > > > > previous version. I have moved it from bin to contrib. More detail on
> > > > > the changes are in the TODO file.
> > > > >
> > > > > I have not tested the xid wraparound code as I have to let my AthlonXP
> > > > > 1600 run select 1 in a tight loop for approx. two days in order to
> > > > > perform the required 500,000,000 xacts.
> > > > >
> > > > > Side note: in trying to test the xid wrap around code I noted that the
> > > > > select 1 query is nearly 30% slower with stats_row_level = true. I
> > > > > guess this is another reason for a backend integrated autovacuum that
> > > > > not built on top of the stats system. I know the net effect is reduced
> > > > > with more realistic queries but still seems like a lot.
> > > > >
> > > > > As always, any and all feedback is appreciated.
> > > > >
> > > > > Matthew T. O'Connor
> > > > >
> > > > >
> > > >
> > > > [ Attachment, skipping... ]
> > > >
> > > > >
> > > > > ---------------------------(end of broadcast)---------------------------
> > > > > TIP 2: you can get off all lists at once with the unregister command
> > > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > >
> > > >
> > >
> > > --
> > > Peter Eisentraut peter_e@gmx.net
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
>
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Peter Eisentraut wrote:
>> Did we not tend in the direction of a server-side solution?
> We did, but he contacted me directly asking if it could be put in
> /contrib for 7.4 so it would get some testing and usage. Is that valid?
I think a server-side solution is the way to go in the long run, but
since one probably won't be available for 7.4, a contrib module seems
like a reasonable stopgap offering. Contrib is our traditional refuge
for "not ready for prime time" code, no? The fact that it's a client
and doesn't touch server-side code actually works in its favor here
--- there's nothing to rip out after we have a better answer.
regards, tom lane
Added to /contrib. Thanks. --------------------------------------------------------------------------- Matthew T. O'Connor wrote: > I have updated my pg_autovacuum program (formerly pg_avd, the name > changed as per discussion on the patches list). > > This version should be a good bit better. It addresses all the issues > pointed out by Neil Conway. Vacuum and Analyze are now handled > separately. It now monitors for xid wraparound. The number of database > connections and queries has been significantly reduced compared the > previous version. I have moved it from bin to contrib. More detail on > the changes are in the TODO file. > > I have not tested the xid wraparound code as I have to let my AthlonXP > 1600 run select 1 in a tight loop for approx. two days in order to > perform the required 500,000,000 xacts. > > Side note: in trying to test the xid wrap around code I noted that the > select 1 query is nearly 30% slower with stats_row_level = true. I > guess this is another reason for a backend integrated autovacuum that > not built on top of the stats system. I know the net effect is reduced > with more realistic queries but still seems like a lot. > > As always, any and all feedback is appreciated. > > Matthew T. O'Connor > > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073