Hi all,

I am having the following problems using Postgres 7.1.2 with clients which
have long transactions:

If there is a transaction running when 'vacuumdb -a -z' is run (as a cron
job) it stops running at that database till the transaction completes.  That
is not so much of a problem until a new client tries to connect to the
database.  This new connection hangs, waiting for the vacuum to complete.
This situation is not all that helpful and means I have to be careful at
what time I run vacuum so it does not interfere with new clients.  Is this a
bug or the standard way in which postgres works and are there any plans
change this?

Regards

Ben


*****************************************************************************
This email and any attachments transmitted with it are confidential
and intended solely for the use of the individual or entity to whom
they are addressed. If you have received this email in error please
notify the sender and do not store, copy or disclose the content
to any other person.

It is the responsibility of the recipient to ensure that opening this
message and/or any of its attachments will not adversely affect
its systems. No responsibility is accepted by the Company.
*****************************************************************************

Fri, 6 Jul 2001 15:25:49 +0100 
"Trewern, Ben" <Ben.Trewern@mowlem.com>
Vacuum and Transactions
Trewern, Ben writes:

> If there is a transaction running when 'vacuumdb -a -z' is run (as a cron
> job) it stops running at that database till the transaction completes.  That
> is not so much of a problem until a new client tries to connect to the
> database.  This new connection hangs, waiting for the vacuum to complete.

There are plans to make vacuum less intrusive in the next major release,
but until then this is what you have to deal with.  Unless you really need
to run vacuum all the time you should schedule it for low activity times.
Yes, that means 24/7 100% uptime is not *really* feasible with PostgreSQL.

> This email and any attachments transmitted with it are confidential

If the email is confidential you shouldn't send it to public mailing
lists.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Fri, 6 Jul 2001 17:45:17 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Vacuum and Transactions
In 7.2, VACUUM will not require an exclusive lock.

			regards, tom lane

Fri, 06 Jul 2001 12:02:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Vacuum and Transactions 
From: "Trewern, Ben" <Ben.Trewern@mowlem.com>

> If there is a transaction running when 'vacuumdb -a -z' is run (as a cron
> job) it stops running at that database till the transaction completes.
That
> is not so much of a problem until a new client tries to connect to the
> database.  This new connection hangs, waiting for the vacuum to complete.
> This situation is not all that helpful and means I have to be careful at
> what time I run vacuum so it does not interfere with new clients.  Is this
a
> bug or the standard way in which postgres works and are there any plans
> change this?

Would vacuuming the tables one at a time not help here? It'd mean a small
script to read a list of databases/tables out of PG but should reduce the
impact on your clients (if I'm thinking straight here)

- Richard Huxton


Fri, 6 Jul 2001 17:42:23 +0100
"Richard Huxton" <dev@archonet.com>
Re: Vacuum and Transactions
> In 7.2, VACUUM will not require an exclusive lock.

Care to elaborate on that?  How are you going to do it?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 6 Jul 2001 14:42:39 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Vacuum and Transactions
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> In 7.2, VACUUM will not require an exclusive lock.

> Care to elaborate on that?  How are you going to do it?

Uh, have you not been paying attention to pg-hackers for the
last two months?

I am assuming here that concurrent VACUUM will become the default
kind of vacuum, and the old style will be invoked by some other
syntax (VACUUM FULL ..., maybe).

			regards, tom lane

Fri, 06 Jul 2001 14:45:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Vacuum and Transactions 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> In 7.2, VACUUM will not require an exclusive lock.
> 
> > Care to elaborate on that?  How are you going to do it?
> 
> Uh, have you not been paying attention to pg-hackers for the
> last two months?
> 
> I am assuming here that concurrent VACUUM will become the default
> kind of vacuum, and the old style will be invoked by some other
> syntax (VACUUM FULL ..., maybe).

By concurrent vacuum, do you mean the auto-vacuum you are doing?  I
realize that will not need a lock.  Are you changing default VACUUM so
it only moves rows inside existing blocks too?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 6 Jul 2001 14:49:37 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Vacuum and Transactions
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Are you changing default VACUUM

Only to the extent of not being the default.

			regards, tom lane

Fri, 06 Jul 2001 14:50:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Vacuum and Transactions 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> In 7.2, VACUUM will not require an exclusive lock.
> 
> > Care to elaborate on that?  How are you going to do it?
> 
> Uh, have you not been paying attention to pg-hackers for the
> last two months?
> 
> I am assuming here that concurrent VACUUM will become the default
> kind of vacuum, and the old style will be invoked by some other
> syntax (VACUUM FULL ..., maybe).

OK, I just talked to Tom on the phone and here is his idea for 7.2.  He
says he already posted this, but I missed it.

His idea is that in 7.2 VACUUM will only move rows within pages.  It
will also store unused space locations into shared memory to be used by
backends needing to add rows to tables.  Actual disk space compaction
will be performed by new a VACUUM FULL(?) command.

The default VACUUM will not lock the table but only prevent the table
from being dropped.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 6 Jul 2001 17:59:46 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] Vacuum and Transactions
At 05:59 PM 7/6/01 -0400, Bruce Momjian wrote:
>
>OK, I just talked to Tom on the phone and here is his idea for 7.2.  He
>says he already posted this, but I missed it.
>
>His idea is that in 7.2 VACUUM will only move rows within pages.  It
>will also store unused space locations into shared memory to be used by
>backends needing to add rows to tables.  Actual disk space compaction
>will be performed by new a VACUUM FULL(?) command.
>
>The default VACUUM will not lock the table but only prevent the table
>from being dropped.

Would 7.2 maintain performance when updating a row repeatedly (update,
commit)? Right now performance goes down in a somewhat 1/x manner. It's
still performs ok but it's nice to have things stay blazingly fast.

If not will the new vacuum restore the performance? 

Or will we have to use the VACUUM FULL?

Thanks,
Link.


Sat, 07 Jul 2001 16:17:57 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: [GENERAL] Vacuum and Transactions
Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Would 7.2 maintain performance when updating a row repeatedly (update,
> commit)?

You'll still need to VACUUM to get rid of the obsoleted versions of the
row.  The point of the planned 7.2 changes is to make VACUUM cheap and
nonintrusive enough so that you can run it frequently on tables that are
seeing continual updates.

			regards, tom lane

Sat, 07 Jul 2001 15:15:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: [GENERAL] Vacuum and Transactions 
> You'll still need to VACUUM to get rid of the obsoleted versions of the
> row.  The point of the planned 7.2 changes is to make VACUUM cheap and
> nonintrusive enough so that you can run it frequently on tables that are
> seeing continual updates.

If it becomes non-intrusive, then why not have PostgreSQL run VACUUM
automatically when certain conditions (user-configurable, load, changes per
table, etc.) are met.

All the sys admin would need to do is put the VACCUUM FULL in a cron job.

Chris


Mon, 9 Jul 2001 09:29:43 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Re: [GENERAL] Vacuum and Transactions 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> If it becomes non-intrusive, then why not have PostgreSQL run VACUUM
> automatically

That might happen eventually, but I'm not all that eager to convert
the postmaster into a (half-baked) substitute for cron.  My experience
as a dbadmin is that you need various sorts of routinely-run maintenance
tasks anyway; VACUUM is only one of them.  So you're gonna need some
cron tasks no matter what.  If we try to make the postmaster responsible
for this sort of thing, we're going to end up reimplementing cron.
I think that's a waste of effort.

			regards, tom lane

Sun, 08 Jul 2001 21:46:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: [GENERAL] Vacuum and Transactions 
> That might happen eventually, but I'm not all that eager to convert
> the postmaster into a (half-baked) substitute for cron.  My experience
> as a dbadmin is that you need various sorts of routinely-run maintenance
> tasks anyway; VACUUM is only one of them.  So you're gonna need some
> cron tasks no matter what.  If we try to make the postmaster responsible
> for this sort of thing, we're going to end up reimplementing cron.
> I think that's a waste of effort.

Except that you can only set cron jobs to run every hour, etc.  The DBA
might want to set it to run after say 5% of the rows in a table are
updated/deleted, etc.  It is an esoteric feature, I know, but it'd be cool.

Chris


Mon, 9 Jul 2001 09:46:40 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Re: [GENERAL] Vacuum and Transactions 
> > That might happen eventually, but I'm not all that eager to convert
> > the postmaster into a (half-baked) substitute for cron.  My experience
> > as a dbadmin is that you need various sorts of routinely-run maintenance
> > tasks anyway; VACUUM is only one of them.  So you're gonna need some
> > cron tasks no matter what.  If we try to make the postmaster responsible
> > for this sort of thing, we're going to end up reimplementing cron.
> > I think that's a waste of effort.
> 
> Except that you can only set cron jobs to run every hour, etc.  The DBA
> might want to set it to run after say 5% of the rows in a table are
> updated/deleted, etc.  It is an esoteric feature, I know, but it'd be cool.

I don't think it is esoteric.  If I UPDATE all the rows in a table,
COMMIT, and all transactions viewing old versions of my table are gone,
it would be nice for VACUUM-light to come alone and gather up my free
tuple space for later use.  

Only the database knows when this has happened, not cron.

I also think we have to leave VACUUM alone and come up with a new name
for our light VACUUM.  That way, people who do VACUUM at night when no
one is on the system can keep doing that, and just add something to run
light vacuum periodically during the day.  I also believe eventually we
will remove VACUUM-light and come up with some automatic solution.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 12 Jul 2001 15:53:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: [GENERAL] Vacuum and Transactions