Обсуждение: Load balancing across disks
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
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
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/
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
> 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?