Thread: Re: [GENERAL] Physical Database Configuration
On 24 Jun 2003 at 14:48, Jonathan Bartlett wrote: > I know the current method for specifying alternate drives for PG tables is > by using symlinks. I had some ideas for simple ways to do this in PG > code, but wanted to know if anyone was working on this right now. I'd > hate to take the time to start messing with this if others were already on > it. Well, correct solution is to implement tablespaces on which objects like databases, tables and indexes can be put. There was a long discussion on this and there was a tablespaces patch. It was agreed that tablespace as a set of directories would be a good point to start. I have no idea what is the status of that effort right now. You can search the archives or I hope this kicks a fresh discussion..:-) Please correct me if I am wrong. I am quoting from off my head.. not a trusted source.. Bye Shridhar -- Harriet's Dining Observation: In every restaurant, the hardness of the butter pats increases in direct proportion to the softness of the bread.
> Well, correct solution is to implement tablespaces on which objects like > databases, tables and indexes can be put. I have started working on tablespaces (to the extent that I am capable!), based not on the rejected patch, but on Jim's eventual syntax proposal that was never developed. eg. CREATE LOCATION blah AS '/exports/indexes' CREATE DATABASE db WITH LOCATION loc; CREAT TABLE foo (a PRIMARY KEY LOCATION blah) LOCATION blah; ..etc... > There was a long discussion on this and there was a tablespaces patch. It was > agreed that tablespace as a set of directories would be a good point to start. If anyone wants to help me (as I've not had time to code on it for a while due to phpPgAdmin), then they can email me! I'm working from a top-down perspective - eg. adding new catalog and grammar and support functions before mucking about with low level storage... Chris
On 25 Jun 2003 at 14:55, Christopher Kings-Lynne wrote: > > Well, correct solution is to implement tablespaces on which objects like > > databases, tables and indexes can be put. > > I have started working on tablespaces (to the extent that I am capable!), > based not on the rejected patch, but on Jim's eventual syntax proposal that > was never developed. Hmm... Remember feature freeze is 1st of July. So unless you send out a minimally working patch before that, it won't be considered for 7.4. > CREATE LOCATION blah AS '/exports/indexes' > > CREATE DATABASE db WITH LOCATION loc; > > CREAT TABLE foo (a PRIMARY KEY LOCATION blah) LOCATION blah; > > ..etc... > > > There was a long discussion on this and there was a tablespaces patch. It > was > > agreed that tablespace as a set of directories would be a good point to > start. > > If anyone wants to help me (as I've not had time to code on it for a while > due to phpPgAdmin), then they can email > me! > > I'm working from a top-down perspective - eg. adding new catalog and grammar > and support functions before mucking about with low level storage... I would love to hack this one. Especially putting WAL in a location that is configurable, I mean PG knowing where to find it's WAL. If you complete the syntactic part, I guess a very very rough patch should be possible before feature freeze but that is way tooooooo optimistic. Besides I don't know what core thinks about introducing such a feature right now. I think it would be a tough sell at this point of time.. ByeShridhar -- Virtue is a relative term. -- Spock, "Friday's Child", stardate 3499.1
> > I have started working on tablespaces (to the extent that I am capable!), > > based not on the rejected patch, but on Jim's eventual syntax proposal that > > was never developed. > > Hmm... Remember feature freeze is 1st of July. So unless you send out a > minimally working patch before that, it won't be considered for 7.4. I have no intention of having it ready anywhere near 7.4 :) > > I'm working from a top-down perspective - eg. adding new catalog and grammar > > and support functions before mucking about with low level storage... > > I would love to hack this one. Especially putting WAL in a location that is > configurable, I mean PG knowing where to find it's WAL. This patch won't affect WAL location - that's a separate issue. > If you complete the syntactic part, I guess a very very rough patch should be > possible before feature freeze but that is way tooooooo optimistic. Not going to happen :) I haven't done very much on it yet. Chris
On 25 Jun 2003 at 12:30, Shridhar Daithankar wrote: > On 25 Jun 2003 at 14:55, Christopher Kings-Lynne wrote: > > > > Well, correct solution is to implement tablespaces on which objects like > > > databases, tables and indexes can be put. > > > > I have started working on tablespaces (to the extent that I am capable!), > > based not on the rejected patch, but on Jim's eventual syntax proposal that > > was never developed. For reference, http://archives.postgresql.org/pgsql-hackers/2002-09/msg01780.php ByeShridhar -- Rules for Academic Deans: (1) HIDE!!!! (2) If they find you, LIE!!!! -- Father Damian C. Fandal
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I have started working on tablespaces (to the extent that I am capable!),
> based not on the rejected patch, but on Jim's eventual syntax proposal that
> was never developed.
Has anyone looked at the syntaxes used by other databases to control
tablespaces (Oracle, DB2, etc)? I have no strong desire to slavishly
follow Oracle, but it would be a shame to miss out on any good ideas.
regards, tom lane
On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote:
> Has anyone looked at the syntaxes used by other databases to control
> tablespaces (Oracle, DB2, etc)? I have no strong desire to
> slavishly follow Oracle, but it would be a shame to miss out on any
> good ideas.
DB2:
CREATE TABLESPACE spacename ...
ALTER TABLESPACE spacename ...
RENAME TABLESPACE spacename TO newspacename
CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN spacename]
"INDEX IN" and "LONG IN" refer to the tablespace used to store the
indices and the LOB values for that table, respectively.
The create syntax revolves around nodegroups and such which are DB2
concepts i don't fully grok (i'm a programmer, not a DBA).
But, yeah, those are really the only entrypoints. You can't create an
index in a specific tablespace -- it will go wherever the table is set
to put indices.
I like the syntax ("IN spacename"), though. It's simple and
straightforward.
-johnnnnnnnnnn
On Wednesday, June 25, 2003, at 12:10 PM, johnnnnnn wrote:
> On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote:
>> Has anyone looked at the syntaxes used by other databases to control
>> tablespaces (Oracle, DB2, etc)? I have no strong desire to
>> slavishly follow Oracle, but it would be a shame to miss out on any
>> good ideas.
>
> DB2:
> CREATE TABLESPACE spacename ...
> ALTER TABLESPACE spacename ...
> RENAME TABLESPACE spacename TO newspacename
> CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN
> spacename]
> "INDEX IN" and "LONG IN" refer to the tablespace used to store the
> indices and the LOB values for that table, respectively.
> The create syntax revolves around nodegroups and such which are DB2
> concepts i don't fully grok (i'm a programmer, not a DBA).
> But, yeah, those are really the only entrypoints. You can't create an
> index in a specific tablespace -- it will go wherever the table is set
> to put indices.
> I like the syntax ("IN spacename"), though. It's simple and
> straightforward.
Oracle 8 examples:
CREATE TABLE name(dud INTEGER) storage 8M next 4M pctincrease 0
minextents 1 maxextents 200 tablespace TSNAME;
where storage, next, pctincrease, minextents, and maxentents are table
space usage granularity requests.
CREATE TABLESPACE TSNAME datafile '/path/file.dbf' size 100M,
'/another/file.dbf' size 50M default storage (initial 1M next 1M
pctincrease 0 maxentents 249);
where each comma-delimited item is an "extent"- simply put, a block
which Oracle is allowed to use for storage.
ALTER TABLESPACE TEMP ...;
allows for arbitrary placement of temporary table storage (higher-speed
area?)
ALTER TABLESPACE TSNAME default storage (...);
changes settings for tablespace.
ALTER TABLESPACE TSNAME coalesce;
more extent "optimization" granularlity.
CREATE ROLLBACK SEGMENT R1 tablespace TSNAME2 storage (...);
which allocates space for a rollback area.
ALTER ROLLBACK SEGMENT R1 offline/online;
allows for cleanup of rollback segment's area.
CREATE TABLE name(dud INTEGER PRIMARY KEY USING INDEX ....);
allows for pointing an index to a tablespace.
CREATE INDEX ind ON table(col) global/local partition by range(col)
(partition PART1 values less than (11) tablespace TS1,
partition PART2 values less than (21) tablespace TS2,
....
partition PART3 values less than (MAXVALUE) tablespace TS3);
allows for a partioned index across tablespaces, but whose grammar
setup could use some work.
ALTER TABLE table MODIFY PARTITION part1storage (...) logging/nologgingMOVE PARTITIONADD PARTITION part1 values less
than(...)DROP PARTITIONTRUNCATE PARTITIONSPLIT PARTITION ... INTO ...EXCHANGE PARTITION
a nasty alter table command related to partitions (a tablespace can
have multiple partitions).
I post this just so there a flavor of how many "optimization" options
are available in Oracle 8. Personally, I would prefer not to have so
many options but this listing should help folks so they don't paint
themselves into a corner while coding on the tablespaces.
All examples courtesy of "Oracle 8: Advanced Tuning and
Administration", Aronoff, Eyal, et al. ASIN: 0078822416 (c) 1998.
(perhaps a little outdated)
><><><><><><><><><
AgentM
agentm@cmu.edu
On Wed, 25 Jun 2003, Tom Lane wrote: > Has anyone looked at the syntaxes used by other databases to control > tablespaces (Oracle, DB2, etc)? I have no strong desire to slavishly > follow Oracle, but it would be a shame to miss out on any good ideas. > Informix is pretty bad. First, you use an external app to create the tablespace (known as a dbspace to informix). Lets call the new one 'newspace'. (the syntax is onspaces -c -d newspace -p /path/to/space -s size_in_kb -o offset_in_file I'll cry if we have something liek that in pg) then to 'use' the space: create table|index ... in newspace There's a bizzare syntax for copying a table from one space to another, but it is mostly useless since it runs in a transaction and if you have a big table.. well you get the idea. Where it gets more interesting is table fragments. Informix is able to fragment a table based on a few different criteria. Each fragment goes in a separate dbspace and the idea is the planner is smart enough to realize that it can parellelize seq scans and various other IO operations... but given the nature of postgres I don't think we could build something like that... (for the record, the fragment types are round robin and expression. You can fragment based on a limited-edition where clause.. ) -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
DB2 looks good. I have horrid, horrid memories of wrestling with the Oracle
"extent" madness.
andrew
AgentM wrote:
> On Wednesday, June 25, 2003, at 12:10 PM, johnnnnnn wrote:
>> On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote:
>>> Has anyone looked at the syntaxes used by other databases to control
>>> tablespaces (Oracle, DB2, etc)? I have no strong desire to
>>> slavishly follow Oracle, but it would be a shame to miss out on any
>>> good ideas.
>>
>> DB2:
>> CREATE TABLESPACE spacename ...
>> ALTER TABLESPACE spacename ...
>> RENAME TABLESPACE spacename TO newspacename
>> CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN
>> spacename]
>> "INDEX IN" and "LONG IN" refer to the tablespace used to store the
>> indices and the LOB values for that table, respectively.
>> The create syntax revolves around nodegroups and such which are DB2
>> concepts i don't fully grok (i'm a programmer, not a DBA).
>> But, yeah, those are really the only entrypoints. You can't create an
>> index in a specific tablespace -- it will go wherever the table is set
>> to put indices.
>> I like the syntax ("IN spacename"), though. It's simple and
>> straightforward.
> Oracle 8 examples:
>
> CREATE TABLE name(dud INTEGER) storage 8M next 4M pctincrease 0
> minextents 1 maxextents 200 tablespace TSNAME;
>
> where storage, next, pctincrease, minextents, and maxentents are table
> space usage granularity requests.
>
> CREATE TABLESPACE TSNAME datafile '/path/file.dbf' size 100M,
> '/another/file.dbf' size 50M default storage (initial 1M next 1M
> pctincrease 0 maxentents 249);
>
> where each comma-delimited item is an "extent"- simply put, a block
> which Oracle is allowed to use for storage.
>
> ALTER TABLESPACE TEMP ...;
>
> allows for arbitrary placement of temporary table storage (higher-speed
> area?)
>
> ALTER TABLESPACE TSNAME default storage (...);
>
> changes settings for tablespace.
>
> ALTER TABLESPACE TSNAME coalesce;
>
> more extent "optimization" granularlity.
>
> CREATE ROLLBACK SEGMENT R1 tablespace TSNAME2 storage (...);
>
> which allocates space for a rollback area.
>
> ALTER ROLLBACK SEGMENT R1 offline/online;
>
> allows for cleanup of rollback segment's area.
>
> CREATE TABLE name(dud INTEGER PRIMARY KEY USING INDEX ....);
>
> allows for pointing an index to a tablespace.
>
> CREATE INDEX ind ON table(col) global/local partition by range(col)
> (partition PART1 values less than (11) tablespace TS1,
> partition PART2 values less than (21) tablespace TS2,
> ....
> partition PART3 values less than (MAXVALUE) tablespace TS3);
>
> allows for a partioned index across tablespaces, but whose grammar
> setup could use some work.
>
> ALTER TABLE table MODIFY PARTITION part1storage (...) logging/nologging
> MOVE PARTITION
> ADD PARTITION part1 values less than (...)
> DROP PARTITION
> TRUNCATE PARTITION
> SPLIT PARTITION ... INTO ...
> EXCHANGE PARTITION
>
> a nasty alter table command related to partitions (a tablespace can
> have multiple partitions).
>
> I post this just so there a flavor of how many "optimization" options
> are available in Oracle 8. Personally, I would prefer not to have so
> many options but this listing should help folks so they don't paint
> themselves into a corner while coding on the tablespaces.
>
> All examples courtesy of "Oracle 8: Advanced Tuning and
> Administration", Aronoff, Eyal, et al. ASIN: 0078822416 (c) 1998.
> (perhaps a little outdated)
>
>
>
> ><><><><><><><><><
> AgentM
> agentm@cmu.edu
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
On Wed, Jun 25, 2003 at 10:30:31AM -0500, Andrew Dunstan wrote: > DB2 looks good. I have horrid, horrid memories of wrestling with the > Oracle "extent" madness. I do think that it's worth providing additional access points to tablespaces, though. That is, it would make sense to me to allow "CREATE INDEX indexname IN spacename", instead of attaching an indexspace to a table. This is especially true with postgresql, since i've seen more than one proposal for multi-table indices. If we're spacing indices based on the table, it's unclear where a given multi-table index should go. It would also allow for other flexibilities, like putting join indices (on foreign keys) in one tablespace, with indices for aggregation or sorting in another tablespace. So, my vote, as a non-code-contributing member, would be for a DB2-style syntax, without the "INDEX IN" and "LONG IN" extensions, but with the ability to put indices explicitly into a tablespace. -johnnnnnn
> DB2 looks good. I have horrid, horrid memories of wrestling with the Oracle > "extent" madness. I think Oracle's extents came from their fixed size data file legacy, in 9i the extent limits appear to be completely overridable and sometimes even ignored, such as the next extent size. I agree that the 128 extent limit was a pain, and the default for each new extent to be larger than the previous one created many problems. Oracle also took physical abstraction one level beyond 'tablespaces'. I think if each tablespace pointed to a specific directory, that'd be sufficient for me. And since I envision the tablespace as an attribute of the table that should take care of the 1GB file rollover issue, as the rollover would occur in the same directory as the first file. Without having delved into the code yet, setting up entries for user default tablespaces and system information is probably at least as much work as getting a tablespace to point to a specific directory for the purposes of opening or creating files for an object. My personal preference would be to have four tablespaces predefined as part of a new database, though initially they could all point to the same place: SYSTEM USER TEMP INDEXES What about the concepts of a 'read-only' tablespace, or taking tablespaces offline? -- Mike Nolan
johnnnnnn wrote: >On Wed, Jun 25, 2003 at 10:30:31AM -0500, Andrew Dunstan wrote: > > >>DB2 looks good. I have horrid, horrid memories of wrestling with the >>Oracle "extent" madness. >> >> > >I do think that it's worth providing additional access points to >tablespaces, though. That is, it would make sense to me to allow >"CREATE INDEX indexname IN spacename", instead of attaching an >indexspace to a table. > >This is especially true with postgresql, since i've seen more than one >proposal for multi-table indices. If we're spacing indices based on >the table, it's unclear where a given multi-table index should go. > >It would also allow for other flexibilities, like putting join indices >(on foreign keys) in one tablespace, with indices for aggregation or >sorting in another tablespace. > > I wonder why an index spanning multiple tables should be stored in a different location than the tables itself. If we're talking about derived tables, all data/index must be available at the same time to be meaningful, so why not restrict them to the same tablespace? This sounds like more flexibility than really useful to me. The philosophy of pgsql is to let the os and the io system distribute the load over disks and other resources, not to do it in the backend. That's why we need much less organizational effort than other systems that try to implement everything themselves, on raw devices etc. Regards, Andreas
En un mensaje anterior, nolan@celery.tssi.com escribió: > > Well, correct solution is to implement tablespaces on which objects like > > databases, tables and indexes can be put. > > I've not looked at the SQL standard, but it seems to me like the order > should be: > > Databases > Tablespaces > Schemas > Objects (tables, indexes, functions, etc.) > I'm not well versed in the SQL standard here, so maybe this is plain wrong, but I think it would be nice to have some kind of separation between the logical structure of the table (developer concern) and the physical disposition (DBA concern), unlike what Oracle does (CREATE TABLE ... TABLESPACE ... OTHER PHYSICAL PARAMETERS HERE). Maybe a way is having storage classes: CREATE TABLE ... STORAGE CLASS <name>. STORAGE CLASS <name> TABLESPACE ... Example of use: Developer: CREATE TABLE a (...) STORAGE CLASS big_tuples; CREATE TABLE b (...) STORAGE CLASS heavy_use; DBA: STORAGE CLASS big_tuples TABLESPACE x; STORAGE CLASS heavy_use TABLESPACE y; Regards. Fernando.
> Well, correct solution is to implement tablespaces on which objects like
> databases, tables and indexes can be put.
I've not looked at the SQL standard, but it seems to me like the order
should be:
Databases
Tablespaces
Schemas
Objects (tables, indexes, functions, etc.)
And it really isn't hierarchical. As I understand them (based on my
Oracle background), tablespaces, unlike schemas, do NOT create a layer
of data abstraction. That is to say, while the same table name
can exist in multiple schemas, only one instance of a given table name
within a given schema can exist, regardless of what tablespace it is in.
That makes the tablespace a property of an object.
Whether or not two databases can share tablespaces isn't clear to me,
though as a DBA I can think of good reasons why they probably shouldn't
do so, I'm not sure if that is an absolute.
> I have no idea what is the status of that effort right now. You can search the
> archives or I hope this kicks a fresh discussion..:-)
I'm game, though I'm also not ready to lead such a project, probably not
even the discussion on it.
--
Mike Nolan