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

In 7.2, VACUUM will not require an exclusive lock.

regards, tom lane

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

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

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

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

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

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

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.

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

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

"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

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

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

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.

If I understood what VACUUM light does, I do not think that people
will need to actually do the conventional VACUUM as often anymore.
I understood, that VACUUM light makes outdated tuple heap space available
for reuse, and removes the corresponding index entries.
It does not make space available to other tables or the OS,
but most other DB's do not do that eighter.
The conventional VACUUM would then be something you do as part of a DB
reorganization (maybe once every month or so).

Andreas

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.

If I understood what VACUUM light does, I do not think that people
will need to actually do the conventional VACUUM as often anymore.
I understood, that VACUUM light makes outdated tuple heap space available
for reuse, and removes the corresponding index entries.
It does not make space available to other tables or the OS,
but most other DB's do not do that eighter.
The conventional VACUUM would then be something you do as part of a DB
reorganization (maybe once every month or so).

Yes, but in other DB's if you UPDATE all rows in the table, you don't
double the disk space. They also reuse DELETEd space automatically.

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

The conventional VACUUM would then be something you do as part of a DB
reorganization (maybe once every month or so).

Yes, but in other DB's if you UPDATE all rows in the table, you don't
double the disk space.

Sure, but what is wrong with keeping the space allocated for the next
"UPDATE all rows", if that is something the application needs to do frequently ?
PostgreSQL needs more space on disc, but we knew that already :-)

Andreas

The conventional VACUUM would then be something you do as part of a DB
reorganization (maybe once every month or so).

Yes, but in other DB's if you UPDATE all rows in the table, you don't
double the disk space.

Sure, but what is wrong with keeping the space allocated for
the next "UPDATE all rows", if that is something the application
needs to do frequently ? PostgreSQL needs more space on disc,
but we knew that already :-)

In many cases, a VACUUM will not have been run before more space is
needed in the table so you will get ever-increasing sizes until a full
VACUUM. Only in an optimial light VACUUM state would a table that gets
continually updated _not_ continue to grow.

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