RE: Performance advice

I've been led to believe that all logged temp table activity goes to rootdbs and all unlogged activity goes to temp dbspaces. Yes, no? > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org] On Behalf Of Andrew Hamm > Sent: 22 April 2004 05:53 > To: informix-list@iiug.org > Subject: Re: Performance advice > > > Andy Kent wrote: > > > > You've lost me. What do you mean by "a decent mix of types"? > > Well, some logged temp spaces, and some unlogged temp spaces. > Some activity > uses unlogged temp tables (eg the secret temp tables used by > the engine > during query plans, or explicitly unlogged temps) and some > activity uses > logged temp tables - ie explicit code. Both types of temp > activity require > assistance. > > > And how is > 1 tempdbs useful when most people are on RAID > these days? > > (If it's JABOD, or > 1 RAID array, I can understand) > > I think that's a bit of a leap; I expect that most people are > on commodity > machines with SCSI controllers and disks, perhaps with O/S or simple > hardware RAID of the 0, 1, 01, or 10 kind. I wouldn't expect > that a large > part of the userbase is using systems with massively cached > disk systems > (NVRAM etc) > > So, if the site is using the typical disk hardware, they can > see a lot of > benefit from laying out the chunks thoughtfully. Anything > that keeps more > disks busy to just the right capacity will do wonders for performance. > > Anyway, enough to and fro. sumGirl has replied and needs followup. > > > Disclaimer http://www.shoprite.co.za/disclaimer.html sending to informix-list

Well, Following up on two points : Willem Roos wrote: > I've been led to believe that all logged temp table activity goes to > rootdbs and all unlogged activity goes to temp dbspaces. Yes, no? > > Nearly : All logged temp table activity goes to the dbspace where the database was created (which is rootdbs if "in dbspace" is not mentioned). There is a good section in the manual which details the order of usage, because there are several places where the DBSPACETEMP can be defined, along with several creation types ... $ONCONFIG, environment variable, "with no log", without the "with no log", perhaps no temp dbspace at all, perhaps unlogged database etc. etc. >>-----Original Message----- >>From: owner-informix-list@iiug.org >>[mailto:owner-informix-list@iiug.org] On Behalf Of Andrew Hamm >>Sent: 22 April 2004 05:53 >>To: informix-list@iiug.org >>Subject: Re: Performance advice >> >> >>Andy Kent wrote: >> >>>You've lost me. What do you mean by "a decent mix of types"? >> >>Well, some logged temp spaces, and some unlogged temp spaces. >>Some activity >>uses unlogged temp tables (eg the secret temp tables used by >>the engine >>during query plans, or explicitly unlogged temps) and some >>activity uses >>logged temp tables - ie explicit code. Both types of temp >>activity require >>assistance. >> >> >>>And how is > 1 tempdbs useful when most people are on RAID >> >>these days? >> Well, if selecting into temp from fragmented tables, the engine will fragment within the temp dbspace*s*, and so can do parrellelism. Also, with multiple temp dbspaces, you alleviate the load on the tblspace tblspace.