Re: [PERFORM] hardware and For PostgreSQL
Magnus Hagander wrote: Ron St-Pierre wrote: Joe Uhl wrote: I realize there are people who discourage looking at Dell, but i've been very happy with a larger ball of equipment we ordered recently from them. Our database servers consist of a PowerEdge 2950 connected to a PowerVault MD1000 with a 1 meter SAS cable. We have a similar piece of equipment from Dell (the PowerEdge), and when we had a problem with it we received excellent service from them. When our raid controller went down (machine 1 year old), Dell helped to diagnose the problem and installed a new one at our hosting facility, all within 24 hours. 24 hours?! I have a new one for my HP boxes onsite in 4 hours, including a tech if needed... But I assume Dell also has service-agreement deals you can get to get the level of service you'd want. (But you won't get it for a non-brand-name server, most likely) Bottom line - don't underestimate the service you get from the vendor when something breaks. Because eventually, something *will* break. //Magnus Yeah the response time depends on the service level purchased. I generally go with 24 hour because everything is redundant so a day of downtime isn't going to bring services down (though it could make them slow depending on what fails) but you can purchase 4 hr and in some cases even 2 hr. I had a gold level support contract on a server that failed awhile back and within 3 net hours they diagnosed and fixed the problem by getting onsite and replacing the motherboard and a cpu. I haven't had any of our 24hr support level devices fail yet so don't have anything to compare there. If you do go with Dell and want the higher support contracts i'll restate that a small business account is the way to go. Typically the prices are better to the point that a support level upgrade appears free when compared to the best shopping cart combo I can come up with. Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] hardware and For PostgreSQL
I am trying to build a very Robust DB server that will support 1000+ concurrent users (all ready have seen max of 237 no pooling being used). i have read so many articles now that I am just saturated. I have a general idea but would like feedback from others. I understand query tuning and table design play a large role in performance, but taking that factor away and focusing on just hardware, what is the best hardware to get for Pg to work at the highest level (meaning speed at returning results)? How does pg utilize multiple processors? The more the better? Are queries spread across multiple processors? Is Pg 64 bit? If so what processors are recommended? I read this : http://www.postgresql.org/files/documentation/books/ aw_pgsql/hw_performance/node12.html POSTGRESQL uses a multi-process model, meaning each database connection has its own Unix process. Because of this, all multi-cpu operating systems can spread multiple database connections among the available CPUs. However, if only a single database connection is active, it can only use one CPU. POSTGRESQL does not use multi- threading to allow a single process to use multiple CPUs. Its pretty old (2003) but is it still accurate? if this statement is accurate how would it affect connection pooling software like pg_pool? RAM? The more the merrier right? Understanding shmmax and the pg config file parameters for shared mem has to be adjusted to use it. Disks? standard Raid rules right? 1 for safety 5 for best mix of performance and safety? Any preference of SCSI over SATA? What about using a High speed (fibre channel) mass storage device? Who has built the biggest baddest Pg server out there and what do you use? Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] hardware and For PostgreSQL
On 10/31/07, Ketema Harris [EMAIL PROTECTED] wrote: I am trying to build a very Robust DB server that will support 1000+ concurrent users (all ready have seen max of 237 no pooling being used). i have read so many articles now that I am just saturated. I have a general idea but would like feedback from others. Slow down, take a deep breath. It's going to be ok. You should definitely be looking at query pooling. pgbouncer from skype has gotten some good coverage lately. I've used pgpool and pgpool II with good luck myself. How does pg utilize multiple processors? The more the better? Are queries spread across multiple processors? Is Pg 64 bit? If so what processors are recommended? Generally more is better, up to a point. PG runs one query per processor max. i.e. it doesn't spread a single query out over multiple CPUs. Yes, it's 64 bit, if you use a 64 bit version on a 64 bit OS. Right now both Intel and AMD CPUs seem pretty good. RAM? The more the merrier right? That depends. If 16 Gigs runs 33% faster than having 32 Gigs, the 16 Gigs will probably be better, especially if your data set fits in 16Gig. But all things being equal, more memory = good. Understanding shmmax and the pg config file parameters for shared mem has to be adjusted to use it. Don't forget all the other paramenters like work_mem and fsm settings. and regular vacuuming / autovacuuming Disks? standard Raid rules right? 1 for safety 5 for best mix of performance and safety? Neither of those is optimal for a transactional database. 5 isn't particularly safe since two disks can kill your whole array. RAID-10 is generally preferred, and RAID 50 or 6 can be a good choice. Any preference of SCSI over SATA? What about using a High speed (fibre channel) mass storage device? What's most important is the quality of your controller. A very high quality SATA controller will beat a mediocre SCSI controller. Write back cache with battery backed cache is a must. Escalade, Areca, LSI and now apparently even Adaptec all have good controllers. Hint: If it costs $85 or so, it's likely not a great choice for RAID. I've seen many $200 RAID controllers that were much better when you turned off the RAID software and used kernel SW mode RAID instead (witness Adaptec 14xx series) Mass storage can be useful, especially if you need a lot of storage or expansion ability. Who has built the biggest baddest Pg server out there and what do you use? Not me, but we had a post from somebody with a very very very large pgsql database on this list a few months ago... Search the archives. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] hardware and For PostgreSQL
It would probably help you to spend some time browsing the archives of this list for questions similar to yours - you'll find quite a lot of consistent answers. In general, you'll find that: - If you can fit your entire database into memory, you'll get the best performance. - If you cannot (and most databases cannot) then you'll want to get the fastest disk system you can. - For reads, RAID5 isn't so bad but for writes it's near the bottom of the options. RAID10 is not as efficient in terms of hardware, but if you want performance for both reads and writes, you want RAID10. - Your RAID card also matters. Areca cards are expensive, and a lot of people consider them to be worth it. - More procs tend to be better than faster procs, because more procs let you do more at once and databases tend to be i/o bound more than cpu bound. - More or faster procs put more contention on the data, so getting more or better cpus just increases the need for faster disks or more ram. - PG is 64 bit if you compile it to be so, or if you install a 64-bit binary package. and all that said, application and schema design can play a far more important role in performance than hardware. On Wed, 31 Oct 2007, Ketema Harris wrote: I am trying to build a very Robust DB server that will support 1000+ concurrent users (all ready have seen max of 237 no pooling being used). i have read so many articles now that I am just saturated. I have a general idea but would like feedback from others. I understand query tuning and table design play a large role in performance, but taking that factor away and focusing on just hardware, what is the best hardware to get for Pg to work at the highest level (meaning speed at returning results)? How does pg utilize multiple processors? The more the better? Are queries spread across multiple processors? Is Pg 64 bit? If so what processors are recommended? I read this : http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/node12.html POSTGRESQL uses a multi-process model, meaning each database connection has its own Unix process. Because of this, all multi-cpu operating systems can spread multiple database connections among the available CPUs. However, if only a single database connection is active, it can only use one CPU. POSTGRESQL does not use multi-threading to allow a single process to use multiple CPUs. Its pretty old (2003) but is it still accurate? if this statement is accurate how would it affect connection pooling software like pg_pool? RAM? The more the merrier right? Understanding shmmax and the pg config file parameters for shared mem has to be adjusted to use it. Disks? standard Raid rules right? 1 for safety 5 for best mix of performance and safety? Any preference of SCSI over SATA? What about using a High speed (fibre channel) mass storage device? Who has built the biggest baddest Pg server out there and what do you use? Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] hardware and For PostgreSQL
I realize there are people who discourage looking at Dell, but i've been very happy with a larger ball of equipment we ordered recently from them. Our database servers consist of a PowerEdge 2950 connected to a PowerVault MD1000 with a 1 meter SAS cable. The 2950 tops out at dual quad core cpus, 32 gb ram, and 6 x 3.5 drives. It has a Perc 5/i as the controller of the in-box disks but then also has room for 2 Perc 5/e controllers that can allow connecting up to 2 chains of disk arrays to the thing. In our environment we started the boxes off at 8gb ram with 6 15k SAS disks in the server and then connected an MD1000 with 15 SATA disks to one of the Perc 5/e controllers. Gives tons of flexibility for growth and for tablespace usage depending on budget and what you can spend on your disks. We have everything on the SATA disks right now but plan to start moving the most brutalized indexes to the SAS disks very soon. If you do use Dell, get connected with a small business account manager for better prices and more attention. Joe Ketema Harris wrote: I am trying to build a very Robust DB server that will support 1000+ concurrent users (all ready have seen max of 237 no pooling being used). i have read so many articles now that I am just saturated. I have a general idea but would like feedback from others. I understand query tuning and table design play a large role in performance, but taking that factor away and focusing on just hardware, what is the best hardware to get for Pg to work at the highest level (meaning speed at returning results)? How does pg utilize multiple processors? The more the better? Are queries spread across multiple processors? Is Pg 64 bit? If so what processors are recommended? I read this : http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/node12.html POSTGRESQL uses a multi-process model, meaning each database connection has its own Unix process. Because of this, all multi-cpu operating systems can spread multiple database connections among the available CPUs. However, if only a single database connection is active, it can only use one CPU. POSTGRESQL does not use multi-threading to allow a single process to use multiple CPUs. Its pretty old (2003) but is it still accurate? if this statement is accurate how would it affect connection pooling software like pg_pool? RAM? The more the merrier right? Understanding shmmax and the pg config file parameters for shared mem has to be adjusted to use it. Disks? standard Raid rules right? 1 for safety 5 for best mix of performance and safety? Any preference of SCSI over SATA? What about using a High speed (fibre channel) mass storage device? Who has built the biggest baddest Pg server out there and what do you use? Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] hardware and For PostgreSQL
Joe Uhl wrote: I realize there are people who discourage looking at Dell, but i've been very happy with a larger ball of equipment we ordered recently from them. Our database servers consist of a PowerEdge 2950 connected to a PowerVault MD1000 with a 1 meter SAS cable. We have a similar piece of equipment from Dell (the PowerEdge), and when we had a problem with it we received excellent service from them. When our raid controller went down (machine 1 year old), Dell helped to diagnose the problem and installed a new one at our hosting facility, all within 24 hours. fyi Ron ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] hardware and For PostgreSQL
On Wed, 31 Oct 2007 14:54:51 -0400 Joe Uhl [EMAIL PROTECTED] wrote: I realize there are people who discourage looking at Dell, but i've been very happy with a larger ball of equipment we ordered recently from them. Our database servers consist of a PowerEdge 2950 connected to a PowerVault MD1000 with a 1 meter SAS cable. The 2950 tops out at dual quad core cpus, 32 gb ram, and 6 x 3.5 drives. It has a Perc 5/i as the controller of the in-box disks but then also has room for 2 Perc 5/e controllers that can allow connecting up to 2 chains of disk arrays to the thing. The new Dell's based on Woodcrest (which is what you are talking about) are a much better product that what Dell used to ship. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
Re: [PERFORM] hardware and For PostgreSQL
Ron St-Pierre wrote: Joe Uhl wrote: I realize there are people who discourage looking at Dell, but i've been very happy with a larger ball of equipment we ordered recently from them. Our database servers consist of a PowerEdge 2950 connected to a PowerVault MD1000 with a 1 meter SAS cable. We have a similar piece of equipment from Dell (the PowerEdge), and when we had a problem with it we received excellent service from them. When our raid controller went down (machine 1 year old), Dell helped to diagnose the problem and installed a new one at our hosting facility, all within 24 hours. fyi Ron This is good to know - I've got a new Dell PowerEdge 2900 quad-core, 4GB RAM, 6*146Gb SAN disks on RAID-5 controller arriving in the next week or two as a new database server. Well, more like a database development machine - but it will host some non-production databases as well. Good to know Dell can be relied on these days, I was a bit concerned about that when purchasing sent me a copy invoice from Dell - particularly since I was originally told an IBM was on the way. Good ol' purchasing, can always rely on them to change their minds last minute when they find a cheaper system. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] hardware and For PostgreSQL
Ron St-Pierre wrote: Joe Uhl wrote: I realize there are people who discourage looking at Dell, but i've been very happy with a larger ball of equipment we ordered recently from them. Our database servers consist of a PowerEdge 2950 connected to a PowerVault MD1000 with a 1 meter SAS cable. We have a similar piece of equipment from Dell (the PowerEdge), and when we had a problem with it we received excellent service from them. When our raid controller went down (machine 1 year old), Dell helped to diagnose the problem and installed a new one at our hosting facility, all within 24 hours. 24 hours?! I have a new one for my HP boxes onsite in 4 hours, including a tech if needed... But I assume Dell also has service-agreement deals you can get to get the level of service you'd want. (But you won't get it for a non-brand-name server, most likely) Bottom line - don't underestimate the service you get from the vendor when something breaks. Because eventually, something *will* break. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
