Thread: optimum postgres server configuration
We're about to buy a new server for dedicated Postgres serving. It'll be serving 4 - 10 databases (average dump size 20Gb) for various web applications. What is the list's advice on optimum configuration of hardware and software, given a smallish fixed budget (around $US 2000)? In particular, should we prioritise * Disk speed * Hardware RAID * Memory * Processor speed * Processor type * Multiple processors And does the list have any observations regarding Postgres performance on different platforms? Our webservers run Linux (RedHat 8), but we're willing to experiment with alternatives. Many thanks Tom -----------------+ tom dyson t: +44 (0)1608 811870 m: +44 (0)7958 752657 http://torchbox.com
> We're about to buy a new server for dedicated Postgres serving. It'll be > serving 4 - 10 databases (average dump size 20Gb) for various web > applications. What is the list's advice on optimum configuration of hardware > and software, given a smallish fixed budget (around $US 2000)? In > particular, should we prioritise > > * Disk speed > * Hardware RAID > * Memory > * Processor speed > * Processor type > * Multiple processors Even with RAID you need to think about an external backup method and budget for the hardware accordingly. 20GB x 10 databases is getting up there for tape backup both in terms of capacity and in the time it would take to do the backup. -- Mike Nolan
On Thu, 24 Jul 2003, tom dyson wrote: > We're about to buy a new server for dedicated Postgres serving. It'll be > serving 4 - 10 databases (average dump size 20Gb) for various web > applications. What is the list's advice on optimum configuration of hardware > and software, given a smallish fixed budget (around $US 2000)? In > particular, should we prioritise > > * Disk speed > * Hardware RAID > * Memory > * Processor speed > * Processor type > * Multiple processors This really kinda depends. Will these databases see much writing? Or will they be mostly read? Generally, the more writing, the faster you want your disk subsystem to be, and the more reading, the more memory / CPU horsepower you want. Especially if you'll be munging tables against each other a lot. I like having dual CPUs, as they aren't very expensive compared to single ones, and provide a very noticeable performance gain for me. I'd rather have dual 1400MHz CPUs than one 2800MHz CPU. You can't have too much memory. Hardware RAID is overrated. It's good, but software RAID on fast machines is every bit a match for hardware RAID in performance, the only issue being support for hot swapping and what not some hardware RAID cards have. On Linux sw RAID however, stacking RAID levels doesn't provide as much gain as it does with a hardware controller, so if you're looking at RAID 1+0, hardware is probably a better choice. 64 bit CPUs are nice if you're gonna use tons o ram to cache your databases. > And does the list have any observations regarding Postgres performance on > different platforms? Our webservers run Linux (RedHat 8), but we're willing > to experiment with alternatives. BSD is generally considered the best performer, with Linux a close second. Solaris is not so fast, but it does scale out to dozens and dozens of CPUs, and Sparc boxes tend to have pretty fast I/O (the real ones, not the IDE based workstations.) Note that many folks consider BSD or Linux on Sparc to be a nice solution for speed and expandability.
>>>>> "td" == tom dyson <tom@torchbox.com> writes: td> and software, given a smallish fixed budget (around $US 2000)? In td> particular, should we prioritise td> * Disk speed td> * Hardware RAID td> * Memory td> * Processor speed td> * Processor type td> * Multiple processors Like others have said, splurge on the disk speed. Personally I use hardware RAID 10, but I'm not on a tight budget. Any modern system will have enough CPU power. Next thing to maximize is RAM. Another thing to consider is leasing the equipment. Instead of putting up the $2k cash now, spread out the money outlay over three years. You can buy a much bigger machine with lower cash flow impact. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
Vivek Khera wrote: > >>>>> "td" == tom dyson <tom@torchbox.com> writes: > > td> and software, given a smallish fixed budget (around $US 2000)? In > td> particular, should we prioritise > > td> * Disk speed > td> * Hardware RAID > td> * Memory > td> * Processor speed > td> * Processor type > td> * Multiple processors > > Like others have said, splurge on the disk speed. Personally I use > hardware RAID 10, but I'm not on a tight budget. Any modern system > will have enough CPU power. Next thing to maximize is RAM. Alot of RAID system have sophisticated battery-backed cache, that helps PostgreSQL a lot. The RAID controller centralizes all that cache, rather than trying to get individual disk drives with that capability. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
| We're about to buy a new server for dedicated Postgres serving. It'll | be serving 4 - 10 databases (average dump size 20Gb) for various web | applications. What is the list's advice on optimum configuration of | hardware and software, given a smallish fixed budget (around $US | 2000)? In particular, should we prioritise Unfortunately, hardware sizing for databases is very much trial-and-error. How many users, type of queries, percentage of updates, transactions per period -- throw that all together you and you still have a wild-ass guess. But since you have a budget of $2000, that limits your choices so there's no point in going over stuff you can't get. Let's start with the following: - Case/Floppy/CD-ROM/PS: $200 - Athlon 760MPX: $200 - 2x Athlon MP2400+: $300 - 3GB RAM: $500 That leaves you $800 for storage. You definitely need to go SCSI without question. The question is whether to get a card with more channels or more disks on less channels. My personal experience is that the bandwidth of the HBAs are outpacing the hard drives. - Adaptec 29320-R (single channel U320 w/ RAID0,1) $200 - 4x 10K RPM 73GB HD $600 If you want hotswap, you can get 4/5-drive hotswap cages in the ~$200 range. Almost hits your pricing target perfectly.
On Thursday, July 24, 2003, at 10:44 AM, scott.marlowe wrote: > On Thu, 24 Jul 2003, tom dyson wrote: > >> We're about to buy a new server for dedicated Postgres serving. It'll >> be >> serving 4 - 10 databases (average dump size 20Gb) for various web >> applications. What is the list's advice on optimum configuration of >> hardware >> and software, given a smallish fixed budget (around $US 2000)? In >> particular, should we prioritise <snip> In the short time I've lurked (and de-lurked, and re-lurked) on this list, I've seen this question (and the same answers) probably a hundred times. Is there any document we can point this people to? If not, I'm volunteering to write some documentation (I've already started actually, I'm in the preface ATM, in docbook) that can be used as guide for: 1) determining how your data will use postgres (heavy read, heavy write, etc) 2) getting hardware (disks, cpu, memory) and low-level software (OS) 3) configuring Pg (simple definitions of the params, example configs for common setups, etc) Much of this is in the 'big' docs @pg.org, but I believe we could benefit from a more targeted document. If this is needed, is there some(where|one) that I should put my work? I'm not a guru at this, but my db runs well, and it would be a nice learning experience. I'll obviously post work periodically to the general list for review/clarification and accept changes. Cory 'G' Watson http://www.loggerithim.org
> > * Disk speed 4 drives... 7200 RPM... as big as you can get. Get 8 Meg cache drives so you have a 3 year warranty. > * Hardware RAID 3Ware IDE controller (or SATA) running RAID 10 > * Memory 2 gig > * Processor speed 1800+ > * Processor type Athlon MP > * Multiple processors > 2 We are able to run a 60 person insurance company entirely off of the box I described above with ZERO load problems. > And does the list have any observations regarding Postgres performance on > different platforms? Our webservers run Linux (RedHat 8), but we're willing > to experiment with alternatives. > > Many thanks > > Tom > > -----------------+ > tom dyson > t: +44 (0)1608 811870 > m: +44 (0)7958 752657 > http://torchbox.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match