Troubleshooting
Problem
When using IBM Informix® several output sort and temp files starting with bui* srt* pro* may flood the /tmp directory, causing aggressive disk I/O operations and therefore Unix/Linux swap space usage.
Symptom
Several files with the prefix below (bui* srt* pro*) are created in the /tmp directory causing the filesystem to get full:
-rw------- 1 root informix 770048 Out 4 12:03 bui0920471_031
-rw------- 1 root informix 761856 Out 4 12:03 bui0920471_032
-rw------- 1 root informix 761856 Out 4 12:03 bui0920471_033
-rw------- 1 root informix 778240 Out 4 12:03 bui0920471_034
-rw------- 1 root informix 761856 Out 4 12:03 pro0920471_035
-rw------- 1 root informix 761856 Out 4 12:03 pro0920471_036
-rw------- 1 root informix 770048 Out 4 12:03 pro0920471_037
-rw------- 1 root informix 6995968 Out 4 12:03 pro0920471_038
-rw------- 1 root informix 3497984 Out 4 12:03 pro0920471_039
-rw------- 1 root informix 3792896 Out 4 12:03 pro0920471_040
-rw------- 1 root informix 4038656 Out 4 12:03 pro0920471_041
-rw------- 1 root informix 964337664 Out 4 12:23 pro0920471_042
-rw------- 1 root informix 968654848 Out 4 12:23 pro0920471_043
-rw------- 1 root informix 967467008 Out 4 12:23 srt0920471_044
-rw------- 1 root informix 963878912 Out 4 12:23 srt0920471_045
-rw------- 1 root informix 966320128 Out 4 12:23 srt0920471_046
-rw------- 1 root informix 961658880 Out 4 12:23 srt0920471_047
High intensive I/O activity can be seen, causing system overload.
Cause
Queries performing sort operations should consume a lot of temp space in the Informix temporary dbspaces. But if the environment variable PSORT_DBTEMP is set, the engine will send all the temporary data to its value. Also, it is possible that temporary dbspaces are no set in the DBSPACETEMP onconfig parameter, and therefore, causing all temporary data to be written to the ROOT dbspace and the /tmp filesystem.
Possibles causes:
1 - PSORT_DBTEMP environment variable is set;
2 - No temporary dbspace is set in the DBSPACETEMP onconfig parameter;
3 - There's no space available in the temporary dbspaces;
4 - Temp directories have wrong permissions.
Diagnosing The Problem
1 - Check for the output from onstat -g env command and look for the value of the PSORT_DBTEMP environment variable:
PSORT_DBTEMP /tmp
2 - Set DBSPACETEMP onconfig parameter to a valid temporary dbspace;
3 - Check that your temp directories have 664 permissions.
Resolving The Problem
1) Unset PSORT_DBTEMP environment variable:
$ unset PSORT_DBTEMP
2) Make sure that your onconfig parameter DBSPACETEMP is set to a temporary dbspace:
$ onstat -c|grep DBSPACETEMP
# DBSPACETEMP - The list of dbspaces used to store temporary
DBSPACETEMP tempdbs1,tempdbs2
Notice: You may want to check Technote 1292093 in the 'Related Information' section of this document.
3) Check if there's enough room for the sort operations in the temporary dbspaces:
$ dbaccess sysmaster -
Database selected.
> select name,
count(*) chunks, sum(chksize), sum(nfree)
from syschunks, sysdbspaces
where name matches '*temp*' and syschunks.dbsnum = sysdbspaces.dbsnum
group by 1;
>
name tempdbs2
chunks 1
(sum) 5000
(sum) 4939
name tempdbs1
chunks 1
(sum) 5000
(sum) 4947
2 row(s) retrieved.
>
This values are displayed in pages.
You might want to multiply those values by the pagesize to get it in kilobytes.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21567274