From elovelle@dialdata.com.ar Sun May 15 22:02:57 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 3451AB5D91F for ; 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 ; 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 ; 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 ; 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 To: 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

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

I have a php program that makes 100000 inserts in my database.
aut= oincrement numbers inserted into a table with 5 columns.
The script t= akes 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 (gm= irror 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 =3D '*'
wal_level =3D archive
fsync = =3D on
archive_mode =3D on
archive_command =3D 'exit 0'
m= aintenance_work_mem =3D 480MB
checkpoint_completion_target =3D 0.5 effective_cache_size =3D 5632MB
work_mem =3D 40MB
wal_buffe= rs =3D 16MB
checkpoint_segments =3D 30
shared_buffers =3D 1920M= B
max_connections =3D 40

 

My execution time of my script:

[root@webserver ~]# time php script.php

real  &nb= sp; 4m54.846s
user    0m2.695s
sys  &n= bsp;  0m1.775s

 

My scipt:

<?php

pg_connect("host=3Dhost  port=3Dport dbname=3Ddb user=3Duser passwo= rd=3Dpass") or die ("No me conecto...");
for ( $var =3D 1; $var <= =3D 100000 ; $var++ )
{
$sql =3D "INSERT INTO server (aa, bb, c= c, dd, ee) VALUES ('$var','$var','$var','$var','$var')";
pg_query($sq= l);
}
?>

my dd test is:

#time sh -c "dd if=3D/dev/zero of=3D/tmp/test count=3D500000 && = fsync"
500000+0 records in
500000+0 records out
256000000= bytes transferred in 2.147917 secs (119185237 bytes/sec)
usage: fsyn= c file ...

real    0m2.177s
user &nb= sp;  0m0.188s
sys     0m0.876s

 

Thanks, any help will be well recived,

--=_9226a410948db25ed35cd74866ab8a0a-- From birken@gmail.com Sun May 15 22:27:39 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5ED36B5DBCF for ; Sun, 15 May 2011 19:27:39 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 55186-02 for ; Sun, 15 May 2011 22:27:32 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f181.google.com (mail-qy0-f181.google.com [209.85.216.181]) by mail.postgresql.org (Postfix) with ESMTP id EDEFFB5DBD1 for ; Sun, 15 May 2011 19:27:31 -0300 (ADT) Received: by qyg14 with SMTP id 14so2308884qyg.19 for ; Sun, 15 May 2011 15:27:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=NjjoHN8x0hE9of7NuLKcHm+DAR6MGhi/G42Y13rx+G0=; b=axA4NGENpd8yGSMn1Ab9Ip0Nft3FDLliO8rKEUfvQBsgGe5NhQs+nAIB/EzR0GiLns fGd6DTivN4l01+Ivf5trhJaDtlNz7QuUy4tMdW/oQ7sD1UuOt2GY4DMGLRLWNod3G1Nl f5szxa5wwZ3auV4u9x4usQMCniKL0Dr1go+94= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=b1pm/bUIEoNcCIzXoXv1nIyrVNHixPfjJGBt2tqaJcfEhpoPrkKhe2gTnHUWSX5QQ8 vtat4kMKLPBHBWyx2CYUeGontrjrnNO5RJMb2dyhUggaTNIDFoM6pqZL+RBm5ZaSqIUh woB7xQeWnqnNyxMMKEUqsiPLw+lE7Y3wlSBmc= MIME-Version: 1.0 Received: by 10.224.215.193 with SMTP id hf1mr2564876qab.313.1305498451930; Sun, 15 May 2011 15:27:31 -0700 (PDT) Received: by 10.224.6.71 with HTTP; Sun, 15 May 2011 15:27:31 -0700 (PDT) In-Reply-To: <284696193619d8ac712400941bc5946c@dialdata.com.ar> References: <284696193619d8ac712400941bc5946c@dialdata.com.ar> Date: Sun, 15 May 2011 15:27:31 -0700 Message-ID: Subject: Re: slow loop inserts? From: Dan Birken To: Ezequiel Lovelle Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=20cf300514907b6d1704a3580b59 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.897 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201105/228 X-Sequence-Number: 43737 --20cf300514907b6d1704a3580b59 Content-Type: text/plain; charset=ISO-8859-1 Try wrapping all your inserts in a transaction: pg_query('BEGIN'); // your inserts pg_query('COMMIT'); That way you won't have to sync each of those inserts to disk, should provide a huge speedup. Of course this means your 10,000 inserts will be all or nothing, but it seems like in this case that should be fine. -Dan On Sun, May 15, 2011 at 3:02 PM, Ezequiel Lovelle wrote: > 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*: > > > pg_connect("host=host port=port dbname=db user=user password=pass") or die > ("No me conecto..."); > for ( $var = 1; $var <= 100000 ; $var++ ) > { > $sql = "INSERT INTO server (aa, bb, cc, dd, ee) VALUES > ('$var','$var','$var','$var','$var')"; > pg_query($sql); > } > ?> > > *my dd test is*: > > #time sh -c "dd if=/dev/zero of=/tmp/test count=500000 && fsync" > 500000+0 records in > 500000+0 records out > 256000000 bytes transferred in 2.147917 secs (119185237 bytes/sec) > usage: fsync file ... > > real 0m2.177s > user 0m0.188s > sys 0m0.876s > > > > Thanks, any help will be well recived, > --20cf300514907b6d1704a3580b59 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Try wrapping all your inserts in a transaction:

pg_query= ('BEGIN');
// your inserts
pg_query('COMMIT= ');

That way you won't have to sync each o= f those inserts to disk, should provide a huge speedup. =A0Of course this m= eans your 10,000 inserts will be all or nothing, but it seems like in this = case that should be fine.

-Dan

On Sun, May 15, 2011 at 3:02 PM, Ezequiel Lovelle <<= a href=3D"mailto:elovelle@dialdata.com.ar">elovelle@dialdata.com.ar>= wrote:

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

I have a php program that makes 100000 inserts in my database.
autoi= ncrement 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

=A0

Hardware: 2 disks 1TB 7200 rpm with software raid 1 (gm= irror raid)

8 Gb RAM

CPU Intel Quad Core 2.4 Ghz

OS: Freebsd 8.2

Postgres version: 9.0.4

=A0

My postgres config:


listen_addresses =3D '*'
wal_level =3D archive
fsyn= c =3D on
archive_mode =3D on
archive_command =3D 'exit 0' maintenance_work_mem =3D 480MB
checkpoint_completion_target =3D 0.5<= br> effective_cache_size =3D 5632MB
work_mem =3D 40MB
wal_buffers =3D 16MB
checkpoint_segments =3D 30<= br> shared_buffers =3D 1920MB
max_connections =3D 40

=A0

My execution time of my script:

[root@webserver ~]# time php script.php

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

=A0

My scipt:

<?php

pg_connect("host=3Dhost=A0 port=3Dport dbname=3Ddb user=3Duser pass= word=3Dpass") or die ("No me conecto...");
for ( $var = =3D 1; $var <=3D 100000 ; $var++ )
{
$sql =3D "INSERT INTO = server (aa, bb, cc, dd, ee) VALUES ('$var','$var','$var= ','$var','$var')";
pg_query($sql);
}
?>

my dd test is:

#time sh -c "dd if=3D/dev/zero of=3D/tmp/test count=3D500000 &&= amp; fsync"
500000+0 records in
500000+0 records out
25600= 0000 bytes transferred in 2.147917 secs (119185237 bytes/sec)
usage: fs= ync file ...

real=A0=A0=A0 0m2.177s
user=A0=A0=A0 0m0.188s
sys=A0=A0=A0=A0= 0m0.876s

=A0

Thanks, any help will be well recived,


--20cf300514907b6d1704a3580b59--