3

We have an application which runs already with its database on server. Because of the large amount of I/O (read and write) from this applications database we have very slow responses to our queries. So, we've decided to build a new server machine to install there that application with its database. The database size is 15Gb with 10-100 transactions/sec ,there are at least 5 physical users and 10-20 other from other applications which connects to this database and the average IOPS is 150 to 200.

The machine we're thinking to buy is detailed below. Interesting points:

  • Two raid 1 arrays. one for the database and another one for the system.

Machine Specs:

  • Xeon 2.4Ghz
  • 12GB RAM
  • Two raid 1 arrays. 4 146GB 15K disks
  • Windows server 2008 R2
  • Microsoft SQL Server 2008 standard.

What do you think about it??

Zypher
  • 36,995
  • 5
  • 52
  • 95

3 Answers3

2

You don't mention which specific Xeon it is - there is huge variety in performance based on the model, it would be good to know.

You could consider increasing the memory in an effort to hold all data in memory all the time - see if you can go up to 16-24GB.

Do you know the overall profile of the database? is it reading most of the time with few writes, or is it writing all the time? how many disk slots have you available and of what type/size/speed/physical-size?

Chopper3
  • 100,240
  • 9
  • 106
  • 238
  • Thank you for your immediate answer. This is the processors' model: Intel® Xeon® E5530, 2.4Ghz, 8M Cache, Turbo, HT, 1066MHz Max Mem. Both writing and reading all the time. The case is from dell(T410) and has 6 disk slots available for SAS, SATA,PERC S100/S300/ SAS 6/iR/H200/H700 –  Jul 30 '10 at 10:15
  • Ah, I know that processor very well indeed and it'll easily handle all your requirements from a CPU perspective. I'd pick up two cheapo 7.2krpm disks for your OS, no point in wasting a 15k on them, then have your OS on this 7.2 R1 pair and user the 4 x 15k's in a R10 for data and logs - this should hit all your buttons. – Chopper3 Jul 30 '10 at 10:20
  • 2
    Oh and try to increase that memory (oh and if you can go 64-bit all the way - OS and MSSQL). – Chopper3 Jul 30 '10 at 10:21
  • Nice idea for hdds, both of them planned for the 64bit version. Can i ask you if you know what is the difference between the Sql Server with proxies and with CALs'? –  Jul 30 '10 at 10:29
  • that's not my area of expertise - why not ask just that question as a new question? – Chopper3 Jul 30 '10 at 10:38
0

to this database and the average IOPS is 150 to 200.

Something is wrong here. If you get ONE Velociraptor with 10.000 RPM you have a higher IO budget than 150 to 200 OPS.

150 to 200 IOPS are not that much.

The database size is 15Gb with 10-100 transactions/sec

Get a low cost SSD (32, 64GB) and use that. A LOT more IO than the 15k discs, and those can be gotten cheaper, too.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • 4
    what if it's very 'write'y'? – Chopper3 Jul 30 '10 at 10:09
  • Thanks for answer! In the same server there are also other applications with IO. You mean we don't need a new machine? –  Jul 30 '10 at 10:22
  • It is unlikely that you need a new machine. As for the SSD suggestion: I don't think it is necessary for such a low volume of transactions. If you do buy SSDs, be sure to do the math on write volume/wear before you consider a "low cost" MLC SSD, and beware that many "low cost" SSDs are not optimized for IOPS. The bare minimum SSD for database would be something like an Intel X25M, but anything write-intensive must use SLC flash (X25E or better). – Skyhawk Jul 30 '10 at 15:23
  • IOPS is IOPS. 150-200 is low. Non-Optimized SSD is irrelevant here. A FAST one does about 40.000 IOPS. Even non-optimized ones should easily do 200. They normally are a factor of 100 or more faster than hard discs. – TomTom Jul 30 '10 at 16:06
0

Difficult to say if the spec you suggest will be adequate without knowing a lot more about what the DBMS is actually doing.

One thing which stands out is that the 'system' mirror is going to be relatively inactive - I'd recommend a single volume of raid 0+1 (mirror a stripe set on disks 1 and 3 on disks 2 and 4). Although since the memory you're proposing is nearly the same size as the DB itself, you may get more benefit from spending the money on just 2 disks and adding more cpu cores.

C.

symcbean
  • 19,931
  • 1
  • 29
  • 49