Re: out-of-control /tmp files (fwd)

> > Mark Trotter writes: > >I don't have a SMP machine. I will take a stab at guessing that most of the > >machines running Informix are not SMP machines. Using the file system for > >data storage seems to be a step backwards on my machine, especially for > >"larger sorts". I don't have the room, and don't want the overhead. How > >can I get sorting back in the dbspace where it belongs? > > > David Kosenko writes (from Informix): > >> You cannot "convince" the server to use raw space (dbspace space, actually) > >> for sorts. The "new" sort package was designed to perform parallel > >> sorting on SMP machines. This requires having different drives available > >> for i/o when psorting is used. It was decided that the best approach to > >> this was to use Unix files for the sorting process (generally only used > >> for larger sorts). > > sorting will NOT use dbspace space, it will always > use Unix file space. You cannot "get sorting back in the dbspace." The new > sort package was designed to be able to take advantage of SMP machines, but > works and is also used for single processor machines. > Mark, I ran into this immediately with Informix 5.0 OnLine while trying to build a TPC benchmark database with 6 million rows and generating an index. I used the DBTEMP environment redirecting temporary space to a partition that had 50 MB of free space. I almost ran out of space again because it took 46 MB of temporary space to create the index. This was before I read the release notes file that gave the NOSORTINDEX environment variable. Hope one of these solutions will help you. This comes from the file $INFORMIXDIR/release/ENGREL_5.0: ADDITIONAL FEATURE FUNCTIONALITY ================================ Sort Merge Joins ---------------- Sort merge is a new table join technique implemented in Version 5.0. Sort merge join provides an alternative to the existing nested loop table join where temporary indexes are used. The query optimizer will choose the best path of execution to produce the fastest possible join: either loop join, sort merge, or a combination of the two. Loop joins will create a temporary table and index on the inner table of a join, if such an index does not already exist. A sort merge join instead sorts either one or both of the tables involved in the join. Consequently, if sort merge joins are chosen, sort temp space is required rather than temp space within dbspaces (in the case of INFORMIX-OnLine). The sort temp space is determined by the value of DBTEMP. and more: FAST INDEX BUILD ==================== Informix Version 5.0 introduces a way to improve the performance for index creation on large tables. Prior to OnLine Version 5.0, OnLine read each row in the table sequentially and added its key value to the index being built. The first step in creating an index on a large table now involves sorting the index key values. This significantly reduces the amount of i/o during the index build. Fast indexing is the default for tables with more than 500 rows and 30 OnLine data pages. To disable fast indexing and revert to the previous method for index creation you can set NOSORTINDEX as an environment variable before you begin your session. The index key values are sorted using files in /tmp or another directory you can specify by setting DBTEMP. There will be at least one file created whose size can be calculated as follows: (size of key value in bytes + 4) * number of rows in table For machines with multiple processors, the key value sort can benefit from the use of the parallel sort facility in Version 5.0. See the INFORMIX-OnLine Administrator's Guide for documentation on how to make use of this feature. Regards, Cris Schramm Texas Instruments, Inc. #include <disclaimers> Internet cris@bs1.dsg.ti.com