-
1
- #1
Olaf Doschke
Programmer
There has been much discussion about what settings to make with SYS(3050). Perhaps the highest truth is "you can find the best value by trial and error".
First here is a good source of info with links to further discussion in the wiki:
This is rather a story and a correction to another statement I made about the SYS(3050) function in previous posts. I once said you could set this to a low value, like 1-8MB, and foxpro would higher the RAM allocation when needed. That's not the full truth, as it seems. Reading the wiki it states what is surely more correct:
So here I am taking back waht I said.
By the way, just as a side note... Bill Gates never said that quote about the 640KB. You find the full story behind it in wikiquotes. It just fits this topic as title, quite nice.
Let's see what problem I was faced and how I found out SYS(3050) would rescue me: For a few days a certain query on a large table (600MB) ran very slow. There was no error, not even a "not enogh memory to finish the operation" or something like that. Debugging did point us to some false positives, thinking about index corruption and other things. Finally I started up Rushmore optimisation reporting via SYS(3054,12) and it reported back "Not enough memory for optimization".
As said this is not raising an error, it simply leads to letting VFP run the query unoptimized, which is deadly for the performance of a query on a 500MB table.
I could solve that problem by highering memory buffers in the starting prg. There was a setting of it to 1MB. It seems the table has grown just enough to make that 1MB too less caching memory for indexes and highering it solved the problem.
I make one conclusions from this: The memory needed for caching is never raised by VFP on it's own, you need to adjust the setting yourself.
In my case the dbf as said has a size of 600MB, CDX file is about 200MB. As 8MB buffer memoery is sufficient, VFP only buffers partial DBF/CDX. That's also what is to be expected, but it would be good to know what amount of memory you need for rushmore to always work even on 500MB tables. I think what takes a few more bytes in memory is the "bitmap" it builds to determine the recnos it will retrieve That bitmap grows with the size of the table. The number of records is the main figure, I think. That bitmap should need 1bit per record (1 to denote to read the corresponding record, 0 for not needing that record). The 600MB DBF contains roughly 6.2 million records and this means a bitmap initially blank to fill in from rushmore with recnos read from index nodes, needs 6.2 million/8 bytes = 0.78 MB. That would take most of the 1MB buffer memory and if there are further things cached, of course this will make rushmore fail to get that .78 MB for it's rushmore bitmap.
Perhaps that's a good figure to think about, when determining SYS(3050) memory allocation size. Take the largest table you have in number of records and divide that by 8. Take into account joins with other tables, so add their reccount to the figure. Add a few KB to MB for other caching and you have a reasonable figure. I guess if whatever you get that way is lower than a MB, take at least a MB. I think the time is over to make it a percentage of the whole RAM, just think in absolute figures. The buffer you reserve is notfor code, the runtime itself, variables or such things. I have another application, where I move data of a few databases together and there I set the buffer to 500MB, which may not even be needed.
Finally, I want to point to
I can't confirm, that Foxpro decides to allocate more memory or creates page files to store a new optimization bitmap, it simply does not and executes a query nonoptimised at least in some cases, so you better watch out for your setting.
Bye, Olaf.
First here is a good source of info with links to further discussion in the wiki:
This is rather a story and a correction to another statement I made about the SYS(3050) function in previous posts. I once said you could set this to a low value, like 1-8MB, and foxpro would higher the RAM allocation when needed. That's not the full truth, as it seems. Reading the wiki it states what is surely more correct:
wiki said:
So here I am taking back waht I said.
By the way, just as a side note... Bill Gates never said that quote about the 640KB. You find the full story behind it in wikiquotes. It just fits this topic as title, quite nice.
Let's see what problem I was faced and how I found out SYS(3050) would rescue me: For a few days a certain query on a large table (600MB) ran very slow. There was no error, not even a "not enogh memory to finish the operation" or something like that. Debugging did point us to some false positives, thinking about index corruption and other things. Finally I started up Rushmore optimisation reporting via SYS(3054,12) and it reported back "Not enough memory for optimization".
As said this is not raising an error, it simply leads to letting VFP run the query unoptimized, which is deadly for the performance of a query on a 500MB table.
I could solve that problem by highering memory buffers in the starting prg. There was a setting of it to 1MB. It seems the table has grown just enough to make that 1MB too less caching memory for indexes and highering it solved the problem.
I make one conclusions from this: The memory needed for caching is never raised by VFP on it's own, you need to adjust the setting yourself.
In my case the dbf as said has a size of 600MB, CDX file is about 200MB. As 8MB buffer memoery is sufficient, VFP only buffers partial DBF/CDX. That's also what is to be expected, but it would be good to know what amount of memory you need for rushmore to always work even on 500MB tables. I think what takes a few more bytes in memory is the "bitmap" it builds to determine the recnos it will retrieve That bitmap grows with the size of the table. The number of records is the main figure, I think. That bitmap should need 1bit per record (1 to denote to read the corresponding record, 0 for not needing that record). The 600MB DBF contains roughly 6.2 million records and this means a bitmap initially blank to fill in from rushmore with recnos read from index nodes, needs 6.2 million/8 bytes = 0.78 MB. That would take most of the 1MB buffer memory and if there are further things cached, of course this will make rushmore fail to get that .78 MB for it's rushmore bitmap.
Perhaps that's a good figure to think about, when determining SYS(3050) memory allocation size. Take the largest table you have in number of records and divide that by 8. Take into account joins with other tables, so add their reccount to the figure. Add a few KB to MB for other caching and you have a reasonable figure. I guess if whatever you get that way is lower than a MB, take at least a MB. I think the time is over to make it a percentage of the whole RAM, just think in absolute figures. The buffer you reserve is notfor code, the runtime itself, variables or such things. I have another application, where I move data of a few databases together and there I set the buffer to 500MB, which may not even be needed.
Finally, I want to point to
Christof Wollenhaupt said:
I can't confirm, that Foxpro decides to allocate more memory or creates page files to store a new optimization bitmap, it simply does not and executes a query nonoptimised at least in some cases, so you better watch out for your setting.
Bye, Olaf.
