Re: import performance

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/