Re: [PERFORM] Temporary table retains old contents on update eventually causing slow temp file usage.
Sorry for replying to my own post. I forgot to include my version information, I used: PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc. build 5026) and PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1 20050727 (Red Hat 4.0.1-5) On both the same result happens. Cheers, Rusty -- Rusty Conover InfoGears Inc. http://www.infogears.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Temporary table retains old contents on update eventually
On Tue, 18 Jul 2006, Rusty Conover wrote: Hi, It would seem that doing any changes on a temp table forces a copy of the entire contents of the table to be retained in memory/disk. Is this happening due to MVCC? Is there a way to change this behavior? It could be very useful when you have really huge temp tables that need to be updated a few times before they can be dropped. This is caused by our MVCC implementation. It cannot be easily changed. We rely on MVCC for two things: concurrency and rolling back of aborted commands. Without the latter, we couldn't support the following trivially: template1=# create temp table bar (i int); CREATE TABLE template1=# begin; BEGIN template1=# insert into bar values(1); INSERT 0 1 template1=# abort; ROLLBACK template1=# select * from bar; i --- (0 rows) It would be nice if we could special case temp tables because of the fact that concurrency does not come into the equation but I cannot see it happening without a generalised overwriting MVCC system. The only alternative in the mean time is to vacuum your temporary table(s) as part of your interaction with them. Thanks, Gavin ---(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: [PERFORM] Temporary table retains old contents on update eventually causing slow temp file usage.
On Jul 18, 2006, at 6:22 AM, Gavin Sherry wrote: On Tue, 18 Jul 2006, Rusty Conover wrote: Hi, It would seem that doing any changes on a temp table forces a copy of the entire contents of the table to be retained in memory/disk. Is this happening due to MVCC? Is there a way to change this behavior? It could be very useful when you have really huge temp tables that need to be updated a few times before they can be dropped. This is caused by our MVCC implementation. It cannot be easily changed. We rely on MVCC for two things: concurrency and rolling back of aborted commands. Without the latter, we couldn't support the following trivially: template1=# create temp table bar (i int); CREATE TABLE template1=# begin; BEGIN template1=# insert into bar values(1); INSERT 0 1 template1=# abort; ROLLBACK template1=# select * from bar; i --- (0 rows) It would be nice if we could special case temp tables because of the fact that concurrency does not come into the equation but I cannot see it happening without a generalised overwriting MVCC system. The only alternative in the mean time is to vacuum your temporary table(s) as part of your interaction with them. I forgot to add in my original post that the temporary tables I'm dealing with have the on commit drop flag, so really persisting beyond the transaction isn't needed. But I don't think that makes any difference, because of savepoints' required functionality. The problem with vacuuming is that you can't do it by default right now inside of a transaction. Reading vacuum.c though, it leaves the door open: /* * We cannot run VACUUM inside a user transaction block; if we were inside * a transaction, then our commit- and start-transaction-command calls * would not have the intended effect! Furthermore, the forced commit that * occurs before truncating the relation's file would have the effect of * committing the rest of the user's transaction too, which would * certainly not be the desired behavior. (This only applies to VACUUM * FULL, though. We could in theory run lazy VACUUM inside a transaction * block, but we choose to disallow that case because we'd rather commit * as soon as possible after finishing the vacuum. This is mainly so that * we can let go the AccessExclusiveLock that we may be holding.) * * ANALYZE (without VACUUM) can run either way. */ Since we're dealing with a temporary table we shouldn't have any problems with the AccessExclusiveLock. Would lazy vacuuming mark the pages as free? I assume it wouldn't release them or shrink the size of the relation, but could they be reused on later updates in that same transaction? Cheers, Rusty -- Rusty Conover InfoGears Inc. Web: http://www.infogears.com ---(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: [PERFORM] Temporary table
Franklin Haut [EMAIL PROTECTED] writes: How to reproduce : CREATE TEMP TABLE cademp ( codemp INTEGER, codfil INTEGER, nomemp varchar(50) ) ON COMMIT DROP; INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); Select * from cademp; You need a BEGIN/COMMIT around that, or else rethink using ON COMMIT DROP. As is, the temp table goes away instantly when the CREATE commits. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Temporary table
Franklin Haut wrote: Hello, I´m have some problems with a temporary table, i need create a table, insert some values, make a select and at end of transaction the table must droped, but after i created a table there not more exist, is this normal ? How to reproduce : CREATE TEMP TABLE cademp ( codemp INTEGER, codfil INTEGER, nomemp varchar(50) ) ON COMMIT DROP; INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); Select * from cademp; In this case, the table cademp doesn´t exist at the first insert, in the same transaction. It is NOT the same transaction. By default, each STATEMENT is it's own transaction. Stick a BEGIN; before the create table, and a commit; after the select. Larry Rosenman Tks, Franklin ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(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: [PERFORM] Temporary Table
Christian Paul B. Cosinas wrote: Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? The system will drop it automatically, so it shouldn't affect. What _could_ be affecting you if you execute that function a lot, is accumulated bloat in pg_class, pg_attribute, or other system catalogs. You may want to make sure these are vacuumed often. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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: [PERFORM] Temporary Table
Alvaro Herrera wrote: Christian Paul B. Cosinas wrote: Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? The system will drop it automatically, so it shouldn't affect. What _could_ be affecting you if you execute that function a lot, is accumulated bloat in pg_class, pg_attribute, or other system catalogs. You may want to make sure these are vacuumed often. The answer in my experience is a very loud YES YES YES If you use lots of temporary tables you will grow and dirty your system catalogs, so you need to be vacuuming them regularly also (pg_call, pg_attribute) Otherwise your db will slow to a crawl after a while. Ralph ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Temporary Table
In what directory in my linux server will I find these 3 tables? -Original Message- From: Alvaro Nunes Melo [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 26, 2005 10:49 AM To: Christian Paul B. Cosinas Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: I am creating a temporary table in every function that I execute. Which I think is bout 100,000 temporary tables a day. I think that a lot. ;) What is the command for vacuuming these 3 tables? VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; I'm using this ones. Before using them, take a look in the size that this tables are using in your HD, and compare to what you get after running this commands. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Temporary Table
I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Temporary Table
Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found That needs to be run from psql ... I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Temporary Table
Ummm...they're SQL commands. Run them in PostgreSQL, not on the unix command line... Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Temporary Table
In what directory in my linux server will I find these 3 tables? Directory? They're tables in your database... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Temporary Table
I see. But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 08, 2005 2:11 AM To: Christian Paul B. Cosinas Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found That needs to be run from psql ... I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Temporary Table
Or you could just run the 'vacuumdb' utility... Put something like this in cron: # Vacuum full local pgsql database 30 * * * * postgres vacuumdb -a -q -z You really should read the manual. Chris Christian Paul B. Cosinas wrote: I see. But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 08, 2005 2:11 AM To: Christian Paul B. Cosinas Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found That needs to be run from psql ... I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Temporary Table
You can use the vacuumdb external command. Here's an example: vacuumdb --full --analyze --table mytablename mydbname On Tue, 8 Nov 2005, Christian Paul B. Cosinas wrote: But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 08, 2005 2:11 AM To: Christian Paul B. Cosinas Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found That needs to be run from psql ... I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Temporary Table
On Tue, 2005-11-08 at 10:22 +, Christian Paul B. Cosinas wrote: I see. But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. I usually write a small shell script something like: == #!/bin/sh psql somedatabase EOQ VACUUM this; VACUUM that; DELETE FROM someotherplace WHERE delete_this_record; EOQ == and so forth... This makes the SQL quite nicely readable. Regards, Andrew McMillan. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 You work very hard. Don't try to think as well. - signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Temporary Table
I am creating a temporary table in every function that I execute. Which I think is bout 100,000 temporary tables a day. What is the command for vacuuming these 3 tables? Also I read about the auto vacuum of postgresql. How can I execute this auto vacuum or the settings in the configuration? -Original Message- From: Alvaro Nunes Melo [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 25, 2005 10:58 AM To: Christian Paul B. Cosinas Subject: Re: [PERFORM] Temporary Table Hi Christian, Christian Paul B. Cosinas wrote: Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? I believe it will depend on how many temporary tables you will create in a daily basis. We had a performance problem caused by them, and by not monitoring properly the database size. The pg_attribite, pg_class and pg_depend tables grow a lot. When I found out that this was the problem I saw some messages in the list archieve, and now the overall performance is great. What I do is daily run VACUUM FULL and REINDEX in this three tables. Alvaro I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
