Thread:
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?
Thx,
Orhan a.
Orhan Aglagul wrote: > Hi Everybody, > > I was trying to see how many inserts per seconds my application could > handle on various machines. > > > 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. CPU is unlikely your bottleneck.. You failed to mention anything about your I/O setup. More details in this regard will net you better responses. However, an archive search for insert performance will probably be worthwhile, since this type of question is repeated about once a month.
Dan Harris wrote:
> Orhan Aglagul wrote:
>> Hi Everybody,
>>
>> I was trying to see how many inserts per seconds my application could
>> handle on various machines.
>>
>>
>> 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.
>
> CPU is unlikely your bottleneck.. You failed to mention anything about
> your I/O setup. More details in this regard will net you better
> responses. However, an archive search for insert performance will
> probably be worthwhile, since this type of question is repeated about
> once a month.
He also fails to mention if he is doing the inserts one at a time or as
batch.
Joshua D. Drake
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Joshua D. Drake wrote: > >> CPU is unlikely your bottleneck.. You failed to mention anything >> about your I/O setup. [...] > > He also fails to mention if he is doing the inserts one at a time or > as batch. Would this really be important? I mean, would it affect a *comparison*?? As long as he does it the same way for all the hardware setups, seems ok to me. Carlos --
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)
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. > > 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 > > > In addition to my previous post, if you see that big a change between fsync on and off, you likely have a drive subsystem that is actually reporting fsync properly. The other two machines are lying. Or they have a battery backed caching raid controller
Forgot to reply to the mailing list..... Sorry (new here)
Here are responses to previous questions....
-----Original Message-----
From: Orhan Aglagul
Sent: Tuesday, May 08, 2007 5:30 PM
To: 'Joshua D. Drake'
Subject: RE: [PERFORM]
I am using a prepared statement and inserting in a loop 10,000 records.
I need the data real time, so I am not using batch inserts. I have to
run each insert as a separate transaction....
I am running the app on a RH EL4 (Kernel 2.6.20).
In fact my CPU usage is too low when running the app with fsync=off.
Here is the output of vmstat during the test:
First 10 lines:
r b swpd free buff cache si so bi bo in cs us sy
id wa
0 1 0 1634144 21828 234752 0 0 32 408 210 404 0 0
90 9
0 1 0 1634020 21828 234816 0 0 0 1404 538 1879 0 0
50 50
0 1 0 1633896 21828 234940 0 0 0 1400 525 1849 0 0
50 49
0 1 0 1633772 21828 235048 0 0 0 1412 537 1878 0 0
50 50
0 1 0 1633648 21832 235168 0 0 0 1420 531 1879 0 0
50 50
0 1 0 1633524 21840 235280 0 0 0 1420 535 1884 0 0
50 50
0 1 0 1633524 21844 235400 0 0 0 1396 535 1718 0 0
50 50
0 1 0 1633524 21848 235524 0 0 0 1536 561 1127 0 0
50 50
0 1 0 1633524 21852 235644 0 0 0 1412 557 1390 0 0
50 50
0 1 0 1633268 21860 235728 0 0 0 1408 582 1393 0 0
50 50
0 1 0 1633268 21868 235844 0 0 0 1424 548 1377 1 4
50 45
1 0 0 1633144 21876 235968 0 0 0 1404 548 1394 14 4
48 34
0 1 0 1633020 21884 236084 0 0 0 1420 540 1374 5 0
50 46
...
The logical volume is an ext3 file system. That's where all the database
files reside. (No hardware optimization done).
Sorry for the delay,
Thanks..
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joshua D.
Drake
Sent: Tuesday, May 08, 2007 5:05 PM
To: Dan Harris
Cc: PostgreSQL Performance
Subject: Re: [PERFORM]
Dan Harris wrote:
> Orhan Aglagul wrote:
>> Hi Everybody,
>>
>> I was trying to see how many inserts per seconds my application could
>> handle on various machines.
>>
>>
>> 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.
>
> CPU is unlikely your bottleneck.. You failed to mention anything
about
> your I/O setup. More details in this regard will net you better
> responses. However, an archive search for insert performance will
> probably be worthwhile, since this type of question is repeated about
> once a month.
He also fails to mention if he is doing the inserts one at a time or as
batch.
Joshua D. Drake
>
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
On Tue, 8 May 2007, Orhan Aglagul wrote: > Time for 10000 inserts > Pentium M 1.7 > ~17 sec fsync=on > ~6 sec fsync=off This is 588 inserts/second with fsync on. It's impossible to achieve that without write caching at either the controller or hard drive. My bet would be that your hard drive in this system is a regular IDE/SATA drive that has write caching enabled, which is the normal case. That means this system doesn't really do a fsync when you tell it to. > Pentium 4 2.4 > ~13 sec fsync=on > ~11 sec fsync=off Same response here. Odds are good the fsync=on numbers here are a fantasy; unless you have some serious disk hardware in this server, it can't really be doing an fsync and giving this performance level. > Dual Xeon > ~65 sec fsync=on > ~1.9 sec fsync=off Now this looks reasonable. 5263/second with fsync off, 154/second with it on. This system appears to have hard drives in it that correctly write data out when asked to via the fsync mechanism. I would bet this one is a server that has some number of 10,000 RPM SCSI drives in it. Such a drive gives a theoretical maximum of 166.7 inserts/second if the inserts are done one at a time. If this all is confusing to you, I have written a long primer on this subject that explains how the interaction between the PostgreSQL, fsync, and the underlying drives work. If you have the patience to work your way through it and follow the references along the way, I think you'll find the results you've been seeing will make more sense, and you'll be in a better position to figure out what you should do next: http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Tuesday 08 May 2007 20:20, Carlos Moreno wrote:
> Joshua D. Drake wrote:
> >> CPU is unlikely your bottleneck.. You failed to mention anything
> >> about your I/O setup. [...]
> >
> > He also fails to mention if he is doing the inserts one at a time or
> > as batch.
>
> Would this really be important? I mean, would it affect a *comparison*??
> As long as he does it the same way for all the hardware setups, seems ok
> to me.
>
Sure. He looks i/o bound, and single inserts vs. batch inserts will skew
results even further depending on which way your doing it.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL