3par Oracle

[Oradba]

Hi, i m oracle dba and try to configure 3par f400 to oracle oltp database, but I can not find any information. Can you help, is there any best practice? I can not get the disk latency less than 8 ms.

[Biite]

Errr...

This is very little information, it could be multiple issues.

Some questions to be asked:

• Just how many transactions does you OLTP database need?
• How many underlying disks are serving your data?
• How many FC connections are made from your server to your storage?
• Is replication used? If so, synchronous or asynchronous and what the latency there?
• etc....

And that's just storage related. How your Oracle database is configured can also be taken into consideration.

Regards,
Martien

[Oradba]

Statistics:
Commite per/sec 100-300
Executions per/sec 7000-10000
Transactions per/sec 100-300

-How many underlying disks are serving your data?
all 64 FC 15K RPM
-How many FC connections are made from your server to your storage?
2 ports
-Is replication used? If so, synchronous or asynchronous and what the latency there?
No replication used
-How your Oracle database is configured can also be taken into consideration
oracle 10.2.0.4 + ASM 10g, asm diskgroups: controlfiles, redologs, temp, undo,data, index.
db_block_size=8K
The main problem with the disk groups DATE and INDEX with RAID 3+1

[Richard Siemers]

Statistics:
-How many FC connections are made from your server to your storage?
2 ports

That *could* be a choke point depending on how it is zoned/balanced. I assume your F400 has 4 nodes.

Each server port should be zoned to at least 2 3PAR ports, on different storage nodes. So if you have an F400 with 4 storage nodes...

HBA1 zoned to Node0, Node1
HBA2 zoned to Node2, Node3

Round Robin MPIO should be used to load balance across those 4 paths.

[Richard Siemers]

-How your Oracle database is configured can also be taken into consideration
oracle 10.2.0.4 + ASM 10g, asm diskgroups: controlfiles, redologs, temp, undo,data, index.
db_block_size=8K
The main problem with the disk groups DATE and INDEX with RAID 3+1

How are your CPG's configured on the 3PAR... one CPG using all 64 spindles, raid 5 set size of 4?

Can you run this command and post the output? This will show us your CPGs and how they are configured.

showcpg -sdg

[Oradba]

We have only 2 controllers and 4 drive cages.

We are experimenting.
Now the configuration is as follows.

Index-CPG - 8192 -t r5 -ssz 4 -ss 32 -p -mg 0,4,8,12 -cg 0,1,2,3 -p -cg 0,2 -mg 15 -p -cg 1,3 -mg 11
Data-CPG - 8192 -t r5 -ssz 4 -ss 32 -p -mg 1,5,9,13 -cg 0,1,2,3 -p -cg 0,2 -mg 11 -p -cg 1,3 -mg 15
CTRLSystem-CPG - 8192 -t r1 -ssz 4 -ss 32 -p -devtype FC -rpm 15 -mg 0,9,2,11 -cg 0,1,2,3
Redo-CPG - 8192 -t r5 -ssz 4 -ss 256 -p -mg 2,6,10,14 -cg 0,1,2,3
UndoTemp-CPG - 8192 -t r5 -ssz 4 -p -cg 0,1,2,3 -mg 3,7

[Richard Siemers]

Well good news is I am pretty sure isolating your cages and magazines is your problem. I recognize this is based on a Oracle's best practice for laying out your data on legacy storage. 3PAR, XIV and Compellent storage are not legacy disk based raid, and throw this Oracle guideline out the window.

Hopefully you can experiment more, create a new CPG with no restrictions at all on magazine or cage. Just raid5, set size 4, devtype FC. Then use dynamic optimizer (or ASM rebalance) to relocate all your index and db data there. The new LUNs in the new CPG will spread their data across all 64 spindles.

You're DBAs may protest about keeping everything on separate spindles, I've been there... but the results will speak for itself.

The method you are experimenting with can be extremely high maintenance as it creates islands/pockets of performance and capacity that cant be shared. You will have a challenge monitoring and alerting when one of those CPGs runs out of locations to write new data to. The system will report TBs of a free unused FC space, however, it could all be on the disks not permitted to the CPG that is filling up. You will have to micromanage the system to stay on top of that.

[Richard Siemers]

I think this might help:

http://www.oracle.com/technetwork/datab ... 130057.pdf

Starts on page 7 through 9.

Also:

http://h20195.www2.hp.com/v2/GetPDF.asp ... 519ENW.pdf

[Oradba]

Thank you very much for your help. I'll try this configuration.

[Richard Siemers]

My pleasure, I would love to hear your results, especially if they differ than what is expected. Everyone's workloads are different, and everyone needs to do whats best for their environment.

--RRS