pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtemp table "on commit delete rows": transaction overhead
6+ messages / 3 participants
[nested] [flat]
* temp table "on commit delete rows": transaction overhead
@ 2010-03-30 11:46 Artiom Makarov <artiom.makarov@gmail.com>
2010-03-30 17:50 ` Re: temp table "on commit delete rows": transaction overhead Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Artiom Makarov @ 2010-03-30 11:46 UTC (permalink / raw)
To: pgsql-performance
Hi!
We have a postgres database which accessed by clients app via PL/PGSQL
stored procedures ( 8.4.1 on x86_64 ubuntu 8.04 server).
For some reasons we use about 25 temp tables "on commit delete rows".
It widely used by our SP.
When temp tables with "on commit delete rows" exists, I can see a
strange delay at any “begin” and “commit”.
2010-03-09 15:14:01 MSK logrus 32102 amber LOG: duration: 20.809 ms
statement: BEGIN
2010-03-09 15:14:01 MSK logrus 32102 amber LOG: duration: 0.809 ms
statement: SELECT empl.BL_CustomerFreeCLGet('384154676925391',
'8189', NULL)
010-03-09 15:14:01 MSK logrus 32102 amber LOG: duration: 0.283 ms
statement: FETCH ALL IN "<unnamed portal 165>"; --
+++empl.BL_CustomerFreeCLGet+++<<21360>>
2010-03-09 15:14:01 MSK logrus 32102 amber LOG: duration: 19.895 ms
statement: COMMIT
The more system load and more temp table quantity in session, then
more “begin” and “commit” delays.
Test example below:
create database test;
create language plpgsql;
CREATE OR REPLACE FUNCTION test_connectionprepare(in_create
bool,in_IsTemp bool,in_DelOnCommit bool,in_TableCount int)
RETURNS boolean AS $$
declare
m_count int := 50;
m_isTemp bool;
begin
m_count := coalesce(in_TableCount,m_count);
FOR i IN 0..m_count LOOP
if in_create then
execute 'create ' || case when in_IsTemp then ' temp ' else ' ' end
||' table tmp_table_'
|| i::text || '(id int,pid int,name text) '
|| case when in_DelOnCommit then ' on commit delete rows
' else ' ' end || ';';
else
execute 'drop table if exists tmp_table_' || i::text ||';';
end if;
END LOOP;
return in_create;
end;
$$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
------------------------------------------------------------------------------
Now run pgScript:
DECLARE @I;
SET @I = 1;
WHILE @I <= 100
BEGIN
select now();
SET @I = @I + 1;
END
It spent about 2200-2300 ms on my server.
Let's create 50 temp tables: select test_connectionprepare(true,true,true,100);
and run script again.
We can see 2-3 times slowing!
Here temp tables quantity vs test run time:
0 - 2157-2187
10 - 2500-2704
50 - 5900-6000
100 - 7900-8000
500 - 43000+
I can to suppose, that all tables are truncated before and after every
transactions. Very strange method for read only transactions!
------------------------------------------------------------------------------
Sorry for my English.
My server info:
"PostgreSQL 8.4.1 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.2.real
(GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4), 64-bit"
Linux u16 2.6.24-24-server #1 SMP Tue Jul 7 19:39:36 UTC 2009 x86_64 GNU/Linux
4xOpteron 16 processor cores.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp table "on commit delete rows": transaction overhead
2010-03-30 11:46 temp table "on commit delete rows": transaction overhead Artiom Makarov <artiom.makarov@gmail.com>
@ 2010-03-30 17:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-31 07:42 ` Re: temp table "on commit delete rows": transaction overhead Artiom Makarov <artiom.makarov@gmail.com>
2010-04-01 00:44 ` Re: temp table "on commit delete rows": transaction overhead Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2010-03-30 17:50 UTC (permalink / raw)
To: Artiom Makarov <artiom.makarov@gmail.com>; +Cc: pgsql-performance
Artiom Makarov <artiom.makarov@gmail.com> writes:
> When temp tables with "on commit delete rows" exists, I can see a
> strange delay at any begin and commit.
A delay at commit is hardly surprising, because each such temp table
requires filesystem operations at commit (basically an "ftruncate").
I don't recall any operations at transaction start for such tables,
but there may be some.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp table "on commit delete rows": transaction overhead
2010-03-30 11:46 temp table "on commit delete rows": transaction overhead Artiom Makarov <artiom.makarov@gmail.com>
2010-03-30 17:50 ` Re: temp table "on commit delete rows": transaction overhead Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-03-31 07:42 ` Artiom Makarov <artiom.makarov@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Artiom Makarov @ 2010-03-31 07:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
2010/3/30 Tom Lane <tgl@sss.pgh.pa.us>:
> I don't recall any operations at transaction start for such tables,
> but there may be some.
>
Both in СommitTransaction(void) and PrepareTransaction(void) we can
see PreCommit_on_commit_actions() call;
Here PreCommit_on_commit_actions()
<CUT>
case ONCOMMIT_DELETE_ROWS:
oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
break;
<CUT>
As a my opinion, the same action taked place at transaction start and commit.
To truncate rows of any temp tables, both changed and unchanged(!)
after transaction looks as little reinsurance. Well.
But why do the same action _before_ any transaction?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp table "on commit delete rows": transaction overhead
2010-03-30 11:46 temp table "on commit delete rows": transaction overhead Artiom Makarov <artiom.makarov@gmail.com>
2010-03-30 17:50 ` Re: temp table "on commit delete rows": transaction overhead Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-04-01 00:44 ` Bruce Momjian <bruce@momjian.us>
2010-04-05 07:56 ` Re: temp table "on commit delete rows": transaction overhead Artiom Makarov <artiom.makarov@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2010-04-01 00:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Artiom Makarov <artiom.makarov@gmail.com>; pgsql-performance
Tom Lane wrote:
> Artiom Makarov <artiom.makarov@gmail.com> writes:
> > When temp tables with "on commit delete rows" exists, I can see a
> > strange delay at any begin and commit.
>
> A delay at commit is hardly surprising, because each such temp table
> requires filesystem operations at commit (basically an "ftruncate").
> I don't recall any operations at transaction start for such tables,
> but there may be some.
I think one of the problems is that we do the truncate even if the table
has not be touched by the query, which is poor behavior.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp table "on commit delete rows": transaction overhead
2010-03-30 11:46 temp table "on commit delete rows": transaction overhead Artiom Makarov <artiom.makarov@gmail.com>
2010-03-30 17:50 ` Re: temp table "on commit delete rows": transaction overhead Tom Lane <tgl@sss.pgh.pa.us>
2010-04-01 00:44 ` Re: temp table "on commit delete rows": transaction overhead Bruce Momjian <bruce@momjian.us>
@ 2010-04-05 07:56 ` Artiom Makarov <artiom.makarov@gmail.com>
2010-04-05 12:09 ` Re: temp table "on commit delete rows": transaction overhead Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Artiom Makarov @ 2010-04-05 07:56 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
2010/4/1 Bruce Momjian <bruce@momjian.us>:
> I think one of the problems is that we do the truncate even if the table
> has not be touched by the query, which is poor behavior.
Thank you for the support.
Will be this problem registered?
PS
I see a workaround: switch off "on commit delete rows" on temp tables
and use txid_current() to control transaction visibility.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp table "on commit delete rows": transaction overhead
2010-03-30 11:46 temp table "on commit delete rows": transaction overhead Artiom Makarov <artiom.makarov@gmail.com>
2010-03-30 17:50 ` Re: temp table "on commit delete rows": transaction overhead Tom Lane <tgl@sss.pgh.pa.us>
2010-04-01 00:44 ` Re: temp table "on commit delete rows": transaction overhead Bruce Momjian <bruce@momjian.us>
2010-04-05 07:56 ` Re: temp table "on commit delete rows": transaction overhead Artiom Makarov <artiom.makarov@gmail.com>
@ 2010-04-05 12:09 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2010-04-05 12:09 UTC (permalink / raw)
To: Artiom Makarov <artiom.makarov@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Artiom Makarov wrote:
> 2010/4/1 Bruce Momjian <bruce@momjian.us>:
>
> > I think one of the problems is that we do the truncate even if the table
> > has not be touched by the query, which is poor behavior.
>
> Thank you for the support.
> Will be this problem registered?
I have it on my personal TODO and will try to get it on the official
TODO soon.
> I see a workaround: switch off "on commit delete rows" on temp tables
> and use txid_current() to control transaction visibility.
OK.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-04-05 12:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-30 11:46 temp table "on commit delete rows": transaction overhead Artiom Makarov <artiom.makarov@gmail.com>
2010-03-30 17:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-31 07:42 ` Artiom Makarov <artiom.makarov@gmail.com>
2010-04-01 00:44 ` Bruce Momjian <bruce@momjian.us>
2010-04-05 07:56 ` Artiom Makarov <artiom.makarov@gmail.com>
2010-04-05 12:09 ` Bruce Momjian <bruce@momjian.us>
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