PHISSUG

Hi PHISSUG,

This is my first post and I'd like to solicit your comments/opinion/experiences in how to configure your server to give the best disk throughput performance using available off-the-shelf products.

As a backgrounder, I don't have very deep pockets to buy one of those big name boxes so I'm stuck to putting together my own server using SuperMicro boxes with 3Ware controllers and Seagate hard drives.

My current box is a 10-drive box configured as follows:

Drive C: Windows binaries, mirrored drives (2 spindles)

Drive D: MS SQL Data files, RAID 1+0 array (6 spindles)

Drive E: MS SQL Log Files, Raid 0 (2 spindles)

After using this box for 2 years, I realized that I don't have enough spindles to give as high a throughput as today's technology can provide. So I Googled a bit and found that my next box should at least be configured as follows:

Drive C: Windows binaries, Raid 0 (2 spindles)

Drive D: MS SQL binaries, Raid 0 (2 spindles)

Drive E: MS SQL Temp files, Raid 1+0 (4 spindles) 

Drive F: MS SQL Data Files A, Raid 1+0 (8 spindles)

Drive G: MS SQL Data Files B, Raid 1+0 (8 spindles)

Drive F: MS SQL Log Files, Raid 0 (2 spindles)

These drives will run off a new dual-processor Quad Core Xeon motherboard with dual 3Ware 8-port SAS RAID controllers.

That's as far as I know though based on different opinions online. I will do actual tests soon using a few disks but not with the above configuration (the drives alone cost north of 700T!) so I was wondering if anyone in this forum has any experience configuring such boxes to assist me in my benchmarking effort.

Oh, the application is a web-based solution that has about 200 concurrent users spread over 150 databases. The current system slows down when multiple (>8) batch processing requests are made and a 1.5hr processing request turns into a 6-hr jam.

Any experiences shared will be highly appreciated! Salamat po. :)   

 

This is more a question of budget Tempdb should be on a different volume but it's not necessary for this to be on RAID 1+0 as you really don' need high availability, redundancy and stuff like that for tempdb. Data files and log files should be on a separate volume.  This gives you at least 3 volumes.  To stress test, you can use this tool from Microsoft 

---

Check out SQL Server tips at http://www.mssqltips.com
Check out SQL Server 2008 videos at
http://blogcastrepository.com

Whew! I thought no one would post something like this in PHISSUG or in some local forums. You got a monster in your realm there. Before i share my opinion, to be frank, I suspect if there are many guys out here and there with that kind of setup. Mostly, you would run to those big SQL forums based on the US as these kinds of setup are a norm. Nevertheless, malay natin hehehe. Baka nga naman meron dito tahimik lang sa mga setup nila.

Here is my take on your post:

In a flash, there seems no outright answer to your questions. There are no easy asnwers for these, instead I have more questions. At first glance, your existing box seem not bad at all. But here are some questions that need answers so I have a better understanding.

• 2 years is a bit too fast to outgrow a large setup (hoping you have done a sound capacity/load planning cuople of years ago). question now is what made you conclude that you are running out of disk throughput?
• what are the biggest databases in over 150? what are the busiest of the bunch?
• what was the load/growth 2 years ago?
• what was the load/growth 1 year ago?
• what's the load/growth now?
• from last year and 2 years ago, how much your system has grown in terms of number of users, volume of data, concurrency?
• from last year and 2 years ago, how much your system has degraded in terms of performance.
• and the most important question of all is how much is your current memory?

In my opnion, upgrading boxes can only achieve as much. if the problem is not with the hardware, sooner (like 2 years from now), you will end up facing the same problem if you have a steady growth rate. I have seen boxes upgraded significantly but only reduced a 6 hour processing batch to just 5.

I would first attempt to look at the greater picture if I can fix somethings without the need to change boxes. new boxes will always be my last option (IF you are not in a hurry ).

You mentioned like 150 databases. How about splitting them into several smaller boxes?

Lastly, I am assuming though that your current system is optimized and well tuned and you really have reached the point that you need a hardware upgrade. If this is the case, your only limitations is your budget.

 

I totally agree with sir ggsubscribe.  Capacity planning should take into account database lifecycle for at least 3-5 years.  I've seen databases (and have one on my own backyard) where improper capacity planning always ends up to be more expensive.  One database of mine has grown from 5GB 10 now 100GB in a year's time.  The problem is that even the application vendor did not expect the data to be as big as it is now.  SAN storage was almost always upgraded by adding disks.  Filegroups were not even considered which made it very difficult to do backups and restores.  Bottomline is that planning will save you your future if it is properly done

---

Check out SQL Server tips at http://www.mssqltips.com
Check out SQL Server 2008 videos at
http://blogcastrepository.com