If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Recommended stripe depth for Oracle on a Raid?
In the next weeks, I will have the seldom opportunity to rebuild the
storage containing the DB. All file systems will be reformated. We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding the Raid. Papers I have read say that for Oracle, one should set the stripe depth (the amount of data in KBytes per Disk) according to the product (Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case: 8x32=256K. I just don't understand this theory. That would mean that every I/O in a full table scan would only read from one disk at a time, one disk after the next - bad. However, somewhere else a rule of thomb is mentioned saying that the total amount of data per I/O (i.e. blocksize x DB_FILE_MULTIBLOCK_READ_COUNT) should correspond to the stripe width. The stripe width is (the stripe depth X the number of disks in the raid group) - we have 8 disks (+1 parity +1 spare). This advice seems more reasonable to me, because every I/O would be serviced by ALL disks in parallel. So what do you recommend? The number of users is about 5, but they use batch jobs spending hours with DB work. Thanks a lot Rick Denoire |
#2
|
|||
|
|||
Rick Denoire wrote:
In the next weeks, I will have the seldom opportunity to rebuild the storage containing the DB. All file systems will be reformated. We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding the Raid. Papers I have read say that for Oracle, one should set the stripe depth (the amount of data in KBytes per Disk) according to the product (Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case: 8x32=256K. I just don't understand this theory. That would mean that every I/O in a full table scan would only read from one disk at a time, one disk after the next - bad. Each read only has to do 1 disk I/O = good. However, somewhere else a rule of thomb is mentioned saying that the total amount of data per I/O (i.e. blocksize x DB_FILE_MULTIBLOCK_READ_COUNT) should correspond to the stripe width. The stripe width is (the stripe depth X the number of disks in the raid group) - we have 8 disks (+1 parity +1 spare). This advice seems more reasonable to me, because every I/O would be serviced by ALL disks in parallel. Every I/O hits every disk, so you get no speedup from having multiple disks = bad. If you have many I/Os going on, you want them to be hitting different disks so they don't interfere with each other. If you had only one job running, like backing up the whole DB, then you would want it to do large I/Os that stripe across all of the disks to improve bandwidth as you describe. Most DB access is not bandwidth limited it is IOPS limited. So what do you recommend? The number of users is about 5, but they use batch jobs spending hours with DB work. Thanks a lot Rick Denoire -- Jason |
#3
|
|||
|
|||
Le Thu, 30 Oct 2003 00:01:29 +0100
Rick Denoire a =E9crit: In the next weeks, I will have the seldom opportunity to rebuild the storage containing the DB. All file systems will be reformated. We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding the Raid. Papers I have read say that for Oracle, one should set the stripe depth (the amount of data in KBytes per Disk) according to the product (Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case: 8x32=3D256K. It's not totally True. In fact, Oracle will run a 32x8=3D256K IO only if all the 32 Oracle's Block are not in the SGA. The right way to understand the rule "(Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT)" is : Oracle will run a maximal size of 32 block. So in a sequential read, if oracle need 45 block, if the block 5 and 41,42,43,44 are in the SGA you will get the following IO: #IO Block Location 1 1-4 Disk 2 5 SGA 3 6-37 Disk 4 38-40 Disk 5 41-44 SGA 6 45 Disk For you Striping size, you could use a size of 8k (one oracle block) or a multiple of this size. But tunning for DB performance is not simple. There isn't an universal solution. The solution greatly depend of the database profil. There isn't the same profile on a full transactional DB or on a Datawarehouse DB. One is playing with small random IO mostly in read an the other one use more batch IO (sequential). Bur sequential IO is right only from Oracle point of vue. Once the DB live, then the block are written an deleted. So Oracle look blocks as sequential blocks in the datafile but in fact they aren't written sequentially on the disk. You have to take care too to the FileSystem BlockSize. The best way is to have the FS BlokSize and the Oracle BlokSize at the same value. I work on HP-UX servers. With JFS we use synchronous IO so only One IO could be done at one one time on a FS or on a disk. So we only one FS for an entire big DB (450G), you can only do one IO after one for adressing the entire DB. This is a serious bottleneck. A basic line to get perfomance is to spread as possible the IO, on disk (or lun) of course but on FS too, and over multiple ADAPTER if you have a high IO charge or. In this case, you could have a better load balancing accros the entire JBOD. For exemple, i plan the layout for a 450Go DB with Oracle Application. To get short response time in the transactionnal mode, i spread the DB on 100 Lun (8G) with separate FS for index, data, redolog, ... This work on an IBM ESS 2105 800 with 4 FC direct attachement with LVM Distribution on 20VG and 40FS. But for planning this, i work with the DB Administrator, with the IO number for each tablespace, the R/W ratio, and so on... If you want to get high performance DB you will have an hard work. @++ Nicolas CHEVE I just don't understand this theory. That would mean that every I/O in a full table scan would only read from one disk at a time, one disk after the next - bad. However, somewhere else a rule of thomb is mentioned saying that the total amount of data per I/O (i.e. blocksize x DB_FILE_MULTIBLOCK_READ_COUNT) should correspond to the stripe width. The stripe width is (the stripe depth X the number of disks in the raid group) - we have 8 disks (+1 parity +1 spare). This advice seems more reasonable to me, because every I/O would be serviced by ALL disks in parallel. So what do you recommend? The number of users is about 5, but they use batch jobs spending hours with DB work. Thanks a lot Rick Denoire |
#4
|
|||
|
|||
"Nicolas CHEVE" wrote in message et... Le Thu, 30 Oct 2003 00:01:29 +0100 Rick Denoire a écrit: In the next weeks, I will have the seldom opportunity to rebuild the storage containing the DB. All file systems will be reformated. We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding the Raid. Papers I have read say that for Oracle, one should set the stripe depth (the amount of data in KBytes per Disk) according to the product (Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case: 8x32=256K. It's not totally True. In fact, Oracle will run a 32x8=256K IO only if all the 32 Oracle's Block are not in the SGA. The right way to understand the rule "(Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT)" is : Oracle will run a maximal size of 32 block. So in a sequential read, if oracle need 45 block, if the block 5 and 41,42,43,44 are in the SGA you will get the following IO: #IO Block Location 1 1-4 Disk 2 5 SGA 3 6-37 Disk 4 38-40 Disk 5 41-44 SGA 6 45 Disk For you Striping size, you could use a size of 8k (one oracle block) or a multiple of this size. I suspect that in a case like the one described above you'd still be better off using the 256 KB per-disk stripe unit. Because if the Oracle code is at all smart (which it should be), it will issue all the I/O requests required by a single logical read operation in parallel, and lower-level software/firmware will coalesce the individual pieces into the minimum number of actual platter accesses required to satisfy it. So all the transferred blocks that fall within each single 256 KB range will be fetched in a single disk rotation, and the number of disks touched by the overall logical request will be limited (leaving other disks free to satisfy other parallel requests) without noticeably increasing the latency in satisfying the request (though if you go much above 256 KB with current disks, you may start to see latency increases: that's fine if you're parallel-throughput-limited, but for sequential single-threaded processing you might be better off using smaller per-disk stripes). - bill |
#5
|
|||
|
|||
We are using a CX-400 (Dell/EMC) connected via FibreChannel to a Sun Server; Oracle is 8.1.7 by now but will be 9.2.0.4 after rebuilding the Raid. Papers I have read say that for Oracle, one should set the stripe depth (the amount of data in KBytes per Disk) according to the product (Oracle block size X DB_FILE_MULTIBLOCK_READ_COUNT), in my case: 8x32=256K. There is an excellent white paper from EMC about optimizing the Clariion for different workloads. EMC has a pretty good realationship with Oracle, your EMC SE should be able to point you to specific guidelines on setting up the Clariion box for Oracle. Good luck, Greg G |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
IDE RAID | Ted Dawson | Asus Motherboards | 29 | September 21st 04 03:39 AM |
Need help with SATA RAID 1 failure on A7N8X Delux | Cameron | Asus Motherboards | 10 | September 6th 04 11:50 PM |
Asus P4C800 Deluxe ATA SATA and RAID Promise FastTrack 378 Drivers and more. | Julian | Asus Motherboards | 2 | August 11th 04 12:43 PM |
my new mobo o/c's great | rockerrock | Overclocking AMD Processors | 9 | June 30th 04 08:17 PM |
Gigabyte GA-8KNXP and Promise SX4000 RAID Controller | Old Dude | Gigabyte Motherboards | 4 | November 12th 03 07:26 PM |