pg_autovacuum (pg_avd version 2)

"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.

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