Database storage hardware

[hlaprade]

Hi Guys I want to setup a good database server, so of course I have a lot of questions hardware wise:
I want to setup a hard drive subsystem divide in 3 devices
1- Operating system
2- DATA
3- Logs
These 3 devices should be Raid Arrays 10 with 15k SAS Hard Drives controlled by Percs 5/6
Am I in the right path so far?
When it comes to Server per se I have a Itanium server with 128 gigs of ram but I am a little bit confuse with the storage, if there is something better than this please let me know, I will deeply thank you for it.

 

[sub mesa]

How big is your database? If not all that big, i would consider SSDs instead because of their superior performance. Especially the Intels with good random write specs and excellent random reads would be very suitable to this task.

What OS will the server be running? You talk about Itanium; is that the system that will be the database server? As you know Itanium is a highly specialized architecture.

 

[hlaprade]

Hi Sub Mesa, my DB is going to a tera at the end of this year, I grow around 0.7 teras per year. In addition to that are the BI solutions we had developed.
SDDs is a solution that I studied but in this case based on the amount of data written is not that effective, they are great for web servers, as far as I read. Supposedly log files do not react well at this SSDs at all. This is great where sequential writes and random reads are prevalent.
I am running SQL Server 2005 in a Windows 2008 IA64 Itanium 2 box with 32 processors and 128 gigs of ram, my main problem is 7000 users accessing this websites and applications through 31 servers web farm at pick times. At that point you can hear the datacenter screaming for help sometimes.

One of the problems that I identify was the sub system, that creates index scanning and this is my worst enemy so far. That is why I separate everything in 3 devices that way I am able to reduce IO considerately, it is expensive but looks like it is working for now.
My main task is to hunt for a better solution using fiber channel or a totally different setup.

 

[sub mesa]

Well, in your case ZFS + SSD cache might be useful. You would have the storage capacity of HDDs, but random access speeds of an SSD.

However this would mean a significant change in your setup; i can only recommend that if you can spare the time and money to invest in such a setup and test to conform to your wishes. Since this is a production box i would be lightly conservative; if tuning your existing config solves most of your problems that's likely a better option than changing everything and basically starting from scratch.

The problem is, HDDs are extremely slow when seeking alot; it could kill the performance of your server and cores will mostly be idling while clients are waiting. So it may be disk-bound; analyse well and do what is logical.

 

[gtvr]

With traditional HDs, you get much more bang for your buck than SSD. RAID 10 will give you the highest IO throughput for a database system, so that is the RAID level I would suggest. 15K rpm drives minimize rotational latency, and give you higher throughput. Obviously there is a dollar cost & heat issues to watch out for.

It sounds like you already have a server running, that isn't up to the challenge of what you are throwing at it - is that right? What is the current config?

I'm more familiar with Oracle than SQL server, but the same concepts would apply. Yes, you can separate out os/data/log, but this is the minimum standard. On Oracle, you could use an analysis of tablespaces, usage, i/o statistics and more to spread data out over a bunch of drives. With newer Oracle installs, ASM (automated storage manager) handles a lot of that.

Are you the DBA? Anyone in that role should have some books on optimizing I/O in their database. It's a very detailed subject.

 

[sub mesa]

Random reads are very low on HDD - so why would it have more bang per buck? Generally HDDs can do up to 100 IOps on true random reads; often closer to 50-60 when talking about large 5400rpm drives; or little over 100 when talking 15k rpm SAS.

Now depending on your RAM size you may remove alot of those random reads due to RAM caching. So RAM size is likely the most important spec for a database server; the more RAM the less you need to access the disk. In some cases where the database fits completely into RAM, you'll only see random writes on your database dir. Those can be buffered and still be fast with HDDs.

But if it doesnt fit in RAM; then you'll see alot of random reads and the HDDs would slow down the system alot; no matter what RAID level.

 

[gtvr]

I probably wasn't clear enough, but what I meant was you get more storage space bang per buck with traditional HDs. Performance is a different issue.

I'd certainly want to see benchmarks of SSDs vs. true enterprise RAID arrays to make a decision on this type of implementation. I would also want to know the workload mix of my database, sequential reads, random reads, or seq/random writes, and see where that fit in.

 

[sub mesa]

I think everyone understands that you pay more per GB when you choose an SSD; SSD is all about performance being the priority; though there are other benefits as well.

The HDDs can keep up quite well as long as the workload is highly sequential. If its not, SSDs are like 100 times faster, and in some cases even 1000 times (multiqueue random read).

 

[hlaprade]

Sure, I am not up to make the change yet, in order to respond to several questions, my systems is a Mid Range Itanium HP RX8640 with 32 processors and 256 Gigs of ram, solid state it is not a possibility so far, because it is a distribute database with a lot of instances. What I am doing is that at the architectural design level of the application, is being modify to work in a more friendly way with the hardware, right now it demands like there is no tomorrow. But Sub Mesa I will defenitly give it try, I will setup a testing environment in regular servers with OCZ Z-Drive p84 PCI-E MLC SSD 250G OCZSSDPCIE-ZDP84256G, I saw those on ebay.
The only downside in my particular case is the investment in order to create Raid Arrays 10 with this things. It makes almost forbiden. Jajajaj, but lets wait 3 months and then I can affort the technology since it is always going down.

Thanks so much
Aldo

 

[sub mesa]

Please note that a single Intel SSD is much faster than the OCZ Z-drive PCIe SSD. this is because they can't use a modern controller on that PCI-express card; so they used several Indilinx ones in RAID0. But that doesn't make a great controller; Intel is much better in random I/O.

So i highly suggest you only look for SSDs with a controller of Intel, SandForce or Micron. those three are high-performance and make good SSDs.

 

[hlaprade]

Best answer selected by hlaprade.