pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed(no subject)
9+ messages / 7 participants
[nested] [flat]
* (no subject)
@ 2007-05-08 22:59 Orhan Aglagul <oaglagul@cittio.com>
2007-05-08 23:48 ` Re: Dan Harris <fbsd@drivefaster.net>
2007-05-09 00:22 ` Re: Scott Marlowe <smarlowe@g2switchworks.com>
2007-05-09 00:30 ` Re: Scott Marlowe <smarlowe@g2switchworks.com>
2007-05-09 04:14 ` Re: Greg Smith <gsmith@gregsmith.com>
0 siblings, 4 replies; 9+ messages in thread
From: Orhan Aglagul @ 2007-05-08 22:59 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re:
2007-05-08 22:59 (no subject) Orhan Aglagul <oaglagul@cittio.com>
@ 2007-05-08 23:48 ` Dan Harris <fbsd@drivefaster.net>
2007-05-09 00:05 ` Re: Joshua D. Drake <jd@commandprompt.com>
3 siblings, 1 reply; 9+ messages in thread
From: Dan Harris @ 2007-05-08 23:48 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re:
2007-05-08 22:59 (no subject) Orhan Aglagul <oaglagul@cittio.com>
2007-05-08 23:48 ` Re: Dan Harris <fbsd@drivefaster.net>
@ 2007-05-09 00:05 ` Joshua D. Drake <jd@commandprompt.com>
2007-05-09 00:20 ` Re: Carlos Moreno <moreno_pg@mochima.com>
2007-05-09 01:12 ` Re: Orhan Aglagul <oaglagul@cittio.com>
0 siblings, 2 replies; 9+ messages in thread
From: Joshua D. Drake @ 2007-05-09 00:05 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re:
2007-05-08 22:59 (no subject) Orhan Aglagul <oaglagul@cittio.com>
2007-05-08 23:48 ` Re: Dan Harris <fbsd@drivefaster.net>
2007-05-09 00:05 ` Re: Joshua D. Drake <jd@commandprompt.com>
@ 2007-05-09 00:20 ` Carlos Moreno <moreno_pg@mochima.com>
2007-05-09 04:58 ` Re: Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 1 reply; 9+ messages in thread
From: Carlos Moreno @ 2007-05-09 00:20 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
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
--
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re:
2007-05-08 22:59 (no subject) Orhan Aglagul <oaglagul@cittio.com>
2007-05-08 23:48 ` Re: Dan Harris <fbsd@drivefaster.net>
2007-05-09 00:05 ` Re: Joshua D. Drake <jd@commandprompt.com>
2007-05-09 00:20 ` Re: Carlos Moreno <moreno_pg@mochima.com>
@ 2007-05-09 04:58 ` Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 9+ messages in thread
From: Robert Treat @ 2007-05-09 04:58 UTC (permalink / raw)
To: pgsql-performance; +Cc: Carlos Moreno <moreno_pg@mochima.com>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re:
2007-05-08 22:59 (no subject) Orhan Aglagul <oaglagul@cittio.com>
2007-05-08 23:48 ` Re: Dan Harris <fbsd@drivefaster.net>
2007-05-09 00:05 ` Re: Joshua D. Drake <jd@commandprompt.com>
@ 2007-05-09 01:12 ` Orhan Aglagul <oaglagul@cittio.com>
1 sibling, 0 replies; 9+ messages in thread
From: Orhan Aglagul @ 2007-05-09 01:12 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re:
2007-05-08 22:59 (no subject) Orhan Aglagul <oaglagul@cittio.com>
@ 2007-05-09 00:22 ` Scott Marlowe <smarlowe@g2switchworks.com>
3 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2007-05-09 00:22 UTC (permalink / raw)
To: Orhan Aglagul <oaglagul@cittio.com>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re:
2007-05-08 22:59 (no subject) Orhan Aglagul <oaglagul@cittio.com>
@ 2007-05-09 00:30 ` Scott Marlowe <smarlowe@g2switchworks.com>
3 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2007-05-09 00:30 UTC (permalink / raw)
To: Orhan Aglagul <oaglagul@cittio.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re:
2007-05-08 22:59 (no subject) Orhan Aglagul <oaglagul@cittio.com>
@ 2007-05-09 04:14 ` Greg Smith <gsmith@gregsmith.com>
3 siblings, 0 replies; 9+ messages in thread
From: Greg Smith @ 2007-05-09 04:14 UTC (permalink / raw)
To: Orhan Aglagul <oaglagul@cittio.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-05-09 04:58 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-08 22:59 Orhan Aglagul <oaglagul@cittio.com>
2007-05-08 23:48 ` Re: Dan Harris <fbsd@drivefaster.net>
2007-05-09 00:05 ` Re: Joshua D. Drake <jd@commandprompt.com>
2007-05-09 00:20 ` Re: Carlos Moreno <moreno_pg@mochima.com>
2007-05-09 04:58 ` Re: Robert Treat <xzilla@users.sourceforge.net>
2007-05-09 01:12 ` Re: Orhan Aglagul <oaglagul@cittio.com>
2007-05-09 00:22 ` Re: Scott Marlowe <smarlowe@g2switchworks.com>
2007-05-09 00:30 ` Re: Scott Marlowe <smarlowe@g2switchworks.com>
2007-05-09 04:14 ` Re: Greg Smith <gsmith@gregsmith.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox