pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedThe results of my PostgreSQL/filesystem performance tests
26+ messages / 16 participants
[nested] [flat]
* The results of my PostgreSQL/filesystem performance tests
@ 2003-08-27 01:47 Bill Moran <wmoran@potentialtech.com>
0 siblings, 8 replies; 26+ messages in thread
From: Bill Moran @ 2003-08-27 01:47 UTC (permalink / raw)
To: pgsql-performance
Hey all.
I said I was going to do it, and I finally did it.
As with all performance tests/benchmarks, there are probably dozens or
more reasons why these results aren't as accurate or wonderful as they
should be. Take them for what they are and hopefully everyone can
learn a few things from them.
Intelligent feedback is welcome.
http://www.potentialtech.com/wmoran/postgresql.php
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 05:21 Balazs Wellisch <balazs@neusolutions.com>
parent: Bill Moran <wmoran@potentialtech.com>
7 siblings, 3 replies; 26+ messages in thread
From: Balazs Wellisch @ 2003-08-28 05:21 UTC (permalink / raw)
To: pgsql-performance
Bill,
Very interesting results. I'd like to command you on your honesty.
Having started out with the intentions of proving that FreeBSD is faster
than Linux only to find that the opposite is true must not have been
rewarding for you. However, these unexpected results serve only to
reinforce the integrity of your tests.
Thanks for all the work.
Balazs
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Bill Moran
Sent: Tuesday, August 26, 2003 6:48 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] The results of my PostgreSQL/filesystem performance
tests
Hey all.
I said I was going to do it, and I finally did it.
As with all performance tests/benchmarks, there are probably dozens or
more reasons why these results aren't as accurate or wonderful as they
should be. Take them for what they are and hopefully everyone can
learn a few things from them.
Intelligent feedback is welcome.
http://www.potentialtech.com/wmoran/postgresql.php
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 06:45 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Bill Moran <wmoran@potentialtech.com>
7 siblings, 1 reply; 26+ messages in thread
From: Shridhar Daithankar @ 2003-08-28 06:45 UTC (permalink / raw)
To: pgsql-performance
On 26 Aug 2003 at 21:47, Bill Moran wrote:
> Hey all.
>
> I said I was going to do it, and I finally did it.
>
> As with all performance tests/benchmarks, there are probably dozens or
> more reasons why these results aren't as accurate or wonderful as they
> should be. Take them for what they are and hopefully everyone can
> learn a few things from them.
>
> Intelligent feedback is welcome.
>
> http://www.potentialtech.com/wmoran/postgresql.php
Can we have these benchmarks and relevant information stored in a central
archive at techdocs(Say)?
That would be better than searching mail archives..
Bye
Shridhar
--
"... freedom ... is a worship word..." "It is our worship word too." -- Cloud
William and Kirk, "The Omega Glory", stardate unknown
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-28 08:11 Dennis Björklund <db@zigo.dhs.org>
parent: Bill Moran <wmoran@potentialtech.com>
7 siblings, 0 replies; 26+ messages in thread
From: Dennis Björklund @ 2003-08-28 08:11 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
On Tue, 26 Aug 2003, Bill Moran wrote:
> As with all performance tests/benchmarks, there are probably dozens or
> more reasons why these results aren't as accurate or wonderful as they
> should be. Take them for what they are and hopefully everyone can
> learn a few things from them.
What version of pg was used in debian and redhat? For freebsd it's 7.2.4
it says on the page, but I see nothing about the other two. The version
that comes with Redhat 9 (Shrike) is 7.3.2.
--
/Dennis
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-28 09:19 Ron Johnson <ron.l.johnson@cox.net>
parent: Bill Moran <wmoran@potentialtech.com>
7 siblings, 0 replies; 26+ messages in thread
From: Ron Johnson @ 2003-08-28 09:19 UTC (permalink / raw)
To: pgsql-performance
On Tue, 2003-08-26 at 20:47, Bill Moran wrote:
> Hey all.
>
> I said I was going to do it, and I finally did it.
>
> As with all performance tests/benchmarks, there are probably dozens or
> more reasons why these results aren't as accurate or wonderful as they
> should be. Take them for what they are and hopefully everyone can
> learn a few things from them.
>
> Intelligent feedback is welcome.
>
> http://www.potentialtech.com/wmoran/postgresql.php
Hi,
Woody has pg 7.2.1. Note also that Woody comes with kernel 2.4.18.
It would be interesting to see how Debian Sid (kernel 2.4.21 and
pg 7.3.3) would perform.
Thanks for the results!
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
"Oh, great altar of passive entertainment, bestow upon me thy
discordant images at such speed as to render linear thought impossible"
Calvin, regarding TV
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-28 09:23 Ron Johnson <ron.l.johnson@cox.net>
parent: Bill Moran <wmoran@potentialtech.com>
7 siblings, 1 reply; 26+ messages in thread
From: Ron Johnson @ 2003-08-28 09:23 UTC (permalink / raw)
To: pgsql-performance
On Tue, 2003-08-26 at 20:47, Bill Moran wrote:
> Hey all.
>
> I said I was going to do it, and I finally did it.
>
> As with all performance tests/benchmarks, there are probably dozens or
> more reasons why these results aren't as accurate or wonderful as they
> should be. Take them for what they are and hopefully everyone can
> learn a few things from them.
>
> Intelligent feedback is welcome.
>
> http://www.potentialtech.com/wmoran/postgresql.php
I notice that the Linux FSs weren't tested with noatime. Any
reason?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
"As I like to joke, I may have invented it, but Microsoft made
it popular"
David Bradley, regarding Ctrl-Alt-Del
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 11:48 Christopher Browne <cbbrowne@acm.org>
parent: Balazs Wellisch <balazs@neusolutions.com>
2 siblings, 2 replies; 26+ messages in thread
From: Christopher Browne @ 2003-08-28 11:48 UTC (permalink / raw)
To: pgsql-performance
A long time ago, in a galaxy far, far away, balazs@neusolutions.com ("Balazs Wellisch") wrote:
> Very interesting results. I'd like to command you on your honesty.
> Having started out with the intentions of proving that FreeBSD is faster
> than Linux only to find that the opposite is true must not have been
> rewarding for you. However, these unexpected results serve only to
> reinforce the integrity of your tests.
Well put.
To see a result that the tester didn't really want to see/present does
suggest good things about the tester's honesty. There was incentive
to hide unfavorable results.
What it still leaves quite open is just what happens when the OS has
more than one disk drive or CPU to play with. It's not clear what
happens in such cases, whether FreeBSD would catch up, or be "left
further in the dust." The traditional "propaganda" has been that
there are all sorts of reasons to expect PostgreSQL on FreeBSD to run
a bit faster than on Linux; it is a bit unexpected for the opposite to
seem true.
--
output = reverse("gro.mca" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/sap.html
"I am aware of the benefits of a micro kernel approach. However, the
fact remains that Linux is here, and GNU isn't --- and people have
been working on Hurd for a lot longer than Linus has been working on
Linux." -- Ted T'so, 1992.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 12:05 Ludek Finstrle <ludek.finstrle@pzkagis.cz>
parent: Bill Moran <wmoran@potentialtech.com>
7 siblings, 1 reply; 26+ messages in thread
From: Ludek Finstrle @ 2003-08-28 12:05 UTC (permalink / raw)
To: pgsql-performance; +Cc: Bill Moran <wmoran@potentialtech.com>
> Intelligent feedback is welcome.
>
> http://www.potentialtech.com/wmoran/postgresql.php
Good work. But I can't find information about xfs. Do you plan to add
this one FS in test?
Luf
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-28 12:25 Jeff <threshar@torgo.978.org>
parent: Bill Moran <wmoran@potentialtech.com>
7 siblings, 0 replies; 26+ messages in thread
From: Jeff @ 2003-08-28 12:25 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
On Tue, 26 Aug 2003, Bill Moran wrote:
>
> Intelligent feedback is welcome.
>
That's some good work there, Lou. You'll make sgt for that someday.
But I think the next step, before trying out other filesystems and options
would be concurrency. Run a bunch of these beasts together and see what
happens (I don't think too many of us have a single session running).
Perhaps even make them "interfere" with each other to create as much
"pain" as possible?
on a side note - I might be blind here - I didn't see what version of pg
you were using or any postgresql.conf tweaks - or did you just use
whatever came with each distro?
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 13:44 Rod Taylor <rbt@rbt.ca>
parent: Bill Moran <wmoran@potentialtech.com>
7 siblings, 0 replies; 26+ messages in thread
From: Rod Taylor @ 2003-08-28 13:44 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
Couple of questions:
What was the postgresql.conf configuration used? Default?
How many threads of the script ran? Looks like a single user only.
I assume there was nothing else running at the time (cron, sendmail,
etc. were all off?)
Do you know whether the machines were disk or I/O bound?
Was PostgreSQL compiled the same for each OS or did you use the rpm,
deb, tgz that were available?
On Tue, 2003-08-26 at 21:47, Bill Moran wrote:
> Hey all.
>
> I said I was going to do it, and I finally did it.
>
> As with all performance tests/benchmarks, there are probably dozens or
> more reasons why these results aren't as accurate or wonderful as they
> should be. Take them for what they are and hopefully everyone can
> learn a few things from them.
>
> Intelligent feedback is welcome.
>
> http://www.potentialtech.com/wmoran/postgresql.php
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-28 13:44 Tomka Gergely <tomka@zeus.gau.hu>
parent: Christopher Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 26+ messages in thread
From: Tomka Gergely @ 2003-08-28 13:44 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
2003-08-28 ragyogó napján Christopher Browne ezt üzente:
> A long time ago, in a galaxy far, far away, balazs@neusolutions.com ("Balazs Wellisch") wrote:
> > Very interesting results. I'd like to command you on your honesty.
> > Having started out with the intentions of proving that FreeBSD is faster
> > than Linux only to find that the opposite is true must not have been
> > rewarding for you. However, these unexpected results serve only to
> > reinforce the integrity of your tests.
>
> Well put.
>
> To see a result that the tester didn't really want to see/present does
> suggest good things about the tester's honesty. There was incentive
> to hide unfavorable results.
>
> What it still leaves quite open is just what happens when the OS has
> more than one disk drive or CPU to play with. It's not clear what
> happens in such cases, whether FreeBSD would catch up, or be "left
> further in the dust." The traditional "propaganda" has been that
> there are all sorts of reasons to expect PostgreSQL on FreeBSD to run
> a bit faster than on Linux; it is a bit unexpected for the opposite to
> seem true.
AFAIK *BSD better in the handling of big loads - maybe when multiple
concurrent tests run against a linux and a bsd box, we see better result.
Or not.
--
Tomka Gergely
"S most - vajon barbárok nélkül mi lesz velünk?
Ők mégiscsak megoldás voltak valahogy..."
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-28 13:45 Tomka Gergely <tomka@zeus.gau.hu>
parent: Ludek Finstrle <ludek.finstrle@pzkagis.cz>
0 siblings, 0 replies; 26+ messages in thread
From: Tomka Gergely @ 2003-08-28 13:45 UTC (permalink / raw)
To: Ludek Finstrle <ludek.finstrle@pzkagis.cz>; +Cc: pgsql-performance; Bill Moran <wmoran@potentialtech.com>
2003-08-28 ragyogó napján Ludek Finstrle ezt üzente:
> > Intelligent feedback is welcome.
> >
> > http://www.potentialtech.com/wmoran/postgresql.php
>
> Good work. But I can't find information about xfs. Do you plan to add
> this one FS in test?
http://mail.sth.sze.hu/~hsz/sql/
--
Tomka Gergely
"S most - vajon barbárok nélkül mi lesz velünk?
Ők mégiscsak megoldás voltak valahogy..."
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 16:12 Sean Chittenden <sean@chittenden.org>
parent: Christopher Browne <cbbrowne@acm.org>
1 sibling, 2 replies; 26+ messages in thread
From: Sean Chittenden @ 2003-08-28 16:12 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
> What it still leaves quite open is just what happens when the OS has
> more than one disk drive or CPU to play with. It's not clear what
> happens in such cases, whether FreeBSD would catch up, or be "left
> further in the dust." The traditional "propaganda" has been that
> there are all sorts of reasons to expect PostgreSQL on FreeBSD to
> run a bit faster than on Linux; it is a bit unexpected for the
> opposite to seem true.
Let me nip this in the butt before people run away with ideas that
aren't correct. When the tests were performed in FreeBSD 5.1 and
Linux, the hard drives were running UDMA. When running 4.8, for some
reason his drives settled in on PIO mode:
ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) falling back to PIO mode
The benchmarks were hardly conclusive as UDMA runs vastly faster than
PIO. Until we hear back as to whether cables were jarred loose
between the tests or hearing if something else changed, I'd hardly
consider these conclusive tests given PIO/UDMA is apples to oranges in
terms of speed and I fully expect that FreeBSD 4.8 will perform at
least faster than 5.1 (5.x is still being unwound from Giant), but
should out perform Linux as well if industry experience iss any
indicator.
-sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 18:17 Al Hulaton <ahulaton@commandprompt.com>
parent: Balazs Wellisch <balazs@neusolutions.com>
2 siblings, 0 replies; 26+ messages in thread
From: Al Hulaton @ 2003-08-28 18:17 UTC (permalink / raw)
To: pgsql-performance
> http://www.potentialtech.com/wmoran/postgresql.php
> --
> Bill Moran
> Potential Technologies
> http://www.potentialtech.com
Adding my voice to the many, thanks for sharing your results Bill. Very
instructive.
--
Best,
Al Hulaton | Sr. Account Engineer | Command Prompt, Inc.
503.222.2783 | ahulaton@commandprompt.com
Home of Mammoth PostgreSQL and 'Practical PostgreSQL'
Managed PostgreSQL, Linux services and consulting
Read and Search O'Reilly's 'Practical PostgreSQL' at
http://www.commandprompt.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-28 19:16 Bill Moran <wmoran@potentialtech.com>
parent: Sean Chittenden <sean@chittenden.org>
1 sibling, 1 reply; 26+ messages in thread
From: Bill Moran @ 2003-08-28 19:16 UTC (permalink / raw)
To: Sean Chittenden <sean@chittenden.org>; +Cc: pgsql-performance
I need to step in and do 2 things:
First, apologize for posting inaccurate test results.
Second, verify that Sean is absolutely correct. FreeBSD 4.8 was accessing
the drives in PIO mode, which is significantly lousier than DMA, which
RedHat was able to use. As a result, the tests are unreasonably skewed
in favor of Linux.
The only thing that the currently posted results prove is that Linux is
better at dealing with crappy hardware than BSD (which I feel we already
knew).
I did some rescrounging, and found some newer hardware stuffed in a
corner that I had forgotten was even around. I am currently re-running
the tests and will post new results as soon as there are enough to
be interesting to talk about.
In an attempt to avoid the same mistake, I did a timed test with dd(1)
to a raw partition on both Linux and FreeBSD to ensure that both systems
are able to access the hardware at more or less the same speed. The
results of this will be included.
I'm also gathering considerably more information about the state of
the system during the tests, which should answer a number of questions
I've been getting.
To the many people who asked questions like "why not try filesystem x
on distribution y" and similar questions, the answer in most cases is
time. I've pared the tests down some so they run faster, and I'm hoping
to be able to run more tests on more combinations of configurations as
a result. Also, I never intended for anyone to assume that I was _done_
testing, just that I had enough results for folks to talk about.
I'll post again when I have enough results to be interesting, until then,
I apologize again for the inaccurate results.
Sean Chittenden wrote:
>>What it still leaves quite open is just what happens when the OS has
>>more than one disk drive or CPU to play with. It's not clear what
>>happens in such cases, whether FreeBSD would catch up, or be "left
>>further in the dust." The traditional "propaganda" has been that
>>there are all sorts of reasons to expect PostgreSQL on FreeBSD to
>>run a bit faster than on Linux; it is a bit unexpected for the
>>opposite to seem true.
>
> Let me nip this in the butt before people run away with ideas that
> aren't correct. When the tests were performed in FreeBSD 5.1 and
> Linux, the hard drives were running UDMA. When running 4.8, for some
> reason his drives settled in on PIO mode:
>
> ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
> ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
> ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
> ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) falling back to PIO mode
>
> The benchmarks were hardly conclusive as UDMA runs vastly faster than
> PIO. Until we hear back as to whether cables were jarred loose
> between the tests or hearing if something else changed, I'd hardly
> consider these conclusive tests given PIO/UDMA is apples to oranges in
> terms of speed and I fully expect that FreeBSD 4.8 will perform at
> least faster than 5.1 (5.x is still being unwound from Giant), but
> should out perform Linux as well if industry experience iss any
> indicator.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 19:31 Sean Chittenden <sean@chittenden.org>
parent: Bill Moran <wmoran@potentialtech.com>
0 siblings, 3 replies; 26+ messages in thread
From: Sean Chittenden @ 2003-08-28 19:31 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
> I need to step in and do 2 things:
Thanks for posting that. Let me know if you have any questions while
doing your testing. I've found that using 16K blocks on FreeBSD
results in about an 8% speedup in writes to the database, fwiw.
I'm likely going to make this the default for PostgreSQL on FreeBSD
starting with 7.4 (just posted something to -hackers about this)f. If
you'd like to do this in your testing, just apply the following patch.
Right now PostgreSQL defaults to 8K blocks, but FreeBSD uses 16K
blocks which means that currently, reading two blocks of data in PG is
two read calls to the OS, one reads 16K of data off disk and returns
the 1st page, the 2nd call pulls the 2nd block from the FS cache. In
making things 16K, it avoids the need for the 2nd system call which is
where the performance difference is coming from, afaikt. -sc
--
Sean Chittenden
Attachments:
patch (text/plain, 546B) download
Index: src/include/pg_config_manual.h
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/src/include/pg_config_manual.h,v
retrieving revision 1.5
diff -u -r1.5 pg_config_manual.h
--- src/include/pg_config_manual.h 4 Aug 2003 00:43:29 -0000 1.5
+++ src/include/pg_config_manual.h 27 Aug 2003 17:40:12 -0000
@@ -23,7 +23,7 @@
*
* Changing BLCKSZ requires an initdb.
*/
-#define BLCKSZ 8192
+#define BLCKSZ 16384
/*
* RELSEG_SIZE is the maximum number of blocks allowed in one disk
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 20:20 Vivek Khera <khera@kcilink.com>
parent: Sean Chittenden <sean@chittenden.org>
2 siblings, 0 replies; 26+ messages in thread
From: Vivek Khera @ 2003-08-28 20:20 UTC (permalink / raw)
To: pgsql-performance
>>>>> "SC" == Sean Chittenden <sean@chittenden.org> writes:
>> I need to step in and do 2 things:
SC> Thanks for posting that. Let me know if you have any questions while
SC> doing your testing. I've found that using 16K blocks on FreeBSD
SC> results in about an 8% speedup in writes to the database, fwiw.
Where/how does one set this? In postgresql.conf or on the file system
or during compilation of postgres? I'm on FreeBSD 4.8 still.
I've got a box right now on which I'm comparing the speed merits of
hardware RAID10, RAID5, and RAID50 using a filesystem benchmark
utility (bonnie++). If I have time I'm gonna try different striping
block sizes. Right now I'm using 32k byte stripe size.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 20:27 Vivek Khera <khera@kcilink.com>
parent: Sean Chittenden <sean@chittenden.org>
2 siblings, 1 reply; 26+ messages in thread
From: Vivek Khera @ 2003-08-28 20:27 UTC (permalink / raw)
To: pgsql-performance
>>>>> "SC" == Sean Chittenden <sean@chittenden.org> writes:
>> I need to step in and do 2 things:
SC> Thanks for posting that. Let me know if you have any questions while
SC> doing your testing. I've found that using 16K blocks on FreeBSD
SC> results in about an 8% speedup in writes to the database, fwiw.
ok.. ignore my prior request about how to set that... i missed you had
included a patch.
Any recommendations on newfs parameters for an overly large file
system used solely for Postgres? Over 100Gb (with raid 10) or over
200Gb (with raid 5)?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-28 20:33 scott.marlowe <scott.marlowe@ihs.com>
parent: Sean Chittenden <sean@chittenden.org>
1 sibling, 0 replies; 26+ messages in thread
From: scott.marlowe @ 2003-08-28 20:33 UTC (permalink / raw)
To: Sean Chittenden <sean@chittenden.org>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance
On Thu, 28 Aug 2003, Sean Chittenden wrote:
> > What it still leaves quite open is just what happens when the OS has
> > more than one disk drive or CPU to play with. It's not clear what
> > happens in such cases, whether FreeBSD would catch up, or be "left
> > further in the dust." The traditional "propaganda" has been that
> > there are all sorts of reasons to expect PostgreSQL on FreeBSD to
> > run a bit faster than on Linux; it is a bit unexpected for the
> > opposite to seem true.
>
> Let me nip this in the butt before people run away with ideas that
> aren't correct. When the tests were performed in FreeBSD 5.1 and
> Linux, the hard drives were running UDMA. When running 4.8, for some
> reason his drives settled in on PIO mode:
>
> ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
> ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
> ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying
> ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) falling back to PIO mode
>
> The benchmarks were hardly conclusive as UDMA runs vastly faster than
> PIO. Until we hear back as to whether cables were jarred loose
> between the tests or hearing if something else changed, I'd hardly
> consider these conclusive tests given PIO/UDMA is apples to oranges in
> terms of speed and I fully expect that FreeBSD 4.8 will perform at
> least faster than 5.1 (5.x is still being unwound from Giant), but
> should out perform Linux as well if industry experience iss any
> indicator.
Plus, in most "real" servers you're gonna be running SCSI, so it might be
nice to see a test with a good SCSI controller (Symbios 875 is a nice
choice) and a couple hard drives, one each for WAL and data. This would
more closely resemble actual usage and there are likely to be fewer issues
with things like UDMA versus PIO on SCSI.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-28 21:49 Sean Chittenden <sean@chittenden.org>
parent: Vivek Khera <khera@kcilink.com>
0 siblings, 0 replies; 26+ messages in thread
From: Sean Chittenden @ 2003-08-28 21:49 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
> >> I need to step in and do 2 things:
> SC> Thanks for posting that. Let me know if you have any questions while
> SC> doing your testing. I've found that using 16K blocks on FreeBSD
> SC> results in about an 8% speedup in writes to the database, fwiw.
>
> ok.. ignore my prior request about how to set that... i missed you
> had included a patch.
>
> Any recommendations on newfs parameters for an overly large file
> system used solely for Postgres? Over 100Gb (with raid 10) or over
> 200Gb (with raid 5)?
Nope, you'll have to test and see. If you find something that works,
however, let me know. -sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-29 01:19 Bill Moran <wmoran@potentialtech.com>
parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 0 replies; 26+ messages in thread
From: Bill Moran @ 2003-08-29 01:19 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; +Cc: pgsql-performance
Shridhar Daithankar wrote:
> On 26 Aug 2003 at 21:47, Bill Moran wrote:
>
>
>>Hey all.
>>
>>I said I was going to do it, and I finally did it.
>>
>>As with all performance tests/benchmarks, there are probably dozens or
>>more reasons why these results aren't as accurate or wonderful as they
>>should be. Take them for what they are and hopefully everyone can
>>learn a few things from them.
>>
>>Intelligent feedback is welcome.
>>
>>http://www.potentialtech.com/wmoran/postgresql.php
>
>
> Can we have these benchmarks and relevant information stored in a central
> archive at techdocs(Say)?
>
> That would be better than searching mail archives..
I agree.
There doesn't seem to be a place on techdocs for benchmarks at the time.
Is there another part of the site that would be good for these to go?
I'll keep them posted on my site until a better location is found.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-29 01:40 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 1 reply; 26+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-29 01:40 UTC (permalink / raw)
To: Ron Johnson <ron.l.johnson@cox.net>; pgsql-performance
> > As with all performance tests/benchmarks, there are probably dozens or
> > more reasons why these results aren't as accurate or wonderful as they
> > should be. Take them for what they are and hopefully everyone can
> > learn a few things from them.
> >
> > Intelligent feedback is welcome.
> >
> > http://www.potentialtech.com/wmoran/postgresql.php
>
> I notice that the Linux FSs weren't tested with noatime. Any
> reason?
My friend, (a FreeBSD committer), was wondering what the results are if you
turn off softupdates (to match Linux default installation) and use noatime.
He also wonders how bug the default IO is?
Chris
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-29 01:59 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Sean Chittenden <sean@chittenden.org>
2 siblings, 1 reply; 26+ messages in thread
From: Christopher Kings-Lynne @ 2003-08-29 01:59 UTC (permalink / raw)
To: Sean Chittenden <sean@chittenden.org>; Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
> I'm likely going to make this the default for PostgreSQL on FreeBSD
> starting with 7.4 (just posted something to -hackers about this)f. If
> you'd like to do this in your testing, just apply the following patch.
>
> Right now PostgreSQL defaults to 8K blocks, but FreeBSD uses 16K
> blocks which means that currently, reading two blocks of data in PG is
> two read calls to the OS, one reads 16K of data off disk and returns
> the 1st page, the 2nd call pulls the 2nd block from the FS cache. In
> making things 16K, it avoids the need for the 2nd system call which is
> where the performance difference is coming from, afaikt. -sc
Are you _sure_ this won't cause any atomicity problems? Can FreeBSD write
16k as an atomic unit?
Chris
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance
@ 2003-08-29 02:15 Bill Moran <wmoran@potentialtech.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 26+ messages in thread
From: Bill Moran @ 2003-08-29 02:15 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Ron Johnson <ron.l.johnson@cox.net>; pgsql-performance
Christopher Kings-Lynne wrote:
>>>As with all performance tests/benchmarks, there are probably dozens or
>>>more reasons why these results aren't as accurate or wonderful as they
>>>should be. Take them for what they are and hopefully everyone can
>>>learn a few things from them.
>>>
>>>Intelligent feedback is welcome.
>>>
>>>http://www.potentialtech.com/wmoran/postgresql.php
>>
>>I notice that the Linux FSs weren't tested with noatime. Any
>>reason?
>
> My friend, (a FreeBSD committer), was wondering what the results are if you
> turn off softupdates (to match Linux default installation) and use noatime.
Keep an eye on the page. The test results will be posted shortly after I
finish them.
Keep in mind, I'm more interested in figuring out what can be done to make
Postgres _faster_, so tests along that line are going to have a higher
priority than ones that specifically compare "apples to apples" or anything
like that.
> He also wonders how bug the default IO is?
Huh?
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-30 02:32 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 26+ messages in thread
From: Bruce Momjian @ 2003-08-30 02:32 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Sean Chittenden <sean@chittenden.org>; Bill Moran <wmoran@potentialtech.com>; pgsql-performance
Christopher Kings-Lynne wrote:
> > I'm likely going to make this the default for PostgreSQL on FreeBSD
> > starting with 7.4 (just posted something to -hackers about this)f. If
> > you'd like to do this in your testing, just apply the following patch.
> >
> > Right now PostgreSQL defaults to 8K blocks, but FreeBSD uses 16K
> > blocks which means that currently, reading two blocks of data in PG is
> > two read calls to the OS, one reads 16K of data off disk and returns
> > the 1st page, the 2nd call pulls the 2nd block from the FS cache. In
> > making things 16K, it avoids the need for the 2nd system call which is
> > where the performance difference is coming from, afaikt. -sc
>
> Are you _sure_ this won't cause any atomicity problems? Can FreeBSD write
> 16k as an atomic unit?
We pre-modified page images to WAL before modifying the page. The disks
are only 512-byte blocks, so we don't rely on file system atomicity
anymore anyway.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: The results of my PostgreSQL/filesystem performance tests
@ 2003-08-30 02:34 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Balazs Wellisch <balazs@neusolutions.com>
2 siblings, 0 replies; 26+ messages in thread
From: Bruce Momjian @ 2003-08-30 02:34 UTC (permalink / raw)
To: Balazs Wellisch <balazs@neusolutions.com>; +Cc: pgsql-performance
Balazs Wellisch wrote:
>
> Bill,
>
> Very interesting results. I'd like to command you on your honesty.
> Having started out with the intentions of proving that FreeBSD is faster
> than Linux only to find that the opposite is true must not have been
> rewarding for you. However, these unexpected results serve only to
> reinforce the integrity of your tests.
Looking at the results, ext2 is out because it isn't crash safe, and I
have heard Reiser uses a lot more CPU to do its work. It does show
ext3 as slow, which was expected. Interesting how JFS came out, and XFS
would be interesting. And, of course, it is multiple backends that
really shows PostgreSQL off, so it could radically affect the results.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2003-08-30 02:34 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-27 01:47 The results of my PostgreSQL/filesystem performance tests Bill Moran <wmoran@potentialtech.com>
2003-08-28 05:21 ` Balazs Wellisch <balazs@neusolutions.com>
2003-08-28 11:48 ` Christopher Browne <cbbrowne@acm.org>
2003-08-28 13:44 ` Re: The results of my PostgreSQL/filesystem performance Tomka Gergely <tomka@zeus.gau.hu>
2003-08-28 16:12 ` Sean Chittenden <sean@chittenden.org>
2003-08-28 19:16 ` Re: The results of my PostgreSQL/filesystem performance Bill Moran <wmoran@potentialtech.com>
2003-08-28 19:31 ` Sean Chittenden <sean@chittenden.org>
2003-08-28 20:20 ` Vivek Khera <khera@kcilink.com>
2003-08-28 20:27 ` Vivek Khera <khera@kcilink.com>
2003-08-28 21:49 ` Sean Chittenden <sean@chittenden.org>
2003-08-29 01:59 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-30 02:32 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-28 20:33 ` Re: The results of my PostgreSQL/filesystem performance scott.marlowe <scott.marlowe@ihs.com>
2003-08-28 18:17 ` Al Hulaton <ahulaton@commandprompt.com>
2003-08-30 02:34 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-28 06:45 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-29 01:19 ` Re: The results of my PostgreSQL/filesystem performance Bill Moran <wmoran@potentialtech.com>
2003-08-28 08:11 ` Re: The results of my PostgreSQL/filesystem performance Dennis Björklund <db@zigo.dhs.org>
2003-08-28 09:19 ` Re: The results of my PostgreSQL/filesystem performance Ron Johnson <ron.l.johnson@cox.net>
2003-08-28 09:23 ` Re: The results of my PostgreSQL/filesystem performance Ron Johnson <ron.l.johnson@cox.net>
2003-08-29 01:40 ` Re: The results of my PostgreSQL/filesystem performance Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-29 02:15 ` Re: The results of my PostgreSQL/filesystem performance Bill Moran <wmoran@potentialtech.com>
2003-08-28 12:05 ` Ludek Finstrle <ludek.finstrle@pzkagis.cz>
2003-08-28 13:45 ` Re: The results of my PostgreSQL/filesystem performance Tomka Gergely <tomka@zeus.gau.hu>
2003-08-28 12:25 ` Re: The results of my PostgreSQL/filesystem performance Jeff <threshar@torgo.978.org>
2003-08-28 13:44 ` Rod Taylor <rbt@rbt.ca>
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