Re: Disk/tspace config. for Oracle on AIX

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 - 17:09:21 CDT