December 29, 2005 at 6:52 am
We are planning on upgrading our data warehouse server in the next fiscal year. We are currently trying to get some prices for the budget. I need some advice on what to do with the disk configuration. Here is what we currently have:
Dual Xeon 2.1 GHZ
4 GB Ram
C: mirrored 68 GB drives (program files)
E: Raid 10, 10 15k 73 GB Drives (data files)
F: mirrored 68 GB drives (log files)
G: Raid 10, 10 15k 73 GB Drives (Analysis Services folder & some ndf files)
H: mirrored 140 GB Drives (Log files)
Here are some perfmon counters I have captured:
Avg Disk Queue Length:
C: 3.84
E: 15.05
G: 6.02
Avg Disk Transfers/Sec:
C: 4.77
E: 232.95
F: 12.71
G: 152.49
Avg Disk Writes/Sec
C: 4.71
E: 99.97
F: 18.68
G: 61.39
Avg Disk sec/Write
E: .02
G: .02
Avg Disk % Idle Time
C: 89.47
E: 68.19
F: 97.34
G: 67.26
H: 99.40
The disk queue length for the e drive is starting to concern me along with the high number of disk transfers/sec. I read in a couple of different places that disk transfers should be less than 50 or it could be a good indicator of a disk bottleneck. We are starting to hit our window for our refresh process during off business hours & I believe that we may be hitting a disk bottleneck. I am trying to hone my skills for performance monitoring so if you have any other counters that I should be looking at, please let me know.
For our new server, I am thinking that we will go with a 2 way 64 bit dual core, 6 gb RAM & a similar disk configuration except have 15-20 disks for the data drives instead of only the 10. Does anybody have any advice they could give on my configuration?
Thanks!
John
December 29, 2005 at 9:15 am
I saw a presentation about 4 years ago from a guy on hardware tuning. He talked about a few interesting things with disks. First was that you start to really lose out on efficiencies with RAID 5 after 6 or 7 disks, but that's not a concern here. You're RAID 1 or 10.
The other thing was that you want to keep the disks < 1/2 full because they fill from the inside out and seek times go up dramatically after the disks are half full.
I'd be concerned about your disk Qs since it seems you're hitting them hard. Going to move spindles will help spread the load, as will a little more RAM. I assume you're running SQL Enterprise, correct? You could upgrade to ss2k5, though not sure I'd recommend that. I might recommend looking at 64-bit, however, especially the Itanium 2s. There are two reasons for that.
First, with large data loads, especially with SSAS, you get more throughput from memory with the 64 bit processing. You also get a flat memory space, no paging in and out with AWE and PAE.
The second thing is that the Exchange testing has shown less disk I/O with 64 bit and in fact, they're not releasing any more 32 bit versions at all. http://bink.nu/Article5344.bink
Not sure about the $$ involved, but the upgrade should be fairly smooth, so I'd look at that.
Also, is this an OLTP server with SSAS? Or is is a warehouse of some sort? If it's the former, I might try separating them to two boxes as well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 29, 2005 at 9:32 am
Steve,
Thanks for the reply. We are running SQL 2000 Enterprise Edition. We are looking at going to SS2k5 with this new box as well as 64 bit technology.
We keep all of our servers at less than 70% full. Right now we are at 58% full on our data drives.
This is our data warehouse server & we currently do have SSAS on it as well. We don't have a huge user base right now for SSAS, maybe 10 users. We currently only have 6 OLAP cubes in production. We see potential for this increasing in the next year or so. We have considereed breaking off SSAS onto its own server but our thought for now was to keep it together on the new server & see what performance will be like. At that point if we are not happy with it, we can then split it off onto its own box.
Right now our hardest thing to determine is the number of spindles that we should go with for the data array. Currently our data warehouse database is at 258 gb, not huge but good sized. We want to get a performance boost over what we currently have but its determining how big can we go & still justify it $$$ wise. We may investigate pricing for a SAN to see if that would be cost effective or not.
Thanks again,
John
December 30, 2005 at 9:11 am
There are definite benefits to going SAN. It looks like you have a couple of disk arrays and appear to be configured pretty well. I would say you could roughly expect 20% increase in performance on the SAN with the same disk config. A SAN also offers extreme flexibility with the ability to move LUNs (Logical Unit) from one server to another and also resizing on demand. The SAN has its own storage management processors and memory. We also recently made the leap to 64 bit servers in our production environments and saw significant increase in performance and decrease in disk utilization mostly due inpart to the ability to work moreso from the additional memory. 64bit + SAN = tha bomb. However just going 64bit would probably deliver the most bang for the buck.
Good luck,
Corey
December 30, 2005 at 9:54 am
You perhaps to have the benefit also to install the SATA disk device in RAID also.
The SATA will increase the performance .
Also have a look at http://www.stratus.com and check to the server 6600.
December 30, 2005 at 11:51 am
Regarding 64Bit Itanium, it outperformed our existing heavily burdened 32 bit OLTP with 4 times as many users and transactions while utilizing only half of the 64Bit servers resources (4 of 8 processors; 16 of 32GB RAM).
January 3, 2006 at 4:29 am
Thanks for the feedback everyone! As far as 64 bit, are you running the 64-bit OS or the 32-bit OS on the 64 bit hardware? If 64-bit OS, any issues that you have run into?
Thanks!
John
January 3, 2006 at 6:26 am
We are running 64bit OS Server 2003 & 64bit SQLServer 2000. If you have any 32bit apps they will have to run in compatibility mode which will slow it down. We only had a couple of minor issues with the 64bit mostly to do w/ Server 2003 security enhancements dealing with linked server queries. We also had difficulty getting one machine to work with Emulex HBAs but works like a champ with Qlogic.
January 3, 2006 at 7:58 am
What about custom VB/VB.NET/C# apps? Does the binary need to be compiled specially for 64-bit? On a 64-bit workstation or is there another version of these products?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 3, 2006 at 8:09 am
Have you also found that drivers are more widely available for the 64-bit OS? I know that was an initial complication but I would assume that HW vendors are catching up with that.
January 4, 2006 at 7:32 am
I cannot speak on the custom VB or C+ apps as we are not running any although I would assume they would just run as 32bit apps. They only run in compatibility mode while they are running on the machine. Drivers have gotten better. The HBAs and drivers that I am using have been available and used for several years already. I think software vendors have been slower to adapt specifically to the Itanium2 since it is a small niche. I had a problem with Redgate SQLBackup initially. Command line 32bit works on the Itanium however the gui does not. I am told it is tested in version 4.0 upcoming. I have no experience with other devices that would require drivers such as a jukebox used for a WORM archiving scheme. Veritas (now Symantec) Backup Exec has a special remote agent for the 64bit machines although requires being on version 10.0. As you can see there are some special considerations and things to be worked out and thoroughly tested but 64bit is the future. I think if you keep your disk config and upgrade to a 4 way 64bit machine you will see significant improvement. The SAN is awesome but pricey.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply