Thread: optimal hardware for postgres?
I'm just going to buy a new server which will mainly run a postgreSQL-database and a apache2 with mod_perl and do little more than deliver loads of index-based select-queries. But it will have to do a lot of them. And it will be master for a replication to slaves. As always of course we dont know how many selects we'll have to face. There will be "many" and the current Athlon1800+ with 1GB Ram is too slow. (to its excuse : It has to perform loads of other tasks as well) I was now wondering which hardware would fit this server best. I think about 2GB RAM, a fast Raid1 and now I'm not sure about the CPU. I was considering 64-bit AMD : A Athlon 64 FX-51 or a Opteron 2.2GHz. The hosting system will be a 64-Bit linux. Does postgres benefit from 64-bit AMD's or would I be better off with a cheaper AthlonXP or even with a Pentium or a more expensive Xeon? Or is my question faulty at all, cause CPU is only 20% of the whole system. Any comments appretiated, thnx, peter -- mag. peter pilsl goldfisch.at IT-management tel +43 699 1 3574035 fae +43 699 4 3574035 pilsl@goldfisch.at
peter pilsl wrote: > I was now wondering which hardware would fit this server best. I think > about 2GB RAM, a fast Raid1 and now I'm not sure about the CPU. > I was considering 64-bit AMD : A Athlon 64 FX-51 or a Opteron 2.2GHz. > The hosting system will be a 64-Bit linux. While the A64 and Opteron are two sides of the same coin, I'd avoid using A64s because you need ECC reg memory to keep your server stable. To get fast RAID, you need fast I/O connections. Up to just a month ago, that probably meant you had to get a hefty Dual Opteron MB in order to get PCI-X slots. The other option now is use PCIe. Ordinarily, most people use fast graphics cards in PCIe slots but you could pop in a PCIe RAID controller instead. This gives you the option of looking at "workstation-class" platforms versus a more expensive server platform. On other note -- if you are thinking about SMP Opteron, you may actually get better performance from 1x275 (Dual Core 2.2ghz) versus 2x248 (2.2ghz). Full duals have twice the bandwidth but without good NUMA support, memory has to be interleaved between CPUs. This means when reading chunks of continous memory, CPUs have to continually request data from the other CPU. Because the CPU<->CPU requests on DC Opterons are so much faster, AMD's own performance optimization guide says to put 2 threads onto a single DC chip leaving other CPUs in the system idle versus trying to fully balance between chips. > Does postgres benefit from 64-bit AMD's or would I be better off with a > cheaper AthlonXP or even with a Pentium or a more expensive Xeon? > Or is my question faulty at all, cause CPU is only 20% of the whole system. My experience going from RH9 32-bit to FCx 64-bit was huge increases in speed.
William Yu wrote: > On other note -- if you are thinking about SMP Opteron, you may > actually get better performance from 1x275 (Dual Core 2.2ghz) versus > 2x248 (2.2ghz). Full duals have twice the bandwidth but without good > NUMA support, memory has to be interleaved between CPUs. I thought the 2.6 kernel had good NUMA support? (I realize I just made an assumption that the original poster was running PG on Linux 2.6.) -- Guy Rouillier
The first thing you need to do is assess where your bottleneck is going to be. Is it RAM? or CPU or IO? If your system is IO bound, you are better off spending your $s on a good IO controller and lots of drives that racking up the $$s with Expensive Opteron 275s or 250s, when a 242 would do the job just fine for les than $200 ea. The Tyan S2885 mobo is a good chioce for a rig, it's not too expense, and has 2 independant PCI buses if you want to run two seperate controlers, which if you are going to run both database and webserver on one box I would heartily recommend doing. Run the webserver on a RAID 1 on the first controler with WAL on another RAID 1, then run your DB on the other controller with a 4 or 6 drive RAID 10 (This is what you might want to look at if you are IO bound). This kind of server can be built for $4-7k in either SCSI or SATA configs depending on your preference/budget/size requirements. SCSI is a bit more, but you get better performance, SATA raptor has good performance, and gives you more space for your dollar if you don't mind going SATA (plus if you use multi-lane with 3ware controller you get a reasonable number of cables instead of spaghetti junction). Don't bother with Xeon, the performance has been shown to be pathetic relative to Opteron for Postgresql and also not as cost effective. Check out the size of your data set, and your search requirement. The more data you can cache in RAM the better off you will be, and the quicker you will move the bottleneck from IO to RAM/CPU (It looks like you will be read intensive not write from your comments). If you need more RAM, put it in as it can save on the disk cost, and you can go with a generic 4U chasis instead of an expensive chasis with backplace built in. Antec has a nice 4U that has 8 drive bays that will fit 2x3U 4 drive modules for 8 drives total plus room for a CD ROM to spare. If you need more than this check out rackmountmart.com. They have a nice 4U and 5U chasis that has lots of space for drives. Thats my 0.02 Alex Turner netEconomist On 4/24/05, peter pilsl <pilsl@goldfisch.at> wrote: > > I'm just going to buy a new server which will mainly run a > postgreSQL-database and a apache2 with mod_perl and do little more than > deliver loads of index-based select-queries. But it will have to do a > lot of them. And it will be master for a replication to slaves. > As always of course we dont know how many selects we'll have to face. > There will be "many" and the current Athlon1800+ with 1GB Ram is too > slow. (to its excuse : It has to perform loads of other tasks as well) > > I was now wondering which hardware would fit this server best. I think > about 2GB RAM, a fast Raid1 and now I'm not sure about the CPU. > I was considering 64-bit AMD : A Athlon 64 FX-51 or a Opteron 2.2GHz. > The hosting system will be a 64-Bit linux. > > Does postgres benefit from 64-bit AMD's or would I be better off with a > cheaper AthlonXP or even with a Pentium or a more expensive Xeon? > Or is my question faulty at all, cause CPU is only 20% of the whole system. > > Any comments appretiated, > thnx, > peter > > -- > mag. peter pilsl > goldfisch.at > IT-management > tel +43 699 1 3574035 > fae +43 699 4 3574035 > pilsl@goldfisch.at > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Linux 2.6 does have NUMA support. But whether it's actually a for Postgres is debatable due to the architecture. First let's take a look at how NUMA makes this run faster in a 2x Opteron system. The idea is that the processes running on CPU0 can access memory attached to that CPU a lot faster than memory attached to CPU1. So in a NUMA-aware system, a process running on CPU0 can request all it's memory be located memory bank0. Now let's look at how Postgres manages memory. 1 big shared memory block and an even bigger OS cache pool. Assuming the postgres decides to follow the NUMA model -- the main process requests a big 150MB chunk of shared memory on CPU0. All good and dandy at this point. So let's start making connections to postgres. Now where do you put these processes? Do you continue to pile these processes onto CPU0 where memory latency is really low? Or do you use the idle CPU but accept the +40ns latency? Continue on and think about the cache. One of the design philosphies of Postgres is that the OS manages the memory. So you've got 7GB of memory left over -- 3GB is on CPU0 and 4GB on CPU1. When you happen to hit stuff cached on CPU0, you get extremely fast queries. When you hit stuff on CPU1, your queries are much slower (although still way faster than disk). If your dataset is small and your multi-user load is low (e.g. you have 8GB of RAM but you only need to use 4GB to do all your stuff), Linux can probably put all your processes onto the "fast" CPU so NUMA could be a win. But otherwse, what you'd probably end up with is uneven performance -- sometimes very fast and sometimes very slow. Would you prefer this kind of behavior over interleaved memory access where everything performs in the middle? Dunno. Would definitely depend on the server usage pattern. Guy Rouillier wrote: > William Yu wrote: > > >>On other note -- if you are thinking about SMP Opteron, you may >>actually get better performance from 1x275 (Dual Core 2.2ghz) versus >>2x248 (2.2ghz). Full duals have twice the bandwidth but without good >>NUMA support, memory has to be interleaved between CPUs. > > > I thought the 2.6 kernel had good NUMA support? (I realize I just made > an assumption that the original poster was running PG on Linux 2.6.) >
On Tue, 2005-04-26 at 01:32 -0700, William Yu wrote: > Linux 2.6 does have NUMA support. But whether it's actually a for > Postgres is debatable due to the architecture. > > First let's take a look at how NUMA makes this run faster in a 2x > Opteron system. The idea is that the processes running on CPU0 can > access memory attached to that CPU a lot faster than memory attached to > CPU1. So in a NUMA-aware system, a process running on CPU0 can request > all it's memory be located memory bank0. [...] This is only part of the truth. You should compare it with real SMP solutions. The idea is that CPU0 can access directly attached memory faster than it would on a SMP system, given equivalent or so technology, of course. So NUMA has a fast path and a slow path, while SMP has only one, uniform, medium path. The whole point is where the SMP path lies. If it's close to the fast (local) path in NUMA, then NUMA won't pay off (performance wise) unless the application is NUMA-aware _and_ NUMA-friendly (which depends on how the application is writter, assuming the underlying problem _can_ be solved in a NUMA-friendly way). If the SMP path is close to the slow (remote) path in NUMA (for example, they have to keep the caches coherent, and obey to memory barriers and locks) then NUMA has little to loose for NUMA-unaware or NUMA-unfriendly applications (worst case), and a lot to gain when some NUMA-aware optimizations kick in. I've read some articles that refer to the name SUMO (sufficiently uniform memory organization) AMD would use to describe their NUMA, which seems to imply that their worst case is "sufficiently" close to the usual SMP timing. There are other interesting issues in SMP scaling, on the software side. Scaling with N > 8 might force partitioning at software level anyway, in order to reduce the number of locks, both as software objects (reduce software complexity) and as hardware events (reduce time spent in useless synchronizations). See: http://www.bitmover.com/llnl/smp.pdf This also affects ccNUMA, of course, I'm not saying NUMA avoids this in any way. But it's a price _both_ have to pay, moving their numbers towards the worst case anyway (which makes the worst case not so worse). .TM. -- ____/ ____/ / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _____/ _____/ _/ Colombo@ESI.it