Re: Solaris: temporary tables in /tmp VS in a temporary dbspace

Rupan3rd wrote: > Hi Gurus out there! > > Using IDS 10 on Sun Solaris 10. > > I have a question about Informix performance when using a dedicated temporary > dbspace (properly setup and declared in DBSPACETEMP in onconfig) versus using > the default location of temporary tables ('/tmp', which on Solaris is a memory > based file system). > > This IBM page > http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqlr.doc/sqlrmst193.htm > says the following: > > 1) The dbspaces that you list in DBSPACETEMP must be composed of chunks that are > allocated as raw UNIX devices. > 2) order of precedence for where to create temporary tables: > # The dbspace or dbspaces that the environment variable DBSPACETEMP > specifies, if this is set > # The dbspace or dbspaces that the ONCONFIG parameter DBSPACETEMP specifies. > # The dbspace or dbspaces that the ONCONFIG parameter TABLESPACE specifies. > # The operating-system file space in /tmp (UNIX) or %temp% (Windows) > > Am I right in interpreting that using DBSPACETEMP is to be preferred over /tmp? > > If so, why, beside the fact that using '/tmp' on Solaris means eating memory? > > Is there anyone that has done a performance benchmark using DBSPACETEMP > versus '/tmp' on Sun Solaris? > > Thank you in advance > Rupan3rd (from Italy - and no, it's not sunny) Hm, I am not a guru, but I will say something on the topic. /tmp is of limited size on Solaris (depending on amount of RAM and/or swap). I would not use /tmp. There were threads that contained good recommendations on how to setup temporary dbspaces. You can search for them. I remember that Art Kagel was one of the authors of good recipes. One possibility, that I have used with good results, is to create at least 4 dbspaces in cooked files (not raw devices). That way you can still use lots of RAM if you have it through the file system baffer cache. If you have for example 4 dbspaces, you can make 2 of them logged and 2 nonlogged. In practice, it turns out that nonlogged dbspaces have at least an order of magnitude higher activity against them. Darko Krstic

Thank you to everyone that gave input, much appreciated! Will stick to a "real" temp dbspace, as suggested. Grazie! Rupan3rd darko wrote: > Rupan3rd wrote: >> Hi Gurus out there! >> >> Using IDS 10 on Sun Solaris 10. >> >> I have a question about Informix performance when using a dedicated temporary >> dbspace (properly setup and declared in DBSPACETEMP in onconfig) versus using >> the default location of temporary tables ('/tmp', which on Solaris is a memory >> based file system). >> >> This IBM page >> http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqlr.doc/sqlrmst193.htm >> says the following: >> >> 1) The dbspaces that you list in DBSPACETEMP must be composed of chunks that are >> allocated as raw UNIX devices. >> 2) order of precedence for where to create temporary tables: >> # The dbspace or dbspaces that the environment variable DBSPACETEMP >> specifies, if this is set >> # The dbspace or dbspaces that the ONCONFIG parameter DBSPACETEMP specifies. >> # The dbspace or dbspaces that the ONCONFIG parameter TABLESPACE specifies. >> # The operating-system file space in /tmp (UNIX) or %temp% (Windows) >> >> Am I right in interpreting that using DBSPACETEMP is to be preferred over /tmp? >> >> If so, why, beside the fact that using '/tmp' on Solaris means eating memory? >> >> Is there anyone that has done a performance benchmark using DBSPACETEMP >> versus '/tmp' on Sun Solaris? >> >> Thank you in advance >> Rupan3rd (from Italy - and no, it's not sunny) > > Hm, I am not a guru, but I will say something on the topic. > > /tmp is of limited size on Solaris (depending on amount of RAM and/or > swap). I would not use /tmp. > > There were threads that contained good recommendations on how to setup > temporary dbspaces. You can search for them. I remember that Art Kagel > was one of the authors of good recipes. One possibility, that I have > used with good results, is to create at least 4 dbspaces in cooked > files (not raw devices). That way you can still use lots of RAM if you > have it through the file system baffer cache. If you have for example 4 > dbspaces, you can make 2 of them logged and 2 nonlogged. In practice, > it turns out that nonlogged dbspaces have at least an order of > magnitude higher activity against them. > > Darko Krstic >