Large srt* file in progress 4gl

Question

Progress version 11.0 srt* (srt) sort/temp files grow very large in RHEL Linux 6.0. Isolated to specific database that is used with webspeed for web application. Using the -T switch parameter to define the location of file. Not using -t so the files are disconnected and not showing on the file system.

executing lsof at the shell shows the files grow to GB sizes and increasing. Third column is size in byes:

_mprosrv 29968 3862790144 /usr/temp/srtJrjsxX (deleted)

_mprosrv 31588 15290187776 /usr/temp/srtVEi9Lp (deleted)

_mprosrv 32644 1533157376 /usr/temp/srtTozP1W (deleted)

_mprosrv 32667 3890683904 /usr/temp/srte5qI1U (deleted)

Is there a way to limit the size of these temp files or stop them from growing so large?

Tom Bascom · Answer 1 · 2014-03-07T14:03:51.033

1

No, there is no parameter to limit them. Understanding what you are doing to cause the growth is key. Usually they are the result of queries that lack appropriate indexes and thus must have records selected and sorted by the client.

I would:

Enable -t on clients so that you can monitor SRT file growth in realtime.
Enable client statement caching so that you can determine what query on what line of code in which source module is responsible when growth occurs.
Compile with XREF and DEBUG so that you can review your code for table scans (XREF "whole index" references) and find (debug) source lines from the statement cache info
Download ProTop 3 from http://dbappraise.com/protop.html so that you can monitor query activity in real time
Add the -noautoresultlist parameter to your client startup (it is not a panacea but it might help in some cases)
If you happen to catch a client "in the act" without client statement caching enabled send "kill -USR1 " then find and examine the 4gl stack trace in protrace. (probably in the startup dir of the client)

edited Mar 07 '14 at 14:03

answered Mar 06 '14 at 16:14

Tom Bascom

13,405
2
27
33

Reviewed the following progress article http://knowledgebase.progress.com/articles/Article/P95930?popup=true any help on identifying which code/query is actually causing the growth? – MikeM Mar 06 '14 at 16:46
I would enable the client statement cache, PROMON, R&D, 1, 18 – Tom Bascom Mar 06 '14 at 17:23
Any side effects to just killing the PIDs associated with the SRT files once they get too large so they space can be reclaimed? Looks like once killed the files are recreated when the queries in the application are executed. – MikeM Mar 06 '14 at 20:42
Whats the real problem? Are the files too large for your storage? Is there extensive disc io? Killing the pids will kill whatever processing is being done. Try to identify the queries - see if they can be changed or if indices can be changed in the db. – Jensd Mar 06 '14 at 21:13
Correct. Files get to a size that is too large for storage depleting all the free space on the volume they reside on. And with no limit to the file size there is no telling how big the files can get therefore making it hard to allocate enough storage for them. – MikeM Mar 06 '14 at 22:25
The solution whatever the reason is to follow the link posted by @MikeM above. Check the queries and optimize them. – Jensd Mar 07 '14 at 12:34

user3466351 · Accepted Answer · 2014-04-01T14:27:04.117

If you are on 11.0, consider upgrading to 11.2 or later.

There is apparently a bug (referred to as defect OE00227173, fixed in 11.2) where some large queries cause the _mprosrv processes to continually grow their .srt files rather than reuse the filespace as they are supposed to.

From the release notes:

Issue Number: OE00227173 Temp sort file grow every time that a query is executed

When running a search that has any wildcard in a word-index, the search will create an srt file on the db server. If the query returns a large of number of rows (greater than 100,000) then space in the sort file is not entirely re-used and the .srt can grow very large.

Temporary relief can be found by disconnecting user sessions from the server PIDs in question and then terminating the server processes (best to use promon R&D,4,7,7).

Code to fetch users by server PID:

def var v-pid as int format ">>>>>>>>>9" label "Server PID" no-undo.

do while true:
    update v-pid with frame f1 side-labels.

    find _server where _server._server-pid eq v-pid
        no-lock no-error.
    disp _server with frame f2.

    for each _connect where
        _connect._Connect-Server eq _server._server-num  /** NOT _server-id **/
        no-lock:

        find _userio where _connect._connect-id eq _userio._userio-id
        no-lock no-error.

        disp _connect._Connect-Usr /** NOT _Connect-Id **/
             _connect._Connect-Name
             _connect._Connect-Device
             _connect._Connect-Time
             _connect._Connect-Pid
             _userio._userio-dbaccess
             _userio._userio-dbread
             _userio._userio-dbwrite.

    end.
end.

Sounds like this addresses the exact problem we are having. We can see file growth when doing searches that have wildcard in a word-index. Thanks — MikeM, Mar 31 '14 at 18:01
Realize also that as a temporary measure you can stop server processes with especially large srt files to release that disk space. (Either direct kill or promon R&D,4,7,7...) Of course any users connected to that server are likely to be disconnected, and killing a server process directly may tend to crash the DB. Best to disconnect the users from the server PID in question first [probably during a quiet time in the system, if it exists]. — user3466351, Apr 01 '14 at 14:22

Large srt* file in progress 4gl

2 Answers2