Subject Re: [firebird-support] Re: Sort error
Author Helen Borrie
At 09:58 PM 27/01/2005 +0000, you wrote:

>--- In, "Robert DiFalco"
><rdifalco@t...> wrote:
> > Vers: FB 1.0.3 Windows
> >
> > We were having problems with some queries running out of sort space, so
> > we began setting the TMP_DIRECTORY variable in the ibconfig file like
> > so:
> >
> > TMP_DIRECTORY 5368709120 "C:\Program
> > Files\Tripwire\TE\Server\tmp\firebird"
> >
> > When we do that however, we get the following error:
> >
> > org.firebirdsql.jdbc.FBSQLException: GDS Exception.335544676. sort
> > error: not enough memory
> >
> > Unsetting the variable allows the latter query to complete but once
> > again breaks the earlier queries. In either case the temporary files
> > created during the query add up to around 750 megabytes of space, the
> > only difference seems to be where the sort files are created. Why would
> > setting this variable lead to an out of memory error?
> >
> > Also, note that there is plenty of disk space to accommodate the
> > TMP_DIRECTORY setting.
>Correct me if I'm wrong, but it looks to me like that might overflow a
>32-bit integer (I am no developer on firebird, but am a (C)
>programmer. What that would look to me like is that the amount of
>temp space is stored in a 32-bit integer. Try it first with an amount
>below 2^32, then with an amount below 2^31.
>Else wait for someone that knows what they're doing :)

It looks like a reasonable theory to me. :-) It's possibly due to a 32-bit
limitation in what the engine can accept but it might also be some kind of
filesystem limit, similar to the one that prevents the engine from
accessing an external file bigger than 2 Gb on some Windows systems.

You say that your large sorts need 750 Mb. If you really do want to
allocate 5 Gb of sort space, you can configure multiple locations. Just
list them one after the other:

TMP_DIRECTORY 1013741824 "C:\Program Files\Tripwire\TE\Server\tmp\firebird00"
TMP_DIRECTORY 1013741824 "C:\Program Files\Tripwire\TE\Server\tmp\firebird01"
TMP_DIRECTORY 1013741824 "C:\Program Files\Tripwire\TE\Server\tmp\firebird02"
TMP_DIRECTORY 1013741824 "C:\Program Files\Tripwire\TE\Server\tmp\firebird03"
TMP_DIRECTORY 1013741824 "C:\Program Files\Tripwire\TE\Server\tmp\firebird04"

Also realise that the server won't be aware of newly-configured sort space
until after a server stop and restart...there's also an absolute limit of
80 bytes (including quotes and spaces) that can be read as an entry from