Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Disk/tspace config. for Oracle on AIX

Re: Disk/tspace config. for Oracle on AIX

From: Mark Baker <mark_at_mrb-basys.demon.co.uk>
Date: Wed, 15 Jul 1998 00:20:31 +0100
Message-ID: <900458729.6262.0.nnrp-04.c1ed77a9@news.demon.co.uk>


Steve, thanks for taking the time for such a detailed response.

Many of the things you mention are in place - separated partitions, 2 loops per adapter , parallel query, etc... but I've read conflicting (Oracle and other) documentation on many points, so your comments are most helpful.

Unfortunately Oracle 8 is not an option for a while or else we'd have jumped at it. We're restricted to 7.3.2 so can't even get the best from bitmap indexes, let alone partition tables/views. We're likely to upgrade at a less sensitive time for the business, probably spring '99 - and we need to go on a few Oracle 8 courses before then too!

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, ...

... 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.

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)

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?

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?

Once again thank you for your help.

Best regards

Mark

Steve Phelan wrote in message <35ABD711.892DE4DF_at_toneline.demon.co.uk>...
>
>
>Mark Baker wrote:
>
>> I'm trying to determine the best set up for a data warehouse application:
>> Using parallel query, summary tables, denormalisation of keys onto fact
>> tables, partition views, etc. for Oracle 7.2.3.2.0 on AIX 4.2
>>
>
>One piece of advice - go Oracle 8.0.4. I have a number of databases (dw's)
on
>AIX4.1 through 4.3 under Oracle 8.0.4 (migrated from 7.3.4) and it's much
better
>for datawarehousing. Table partitioning and local indexes alone are worth
the
>upgrade.
>
>> The machine is pretty much dedicated to the data warehouse and I have 24
>> disks available.
>>
>> I've pretty much ruled out using raw devices as I believe each successive
>> version of AIX has cut away most/all of the performance benefits they
gave,
>> but am unsure as to the best combination of mirroring, striping and
'single
>> disk'-ing to use.
>
>Raw disks set-up through LVM can be a lot less hassle than filesystems.
After
>all, you have to create the raw LV's anyway, so why bother putting a
file-system
>in them when you don't need it? Likewise, why waste all that memory on
>filesystem buffers and CPU copying to-from them when you don't need them.
That
>CPU and memory would be much better dedicated to your db.
>
>I checked out 3 large Oracle/AIX warehouses before I started - all of them
were
>on raw. The Oracle installation manual for AIX recommends raw. I also went
to
>IBM's Oracle/AIX tuning labs here in the UK and they said: raw, raw and
raw.
>Never use filesystems for such an application.
>
>>
>>
>> For example, taking the TEMP tablespace I've tried various combinations
of
>> (say) 4 Dedicated physical disks for temp:
>> 1. OS striped - 2 logical volumes with 2 disks to each with128k stripe
>> size for both.
>> 2. OS striped - 1 logical volume of 4 disks with 128k stripe size.
>> 3. 'Oracle striped' - 4 logical volumes, one per disk with a couple of
>> datafiles on each disk belonging to temp.
>
>One - don't use the Oracle striping. It's a pain in the arse to manage it
>forever more and it isn't fine-grained enough to give you a performance
boost
>(well, not much). Test it out if you like.
>
>Stick to a smallish number of fixed sized LV's. Especially if you go raw.
I've
>tried the 128k stripe and found it 5% worse than 64k, and 64k is what
Oracle and
>IBM recommend. And use Async I/O (even works on raw). In fact, make sure
you
>read the install and tuning guide for AIX carefully (minpageahead, Async
I/O
>process numbers, MULTI_BLOCK_READ_COUNT, etc.)
>
>>
>>
>> I tried a query on an isolated large partition view spanning 3 partitions
>> with a nasty group by and order by. The partitioning is by month, with
>> around half a million rows per partition:
>> Results (in tkprof) came out much the same regardless, even when I tested
>> temp made up as a single disk with 4 datafiles on it!
>> I've tried to perform my tests with as little other activity on the
machine
>> as possible - there has been some sporadic work going on making
comparisons
>> difficult, but I was still expecting (hoping for) a clear winner. (The
temp
>> disks and partition view tables are all on their own isolated disks, so
it
>> should only be CPU power being drained by other users.)
>>
>> Anyone performed any testing in this area?
>
>Lots. Did you run monitor or iostat to watch what was going on? I've only
found
>striping to give a good boost when you also run parallel query processes.
You
>really need to start digging in a bit deeper to get a view of what's going
on on
>your system when you run these tests.
>
>BTW, you 'nasty' query was no doubt swamped on CPU, which is why you didn't
see
>much performance boost. Start with some simple like a large full table
scan -
>and then repeat it with progressively more query processes. Build up from
that.
>Same for writes - start small and build up.
>
>>
>>
>> As for the partition tables and their indexes - I'm about to try various
>> disk config. scenarios with them, but any help to cut down this (so far
>> inconclusive) testing will be very greatfully received!
>
>Stripe with LVM. Use parallel features wherever you can (Oracle 8 much
better in
>this area). Use parallel for loads; use parallel for summary table creates.
>Partition whereever you can. Split you central fact table(s) off on there
own
>tablespace and disks, and partition them. Test and monitor everything you
do.
>Read the AIX install guide and Tuning manual. Use raw devices (if your AIX
>skills are good, especially LVM). Use 64k stripes. Split the I/O over
multiple
>adapters. Are you on SSA? If so, split the drives over the 2 loops per
adapter,
>and get the heavily used drives furthest away from the adapter ports (see
the
>SSA/SSARAID Manuals for details).
>
>We run 100,000,000+ rows in many of our databases, and the above has saved
us
>from a performance and maintenance nightmare.
>
>Steve Phelan (MotoX)
>
>>
>>
>> Regards
>>
>> Mark
>
>
>
Received on Tue Jul 14 1998 - 18:20:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US