slow loop inserts?

From: Ezequiel Lovelle <elovelle(at)dialdata(dot)com(dot)ar>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: slow loop inserts?
Date: 2011-05-15 22:02:39
Message-ID: 284696193619d8ac712400941bc5946c@dialdata.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi, I'm new to postgres and I have the next question.

I have a
php program that makes 100000 inserts in my database.
autoincrement
numbers inserted into a table with 5 columns.
The script takes about 4
minutes from a webserver
Is it a normal time?

How could reduce this
time by a bulce of inserts?

When I turn off fsync get much more
performance, but it is not ideal in power failure

HARDWARE: 2 disks
1TB 7200 rpm with software raid 1 (gmirror raid)

8 Gb RAM

CPU Intel
Quad Core 2.4 Ghz

OS: Freebsd 8.2

POSTGRES VERSION: 9.0.4

MY
POSTGRES CONFIG:

listen_addresses = '*'
wal_level = archive
fsync =
on
archive_mode = on
archive_command = 'exit 0'
maintenance_work_mem
= 480MB
checkpoint_completion_target = 0.5
effective_cache_size =
5632MB
work_mem = 40MB
wal_buffers = 16MB
checkpoint_segments = 30

shared_buffers = 1920MB
max_connections = 40

MY EXECUTION TIME OF MY
SCRIPT:

[root(at)webserver ~]# time php script.php

real 4m54.846s
user
0m2.695s
sys 0m1.775s

MY SCIPT:

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Dan Birken 2011-05-15 22:27:31 Re: slow loop inserts?
Previous Message Cédric Villemain 2011-05-15 21:45:55 Re: reducing random_page_cost from 4 to 2 to force index scan