On 3 Aug 2002 at 11:47, Helen Borrie wrote:

>
> If you don't configure sort space explicitly, your /tmp directory is
> used by default. If it's physically not big enough to accommodate
> Firebird's temp files at runtime (and sort files can get huge) then it
> doesn't have any choice but to abend. Each operation has its own sort
> files, so 100 users all simultaneously doing an ordered query of the
> select * from MyMegaTable will potentially eat lots of sort space.
> You will make your server a lot more robust by configuring it to use
> its own specific sort space (see OpGuide) on a partition that you know
> will always have enough space for maximum likely needs.

The question becomes what is enough space for maximum likely needs? Is there
any way to guage this?

Here is a situation, I have a table called orders, there are 157 fields in the table, and
5,000,000 records, 1000 records created after 2002-07-01 each record is 5,000
bytes wide. The ORDER_ID is an INTEGER, and the ORDER_DATE is a date field
(dialect 3). ORDER_ID, ascending is indexed, but not descending.

If I have the following query:

SELECT ORDER_ID FROM ORDERS WHERE ORDER_DATE > '2002-07-01'
ORDER BY ORDER_ID DESC;

How big a sort file should I guess to end up with, providing neither field is indexed.
Will the sort contain just the ORDER_ID, the ORDER_ID and ORDER_DATE, or a
copy of the entire ORDERS record, and will it sort just the 1000 records that pass
the filter or all 5,000,000 records. If it's just 1000 records x an integer then it's 4000
bytes total, if it's 5,000,000 records times 5,000 bytes it's ~25GB. Is there a way of
determining from the plan, how big a sort file will be generated by a specific query?

> However, the server is very tidy about cleaning up sort files, AS LONG
> AS IT KNOWS ABOUT THEM. If you have users who crash out of
> operations, or applications that crash the server, then sort files
> will get marooned in your sort space. The server doesn't have any way
> to revisit the sort space on restart and make decisions about files
> that have been left behind because of abends; and you can't put a
> gallon into a pint bottle.
>

I will assume those are Imperial gallons and not the short changed American variety
:-).

It could probably be safely assumed that on system startup or even Firebird startup,
you could delete any sort files that are left over. If you set up a sort space for FB,
then you could easily add a line to the FB startup script that cleaned that directory
out.

>
> However, I'm a bit doubtful that Firebird is writing any files to a
> file or directory named /tmp/core. The /tmp directory is globally
> configured, for use by apps and the OS. Firebird's temp files have
> dynamically generated names with numbers in them which are, by default
> (i.e. if you didn't configure space specifically) written into /tmp.
> Is "core" the name of the file or the name of a directory? On RH 7.2
> I see Firebird temp files but they aren't named "core"... It looks a
> bit like a core dump...
>

It is a core dump, I saw a Linux core dump once, and it was in the /tmp directory,
and was named core. You can disable them, but I forget how.Paul Schmidt,
President
Tricat Technologies
paul@...
www.tricattechnologies.com