pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PostgreSQL on Solaris 8 and ufs
13+ messages / 7 participants
[nested] [flat]

* PostgreSQL on Solaris 8 and ufs
@ 2005-03-22 20:44  Brandon Metcalf <bmetcalf@nortel.com>
  0 siblings, 3 replies; 13+ messages in thread

From: Brandon Metcalf @ 2005-03-22 20:44 UTC (permalink / raw)
  To: pgsql-performance

We've recently moved our pgsql installation and DBs to a Solaris 8
machine with striped and mirrored ufs filesystem that houses the DB
data.  We are now seeing terrible performance and the bottleneck is no
doubt disk I/O.

We've tried modifying a tunables related to ufs, but it doesn't seem
to be helping.

Is there anything we should be looking at that is specifically related
to ufs filesystems on Solaris 8 or possibly something in general that
would improve performance?

Thanks.

-- 
Brandon



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-22 21:02  Alan Stange <stange@rentec.com>
  parent: Brandon Metcalf <bmetcalf@nortel.com>
  2 siblings, 1 reply; 13+ messages in thread

From: Alan Stange @ 2005-03-22 21:02 UTC (permalink / raw)
  To: Brandon Metcalf <bmetcalf@nortel.com>; +Cc: pgsql-performance

Brandon Metcalf wrote:

>We've recently moved our pgsql installation and DBs to a Solaris 8
>machine with striped and mirrored ufs filesystem that houses the DB
>data.  We are now seeing terrible performance and the bottleneck is no
>doubt disk I/O.
>
>We've tried modifying a tunables related to ufs, but it doesn't seem
>to be helping.
>
>Is there anything we should be looking at that is specifically related
>to ufs filesystems on Solaris 8 or possibly something in general that
>would improve performance?
>  
>
Well, Solaris 8 is a bit old now, so I don't remember all the details.   
But, if memory servers, Solaris 8 still has some "high water" and "lo 
water" tunables related to the amount of IO can be outstanding to a 
single file.

Try setting
set ufs:ufs_WRITES=0
in /etc/system and rebooting, which basically says "any amount of disk 
IO can be outstanding".  There's a tunables doc on docs.sun.com that 
explains this option.

Also, logging UFS might help with some of the metadata requirements of 
UFS as well.  So, use "mount -o logging" or add the relevant entry in 
/etc/vfstab.

Of course, the best thing is Solaris 9 or 10, which would be much better 
for this sort of thing.

Hope this helps.

-- Alan



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-22 21:03  Sven Willenberger <sven@dmv.com>
  parent: Brandon Metcalf <bmetcalf@nortel.com>
  2 siblings, 1 reply; 13+ messages in thread

From: Sven Willenberger @ 2005-03-22 21:03 UTC (permalink / raw)
  To: Brandon Metcalf <bmetcalf@nortel.com>; +Cc: pgsql-performance

On Tue, 2005-03-22 at 14:44 -0600, Brandon Metcalf wrote:
> We've recently moved our pgsql installation and DBs to a Solaris 8
> machine with striped and mirrored ufs filesystem that houses the DB
> data.  We are now seeing terrible performance and the bottleneck is no
> doubt disk I/O.
> 
> We've tried modifying a tunables related to ufs, but it doesn't seem
> to be helping.
> 
> Is there anything we should be looking at that is specifically related
> to ufs filesystems on Solaris 8 or possibly something in general that
> would improve performance?
> 
> Thanks.
> 

What are you using to create your raid? You say it is "no doubt disk
I/O" - does iostat confirm this? A lot of performance issues are related
to the size of the stripe you chose for the striped portion of the
array, the actual array configuration, etc. I am assuming you have
looked at system variables such as autoup and the likes? What tweaks
have you done?

Also, are your pg_xlog and data directories separated onto separate
volumes? Doing so will help immensely. What are you using to measure
performance?

Sven




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-22 21:06  Brandon Metcalf <bmetcalf@nortel.com>
  parent: Alan Stange <stange@rentec.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Brandon Metcalf @ 2005-03-22 21:06 UTC (permalink / raw)
  To: Alan Stange <stange@rentec.com>; +Cc: pgsql-performance

s == stange@rentec.com writes:

 s> Try setting
 s> set ufs:ufs_WRITES=0
 s> in /etc/system and rebooting, which basically says "any amount of disk
 s> IO can be outstanding".  There's a tunables doc on docs.sun.com that
 s> explains this option.

 s> Also, logging UFS might help with some of the metadata requirements of
 s> UFS as well.  So, use "mount -o logging" or add the relevant entry in
 s> /etc/vfstab.

OK.  I'll try these out.  We do have ufs_WRITES enabled with the
following parameters:

  set ncsize                      =   257024
  set autoup                      =       90
  set bufhwm                      =    15000
  set tune_t_fsflushr             =       15
  set ufs:ufs_HW                  = 16777216
  set ufs:ufs_LW                  =  8388608


-- 
Brandon



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-22 21:23  Brandon Metcalf <bmetcalf@nortel.com>
  parent: Sven Willenberger <sven@dmv.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Brandon Metcalf @ 2005-03-22 21:23 UTC (permalink / raw)
  To: Sven Willenberger <sven@dmv.com>; +Cc: pgsql-performance

s == sven@dmv.com writes:

 s> What are you using to create your raid?

Hm.  I didn't set this up.  I'll have to check.

 s> You say it is "no doubt disk
 s> I/O" - does iostat confirm this? A lot of performance issues are related
 s> to the size of the stripe you chose for the striped portion of the
 s> array, the actual array configuration, etc. I am assuming you have
 s> looked at system variables such as autoup and the likes? What tweaks
 s> have you done?

I've mainly been using Glance which shows a lot of queued requests for
the disks in question.

Here's currently what we have in /etc/system related to ufs:

  set ncsize                      =   257024
  set autoup                      =       90
  set bufhwm                      =    15000
  set tune_t_fsflushr             =       15
  set ufs:ufs_HW                  = 16777216
  set ufs:ufs_LW                  =  8388608

 s> Also, are your pg_xlog and data directories separated onto separate
 s> volumes? Doing so will help immensely.

No, they are on the same volume.

 s> What are you using to measure
 s> performance?

Nothing too scientific other than the fact that since we have moved
the DB, we consistenly see a large number of postmater processes
(close to 100) where before we did not.


-- 
Brandon



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-22 21:26  Mark Kirkwood <markir@paradise.net.nz>
  parent: Brandon Metcalf <bmetcalf@nortel.com>
  2 siblings, 0 replies; 13+ messages in thread

From: Mark Kirkwood @ 2005-03-22 21:26 UTC (permalink / raw)
  To: Brandon Metcalf <bmetcalf@nortel.com>; +Cc: pgsql-performance

Brandon Metcalf wrote:
> We've recently moved our pgsql installation and DBs to a Solaris 8
> machine with striped and mirrored ufs filesystem that houses the DB
> data.  We are now seeing terrible performance and the bottleneck is no
> doubt disk I/O.
> 
> We've tried modifying a tunables related to ufs, but it doesn't seem
> to be helping.
> 
> Is there anything we should be looking at that is specifically related
> to ufs filesystems on Solaris 8 or possibly something in general that
> would improve performance?
> 
> Thanks.
> 

I found that mounting the filesystem that contains the PGDATA directory 
(probably only the pg_xlog directory in fact) without logging improved 
things a great deal (assuming you are using logging that is...).

In addition changing the postgresql.conf parameter wal_sync_method from 
the default of open_datasync to fdatasync improved  things a bit more. 
However I seem to recall a posting suggesting the opposite! ...so feel 
free to experiment and let us know!

Mark

P.s : original tests on Solaris 8, 
http://archives.postgresql.org/pgsql-performance/2003-12/msg00165.php



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-23 16:58  Andrew Sullivan <ajs@crankycanuck.ca>
  parent: Brandon Metcalf <bmetcalf@nortel.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Andrew Sullivan @ 2005-03-23 16:58 UTC (permalink / raw)
  To: pgsql-performance

On Tue, Mar 22, 2005 at 03:23:18PM -0600, Brandon Metcalf wrote:
>  s> What are you using to measure
>  s> performance?
> 
> Nothing too scientific other than the fact that since we have moved
> the DB, we consistenly see a large number of postmater processes
> (close to 100) where before we did not.

What did you move from?  The Solaris ps (not in ucb, which is the
BSD-style ps) shows the parent process name, so everything shows up
as "postmaster" rather than "postgres".  There's always one back end
per connection.

If you are in fact using more connections, by the way, I can tell you
that Solaris 8, in my experience, is _very bad_ at managing context
switches.  So you may not be merely I/O bound (although your other
reports seem to indicate that you are).

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
		--George Orwell



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-23 17:16  Brandon Metcalf <bmetcalf@nortel.com>
  parent: Andrew Sullivan <ajs@crankycanuck.ca>
  0 siblings, 2 replies; 13+ messages in thread

From: Brandon Metcalf @ 2005-03-23 17:16 UTC (permalink / raw)
  To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-performance

a == ajs@crankycanuck.ca writes:

 a> On Tue, Mar 22, 2005 at 03:23:18PM -0600, Brandon Metcalf wrote:
 a> >  s> What are you using to measure
 a> >  s> performance?
 a> >
 a> > Nothing too scientific other than the fact that since we have moved
 a> > the DB, we consistenly see a large number of postmater processes
 a> > (close to 100) where before we did not.

 a> What did you move from?  The Solaris ps (not in ucb, which is the
 a> BSD-style ps) shows the parent process name, so everything shows up
 a> as "postmaster" rather than "postgres".  There's always one back end
 a> per connection.

 a> If you are in fact using more connections, by the way, I can tell you
 a> that Solaris 8, in my experience, is _very bad_ at managing context
 a> switches.  So you may not be merely I/O bound (although your other
 a> reports seem to indicate that you are).


We moved from an HP-UX 10.20 box where the pgsql installation and data
were on a vxfs fileystem.

And we're definitely seeing more connections at a time which indicates
that each process is taking longer to complete.


-- 
Brandon



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-23 17:32  Tom Arthurs <tarthurs@jobflash.com>
  parent: Brandon Metcalf <bmetcalf@nortel.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Tom Arthurs @ 2005-03-23 17:32 UTC (permalink / raw)
  To: Brandon Metcalf <bmetcalf@nortel.com>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-performance

On the context switching issue, we've found that this setting in /etc/system helps:

set rechoose_interval=30

this sets the minimum time that a process is eligible to be switched to another cpu. (the default is 3).

You can monitor context switching with the cs column in vmstat.  We've found that high context switching seems to be more a symptom,
rather than a cause of problems -- for example we had an issue with column statistics and some really bad queries, and the cpu's start
context switching like crazy. (20,000 - 50,000 or more in a 5 second period, normally < 5000 per 5 second period under heavy load.)

Brandon Metcalf wrote:

> a == ajs@crankycanuck.ca writes:
> 
>  a> On Tue, Mar 22, 2005 at 03:23:18PM -0600, Brandon Metcalf wrote:
>  a> >  s> What are you using to measure
>  a> >  s> performance?
>  a> >
>  a> > Nothing too scientific other than the fact that since we have moved
>  a> > the DB, we consistenly see a large number of postmater processes
>  a> > (close to 100) where before we did not.
> 
>  a> What did you move from?  The Solaris ps (not in ucb, which is the
>  a> BSD-style ps) shows the parent process name, so everything shows up
>  a> as "postmaster" rather than "postgres".  There's always one back end
>  a> per connection.
> 
>  a> If you are in fact using more connections, by the way, I can tell you
>  a> that Solaris 8, in my experience, is _very bad_ at managing context
>  a> switches.  So you may not be merely I/O bound (although your other
>  a> reports seem to indicate that you are).
> 
> 
> We moved from an HP-UX 10.20 box where the pgsql installation and data
> were on a vxfs fileystem.
> 
> And we're definitely seeing more connections at a time which indicates
> that each process is taking longer to complete.
> 
> 



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-23 17:55  Andrew Sullivan <ajs@crankycanuck.ca>
  parent: Brandon Metcalf <bmetcalf@nortel.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Andrew Sullivan @ 2005-03-23 17:55 UTC (permalink / raw)
  To: pgsql-performance

On Wed, Mar 23, 2005 at 11:16:29AM -0600, Brandon Metcalf wrote:
> 
> We moved from an HP-UX 10.20 box where the pgsql installation and data
> were on a vxfs fileystem.

My best guess, then, is that ufs tuning really is your issue.  We
always used vxfs for our Sun database servers (which was a nightmare
all on its own, BTW, so I don't actually recommend this), so I don't
have any real ufs tuning advice.  

The Packer Solaris database book (Packer, Allan N., _Configuring &
Tuning Databases on the Solaris Platform_.  Palo Alto: Sun
Microsystems P, 2002.  ISBN 0-13-083417-3) does suggest mounting the
filesystems with forcedirectio; I dimly recall using this for the wal
partition on one test box, and STR that it helped.  Also, you want to
make sure you use the right fsync method; if it's still set to
"fsync" in the config file, you'll want to change that.  I remember
finding that fsync was something like 3 times slower than everything
else.  I don't have any more Solaris boxes to check, but I believe we
were using open_datasync as our method.  You'll want to run some
tests.

You also should enable priority paging, but expect that this will
give you really strange po numbers from vmstat and friends.  Priority
paging, I found, makes things look like you're swapping when you
aren't.  Procmem is useful, but if you really want the goods on
what's going on, you need the SE toolkit.  Just be careful using it
as root -- in some cases it'll modify kernel parameters behind the
scenes.  In my case, I didn't have superuser access, so there wasn't
a danger; but I've heard sysadmins complain about this.   

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
		--Dennis Ritchie



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-23 17:58  Andrew Sullivan <ajs@crankycanuck.ca>
  parent: Tom Arthurs <tarthurs@jobflash.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Andrew Sullivan @ 2005-03-23 17:58 UTC (permalink / raw)
  To: pgsql-performance

On Wed, Mar 23, 2005 at 09:32:07AM -0800, Tom Arthurs wrote:
> found that high context switching seems to be more a symptom,

Yes, that's a good point.  It usually _is_ a symptom; but it might be
a symptom that you've got an expensive query, and Solaris's foot-gun
approach to handling such cases is a little painful.  (We didn't give
up on Solaris because of cs problems, BTW; but I have to say that AIX
seems to be a little less prone to self-DOS on this front than
Solaris was.  If you really want to hear me rant, ask me some time
about ecache and Sun support.)

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.
		--Roger Brinner



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-23 23:39  Josh Berkus <josh@agliodbs.com>
  parent: Andrew Sullivan <ajs@crankycanuck.ca>
  0 siblings, 1 reply; 13+ messages in thread

From: Josh Berkus @ 2005-03-23 23:39 UTC (permalink / raw)
  To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-performance

Andrew,

> The Packer Solaris database book (Packer, Allan N., _Configuring &
> Tuning Databases on the Solaris Platform_.  Palo Alto: Sun
> Microsystems P, 2002.  ISBN 0-13-083417-3) does suggest mounting the
> filesystems with forcedirectio; I dimly recall using this for the wal
> partition on one test box, and STR that it helped.

This is a good idea for a WAL partition, but is NOT a good idea for the 
database.

You pay want to look into setting segmap_percent to 70% or something.   On 
Solaris 10 at least, the OS by default only uses 10% of memory for disk 
buffers.   

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: PostgreSQL on Solaris 8 and ufs
@ 2005-03-30 21:00  Brandon Metcalf <bmetcalf@nortel.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Brandon Metcalf @ 2005-03-30 21:00 UTC (permalink / raw)
  To: pgsql-performance

I just wanted to follow up and let everyone know that the biggest
improvement in performance came from moving the pg_xlog directory to
another filesystem (different set of disks) separate from the data
directory.

Thanks for the suggestions.

-- 
Brandon



^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2005-03-30 21:00 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-22 20:44 PostgreSQL on Solaris 8 and ufs Brandon Metcalf <bmetcalf@nortel.com>
2005-03-22 21:02 ` Alan Stange <stange@rentec.com>
2005-03-22 21:06   ` Brandon Metcalf <bmetcalf@nortel.com>
2005-03-22 21:03 ` Sven Willenberger <sven@dmv.com>
2005-03-22 21:23   ` Brandon Metcalf <bmetcalf@nortel.com>
2005-03-23 16:58     ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-03-23 17:16       ` Brandon Metcalf <bmetcalf@nortel.com>
2005-03-23 17:32         ` Tom Arthurs <tarthurs@jobflash.com>
2005-03-23 17:58           ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-03-23 17:55         ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-03-23 23:39           ` Josh Berkus <josh@agliodbs.com>
2005-03-30 21:00             ` Brandon Metcalf <bmetcalf@nortel.com>
2005-03-22 21:26 ` Mark Kirkwood <markir@paradise.net.nz>

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