Oninit cdi / Archive
Talk to an expert →

srt* files PSORT_DBTEMP DBSPACETEMP strange

Posted in 2016

Summary

A user on IDS 11.70/AIX 7.1 found sort-work files (srtXXX*) being written to $INFORMIXDIR/tmp even though four 8GB temp dbspaces were configured via DBSPACETEMP (engine restarted) and PSORT_DBTEMP was unset; he feared the filesystem I/O was hurting performance. Replies suggested checking that the temp chunks were actually used (onstat -d/-D, -g iof), noted PSORT_DBTEMP can be set client-side, and Art Kagel pointed out cooked/temp-file sorting is often faster since short-lived files stay in OS cache, recommending pointing PSORT_DBTEMP at a RAM disk/tmpfs. The temp dbspaces were confirmed in use; no definitive explanation or fix was recorded, with the last poster still asking what the procedure was doing and how much sort space was used.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Performance & Tuning, Storage & Space Management, Server Administration, Platform-Specific Issues

ALEKSANDAR — — source: IIUG Forums & Mailing Lists

Dear All, Have an production engine (11.7 Enterprise on AIX 7.1) with the following setup regarding tempdbs: > onstat -d | grep -i temp70000102ff5e930 5 0x42001 100 1 4096 N TBA informix tempdbs1 70000102ff5ead8 6 0x42001 101 1 4096 N TBA informix tempdbs2 70000102ff5ec80 7 0x42001 102 1 4096 N TBA informix tempdbs3 70000102ff5ee28 8 0x42001 103 1 4096 N TBA informix tempdbs4 70000102ff72828 100 5 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs1 70000102ff72a28 101 6 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs2 70000102ff72c28 102 7 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs3 70000102ff72e28 103 8 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs4 So basically 4 different chunks 8GB each. > onstat -c | grep -i temp ...DBSPACETEMP tempdbs1:tempdbs2:tempdbs3:tempdbs4 ... PSORT_DBTEMP not set neither in onconfig nor as env var. However even though tempdbs spaces are 99% free most of the time, I get quite a lot writes in $INFORMIXDIR/tmp ... i.e. srtXXX* files. AFAIK all these sorting should be done within the tempdbs if space is available. This is killing my performance. However if must, I'll move the sort directory to a cooked file on faster disks, just wondering if i get the situation even worse, i.e. by setting up PSORT_DBTEMP not to cause all the activities from tempdbs (which are raw UNIX files) to be forwarded to cooked file system thus making the situation even worse? Thank you, A

↪ replying to ALEKSANDAR
Marcus Haarmann — — source: IIUG Forums & Mailing Lists

Hi, how did you put the DBSPACETEMP in the onconfig ? Just added them with an editor or did you work with onmode -wf ? Just modifying the onconfig is not enough unless you bounce the engine, the engine will not be aware about the tempdbs volumes. Further checks: Are the tempdbs volumes used at all ? Does the free counter change in onstat -d over the time ? Are the devices active according to onstat -g iof ? Are the devices used if you set the DBSPACETEMP environment variable ? Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "ALEKSANDAR IVANOVSKI" <aleksandar.ivanovski@gmail.com> An: ids@iiug.org Gesendet: Donnerstag, 16. Juni 2016 05:07:52 Betreff: srt* files PSORT_DBTEMP DBSPACETEMP strange [37267] Dear All, Have an production engine (11.7 Enterprise on AIX 7.1) with the following setup regarding tempdbs: > onstat -d | grep -i temp70000102ff5e930 5 0x42001 100 1 4096 N TBA informix tempdbs1 70000102ff5ead8 6 0x42001 101 1 4096 N TBA informix tempdbs2 70000102ff5ec80 7 0x42001 102 1 4096 N TBA informix tempdbs3 70000102ff5ee28 8 0x42001 103 1 4096 N TBA informix tempdbs4 70000102ff72828 100 5 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs1 70000102ff72a28 101 6 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs2 70000102ff72c28 102 7 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs3 70000102ff72e28 103 8 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs4 So basically 4 different chunks 8GB each. > onstat -c | grep -i temp ....DBSPACETEMP tempdbs1:tempdbs2:tempdbs3:tempdbs4 .... PSORT_DBTEMP not set neither in onconfig nor as env var. However even though tempdbs spaces are 99% free most of the time, I get quite a lot writes in $INFORMIXDIR/tmp ... i.e. srtXXX* files. AFAIK all these sorting should be done within the tempdbs if space is available. This is killing my performance. However if must, I'll move the sort directory to a cooked file on faster disks, just wondering if i get the situation even worse, i.e. by setting up PSORT_DBTEMP not to cause all the activities from tempdbs (which are raw UNIX files) to be forwarded to cooked file system thus making the situation even worse? Thank you, A ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to ALEKSANDAR
Art Kagel — — source: IIUG Forums & Mailing Lists

Aleksandar: Several comments: First, I can't think of why the engine would be writing sort-work files to $INFORMIXDIR/tmp if PSORT_DBTEMP is not set. Second, note that sorting to cooked files is often faster than sorting to RAW temp dbspaces because the files lives are often short enough that they never actually get written out to disk and live entirely in the OS's cache. Third, you can make that even faster if you point PSORT_DBTEMP to a RAM disk or in-memory filesystem like tmpfs. FWIW. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jun 15, 2016 at 11:07 PM, ALEKSANDAR IVANOVSKI < aleksandar.ivanovski@gmail.com> wrote: > Dear All, > > Have an production engine (11.7 Enterprise on AIX 7.1) with the following > setup regarding tempdbs: > > > onstat -d | grep -i temp> 70000102ff5e930 5 0x42001 100 1 4096 N TBA informix tempdbs1 > 70000102ff5ead8 6 0x42001 101 1 4096 N TBA informix tempdbs2 > 70000102ff5ec80 7 0x42001 102 1 4096 N TBA informix tempdbs3 > 70000102ff5ee28 8 0x42001 103 1 4096 N TBA informix tempdbs4 > 70000102ff72828 100 5 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs1 > 70000102ff72a28 101 6 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs2 > 70000102ff72c28 102 7 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs3 > 70000102ff72e28 103 8 25 2097127 2096524 PO-B-- /usr/informix/dbs/tempdbs4 > > So basically 4 different chunks 8GB each. > > > onstat -c | grep -i temp > ....> DBSPACETEMP tempdbs1:tempdbs2:tempdbs3:tempdbs4 > .... > > PSORT_DBTEMP not set neither in onconfig nor as env var. > However even though tempdbs spaces are 99% free most of the time, I get > quite > a lot writes in $INFORMIXDIR/tmp ... i.e. srtXXX* files. AFAIK all these > sorting should be done within the tempdbs if space is available. This is > killing my performance. However if must, I'll move the sort directory to a > cooked file on faster disks, just wondering if i get the situation even > worse, > i.e. by setting up PSORT_DBTEMP not to cause all the activities from > tempdbs > (which are raw UNIX files) to be forwarded to cooked file system thus > making > the situation even worse? > > Thank you, > A > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1144577e5d6e3e0535627da9

↪ replying to Art Kagel
ALEKSANDAR — — source: IIUG Forums & Mailing Lists

Hi, DBSPACETEMP are in the onconfig and the engine has been restarted afterwards. Yes the counter changes within the onstat -d/-D but quite small portions (and i know that programmers using A LOT of temp tables. 70000102ff72828 100 5 25 20834672 20178684 /usr/informix/dbs/tempdbs1 70000102ff72a28 101 6 25 21273194 20567977 /usr/informix/dbs/tempdbs2 70000102ff72c28 102 7 25 20589829 20261282 /usr/informix/dbs/tempdbs3 70000102ff72e28 103 8 25 21503097 21127646 /usr/informix/dbs/tempdbs4 > onstat -D | grep -i temp70000102ff72828 100 5 25 20834672 20178684 /usr/informix/dbs/tempdbs1 70000102ff72a28 101 6 25 21273194 20567977 /usr/informix/dbs/tempdbs2 70000102ff72c28 102 7 25 20589829 20261282 /usr/informix/dbs/tempdbs3 70000102ff72e28 103 8 25 21503097 21127646 /usr/informix/dbs/tempdbs4 And yes, -g iof shows activities on chunks 105 tempdbs4 88085573632 21505267 86547742720 21129842 2582.6 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 8296729 0.0002 kaio_writes 7643479 0.0006 Art, I guess what you are suggesting is that cooked files are in file system cache only so they never end up written on disk? If thats so, we should be ok. I'll give it a try to point files to the RAM File system Thank you A.

↪ replying to ALEKSANDAR
Luis Silvestre — — source: IIUG Forums & Mailing Lists

The PSORT_DBTEMP can be set by the client side, if a small test I did is correct. So probably your developers are setting PSORT_DBTEMP on their side. Not sure if you can override this behavior on the server side. Luis Filipe Silvestre Marques -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ALEKSANDAR IVANOVSKI Sent: quinta-feira, 16 de Junho de 2016 14:14 To: ids@iiug.org Subject: Re: srt* files PSORT_DBTEMP DBSPACETEMP strange [37270] Hi, DBSPACETEMP are in the onconfig and the engine has been restarted afterwards. Yes the counter changes within the onstat -d/-D but quite small portions (and i know that programmers using A LOT of temp tables. 70000102ff72828 100 5 25 20834672 20178684 /usr/informix/dbs/tempdbs1 70000102ff72a28 101 6 25 21273194 20567977 /usr/informix/dbs/tempdbs2 70000102ff72c28 102 7 25 20589829 20261282 /usr/informix/dbs/tempdbs3 70000102ff72e28 103 8 25 21503097 21127646 /usr/informix/dbs/tempdbs4 > onstat -D | grep -i temp70000102ff72828 100 5 25 20834672 20178684 /usr/informix/dbs/tempdbs1 70000102ff72a28 101 6 25 21273194 20567977 /usr/informix/dbs/tempdbs2 70000102ff72c28 102 7 25 20589829 20261282 /usr/informix/dbs/tempdbs3 70000102ff72e28 103 8 25 21503097 21127646 /usr/informix/dbs/tempdbs4 And yes, -g iof shows activities on chunks 105 tempdbs4 88085573632 21505267 86547742720 21129842 2582.6 op type count avg. time seeks 0 N/A reads 0 N/A writes 0 N/A kaio_reads 8296729 0.0002 kaio_writes 7643479 0.0006 Art, I guess what you are suggesting is that cooked files are in file system cache only so they never end up written on disk? If thats so, we should be ok. I'll give it a try to point files to the RAM File system Thank you A. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Luis Silvestre
ALEKSANDAR — — source: IIUG Forums & Mailing Lists

Thank you for noticing this, However since I've "caught" this behavior while running a cron which does an dbaccess exec proc XXX i think this is not the case, since there in no definition of this type neither in the script nor in the environment anywhere. Thank you, A

↪ replying to ALEKSANDAR
BENJAMIN — — source: IIUG Forums & Mailing Lists

This is a very opaque area of the engine. I wrote a blog post about user-generated temporary tables which touches on this topic a while ago: https://informixdba.wordpress.com/2015/03/29/temporary-dbspaces/ What specifically is causing the srt files to be created. I see you can reproduce it by calling a stored procedure. What is that doing? Creating an index? A large sort too large for memory? Also do you have any idea how much sort space is actually being used (maximum size of srt files) and how this compares to what temporary space is available? Ben.

Related threads

Glossary