Обсуждение: Multiple disks

What is the best/simplest way to split:
    a database
    multiple databases
    a table
    multiple tables
across more than one disk drive?

I know that this has come up before but I can't find
any info.  A pointer to a HOWTO or other info would
be wonderful.

hal

hal <hal@cc.usu.edu> writes:
> What is the best/simplest way to split:
> ...
> across more than one disk drive?

LVM or RAID solutions should do the job.

            regards, tom lane

On Thu, 8 Apr 2004, hal wrote:

> What is the best/simplest way to split:
>     a database
>     multiple databases
>     a table
>     multiple tables
> across more than one disk drive?
>
> I know that this has come up before but I can't find
> any info.  A pointer to a HOWTO or other info would
> be wonderful.

The current king of putting postgresql on multiple platters and getting
best overall performance is a battery backed caching raid controller run
one or more many disk RAID-5 arrays.  The more disks, the better up to a
dozen or two.

The red headed step child is the alternate location stuff, discussed in
the docs here:
http://www.postgresql.org/docs/7.4/static/manage-ag-alternate-locs.html
which will allow you to put individual databases in different locations.
It's a hackish kludge that should be replaced by the upcoming tablespaces
patches.  I'm not sure if those will be done by 7.5 release or not.

The next way is to individually link thinks like indexes onto other
volumes.  The procedure is basically, create the index, figure out which
file in $PGDATA/base/oidofyourdbhere is the index, shut down postgresql,
copy to file elsewhere, softlink it, restart apache.  This setup will not
survive reindexing or dropping / recreating the index.

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> The next way is to individually link thinks like indexes onto other
> volumes.  The procedure is basically, create the index, figure out which
> file in $PGDATA/base/oidofyourdbhere is the index, shut down postgresql,
> copy to file elsewhere, softlink it, restart apache.  This setup will not
> survive reindexing or dropping / recreating the index.

It should also be noted that this hack doesn't cope well with tables or
indexes exceeding 1Gb.  You'd have to separately symlink each segment,
and deal with creating new links every time the table/index grows past
another 1Gb boundary.

Tablespaces will be a lot nicer when we have them.  For now, a RAID
array is definitely the best bet.

            regards, tom lane

scott.marlowe wrote:

> The current king of putting postgresql on multiple platters and getting
> best overall performance is a battery backed caching raid controller run
> one or more many disk RAID-5 arrays.

I thought the best choice were RAID-10 (1+0) (when available). There
isn't a write penalty with RAID-5?

Thank you. Kind regards,

--
Cris Carampa (spamto:cris119@operamail.com)
The man thinks. The horse thinks.  The sheep thinks. The cow thinks.
The fish doesn't think: the fish is mute, expressionless.
The fish doesn't think, because the fish knows - everything. [Kusturica]

On Thu, 15 Apr 2004, Cris Carampa wrote:

> scott.marlowe wrote:
>
> > The current king of putting postgresql on multiple platters and getting
> > best overall performance is a battery backed caching raid controller run
> > one or more many disk RAID-5 arrays.
>
> I thought the best choice were RAID-10 (1+0) (when available). There
> isn't a write penalty with RAID-5?

there is for software RAID-5 or for non-battery backed cached RAID-5, but
for a battery backed controller, the penalty is basically gotten rid of by
being hidden in a lazy write.  Plus the controller can "gang" writes to
individual drives to achieve a very good throughput.