0

I’m looking to upgrade one of my site servers. The site currently executes around 20m sql queries a day and this is growing quite fast. Right now IO throughput is a real issue, so my idea is to move various databases onto their own disk pairs. My idea is to do something like this:

Config

Disk 1 & Disk 2 / Raid 5,6 or 10 (SAS or SSD) - database 1

Disk 3 & Disk 4 / Raid 5,6 or 10 (SAS or SSD) - database 2

Disk 5 & Disk 6 / Raid 5,6 or 10 (SAS or SSD) - database 3

Disk 7 (SSD) - Transaction Logs

Disk 8 (SSD) - Transaction Logs

My experience is lacking here, so got a few questions:

1) Firstly, does the above look stupid?

2) Is there anything I should look out for in the RAID controller, eg. Bandwidth throughput when using SSD?

3) Are all SSD disks created equal? I’ve picked up threads saying that certain types are substandard?

4) I’m assuming that for simple two disk mirrors any of those RAID levels will be ok? Bad assumption?

5) Any other tweaks to make this config better?

My site is highly transactional, but the databases aren’t that big, only a couple of gigs currently. So my major scaling requirement is IO, not size.. I’ll be throwing a lot of RAM at the box as well to put more indexes in memory etc..

Thanks Dave

Dave Sumter
  • 189
  • 6
  • What are your io requirements? Are your databases mostly read or write? How large is your database? How much ram do you have? What is your page live expectancy? – mrdenny Apr 24 '11 at 20:19
  • Hi mrdenny, I can provide all that info but I didn't want to increase the scope of the question too much. 128Gb RAM, all indexes fully in RAM. More Read than Write (about 10:1). I'm guessing with the higher read RAID 10 will be better as reads will be distributed..? – Dave Sumter Apr 25 '11 at 08:23
  • With all the data in memory already why are you trying to increase the throughput on the disks? You shouldn't be hitting the disks very hard if at all. What's the Page Life Expectancy perfmon counter show? What's the current seconds per read and seconds per write perfmon counters show? – mrdenny Apr 25 '11 at 16:16
  • Hi, all writes will hit the disk, only reads can be serviced entirely from memory. The app is highly transactional, hence lot's of reads and writes.. – Dave Sumter Apr 25 '11 at 17:23
  • Writes are written the transaction log, then flushed to disk upon checkpoint. Writes aren't written to the data files as they come in. – mrdenny Apr 25 '11 at 19:12
  • Correct, but I think that's not really relevant. Most of our transactions affect single rows. So each update/insert/delete is effectively checkpointed directly after it occurs. In effect each write affects both the transaction log, the data files, and the index files as they occur. But, I think we're getting a little off-topic here... – Dave Sumter Apr 25 '11 at 19:46
  • Nope, checkpoints only happen about once a minute, at which point the writes are flushed from the write buffer and the transaction log to disk. The only way that the write buffer would be flushed to disk faster than that would be if the write buffer filled, then SQL would flush the disk. I'm trying to make sure that you actually need faster disks. – mrdenny Apr 25 '11 at 19:58
  • Hmmm, I never knew that.. I thought checkpoint occurred after each commit..? Thanks, appreciate the info.. – Dave Sumter Apr 25 '11 at 20:53
  • No problem, it's a common misunderstanding. – mrdenny Apr 30 '11 at 20:49
  • How have you determined you have a disk IO problem? – SuperCoolMoss May 04 '11 at 14:18
  • Hi SuperCoolMoss, have a large proportion of writelog waits in SQL as well as other IO related waits. And can see that the disks are pretty much maxed out by looking at the relevant IO related performance counters directly on the server. – Dave Sumter May 04 '11 at 15:33

1 Answers1

2

No, all SSD's are not equal. The Intel G2 are quite good, and the Crucial RealSSD are good. However, the best performance is from the SSD cards that plug into PCI Express slots. The best of those currently have 1.4 TB/second (sequential) throughput.

The common denominator is random write throughput. This is best measured by running the Microsoft SQLIO utility.

You may want to go even further and put (some) indexes on a separate file group that are on an SSD. This may not necessarily help update performance, but would help query performance. Some queries may never hit the database, only the index(es).

Don't forget TempDB, if you have procedures or queries that use that.

Greg Askew
  • 35,880
  • 5
  • 54
  • 82
  • Thanks Greg, that's a great suggestion..! I've never thought of putting indexes on to their own SSD disks. That would be great for some of the indexes that get updated a lot.. – Dave Sumter Apr 24 '11 at 19:28