Thread: Database over multiple drives

Database over multiple drives

From
Chris Ruprecht
Date:
Hi all,

Can I spread a database over multiple physical drives? I want to put temp
files somewhere, some tables somewhere, some other tables somewhere else
etc. Is this possible? Why? My db is growing at an enormous rate, last
month, it didn't exist, now it's 530 MB - and that's just one client, we're
expecting about 50 clients by year end.

Best regards,
Chris


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Re: Database over multiple drives

From
David Lizano
Date:
You can't do it with Postgres. To do it, Postgres must implement
"tablespaces" to spread the database in different localizations (who can be
different physical localizations, of course). Then a table can be assigned
to a tablespace.

If the RDBMS implements partitions also, you can distribute a table (by
rows) in different tablespaces. Of course it can improve the perfomance by
reducing the I/O operations.

>Hi all,
>
>Can I spread a database over multiple physical drives? I want to put temp
>files somewhere, some tables somewhere, some other tables somewhere else
>etc. Is this possible? Why? My db is growing at an enormous rate, last
>month, it didn't exist, now it's 530 MB - and that's just one client, we're
>expecting about 50 clients by year end.
>
>Best regards,
>Chris
>
>
>_________________________________________________________
>Do You Yahoo!?
>Get your free @yahoo.com address at http://mail.yahoo.com
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html


Re: Database over multiple drives

From
Ragnar Kjørstad
Date:
On Thu, May 31, 2001 at 10:37:26AM +0200, David Lizano wrote:
> You can't do it with Postgres. To do it, Postgres must implement
> "tablespaces" to spread the database in different localizations (who can be
> different physical localizations, of course). Then a table can be assigned
> to a tablespace.

Sure you can.
You can move some files to a different drive, and put a symlink in the
original directory.

Or, if you have an operatingsystem that has an logical volume manager,
you can concatenate several disks, use striping or whatever, to get a
logical device that spans several physical devices.


--
Ragnar Kjørstad
Big Storage

Re: Database over multiple drives

From
David Lizano
Date:
>On Thu, May 31, 2001 at 10:37:26AM +0200, David Lizano wrote:
> > You can't do it with Postgres. To do it, Postgres must implement
> > "tablespaces" to spread the database in different localizations (who
> can be
> > different physical localizations, of course). Then a table can be assigned
> > to a tablespace.
>
>Sure you can.
>You can move some files to a different drive, and put a symlink in the
>original directory.
>
>Or, if you have an operatingsystem that has an logical volume manager,
>you can concatenate several disks, use striping or whatever, to get a
>logical device that spans several physical devices.
>

Yes, you can do it, but it should be managed by the RDBMS, so it isn't the
optimal solution. Of course a RAID of disk improve the I/O perfomance.

If the RDBMS implements "tablespaces" is easier also to make a backup in a
big database.


Re: Database over multiple drives

From
"Andy Samuel"
Date:
Has anybody *really* tried this solution ?
Is it safe ?

TIA
Andy

----- Original Message -----
From: "Ragnar Kjørstad" <postgres@ragnark.vestdata.no>
To: "David Lizano" <david.lizano@izanet.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Thursday, May 31, 2001 4:33 PM
Subject: Re: Database over multiple drives


> On Thu, May 31, 2001 at 10:37:26AM +0200, David Lizano wrote:
> > You can't do it with Postgres. To do it, Postgres must implement
> > "tablespaces" to spread the database in different localizations (who can
be
> > different physical localizations, of course). Then a table can be
assigned
> > to a tablespace.
>
> Sure you can.
> You can move some files to a different drive, and put a symlink in the
> original directory.
>
> Or, if you have an operatingsystem that has an logical volume manager,
> you can concatenate several disks, use striping or whatever, to get a
> logical device that spans several physical devices.
>
>
> --
> Ragnar Kjørstad
> Big Storage
>



Re: Re: Database over multiple drives

From
Stefan Huber
Date:
At 11:55 2001-06-01 +0700, Andy Samuel wrote:
>Has anybody *really* tried this solution ?
>Is it safe ?

Not with LVM, but with Postgres.....7.0 I think, we had some extraordinary
large files and used symlinks to other partitions. Went fine.
Using a non-beta LVM should be as fine as using a single partition.

Stefan

--
If a man stands in the middle of the forest speaking and
there is no woman around to hear him... is he still wrong?


Re: Re: Database over multiple drives

From
Wm Brian McCane
Date:
Yes!!

I have done this very successfully.  I have mounts:

/usr/local/pgsql/data      8Gig slice on Primary IDE-Slave
/usr/local/pgsql/data2    8Gig slice on Secondary IDE-Master
/usr/local/pgsql/data3    4Gig slice on Primary IDE-Master

Then I move files from data/base/<dbdir> to data2/base/<dbdir> and create a
symbolic link with the following.

$ pg_ctl stop
*NOTE*  Make sure it really shut down, I have some long running tasks which
have bitten me during a moment of stupidity
$ cd /usr/local/pgsql/data/base/<dbdir>
$ mv <datafile> /usr/local/pgsql/data2/base/<dbdir>/<datafile>
$ ln -s /usr/local/pgsql/data2/base/<dbdir>/<datafile> .
$ pg_ctl start

I currently only have the pg_xlog directory on data3 because that drive is
also shared with the operating system.  But just moving the pg_xlog
directory alone gave me a significant performance boost.  By freeing up the
data drives from having to write those log files, I am less likely to have
to wait for the heads to move around after fsyncing a log file.  You can
also move entire database directories using commands similar to those above.

- brian


----- Original Message -----
From: "Andy Samuel" <andysamuel@geocities.com>
To: <pgsql-admin@postgresql.org>
Sent: Thursday, May 31, 2001 11:55 PM
Subject: [ADMIN] Re: Database over multiple drives


> Has anybody *really* tried this solution ?
> Is it safe ?
>
> TIA
> Andy
>
> ----- Original Message -----
> From: "Ragnar Kjørstad" <postgres@ragnark.vestdata.no>
> To: "David Lizano" <david.lizano@izanet.com>
> Cc: <pgsql-admin@postgresql.org>
> Sent: Thursday, May 31, 2001 4:33 PM
> Subject: Re: Database over multiple drives
>
>
> > On Thu, May 31, 2001 at 10:37:26AM +0200, David Lizano wrote:
> > > You can't do it with Postgres. To do it, Postgres must implement
> > > "tablespaces" to spread the database in different localizations (who
can
> be
> > > different physical localizations, of course). Then a table can be
> assigned
> > > to a tablespace.
> >
> > Sure you can.
> > You can move some files to a different drive, and put a symlink in the
> > original directory.
> >
> > Or, if you have an operatingsystem that has an logical volume manager,
> > you can concatenate several disks, use striping or whatever, to get a
> > logical device that spans several physical devices.
> >
> >
> > --
> > Ragnar Kjørstad
> > Big Storage
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>