Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
On Mon, Feb 19, 2007 at 07:10:52PM -0800, David Fetter wrote: > > Isn't this one of the big use cases for table partitioning? Sure, but you can't detach that data in the meantime, AFAIK. Maybe I've missed something. If I have 10 years of finace data, and I have to keep it all online all the time, my electricity costs alone are outrageous. If I can know _where_ the data is without being able to get it until I've actually made it available, then my query tools could be smart enough to detect "table partitioned; this data is offline", I could roll back to my savepoint, return a partial result set to the user, and tell it "call back in 24 hours for your full report". Yes, I know, hands waving in the air. But I already said I was having a "you know what would be sweet" moment. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
On Monday 19 February 2007 11:27, Martijn van Oosterhout wrote:
> On Mon, Feb 19, 2007 at 05:10:36PM +0100, Dimitri Fontaine wrote:
> > > RAID and LVM too. I can't get excited about re-inventing those wheels
> > > when perfectly good implementations already exist for us to sit on top
> > > of.
> >
> > I though moving some knowledge about data availability into PostgreSQL
> > code could provide some valuable performance benefit, allowing to
> > organize reads (for example parallel tables scan/indexes scan to
> > different volumes) and obtaining data from 'quicker' known volume (or
> > least used/charged).
>
> Well, organising requests to be handled quickly is a function of
> LVM/RAID, so we don't go there. However, speeding up scans by having
> multiple requests is an interesting approach, as would perhaps a
> different random_page_cost for different tablespaces.
>
On one of my systems I have 1 tablespace for read data (99-1), 1 for read
mostly data (90-10), and 1 for write mostly (40-60). The breakdown is based
on a combination of the underlying hardware and usage patterns of the tables
involved. I suspect that isn't that uncommon really. I've often thought that
being able to set guc variables to a specific tablespace (like you can do for
users) would allow for a lot of flexibility in tuning queries that go across
different tablespaces.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
On Monday 19 February 2007 15:08, Bruce Momjian wrote:
> Joshua D. Drake wrote:
> > Andrew Sullivan wrote:
> > > On Mon, Feb 19, 2007 at 10:33:24AM -0500, Tom Lane wrote:
> > >> Martijn van Oosterhout writes:
> > >>> Somehow this seems like implementing RAID within postgres,
> > >>
> > >> RAID and LVM too. I can't get excited about re-inventing those wheels
> > >> when perfectly good implementations already exist for us to sit on top
> > >> of.
> > >
> > > Ok, warning, this is a "you know what would be sweet" moment.
> >
> > The dreaded words from a developers mouth to every manager in the world.
>
> Yea, I just instinctively hit "delete" when I saw that phrase.
Too bad... I know oracle can do what he wants... possibly other db systems as
well.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
On Mon, Feb 19, 2007 at 02:50:34PM -0500, Andrew Sullivan wrote: > On Mon, Feb 19, 2007 at 10:33:24AM -0500, Tom Lane wrote: > > Martijn van Oosterhout writes: > > > Somehow this seems like implementing RAID within postgres, > > > > RAID and LVM too. I can't get excited about re-inventing those > > wheels when perfectly good implementations already exist for us to > > sit on top of. > > Ok, warning, this is a "you know what would be sweet" moment. > > What would be nice is to be able to detach one of the volumes, and > know the span of the data in there without being able to access the > data. > > The problem that a lot of warehouse operators have is something like > this: "We know we have all this data, but we don't know what we will > want to do with it later. So keep it all. I'll get back to you > when I want to know something." > > It'd be nice to be able to load up all that data once, and then > shunt it off into (say) read-only media. If one could then run a > query that would tell one which spans of data are candidates for the > search, you could bring back online (onto reasonably fast storage, > for instance) just the volumes you need to read. Isn't this one of the big use cases for table partitioning? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
On Mon, 2007-02-19 at 17:35 -0300, Alvaro Herrera wrote: > Andrew Sullivan wrote: > > On Mon, Feb 19, 2007 at 10:33:24AM -0500, Tom Lane wrote: > > > Martijn van Oosterhout writes: > > > > Somehow this seems like implementing RAID within postgres, > > > > > > RAID and LVM too. I can't get excited about re-inventing those wheels > > > when perfectly good implementations already exist for us to sit on top of. > > > > Ok, warning, this is a "you know what would be sweet" moment. > > > > What would be nice is to be able to detach one of the volumes, and > > know the span of the data in there without being able to access the > > data. > > > > The problem that a lot of warehouse operators have is something like > > this: "We know we have all this data, but we don't know what we will > > want to do with it later. So keep it all. I'll get back to you when > > I want to know something." > > You should be able to do that with tablespaces and VACUUM FREEZE, the > point of the latter being that you can take the disk containing the > "read only" data offline, and still have the data readable after > plugging it back in, no matter how far along the transaction ID counter > is. Doesn't work anymore because VACUUM FREEZE doesn't (and can't) take a full table lock, so somebody can always update data after a data block has been frozen. That can lead to putting a table onto read-only media when it still needs vacuuming, which is a great way to break the DB. It also doesn't freeze still visible data, so there's no easy way of doing this. Waiting until the VF is the oldest Xid is prone to deadlock as well. Ideally, we'd have a copy to read-only media whilst freezing, as an atomic operation, with some guarantees that it will actually have frozen *everything*, or fail: ALTER TABLE SET TABLESPACE foo READONLY; Can we agree that as a TODO item? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Andrew Sullivan wrote: > On Mon, Feb 19, 2007 at 10:33:24AM -0500, Tom Lane wrote: > > Martijn van Oosterhout writes: > > > Somehow this seems like implementing RAID within postgres, > > > > RAID and LVM too. I can't get excited about re-inventing those wheels > > when perfectly good implementations already exist for us to sit on top of. > > Ok, warning, this is a "you know what would be sweet" moment. > > What would be nice is to be able to detach one of the volumes, and > know the span of the data in there without being able to access the > data. > > The problem that a lot of warehouse operators have is something like > this: "We know we have all this data, but we don't know what we will > want to do with it later. So keep it all. I'll get back to you when > I want to know something." You should be able to do that with tablespaces and VACUUM FREEZE, the point of the latter being that you can take the disk containing the "read only" data offline, and still have the data readable after plugging it back in, no matter how far along the transaction ID counter is. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Joshua D. Drake wrote: > Andrew Sullivan wrote: > > On Mon, Feb 19, 2007 at 10:33:24AM -0500, Tom Lane wrote: > >> Martijn van Oosterhout writes: > >>> Somehow this seems like implementing RAID within postgres, > >> RAID and LVM too. I can't get excited about re-inventing those wheels > >> when perfectly good implementations already exist for us to sit on top of. > > > > Ok, warning, this is a "you know what would be sweet" moment. > > The dreaded words from a developers mouth to every manager in the world. Yea, I just instinctively hit "delete" when I saw that phrase. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
On Mon, 19 Feb 2007, Joshua D. Drake wrote: Longer than that... it supported mirroring and raid 5 in NT4 and possibly even NT3.51 IIRC. Mirroring and RAID 5 go back to Windows NT 3.1 Advanced Server in 1993: http://support.microsoft.com/kb/114779 http://www.byte.com/art/9404/sec8/art7.htm The main source of confusion about current support for this feature is that the desktop/workstation version of Windows don't have it. For Windows XP, you need the XP Professional version to get "dynamic disk" support; it's not in the home edition. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Andrew Sullivan wrote: > On Mon, Feb 19, 2007 at 10:33:24AM -0500, Tom Lane wrote: >> Martijn van Oosterhout writes: >>> Somehow this seems like implementing RAID within postgres, >> RAID and LVM too. I can't get excited about re-inventing those wheels >> when perfectly good implementations already exist for us to sit on top of. > > Ok, warning, this is a "you know what would be sweet" moment. The dreaded words from a developers mouth to every manager in the world. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
On Mon, Feb 19, 2007 at 10:33:24AM -0500, Tom Lane wrote: > Martijn van Oosterhout writes: > > Somehow this seems like implementing RAID within postgres, > > RAID and LVM too. I can't get excited about re-inventing those wheels > when perfectly good implementations already exist for us to sit on top of. Ok, warning, this is a "you know what would be sweet" moment. What would be nice is to be able to detach one of the volumes, and know the span of the data in there without being able to access the data. The problem that a lot of warehouse operators have is something like this: "We know we have all this data, but we don't know what we will want to do with it later. So keep it all. I'll get back to you when I want to know something." It'd be nice to be able to load up all that data once, and then shunt it off into (say) read-only media. If one could then run a query that would tell one which spans of data are candidates for the search, you could bring back online (onto reasonably fast storage, for instance) just the volumes you need to read. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Stefan Kaltenbrunner wrote: > Peter Eisentraut wrote: >> Tom Lane wrote: >>> Martijn van Oosterhout writes: Somehow this seems like implementing RAID within postgres, >>> RAID and LVM too. I can't get excited about re-inventing those >>> wheels when perfectly good implementations already exist for us to >>> sit on top of. >> I expect that someone will point out that Windows doesn't support RAID >> or LVM, and we'll have to reimplement it anyway. > > windows supports software raid just fine since Windows 2000 or so ... Longer than that... it supported mirroring and raid 5 in NT4 and possibly even NT3.51 IIRC. Joshua D. Drake > > > Stefan > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Magnus Hagander wrote: > Windows supports both RAID and LVM. Oh good, so we've got that on record. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Peter Eisentraut wrote: > Tom Lane wrote: >> Martijn van Oosterhout writes: >>> Somehow this seems like implementing RAID within postgres, >> RAID and LVM too. I can't get excited about re-inventing those >> wheels when perfectly good implementations already exist for us to >> sit on top of. > > I expect that someone will point out that Windows doesn't support RAID > or LVM, and we'll have to reimplement it anyway. windows supports software raid just fine since Windows 2000 or so ... Stefan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Peter Eisentraut wrote: > Tom Lane wrote: >> Martijn van Oosterhout writes: >>> Somehow this seems like implementing RAID within postgres, >> RAID and LVM too. I can't get excited about re-inventing those >> wheels when perfectly good implementations already exist for us to >> sit on top of. > > I expect that someone will point out that Windows doesn't support RAID > or LVM, and we'll have to reimplement it anyway. Windows supports both RAID and LVM. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Tom Lane wrote: > Martijn van Oosterhout writes: > > Somehow this seems like implementing RAID within postgres, > > RAID and LVM too. I can't get excited about re-inventing those > wheels when perfectly good implementations already exist for us to > sit on top of. I expect that someone will point out that Windows doesn't support RAID or LVM, and we'll have to reimplement it anyway. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
On Mon, Feb 19, 2007 at 05:10:36PM +0100, Dimitri Fontaine wrote: > > RAID and LVM too. I can't get excited about re-inventing those wheels > > when perfectly good implementations already exist for us to sit on top of. > > I though moving some knowledge about data availability into PostgreSQL code > could provide some valuable performance benefit, allowing to organize reads > (for example parallel tables scan/indexes scan to different volumes) and > obtaining data from 'quicker' known volume (or least used/charged). Well, organising requests to be handled quickly is a function of LVM/RAID, so we don't go there. However, speeding up scans by having multiple requests is an interesting approach, as would perhaps a different random_page_cost for different tablespaces. My point is, don't try to implement the mechanics of LVM/RAID into postgres, instead, work on providing ways for users to take advantage of these mechanisms if they have them. Look at it as if you have got LVM/RAID setup for your ideas, how do you get postgres to take advantage of them? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Dimitri Fontaine <[EMAIL PROTECTED]> writes: > You're both saying RAID/LVM implementations provide good enough performances > for PG not having to go this way, if I understand correctly. There's certainly no evidence to suggest that reimplementing them ourselves would be a productive use of our time. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Le lundi 19 février 2007 16:33, Tom Lane a écrit : > Martijn van Oosterhout writes: > > Somehow this seems like implementing RAID within postgres, > > RAID and LVM too. I can't get excited about re-inventing those wheels > when perfectly good implementations already exist for us to sit on top of. I though moving some knowledge about data availability into PostgreSQL code could provide some valuable performance benefit, allowing to organize reads (for example parallel tables scan/indexes scan to different volumes) and obtaining data from 'quicker' known volume (or least used/charged). You're both saying RAID/LVM implementations provide good enough performances for PG not having to go this way, if I understand correctly. And distributed file systems are enough to have the replication stuff, without PG having to deal explicitly with the work involved. May be I should have slept after all ;) Thanks for your time and comments, regards, -- Dimitri Fontaine ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
Martijn van Oosterhout writes: > Somehow this seems like implementing RAID within postgres, RAID and LVM too. I can't get excited about re-inventing those wheels when perfectly good implementations already exist for us to sit on top of. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Multiple Storage per Tablespace, or Volumes
On Mon, Feb 19, 2007 at 11:25:41AM +0100, Dimitri Fontaine wrote:
> Hi list,
>
> Here's a proposal of this idea which stole a good part of my night.
> I'll present first the idea, then 2 use cases where to read some rational and
> few details. Please note I won't be able to participate in any development
> effort associated with this idea, may such a thing happen!
>
> The bare idea is to provide a way to 'attach' multiple storage facilities
> (say
> volumes) to a given tablespace. Each volume may be attached in READ ONLY,
> READ WRITE or WRITE ONLY mode.
> You can mix RW and WO volumes into the same tablespace, but can't have RO
> with
> any W form, or so I think.
Somehow this seems like implementing RAID within postgres, which seems
a bit outside of the scope of a DB.
> Use Case A: better read performances while keeping data write reliability
>
> The first application of this multiple volumes per tablespace idea is to keep
> a tablespace both into RAM (tmpfs or ramfs) and on disk (both RW).
For example, I don't beleive there is a restiction against having one
member of a RAID array being a RAM disk.
> Use Case B: Synchronous Master Slave(s) Replication
>
> By using a Distributed File System capable of being mounted from several
> nodes
> at the same time, we could have a configuration where a master node has
> ('exports') a WO tablespace volume, and one or more slaves (depending on FS
> capability) configures a RO tablespace volume.
Here you have the problem of row visibility. The data in the table isn't
very useful without the clog, and that's not stored in a tablespace...
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
