We're planning to build a new production database, for which we have ample disk available in 4 RAID 5 arrays on an AIX 5.1 server. The database will consume about 100Gb, will be used for DSS and uses a vendor-supplied structure and application tools to build user queries. We've been using two TEMPSPACEs in our previous production environments, each using a separate array with a couple of containers each. I've been reading a lot about trying to use a number of TEMPSPACE containers equal to prefetch size/extent size, which is 25, per our vendor's recommendation.

Given the use of RAID 5, is it worthwhile to build a TEMPSPACE with 25 containers on the same RAID array?

Likewise, is there value to having a second TEMPSPACE with 25 more containers on one of the other arrays?

And would there be additional value to adding a third or fourth similar TEMPSPACE area?

In general, this reporting-only environment (save for the once per month update) seems to underperform when compared to user expectations (though not vendor expectations), and I've seen indications that sorts are at the heart of some of the performance issues, so I'm wondering if I could get more bang for the buck with better TEMPSPACE handling.

Any feedback will be most appreciated.

Thanks,
Barry Spiegel
barry.spiegel@eds.com

Your vendor recommendation seems to be valid for the cases where you have JBOD configuration (Just a Bunch Of Disks) as opposed to a RAID. This way effectively stripe your data across all disks, which allows the database manager to initiate parellel reads and writes.

With RAID you already have your data striped and you probably wouldn't want the database to mess with it.

Theoretically, I think that having JBODs for temporary spaces would be more efficient than RAID-5 since in that case you wouldn't have any overhead from parity calculation and update. It may seem insignificant but, given the nature of temp space usage, could have noticeable impact on performance. If your RAIDs allow it, you could also reconfigure them without parity (RAID-4).


People say you would want to have a separate temporary tablespace for each distinct pagesize used in data tablespaces. Say, you have a 4K TEMPSPACE (because it's a requirement to accomodate catalog tablespace, which always uses 4K pages). If you also have 16K page tablespace(s) for your data you would also define a 16K-page TEMPSPACE. I don't think there would be any benefit from additional TEMPSPACEs.

For what it's worth...

Nick