ORACLE Database on HP Server DL580 G7 / which (SSD) storage solution?

[Fritz01]

Hi Storage experts,

we have an HP DL580 G7 server hosting an ORACLE database with ~120GB of data files in total. It is used to server an OLTP system with a few hundred users. Main workload therefore is random read I/O.

Currently the server is connected to an external storage which is way too slow and some queries suffer extremely from the low random IO throuput of the external storage (with "normal" disks).

Therefore we decided to move the database files to some kind of internal storage. There are so many options and we are not sure which would suite our needs in a long term.

1. Buy internal SSDs from HP to receive 24x7 support in case of failure. Mirror those using the existing HP SmartArray 410i Controller.
Pro: Support
Con: The price! A single HP 200GB 6G SAS SLC SSD drive is around 3000$ while delivering "only" ~40.000 random IOPs / 370 MB/s sequential read

2. Buy internal SSDs from any other company which are way cheaper and some also faster.
Pro: cheaper, could even buy double the amount to be able to replace them when dead; Faster because we could use 6 instead of 2 for the same money or less
Con: No hardware support from HP

3. Go for a PCIe solution like Intel 910 series (which from what I read would kill any 2x or 4x HP SSDs in a performance perspective)
Pro: cheap, compared to HP supported single 2,5" SSDs; FAST!
Con: security? Is it possible to "mirror" two of those cards in case one fails? I doubt there is any chance of getting the comfort of a mirrored hot plug 2,5" drive connected to a raid controller, right? In case of any damage, we would definaltey shut down the server and replace the whole 910 card, right?


Sorry for the long text, I tried to describe as good as I can and really appreciate any comment.

Thanks!!

Fritz

 

[Broheim]

I've actually had the same question in the back of my mind for a while (we use HP too and they really do screw you over on SSD prices).

the solution I've been envisioning is using 910s for caching on our revit servers, it would make sense since we have a lot of huge projects but not every project is active all of the time. Should the 910 fail then we hopefully just fall transparently through to our mechanical drive array (atleast I hope it works this way).

 

[smangular]

I'm a big fan of Intel's Support through Partners since they can get overnight replacement for you. Partner up with a good local reseller and they can help.

 

[beginner99]

i would say 120 GB of data has soem monetary value and putting data with a value of 1 Mio $ on a $200 consumer SSD, well I guess you can see my point...

The drives don't cost $3000 because of their performance but because of the support. I guess it depends how important this application is. if you can afford a downtime of several days now and then and have proper backups well then I think you can risk it but if you need 99.999% up time well, not so much.

Also 40'000 IOPS is a lot. Your current external solution is probably somehwere around 1'000 IOPS or lower. But that is just a pure guess from me.

 

[Phynaz]

What was your external storage? Was it a name brand SAN? What do they offer for increasing the performance of a database?

Moving to internal storage is really backwards. Like welcome to 1999 backwards.

 

[razel]

Is this a production machine? If so, go to your SQL admin. Chances are he/she will be best at diagnosing the bottleneck. Do not toy around and experiment when businesses are on the line. If you screw up, it's your bad.

Throwing more hardware to solve a problem is the beginning of a disaster. If you scale this mountain, you will relieve this situation and a bigger one will occur. That is a much be larger mountain to scale and you can no longer throw more hardware to help you.

Believe me, I program. We do NOT have any SSDs in our farm. There is no need when you have great SQL/server admins. They have MANY tricks. Most of the time, the best one is heating up the feet of the programmers.

 

[Cable God]

First, OP said this was Oracle. Not sure why the above poster is bringing up SQL Server. Secondly, you can tune an instance and queries only so far. There comes a point of diminishing returns that you spend more money and time tuning than you would by adding SSD. I've heard developers say the exact same thing before: "The DBA should be able to tune it to make it faster". This is not always the case. I've personally seen processes that had many hours of tuning go from taking 6-7 hours to complete to minutes after simply moving the DB storage to SSD. I have done in-depth testing with Oracle on SSD's ranging from 2.5" SATA SSD's to Fusion IO PCI-E SSD's to massive Texas Memory Systems SLC and DDR based arrays. I have SSD's deployed everywhere in our Oracle environments, and it has been worth every penny.

OP: With the Intel 910 cards, you can indeed use software RAID to mirror two of the cards for redundancy. This will also add even more performance. I've seen it with the Fusion IO cards.

 

[razel]

First, I didn't say SQL Server. I said SQL. Oracle uses SQL language. In the industry saying SQL means dealing with databases. However, reading the tone of your post, it seems like you want to spread your feathers about accomplishments instead of providing wise business advice.

In this economy you maximize utilization to survive. People just aren't spending money. So utilize your workers. Your dba admins and programmers.

 

[Cerb]

If it's only 120GB, how much does more RAM for the servers cost? Note that most DBMSes will require an admin to explicitly configure the server to be able to make good use of a lot of RAM, even when plenty if there to be used.

 

[Fritz01]

Hi All,

thank you all for the valuable replies!

@beginner99: You are right, the data is worth a lot. Of course we do backup the database regularly (now and will also do this in the future) and backups are stored in another building for safety. The internal SSDs are planned to be mirrored as well, so in case one fails we could replace it with another "on the fly". The raid Controller does support this.

@Phynaz: The current external storage is an HP EVA 3000. It is quite powerful but there are so many other applications running on it at the moment (a lot of ESX servers and other databases for example) that read performance has become really bad. Moving to internal storage might be a step backwards in case of security, but still we have RAID + backup so I think it will be OK. There won't be any other solution in the near future to make the EVA perform better.

@razel: I totally agree with you that throwing hardware at a problem is not always the ideal solution (especially with databases). I am familiar with ORACLE SQL Tuning and believe me we have tried a lot to make bad performing queries perform better. The server has 64GB of RAM so there's quite a lot of space for ORACLE Buffer cache, but still there are queries where required blocks are mostly NOT in the cache so they need to be read from HDD. Even with no full table scans, all index accesses, some queries still need quite a time. I mean even 15-20 seconds are already "disturbing" for a query in an OLTP system. Analyzing the query execution I can see that data is coming in with somewhat between 1,5 and 2,5 MB/s which is incredibely slow (random read).
In this case throwing hardware at the problem WILL definately solve the problem as tuning the SQL even more is not worth the time as it will not result in a 20 times faster execution.
I ran some tests with a copy of the whole database on a workstation equipped with 4x consumer SSDs and some of the affected queries indeed executed up to 10x faster.

@cerb: The server is equipped with 64GB RAM which should be enough I think. There is no "easy" solution to have the database run entirely in memory unless you buy something like ORACLE's "TimesTen" which is a dedicated in-memory database with additional installation, configuration and licensing requirements.

@Cable God: I thought about software mirroring two Intel 910 cards already, but for some unknown reason I don't thrust in software RAID a lot and would definately prefer some hardware mirroring for a production system.

I'll throw in another "Enterprise" SSD candidate for internal use: The Intel 710. It's way lower priced than the HP drives and still seems to be very long lasting.

 

[Cable God]

SQL admin

In the industry SQL Admin = SQL Server DBA, and SQL Administration = Administration of a SQL Server instance. No ill will nor harm intended towards you. I'm simply giving my experience from actually running Oracle on SSD which you seem to have not done since you say "We do NOT have any SSDs in our farm".

@OP: I don't like softRAID either, but talk to the guys at Fusion IO. They'll send you a demo card so you can see for yourself if the purchase it worth it. They have thousands of cards in production in softRAID in both Windows servers and Linux servers. Performance scales nearly linear with each additional card. If you don't want to spend "Fusion IO" dollars, the Samsung 830/840 seems to be the most popular in "enterprise circles" for running in DB servers. Just make sure to manually over provision by ~20% and you double to triple the life of the drive in most cases. Here's a good thread covering the subject: http://www.webhostingtalk.com/showthread.php?t=1198450

 

[Cerb]

With 64GB, if the admins can't get it fast enough, yeah, there's not much left to do that's easy.

 

[Phynaz]

@Phynaz: The current external storage is an HP EVA 3000. It is quite powerful but there are so many other applications running on it at the moment (a lot of ESX servers and other databases for example) that read performance has become really bad. Moving to internal storage might be a step backwards in case of security, but still we have RAID + backup so I think it will be OK. There won't be any other solution in the near future to make the EVA perform better.

Can I assume that you have a skilled storage management person running the EVA 3000? What do they say about the performance? How are they partitioning the storage? If you are having read performance issues, are you multiplexing across multiple HBA adaptors?

 

[smangular]

Fritz01,

Did you get a good answers to your questions?
I'm curious how your selection ends up performing.

 

[Fritz01]

Can I assume that you have a skilled storage management person running the EVA 3000? What do they say about the performance? How are they partitioning the storage? If you are having read performance issues, are you multiplexing across multiple HBA adaptors?

Well yes and no. We have someone taking care but it's not a full-time storage expert. There might as well be room for improving the EVA, but we don't know how.
Don't ask me how "they" are partitioning the storage, I have no idea how this works. I will forward this question and post reply once I know.

Did you get a good answers to your questions?
I'm curious how your selection ends up performing.

No decision yet, but will let you know once we decided!

 

[Fritz01]

Can I assume that you have a skilled storage management person running the EVA 3000? What do they say about the performance? How are they partitioning the storage? If you are having read performance issues, are you multiplexing across multiple HBA adaptors?

OK, the answer to multiplexing across HBA adaptors is "no".
And sorry, we have an EVA6100 and not a 3000 as stated in a previous post. My mistake.

 

[Fritz01]

OK guys optimizing EVA performance in regards of this database server is not an option since know-how is mostly external and the EVA will be replaced within the next half year or year anyway. So the preferred solution is to get internal SSDs for the database server to have an immediate performance increase.

Decision is between those three candidates attached to a HP SmartArray P410i (note that prices are roughly translated from EUR to USD as I guess most of you are located in the US):

1. 4x HP 200GB MLC SSD (~8500$)
pro: support
con: most expensive while not the fastest

2. 7x Intel 710 100GB MLC-HET (~3500$) (6x RAID + 1 spare)
pro: cheaper, but still good for server/business use
con: no HP support

3. 7x Samsung 840 Pro 256GB (~2300$) (6x RAID + 1 spare)
pro: cheapest, fastest
con: no HP support

questions:

- which would you prefer in terms of reliability
- are all of those hot-swap capable on a SAS controller?? I read that SATA II drives like the Intel 710 might not be
- When used in a RAID, I read that TRIM command can not be sent from OS, so SSD controller needs to manage by itself -> which of those can do it?

At the moment I tend to get the 840 Pro's and format them with e.g. 200GB each so there's still space left to manage dead cells.

What do you think?
Thank you very much!

 

[Phynaz]

OK guys optimizing EVA performance in regards of this database server is not an option since know-how is mostly external and the EVA will be replaced within the next half year or year anyway. So the preferred solution is to get internal SSDs for the database server to have an immediate performance increase.

EVA 6100 is a high performance enterprise class system. For the money you want to spend on on DASD you should spend on getting help configuring your storage properly and helping all the applications using it.

Spending $20K - $40K on consulting on your current storage would most likely put you in a position of not having to spend $200K replacing it in six months.

There is a great probability that your issue isn't in the storage itself, but in the skills of the person running it.

 

[Phynaz]

At the moment I tend to get the 840 Pro's and format them with e.g. 200GB each so there's still space left to manage dead cells.

What do you think?
Thank you very much!

You do not put consumer class SSD in a server.
Unless you don't value your data or your job.

 

[tweakboy]

No such thing as internal SSD you can have it outside too.

Don't buy that POS SSD for your delicate storage tasks.

Grab a Samsung or Crucial and off you go!!!!!!!!!!!

Hi Storage experts,

we have an HP DL580 G7 server hosting an ORACLE database with ~120GB of data files in total. It is used to server an OLTP system with a few hundred users. Main workload therefore is random read I/O.

Currently the server is connected to an external storage which is way too slow and some queries suffer extremely from the low random IO throuput of the external storage (with "normal" disks).

Therefore we decided to move the database files to some kind of internal storage. There are so many options and we are not sure which would suite our needs in a long term.

1. Buy internal SSDs from HP to receive 24x7 support in case of failure. Mirror those using the existing HP SmartArray 410i Controller.
Pro: Support
Con: The price! A single HP 200GB 6G SAS SLC SSD drive is around 3000$ while delivering "only" ~40.000 random IOPs / 370 MB/s sequential read

2. Buy internal SSDs from any other company which are way cheaper and some also faster.
Pro: cheaper, could even buy double the amount to be able to replace them when dead; Faster because we could use 6 instead of 2 for the same money or less
Con: No hardware support from HP

3. Go for a PCIe solution like Intel 910 series (which from what I read would kill any 2x or 4x HP SSDs in a performance perspective)
Pro: cheap, compared to HP supported single 2,5" SSDs; FAST!
Con: security? Is it possible to "mirror" two of those cards in case one fails? I doubt there is any chance of getting the comfort of a mirrored hot plug 2,5" drive connected to a raid controller, right? In case of any damage, we would definaltey shut down the server and replace the whole 910 card, right?


Sorry for the long text, I tried to describe as good as I can and really appreciate any comment.

Thanks!!

Fritz

 

[Emulex]

hp renew selles (fusion) io with a warranty. check it out.

I suspect you have filled every dimm slot with 16GB of ram? Ram is cheapest. and hella fast. If your entire DB+indexes fits nearly in ram, you only have to worry about log files which you can speed up by throwing a metric ton of DASD at it. (hp wins TPCC using 24 spindles in raid-0 for redo/log) - i'd look at raid-10.

the p410i is way too slow, it is about 1/2 the speed of the M5014/9260 I have.

Get that server full of ram man, you bought it, iirc it can handle 512 or 1024GB of ram so you should always keep more ram than database+indexes+o/s

 

[Fritz01]

Hi All,

just wanted to let you know our final decision.
We got internal SSDs for this server now. We've decided for HP SSDs to have enterprise support. We got the "cheaper" version from HP which means MLC SATA and are using them with the P410i in Raid10.
With the "HP Array Diagnostics and SmartSSD Wear Gauge Utility" we are able to monitor the SSD wear level.

Now we get ~ 30.000 8K random read IOps from this Raid drive. This might not be the best scoring for an SSD raid, but keeping in mind that the P410i is not high-performance controller, and that those HP SSDs are built for endurance instead of ultra-high-speed it's still a 6x increase compared to the 3500-5000 IOps we got with the EVA.


OLTP application performance has increased. a lot of I/O intensive queries run much faster (example: one query dropped from 30 seconds to 5 seconds).
Regular jobs also perform better. One job which tool 70-90 minutes in average now completes in ~20 minutes.

We are very happy with the results so far.

Thank you all for your replies.

 

[Emulex]

psst throw in a p420 and watch them really fly. the p410 runs ssd's sata at 3gbp/s.

about 80K IOPS with 6 in raid-10 (840 pro) - you want to avoid raid since the most latent drive drags down the whole system with write through caching.

the adaptec 7805/71605e is not a bad choice but its the same chipset as the p420 hp.

LSI 9260 w/fastpath will decimate the p410 on ssd. the p410 is similar to the lsi 9260 without fastpath.

the 9265/9266 is faster than the 9260 with fastpath - dual roc. the 9271 of course is even faster with pci-E 3.0

the non-megaraid value lines tend to have problems with esxi (9211/9217) not sure why but caching is disabled. Perhaps it is the main card ram that is poor.

One more thing: The dl380e comes with soft-raid. like ICH style. However they have HPVSA drivers! for esxi software raid! and flash back write cache! "Designed for emerging technologies" AKA Consumer SSD is fastest direct to the intel mobo ports.

And that my friend is the truth.