Thread: Re: [GENERAL] Physical Database Configuration

Re: [GENERAL] Physical Database Configuration

From
"Shridhar Daithankar"
Date:
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.


Re: [GENERAL] Physical Database Configuration

From
"Christopher Kings-Lynne"
Date:
> 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



Re: [GENERAL] Physical Database Configuration

From
"Shridhar Daithankar"
Date:
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



Re: [GENERAL] Physical Database Configuration

From
"Christopher Kings-Lynne"
Date:
> > 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



Re: [GENERAL] Physical Database Configuration

From
"Shridhar Daithankar"
Date:
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



Re: [GENERAL] Physical Database Configuration

From
Tom Lane
Date:
"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


Re: [GENERAL] Physical Database Configuration

From
johnnnnnn
Date:
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



Re: [GENERAL] Physical Database Configuration

From
AgentM
Date:
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




Re: [GENERAL] Physical Database Configuration

From
Jeff
Date:
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/




Re: [GENERAL] Physical Database Configuration

From
"Andrew Dunstan"
Date:
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





Re: [GENERAL] Physical Database Configuration

From
johnnnnnn
Date:
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



Re: [GENERAL] Physical Database Configuration

From
nolan@celery.tssi.com
Date:
> 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


Re: [GENERAL] Physical Database Configuration

From
Andreas Pflug
Date:
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



Re: [GENERAL] Physical Database Configuration

From
Fernando Schapachnik
Date:
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.

Re: [GENERAL] Physical Database Configuration

From
nolan@celery.tssi.com
Date:
> 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