Thread: Low Budget Performance
I'm looking for some advice or benchmarks comparing low end systems for a postgres installation. Currently, I've got postgres running on the same system as the app server accessing a single fast IDE drive. The databaseis on the order of 1 gig, with two main tables accounting for 98% of the data. Between the app servers and the database,I'm pretty sure that neither of the main tables are cached in memory for any significant time. I'm guessing thatthis is sub optimal. (The data size is 1 gig now, but I will be adding more 1 gig databases to this system in the nearfuture) I'm planning to split this into an app server and database server. In an ideal world, I'd throw a lot of 15k scsi/raid0+1 at this. But I don't have an ideal world budget. I've got more ofan ide world budget, if that. (~1k) I know the first order of business is to ignore the hardware and make sure that I've got all of the table scans found andturned into indexes. I'm still working on that. Are there any tools that save queries and the plans, then report on theones that are the biggest performance drags? But since I do software, it's obviously a hardware problem. ;> My hardware options: Processor: * My low cost option is to repurpose an under used p3 with onboard IDE raid and pc133 memory. The high cost option is toget a new mid range Athalon with 266/ddr memory. Will maxing out the memory mean that whatever I don't use for client connectionswill be used for caching the drive system? (most likely, I will be running debian woody with a 2.4 series kernel) Drives: * The cost difference between IDE and SCSI is roughly a factor of 2-4x. (100 gig 7200 rpm IDE can be had for a little over$100, 10k 36 gig SCSI is about $200. Am I better off throwing twice as many (4) IDE disks at the system? Does it changeif I can put each IDE drive on its own channel? Drive Layout: * What Drive layout? Raid? 0 gives better latency if the controller reads from whichever gets the data first. It's unclear if IDE or software raid actuallydoes this though. 1 Gives better throughput, at a cost to latency. 5 Like 1 but with redundancy. It's unclear if I'll be able to do this without hardware SCSI raid. Non Raid? I've read about seperating table spaces on different drives, so that indexes and data can be written at the same time. Thisadvice appears to be tailored to the complexity of oracle. The ideal configuration according to this info appears tobe multiple drives, all mirrored individually. Does the write ahead logging of PG mean that no matter what indexes and data are changed, that there will be one sync todisk? Does this reduce the penalty of indexes? WAL seems to mean that to get performance out of a drive array, I'd wantto use the fastest (latency/throughput) logical single image I could get, not a collection of mirrored drives. I'd appreciate any insight. eric
On 28 Oct 2002 at 13:56, eric soroos wrote: > Currently, I've got postgres running on the same system as the app server accessing a single fast IDE drive. The databaseis on the order of 1 gig, with two main tables accounting for 98% of the data. Between the app servers and the database,I'm pretty sure that neither of the main tables are cached in memory for any significant time. I'm guessing that this is sub optimal. (The data size is 1 gig now, but I willbe adding more 1 gig databases to this system in the near future) I'm planning to split this into an app server anddatabase server. > > In an ideal world, I'd throw a lot of 15k scsi/raid0+1 at this. But I don't have an ideal world budget. I've got moreof an ide world budget, if that. (~1k) > > I know the first order of business is to ignore the hardware and make sure that I've got all of the table scans found andturned into indexes. I'm still working on that. Are there any tools that save queries and the plans, then report on theones that are the biggest performance drags? > > But since I do software, it's obviously a hardware problem. ;> > > My hardware options: I would say throw a lot of RAM no matter what type. Even PC133 is going to be faster than any disk you can buy anytimes. I would say 2Gig is a nice place to start. A gig is not much of a database but a lot depends upon what do you do with the data. Obviously 50 clients doing sequential scan with rows ordered in random fashion would chew any box,..;-) Processor does not matter much. But I would advice to split app server and database server ASAP. Well, IDE RAID looks like nice optio to me, but before finalising RAID config., I would advice to test performance and scalability with separate database server and couple of Gigs of RAM. Because if this configuration is sufficient for your need, probably you can choose a conservatice RAID config that would enhance availability rather than getting every ounce of performance out of it. As far as possible, don't compramise with storage availability. > Does the write ahead logging of PG mean that no matter what indexes and data are changed, that there will be one sync todisk? Does this reduce the penalty of indexes? WAL seems to mean that to get performance out of a drive array, I'd wantto use the fastest (latency/throughput) logical single image I could get, not a collection of mirrored drives. I guess RAID will take care of lot of these issues. Besides if you use volume manager you can add partitions from different disks, effectively splitting the IO. Of course, you can shutdown the database and symlink things to another drive, but that's hack and nothing else. Don't do it as far as possible.. HTH Bye Shridhar -- You're dead, Jim. -- McCoy, "Amok Time", stardate 3372.7
Am Montag, 28. Oktober 2002 22:56 schrieb eric soroos:
> Raid?
> 0 gives better latency if the controller reads from whichever gets the data
> first. It's unclear if IDE or software raid actually does this though. 1
> Gives better throughput, at a cost to latency.
> 5 Like 1 but with redundancy. It's unclear if I'll be able to do this
> without hardware SCSI raid.
Just for the raid part, we've very good expiriences with Raid 10. Performs well and has mirroring. Avoid Raid 5 if
possible,write performance will suffer greatly.
Regards,
Mario Weilguni
Eric, > > Currently, I've got postgres running on the same system as the app > server accessing a single fast IDE drive. The database is on the > order of 1 gig, with two main tables accounting for 98% of the data. > Between the app servers and the database, I'm pretty sure that > neither of the main tables are > cached in memory for any significant time. I'm guessing that this is > sub optimal. (The data size is 1 gig now, but I will be adding more 1 > gig databases to this system in the near future) I'm planning to > split this into an app server and database server. One gig is a large database for a single IDE drive -- especially with multiple client connections. > > In an ideal world, I'd throw a lot of 15k scsi/raid0+1 at this. > But I don't have an ideal world budget. I've got more of an ide > world budget, if that. (~1k) Well, no matter how many performance tricks you add in, the speed will be limited by the hardware. Make sure that your client/employer knows that *before* they complain about the speed. > > I know the first order of business is to ignore the hardware and > make sure that I've got all of the table scans found and turned into > indexes. I'm still working on that. Are there any tools that save > queries and the plans, then report on the ones that are the biggest > performance drags? Not exactly. If you enable Postgres 7.2 STATISTICS, you can get a lot of information about which indexes are being used, which are not, and which tables are having a lot of table scans. A tool like you describe would be really, really useful -- in fact, if anyone wrote one, I'm sure you could sell it for $$$$. > > But since I do software, it's obviously a hardware problem. ;> <grin> Actually, your best option for the hardware is to test what portion of the hardware is bottlenecking your performance, and address that. But first: > Well, IDE RAID looks like nice optio to me, but before finalising > RAID config., > I would advice to test performance and scalability with separate > database > server and couple of Gigs of RAM. I'm not convinced that current IDE RAID actually improves database disk throughput -- there's a lot of overhead in the one controller I tried (Promise). Does anyone have some statistics they can throw at me? A cheaper and easier method, involving 3-4 disks: Channel 1, Disk 1: Operating System, Swap, and PostgreSQL log Channel 1, Disk 2: WAL Files Channel 2, Disk 1: Database Channel 2, Disk 2 (optional): 2nd database data *however*, if you have multiple databases being simulteaneously accessesed, you will want to experiment with shuffling around the databases and WAL files to put them on different disks. The principle is to divide the disk tasks that are simultaenous ammonng as many disks as possible; thus the WAL files always do better on a different disk and channel than the database. > > Does the write ahead logging of PG mean that no matter what indexes > and data are changed, that there will be one sync to disk? Does this > reduce the penalty of indexes? In a word: No. Depending on the size of the update, there may be multiple synchs. And indexes do carry a significant penalty on large updates; just try runninng 10,000 updates to an indexed column as one transaction, and the penalty will be obvious. In fact, for my data load procedures, I tend to drop and re-create indexes. > WAL seems to mean that to get > performance out of a drive array, I'd want to use the fastest > (latency/throughput) logical > single image I could get, not a collection of mirrored drives. Mirrored drives are different than RAID. However, you are correct that the redundancy/fail-over factor in some RAID and Mirroring comes at a performance penalty. But you need to determine where you are actually losing time. Assuming that your tables are correctly indexed, your files are distributed, and your database is VACUUM FULL ANALYZed, and your postgresql.conf configured for optimum use of your exisiting memory, then here's what you do (assuming that you use Linux) 1. From a workstation, open 2 terminal windows on the server. In #1, run "vmstat 3", in the other "top" 2. Have your users pound on the application, trying all of the most complicated (and slow) operations in the app. More users is better, for this test. 3. Watch Vmstat and Top. What you're looking for is: a) Is the processor at 75% or above? If so, you either need a faster processor or more efficient queries b) Is the system using 80-100% of the RAM which you allocated it? If so, add more RAM and increase the Postgresql.conf memory variables. c) Is the system using Swap memory? if so, either add more RAM, or *decrease* the postgresql.conf memory variables. d) Are RAM and Processor at less than 50%, but the Disk I/O reaches a maximum number and stays there for minutes? Then your disk channel is flooded, and you cannot improve performance except by either improving your queries so the pull less rows, or adding more/faster disk capacity. The above process, while drawn-out, will help you avoid spending a lot of money on, for example, RAM that won't make a difference. -Josh Berkus
Mario, > Just for the raid part, we've very good expiriences with Raid 10. > Performs well and has mirroring. Avoid Raid 5 if possible, write > performance will suffer greatly. Out of curiousity, what is it with RAID 5? I've encountered the poor write performance too ... any idea why? -Josh
On Tue, Oct 29, 2002 at 09:10:31AM -0800, Josh Berkus wrote:
>
> Out of curiousity, what is it with RAID 5? I've encountered the poor
> write performance too ... any idea why?
It largely depends on the controller and te implementation. It has
to do with the cost of calculating the checksum. If the
implementation of that is inefficient, the writes become inefficient.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Josh, Thanks for the reply. > One gig is a large database for a single IDE drive -- especially with > multiple client connections. That's good to know. Is a scsi system that much better? Looking at prices, scsi is 1/2 the capacity and double the price for the 80 gig 7200rpmide vs 36 gig 10k rpm scsi. Assuming that I'll never run out of space before running out of performance, I can dedicate2x the number of ide drives to the problem. > > Well, IDE RAID looks like nice optio to me, but before finalising > > RAID config., > > I would advice to test performance and scalability with separate > > database > > server and couple of Gigs of RAM. > > I'm not convinced that current IDE RAID actually improves database disk > throughput -- there's a lot of overhead in the one controller I tried > (Promise). Does anyone have some statistics they can throw at me? All of the benchmarks that I've seen show that IDE raid is good for large operations, but for random seek and small datatransfers, you don't get anywhere near the expected scaling. > A cheaper and easier method, involving 3-4 disks: > > Channel 1, Disk 1: Operating System, Swap, and PostgreSQL log > Channel 1, Disk 2: WAL Files > Channel 2, Disk 1: Database > Channel 2, Disk 2 (optional): 2nd database data With IDE, I think I can manage to put each drive on a seperate channel. I've either got one extra controller onboard, orI can add a 4 channel pci card. From what I've read, this is one of the more important factors in IDE performance. > *however*, if you have multiple databases being simulteaneously > accessesed, you will want to experiment with shuffling around the > databases and WAL files to put them on different disks. The principle > is to divide the disk tasks that are simultaenous ammonng as many disks > as possible; thus the WAL files always do better on a different disk > and channel than the database. That's what I've read about database disk system design. Reduce spindle contention by using lots of drives. (especiallyin Philip Greenspun's book, but he's talking about 2x7 drives as a minimal configuration and 2x21 as ideal forlarger systems. And when licensing is more expensive than that sort of drive system, it's all roundoff error.) So, assuming that I have three databases with roughly equal load on them, does it make sense to partition them like: disk 0: os/swap/log/backup staging disk 1: WAL 1, DB 2 disk 2: WAL 2, DB 3 disk 3: WAL 3, DB 1 Or, in a slightly bigger drive system split 2 ways then mirrored. disk 0: os etc Disk 1,2: WAL 1, DB 2 Disk 3,4: WAL 2, DB 1 From an admin point of view, would this be done with alternate locations, symlinks, or multiple concurrent pg processes? > > > Does the write ahead logging of PG mean that no matter what indexes > > and data are changed, that there will be one sync to disk? Does this > > reduce the penalty of indexes? > > In a word: No. Depending on the size of the update, there may be > multiple synchs. And indexes do carry a significant penalty on large > updates; just try runninng 10,000 updates to an indexed column as one > transaction, and the penalty will be obvious. In fact, for my data > load procedures, I tend to drop and re-create indexes. Most of my update procedures are single row updates, with the exception being things that are already background tasks thatthe user doesn't notice the difference between 10 and 20 sec. So maybe I'm lucky there. > Mirrored drives are different than RAID. However, you are correct > that the redundancy/fail-over factor in some RAID and Mirroring comes > at a performance penalty. From howtos I've seen, there _can_ be a speed boost with mirroring on read using the linux kernel raid 1. Write performancesuffers though. > But you need to determine where you are actually losing time. That looks like it will get me started. eric
On Tue, Oct 29, 2002 at 10:43:33AM -0800, eric soroos wrote:
> Is a scsi system that much better? Looking at prices, scsi is 1/2
> the capacity and double the price for the 80 gig 7200rpm ide vs 36
> gig 10k rpm scsi. Assuming that I'll never run out of space before
> running out of performance, I can dedicate 2x the number of ide
> drives to the problem.
SCSI is dramatically better at using the interface. It is much
smarter about, for instance, handling multiple disks at the same
time; and it requires less attention from the CPU.
That said, if you have enough high speed IDE controllers and disks,
you'll probably beat an older SCSI system. And you can't beat the
price/performance of IDE RAID. We use it for some applications.
Note also that you get better RAID controllers from SCSI vendors,
just because it's the official high speed offering. The Promise IDE
RAID is nice, but it sure isn't as fast as the latest SCSI RAID
controllers. (We have also found that there's some overhead in the
IDE RAID. It was better under FreeBSD than I'm now experiencing
under Linux. But that might just be my prejudices showing!)
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Tue, 2002-10-29 at 11:31, Andrew Sullivan wrote: > On Tue, Oct 29, 2002 at 09:10:31AM -0800, Josh Berkus wrote: > > > > Out of curiousity, what is it with RAID 5? I've encountered the poor > > write performance too ... any idea why? > > It largely depends on the controller and te implementation. It has > to do with the cost of calculating the checksum. If the > implementation of that is inefficient, the writes become inefficient. A high-quality smart controller with lots of cache RAM definitely negates the RAID5 performance issues. (Of course, I'm referring to enterprise-level rack-mounted h/w that costs big bucks...) -- +------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "they love our milk and honey, but preach about another | | way of living" | | Merle Haggard, "The Fighting Side Of Me" | +------------------------------------------------------------+
> -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] > On Behalf Of Ron Johnson > Sent: Tuesday, October 29, 2002 3:10 PM > To: pgsql-performance@postgresql.org > Subject: Re: [pgsql-performance] Low Budget Performance > > > On Tue, 2002-10-29 at 11:31, Andrew Sullivan wrote: > > On Tue, Oct 29, 2002 at 09:10:31AM -0800, Josh Berkus > > wrote: > > > > > > Out of curiousity, what is it with RAID 5? I've > > > encountered the poor write performance too ... any > > > idea why? > > > > It largely depends on the controller and the > > implementation. It has to do with the cost of > > calculating the checksum. If the implementation of > > that is inefficient, the writes become inefficient. > > A high-quality smart controller with lots of cache RAM > definitely negates the RAID5 performance issues. > > (Of course, I'm referring to enterprise-level rack-mounted > h/w that costs big bucks...) Only if you buy it new. EBay has some great deals these days. My company just purchased a very nice Quad Xeon w/ 2GB RAM and last year's high-end PERC RAID controller for under $5K. The external drive array was a bit more expensive but that is an optional purchase. The 3x9GB SCSI drives that came with the machine should be more than sufficient to run a greater than small database server. If you don't want to spend $5K there are Dual Xeon machines with less RAM and not quite so nice RAID controllers that you can get in the $2.5K to $3.5K range. rjsjr