SSD to make stupid financial database go faster on a server... reliability?

[evilspoons]

Hi everyone,

I have a server at work I'm trying to upgrade/replace. It is currently a Core 2 Quad Q6600 with a bunch of hard drives in it. It mostly just acts as an active directory for logins but it also stores backups of important files from the computers attached to a network and runs the core of our inventory tracking / financial invoicing system.

The financial software is a bit crap and runs quite slowly. I'm thinking of putting its database on an SSD to minimize any possible bottlenecks. Capacity isn't really an issue as the database currently coexists with Windows Server 2003 on a 120 GB RAID 1 array and that's got lots of room to spare.

What I'm really worried about is the SSD going 'phththptd' and our database vaporizing. We back it up on a regular basis (nightly, I think) but what are your thoughts on reliability of, say, an OCZ Agility 3 120 GB (quoted to me by the guy building the server) versus something like an RE4 hard drive?

How about SSD caching techniques, does Z68's thing have an equivalent server board yet?

Thanks!

 

[OlafSicky]

OCZ is the worst company in the PC industry why would you want one of their junk drives, they fail all the time need firmware upgrades on almost weekly basis. I would not trust them in any business environment. Try intel drives.

 

[MarkLuvsCS]

Over at xtremesystems http://www.xtremesystems.org/forums/showthread.php?271063-SSD-Write-Endurance-25nm-Vs-34nm they have been running tests hammering SSDs to see if they fail. Now although there is just half a dozen, this shows that the SSDs seem to last quite a while. The tests produce tons more writes then will be seen in the majority of cases. The Intel 320 would be your best bet. Their SSDs also showed <1&#37; performance hit after being slammed with TBs of writes. I'd stay away from OCZ as well.

 

[Elixer]

For work related stuff, I would go with a 'commercial class' SSD. Yeah, they are much more expensive, but the reason is pretty obvious. I know one guy who works at a big database firm, and he said they have no issues with the OCZ Deneva 2, and they use HDs & tape for backup hourly.

Have you done any benchmarking with the database? Are you sure the issue is with the access to the HDs, and not something else ?

 

[nanaki333]

what size is your database? intel has the 311 series that has 20GB with SLC based storage. obviously, if your database exceeds or is planned to exceed 20GB in the near future, it's not the ideal drive.

 

[alaricljs]

And of course you should be using RAID 1 and a hot spare if it's really that important.

 

[Phynaz]

Intel X-25E.

What diagnostics have you run that show database access is the bottleneck?

 

[ViviTheMage]

If this is truely a server, get an enterprise SSD.

We have a standalone machine for our whatsup application (device monitoring), which monitors over 1500 devices, and the database could not keep up. It had numerous timeouts on single disks, and raid 0 disks. We went with a single desktop SSD (this is not mission critical to us, we can redeploy a new whatsup machine in a few minutes), and it's been SOLID ever since.

Our issue was with MS SQL backing up basically.

 

[imagoon]

Honestly, without any data about say CPU utilization, any recommendation to get an SSD makes no sense. If your financial app is failing because it is on a drive that is shared for other data, and is fragmented all to hell or the disk queue depth is like 245 then simply moving the database to another cheap 7200 RPM drive would be loads of performance increase. Also if the CPU is sitting there showing 100&#37; all day and the disk queue depth is 1 or zero, then the SSD also won't help in that case. You need to know what the database is doing before it makes any sense to do anything, let alone "go SSD."

 

[Mark R]

At the very least, before you can make this decision you need to look at the performance statistics for your current server:

If you are running windows, use performance monitor to measure:
PhysicalDisk/Avg disk queue length
PhysicalDisk/Disk transfers/sec
Processor/&#37; Processor Time (one for each core)

Find out which one of these is getting pegged when the database is lagging

 

[pitz]

a) Have you determined what proportion of the transactions are reads versus which are writes?

b) RAID-1 often improves read performance dramatically.

c) If you the traffic is mostly reads, you might want to consider doing a RAID-1 with a SSD and a mechanical hard drive. The RAID should naturally send most of the read requests to the SSD, while the mechanical HDD slogs through the writes.

d) The advice to get a proper enterprise-focussed SSD (ie: Pliant, or even, to a lesser extent, Intel) is good. Also, make sure you're backing the system up properly.

 

[pitz]

BTW, if a 'server builder' is quoting OCZ drives to go into their machines.... I think you need to find a new server vendor .

 

[DaveSimmons]

At the very least, before you can make this decision you need to look at the performance statistics for your current server:

If you are running windows, use performance monitor to measure:
PhysicalDisk/Avg disk queue length
PhysicalDisk/Disk transfers/sec
Processor/% Processor Time (one for each core)

Find out which one of these is getting pegged when the database is lagging

Yes, step 1 should be finding out what the actual bottleneck is -- CPU, RAM, reads, writes.

Step 2 should be choosing a server vendor that uses real server hardware

 

[SolMiester]

a) Have you determined what proportion of the transactions are reads versus which are writes?

b) RAID-1 often improves read performance dramatically.

c) If you the traffic is mostly reads, you might want to consider doing a RAID-1 with a SSD and a mechanical hard drive. The RAID should naturally send most of the read requests to the SSD, while the mechanical HDD slogs through the writes.

d) The advice to get a proper enterprise-focussed SSD (ie: Pliant, or even, to a lesser extent, Intel) is good. Also, make sure you're backing the system up properly.

This is interesting, I didnt think you could mix the HDD like that as the slowest member would dictate speed?....Is this so for all RAID controllers?

 

[Emulex]

wait a few months intel has roadmapped their enterprise mlc drives - these will do the trick - don't expect them to be cheap though.

DO NOT USE consumer MLC cheap drives in raid on a server - you will be disappointed unless you are just building a bunch of caching servers.

ie publish data from SQL to postgres - then run your analytics off the server with ssd (or a desktop). if it blows up - have two restore quickly. reroute to 2ndary server. that's what i'd do.

honestly i just prefer 96GB of ram - then sql server can go fast with a slow drive. ram still is king

 

[biostud]

Maybe wait for Intel 710 series?

 

[thescreensavers]

BTW, if a 'server builder' is quoting OCZ drives to go into their machines.... I think you need to find a new server vendor .

This

Get Intel

 

[Ghiddy]

For databases IO is almost always the bottleneck, but you never know. If you have an IO problem you should definitely move the DB app to an SSD, or an SSD RAID array.

You mentioned that there are a ton of HDD's on this server, and it sounds like quite a few different things are going on on this server. As the other posters have said, you should check out what the resource utilization is on that machine. It might make sense to move your financial app to a separate server with SSD(s). You also want to look at exactly which disks are bottlenecking you. Your financial db might be on one drive, but if that db software is also writing to your OS drive (for master or temp db, or for swap space) you might need to upgrade that drive to SSD too. You'll also need to check if you need a RAM upgrade (usually more is better for DB's, since more data can be kept in cache), and CPU.


Side note: I think that even desktop class SSD's are more reliable than any disk based drive you are currently using. So get even a desktop class Intel SSD and you should be good.

I have two Corsair Force 120 SSD's in RAID 0 for my workstation (anything critical is backed up, and I take full images periodically). It's RIDICULOUSLY fast and I have been running for months without issues.

Make sure your backup system is working. It might be wise to just do a test run to actually verify that you are able to recover from a drive failure. You wouldn't want to experience a failure and then find out that some issue caused your backups to be unusable; then you would be proper fucked.

 

[LokutusofBorg]

Lots of small shops build machines out of desktop parts and use them as "servers". Running anything like QuickBooks or an Access database will be called a "database" at these places, but they should not be considered a database server. Adding a bunch of RAM to a server with an Access database will not speed things up like adding RAM to a SQL box.

For these low-budget operations just looking to get by, putting a consumer class SSD in that "server" is a real option. You might want to consider RAID 1 to protect against the crap-out factor for uptime. If you can free up the cash to go with something more robust then all the above advice is good. If you can't, then you just go with what you can afford.

 

[Ayah]

What kind of budget? If you can afford it, I'd direct you to pick up a RAMSAN 70, iodrive duo or if you have an extravagant budget: iodrive octal.

Any of those solutions will put conventional ssds and most ssd raids to shame. They do however inflict sticker shock for personal use.

 

[Emulex]

yeh $10-20K for a drive. you'd be better off in a server with 16gb dimms (18 of them)

 

[evilspoons]

Haha, I should've been more specific.

The entire server will likely cost about $3500 Canadian. This is not a "SERVER" so much as "a computer that happens to run Windows Server to do logins whilst running the goofy financial database software my boss decided on ten years ago."

Our current server is made of entirely enthusiast-user level components.

 

[Ghiddy]

Haha, I should've been more specific.

The entire server will likely cost about $3500 Canadian. This is not a "SERVER" so much as "a computer that happens to run Windows Server to do logins whilst running the goofy financial database software my boss decided on ten years ago."

Our current server is made of entirely enthusiast-user level components.

That's what I suspected (tipoff was the Q6600, in a "server"). Not a problem, I work in a small shop and we do the same thing. Just make sure there are suitable backups in case the hardware fails.

In that case I think you can get away with using desktop class SSD's. Just research which brands are reliable. I know for sure Intel are considered reliable, possibly others. Try to get RAID 1 or RAID 5, and it should help the speed if your OS was also running off the SSD's.

Look into your CPU and RAM utilization too, to see if those need to be upgraded.