From: | "Todd A(dot) Cook" <tcook(at)blackducksoftware(dot)com> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | slow commits with heavy temp table usage in 8.4.0 |
Date: | 2009-08-05 17:54:25 |
Message-ID: | 4A79C751.4030704@blackducksoftware.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
I've noticed that on 8.4.0, commits can take a long time when a temp table is repeatedly
filled and truncated within a loop. A very contrived example is
begin;
create or replace function commit_test_with_truncations()
returns void
language 'plpgsql'
as $_func_$
declare
i integer;
begin
create temp table t1 (x integer) on commit drop ;
for i in 1 .. 22000 loop
insert into t1 select s from generate_series(1,1000) s ;
truncate t1 ;
end loop;
end;
$_func_$;
select commit_test_with_truncations() ;
commit ;
On may laptop (Core2 Duo with 3.5GB and a disk dedicated to PG), the function call takes
about 124 seconds, and the commit takes about 43 seconds. The function execution generates
a lot of I/O activity, but the commit is entirely CPU bound.
By contrast, the same test on an 8.2.13 system (2 older Xeons and 8GB) had times of 495
and 19 seconds. In this case, both the function execution and the commit were entirely
CPU bound.
The overall process in 8.4 is much faster than 8.2.13, but the commit time is somewhat
surprising to me. Is that to be expected?
8.4 version(): PostgreSQL 8.4.0 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-27), 64-bit
8.2.13 version(): PostgreSQL 8.2.13 on x86_64-suse-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 20050117 (prerelease) (SUSE Linux)
-- todd
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-08-05 18:02:27 | Re: [COMMITTERS] pgsql: Use DocBook XSL stylesheets for man page building This switches |
Previous Message | Tom Lane | 2009-08-05 17:43:05 | Re: CommitFest 2009-07: Closing Soon |