DB Performance: Host Striped RAID10/RAID0+1 versus RAID5/RAID6 EMC, XP, Shark or EVA LUNS

[Alzhy]

I am recommending switching RAID Layouts from 8-way Host Level Stripes of our SAN Arrays from RAID5 to RAID10, 0+1 or VRAID1.

All tuning have been done on our massive DBs that are on cooked/DirecIO'd Filesystems (VxFS 3.5) and it seems we're not getting the performance expected as well as the scalability during crunch time situations where in OLTP is mixed with DSS-Like transactions with heavy writes and updates.

Any WP's/benchmarks/Docs, etcetra that point to RAID1(or 10 or 0+1) Array configurations as generally significantly better than parity based (aka RAID5/RAID6) layouts?

And here's what's puzzling: Host IO metrics (i.e. queuing) does not exhibit any indications we've "slow" LUNS. So does Array back and front end metrics. And I think we've sufficient I/O channels (FC) to the SAN.

[Steven E. Protter]

Shalom,

In the world of database I/O, Index, Data and the equivalent of oracle redo, do much better in write intensive environments in raid 10.

The reason is the writes happen in less places and can happen faster.

Its hard to tell where the performance is being dragged sometimes from the OS level. The OS calculates what it thinks 100% utilization is (gpm/glance) but what relationship that has to reality is hard to tell.

I remember a situation where data replication on the SAN itself was causing massive I/O delays and changing one setting from something non-standard to standard made months of I/O errors disappear in an instant after months of looking for a solution.

There was nothing to indicate on the OS side that was helpful, though the OS reported 100% I/O utilization during the slowdowns. It still reports that, but the throughput is much better since the change on the SAN.

Sounds like you have done everything right on the OS level and maybe trying different I/O layout would be the next logical step.

SEP

[TwoProc]

Well Nelson,

I don't know of any metrics. But one thing to think of is that even though a drive array may have only 10% writes and rest are reads - keep in mind that you've now got 10% of your writes going slowly and "holding up" the next reads. So, don't just focus on percentage of writes, but how many and when.

I know that we've seen improvements by taking our drives to Raid0/1 and off of R5, and they were measured, but the change wasn't from this change alone. We also increased the size of SGA (buff cache) in Oracle as well as flipped the file systems over to Raid 0/1. There wasn't time to single step and measure, all we had time for was for the danged thing to be fixed, so made vast improvements whenever and wherever. This also included doubling the amount of cache in the front of the storage frame.

[Tim Nelson]

An experience we had with an EMC DMX series was write pending ios. This basically was due to full cache slots waiting to flush. When the cache is waiting to destage all, I mean all, ios are at disk speed not cache speed until the back log is caught up.

I remeber not really seeing this at the OS end either as the disk response was still acceptable 9ms-10ms. And it took some magic to derive the true meaning of WP statistic from ECC or SE.

[Hein van den Heuvel]

For serious, high performance, db processing raid controller based raid 0+1 is the only way to go.

>> All tuning have been done on our massive DBs that are on cooked/DirecIO'd Filesystems (VxFS 3.5)

With the direct IO in place that should be close to optimal, but raw can help still.
Why not consider a hybrid?
Specifically deploy raw for redo, undo and temp.
- All 100% write, 0% os caching needed.
- None require filesystem/os based caches
(Just temporarely hook up file system based stubs if you ever feel an OS based backup appropriate).

and uh... review Oracle ASM!

>> Any WP's/benchmarks/Docs, etcetra that point to RAID1(or 10 or 0+1) Array configurations as generally significantly better than parity based (aka RAID5/RAID6) layouts?

There is rather a few SAN writeups around. Google will find several. There are little no application benchmarks which allow you to judge the difference.
1) only the best config are published
2) time constraints often force benchmark folks to jump to the most likely to be optimal config.
The absence of raid-5 in TPC and SAP benchmarks should suggest something!

>> And here's what's puzzling: Host IO metrics (i.e. queuing) does not exhibit any indications we've "slow" LUNS.

Well, for example oracle redo is serial. Oracle does group commits. Once an IO is underway, incoming commits get grouped together and shipped out in the next IO. So the OS does not see a queue.

The normal datafile writes are decoupled from user waits and checkpoint driven. Does your system use the MTTR, constant, write mechanism or the old style bursty checkpoint?

So why do you think it is slow?
There has to be a reason this topic is started. Yes you write 'not getting the performance expected', but please qualify and quantify! Please don't let us guess!

I suspect you are usig Oracle statspack numbers? Are there lots of Io related items in the top 10 Wait Events?
- db file sequential read ?
- log file sync ?

How about the 'ms' indicators for the 'Tablespace IO Stats'?

Hope this helps some,
Please do contact me if you think I can help some more.
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Yogeeraj_1]

hi Nelson,

Maybe you are looking at the wrong place...

Did you check at the application/database levels?

At the database level, you can peep into the Oracle Enterprise Manager Database Control and view the Performance graphs in the ADDM reports.

verify the time being spend by the database doing the following activities:

network
administrative
configuration
commit
application
consurrency
system I/O
user I/O
Scheduler
CPU Used

hope this helps too!

kind regards
yogeeraj

[Eric Antunes]

Hi Nelson,

"Any WP's/benchmarks/Docs, etcetra that point to RAID1(or 10 or 0+1) Array configurations as generally significantly better than parity based (aka RAID5/RAID6) layouts?"

You can have all your non I/O intensive datafiles (OLTP) on RAID5/6, BUT DO NOT PUT REDOLOGS on those RAID5/6! If you have them on RAID5/6, you are getting FOR SURE "redo log sync" waits on your database!

So put those redolog files on a RAID 1+0 disk. Archivelogs and other I/O intensive datafiles (Datawarehouse, Business Intelligence, etc...) also performe better on RAID 1+0.

Furthermore, you should post a daily statspack report to see if your are getting other types of contention.

Best Regards,

Eric Antunes

[Eric Antunes]

Hi again Nelson,

You can see your top non idle wait events with the following sql:

select *
from v$system_event
where event not like 'rdbms%'
and event not like 'SQL%'
and event not like '%time%'
and event not like '%pipe get%'
and event not like '%dle%'
and event not like 'queue%'
and event not like 'jobq%'
order by time_waited desc

Best Regards,

Eric

[Fred Ruffet]

In the same kind of things running an Oracle DB on an EMC array, avoid RAID1 (strict mirroring) for a prefered RAID1/0 (mirrored and stripped).

Even if they say that anything can be put on RAID5, I always prefer RAID1 for logs (and temp). But it put the perfs so low, I had disk bandwith worth than my 7 years old HP FC60... Switching my 2 big disks in RAID1 for many little disks in RAID1/0 gave a big boost and gave much better perfs that RAID5 did (10:1 ratio).

just a hint that may help :)