Обсуждение: Indexes on separate disk ?

Поиск
Список
Период
Сортировка

Indexes on separate disk ?

От
Charlie Toohey
Дата:
I've looked around a lot and don't think this is possible with Postgres, but
figured I would ask in case I missed something. Is it possible to configure
things so that an index resides on a separate disk ? It doesn't look like it,
since they both have to reside in the same database, and the entire contents
of the database would reside underneath a single directory, and therefore on
a single disk.

Thanks
Charlie

Re: Indexes on separate disk ?

От
Ragnar Kjørstad
Дата:
On Thu, Jun 13, 2002 at 12:49:39PM -0700, Charlie Toohey wrote:
> I've looked around a lot and don't think this is possible with Postgres, but
> figured I would ask in case I missed something. Is it possible to configure
> things so that an index resides on a separate disk ? It doesn't look like it,
> since they both have to reside in the same database, and the entire contents
> of the database would reside underneath a single directory, and therefore on
> a single disk.

Maybe you can just move it to a different filesystem and use a symlink?

It should be transparent to postgresql unless it ever replaces the file.



--
Ragnar Kjørstad
Big Storage

Re: Indexes on separate disk ?

От
Tom Lane
Дата:
=?iso-8859-1?Q?Ragnar_Kj=F8rstad?= <postgres@ragnark.vestdata.no> writes:
> On Thu, Jun 13, 2002 at 12:49:39PM -0700, Charlie Toohey wrote:
>> I've looked around a lot and don't think this is possible with Postgres, but
>> figured I would ask in case I missed something. Is it possible to configure
>> things so that an index resides on a separate disk ?

> Maybe you can just move it to a different filesystem and use a symlink?

That's pretty much the standard hack: shut down the postmaster, move the
file, create a symlink.  However this is a fairly labor-intensive kluge.
Especially so if the file exceeds 1Gb, because then you will need to
deal with symlinking multiple segments --- and perhaps re-symlinking
them, if the size dips below a Gb boundary and then grows again.
Still, for sub-Gb-sized indexes it's certainly doable.

We hope to have a cleaner tablespace-based approach in a release or two.

BTW, I'd certainly recommend getting the WAL files (pg_xlog directory)
moved to their own drive long before you worry about separating indexes
from data.  That's a lot simpler (you only need a symlink for the
directory).

            regards, tom lane

Re: Indexes on separate disk ?

От
DHSC Webmaster
Дата:
You sure can, Charlie.
We have our indexes, lightly used/smaller tables and heavily used/larger
tables split across several disks.
In a nutshell,
1. You just have to create the objects.
2. Then identify the objects using oid2name.
3. Shut down your database and move the objects.
4. Then put symlinks in their place pointing to their new destination.
5. Fire it back up.

(creating a reliable backup before this procedure is highly recommended)

Charlie Toohey wrote:
>
> I've looked around a lot and don't think this is possible with Postgres, but
> figured I would ask in case I missed something. Is it possible to configure
> things so that an index resides on a separate disk ? It doesn't look like it,
> since they both have to reside in the same database, and the entire contents
> of the database would reside underneath a single directory, and therefore on
> a single disk.
>
> Thanks
> Charlie
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Bill MacArthur
Webmaster
DHS Club


Re: Indexes on separate disk ?

От
Brian McCane
Дата:
I have a perl script that I have written which handles moving indexes from
one filesystem to another.  It currently only moves indexes, but could be
easily extended to include tables.  The syntax of the command looks like:

./moveindex.pl -s /db1/185204209 -d /db2/185204209 -i foo_pkey bar

It looks up the 'relfilenode' for 'foo_pkey' (-i parameter) in 'pg_class'
for the 'bar' database.  It then checks to make sure that the file does
exist in the source directory (-s parameter) and does not exist in the
desination directory (-d paramater).  If all looks good, it does a:

pg_ctl stop -s -m fast

and if there is no error shutting down, does the move/link/restart thing.

CAVEATS:

1) long running queries/open cursors can prevent shutdown.  the script
terminates correctly, but the postmaster will eventually shutdown since it
has been told to do so.  (you only forget this once ;).

2) The script does not currently check permissions in the source and
destination.  This shouldn't be a problem because if it can't move the
file it just leaves it where it was.  Then the symlink fails because the
file exists.

3) It might be possible to really confuse the postmaster if you move an
index to a directory, then move it to another directory, then move it
again.  You might be able to create a loop in your symlinks if you do this
just right.  I haven't done it, but when I get nervous I still move the
files by hand, JIC :).

Anyway, if people are interested in seeing what I have, gimme a holler and
I will put it up for download somewhere, or post it if people prefer.

- brian


On Thu, 13 Jun 2002, DHSC Webmaster wrote:

>
> You sure can, Charlie.
> We have our indexes, lightly used/smaller tables and heavily used/larger
> tables split across several disks.
> In a nutshell,
> 1. You just have to create the objects.
> 2. Then identify the objects using oid2name.
> 3. Shut down your database and move the objects.
> 4. Then put symlinks in their place pointing to their new destination.
> 5. Fire it back up.
>
> (creating a reliable backup before this procedure is highly recommended)
>
> Charlie Toohey wrote:
> >
> > I've looked around a lot and don't think this is possible with Postgres, but
> > figured I would ask in case I missed something. Is it possible to configure
> > things so that an index resides on a separate disk ? It doesn't look like it,
> > since they both have to reside in the same database, and the entire contents
> > of the database would reside underneath a single directory, and
therefore on
> > a single disk.
> >
> > Thanks
> > Charlie
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> --
> Bill MacArthur
> Webmaster
> DHS Club
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"


Re: Indexes on separate disk ?

От
Steve Lane
Дата:
On 6/13/02 10:22 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> BTW, I'd certainly recommend getting the WAL files (pg_xlog directory)
> moved to their own drive long before you worry about separating indexes
> from data.  That's a lot simpler (you only need a symlink for the
> directory).

tom:

Could you elaborate a bit on what the benefits of this approach would be?
I'm configuring a new server for PgSQL use shortly and would like to take
this into account if it will benefit me.

-- sgl