Thread: Load balancing across disks

Load balancing across disks

From
Paul Lambert
Date:
Im in the process of finalising my conversion from M$ SQL server to
Postgres - all of which I'm very happy about so far.

The database I work with has 37 tables, 5 of which run into the order of
tens of millions of records and approximately another 10 can run into
millions depending on the size of the customers main system. The DB is
going to be expanded to have another 15 or so tables when I step up
replication from my primary OpenVMS based application, some of these
will also have extremely high usage - again depending on the size of the
customers main system.

In order to balance disk load and ensure faster data access, my current
SQL server setup has the data spread across 3 physical disk devices. One
question I would like to know which I can't find in the documentation
I've been reading is if Postgres has any similar data distribution
abilities.

I.e. can I create a data file on D drive which holds tables a, b and e,
and a data file on E drive which holds tables c, d and f.

If this is possible, could someone point me to some documentation so I
can experiment a little.

If not possible, I guess I'll have to upgrade to some faster hardware...
if they'll be willing to give me money for that ;)

BTW, I'm using 8.2 on M$ Weenblows (Yes I know weenblows sucks, but i
don't have sufficient unix/linux/other platform Postgres runs on
experience to run the db on another server.... I'll wait for someone to
port it to OpenVMS ;) and then use it on that (I know, I'm dreaming) -
OpenVMS makes unixish systems look like they have the reliability of
weenblows, I'll tell you that much :D)

Cheers.

--
Paul Lambert
Database Administrator
AutoLedgers


Re: Load balancing across disks

From
Douglas McNaught
Date:
Paul Lambert <paul.lambert@autoledgers.com.au> writes:

> I.e. can I create a data file on D drive which holds tables a, b and
> e, and a data file on E drive which holds tables c, d and f.
>
> If this is possible, could someone point me to some documentation so I
> can experiment a little.

Read the doc section on tablespaces.

-Doug

Re: Load balancing across disks

From
"Joshua D. Drake"
Date:
Paul Lambert wrote:
> Im in the process of finalising my conversion from M$ SQL server to
> Postgres - all of which I'm very happy about so far.
>
> The database I work with has 37 tables, 5 of which run into the order of
> tens of millions of records and approximately another 10 can run into
> millions depending on the size of the customers main system. The DB is
> going to be expanded to have another 15 or so tables when I step up
> replication from my primary OpenVMS based application, some of these
> will also have extremely high usage - again depending on the size of the
> customers main system.
>
> In order to balance disk load and ensure faster data access, my current
> SQL server setup has the data spread across 3 physical disk devices. One
> question I would like to know which I can't find in the documentation
> I've been reading is if Postgres has any similar data distribution
> abilities.
>
> I.e. can I create a data file on D drive which holds tables a, b and e,
> and a data file on E drive which holds tables c, d and f.

You are looking for tablespaces :).


>
> If this is possible, could someone point me to some documentation so I
> can experiment a little.

http://www.postgresql.org/docs/8.0/interactive/sql-createtablespace.html


>
> If not possible, I guess I'll have to upgrade to some faster hardware...
> if they'll be willing to give me money for that ;)
>
> BTW, I'm using 8.2 on M$ Weenblows (Yes I know weenblows sucks, but i
> don't have sufficient unix/linux/other platform Postgres runs on
> experience to run the db on another server.... I'll wait for someone to
> port it to OpenVMS ;) and then use it on that (I know, I'm dreaming) -
> OpenVMS makes unixish systems look like they have the reliability of
> weenblows, I'll tell you that much :D)
>
> Cheers.
>


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


Re: Load balancing across disks

From
Bruno Wolff III
Date:
On Mon, Jan 29, 2007 at 11:50:35 +0900,
  Paul Lambert <paul.lambert@autoledgers.com.au> wrote:
>
> In order to balance disk load and ensure faster data access, my current
> SQL server setup has the data spread across 3 physical disk devices. One
> question I would like to know which I can't find in the documentation
> I've been reading is if Postgres has any similar data distribution
> abilities.
>
> I.e. can I create a data file on D drive which holds tables a, b and e,
> and a data file on E drive which holds tables c, d and f.
>
> If this is possible, could someone point me to some documentation so I
> can experiment a little.

http://developer.postgresql.org/pgdocs/postgres/manage-ag-tablespaces.html

Re: Load balancing across disks

From
ff@suse.de
Date:
> You are looking for tablespaces :).

I don't doubt that he is.

... but wouldn't something as simple as RAID 1+0 give much of the same
benefit (being able to distribute the IO load to more disks), but without
the danger of incorrectly placing the table spaces?