| Subject | Re: [firebird-support] Re: Firebird Classic gulping memory |
|---|---|
| Author | Helen Borrie |
| Post date | 2010-02-04T03:12:14Z |
At 01:13 PM 4/02/2010, you wrote:
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!
./heLen
>Thanks Helen.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.
>
>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.
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!
./heLen