Thread: 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.