Thread: Vacuum and Transactions
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. *****************************************************************************
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
> 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
> 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