Re: [HACKERS] PG_DUMP and table locking in PG7.4
Yann Michel wrote: > ... I guiess that the AUTOVACUUM switch only does an automated VACUUM > but no VACUUM FULL? Certainly. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi, On Wed, Nov 16, 2005 at 10:07:24AM -0500, Tom Lane wrote: > Yann Michel <[EMAIL PROTECTED]> writes: > > Well, now that I'm thinking about, what you've written I think this is > > exactly the point. I think, that there is a VACUUM waiting for the dump > > to finish whereas the INSERTS are waiting for the VACUUM to finish. > > Only if it's a VACUUM FULL ... plain VACUUM neither blocks INSERT nor is > blocked by pg_dump. > > The short answer may be "don't use VACUUM FULL" (at least not for > routine automatic vacuums). ... I guiess that the AUTOVACUUM switch only does an automated VACUUM but no VACUUM FULL? Cheers, Yann ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi Martijn, On Wed, Nov 16, 2005 at 09:47:33AM +0100, Martijn van Oosterhout wrote: > > Ah yes, PostgreSQL doesn't use 2PL, it uses MVCC. quick overview here: > http://linuxgazette.net/issue68/mitchell.html THX! That was interesting! > > Thanks. BTW: Is there anything about locks and their meaning inside of > > the Docs? If not, wouldn't that be nice? > > Check here: > http://www.postgresql.org/docs/8.0/interactive/mvcc.html > > Under "Explicit Locking" it lists all the locks and what they're for. Yes, thanks once more! Cheers, Yann ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Yann Michel <[EMAIL PROTECTED]> writes: > Well, now that I'm thinking about, what you've written I think this is > exactly the point. I think, that there is a VACUUM waiting for the dump > to finish whereas the INSERTS are waiting for the VACUUM to finish. Only if it's a VACUUM FULL ... plain VACUUM neither blocks INSERT nor is blocked by pg_dump. The short answer may be "don't use VACUUM FULL" (at least not for routine automatic vacuums). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG_DUMP and table locking in PG7.4
> I'm asking, because we have a bigger datawarehouse and dump the data for > a backup every night. Unfortunately, the backup now takes realy long. You may want to consider upgrading and using PITR backups instead. They can be much faster to both backup and to restore if something goes wrong. -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PG_DUMP and table locking in PG7.4
On Wed, Nov 16, 2005 at 09:27:55AM +0100, Yann Michel wrote: > On Wed, Nov 16, 2005 at 08:28:31AM +0100, Martijn van Oosterhout wrote: > > On Wed, Nov 16, 2005 at 08:09:31AM +0100, Yann Michel wrote: > > > Well, thanks for all the answers. Are the locks then released once they > > > are not needed any more like in 2PC? > > > > 2PC doesn't release any locks, it can't to maintain integrity. > > Aehm. sorry I meant 2PL ... all this accronyms... ;-) > The normal 2PL releases the locks once they are not needed anymore but > can not aquire new ones. Strict 2PL releases them all at one point. Ah yes, PostgreSQL doesn't use 2PL, it uses MVCC. quick overview here: http://linuxgazette.net/issue68/mitchell.html > Thanks. BTW: Is there anything about locks and their meaning inside of > the Docs? If not, wouldn't that be nice? Check here: http://www.postgresql.org/docs/8.0/interactive/mvcc.html Under "Explicit Locking" it lists all the locks and what they're for. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpHY4pPDVZvH.pgp Description: PGP signature
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi, first of all, thanks to all, that replied! On Wed, Nov 16, 2005 at 08:28:31AM +0100, Martijn van Oosterhout wrote: > On Wed, Nov 16, 2005 at 08:09:31AM +0100, Yann Michel wrote: > > Well, thanks for all the answers. Are the locks then released once they > > are not needed any more like in 2PC? > > 2PC doesn't release any locks, it can't to maintain integrity. Aehm. sorry I meant 2PL ... all this accronyms... ;-) The normal 2PL releases the locks once they are not needed anymore but can not aquire new ones. Strict 2PL releases them all at one point. > > That should still leaqve the taken snapshot of the released table in a > > consistent state but might enable other transactions to work on that one > > table once it is released. > > ACCESS SHARE means what it says, it stops the table being VACUUMed and > a few other things, but doesn't block INSERTs, UPDATEs or DELETEs. Thanks. BTW: Is there anything about locks and their meaning inside of the Docs? If not, wouldn't that be nice? > pg_dump doesn't blocks inserts, so your problem must be somewhere > else... Are you running VACUUM anywhere. It's possible that pg_dump is > blocking VACUUM which blocks your inserts... Well, now that I'm thinking about, what you've written I think this is exactly the point. I think, that there is a VACUUM waiting for the dump to finish whereas the INSERTS are waiting for the VACUUM to finish. Thannks! Cheers, Yann ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Yann Michel wrote: Hi, On Wed, Nov 16, 2005 at 01:25:43PM +0800, Christopher Kings-Lynne wrote: I belive a lock is acquired on every table including inherited children BEFORE doing ANY dumping. To allow pg_dump to get a consistent dump snapshot. Well, thanks for all the answers. Are the locks then released once they are not needed any more like in 2PC? That should still leaqve the taken snapshot of the released table in a consistent state but might enable other transactions to work on that one table once it is released. I'm asking, because we have a bigger datawarehouse and dump the data for a backup every night. Unfortunately, the backup now takes realy long. That means, other processes that insert data will have to wait which is sometime really long! I was searching for a way to avoid this. No, a share lock on the table does not mean that other transactions can't insert or update anymore. What it does, is to prevent tables from being dropped or truncated. To get a consistent snapshot of the data in the tables itself, pg_dump just uses the SERIALIZABLE transaction isolation level. This is implemented via MVCC in postgresql, which makes sure that neither readers nor writers will block waiting. So only if you do full table locks in your application (using LOCK TABLE statements), you will suffer from pg_dump backups. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG_DUMP and table locking in PG7.4
On Wed, Nov 16, 2005 at 08:09:31AM +0100, Yann Michel wrote: > Well, thanks for all the answers. Are the locks then released once they > are not needed any more like in 2PC? 2PC doesn't release any locks, it can't to maintain integrity. > That should still leaqve the taken snapshot of the released table in a > consistent state but might enable other transactions to work on that one > table once it is released. ACCESS SHARE means what it says, it stops the table being VACUUMed and a few other things, but doesn't block INSERTs, UPDATEs or DELETEs. > I'm asking, because we have a bigger datawarehouse and dump the data for > a backup every night. Unfortunately, the backup now takes realy long. > That means, other processes that insert data will have to wait which is > sometime really long! I was searching for a way to avoid this. I thought > besides the query-speedub we could also gain some benefit for the backup > timing... but it sounds, that this will not automatically help me with > that. :-( pg_dump doesn't blocks inserts, so your problem must be somewhere else... Are you running VACUUM anywhere. It's possible that pg_dump is blocking VACUUM which blocks your inserts... Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpoVpmPtroJM.pgp Description: PGP signature
Re: [HACKERS] PG_DUMP and table locking in PG7.4
I'm asking, because we have a bigger datawarehouse and dump the data for a backup every night. Unfortunately, the backup now takes realy long. That means, other processes that insert data will have to wait which is sometime really long! I was searching for a way to avoid this. I thought besides the query-speedub we could also gain some benefit for the backup timing... but it sounds, that this will not automatically help me with that. :-( No, all the tables are locked in ACCESS SHARE mode. All that means is that you cannot alter the schema of the tables in any way. Chris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi, On Wed, Nov 16, 2005 at 01:25:43PM +0800, Christopher Kings-Lynne wrote: > I belive a lock is acquired on every table including inherited children > BEFORE doing ANY dumping. To allow pg_dump to get a consistent dump > snapshot. Well, thanks for all the answers. Are the locks then released once they are not needed any more like in 2PC? That should still leaqve the taken snapshot of the released table in a consistent state but might enable other transactions to work on that one table once it is released. I'm asking, because we have a bigger datawarehouse and dump the data for a backup every night. Unfortunately, the backup now takes realy long. That means, other processes that insert data will have to wait which is sometime really long! I was searching for a way to avoid this. I thought besides the query-speedub we could also gain some benefit for the backup timing... but it sounds, that this will not automatically help me with that. :-( Cheers, Yann ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PG_DUMP and table locking in PG7.4
I belive a lock is acquired on every table including inherited children BEFORE doing ANY dumping. To allow pg_dump to get a consistent dump snapshot. Chris Yann Michel wrote: Hi, On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote: It acquires share locks on EVERY table. do you mean on EVERY inherited table once one of them is dumped? Or do you mean that a share lock is requested(and hold) on each of them once one is dumped, i.e., sequentially? Thanks, Yann ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PG_DUMP and table locking in PG7.4
On 2005-11-16, Yann Michel <[EMAIL PROTECTED]> wrote: > Hi, > > On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote: >> It acquires share locks on EVERY table. > > do you mean on EVERY inherited table once one of them is dumped? Or do > you mean that a share lock is requested(and hold) on each of them once > one is dumped, i.e., sequentially? pg_dump obtains an ACCESS SHARE lock on _every_ table it dumps, including the inherited child tables individually, _before_ it starts dumping anything. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi, On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote: > It acquires share locks on EVERY table. do you mean on EVERY inherited table once one of them is dumped? Or do you mean that a share lock is requested(and hold) on each of them once one is dumped, i.e., sequentially? Thanks, Yann ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PG_DUMP and table locking in PG7.4
It acquires share locks on EVERY table. Yann Michel wrote: Hi all, On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote: I'm using PG_DUMP for backing up a postgres 7.4 database. As I have seen, the pg_dump aquires a table lock while dump the table's content. What will happen, if I have a basic table and several inherited tables. Will the PG_DUMP tool only aquire locks on the inherited tables or on the parent-table, too? Is anyone out there who could answer this question or even give me a hint about where to read about this if there is any writen things about that? Thanks! Yann ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Yann Michel wrote: > Hi all, > > On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote: > > > > I'm using PG_DUMP for backing up a postgres 7.4 database. As I have > > seen, the pg_dump aquires a table lock while dump the table's content. > > What will happen, if I have a basic table and several inherited tables. > > Will the PG_DUMP tool only aquire locks on the inherited tables or on > > the parent-table, too? > > Is anyone out there who could answer this question or even give me a > hint about where to read about this if there is any writen things about > that? You could find out exactly which commands it sends by setting log_statement=all in a test environment and then dumping things to your heart's content. Looking at that output you should be able to easily determine which tables are locked. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi all, On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote: > > I'm using PG_DUMP for backing up a postgres 7.4 database. As I have > seen, the pg_dump aquires a table lock while dump the table's content. > What will happen, if I have a basic table and several inherited tables. > Will the PG_DUMP tool only aquire locks on the inherited tables or on > the parent-table, too? Is anyone out there who could answer this question or even give me a hint about where to read about this if there is any writen things about that? Thanks! Yann ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] PG_DUMP and table locking in PG7.4
Hi, I'm using PG_DUMP for backing up a postgres 7.4 database. As I have seen, the pg_dump aquires a table lock while dump the table's content. What will happen, if I have a basic table and several inherited tables. Will the PG_DUMP tool only aquire locks on the inherited tables or on the parent-table, too? Thanks in advance! Cheers, Yann ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match