Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 3451AB5D91F
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun, 15 May 2011 19:02:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 31851-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun, 15 May 2011 22:02:46 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mailserver.dialdata.com.ar (dialdata.com.ar [200.49.210.5])
	by mail.postgresql.org (Postfix) with ESMTP id 021CBB5D80B
	for <pgsql-performance@postgresql.org>;
	Sun, 15 May 2011 19:02:45 -0300 (ADT)
Received: from mailserver.dialdata.com.ar (localhost [127.0.0.1])
	by mailserver.dialdata.com.ar (Postfix) with ESMTP id F2D4D2E71B
	for <pgsql-performance@postgresql.org>;
	Sun, 15 May 2011 19:02:39 -0300 (ART)
Received: from 190-49-178-152.speedy.com.ar ([190.49.178.152])
	by mailserver.dialdata.com.ar:81
	with HTTP (HTTP/1.1 POST); Sun, 15 May 2011 19:02:39 -0300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=_9226a410948db25ed35cd74866ab8a0a"
Date: Sun, 15 May 2011 19:02:39 -0300
From: Ezequiel Lovelle <elovelle@dialdata.com.ar>
To: <pgsql-performance@postgresql.org>
Subject: slow loop =?UTF-8?Q?inserts=3F?=
Organization: Teleservicios y Marketing S.A
Message-ID: <284696193619d8ac712400941bc5946c@dialdata.com.ar>
X-Sender: elovelle@dialdata.com.ar
User-Agent: DDM/0.5
X-DDM-MailScanner-Information: DDM
X-DDM-MailScanner-ID: F2D4D2E71B.A1567
X-DDM-MailScanner: Found to be clean
X-DDM-MailScanner-From: elovelle@dialdata.com.ar
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.509 tagged_above=-10 required=5
	tests=BAYES_05=-0.5, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201105/227
X-Sequence-Number: 43736

--=_9226a410948db25ed35cd74866ab8a0a
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=UTF-8

  

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@webserver ~]# time php script.php

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

MY SCIPT: 


--=_9226a410948db25ed35cd74866ab8a0a
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=UTF-8

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN">
<html><body>
<p>Hi, I'm new to postgres and I have the next question.</p>
<p>I have a php program that makes 100000 inserts in my database.<br /> aut=
oincrement numbers inserted into a table with 5 columns.<br /> The script t=
akes about 4 minutes from a webserver<br /> Is it a normal time?</p>
<p>How could reduce this time by a bulce of inserts?</p>
<p>When I turn off fsync get much more performance, but it is not ideal in =
power failure</p>
<p>&nbsp;</p>
<p><strong>Hardware</strong>: 2 disks 1TB 7200 rpm with software raid 1 (gm=
irror raid)</p>
<p>8 Gb RAM</p>
<p>CPU Intel Quad Core 2.4 Ghz</p>
<p><strong>OS</strong>: Freebsd 8.2</p>
<p><strong>Postgres version</strong>: 9.0.4</p>
<p>&nbsp;</p>
<p><strong>My postgres config</strong>:</p>
<p><br /> listen_addresses =3D '*'<br /> wal_level =3D archive<br /> fsync =
=3D on<br /> archive_mode =3D on<br /> archive_command =3D 'exit 0'<br /> m=
aintenance_work_mem =3D 480MB<br /> checkpoint_completion_target =3D 0.5<br=
 /> effective_cache_size =3D 5632MB<br /> work_mem =3D 40MB<br /> wal_buffe=
rs =3D 16MB<br /> checkpoint_segments =3D 30<br /> shared_buffers =3D 1920M=
B<br /> max_connections =3D 40</p>
<p>&nbsp;</p>
<p><strong>My execution time of my script</strong>:</p>
<p>[root@webserver ~]# time php script.php<br /> <br /> real&nbsp;&nbsp;&nb=
sp; 4m54.846s<br /> user&nbsp;&nbsp;&nbsp; 0m2.695s<br /> sys&nbsp;&nbsp;&n=
bsp;&nbsp; 0m1.775s</p>
<p>&nbsp;</p>
<p><strong>My scipt</strong>:</p>
<p>&lt;?php</p>
<p>pg_connect("host=3Dhost&nbsp; port=3Dport dbname=3Ddb user=3Duser passwo=
rd=3Dpass") or die ("No me conecto...");<br /> for ( $var =3D 1; $var &lt;=
=3D 100000 ; $var++ )<br /> {<br /> $sql =3D "INSERT INTO server (aa, bb, c=
c, dd, ee) VALUES ('$var','$var','$var','$var','$var')";<br /> pg_query($sq=
l);<br /> }<br /> ?&gt;</p>
<p><strong>my dd test is</strong>:</p>
<p>#time sh -c "dd if=3D/dev/zero of=3D/tmp/test count=3D500000 &amp;&amp; =
fsync"<br /> 500000+0 records in<br /> 500000+0 records out<br /> 256000000=
 bytes transferred in 2.147917 secs (119185237 bytes/sec)<br /> usage: fsyn=
c file ...<br /> <br /> real&nbsp;&nbsp;&nbsp; 0m2.177s<br /> user&nbsp;&nb=
sp;&nbsp; 0m0.188s<br /> sys&nbsp;&nbsp;&nbsp;&nbsp; 0m0.876s</p>
<p>&nbsp;</p>
<p>Thanks, any help will be well recived,</p>
</body></html>

--=_9226a410948db25ed35cd74866ab8a0a--