Lesson about the size of temporary disk space

Robert Gilland wrote:

We are using Firebird 2.1.3.18185 on centos 5.4 32 bit system.

Running lots of SQL statements within a single transaction seems to cause the system to chew all the resources of the system.

Then we get the error:

"operating system directive write failed. No such file or directory".

Nothing was recorded in firebird.log.

Is this expected behaviour?

Helen Borrie answers:

Hmm, first guess on this one is that you have a big sort operation, for which the Classic process needs to create a temporary file on disk because not enough memory is available to do it in RAM. If you don't have a temporary directory defined by one of the environment variables FIREBIRD_TMP, TEMP or TMP on your host server (that is accessible by the firebird user) then you need to configure some space for it and assign it in firebird.conf under TempDirectories.

You might also need to either scale down the amount of SortMem you're allocating to each Classic process, or ramp up the amount of swap available, or put more physical RAM on board, or tweak all of them.

Robert Gilland follows:

Thanks Helen. We found the problem to be a sort folder size of 32 GB on a database whose size is 12 GB We allocated more space for the temporary folder ( 100 GB ) Now it is working. Although the size of the sort folder is puzzling us.

Helen Borrie answers:

Try to visualise what kind of sort space is going to be needed for this ordered query. For example, suppose you have a very wide INVENTORY_ITEM table consisting of half a million records. If you allow a SELECT * FROM INVENTORY_ITEM ORDER BY ITEM_DESCRIPTION, PRICE, you're going to have a couple of pretty big sort files. Though they are temporary, they don't go away until all the results have been fetched over by the client.

Now, suppose you have 10 operators all doing this search at the same time. That's 10 X as much sort space as what you have for one operator. It's going to take any of them rather a long time to search through their set of 500K records, i.e., that sort space is under some pretty heavy usage at times.

And if you have users who do this search at the beginning of the day and just leave it open in case they need it, it's conceivable that the "Zymol" record never gets fetched.

If users and apps behave nicely, the engine deletes the temp files as soon as it can. But if there are operators that crash out, cleaners who pull out the server's plug to plug in their vacuum cleaners, power cuts, etc., or apps that don't close idle sets, then there's a good chance those temp files just won't go away.

A "heavy day" might also be the day when users print out big reports, when a normally smaller temp space requirement suddenly escalates for a few hours.

If you suspect any of these things, it would be worth the effort to keep an eye on that temp space for a while, looking at the size and age of the files there. And of course, I'm sure I don't need to sell you the idea of avoiding the use of table objects in your apps for casual searches, do I? ;-) A targeted search like:

SELECT ITEM_ID, ITEM_DESCRIPTION,  PRICE, QTY_IN_STOCK
FROM INVENTORY_ITEM
WHERE ITEM_DESCRIPTION CONTAINING ?
ORDER BY ITEM_DESCRIPTION, PRICE

is a lot more resource-friendly than a table object with client-side filters!

Pavel Cisar adds:

The Golden Rules for temporary space:

1. Always have free space at least three times of the database size.
2. If your database is too big, reserve at least three times of the size of your biggest table (get the number of data pages allocated for the table from gstat output and multiply by page size)
3. Install a watchdog with reasonable treshold so you'll be noticed in advance that you're running out of free temporary space