Oracle SAME or what is your datafile to disk distribution strategy?

[Former Member]

Hi All,

For big databases with the following characteristics:

1) Large size (multi-Terabytes)

2) Many conccurent transactions and users

3) mixed Workload (OLTP and Reporting)

4) and strict performance SLA requirements

What kinds of Strategies are you employing to ensure optimal I/O performance?

Specifically I'm wondering how you've chosen to layout your SAPDATAs?

*Do you use a strict Oracle SAME apporach (i.e. place all data and logs on all disks/LUNs)?

*A tweaked SAME approach? (i.e. Log and Datas separated and further separate out HOT tables)?

*Do you share LUNs between different SAPDATA filesystems or do you solely dedicate LUNS to 1 SAPDATA filesystem?

*What strategy do you use for growing the SAPDATAs? (i.e. Do you add new datafiles? Do you grow them? How to make sure I/O is distributed evenly?)

Please feel free to address any other areas of consideration I may have left out.

Thank you much!

[Former Member · Accepted solution]

We stripe on storage level with RAID 5. On OS we stripe over the luns for the sapdata's and mirror (RAID 1) for the redolog filesystems.

In my opinion the important thing is, that you establish a few benchmarks. If you suspect a performance problem, you can always rerun the tests to verify if your io is slower than it should be.

Small example:

- random io: if i see data block reads > 5ms then i start to worry

- sequential io: if i see lgwr writes < 20mb/s then i start to worry too

If you have the tests, you can also easily compare different systems.

Cheers Michael

[Former Member · Accepted solution]

Thanks much Michael, yes we have some benchmark SQL and some custom monitoring scripts (mainly on v$seesion_wait and v$filestat)...

Just curious, > 5ms on random I/o is pretty much going to be coming from a cache somewhere, no? (I've not seen disk times that fast and our DB is too big to be fully in cache)....

Or was that just an example number?

Or does your I/O subsystem deliver those speeds consistently?

Also, same question on growing your SAPDATAs.

Thanks much for your help, input and thoughts!

[Former Member · Accepted solution]

Just curious, > 5ms on random I/o is pretty much going to be coming from a cache somewhere, no? (I've not seen disk times that fast and our DB is too big to be fully in cache)....

Yes, those numbers are just plain examples, there is so much that can influence the times. 10ms can be perfectly ok too. 15k RPM disks have access times < 5ms (without any RAID). And think of SSD disks...

Our big storage systems deliver > 1000 IOPS for random IO, of course including RAID and cache. But over several tb databases...

Regarding datafile sizes i would suggest at least 10gb for large tablespaces. For smaller tablespaces i uses sizes like 1gb and add another gb if needed. I prefer not to use autoextend, unless i have abolutely no clue, which tablespace will grow in the future.

Regarding performance i think disk performance and the tuning of the striping is only of minor concern nowadays. To follow your hardware vendors advice should be sufficient. Memory is becoming so cheap, increasing the buffer cache is the way to go (besides tuning sql).

Cheers Michael

BTW: I was once doing a [blog|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/14970] [original link is broken] [original link is broken] [original link is broken]; containing some information on disk benchmarks. I was comparing a retail SSD disk to our SAN storage systems. The 11g DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure is very simple to use.

Edited by: mho on Dec 7, 2010 4:25 PM

[Former Member · Accepted solution]

Thanks mho (Michael)!

I'm thinking you mean ">10,000" or more IOPS for your big storage systems?

I can't argue with your perspective on performance just offer my own -- and that is "it usually depends on the context, the situation and the Org's dynamics".

i.e. Some folks like to Tune, Some like to throw H/W at it, Some like to understand what's causing the Load, Some like to fix the Design, while others say it's not my problem

In my experience, cache and cash is great (when you have it)...when you don't have either then that's when you're going to have to get creative, make sure things are sound, question why, etc...

Again, just my perspective...

Thanks much for sharing your Blog!

[stefan_koehler]

Hello Somckit,

well the strategy depends on your subsystem storage type.

If you got terabyte systems you mostly have a centralized storage like SAN or NAS.

In our case we got one big storage subsystem (IBM DS8100) and we created several LUNs (round about 100 GB as far as i can remember) over all ranks (stripping in storage sub system). We also used partition stripping on the OS too, so that only 32 MB are located in a row one one disk / rank.

Now we are using IBM SVC which does the stripping overall ranks, so we cut out bigger LUNs (round about 300 GB) and do not stripe on OS anymore.

Do you use a strict Oracle SAME apporach (i.e. place all data and logs on all disks/LUNs)?

Yes, the only exception are the standby log files.

Regards

Stefan

[Former Member]

Thanks Stefan, very helpful to hear your setup.

So if I understood correctly you were plaidding (EMC term for striping at the LVM level and SAN Level) but now you are only striping at the SAN level?

If that is correct, can you let me know what kind of performance difference you saw between the 2 setups?

Also, how do you grow your SAPDATAs (i.e. you grow the filesystems and then do you add datafiles or do you grow the existing datafiles)?

Thanks much.

[stefan_koehler]

Hello Somckit,

but now you are only striping at the SAN level?

That is correct. The IBM SVC is now the only component which performs the stripping overall ranks in our storage sub system.

If that is correct, can you let me know what kind of performance difference you saw between the 2 setups?

Well, that's pretty tricky, because of the SVC has its own cache (so the virtualization layer and storage layer has its own cache). With direct acess to the storage sub system (stripping on LVM and SAN level) we got round about 5 to 7 ms for random I/O .. now with virualization layer we got round about 2 ms.

how do you grow your SAPDATAs

We are using AUTOEXTEND datafiles up to 32 GB and if the filesytem is full (round about 500 GB for each sapdata) we add another one.

Regards

Stefan