The database server uses disk space to store temporary tables created by user applications as well as certain byproducts of query processing, such as files created for the SORT and GROUP BY operations and the overflow of hash joins that do not fit entirely in memory.
When SQL statements create temporary tables, these tables can also be explicitly fragmented in the temporary space that you create. For information on creating temporary tables with explicit fragmentation methods, refer to Temporary Tables That User Applications Create.
Follow the guidelines in this section to configure temporary disk space for your applications.
Temporary space management is primarily an issue for DSS applications. OLTP applications require relatively little temporary space. However, for best performance, use the following guidelines for all applications:
Three general guidelines apply to creating temporary dbspaces for DSS applications:
Large DSS applications might require twice as much temporary disk space as permanent table and index disk space, particularly if users can create ad hoc queries.
If you do not balance the dbspaces across the coservers, the coserver with more temporary dbspaces uses more of its resources for temporary file activity and might create a throughput bottleneck.
If temporary dbspaces are evenly distributed across coservers, the database server can use collocated joins as it builds and processes temporary files locally. Other temporary output is usually distributed by round-robin across all temporary spaces.
Use the following guidelines to estimate the amount of temporary space to create:
DSS applications often create explicit temporary tables to improve query processing speed. Environments in which users can submit ad hoc queries might require very large amounts of temporary space. Allow for such tables in your estimates of the temporary space required.
Although hash joins are performed entirely in shared memory if possible, hash tables might overflow to temporary space on disk. Queries that use sort and group operators also require temporary space. To estimate the amount of memory that is required for the hash table in a hash join, use the following formula:
hash_table_size = (32 bytes + row_size) * num_rows_smalltab
The value for num_rows_smalltab should be the number of rows in the probe table, which is the table used to probe the hash table.
For information about limiting use of temporary space, see Limiting Temporary Space for Query-Operator Overflow.
In addition to dbslices that you create for tables and logical logs, create dbslices for user-created temporary tables and for the temporary output that is part of DSS query processing.
To create a dbspace for the exclusive use of temporary tables and sort files, use one of the following commands:
For balanced use of resources on coservers, create the temporary dbslices for so that they span all coservers. For information about onutil syntax, see the IBM Informix: Extended Parallel Server Administrator's Reference.
If you expect to store more than 20,000 tables or table fragments in a dbspace, use the FRAGMENTS clause to allow the database server to manage overhead more efficiently. For example, if you expect that 30,000 tables might be stored in the dbspace, execute an onutil CREATE TEMP DBSPACE command similar to the following:
CREATE TEMP DBSPACE dbsp1 FRAGMENTS 30000 CHUNK "/dev/svr8" SIZE 10000000;
You can also use the FRAGMENTS clause when you create dbspaces for permanent tables.
By default, the database server places temporary tables and other temporary output files in all dbspaces except the root dbspace and other dbspaces that contain critical data, which includes logical or physical log files. However, using dbspaces that contain permanent tables can cause I/O problems.
To specify where the database server places temporary tables and query byproduct, use arguments to the DBSPACETEMP configuration parameter. For a list of all methods of setting DBSPACETEMP, refer to the IBM Informix: Extended Parallel Server Administrator's Reference. This section describes the two recommended methods of managing temporary space:
If you specify a list of dbspaces and dbslices as an argument to DBSPACETEMP, you can include both temporary and logging dbspaces.
If you specify the TEMP argument to the DBSPACETEMP configuration parameter, temporary output created by the database server uses nonlogging temporary spaces. All temporary tables are also placed in nonlogging temporary spaces unless the SQL statement that creates the temporary table specifies a different location.
If you use the TEMP argument, temporary spaces that you create are immediately usable for temporary tables and query output. If you specify the list of dbspaces and dbslices, then you must restart the database server to make newly added temporary space available.
For more information about the DBSPACETEMP configuration parameter, see the IBM Informix: Extended Parallel Server Administrator's Reference.
For information about limiting individual query use of configured temporary space, see Limiting Temporary Space for Individual Query Overflow.
If temporary space is limited or a single user might run a query that uses all temporary space and prevents other queries from completing execution, you can limit the amount of configured temporary space that a single query can use for operator overflow on each coserver.
The DS_TOTAL_TMPSPACE configuration parameter specifies the number of kilobytes of temporary disk space on each coserver that any query can use if query operators, such as hash joins, overflow to disk. For example, you might set DS_TOTAL_TMPSPACE to a quarter of the amount of temporary space that is configured on each coserver to allow more queries to use temporary disk space for overflow.
To change the value for DS_TOTAL_TMPSPACE dynamically, execute onutil SET DS_TOTAL_TMPSPACE as described in the IBM Informix: Administrator's Reference.
To put temporary space limits into effect, execute the SET ENVIRONMENT TMP_SPACE_LIMIT statement for sessions that run queries that you want to limit in this way. If TMP_SPACE_LIMIT is not in effect for a session, DS_TOTAL_TMPSPACE is ignored for queries submitted by the session. For information, see Limiting Temporary Space for Query-Operator Overflow.