X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 7231A9DCDEE
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Mar 2006 21:24:20 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 06014-05-2
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Mar 2006 21:24:23 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.196])
	by postgresql.org (Postfix) with ESMTP id 1C6299DCBD5
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Mar 2006 21:24:17 -0400 (AST)
Received: by zproxy.gmail.com with SMTP id 14so1445702nzn
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Mar 2006 17:24:21 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=PcRZt+717ppXVVBhTxP8TDfXsG/k7S1d8QcziOx9zjr7OVPCZsRk8OhW9JGUw9t4c/Hv+OHvlprblCeyVxAwnjp3xtbtCFzDhRibj5e4NAKZ0FRyqFrmwSmN7pV3y2C4ifZ6U4ooGeS44cqH8QgipmNamDLDRQWy6LscZVk+lnM=
Received: by 10.36.247.71 with SMTP id u71mr1736546nzh;
	Mon, 13 Mar 2006 17:24:21 -0800 (PST)
Received: from ?10.1.1.22? ( [203.217.18.65])
	by mx.gmail.com with ESMTP id r15sm263941nza.2006.03.13.17.24.20;
	Mon, 13 Mar 2006 17:24:21 -0800 (PST)
Message-ID: <44161B46.5050801@gmail.com>
Date: Tue, 14 Mar 2006 12:24:22 +1100
From: Chris <dmagick@gmail.com>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Gavin Sherry <swm@alcove.com.au>
CC: pgsql-performance@postgresql.org
Subject: Re: import performance
References: <441610EB.7070001@gmail.com>
	<Pine.LNX.4.58.0603141146130.16333@linuxworld.com.au>
In-Reply-To: <Pine.LNX.4.58.0603141146130.16333@linuxworld.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.118 required=5 tests=[AWL=0.118]
X-Spam-Score: 0.118
X-Spam-Level: 
X-Archive-Number: 200603/178
X-Sequence-Number: 17615

Gavin Sherry wrote:
> On Tue, 14 Mar 2006, Chris wrote:
> 
> 
>>Hi all,
>>
>>I'm trying to work out why my 8.1 system is slower than my 7.4 system
>>for importing data.
>>
>>The import is a lot of "insert into" commands - it's a converted
>>database from another system so I can't change it to copy commands.
>>

<snip>

new config variables...

autovacuum = off

bgwriter_all_maxpages = 15
bgwriter_all_percent = 2.0
bgwriter_delay = 500
bgwriter_lru_maxpages = 10
bgwriter_lru_percent = 5.0

checkpoint_segments = 10
checkpoint_timeout = 300

commit_delay = 10000
commit_siblings = 10

fsync = on

lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

log_connections = on
log_destination = 'syslog'
log_disconnections = on
log_duration = on
log_statement = 'all'

max_connections = 16

redirect_stderr = on

shared_buffers = 12500

silent_mode = off

stats_command_string = off

syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

temp_buffers = 500

wal_buffers = 256


I changed a couple of things and restarted postgres before trying again. 
Still getting pretty insert times :(

INSERT 0 1
Time: 1251.956 ms
INSERT 0 1
Time: 700.244 ms
INSERT 0 1
Time: 851.254 ms
INSERT 0 1
Time: 407.725 ms
INSERT 0 1
Time: 267.881 ms
INSERT 0 1
Time: 575.834 ms
INSERT 0 1
Time: 371.914 ms
INSERT 0 1


The table schema is bare:

CREATE TABLE ArticleLive_articlepages (
   PageID serial not null,
   ArticleID integer default '0',
   SortOrderID integer default '0',
   Title varchar(100) NOT NULL default '',
   Content text,
   PRIMARY KEY  (PageID)
);

(I know the fields will be lowercased...).

-- 
Postgresql & php tutorials
http://www.designmagick.com/