Thread: What kind of locks does vacuum process hold on the db?
What kind of locks does it hold on the db? Table level / Row level /
AccessExclusiveLock ?
Is there a document that details vacuum process, and its internals?
Use case: I am doing a vacuum {all full analyze} on a postgres 8.1 hosting
two user databases. For this I am using ${pg-home}/bin/vacuumdb, not direct
SQLs. Parallel to this I am running transaction on both user-DBs.
Observation:
Transactions don't pause while vacuum is running. (Odd, how come?)
http://www.postgresql.org/docs/8.1/interactive/sql-vacuum.html
"Plain VACUUM (without FULL) simply reclaims space and makes it available for
re-use. This form of the command can operate in parallel with normal reading
and writing of the table, as an exclusive lock is not obtained. VACUUM FULL
does more extensive processing, including moving of tuples across blocks to
try to compact the table to the minimum number of disk blocks. This form is
much slower and requires an exclusive lock on each table while it is being
processed."
After this I tried two vacuums in parallel but those lock each other.
On 8/29/07, Nitin Verma <nitinverma@azulsystems.com> wrote:
> What kind of locks does it hold on the db? Table level / Row level /
> AccessExclusiveLock ?
> Is there a document that details vacuum process, and its internals?
>
>
> Use case: I am doing a vacuum {all full analyze} on a postgres 8.1 hosting
> two user databases. For this I am using ${pg-home}/bin/vacuumdb, not direct
> SQLs. Parallel to this I am running transaction on both user-DBs.
> Observation:
> Transactions don't pause while vacuum is running. (Odd, how come?)
Why are you doing FULL vacuums? Is there some problem that regular
vacuums aren't solving?
> After this I tried two vacuums in parallel but those lock each other.
Yes, vacuum full takes a hard lock on a table.
Vacuum full is to be avoided.
> Why are you doing FULL vacuums? Is there some problem that regular vacuums
aren't solving?
Using dump/restore from a live DB to fresh DB, I get a DB that takes (49M +
12M - {I have two table spaces)) 61M of disk. Maximum size that I can grow by
the quota allocated to DB is 100M.
A regular vacuum doesn't stop the database growth, and DB grows beyond 100M.
Then we have to trigger a script that dump/restores on the live database. For
that we have a small outage (which is right now automated).
A full vacuum keeps the database below 100M and no outage.
> Yes, vacuum full takes a hard lock on a table.
That means Table Level AccessExclusiveLock, right?
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Thursday, August 30, 2007 10:29 AM
To: Nitin Verma
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] What kind of locks does vacuum process hold on the db?
On 8/29/07, Nitin Verma <nitinverma@azulsystems.com> wrote:
> What kind of locks does it hold on the db? Table level / Row level /
> AccessExclusiveLock ?
> Is there a document that details vacuum process, and its internals?
>
>
> Use case: I am doing a vacuum {all full analyze} on a postgres 8.1 hosting
> two user databases. For this I am using ${pg-home}/bin/vacuumdb, not direct
> SQLs. Parallel to this I am running transaction on both user-DBs.
> Observation:
> Transactions don't pause while vacuum is running. (Odd, how come?)
Why are you doing FULL vacuums? Is there some problem that regular
vacuums aren't solving?
> After this I tried two vacuums in parallel but those lock each other.
Yes, vacuum full takes a hard lock on a table.
Vacuum full is to be avoided.
On 8/30/07, Nitin Verma <nitinverma@azulsystems.com> wrote:
> > Why are you doing FULL vacuums? Is there some problem that regular vacuums
> aren't solving?
>
> Using dump/restore from a live DB to fresh DB, I get a DB that takes (49M +
> 12M - {I have two table spaces)) 61M of disk. Maximum size that I can grow by
> the quota allocated to DB is 100M.
>
> A regular vacuum doesn't stop the database growth, and DB grows beyond 100M.
> Then we have to trigger a script that dump/restores on the live database. For
> that we have a small outage (which is right now automated).
>
> A full vacuum keeps the database below 100M and no outage.
Then you aren't doing regular vacuum often enough and / or don't have
high enough fsm settings.
>
> > Yes, vacuum full takes a hard lock on a table.
>
> That means Table Level AccessExclusiveLock, right?
Not sure which name it is. It definitely blocks writes to the table
while it is vacuuming it. But that's secondary. You're having to do
regular vacuum fulls because of too infrequent regular vacuum and / or
too low fsm setting.
>> Then you aren't doing regular vacuum often enough and / or don't have high enough fsm settings. Right now it's just default, can you please point me to a document that elaborates on calculation of FSM for a given load (or to say averaged load) I found ( http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp ) but does not have details. # - Free Space Map - #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~70 bytes each -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Thursday, August 30, 2007 4:07 PM To: Nitin Verma Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] What kind of locks does vacuum process hold on the db? On 8/30/07, Nitin Verma <nitinverma@azulsystems.com> wrote: > > Why are you doing FULL vacuums? Is there some problem that regular vacuums > aren't solving? > > Using dump/restore from a live DB to fresh DB, I get a DB that takes (49M + > 12M - {I have two table spaces)) 61M of disk. Maximum size that I can grow by > the quota allocated to DB is 100M. > > A regular vacuum doesn't stop the database growth, and DB grows beyond 100M. > Then we have to trigger a script that dump/restores on the live database. For > that we have a small outage (which is right now automated). > > A full vacuum keeps the database below 100M and no outage. Then you aren't doing regular vacuum often enough and / or don't have high enough fsm settings. > > > Yes, vacuum full takes a hard lock on a table. > > That means Table Level AccessExclusiveLock, right? Not sure which name it is. It definitely blocks writes to the table while it is vacuuming it. But that's secondary. You're having to do regular vacuum fulls because of too infrequent regular vacuum and / or too low fsm setting.
On 8/30/07, Nitin Verma <nitinverma@azulsystems.com> wrote: > > >> Then you aren't doing regular vacuum often enough and / or don't have high > enough fsm settings. > > Right now it's just default, can you please point me to a document that > elaborates on calculation of FSM for a given load (or to say averaged load) Run a full vacuum / reindex to reclaim the lost space. Set up the autovacuum daemon to run. After a day or so, vacuum all your databases, and on the last one do a vacuum verbose. At the end you'll have a few lines like this: DETAIL: A total of 9612 page slots are in use (including overhead). 9612 page slots are required to track all free space. Current limits are: 153600 page slots, 1000 relations, using 965 kB. VACUUM So, I'm only using about 10,000 page slots out of a maximum of 153,600 slots. If the number of pages slots requires exceeds your current limits then you'll need to raise them. If not, then autovacuum is doing its job.