Nintendork asks:
"I recently started a new job as a resident computer geek and am analyzing the performance of our SQL server. I did quite a bit of research and would like an opinion from the Slashdot community on my proposed solution for increasing the STR (Sustained Transfer Rate) from the server to the workstations. The server (Compaq ProLiant ML530) has 16 10,000 RPM drives with an average STR of ~43MB/sec. per drive. 14 are used for two RAID 5 logical drives (7 physical drives per logical). The remaining 2 drives are backup drives in case one fails. Currently, they're all connected to a Compaq fibre RA4000 adapter. It runs at 100MB/sec. from what I could find in a jungle of fibre information. Reasoning tells me I have a huge bottleneck at the fibre adapter and the 100baseT NIC. I should also mention that the server has 2 PCI buses. One runs at 64-bit and 66Mhz and has 2 PCI slots. My proposed setup would be to back up all the data and create a new array with a few hardware modifications. Take out the fibre adapter and use two, dual channel 64-bit 66Mhz ultra160 adapters on the two 64-bit 66Mhz PCI slots (4 drives per channel). Take out the 100baseT NIC and start a gigabit backbone." Would this significantly increase performance? Read on, if you to check out the numbers on the new setup.
"From what I've learned thus far, the proposed setup would be a blazingly fast file server approaching ludicrous speed. Let me break it down. Data can be read from the drives at a STR of ~602MB/sec. (~43MB/sec. * 14 drives). Each Ultra160 channel has a STR of 132MB/sec. This provides a bearable bottleneck that reduces the overall STR to ~528MB/sec. (132MB/sec. * 4 channels). The 64-bit 66Mhz PCI bus has a STR of 528MB/sec., which is an exact match for the 4 ultra160 channels! From there, I assume the data goes out the NIC, which is on a gigabit backbone. This would provide a STR of ~528MB/sec. to the workstations. Unless I'm missing something such as a possible bottleneck between the PCI bus and the NIC, my reasoning makes gosh darned perfect sense!
Thanks in advance for any insight you all can provide on this issue."
Slashdot Mirror
Your first priority is to ditch Raid5.
By the looks of your post, you have money to spend, so invest in more disks and go RAID 0+1. You'll notice a speed increace right there. If youre worried about PCI latency, get a system with 2 or 3 PCI busses.
Infact, Whoever set up this configuration needs a slap, if they were going for performance. Raid 5 has more and more overhead penalty the more disks you add to a stripe set. Even knocking back the sets to 4 or 3 disks would help, I would never use more than 4 disks in a raid5 set.
That said, if your app is mostly db reads, 0+1 on good controllers should give you 4* the average disk throughput, thanks to striping, and round-robin mirror reads. Also, make sure your OS and filesystem are well tuned. Often you see people spending money on hardware, because they havent bothered to optomise the software. (Kernel variable, filesystem tuning, etc.)
Anyone who considers arithmetical methods of producing random numbers is, of course, in a state of sin.-John von Neumann
RMS-free software is like a Kenneth-Starr-free blowjob.
I don't get it.
Wouldn't hurt to max the machine out in SDRAM and add gig ethernet too.
One thing to keep in mind though is how much is this increase needed. I don't see why you wouldn't get the 500% performance increase. But if your users are only using 25 Mbps of the current setup, and there's no reason that you know of for this usage to drastically increase soon, then you might not be able to justify this change just yet.
It may be too early in the morning, and I'm just not thinking straight yet, but you calculate you'll have an STR of 538MB/s through the PCI bus which you're trying to shunt the data onto a 1000Mb/s network (notice the small b). 1000Mb/s is something less than 125MB/s, and that's complete saturation which Ethernet doesn't handle well. I'd be surprised if you get better than 100MB/s over that network. Sounds nice to me, but then I do fine with an 11Mb wireless network myself :-)
You also called the box running this stuff a SQL server. Relational databases are very, very slow. I don't know what's under the hood of a ProLiant ML530 (and I'm too lazy to look it up) but I doubt it has the nads to process 528MB/s of SQL queries.
Note to ACs: I usually delete AC replies without reading them. If you want to talk to me, log in.
I'd echo the move-to-RAID-0+1 statement wholeheartedly. You could change very little and still increase sustained performance. In my own testing (moving 1GB of small files and a 1GB file separately), I'm reducing the move time by about 30-percent. That's on a DG Clariion, not a Compaq array, so ymmv.
I don't claim to be an expert on fibre, but that speed seems slow. I don't know if the limitation is the array or the card, but it seems that would be the most promising way to eliminate the backlog. Sure, the 160MB/sec scsi controllers perform well, and that would be a fine upgrade. But 200MB/sec dual fibre connections (with failover support, hopefully) would be even better.
Amateurs discuss tactics. Professionals discuss logistics.
Great; you can transfer the data fast. Assuming you have any more data to transfer.
Are the queries optimized for the database? the right indexes in use, etc? With bad index choices you could have any disk array you want, you can have any network configuration you want but it could still be slow.
a machine running a database isn't a fileserver (and shouldn't be). Does it have enough memory to do it's work? Speeding up the disks is great; but you have to analys what the machine is actual doing with the data it is reading.
Like the other posters said, start with ditching RAID 5 for RAID 10/0+1 (depending on your preference). RAID 10 (Mirror + Stripe) is my preference because of the higher redundacy - if one disk dies the whole stripe doesn't drop out. RAID 0+1 is faster but slightly less redundant. Either way, the parity overhead generated by RAID 5 is the death of a database.
Your controllers are pretty fast, it's more likely your software config or your network. Are you running MS SQL Server, or something else? MS SQL Server requires some pretty specific tuning to get good performance (like telling it *not* to use all the RAM). How well are you objects tuned?
How about your OS? What filesystem are the datafiles living on? Oracle supports RAW partitions, which allows you to eliminate the OS overhead from the database. We're testing the performace now of Oracle on different Solaris filesystems. You'd be surprised the differences between UFS, UFS with Logging, and Veritas File System.
While I won't deny that gigabit ethernet is fast, it's kind of expensive (especially if your network infastucture isn't equipped to handle it). If cost is a concern, and you're in a switched enviroment (no hubs) you can add more 100Mb Ethernet NICs and trunk them for more bandwidth. In reality, I've never choked a 100Mb connection unless something was wrong (like end users writing nasty Crystal Reports).
"All I ever wanted was to see Larry Wall give Bill Gates a Perl necklace."
http://www.eisenschmidt.org/jweisen
528MB/sec ? hello ? on gigabit ethernet you can maybe get 70% of max throughput if your lucky. 1000Mbits/sec works out to 125MBytes/sec and thats sustained which you aint gonna get.
You would be lucky to get 100MB/sec which your current setup delivers anyway.
My advice : get a copy of oracle or some other non crappy database (postgresql), optimise the heck out of your queries (use a connection pool dammit), get 2-3 $50 100Mb/sec network cards and drop them in the server (i use 3com 3c905b's...accept no substitutes...the 905c's are crappy). Then do ip traffic load balancing across the ethernet cards (linux can do it easily with a kernel compile, solaris needs sun trunking software, all other OSes are irrelevant to me). and please tell me you tuned the OS properly (especially that damn SYN packet problem which i keep seeing other clueless admin whine about) for large numbers of connections.
First, you need to consider that because of data locality, you are never going to reach the STR you are pondering. Queries would have to magically occur in such a way that the disk load was perfectly balanced between all drives. Your worst case STR is therefore 43Mb/sec, ASSUMING you benchmarked the STR on random access rather than sequential access.
Second, as many others have said, use RAID0+1 ; RAID5 has overheads that (can) involve other drives in the chain.
Third, your Gb ethernet is gigaBIT. That provides for a maximum throughput of 125Mbyte/sec on a switched network. To improve on this you could use multiple Gb NICs ... but they are on the PCI bus, along with the RAID adapters. This doesn't necessarily halve performance (it could in the worst case scenario), but the degradation depends on the size of the request/response versus the data that must be retrieved and processed.
Forth, you have a couple of more obvious software overheads. Issuing a read or write takes time. Software has to interpret the query, formulate the strategy to come up with a solution, and make read requests. Those requests are processed by file system handlers, which translate into raw disk operations. This means your file system and database software are adding a lot of overhead and latency that will reduce the STR.
Basically you are wanting a heck of a lot of memory, and a vast amount of processing power to keep up with the potential of the hard drives.
i-name =twylite [http://public.xdi.org/=twylite], see idcommons.net
RAID-5 and relational databases are a dangerous mixture. Not only does RAID 5 give you a 50% performance hit, but there are cases where data will be lost or corrupted without you ever hearing about it.
In the event of partial media failure over time with one or more disks in a RAID set, errors can be introduced into your data that will not be detected by partity checks. Once the drive runs out of sectors to remap you'll eventually have data that cannot be reconstructed by the ECC code on the drive.
Also, in the event of total drive failure, the rebuilding process performed automatically by the controllers can reduce overall performance by up to 85%.
RAID 10 is the way to go. Not only do you get highest possible level of performance and redundancy, but you suffer no performance hits during a single failure.
Don't read this post and scoff "I've never had drives break like that". I've worked in some large data facilities (ie 400-500 TERAbytes of storage) and have entire defective batches of 200 brand-new disks. Although hardware failures happen much less than they did in the past, they can and do happen every day.
So my advice to you:
1. Keep your current Fibre-channel configuration.
2. Buy more drives than you need, max out your array.
3. Backup the data, ditch RAID-5 and build RAID 10 volumes.
4. Reload the data, carefully plan where your busy tables and transaction logs are to avoid hot disks.
5. Conduct a through analysis of how your data is accessed and rearrange the volumes accordingly. Re-analyze everything every quarter.
You have reached the point of maximal return looking at your performance issues from the POV of a system administrator. You need to get a very smart DBA or start reading at this point. Designing your physical database design around your queries is the only way to pull signifigant performance increases out of your system. (except for getting rid of RAID-5)
Also, your performance expectations are too high for x86 equipment. You are never going to push out 100MB/sec from a database, even with trivial queries and optimized tables.
Conformity is the jailer of freedom and enemy of growth. -JFK
In my original post I was thinking giga"byte" when I picked the NIC (My bad). I could wait for the 10 gigabit NICs to be released or I could team up several 100baseT NICs as another reader suggested (Thanks!). I should have thought of that, but nobody's perfect. I also forgot that the NIC(s) needs a slot. I would have to place it on the 64-bit 33Mhz bus which would further decrease the overall STR from the original plan. I double checked the specs on the Mobo. The server has 3 PCI buses, not 2 (Again, my bad). There's the 64-bit 66Mhz bus with 2 PCI slots, a 64-bit 33Mhz bus with 5 PCI slots, and a 32-bit 33Mhz bus with 1 PCI slot. It's got 2 P3 800 Xeons and 1.5 GB of memory. It's running NT4 (SP6a and post patches)and SQL 7 (SP3 and post patches).
I'm extremely green in the database arena. In fact, it was just a month or so ago that I learned Access. Don't laugh! Again, suggested reading material is appreciated. I'm sure we all have an area in computers that we ignored completely until it was forced into our lives. My stronger areas and the focus of my career path are networking and security (System, network, physical, etc.).
In regards to the software our company runs, please don't bash MS. It won't help me learn anything I don't already know. I don't agree with their business practices and think that open source is the way all software should be for the good of mankind and progress. On the other hand, I did NT4 server support for Microsoft (Under one of their outsourcers) and prior to that, Windows 2000 Professional support. I have a firm belief that they're great products if you know how to use them properly. The same goes for XP (I flunking HATE 95/98/ME). When it comes to vulnerabilities and exploits, the only flaw is the administrator that doesn't install patches and doesn't understand why a properly configured firewall is a good idea. There's not many worms or hackers that exploit unannounced vulnerabilities.
Reads from Raid 5 spread over lots of disks tend to be pretty quick (quicker than Raid 0+1 / 10) since you've got more spindles active. There very well may be some kind of highly optimized raid 0+1 style controllers that utilize all spindles for reads -- that would negate the raid 5 advantage there, but I haven't seen such a controller yet.
Writes are what give you the 4x performance hit. For a read-intensive database (e.g.: datamart, datawarehouse) it might make sense to trade off some write performance for read performance.
As far as the question goes, it doesn't make a lot of sense. If you're worried about raw, sustainable transfer rates (e.g.: ftp downloads) then your tuning options are very different than if you are worried about how many transactions per second your DB is able to handle.
"But actually trying to use m4 as a general-purpose langage would be deeply perverse" --ESR