pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbad performance on Solaris 10
30+ messages / 10 participants
[nested] [flat]
* bad performance on Solaris 10
@ 2006-04-04 00:39 Chris Mair <list@1006.org>
0 siblings, 3 replies; 30+ messages in thread
From: Chris Mair @ 2006-04-04 00:39 UTC (permalink / raw)
To: pgsql-performance
Hi,
I've got a somewhat puzzling performance problem here.
I'm trying to do a few tests with PostgreSQL 8.1.3 under Solaris
(an OS I'm sort of a newbie in).
The machine is a X4100 and the OS is Solaris 10 1/06 fresh install
according to manual. It's got two SAS disks in RAID 1, 4GB of RAM.
Now the problem is: this box is *much* slower than I expect.
I've got a libpg test program that happily inserts data
using PQputCopyData().
It performs an order of magnitude worse than the same thing
on a small Sun (Ultra20) running Linux. Or 4 times slower than
an iBook (sic!) running MacOS X.
So, I've this very bad feeling that there is something basic
I'm missing here.
Following are some stats:
"sync; dd; sync" show these disks write at 53 MB/s => good.
iostat 1 while my test is running says:
tty sd0 sd1 sd2 sd5
cpu
tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy
wt id
1 57 0 0 0 0 0 0 0 0 0 1809 23 70 0
1 0 99
0 235 0 0 0 0 0 0 0 0 0 2186 223 14 1
1 0 99
0 81 0 0 0 0 0 0 0 0 0 2488 251 13 1
1 0 98
0 81 0 0 0 0 0 0 0 0 0 2296 232 15 1
0 0 99
0 81 0 0 0 0 0 0 0 0 0 2416 166 9 1
0 0 98
0 81 0 0 0 0 0 0 0 0 0 2528 218 14 1
1 0 99
0 81 0 0 0 0 0 0 0 0 0 2272 223 15 1
0 0 99
If I interpret this correctly the disk writes at not more than 2.5
MB/sec while the Opterons do nothing => this is bad.
I've tried both, a hand compile with gcc and the solarispackages
from pgfoundry.org => same result.
Eons ago PCs had those "turbo" switches (it was never totally clear
why they put them there in the first place, anyway). I've this bad
feeling there's a secret "turbo" switch I can't spot hidden somewhere
in Solaris :/
Bye, Chris.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-04 00:49 Josh Berkus <josh@agliodbs.com>
parent: Chris Mair <list@1006.org>
2 siblings, 1 reply; 30+ messages in thread
From: Josh Berkus @ 2006-04-04 00:49 UTC (permalink / raw)
To: pgsql-performance; +Cc: Chris Mair <list@1006.org>
Chris,
> Eons ago PCs had those "turbo" switches (it was never totally clear
> why they put them there in the first place, anyway). I've this bad
> feeling there's a secret "turbo" switch I can't spot hidden somewhere
> in Solaris :/
Yes. Check out Jignesh's configuration advice .... ach, this is down.
Hold on, I will get you instructions on how to turn on filesystem caching
and readahead in Solaris.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-04 03:46 Luke Lonergan <llonergan@greenplum.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 30+ messages in thread
From: Luke Lonergan @ 2006-04-04 03:46 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; pgsql-performance; +Cc: Chris Mair <list@1006.org>
Jignesh¹s blog has some of the good stuff in it:
http://blogs.sun.com/roller/page/jkshah
- Luke
On 4/3/06 5:49 PM, "Josh Berkus" <josh@agliodbs.com> wrote:
> Chris,
>
>> > Eons ago PCs had those "turbo" switches (it was never totally clear
>> > why they put them there in the first place, anyway). I've this bad
>> > feeling there's a secret "turbo" switch I can't spot hidden somewhere
>> > in Solaris :/
>
> Yes. Check out Jignesh's configuration advice .... ach, this is down.
> Hold on, I will get you instructions on how to turn on filesystem caching
> and readahead in Solaris.
>
> --
> --Josh
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-04 05:12 Mark Kirkwood <markir@paradise.net.nz>
parent: Chris Mair <list@1006.org>
2 siblings, 1 reply; 30+ messages in thread
From: Mark Kirkwood @ 2006-04-04 05:12 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; +Cc: pgsql-performance
Chris Mair wrote:
> Hi,
>
> I've got a somewhat puzzling performance problem here.
>
> I'm trying to do a few tests with PostgreSQL 8.1.3 under Solaris
> (an OS I'm sort of a newbie in).
>
> The machine is a X4100 and the OS is Solaris 10 1/06 fresh install
> according to manual. It's got two SAS disks in RAID 1, 4GB of RAM.
>
> Now the problem is: this box is *much* slower than I expect.
>
> I've got a libpg test program that happily inserts data
> using PQputCopyData().
>
> It performs an order of magnitude worse than the same thing
> on a small Sun (Ultra20) running Linux. Or 4 times slower than
> an iBook (sic!) running MacOS X.
>
> So, I've this very bad feeling that there is something basic
> I'm missing here.
>
> Following are some stats:
>
> "sync; dd; sync" show these disks write at 53 MB/s => good.
>
> iostat 1 while my test is running says:
>
> tty sd0 sd1 sd2 sd5
> cpu
> tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy
> wt id
> 1 57 0 0 0 0 0 0 0 0 0 1809 23 70 0
> 1 0 99
> 0 235 0 0 0 0 0 0 0 0 0 2186 223 14 1
> 1 0 99
> 0 81 0 0 0 0 0 0 0 0 0 2488 251 13 1
> 1 0 98
> 0 81 0 0 0 0 0 0 0 0 0 2296 232 15 1
> 0 0 99
> 0 81 0 0 0 0 0 0 0 0 0 2416 166 9 1
> 0 0 98
> 0 81 0 0 0 0 0 0 0 0 0 2528 218 14 1
> 1 0 99
> 0 81 0 0 0 0 0 0 0 0 0 2272 223 15 1
> 0 0 99
>
> If I interpret this correctly the disk writes at not more than 2.5
> MB/sec while the Opterons do nothing => this is bad.
>
> I've tried both, a hand compile with gcc and the solarispackages
> from pgfoundry.org => same result.
>
> Eons ago PCs had those "turbo" switches (it was never totally clear
> why they put them there in the first place, anyway). I've this bad
> feeling there's a secret "turbo" switch I can't spot hidden somewhere
> in Solaris :/
>
I ran across something like this on a Solaris 8, RAID1 system, and
switching off logging on filesystem containing postgres made a huge
difference!
Now solaris 8 is ancient history, however see:
http://bugs.opensolaris.org/bugdatabase/view_bug.do?bug_id=6238533
Apparently there can still be issues with logging without forcedirectio
(which is the default I think).
I suspect that making a *separate* filesystem for the pg_xlog directory
and mounting that logging + forcedirectio would be a nice way to also
get performance while keeping the advantages of logging + file
buffercache for the *rest* of the postgres components.
Cheers
Mark
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-04 06:09 Josh Berkus <josh@agliodbs.com>
parent: Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 0 replies; 30+ messages in thread
From: Josh Berkus @ 2006-04-04 06:09 UTC (permalink / raw)
To: pgsql-performance; +Cc: Mark Kirkwood <markir@paradise.net.nz>; Chris Mair <list@1006.org>
Mark,
> I suspect that making a *separate* filesystem for the pg_xlog directory
> and mounting that logging + forcedirectio would be a nice way to also
> get performance while keeping the advantages of logging + file
> buffercache for the *rest* of the postgres components.
> Cheers
Yes, we tested this. It makes a huge difference in WAL speed.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-05 21:31 Chris Mair <list@1006.org>
parent: Chris Mair <list@1006.org>
2 siblings, 4 replies; 30+ messages in thread
From: Chris Mair @ 2006-04-05 21:31 UTC (permalink / raw)
To: pgsql-performance; +Cc: markir@paradise.net.nz; josh@agliodbs.com; llonergan@greenplum.com
Hi,
thanks for all replys.
I've done a few tests.
Remounting the fs where $PGDATA lives with "forcedirectio"
(together with logging, that is default) did not help
(if not harm...) performance.
Doing what http://blogs.sun.com/roller/page/jkshah suggests:
wal_sync_method = fsync (unchanged)
wal_buffers = 128 (was 8)
checkpoint_segments = 128 (was 3)
bgwriter_all_percent = 0 (was 0.333)
bgwriter_all_maxpages = 0 (was 5)
and leaving everything else default (solarispackages from pgfoundry)
increased performance ~ 7 times!
Playing around with these modifications I find that it's
actually just the
wal_buffers = 128
alone which makes all the difference!
Quickly playing around with wal_buffers on Linux and Mac OS X
I see it influences the performance of my test a bit, maybe in the
10-20% range (I'm really doing quick tests, nothing systematic),
but nowhere near as spectacularly as on Solaris.
I'm happy so far, but I find it very surprising that this single
parameter has such an impact (only on) Solaris 10.
(my test program is a bulk inserts using PQputCopyData in large
transactions - all test were 8.1.3).
Bye, Chris
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-05 21:40 Luke Lonergan <llonergan@greenplum.com>
parent: Chris Mair <list@1006.org>
3 siblings, 1 reply; 30+ messages in thread
From: Luke Lonergan @ 2006-04-05 21:40 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; pgsql-performance; +Cc: markir@paradise.net.nz, "Josh Berkus" <josh@agliodbs.com>
Chris,
On 4/5/06 2:31 PM, "Chris Mair" <list@1006.org> wrote:
> Doing what http://blogs.sun.com/roller/page/jkshah suggests:
> wal_sync_method = fsync (unchanged)
> wal_buffers = 128 (was 8)
> checkpoint_segments = 128 (was 3)
> bgwriter_all_percent = 0 (was 0.333)
> bgwriter_all_maxpages = 0 (was 5)
> and leaving everything else default (solarispackages from pgfoundry)
> increased performance ~ 7 times!
In the recent past, Jignesh Shaw of Sun MDE discovered that changing the
bgwriter_* parameters to zero had a dramatic positive impact on performance.
There are also some critical UFS kernel tuning parameters to set, you should
find those in his blog.
We found and fixed some libpq issues with Solaris that were also critical -
they should be in 8.1.3 I think.
- Luke
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-05 21:48 Alvaro Herrera <alvherre@commandprompt.com>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 2 replies; 30+ messages in thread
From: Alvaro Herrera @ 2006-04-05 21:48 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Chris Mair <list@1006.org>; pgsql-performance@postgresql.org, markir@paradise.net.nz, Josh Berkus <josh@agliodbs.com>
Luke Lonergan wrote:
> Chris,
>
> On 4/5/06 2:31 PM, "Chris Mair" <list@1006.org> wrote:
>
> > Doing what http://blogs.sun.com/roller/page/jkshah suggests:
> > wal_sync_method = fsync (unchanged)
> > wal_buffers = 128 (was 8)
> > checkpoint_segments = 128 (was 3)
> > bgwriter_all_percent = 0 (was 0.333)
> > bgwriter_all_maxpages = 0 (was 5)
> > and leaving everything else default (solarispackages from pgfoundry)
> > increased performance ~ 7 times!
>
> In the recent past, Jignesh Shaw of Sun MDE discovered that changing the
> bgwriter_* parameters to zero had a dramatic positive impact on performance.
This essentially means stopping all bgwriter activity, thereby deferring
all I/O until checkpoint. Was this considered? With
checkpoint_segments to 128, it wouldn't surprise me that there wasn't
any checkpoint executed at all during the whole test ...
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-05 21:56 Luke Lonergan <llonergan@greenplum.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 30+ messages in thread
From: Luke Lonergan @ 2006-04-05 21:56 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Chris Mair <list@1006.org>; pgsql-performance@postgresql.org, markir@paradise.net.nz, "Josh Berkus" <josh@agliodbs.com>
Alvaro,
On 4/5/06 2:48 PM, "Alvaro Herrera" <alvherre@commandprompt.com> wrote:
> This essentially means stopping all bgwriter activity, thereby deferring
> all I/O until checkpoint. Was this considered? With
> checkpoint_segments to 128, it wouldn't surprise me that there wasn't
> any checkpoint executed at all during the whole test ...
Yes, many things about the Solaris UFS filesystem caused a great deal of
pain over the 10 months of experiments we ran with Sun MDE. Ultimately, the
conclusion was that ZFS is going to make all of the pain go away.
In the meantime, all you can do is tweak up UFS and avoid I/O as much as
possible.
- Luke
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-05 23:13 Chris Mair <list@1006.org>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 30+ messages in thread
From: Chris Mair @ 2006-04-05 23:13 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; pgsql-performance@postgresql.org, markir@paradise.net.nz, Josh Berkus <josh@agliodbs.com>
> > > Doing what http://blogs.sun.com/roller/page/jkshah suggests:
> > > wal_sync_method = fsync (unchanged)
> > > wal_buffers = 128 (was 8)
> > > checkpoint_segments = 128 (was 3)
> > > bgwriter_all_percent = 0 (was 0.333)
> > > bgwriter_all_maxpages = 0 (was 5)
> > > and leaving everything else default (solarispackages from pgfoundry)
> > > increased performance ~ 7 times!
Ok, so I could quite believe my own benchmarks and I decided
to do a fresh initdb and retry everything.
At first it looked like I coudn't reproduce the speed up I just saw.
Then I realized it was the
wal_sync_method = fsync
line that makes all the difference!
Normally parameters that are commented are default values, but for
wal_sync_method it actually says (note the comment):
wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
So Im my last mail I drew the wrong conclusion, because i didn't comment
wal_sync_method to double check.
To the point: the default wal_sync_method choosen on Solaris 10 appears
to be a very bad one - for me, picking fsync increases performance ~
times 7, all other parameters unchanged!
Would it be a good idea to change this in the default install?
Bye, Chris.
PS: yes I did a fresh initdb again to double check ;)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-05 23:25 Mark Kirkwood <markir@paradise.net.nz>
parent: Chris Mair <list@1006.org>
3 siblings, 1 reply; 30+ messages in thread
From: Mark Kirkwood @ 2006-04-05 23:25 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; +Cc: pgsql-performance; josh@agliodbs.com; llonergan@greenplum.com
Chris Mair wrote:
> Hi,
>
> thanks for all replys.
>
> I've done a few tests.
>
> Remounting the fs where $PGDATA lives with "forcedirectio"
> (together with logging, that is default) did not help
> (if not harm...) performance.
>
>
Sure - forcedirectio on the entire $PGDATA is a definite loss, you only
want it on $PGDATA/pg_xlog. The usual way this is accomplished is by
making a separate filsystem for pg_xlog and symlinking from $PGDATA.
Did you try the other option of remounting the fs for $PGDATA without
logging or forcedirectio?
Cheers
Mark
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-05 23:29 Chris Mair <list@1006.org>
parent: Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 30+ messages in thread
From: Chris Mair @ 2006-04-05 23:29 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: pgsql-performance; josh@agliodbs.com; llonergan@greenplum.com
> > I've done a few tests.
> >
> > Remounting the fs where $PGDATA lives with "forcedirectio"
> > (together with logging, that is default) did not help
> > (if not harm...) performance.
> >
> >
>
> Sure - forcedirectio on the entire $PGDATA is a definite loss, you only
> want it on $PGDATA/pg_xlog. The usual way this is accomplished is by
> making a separate filsystem for pg_xlog and symlinking from $PGDATA.
>
> Did you try the other option of remounting the fs for $PGDATA without
> logging or forcedirectio?
not yet, I'm not on the final disk set yet.
when I get there I'll have two separate filesystems for pg_xlog and base
and will try what you suggest.
(but note the other mail about wal_sync_method = fsync)
bye, chris.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-05 23:33 Chris Mair <list@1006.org>
parent: Chris Mair <list@1006.org>
3 siblings, 0 replies; 30+ messages in thread
From: Chris Mair @ 2006-04-05 23:33 UTC (permalink / raw)
To: pgsql-performance
appears this didn't make it to the list... resending to the list
directly...
---
> > > Doing what http://blogs.sun.com/roller/page/jkshah suggests:
> > > wal_sync_method = fsync (unchanged)
> > > wal_buffers = 128 (was 8)
> > > checkpoint_segments = 128 (was 3)
> > > bgwriter_all_percent = 0 (was 0.333)
> > > bgwriter_all_maxpages = 0 (was 5)
> > > and leaving everything else default (solarispackages from
pgfoundry)
> > > increased performance ~ 7 times!
Ok, so I could quite believe my own benchmarks and I decided
to do a fresh initdb and retry everything.
At first it looked like I coudn't reproduce the speed up I just saw.
Then I realized it was the
wal_sync_method = fsync
line that makes all the difference!
Normally parameters that are commented are default values, but for
wal_sync_method it actually says (note the comment):
wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
So Im my last mail I drew the wrong conclusion, because i didn't comment
wal_sync_method to double check.
To the point: the default wal_sync_method choosen on Solaris 10 appears
to be a very bad one - for me, picking fsync increases performance ~
times 7, all other parameters unchanged!
Would it be a good idea to change this in the default install?
Bye, Chris.
PS: yes I did a fresh initdb again to double check ;)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-06 01:23 Mark Kirkwood <markir@paradise.net.nz>
parent: Chris Mair <list@1006.org>
0 siblings, 1 reply; 30+ messages in thread
From: Mark Kirkwood @ 2006-04-06 01:23 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; +Cc: pgsql-performance; josh@agliodbs.com; llonergan@greenplum.com
Chris Mair wrote:
>
> (but note the other mail about wal_sync_method = fsync)
>
Yeah - looks good! (is the default open_datasync still?). Might be worth
trying out the fdatasync method too (ISTR this being quite good... again
on Solaris 8, so things might have changed)!
Cheers
Mark
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-06 04:32 Josh Berkus <josh@agliodbs.com>
parent: Chris Mair <list@1006.org>
3 siblings, 0 replies; 30+ messages in thread
From: Josh Berkus @ 2006-04-06 04:32 UTC (permalink / raw)
To: pgsql-performance; +Cc: Chris Mair <list@1006.org>; markir@paradise.net.nz; llonergan@greenplum.com
Chris,
> Remounting the fs where $PGDATA lives with "forcedirectio"
> (together with logging, that is default) did not help
> (if not harm...) performance.
Not all of PG. JUST pg_xlog. forcedirectio is only a good idea for the xlog.
> Quickly playing around with wal_buffers on Linux and Mac OS X
> I see it influences the performance of my test a bit, maybe in the
> 10-20% range (I'm really doing quick tests, nothing systematic),
> but nowhere near as spectacularly as on Solaris.
>
> I'm happy so far, but I find it very surprising that this single
> parameter has such an impact (only on) Solaris 10.
That *is* interesting. I hadn't tested this previously specifically on
Solaris.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-06 04:35 Josh Berkus <josh@agliodbs.com>
parent: Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 30+ messages in thread
From: Josh Berkus @ 2006-04-06 04:35 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: Chris Mair <list@1006.org>; pgsql-performance; llonergan@greenplum.com
Mark, Chris,
> Yeah - looks good! (is the default open_datasync still?). Might be worth
> trying out the fdatasync method too (ISTR this being quite good... again
> on Solaris 8, so things might have changed)!
I was just talking to a member of the Solaris-UFS team who recommended that we
test fdatasync.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-06 06:01 Chris Mair <list@1006.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 30+ messages in thread
From: Chris Mair @ 2006-04-06 06:01 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Mark Kirkwood <markir@paradise.net.nz>; pgsql-performance; llonergan@greenplum.com
> > Yeah - looks good! (is the default open_datasync still?). Might be worth
> > trying out the fdatasync method too (ISTR this being quite good... again
> > on Solaris 8, so things might have changed)!
>
> I was just talking to a member of the Solaris-UFS team who recommended that we
> test fdatasync.
Ok, so I did a few runs for each of the sync methods, keeping all the
rest constant and got this:
open_datasync 0.7
fdatasync 4.6
fsync 4.5
fsync_writethrough not supported
open_sync 0.6
in arbitrary units - higher is faster.
Quite impressive!
Bye, Chris.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-06 07:36 Robert Lor <Robert.Lor@Sun.COM>
parent: Chris Mair <list@1006.org>
0 siblings, 1 reply; 30+ messages in thread
From: Robert Lor @ 2006-04-06 07:36 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; +Cc: Josh Berkus <josh@agliodbs.com>; Mark Kirkwood <markir@paradise.net.nz>; pgsql-performance; llonergan@greenplum.com
Chris Mair wrote:
>Ok, so I did a few runs for each of the sync methods, keeping all the
>rest constant and got this:
>
>open_datasync 0.7
>fdatasync 4.6
>fsync 4.5
>fsync_writethrough not supported
>open_sync 0.6
>
>in arbitrary units - higher is faster.
>
>Quite impressive!
>
>
>
>
Chris,
Just to make sure the x4100 config is similar to your Linux system, can
you verify the default setting for disk write cache and make sure they
are both enabled or disabled. Here's how to check in Solaris.
As root, run "format -e" -> pick a disk -> cache -> write_cache -> display
Not sure how to do it on Linux though!
Regards,
-Robert
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-07 19:29 Chris Mair <list@1006.org>
parent: Robert Lor <Robert.Lor@Sun.COM>
0 siblings, 1 reply; 30+ messages in thread
From: Chris Mair @ 2006-04-07 19:29 UTC (permalink / raw)
To: Robert Lor <Robert.Lor@Sun.COM>; +Cc: Josh Berkus <josh@agliodbs.com>; Mark Kirkwood <markir@paradise.net.nz>; pgsql-performance; llonergan@greenplum.com
> >Ok, so I did a few runs for each of the sync methods, keeping all the
> >rest constant and got this:
> >
> >open_datasync 0.7
> >fdatasync 4.6
> >fsync 4.5
> >fsync_writethrough not supported
> >open_sync 0.6
> >
> >in arbitrary units - higher is faster.
> >
> >Quite impressive!
> >
> >
> >
> >
> Chris,
> Just to make sure the x4100 config is similar to your Linux system, can
> you verify the default setting for disk write cache and make sure they
> are both enabled or disabled. Here's how to check in Solaris.
> As root, run "format -e" -> pick a disk -> cache -> write_cache -> display
>
> Not sure how to do it on Linux though!
>
> Regards,
> -Robert
I don't have access to the machine for the next few days due to eh...
let's call it firewall accident ;), but it might very well be that it
was off on the x4100 (I know it's on the smaller Linux box).
That together with the bad default sync method can definitely explain
the strangely slow out of box performance I got.
So thanks again for explaining this to me :)
Bye, Chris.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-10 21:05 Chris Mair <list@1006.org>
parent: Chris Mair <list@1006.org>
0 siblings, 0 replies; 30+ messages in thread
From: Chris Mair @ 2006-04-10 21:05 UTC (permalink / raw)
To: Robert Lor <Robert.Lor@Sun.COM>; +Cc: Josh Berkus <josh@agliodbs.com>; Mark Kirkwood <markir@paradise.net.nz>; pgsql-performance; llonergan@greenplum.com
> > Chris,
> > Just to make sure the x4100 config is similar to your Linux system, can
> > you verify the default setting for disk write cache and make sure they
> > are both enabled or disabled. Here's how to check in Solaris.
> > As root, run "format -e" -> pick a disk -> cache -> write_cache -> display
> >
> > Not sure how to do it on Linux though!
> >
> > Regards,
> > -Robert
>
> I don't have access to the machine for the next few days due to eh...
> let's call it firewall accident ;), but it might very well be that it
> was off on the x4100 (I know it's on the smaller Linux box).
>
> That together with the bad default sync method can definitely explain
> the strangely slow out of box performance I got.
>
> So thanks again for explaining this to me :)
>
> Bye, Chris.
Just for completeness:
I checked now using the above commands and can confirm the write cache
was disabled on the x4100 and was on on Linux.
Bye, Chris.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-12 19:56 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 2 replies; 30+ messages in thread
From: Bruce Momjian @ 2006-04-12 19:56 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Chris Mair <list@1006.org>; pgsql-performance@postgresql.org, markir@paradise.net.nz, Josh Berkus <josh@agliodbs.com>
Luke Lonergan wrote:
> Alvaro,
>
> On 4/5/06 2:48 PM, "Alvaro Herrera" <alvherre@commandprompt.com> wrote:
>
> > This essentially means stopping all bgwriter activity, thereby deferring
> > all I/O until checkpoint. Was this considered? With
> > checkpoint_segments to 128, it wouldn't surprise me that there wasn't
> > any checkpoint executed at all during the whole test ...
>
> Yes, many things about the Solaris UFS filesystem caused a great deal of
> pain over the 10 months of experiments we ran with Sun MDE. Ultimately, the
> conclusion was that ZFS is going to make all of the pain go away.
>
> In the meantime, all you can do is tweak up UFS and avoid I/O as much as
> possible.
It is hard to imagine why people spend so much time modifying Sun
machines run with acceptable performance when non-Sun operating systems
work fine without such hurtles.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-12 22:35 Luke Lonergan <llonergan@greenplum.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 30+ messages in thread
From: Luke Lonergan @ 2006-04-12 22:35 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Chris Mair <list@1006.org>; pgsql-performance@postgresql.org, markir@paradise.net.nz, "Josh Berkus" <josh@agliodbs.com>
Bruce,
On 4/12/06 12:56 PM, "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:
> It is hard to imagine why people spend so much time modifying Sun
> machines run with acceptable performance when non-Sun operating systems
> work fine without such hurtles.
There are a lot of Solaris customers that we support and that we'd like to
support. To many of them, Solaris has many advantages other than speed,
though they expect a reasonably comparable performance, perhaps within a
factor of 2 of other options.
Oracle has spent a great deal of time (a decade!) optimizing their software
for Solaris, and it shows. There are also some typical strategies that
Solaris people used to use to make Solaris perform better, like using VxFS
(Veritas Filesystem), or Oracle Raw IO to make their systems perform better.
Lately I find people are not so receptive to VxFS, and Sun is promoting ZFS,
and we don't have a reasonable near term option for Raw IO in Postgres, so
we need to work to find a reasonable path for Solaris users IMO. The long
delays in ZFS production haven't helped us there, as the problems with UFS
are severe.
We at Greenplum have worked hard over the last year to find options for
Postgres on Solaris and have the best configuration setup that we think is
possible now on UFS, and our customers benefit from that. However, Linux on
XFS or even ext3 is definitely the performance leader.
- Luke
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-12 22:49 Josh Berkus <josh@agliodbs.com>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 30+ messages in thread
From: Josh Berkus @ 2006-04-12 22:49 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
People,
> Lately I find people are not so receptive to VxFS, and Sun is promoting
> ZFS, and we don't have a reasonable near term option for Raw IO in
> Postgres, so we need to work to find a reasonable path for Solaris users
> IMO. The long delays in ZFS production haven't helped us there, as the
> problems with UFS are severe.
FWIW, I'm testing on ZFS now. But it's not stable yet. People are welcome
to join the Solaris 11 beta program.
In the near term, there are fixes to be made both in PostgreSQL
configuration and in Solaris configuration. Also, some of the work being
done for 8.2 ... the external sort work done by Simon and sponsored by
GreenPlum, and the internal sort work which Jonah and others are doing ...
will improve things on Solaris as our sort issues hit Solaris harder than
other OSes.
Expect lots more info on performance config for Solaris from me & Robert in
the next few weeks.
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-13 01:53 Jignesh K. Shah <J.K.Shah@Sun.COM>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 2 replies; 30+ messages in thread
From: Jignesh K. Shah @ 2006-04-13 01:53 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Alvaro Herrera <alvherre@commandprompt.com>; Chris Mair <list@1006.org>; pgsql-performance@postgresql.org, markir@paradise.net.nz, Josh Berkus <josh@agliodbs.com>
Bruce,
Hard to answer that... People like me who know and love PostgreSQL and
Solaris finds this as an opportunity to make their favorite database
work best on their favorite operating system.
Many times PostgreSQL has many things based on assumption that it will
run on Linux and it is left to Solaris to emulate that behavior.That
said there are ways to improve performance even on UFS on Solaris, it
just requires more tweaks.
Hopefully this will lead to few Solaris friendly default values like
fsync/odatasync :-)
Regards,
Jignesh
Bruce Momjian wrote:
>
>It is hard to imagine why people spend so much time modifying Sun
>machines run with acceptable performance when non-Sun operating systems
>work fine without such hurtles.
>
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-13 04:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jignesh K. Shah <J.K.Shah@Sun.COM>
1 sibling, 0 replies; 30+ messages in thread
From: Tom Lane @ 2006-04-13 04:52 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@Sun.COM>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Luke Lonergan <llonergan@greenplum.com>; Alvaro Herrera <alvherre@commandprompt.com>; Chris Mair <list@1006.org>; pgsql-performance@postgresql.org, markir@paradise.net.nz, Josh Berkus <josh@agliodbs.com>
"Jignesh K. Shah" <J.K.Shah@Sun.COM> writes:
> Many times PostgreSQL has many things based on assumption that it will
> run on Linux and it is left to Solaris to emulate that behavior.
Au contraire --- PG tries its best to be OS-agnostic. I've personally
resisted people trying to optimize it by putting in Linux-specific
behavior. The above sounds to me like making excuses for a poor OS.
(And yes, I will equally much resist any requests to put in Solaris-
specific behavior...)
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-13 08:39 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Jignesh K. Shah <J.K.Shah@Sun.COM>
1 sibling, 2 replies; 30+ messages in thread
From: Bruce Momjian @ 2006-04-13 08:39 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@Sun.COM>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Alvaro Herrera <alvherre@commandprompt.com>; Chris Mair <list@1006.org>; pgsql-performance@postgresql.org, markir@paradise.net.nz, Josh Berkus <josh@agliodbs.com>
Jignesh K. Shah wrote:
>
> Bruce,
>
> Hard to answer that... People like me who know and love PostgreSQL and
> Solaris finds this as an opportunity to make their favorite database
> work best on their favorite operating system.
>
> Many times PostgreSQL has many things based on assumption that it will
> run on Linux and it is left to Solaris to emulate that behavior.That
> said there are ways to improve performance even on UFS on Solaris, it
> just requires more tweaks.
>
> Hopefully this will lead to few Solaris friendly default values like
> fsync/odatasync :-)
Yes, if someone wants to give us a clear answer on which wal_sync method
is best on all versions of Solaris, we can easily make that change.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-13 17:40 Robert Lor <Robert.Lor@Sun.COM>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 30+ messages in thread
From: Robert Lor @ 2006-04-13 17:40 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jignesh K. Shah <J.K.Shah@Sun.COM>; Luke Lonergan <llonergan@greenplum.com>; Alvaro Herrera <alvherre@commandprompt.com>; Chris Mair <list@1006.org>; pgsql-performance@postgresql.org, markir@paradise.net.nz, Josh Berkus <josh@agliodbs.com>
Bruce Momjian wrote On 04/13/06 01:39 AM,:
>
> Yes, if someone wants to give us a clear answer on which wal_sync method
> is best on all versions of Solaris, we can easily make that change.
>
We're doing tests to see how various parameters in postgresql.conf
affect performance on Solaris and will share the results shortly.
Regards,
-Robert
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-13 19:38 Merlin Moncure <mmoncure@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 30+ messages in thread
From: Merlin Moncure @ 2006-04-13 19:38 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
On 4/12/06, Josh Berkus <josh@agliodbs.com> wrote:
> People,
>
> > Lately I find people are not so receptive to VxFS, and Sun is promoting
> > ZFS, and we don't have a reasonable near term option for Raw IO in
> > Postgres, so we need to work to find a reasonable path for Solaris users
> > IMO. The long delays in ZFS production haven't helped us there, as the
> > problems with UFS are severe.
I just recently worked with sun solaris 10 and found it to be
reasonably performant without much tuning. This was on a dual sparc
sunblade workstation which i felt was very well engineered. I was
able (with zero solaris experience) to get postgresql up and crunching
away at some really data intensive tasks while running an application
compiled their very excellent fortran compiler.
In the enterprise world I am finding that the only linux distrubutions
supported are redhat and suse, meaning if you have a problem with your
san running against your gentoo box you have a serious problem.
Solaris OTOH, is generally very well supported (especially on sun
hardware) and is free. So I give sun great credit for providing a
free if not necessarily completely open platform for developing open
source applications in an enterprise environment.
Merlin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-14 14:02 Jignesh K. Shah <J.K.Shah@Sun.COM>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 30+ messages in thread
From: Jignesh K. Shah @ 2006-04-14 14:02 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Alvaro Herrera <alvherre@commandprompt.com>; Chris Mair <list@1006.org>; pgsql-performance@postgresql.org, markir@paradise.net.nz, Josh Berkus <josh@agliodbs.com>
Hi Bruce,
I saw even on this alias also that people assumed that the default
wal_sync_method was fsync on Solaris.
I would select fsync or fdsync as the default on Solaris. (I prefer
fsync as it is already highlighted as default in postgresql)
Another thing to improve the defaults on Solaris will be to increase the
defaults of
wal_buffers
and
checkpoint_segments
(I think in 8.1 checkpoint_segments have been already improved to a
default of 8 from the previous 3 and that may be already some help in
performance out there. )
These three changes improve out-of-box performance of PostgreSQL quite a
bit on Solaris (SPARC as well as x64 platforms).
Then you will suddenly see decrease in the number of people PostgreSQL
community complaining about Solaris 10, as well as Solaris community
complaining about PostgreSQL. (The benefits are mutual)
Don't get me wrong. As Luke mentioned it took a while to get the
potential of PostgreSQL on Solaris and people like me start doing other
complex workarounds in Solaris like "forcedirectio", etc. (Yeah I did a
test, if you force fsync as wal_sync_method while on Solaris, then
you may not even require to do forcedirectio of your $PGDATA/pg_xlogs to
get back the lost performance)
If we had realized that fsync/odatasync difference was the culprit we
could have saved couple of months of efforts.
Yes I agree that putting OS specific things in PostgreSQL hurts
community and sticking to POSIX standards help.
Just my two cents.
Regards,
Jignesh
Bruce Momjian wrote:
>Jignesh K. Shah wrote:
>
>
>>Bruce,
>>
>>Hard to answer that... People like me who know and love PostgreSQL and
>>Solaris finds this as an opportunity to make their favorite database
>>work best on their favorite operating system.
>>
>>Many times PostgreSQL has many things based on assumption that it will
>>run on Linux and it is left to Solaris to emulate that behavior.That
>>said there are ways to improve performance even on UFS on Solaris, it
>>just requires more tweaks.
>>
>>Hopefully this will lead to few Solaris friendly default values like
>>fsync/odatasync :-)
>>
>>
>
>Yes, if someone wants to give us a clear answer on which wal_sync method
>is best on all versions of Solaris, we can easily make that change.
>
>
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: bad performance on Solaris 10
@ 2006-04-14 19:01 Josh Berkus <josh@agliodbs.com>
parent: Jignesh K. Shah <J.K.Shah@Sun.COM>
0 siblings, 0 replies; 30+ messages in thread
From: Josh Berkus @ 2006-04-14 19:01 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@sun.com>; +Cc: pgsql-performance
Jignesh,
> Don't get me wrong. As Luke mentioned it took a while to get the
> potential of PostgreSQL on Solaris and people like me start doing other
> complex workarounds in Solaris like "forcedirectio", etc. (Yeah I did a
> test, if you force fsync as wal_sync_method while on Solaris, then
> you may not even require to do forcedirectio of your $PGDATA/pg_xlogs to
> get back the lost performance)
I didn't see these later test results. Can you link?
Also, I presume this was on DW, and not on OLTP, yes?
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 30+ messages in thread
end of thread, other threads:[~2006-04-14 19:01 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-04 00:39 bad performance on Solaris 10 Chris Mair <list@1006.org>
2006-04-04 00:49 ` Josh Berkus <josh@agliodbs.com>
2006-04-04 03:46 ` Luke Lonergan <llonergan@greenplum.com>
2006-04-04 05:12 ` Mark Kirkwood <markir@paradise.net.nz>
2006-04-04 06:09 ` Josh Berkus <josh@agliodbs.com>
2006-04-05 21:31 ` Chris Mair <list@1006.org>
2006-04-05 21:40 ` Luke Lonergan <llonergan@greenplum.com>
2006-04-05 21:48 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-04-05 21:56 ` Luke Lonergan <llonergan@greenplum.com>
2006-04-12 19:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-04-12 22:35 ` Luke Lonergan <llonergan@greenplum.com>
2006-04-12 22:49 ` Josh Berkus <josh@agliodbs.com>
2006-04-13 19:38 ` Merlin Moncure <mmoncure@gmail.com>
2006-04-13 01:53 ` Jignesh K. Shah <J.K.Shah@Sun.COM>
2006-04-13 04:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-13 08:39 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-04-13 17:40 ` Robert Lor <Robert.Lor@Sun.COM>
2006-04-14 14:02 ` Jignesh K. Shah <J.K.Shah@Sun.COM>
2006-04-14 19:01 ` Josh Berkus <josh@agliodbs.com>
2006-04-05 23:13 ` Chris Mair <list@1006.org>
2006-04-05 23:25 ` Mark Kirkwood <markir@paradise.net.nz>
2006-04-05 23:29 ` Chris Mair <list@1006.org>
2006-04-06 01:23 ` Mark Kirkwood <markir@paradise.net.nz>
2006-04-06 04:35 ` Josh Berkus <josh@agliodbs.com>
2006-04-06 06:01 ` Chris Mair <list@1006.org>
2006-04-06 07:36 ` Robert Lor <Robert.Lor@Sun.COM>
2006-04-07 19:29 ` Chris Mair <list@1006.org>
2006-04-10 21:05 ` Chris Mair <list@1006.org>
2006-04-05 23:33 ` Chris Mair <list@1006.org>
2006-04-06 04:32 ` Josh Berkus <josh@agliodbs.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