Re: Disk/tspace config. for Oracle on AIX

Mark,

One thing I missed mentioning yesterday was the Gorman and Dodge book - Oracle8 Datawarehousing. Don't be misled by the title, it covers Oracle7 as well. It's *by far* the best Oracle dw book out there, and shows the Oracle Press datawarehousing book up for the overpriced piece of crap it is - IMHO.

As regards the other stuff, see below:

Mark Baker wrote in message
<900458729.6262.0.nnrp-04.c1ed77a9_at_news.demon.co.uk>...
>Could you tell me if your testing revealed any performance differences with
>separated month partitions set up as the following (or similar) scenarios?
>
>We have allocated 6 disks in particular to housing partitions for our main
>fact tables (summary tables, 36 months of data). We can either place them
>thus:
>
>1. Without LV striping, by specific tablespace storage definition on
>partition creation:
>
>disk 1 : month 1, month 7, month 13, ...
>disk 2 : month 2, month 8, month 14, ...
>...
>disk 6 : month 6, month 12, month 18, ...
>

I would never do it this way.

>... or we can
>
>2. stripe with the LVM across all disks on 1 (or several) logical volume(s)
>:
>
>Disk 1 -6 : Months 1-36 thinly sliced in 64k chunks.

I've found in testing that this gives the best read and write rate, as long as the rest of your system can keep up. The performance I've seen isn't linear - 6 disks will push/pull around 4 times that of a single disk, not 6 times. You also need to hit the drives in parallel (PQO), else the striping is mostly wasted.

>
>or maybe even
>
>3. stripe with the LVM having striped 2 logical volumes
>
>Disk 1-3 : Even months, (+indexes for odd months)
>Disk 2-6 : Odd months, (+indexes for even months)
>

Could be. Easier to restore when a disk goes BOOM!

>and many more, there are so many combinations!
>
>With parallel query and option 1 isn't there a certainty of there being
disk
>contention between parallel query fetches all attempting to read from all
>disks? Or maybe this doesn't add up to much of an overhead anyway?

I'd go for taking multiple 1 Gig sized LV's (and thus tablespaces) spread over multiple drives - sorta like your 2, but only if the table is big enough to warrant it (see below about disk loss, and you might want to use 3 instead) and the access patterns are uniform. Sum them up to give the space you require. You'd have to look close at the access patterns of your data to get the most balanced distribution. Another important point about the paritioning scheme you adopt is maintenance and recovery, so always factor this in as much as raw speed.

Like I said in my previous post, I'm not a big fan of Oracle level striping, it's too course and too much hassle. Hardware or an LVM is much better. The downside of a very large table striped over many drives *without protection - RAID5 or RAID1 (or LVM mirror)* is that if you lose a drive you have to recover the entire stripe set - you can just recover the dead disk. For this reason, on a very big table I'd look at multiple stripe sets. Multiple stripe sets also work better when you have some big joins and/or big summary table creates - it helps to pull them away from the same disks/stripes as the central fact table(s).

Another thing to look at is merging historic data into single partitions and pulling it off to less and/or slower storage, as it's less likely to be accessed. Again, look closely at your own system.

>
>With option 2 I'm assuming many LVs rather than just 1 will help with the
>optimizer to break up the query in parallel with each LV treated as
separate
>physical disks. (?)
>
>Did you rule out mirroring, which as well as the extra data security (which
>we don't need for summary tables) can improve the speed of 'reads' by AIX
>making use of both copies, because it still lagged behind striping the data
>with LVM?

Mirroring is great. But...

As far as I know, IBM don't offer RAID0+1 on thier SSA adapters, and you can't *both* stripe and mirror through LVM on the same LVs at the same time. SSA adapters only support RAID5 at present. Not sure about IBM SCSI, but you'd still have the same problem in LVM. It's a pain.

If you find anything different to the above, I'm all ears. You could also consider systems like EMC Symmetix for more flexibility, but the cost is 4-5 times higher. Obviously there will be other 3rd-party products that sit somewhere in between.

>
>Once again thank you for your help.
>
>Best regards
>
>Mark

You're welcome. Keep me posted with how your testing goes.

MotoX. Received on Wed Jul 15 1998 - 02:29:17 CDT