Re: [PERFORM] Hardware performance
I've got a Dell 2650 set up with 5 drives and a separate app server connecting with JDBC. Since you've only got 5 drives, my conclusion regarding the best balance of performance and redundancy was: 2 drives have the OS, swap, and WAL in RAID-1 3 drives have the data in RAID-5 If you can afford it, get the 2+3 split backplane and make the 3 data drives the biggest, fastest you can afford. Currently that means the 15k 73GB drives, which would give you 146GB for data. Make the OS drives smaller and slower if you need to save cash. If only it had six drive baysyou could use 4 drives for the data and do RAID-10. If you've got the additional rackspace available, you could get the 5U Dell 2600 instead for the same ballpark cost. If you order it with rack rails, it comes all set up for rack installation...a special sideways faceplate and everything. By the way, RAID-5 is not the best fault tolerance, RAID-1 or RAID-10 is. And you can certainly hot-swap RAID-10 arrays. I've actually done itrecently! I am of the mind that single drives are not an option for production servers - I just don't need the pain of the server going down at all. Although they DO go down despite redundancy...I just had a SCSI backplane go out in a Dell 6600 that has every bit of redundancy you can order. While uncommon, the backplane is one one of the many single points of failure! Roman Fail POS Portal, Inc. -Original Message- From: Balazs Wellisch [mailto:[EMAIL PROTECTED] Sent: Wed 7/16/2003 7:57 PM To: [EMAIL PROTECTED] Cc: Subject: [PERFORM] Hardware performance Hi all, first of all I'd like to thank everyone who responded to my earlier post. I have a much better understanding of postgres performance tuning now. In case anyone's interested we've decided to go with RH9 and PostgreSQL 7.3 and we'll do the OS and DB tuning ourselves. (should be a good learning experience) We are now getting ready to purchase the hardware that will be used to run the database server. We're spending quite a bit of money on it because this will eventually, if things go well within two months, become a production server. We're getting all RH certified hardware from Dell. (Dell 2650) We're now stuck on the question of what type of RAID configuration to use for this server. RAID 5 offers the best fault tolerance but doesn't perform all that well. RAID 10 offers much better performance, but no hot swap. Or should we not use RAID at all. I know that ideally the log (WAL) files should reside on a separate disk from the rest of the DB. Should we use 4 separate drives instead? One for the OS, one for data, one for WAL, one for swap? Or RAID 10 for everything plus 1 drive for WAL? Or RAID 5 for everything? We have the budget for 5 drives. Does anyone have any real world experience with what hard drive configuration works best for postgres? This is going to be a dedicated DB server. There are going to be a large number of transactions being written to the database. (Information is logged from a separate app through ODBC to postgres) And there will be some moderately complex queries run concurrently to present this information in the form of various reports on the web. (The app server is a separate machine and will connect to the DB through JDBC to create the HTML reports) Any thoughts, ideas, comments would be appreciated. Thank you, Balazs Wellisch Neu Solutions [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Tuning PostgreSQL
> What would you guys think of not using RAID5 in that case, but just a really > fast 15.000 rpm SCSI-320 disk? I'd say you must be able to tolerate losing all the data since your last database backup. Your battery backed cache, rotational speed, and transfer rate aren't going to help at all when the drive itself degrades and corrupts data. If you can really only afford 3 drives, I'd have a single drive with the OS & WAL on it, and the data on a RAID-1 mirror set using the other 2 drives. If you need more space for data, or want your OS drives to be mirrored - it's going to cost more. See if you can get 2x18GB drives for the OS and 2x73GB drives for the data. You have to consider how much headache that small amount of additional money is going to save you (and your users) down the road. Roman -Original Message- From: Alexander Priem [mailto:[EMAIL PROTECTED] Sent: Mon 7/21/2003 5:43 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: Subject: Re: [PERFORM] Tuning PostgreSQL Thanks, i'll look further into these mount setting. I was just thinking, the server will have a (RAID) controller containing 128Mb of battery-backed cache memory. This would really speed up inserts to the disk and would prevent data loss in case of a power-down also. What would you guys think of not using RAID5 in that case, but just a really fast 15.000 rpm SCSI-320 disk? Kind regards, Alexander. - Original Message - From: "Shridhar Daithankar" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, July 21, 2003 2:05 PM Subject: Re: [PERFORM] Tuning PostgreSQL > On 21 Jul 2003 at 13:45, Alexander Priem wrote: > > > So where can I set the noatime & data=writeback variables? They are not > > PostgreSQL settings, but rather Linux settings, right? Where can I find > > these? > > These are typicaly set in /etc/fstab.conf. These are mount settings. man mount > for more details. > > The second setting data=writeback is ext3 specific, IIRC. > > HTH > > Bye > Shridhar > > -- > History tends to exaggerate. -- Col. Green, "The Savage Curtain", stardate > 5906.4 > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] hardware performance and some more
> Now, the second question is related to the performance of the database. Assuming we > have a > dell's poweredge 6650 with 4 x 2.8 Ghz Xeon processors having 2 MB of cache for > each, with the > main memory of lets say 32 GB. We can either use a small SAN from EMC or we can put > all disks > into the machines with the required raid confiuration. > > We will install RedHat Advanced Server 2.1 to the machine as the operating system > and postgresql as > the database server. We have a database having 25 millions records having the > length of 250 bytes > on average for each record. And there are 1000 operators accessing the database > concurrently. The main > operation on the database (about 95%) is select rather than insert, so do you have > any idea about > the performance of the system? I have a very similar installation: Dell PE6600 with dual 2.0 Xeons/2MB cache, 4 GB memory, 6-disk RAID-10 for data, 2-disk RAID-1 for RH Linux 8. My database has over 60 million records averaging 200 bytes per tuple. I have a large nightly data load, then very complex multi-table join queries all day with a few INSERT transactions. While I do not have 1000 concurrent users (more like 30 for me), my processors and disks seem to be idle the vast majority of the time - this machine is overkill. So I think you will have no problem with your hardware, and could probably easily get away with only two processors. Someday, if you can determine with certainty that the CPU is a bottleneck, drop in the 3rd and 4th processors (and $10,000). And save yourself money on the RAM as well - it's incredibly easy to put in more if you need it. If you really want to spend money, set up the fastest disk arrays you can imagine. I cannot emphasize enough: allocate a big chunk of time for tuning your database and learning from this list. I migrated from Microsoft SQL Server. Out of the box PostgreSQL was horrible for me, and even after significant tuning it crawled on certain queries (compared to MSSQL). The list helped me find a data type mismatch in a JOIN clause, and since then the performance of PostgreSQL has blown the doors off of MSSQL. Since I only gave myself a couple days to do tuning before the db had to go in production, I almost had to abandon PostgreSQL and revert to MS. My problems were solved in the nick of time, but I really wish I had made more time for tuning. Running strong in production for 7 months now with PostgreSQL 7.3, and eagerly awaiting 7.4! Roman Fail POS Portal, Inc. ---(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
