Subject: Re: 
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Orhan Aglagul <oaglagul@cittio.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <868BCE5A6576F44A862F1FBBC3E14A01042559C3@ms17.mse9.exchange.ms>
References: <868BCE5A6576F44A862F1FBBC3E14A01042559C3@ms17.mse9.exchange.ms>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1178670165.7497.42.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Tue, 08 May 2007 19:22:45 -0500

On Tue, 2007-05-08 at 17:59, Orhan Aglagul wrote:
> Hi Everybody,
> 
> I was trying to see how many inserts per seconds my application could
> handle on various machines.
> 
> Those are the machines I used to run my app:
> 
>  
> 
> 1)       Pentium M 1.7Ghz
> 
> 2)       Pentium 4 2.4 Ghz
> 
> 3)       DMP Xeon 3Ghz
> 
>  
> 
> Sure, I was expecting the dual Zeon to outperform the Pentium M and 4.
> But the data showed the opposite.
> 
> So, I wrote a simple program (in C) using the libpq.so.5 which opens a
> connection to the database (DB in localhost), 
> 
> Creates a Prepared statement for the insert and does a 10,000 insert.
> The result did not change.
> 
>  
> 
> Only after setting fsync to off in the config file, the amount of time
> to insert 10,000 records was acceptable.
> 
>  
> 
> Here is the data:
> 
>             
> 
> Time for 10000 inserts
> 
> Fsync=on
> 
> Fsync=off
> 
> Pentium M 1.7
> 
> ~17 sec
> 
> ~6 sec
> 
> Pentium 4 2.4
> 
> ~13 sec
> 
> ~11 sec
> 
> Dual Xeon
> 
> ~65 sec
> 
> ~1.9 sec
> 
> 
>  
> 
> I read that postgres does have issues with MP Xeon (costly context
> switching). But I still think that with fsync=on 65 seconds is
> ridiculous. 
> 
>  
> 
> Can anybody direct me to some improved/acceptable  performance with
> fsync=on?

I'm guessing you didn't do the inserts inside a single transaction,
which means that each insert was it's own transaction.

Try doing them all in a transaction.  I ran this simple php script:

<?php
$conn = pg_connect("dbname=smarlowe");
pg_query("begin");
for ($i=0;$i<10000;$i++){
        $r = rand(1,10000000);
        pg_query("insert into tenk (i1) values ($r)");
}
pq_query("commit");
?>

and it finished in 3.5 seconds on my workstation (nothing special)