tablespaces and DB administration

pgsql@mohawksoft.com writes:

First, we keep the standard PostgreSQL directory the way it has always
been with template0, template1, pg_xlog, pg_clog, etc. in the same place.
We can refer to this as the "system" directory. This makes sense because
all the system level stuff is there. User databases should be discouraged
from the system, and users should be encouraged to create and use separate
tablespaces for their databases.

Why?

This seems to me to be pushing complexity onto users whether they
want/need it or not. I think that only a relatively small number of
installations will have any use for tablespaces, and we should not
try to coerce average users into worrying about them.

regards, tom lane

pgsql@mohawksoft.com writes:

First, we keep the standard PostgreSQL directory the way it has always
been with template0, template1, pg_xlog, pg_clog, etc. in the same
place.
We can refer to this as the "system" directory. This makes sense because
all the system level stuff is there. User databases should be
discouraged
from the system, and users should be encouraged to create and use
separate
tablespaces for their databases.

Why?

This seems to me to be pushing complexity onto users whether they
want/need it or not. I think that only a relatively small number of
installations will have any use for tablespaces, and we should not
try to coerce average users into worrying about them.

I forgot to specify that tablepaces should be on separate volumes. (sorry)
If all they have is one volume, no worries, but instructing the use of
alternate volumes for system and data will improve performance by
separating WAL and data operations.

Tablespaces are a familiar construct to experienced DBAs who may not be
familiar with PostgreSQL. PostgreSQL being similar to other databases will
have it better "make sense" to new users.

Users are primarily, if not stupid, ignorant. They will read the absolute
minimum needed to achieve a goal and little else. I say this with the
utmost respect, because I and probably everyone else on this group is
guilty of the same thing. So, the "preferred" installation procedure, i.e.
the one with the easy to follow directions, should showcase features the
user should know, and leave the user in a good place. IMHO, the user's
database on one volume and pg_xlog on another is a better starting place.

BTW: Is there a public spec on what will be tablespace compatible and how?
For instance: will is be possible to create a table on a separate
tablespace than the DB? Will it be possible to create an index on a
separate tablespace than the table?

BTW: Is there a public spec on what will be tablespace compatible and how?
For instance: will is be possible to create a table on a separate
tablespace than the DB? Will it be possible to create an index on a
separate tablespace than the table?

(Since Gavin hasn't replied yet)

1. There are two default tablespaces, global and default. Global is for
shared catalogs, default is for all other catalog data.

2. By default databases are created in the default tablespace, you can
override this.

3. By default schemas are created in the database's tablepsace, but this
can be overridden.

5. Tables and sequences are created in the schema's tablespace by
default, but this can be overridden

6. Indexes and SERIAL sequences are created in the table's tablespace by
default, but this can be overridden.

When pg_dumping, care is taken so that the tablespace qualification is
only dumped if the object is in its 'non-default' tablespace.

Chris

On May 26, 2004, at 7:14 PM, pgsql@mohawksoft.com wrote:

pgsql@mohawksoft.com writes:

First, we keep the standard PostgreSQL directory the way it has
always
been with template0, template1, pg_xlog, pg_clog, etc. in the same
place.
We can refer to this as the "system" directory. This makes sense
because
all the system level stuff is there. User databases should be
discouraged
from the system, and users should be encouraged to create and use
separate
tablespaces for their databases.

Why?

This seems to me to be pushing complexity onto users whether they
want/need it or not. I think that only a relatively small number of
installations will have any use for tablespaces, and we should not
try to coerce average users into worrying about them.

I forgot to specify that tablepaces should be on separate volumes.
(sorry)
If all they have is one volume, no worries, but instructing the use of
alternate volumes for system and data will improve performance by
separating WAL and data operations.

Tablespaces are a familiar construct to experienced DBAs who may not be
familiar with PostgreSQL. PostgreSQL being similar to other databases
will
have it better "make sense" to new users.

Users are primarily, if not stupid, ignorant. They will read the
absolute
minimum needed to achieve a goal and little else. I say this with the
utmost respect, because I and probably everyone else on this group is
guilty of the same thing. So, the "preferred" installation procedure,
i.e.
the one with the easy to follow directions, should showcase features
the
user should know, and leave the user in a good place. IMHO, the user's
database on one volume and pg_xlog on another is a better starting
place.

Yes, that is generally the case (prefer pg_xlog on separate spindle),
but no
need to *forcibly* overcomplicate things if the box has only one
spindle,
or if they have only one single RAID'd partition configured. We should
continue to err on the side of keeping the path to a functional system
nice and simple, yet still offering superb functionality. Oracle gets
this
wrong. pg_autovacuum is another good step in this direction.

----
James Robinson
Socialserve.com

James Robinson wrote:

Users are primarily, if not stupid, ignorant. They will read the
absolute
minimum needed to achieve a goal and little else. I say this with the
utmost respect, because I and probably everyone else on this group is
guilty of the same thing. So, the "preferred" installation procedure,
i.e.
the one with the easy to follow directions, should showcase features the
user should know, and leave the user in a good place. IMHO, the user's
database on one volume and pg_xlog on another is a better starting
place.

Yes, that is generally the case (prefer pg_xlog on separate spindle),
but no
need to *forcibly* overcomplicate things if the box has only one spindle,
or if they have only one single RAID'd partition configured. We should
continue to err on the side of keeping the path to a functional system
nice and simple, yet still offering superb functionality. Oracle gets
this
wrong. pg_autovacuum is another good step in this direction.

In the age of inexpensive RAID, tablespaces have more or less lost their
relevance regarding performance. pgsql's philosophy respects this by
leaving the storage work up to the OS and disk subsystem. Even having
the xlog on a different spindle won't help too much; you'll probably be
better off if you stuff all your spindles in one raid on most systems.
For worse, splitting two disks into separate storage areas to have xlog
separated would degrade safety for very little performance gain. So the
advise is: one disk, no alternative. 2 to 20 disks: use a single raid.
more disks: examine your access patterns carefully before you believe
you can do the job better than your raid controller.

This leaves table spaces as a mere administrative feature, many (most)
installations will happily live without that.

Regards,
Andreas

Yes, that is generally the case (prefer pg_xlog on separate spindle),
but no
need to *forcibly* overcomplicate things if the box has only one
spindle,
or if they have only one single RAID'd partition configured. We should
continue to err on the side of keeping the path to a functional system
nice and simple, yet still offering superb functionality. Oracle gets
this
wrong. pg_autovacuum is another good step in this direction.

----
James Robinson
Socialserve.com

I wouldn't say "forcibly."

While I think everyone would agree, Oracle is the most arcane and
difficult system ever devised by man, one can look at it and bring away
the far too few good things it does do. Similarly, MSSQL likes to user
data and system data separated. Setting user expectation is important,
setting baseline usage by recommendation is important.

One of the places PostgreSQL errs is its default configuration. It is far
to conservitive. The default setup is typically how most users operate
their software. Oracle, while being the worst, has one benefit, it has no
recognizable default, every DBA must slog through endless options to set
it up. In the process they are made aware of the various tuning options
and the flexability of the system.

PostgreSQL, on the other hand, installs easily, and can be used almost
immediately. This is not a problem, per se', but the user is not exposed
to how to make the system perform well unless they read the manual beyond
the "installation." (which we know they won't) Compounding the problem,
various parameters in the default configuration file are too small for
serious servers.

I would love to re-write the installation chapter and perhaps add a
section on generic database tuning. Specifically, like my "PostgreSQL for
Windows" CD a couple years ago, I like to see three sections: "Personal,"
"Workgroup," and "server" configuration tips. I think we've all seen bad
reviews because people used PostgreSQL's default configuration.

James Robinson wrote:

Users are primarily, if not stupid, ignorant. They will read the
absolute
minimum needed to achieve a goal and little else. I say this with the
utmost respect, because I and probably everyone else on this group is
guilty of the same thing. So, the "preferred" installation procedure,
i.e.
the one with the easy to follow directions, should showcase features
the
user should know, and leave the user in a good place. IMHO, the user's
database on one volume and pg_xlog on another is a better starting
place.

Yes, that is generally the case (prefer pg_xlog on separate spindle),
but no
need to *forcibly* overcomplicate things if the box has only one
spindle,
or if they have only one single RAID'd partition configured. We should
continue to err on the side of keeping the path to a functional system
nice and simple, yet still offering superb functionality. Oracle gets
this
wrong. pg_autovacuum is another good step in this direction.

In the age of inexpensive RAID, tablespaces have more or less lost their
relevance regarding performance. pgsql's philosophy respects this by
leaving the storage work up to the OS and disk subsystem. Even having
the xlog on a different spindle won't help too much; you'll probably be
better off if you stuff all your spindles in one raid on most systems.
For worse, splitting two disks into separate storage areas to have xlog
separated would degrade safety for very little performance gain. So the
advise is: one disk, no alternative. 2 to 20 disks: use a single raid.
more disks: examine your access patterns carefully before you believe
you can do the job better than your raid controller.

This leaves table spaces as a mere administrative feature, many (most)
installations will happily live without that.

Regards,
Andreas

I would say that this is almost completely misinformed. Depending on the
OS and the hardware, of course, a write on one spindle may not affect the
performance of another.

There are so many great things that happen when you have separate
spindles. The OS manages the file systems separately, the device drivers
may be separate, and if the low-level I/O device driver is even different,
then you get your own bus mastering I/O buffers. All around good things
happen when you have separate spindles.

A single postgresql process may not see much benefit, because it does not
do background I/O, but multiple postgresql processes will perform better
because multiple I/O requests can be issued and processed simultaneously.
If you got good SMP in your kernel, even better.

"Mohawksoft":

I forgot to specify that tablepaces should be on separate volumes. (sorry)
If all they have is one volume, no worries, but instructing the use of
alternate volumes for system and data will improve performance by
separating WAL and data operations.

... and the place for this is the documentation, maybe with a nice script to
help automate it. Twisting users' arms will just get us a lot of angry
e-mail. Plus force separation of tablespaces is not appropriate for many
kinds of installations:
-- the 1MB 2-table database of someone's DVD collection;
-- the 700GB database running off a $75,000 NAS (which appears to the OS as a
single volume)

Also, you're getting confused here ... Tablespaces has nothing to do with the
location of pg_xlog.

Tablespaces are a familiar construct to experienced DBAs who may not be
familiar with PostgreSQL. PostgreSQL being similar to other databases will
have it better "make sense" to new users.

I'll have to disagree with you there. I used to be a certified MSSQL admin,
and I can tell you that not one in 25 members of MSDN Database forum had any
idea how to use the analogous feature on MSSQL -- despite it being operative
since 1998. So forcing new users to deal with tablespaces, even if they
don't need them, is a great way to get new users to adopt MySQL.

So, the "preferred" installation procedure, i.e.
the one with the easy to follow directions, should showcase features the
user should know, and leave the user in a good place.

No, the "preferred" newbie installation is the one that gets them up and
running and playing with PostgreSQL in the minimum amount of time. Setup is
boring, confusing, and often frustrating, and each step we add to the
required minimum setup loses us another dozen newbies who weren't sure if
they are ready to upgrade from MS Access or MySQL. Heck, for the CDs we're
making to hand out at conventions, we're running PostgreSQL on Knoppix so
that users don't have to install *anything*.

Now, if you want to add a "power user setup" to the Tutorial in our official
docs, please do! We could use more guides. But don't force the guy with
the personal DVD database to set things up like he's running Ameritrade.

Also, consider that about half our users install from packages: RPMs and Deb
packages (and soon MSI as well). Those users aren't going to be going
through a manual installation procedure at all, so your efforts to "educate"
them through proper database setup won't get very far.

IMHO, the user's
database on one volume and pg_xlog on another is a better starting place.

For some setups, yes. For others, no. It depends on your hardware and
application. And, as I said above, Tablespaces will not determine the
location of pg_xlog AFAIK.

BTW: Is there a public spec on what will be tablespace compatible and how?
For instance: will is be possible to create a table on a separate
tablespace than the DB? Will it be possible to create an index on a
separate tablespace than the table?

This is in the archives of this list. The whole point of tablespaces is to
allow placing individual tables and indexes on seperate volumes. AFAIR,
we're not that concerned about whole databases, which have always been easily
re-locatable via symlinks and/or mounts.

P.S. if you signed your e-mails, I'd stop calling you "mohawksoft".

--
Josh Berkus
Aglio Database Solutions
San Francisco

pgsql@mohawksoft.com wrote:

James Robinson wrote:

Users are primarily, if not stupid, ignorant. They will read the
absolute
minimum needed to achieve a goal and little else. I say this with the
utmost respect, because I and probably everyone else on this group is
guilty of the same thing. So, the "preferred" installation procedure,
i.e.
the one with the easy to follow directions, should showcase features
the
user should know, and leave the user in a good place. IMHO, the user's
database on one volume and pg_xlog on another is a better starting
place.

Yes, that is generally the case (prefer pg_xlog on separate spindle),
but no
need to *forcibly* overcomplicate things if the box has only one
spindle,
or if they have only one single RAID'd partition configured. We should
continue to err on the side of keeping the path to a functional system
nice and simple, yet still offering superb functionality. Oracle gets
this
wrong. pg_autovacuum is another good step in this direction.

In the age of inexpensive RAID, tablespaces have more or less lost their
relevance regarding performance. pgsql's philosophy respects this by
leaving the storage work up to the OS and disk subsystem. Even having
the xlog on a different spindle won't help too much; you'll probably be
better off if you stuff all your spindles in one raid on most systems.
For worse, splitting two disks into separate storage areas to have xlog
separated would degrade safety for very little performance gain. So the
advise is: one disk, no alternative. 2 to 20 disks: use a single raid.
more disks: examine your access patterns carefully before you believe
you can do the job better than your raid controller.

This leaves table spaces as a mere administrative feature, many (most)
installations will happily live without that.

Regards,
Andreas

I would say that this is almost completely misinformed. Depending on the
OS and the hardware, of course, a write on one spindle may not affect the
performance of another.

There are so many great things that happen when you have separate
spindles. The OS manages the file systems separately, the device drivers
may be separate, and if the low-level I/O device driver is even different,
then you get your own bus mastering I/O buffers. All around good things
happen when you have separate spindles.

A single postgresql process may not see much benefit, because it does not
do background I/O, but multiple postgresql processes will perform better
because multiple I/O requests can be issued and processed simultaneously.
If you got good SMP in your kernel, even better.

There are good white papers about DB IO performance, e.g from Microsoft.
They are not read very often...
If you dedicate drives to services, it's your responsibility to size
everything to have a balanced load. You'll probably end with some drives
being the bottleneck, while others are still almost idle. That's why
RAID shoud be used in the first and second place, it will distribute the
workload on all spindles equally until saturated. The recommendation to
use separate disks for this and that originates from ancient days where
performance had to be achieved by application level programming and
configuration, implementing own file systems on raw devices. pgsql
deliberately doesn't work like this.

If you may use lets say 10 disks, you'd probably something similar like
2x RAID1 for /
2x RAID1 for /tmp + swap
2x RAID1 for xlog
4x RAID5 for data

I bet you get better performance with all disks in one RAID5, because
now the system disks not only have no negative impact on DB transfer
performance, but add additional seek bandwidth to DB traffic.

Regards,
Andreas

In the age of inexpensive RAID, tablespaces have more or less lost their
relevance regarding performance. pgsql's philosophy respects this by
leaving the storage work up to the OS and disk subsystem. Even having
the xlog on a different spindle won't help too much; you'll probably be
better off if you stuff all your spindles in one raid on most systems.
For worse, splitting two disks into separate storage areas to have xlog
separated would degrade safety for very little performance gain. So the
advise is: one disk, no alternative. 2 to 20 disks: use a single raid.
more disks: examine your access patterns carefully before you believe
you can do the job better than your raid controller.

This leaves table spaces as a mere administrative feature, many (most)
installations will happily live without that.

Regards,
Andreas

Sorry, I just can't leave this one alone. Having multiple spindles i.e.
separate data paths to separate disks and disk systems makes a big
difference. Take this simple program:

testio.c >>>>>>>>>>>>>>>>>>>>>>>>>>

#include <unistd.h>
#include <stdio.h>
/* gcc testio.c -o testio */
int main(int argc, char **argv)
{
int i;
int blocks;
FILE *files[16];
int block[512];
int foo[512];
int nblocks = atoi(argv[1]);

printf("Using %d 2K blocks, total file size %d\n",
nblocks, sizeof(block)*nblocks);
for(i=2; i < argc; i++)
files[i]=fopen(argv[i], "w+b");
for(blocks=0; blocks < nblocks; blocks++)
for(i=2; i < argc; i++)
fwrite(block, 1, sizeof(block), files[i]);
for(i=2; i < argc; i++)
fseek(files[i], 0, SEEK_SET);
for(blocks=0; blocks < nblocks; blocks++)
for(i=2; i < argc; i++)
fread(foo, 1, sizeof(foo), files[i]);
}
<<<<<<<<<<<<<<<<<<<<

The purpose of the program is to write out files, and read them back in.
It is crude, obviously, and not a good general test, but it does show the
effect of which I am writing.

On my test system, I have a bunch of disks, but I'll use "/home" and
"/vol01" as examples: vol01 is ext2 and home is reiserfs, and both are IBM
ultra SCSI 10K RPM disks.

[root@node1 markw]# time ./testio 100000 /home/tmp/test.dat
Using 100000 2K blocks, total file size 204800000

real 0m6.790s
user 0m0.290s
sys 0m4.120s
[root@node1 markw]# time ./testio 100000 /vol01/tmp/test.dat
Using 100000 2K blocks, total file size 204800000

real 0m7.274s
user 0m0.210s
sys 0m1.940s

As you can see, they are fairly well matched +- filesystem issues. Now,
lets run the program across two disks:

[root@node1 markw]# time ./testio 100000 /vol01/tmp/test.dat
/home/tmp/test.dat
Using 100000 2K blocks, total file size 204800000

real 0m12.012s
user 0m0.610s
sys 0m6.820s

As you can see, it looks like almost double the time, and you might be
looking at this as proof that you are right. Actually, it is mostly an
artifact of program.

Now, look ate the results if I write two files to the same volume:

[root@node1 markw]# time ./testio 100000 /vol01/tmp/test.dat
/vol01/tmp/test.dat1
Using 100000 2K blocks, total file size 204800000

real 0m19.298s
user 0m0.680s
sys 0m3.990s

As you can see, it takes a LOT longer. That's because the disk has to do a
lot more work, the hardware data path has to carry twice as much data, and
you have to manage multiple accesses to a single object, the disk.

It doesn't take much imagination to see what this would mean for pg_xlog.

pgsql@mohawksoft.com wrote:

James Robinson wrote:

Users are primarily, if not stupid, ignorant. They will read the
absolute
minimum needed to achieve a goal and little else. I say this with the
utmost respect, because I and probably everyone else on this group is
guilty of the same thing. So, the "preferred" installation procedure,
i.e.
the one with the easy to follow directions, should showcase features
the
user should know, and leave the user in a good place. IMHO, the user's
database on one volume and pg_xlog on another is a better starting
place.

Yes, that is generally the case (prefer pg_xlog on separate spindle),
but no
need to *forcibly* overcomplicate things if the box has only one
spindle,
or if they have only one single RAID'd partition configured. We should
continue to err on the side of keeping the path to a functional system
nice and simple, yet still offering superb functionality. Oracle gets
this
wrong. pg_autovacuum is another good step in this direction.

In the age of inexpensive RAID, tablespaces have more or less lost their
relevance regarding performance. pgsql's philosophy respects this by
leaving the storage work up to the OS and disk subsystem. Even having
the xlog on a different spindle won't help too much; you'll probably be
better off if you stuff all your spindles in one raid on most systems.
For worse, splitting two disks into separate storage areas to have xlog
separated would degrade safety for very little performance gain. So the
advise is: one disk, no alternative. 2 to 20 disks: use a single raid.
more disks: examine your access patterns carefully before you believe
you can do the job better than your raid controller.

This leaves table spaces as a mere administrative feature, many (most)
installations will happily live without that.

Regards,
Andreas

I would say that this is almost completely misinformed. Depending on the
OS and the hardware, of course, a write on one spindle may not affect the
performance of another.

There are so many great things that happen when you have separate
spindles. The OS manages the file systems separately, the device drivers
may be separate, and if the low-level I/O device driver is even
different,
then you get your own bus mastering I/O buffers. All around good things
happen when you have separate spindles.

A single postgresql process may not see much benefit, because it does not
do background I/O, but multiple postgresql processes will perform better
because multiple I/O requests can be issued and processed simultaneously.
If you got good SMP in your kernel, even better.

There are good white papers about DB IO performance, e.g from Microsoft.

Do not trust *anything* from Microsoft, they have very dubious motives and
research practices. They are compulsive liars and everything published by
them is specifically edited to present their agenda.

They are not read very often...
If you dedicate drives to services, it's your responsibility to size
everything to have a balanced load. You'll probably end with some drives
being the bottleneck, while others are still almost idle. That's why
RAID shoud be used in the first and second place, it will distribute the
workload on all spindles equally until saturated. The recommendation to
use separate disks for this and that originates from ancient days where
performance had to be achieved by application level programming and
configuration, implementing own file systems on raw devices. pgsql
deliberately doesn't work like this.

If you may use lets say 10 disks, you'd probably something similar like
2x RAID1 for /
2x RAID1 for /tmp + swap
2x RAID1 for xlog
4x RAID5 for data

I bet you get better performance with all disks in one RAID5, because
now the system disks not only have no negative impact on DB transfer
performance, but add additional seek bandwidth to DB traffic.

Regards,
Andreas

You are absolutely wrong on all accounts here. A RAID5 system is slower
than a single spindle as it is only as fast as the slowest disk in the
stripe and the overhead of the RAID. RAID[3,5] are slower on writes
because they have to calculate the parity and write it to the parity disk.

One of the things you are failing to note is that different disks can
operate in parallel on an SMP box with good CPU interupt management. Two
writes to two different disks can take place at the same time. Two writes
to a single disk (or disk system) must happen serially.

"Mohawksoft":

I forgot to specify that tablepaces should be on separate volumes.
(sorry)
If all they have is one volume, no worries, but instructing the use of
alternate volumes for system and data will improve performance by
separating WAL and data operations.

... and the place for this is the documentation, maybe with a nice script
to
help automate it. Twisting users' arms will just get us a lot of angry
e-mail. Plus force separation of tablespaces is not appropriate for many
kinds of installations:
-- the 1MB 2-table database of someone's DVD collection;
-- the 700GB database running off a $75,000 NAS (which appears to the OS
as a
single volume)

Also, you're getting confused here ... Tablespaces has nothing to do with
the
location of pg_xlog.

I'm not "confused" but, it is an inverse logic thing. By persuading users
to create databases on a separate tablespace than the system (which
contains pg_xlog), you are by definition separating database and pg_xlog.

Tablespaces are a familiar construct to experienced DBAs who may not be
familiar with PostgreSQL. PostgreSQL being similar to other databases
will
have it better "make sense" to new users.

I'll have to disagree with you there. I used to be a certified MSSQL
admin,
and I can tell you that not one in 25 members of MSDN Database forum had
any
idea how to use the analogous feature on MSSQL -- despite it being
operative
since 1998. So forcing new users to deal with tablespaces, even if they
don't need them, is a great way to get new users to adopt MySQL.

That's food for thought. That's different than my experience. I've set up
a few MSSQL systems and recall it saying that you should create a
different database file from the system, but if you say that it will
confuse new users, that is something that should be considered.

So, the "preferred" installation procedure, i.e.
the one with the easy to follow directions, should showcase features the
user should know, and leave the user in a good place.

No, the "preferred" newbie installation is the one that gets them up and
running and playing with PostgreSQL in the minimum amount of time. Setup
is
boring, confusing, and often frustrating, and each step we add to the
required minimum setup loses us another dozen newbies who weren't sure if
they are ready to upgrade from MS Access or MySQL. Heck, for the CDs
we're
making to hand out at conventions, we're running PostgreSQL on Knoppix so
that users don't have to install *anything*.

That's cool, PostgreSQL on knoppix? How do you do that? RAM disk?

Now, if you want to add a "power user setup" to the Tutorial in our
official
docs, please do! We could use more guides. But don't force the guy
with
the personal DVD database to set things up like he's running Ameritrade.

Also, consider that about half our users install from packages: RPMs and
Deb
packages (and soon MSI as well). Those users aren't going to be going
through a manual installation procedure at all, so your efforts to
"educate"
them through proper database setup won't get very far.

I agree that there is a section missing in the manual for this sort of
information.

IMHO, the user's
database on one volume and pg_xlog on another is a better starting
place.

For some setups, yes. For others, no. It depends on your hardware and
application. And, as I said above, Tablespaces will not determine the
location of pg_xlog AFAIK.

See above/.

pgsql@mohawksoft.com wrote:

As you can see, it takes a LOT longer. That's because the disk has to do a
lot more work, the hardware data path has to carry twice as much data, and
you have to manage multiple accesses to a single object, the disk.

It doesn't take much imagination to see what this would mean for pg_xlog.

Well, writing off-reality benchmarks on hardware setups I'd never
suggest for db server usage proves... what?
Additionally, do you care about safety?

Regards,
Andreas

pgsql@mohawksoft.com wrote:

As you can see, it takes a LOT longer. That's because the disk has to do
a
lot more work, the hardware data path has to carry twice as much data,
and
you have to manage multiple accesses to a single object, the disk.

It doesn't take much imagination to see what this would mean for pg_xlog.

Well, writing off-reality benchmarks on hardware setups I'd never
suggest for db server usage proves... what?
Additionally, do you care about safety?

Regards,
Andreas

It was a demostration of the effect that multiple drives has. There is a
very real and measurable I/O bandwidth advantage to be gained by putting
concurrently accessed data on separate data channels. Any test that is
capable fo utilizing multiple I/O channels will show it.

This is not the place to really discuss this, and if you want to persue
this discussion, lets take it off line.

As for RAID5, it has its advantages, as does RAID0 and RAID3, and yes,
RAID1 and higher are pretty safe. A *good* RAID system goes for thousands
or tens of thousands of dollars. A 24x7 redundant storage system may not
be required by everyone. With new IDE with DMA interface cards, it should
be possible to create a very high performance system. A system which will
perform better if the pg_xlog is on a different disk than the data.

As for "do I care about safety?" Yes, yes I do, but "safe" and "paranoid"
are two different things. There is always a cost/benefit analysis for any
system. RAID5 and RAID3 are calculated risks. The bet is that no more than
one drive will fail at any one time. It is a good bet, but not 100%. I've
seen RAID systems have two drive failures at the same time, during the
auto-rebuild to the spare, a second drive dies. Game over.

Since RAID5 is a risk, maybe we should mirror, RAID0+1. That's a risk too,
what if the two mirrored drives die? Yikes, then you're screwed again.

If you want to be safe, you may want RAID5+1, where you mirror two RAID5
systems. That's really safe. You should have each RAID5 system on its own
controller with its own independent battery backed up redundant power
supply. That will be safe. Wait.. What about earth quakes? Shock mounted
racks. Lightning? Surge suppressors on the SCSI cables. Flood? Wrap it in
a zip-lock bag. Al Qaeda? Wrap it in a kevlar!!

There is a cost/benefit analysis for everything. RAIDs are very good and
reasonably safe devices, but the argument that the performance will be the
same as multiple individual disks (which are equivilent to the ones in the
RAID) preferably on different I/O channels is proveable nonsense and you
should know that.

pgsql@mohawksoft.com wrote:

You are absolutely wrong on all accounts here. A RAID5 system is slower
than a single spindle as it is only as fast as the slowest disk in the
stripe and the overhead of the RAID.

Huh, what kind of controller do you use... Sounds like some "value" IDE
one. I'd never suggest IDE raid5 for DBMS purposes anyway.

Actually, my RAID system, currently on my test system, is fully UWLVD SCSI
with fast spindles.

Here is a logical factual question for you to answer: how can a set of
disks, lets say 7, 6 data drives with one parity, deliver results faster
than the slowest drive in the stripe?

If you say predictive and intelligent caching, yea, maybe, but *all* disks
today have caching, but the initial request still has to wait for the
longest seek time across all spindles and the slowest spindle position.
I've been dealing with RAID systems for almost a decade now, and they are
not a magic bullet.

RAID systems are always slower than their compnent disks. This is the
drawback to using them and a fundimental limitation. A single disk will
average 1/2 spindle seek, assuming its initial head placement is random,
and average 1/2 spindle revolution to track, assuming no out of order
sector access. A RAID system has to wait for the slowest disk, thus while
a single disk can average 1/2 seek and rotation, two disks will not. So,
your raid disk access will ALWAYS be slower or as slow as a single disk
access not including the additional RAID processing.

The advantage to a RAID is that a number of smaller disks can look like a
big disk with some redundency. The advantage to a RAID controller is that
the RAID processing and parity generation overhead is done on an external
device. Using a RAID controller that presents a SCSI LUN is great because
you don't need to trust third party drivers. All in all, RAID is a good
idea, but it isn't faster.

As for IDE RAID, IDE RAID is an awesome idea. SCSI disks are just too
expensive. Infortrend has a cool IDE to SCSI or Fibre RAID system that
rocks.

On May 27, 2004 01:38 pm, pgsql@mohawksoft.com wrote:

pgsql@mohawksoft.com wrote:

As you can see, it takes a LOT longer. That's because the disk has to do
a
lot more work, the hardware data path has to carry twice as much data,
and
you have to manage multiple accesses to a single object, the disk.

It doesn't take much imagination to see what this would mean for pg_xlog.

Well, writing off-reality benchmarks on hardware setups I'd never
suggest for db server usage proves... what?
Additionally, do you care about safety?

Regards,
Andreas

It was a demostration of the effect that multiple drives has. There is a
very real and measurable I/O bandwidth advantage to be gained by putting
concurrently accessed data on separate data channels. Any test that is
capable fo utilizing multiple I/O channels will show it.

This is not the place to really discuss this, and if you want to persue
this discussion, lets take it off line.

As for RAID5, it has its advantages, as does RAID0 and RAID3, and yes,
RAID1 and higher are pretty safe. A *good* RAID system goes for thousands
or tens of thousands of dollars. A 24x7 redundant storage system may not
be required by everyone. With new IDE with DMA interface cards, it should
be possible to create a very high performance system. A system which will
perform better if the pg_xlog is on a different disk than the data.

As for "do I care about safety?" Yes, yes I do, but "safe" and "paranoid"
are two different things. There is always a cost/benefit analysis for any
system. RAID5 and RAID3 are calculated risks. The bet is that no more than
one drive will fail at any one time. It is a good bet, but not 100%. I've
seen RAID systems have two drive failures at the same time, during the
auto-rebuild to the spare, a second drive dies. Game over.

Since RAID5 is a risk, maybe we should mirror, RAID0+1. That's a risk too,
what if the two mirrored drives die? Yikes, then you're screwed again.

If you want to be safe, you may want RAID5+1, where you mirror two RAID5
systems. That's really safe. You should have each RAID5 system on its own
controller with its own independent battery backed up redundant power
supply. That will be safe. Wait.. What about earth quakes? Shock mounted
racks. Lightning? Surge suppressors on the SCSI cables. Flood? Wrap it in
a zip-lock bag. Al Qaeda? Wrap it in a kevlar!!

There is a cost/benefit analysis for everything. RAIDs are very good and
reasonably safe devices, but the argument that the performance will be the
same as multiple individual disks (which are equivilent to the ones in the
RAID) preferably on different I/O channels is proveable nonsense and you
should know that.

One other huge advantage that tablespaces will bring, it the ability to place
data based on "cost" ie, you can put your 10 most used tables on fast disk
(or perhaps solid state devices), and move the seldom used data off onto the
slower (lower cost) disks/storage array.

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx: 250.763.1759
http://www.wavefire.com

One other huge advantage that tablespaces will bring, it the ability to
place
data based on "cost" ie, you can put your 10 most used tables on fast disk
(or perhaps solid state devices), and move the seldom used data off onto
the
slower (lower cost) disks/storage array.

Great idea.

On Fri, 2004-05-28 at 08:15, pgsql@mohawksoft.com wrote:

pgsql@mohawksoft.com wrote:

You are absolutely wrong on all accounts here. A RAID5 system is slower
than a single spindle as it is only as fast as the slowest disk in the
stripe and the overhead of the RAID.

Huh, what kind of controller do you use... Sounds like some "value" IDE
one. I'd never suggest IDE raid5 for DBMS purposes anyway.

Actually, my RAID system, currently on my test system, is fully UWLVD SCSI
with fast spindles.

Here is a logical factual question for you to answer: how can a set of
disks, lets say 7, 6 data drives with one parity, deliver results faster
than the slowest drive in the stripe?

If you say predictive and intelligent caching, yea, maybe, but *all* disks
today have caching, but the initial request still has to wait for the
longest seek time across all spindles and the slowest spindle position.
I've been dealing with RAID systems for almost a decade now, and they are
not a magic bullet.

RAID systems are always slower than their compnent disks. This is the
drawback to using them and a fundimental limitation. A single disk will
average 1/2 spindle seek, assuming its initial head placement is random,
and average 1/2 spindle revolution to track, assuming no out of order
sector access. A RAID system has to wait for the slowest disk, thus while
a single disk can average 1/2 seek and rotation, two disks will not. So,
your raid disk access will ALWAYS be slower or as slow as a single disk
access not including the additional RAID processing.

Some high end SCSI drives comes with an option for using an external
source for spindle syncronization. These drives will thus not have to
wait for rotation, as head positions are aligned.

The advantage to a RAID is that a number of smaller disks can look like a
big disk with some redundency. The advantage to a RAID controller is that
the RAID processing and parity generation overhead is done on an external
device. Using a RAID controller that presents a SCSI LUN is great because
you don't need to trust third party drivers. All in all, RAID is a good
idea, but it isn't faster.

As for IDE RAID, IDE RAID is an awesome idea. SCSI disks are just too
expensive. Infortrend has a cool IDE to SCSI or Fibre RAID system that
rocks.

Addonics has these too, I've been using them with great results.

pgsql@mohawksoft.com wrote:

As for IDE RAID, IDE RAID is an awesome idea. SCSI disks are just too
expensive. Infortrend has a cool IDE to SCSI or Fibre RAID system that
rocks.

Obviously, you're caught by those marketing geeks. You're taking
bandwidth (MB/s)as performance index, which is irrelevant for database
access. Limiting factor is average access time, and there's still no 3ms
seek time ide disk. This is not a problem of the interface, it's just a
fact that (for marketing reasons?) all server grade disks are not
equipped with ide.
A good raid system will be able to have independend seeks issued on all
disks in parallel, thus scaling by spindle number (only for parallel
accessing processes of course, not for serialized access). What you're
proposing is that the app should parallelize it, instead of leaving this
to the instance that can (should) do this better.

Regards,
Andreas