slow commits with heavy temp table usage in 8.4.0

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

Responses

Browse pgsql-hackers by date

  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