pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Filesystems WAS: Perfomance Tuning
8+ messages / 7 participants
[nested] [flat]

* Re: Filesystems WAS: Perfomance Tuning
@ 2003-08-12 20:09 Josh Berkus <josh@agliodbs.com>
  2003-08-12 20:31 ` Re: Filesystems WAS: Perfomance Tuning Richard Welty <rwelty@averillpark.net>
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2003-08-12 20:09 UTC (permalink / raw)
  To: Gregory S. Williamson <gsw@globexplorer.com>; Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance

Greg,

> FWIW, Informix can be run using a "cooked" (Unix) file for storing data or
> it uses "raw" disk space and bypasses the ordinary (high level) UNIX
> controllers and does its own reads/writes. About 10 times faster and safer.
> Of course, itmay have taken a lot of programmer time to make that solid.
> But the performance gains are significant.

Yes, but it's still slower than PostgreSQL on medium-end hardware.  ;-)

This idea has been discussed numerous times on the HACKERS list, and is a 
(pretty much) closed issue.   While Oracle and SQL Server use their own 
filesystems, PostgreSQL will not because:

1) It would interfere with our cross-platform compatibility.  PostgreSQL runs 
on something like 20 OSes.

2) The filesystem projects out there are (mostly) well-staffed and are 
constantly advancing using specialized technology and theory.  There's no way 
that the PostgreSQL team can do a better job in our "spare time".

3) Development of our "own" filesystem would then require PostgreSQL to create 
and maintain a whole hardware compatibility library, and troubleshoot 
problems on exotic hardware and wierd RAID configurations.

4) A database FS also often causes side-effect problems; for example, one 
cannot move or copy a SQL Server partition without destroying it.

Of course, that could all change if some corp with deep pockets steps in an 
decides to create a "postgresFS" and funds and staffs the effort 100%.  But 
it's unlikely to be a priority for the existing development team any time in 
the forseeable future.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



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

* Re: Filesystems WAS: Perfomance Tuning
  2003-08-12 20:09 Re: Filesystems WAS: Perfomance Tuning Josh Berkus <josh@agliodbs.com>
@ 2003-08-12 20:31 ` Richard Welty <rwelty@averillpark.net>
  2003-08-12 20:40   ` Re: Filesystems WAS: Perfomance Tuning Andrew Sullivan <andrew@libertyrms.info>
  2003-08-12 20:40   ` Re: Filesystems WAS: Perfomance Tuning Rod Taylor <rbt@rbt.ca>
  2003-08-13 01:48   ` Re: Filesystems WAS: Perfomance Tuning Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 3 replies; 8+ messages in thread

From: Richard Welty @ 2003-08-12 20:31 UTC (permalink / raw)
  To: pgsql-performance

On Tue, 12 Aug 2003 13:09:42 -0700 Josh Berkus <josh@agliodbs.com> wrote:
> This idea has been discussed numerous times on the HACKERS list, and is
> a 
> (pretty much) closed issue.   While Oracle and SQL Server use their own 
> filesystems, PostgreSQL will not because:
...
> 2) The filesystem projects out there are (mostly) well-staffed and are 
> constantly advancing using specialized technology and theory.  There's
> no way 
> that the PostgreSQL team can do a better job in our "spare time".

i consider this a fair answer, but i have a slightly different question to
ask, inspired by my discussions with a good friend who is a top notch
Informix DBA.

there are advantages to being able to split the database across a slew of
disk drives. if we accept the notion of using the native OS filesystem on
each, it would seem that being able to direct various tables and indices to
specific drives might be a valuble capability. i know that i could go into
/var/lib/pgsql/data/base and fan the contents out, but this is unweildy and
impractical. has any consideration been given to providing a way to manage
such a deployment?

or is it the judgement of the hackers community that a monsterous raid-10
array offers comparable performance?

i forget how large the data store on my friend's current project is, but
i'll check. knowing the size and transaction rate he's dealing with might
put a finer point on this discussion.

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security





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

* Re: Filesystems WAS: Perfomance Tuning
  2003-08-12 20:09 Re: Filesystems WAS: Perfomance Tuning Josh Berkus <josh@agliodbs.com>
  2003-08-12 20:31 ` Re: Filesystems WAS: Perfomance Tuning Richard Welty <rwelty@averillpark.net>
@ 2003-08-12 20:40   ` Andrew Sullivan <andrew@libertyrms.info>
  2 siblings, 0 replies; 8+ messages in thread

From: Andrew Sullivan @ 2003-08-12 20:40 UTC (permalink / raw)
  To: pgsql-performance

On Tue, Aug 12, 2003 at 04:31:19PM -0400, Richard Welty wrote:
> impractical. has any consideration been given to providing a way to manage
> such a deployment?

Plenty.  No-one's completed an implementation yet.  

> or is it the judgement of the hackers community that a monsterous raid-10
> array offers comparable performance?

It's tough to say, but I _can_ tell you that, so far in my tests,
I've never been able to prove an advantage in separating even the WAL
on a Sun A5200.  That's not a result yet, of course, just a bit of
gossip.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




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

* Re: Filesystems WAS: Perfomance Tuning
  2003-08-12 20:09 Re: Filesystems WAS: Perfomance Tuning Josh Berkus <josh@agliodbs.com>
  2003-08-12 20:31 ` Re: Filesystems WAS: Perfomance Tuning Richard Welty <rwelty@averillpark.net>
@ 2003-08-12 20:40   ` Rod Taylor <rbt@rbt.ca>
  2 siblings, 0 replies; 8+ messages in thread

From: Rod Taylor @ 2003-08-12 20:40 UTC (permalink / raw)
  To: Richard Welty <rwelty@averillpark.net>; +Cc: pgsql-performance

> specific drives might be a valuble capability. i know that i could go into
> /var/lib/pgsql/data/base and fan the contents out, but this is unweildy and
> impractical. has any consideration been given to providing a way to manage
> such a deployment?

The ability to take various database objects and store them in different
locations, sometimes referred to as table spaces, will probably be done
in the future.  There was a substantial group not all that long ago that
was organizing to complete the implementation.

> or is it the judgement of the hackers community that a monsterous raid-10
> array offers comparable performance?

Often performs well enough... But a raid-10 for data, a stripe for
indexes, and a mirror for WAL will offer better performance :)

Attachments:

  signature.asc (application/pgp-signature, 186B) download

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

* Re: Filesystems WAS: Perfomance Tuning
  2003-08-12 20:09 Re: Filesystems WAS: Perfomance Tuning Josh Berkus <josh@agliodbs.com>
  2003-08-12 20:31 ` Re: Filesystems WAS: Perfomance Tuning Richard Welty <rwelty@averillpark.net>
@ 2003-08-13 01:48   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2003-08-13 01:52     ` Re: Filesystems WAS: Perfomance Tuning Richard Welty <rwelty@averillpark.net>
  2003-08-13 01:53     ` Re: Filesystems WAS: Perfomance Tuning Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 2 replies; 8+ messages in thread

From: Christopher Kings-Lynne @ 2003-08-13 01:48 UTC (permalink / raw)
  To: Richard Welty <rwelty@averillpark.net>; pgsql-performance

> there are advantages to being able to split the database across a slew of
> disk drives. if we accept the notion of using the native OS filesystem on
> each, it would seem that being able to direct various tables and indices
to
> specific drives might be a valuble capability. i know that i could go into
> /var/lib/pgsql/data/base and fan the contents out, but this is unweildy
and
> impractical. has any consideration been given to providing a way to manage
> such a deployment?

We've got a little bunch of us tinkering with a tablespace implementation.
However, it's been staller for a while now.

Chris




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

* Re: Filesystems WAS: Perfomance Tuning
  2003-08-12 20:09 Re: Filesystems WAS: Perfomance Tuning Josh Berkus <josh@agliodbs.com>
  2003-08-12 20:31 ` Re: Filesystems WAS: Perfomance Tuning Richard Welty <rwelty@averillpark.net>
  2003-08-13 01:48   ` Re: Filesystems WAS: Perfomance Tuning Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-08-13 01:52     ` Richard Welty <rwelty@averillpark.net>
  1 sibling, 0 replies; 8+ messages in thread

From: Richard Welty @ 2003-08-13 01:52 UTC (permalink / raw)
  To: pgsql-performance

On Wed, 13 Aug 2003 09:48:18 +0800 Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> We've got a little bunch of us tinkering with a tablespace
> implementation.
> However, it's been staller for a while now.

interesting. i'm involved in the very early stages of a startup that is
likely to do a prototype using Java and PostgreSQL.

tablespace and replication are issues that would weigh heavily in a
decision to stick with PostgreSQL after the prototype.

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security





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

* Re: Filesystems WAS: Perfomance Tuning
  2003-08-12 20:09 Re: Filesystems WAS: Perfomance Tuning Josh Berkus <josh@agliodbs.com>
  2003-08-12 20:31 ` Re: Filesystems WAS: Perfomance Tuning Richard Welty <rwelty@averillpark.net>
  2003-08-13 01:48   ` Re: Filesystems WAS: Perfomance Tuning Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-08-13 01:53     ` Bruce Momjian <pgman@candle.pha.pa.us>
  2003-08-13 02:07       ` Re: Filesystems WAS: Perfomance Tuning Gavin Sherry <swm@linuxworld.com.au>
  1 sibling, 1 reply; 8+ messages in thread

From: Bruce Momjian @ 2003-08-13 01:53 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Richard Welty <rwelty@averillpark.net>; pgsql-performance; swm@linuxworld.com.au


I think Gavin Sherry is working on this.  I am CC'ing him.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> > there are advantages to being able to split the database across a slew of
> > disk drives. if we accept the notion of using the native OS filesystem on
> > each, it would seem that being able to direct various tables and indices
> to
> > specific drives might be a valuble capability. i know that i could go into
> > /var/lib/pgsql/data/base and fan the contents out, but this is unweildy
> and
> > impractical. has any consideration been given to providing a way to manage
> > such a deployment?
> 
> We've got a little bunch of us tinkering with a tablespace implementation.
> However, it's been staller for a while now.
> 
> Chris
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  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] 8+ messages in thread

* Re: Filesystems WAS: Perfomance Tuning
  2003-08-12 20:09 Re: Filesystems WAS: Perfomance Tuning Josh Berkus <josh@agliodbs.com>
  2003-08-12 20:31 ` Re: Filesystems WAS: Perfomance Tuning Richard Welty <rwelty@averillpark.net>
  2003-08-13 01:48   ` Re: Filesystems WAS: Perfomance Tuning Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2003-08-13 01:53     ` Re: Filesystems WAS: Perfomance Tuning Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-08-13 02:07       ` Gavin Sherry <swm@linuxworld.com.au>
  0 siblings, 0 replies; 8+ messages in thread

From: Gavin Sherry @ 2003-08-13 02:07 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Richard Welty <rwelty@averillpark.net>; pgsql-performance

On Tue, 12 Aug 2003, Bruce Momjian wrote:

> 
> I think Gavin Sherry is working on this.  I am CC'ing him.
> 
> ---------------------------------------------------------------------------

Yes I am working on this. I am about 50% of the way through the patch but
have been held up with other work. For those who are interested, it
basically allow:

1) creation of different 'storage' locations. Tables and indexes can be
created in different storage locations. Storage locations can also be
assigned to schemas and databases. Tables and indexes will default to the
schema storage location if STORAGE 'store name' is not provided to CREATE
.... This will cascade to the default database storage location if
the schema was not created with STORAGE 'store name'.

2) the patch will allow different storage locations to have different
rand_cost parameters passed to the planner.

3) the patch *will not* address issues concerning quotas, resource
management, WAL/clog, temp or sort spaces.

Will keep everyone posted if/when I finish.

Thanks,

Gavin




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


end of thread, other threads:[~2003-08-13 02:07 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-12 20:09 Re: Filesystems WAS: Perfomance Tuning Josh Berkus <josh@agliodbs.com>
2003-08-12 20:31 ` Richard Welty <rwelty@averillpark.net>
2003-08-12 20:40   ` Andrew Sullivan <andrew@libertyrms.info>
2003-08-12 20:40   ` Rod Taylor <rbt@rbt.ca>
2003-08-13 01:48   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-08-13 01:52     ` Richard Welty <rwelty@averillpark.net>
2003-08-13 01:53     ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-13 02:07       ` Gavin Sherry <swm@linuxworld.com.au>

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