pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTemporary Table
17+ messages / 10 participants
[nested] [flat]
* Temporary Table
@ 2005-10-25 07:28 Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 0 replies; 17+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-25 07:28 UTC (permalink / raw)
To: pgsql-performance
Does Creating Temporary table in a function and NOT dropping them affects
the performance of the database?
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-10-26 02:15 Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 0 replies; 17+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-26 02:15 UTC (permalink / raw)
To: pgsql-performance; +Cc: 'Alvaro Nunes Melo' <al_nunes@atua.com.br>
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:al_nunes@atua.com.br]
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-07 11:07 Alvaro Herrera <alvherre@commandprompt.com>
parent: Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 1 reply; 17+ messages in thread
From: Alvaro Herrera @ 2005-11-07 11:07 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: pgsql-performance
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 Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 17+ messages in thread
* Temporary Table
@ 2005-11-07 15:35 Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 1 reply; 17+ messages in thread
From: Christian Paul B. Cosinas @ 2005-11-07 15:35 UTC (permalink / raw)
To: pgsql-performance
Does Creating Temporary table in a function and NOT dropping them affects
the performance of the database?
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-07 21:40 Ralph Mason <ralph.mason@telogis.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 17+ messages in thread
From: Ralph Mason @ 2005-11-07 21:40 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-08 02:10 Joshua D. Drake <jd@commandprompt.com>
parent: Christian Paul B. Cosinas <cpc@cybees.com>
1 sibling, 1 reply; 17+ messages in thread
From: Joshua D. Drake @ 2005-11-08 02:10 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: 'Alvaro Nunes Melo' <al_nunes@atua.com.br>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-08 02:14 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Christian Paul B. Cosinas <cpc@cybees.com>
1 sibling, 0 replies; 17+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-08 02:14 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: Alvaro Nunes Melo <al_nunes@atua.com.br>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-08 02:15 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 0 replies; 17+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-08 02:15 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: Alvaro Nunes Melo <al_nunes@atua.com.br>; pgsql-performance
> In what directory in my linux server will I find these 3 tables?
Directory? They're tables in your database...
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-08 02:22 Jeff Frost <jeff@frostconsultingllc.com>
parent: Christian Paul B. Cosinas <cpc@cybees.com>
2 siblings, 0 replies; 17+ messages in thread
From: Jeff Frost @ 2005-11-08 02:22 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: 'Joshua D. Drake' <jd@commandprompt.com>; 'Alvaro Nunes Melo' <al_nunes@atua.com.br>; pgsql-performance
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:jd@commandprompt.com]
> 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 <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-08 02:24 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Christian Paul B. Cosinas <cpc@cybees.com>
2 siblings, 0 replies; 17+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-08 02:24 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: 'Joshua D. Drake' <jd@commandprompt.com>; Alvaro Nunes Melo <al_nunes@atua.com.br>; pgsql-performance
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:jd@commandprompt.com]
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-08 02:35 Andrew McMillan <andrew@catalyst.net.nz>
parent: Christian Paul B. Cosinas <cpc@cybees.com>
2 siblings, 0 replies; 17+ messages in thread
From: Andrew McMillan @ 2005-11-08 02:35 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: 'Joshua D. Drake' <jd@commandprompt.com>; Alvaro Nunes Melo <al_nunes@atua.com.br>; pgsql-performance
On Tue, 2005-11-08 at 10:22 +0000, 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.
-------------------------------------------------------------------------
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-08 10:04 Christian Paul B. Cosinas <cpc@cybees.com>
1 sibling, 1 reply; 17+ messages in thread
From: Christian Paul B. Cosinas @ 2005-11-08 10:04 UTC (permalink / raw)
To: 'Alvaro Nunes Melo' <al_nunes@atua.com.br>; pgsql-performance
In what directory in my linux server will I find these 3 tables?
-----Original Message-----
From: Alvaro Nunes Melo [mailto:al_nunes@atua.com.br]
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-08 10:09 Christian Paul B. Cosinas <cpc@cybees.com>
1 sibling, 2 replies; 17+ messages in thread
From: Christian Paul B. Cosinas @ 2005-11-08 10:09 UTC (permalink / raw)
To: 'Alvaro Nunes Melo' <al_nunes@atua.com.br>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary Table
@ 2005-11-08 10:22 Christian Paul B. Cosinas <cpc@cybees.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 3 replies; 17+ messages in thread
From: Christian Paul B. Cosinas @ 2005-11-08 10:22 UTC (permalink / raw)
To: 'Joshua D. Drake' <jd@commandprompt.com>; +Cc: 'Alvaro Nunes Melo' <al_nunes@atua.com.br>; pgsql-performance
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:jd@commandprompt.com]
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Temporary table
@ 2006-06-23 21:58 Franklin Haut <franklin.haut@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Franklin Haut @ 2006-06-23 21:58 UTC (permalink / raw)
To: pgsql-performance
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.
Tks,
Franklin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary table
@ 2006-06-23 22:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Franklin Haut <franklin.haut@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Tom Lane @ 2006-06-23 22:04 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; +Cc: pgsql-performance
"Franklin Haut" <franklin.haut@gmail.com> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Temporary table
@ 2006-06-23 22:08 Larry Rosenman <ler@lerctr.org>
parent: Franklin Haut <franklin.haut@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Larry Rosenman @ 2006-06-23 22:08 UTC (permalink / raw)
To: 'Franklin Haut' <franklin.haut@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2006-06-23 22:08 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-25 07:28 Temporary Table Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-26 02:15 Re: Temporary Table Christian Paul B. Cosinas <cpc@cybees.com>
2005-11-07 15:35 Temporary Table Christian Paul B. Cosinas <cpc@cybees.com>
2005-11-07 11:07 ` Alvaro Herrera <alvherre@commandprompt.com>
2005-11-07 21:40 ` Ralph Mason <ralph.mason@telogis.com>
2005-11-08 10:04 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-11-08 02:15 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-11-08 10:09 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-11-08 02:10 ` Joshua D. Drake <jd@commandprompt.com>
2005-11-08 10:22 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-11-08 02:22 ` Jeff Frost <jeff@frostconsultingllc.com>
2005-11-08 02:24 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-11-08 02:35 ` Andrew McMillan <andrew@catalyst.net.nz>
2005-11-08 02:14 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-06-23 21:58 Temporary table Franklin Haut <franklin.haut@gmail.com>
2006-06-23 22:04 ` Re: Temporary table Tom Lane <tgl@sss.pgh.pa.us>
2006-06-23 22:08 ` Re: Temporary table Larry Rosenman <ler@lerctr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox