Re: [Firebird-devel] AutoOptimize

[Ann W. H. <aha...@ib...>]

= m. Th = wrote:

>>   
> 1. That's why I had SetPriority(IDLE_PRIORITY_CLASS, 
> THREAD_PRIORITY_BELOW_NORMAL) - in order to immediately back-off when a 
> load is present. And I saw this with my eyes. This is by far the most 
> appropriate approach IMHO ...and not only IMHO ( :) )because I spoke 
> with many other programmers in newsgroups, searching the net, read some 
> books aso. until I reached to conclusion (which I must admit is the 
> common one) that is better to leave the OS thread scheduler to do the 
> work. Knows better.

Playing with thread priorities in Firebird is playing with fire because
any thread can get a lock on a page in cache.  If a low priority thread
has a read lock on a page, it will block high priority threads that want
to change that page.

> 
> 2. What do you mean "all index pages will be *fetched*"? You don't cache 
> the indexes in memory ? (Now, my heart beats because of fear of 
> possibility to answer "no" here...) Of course, you mean "fetched" from 
> the page cache... :)

No, he means fetched from disk.  We don't hold unreferenced pages in 
cache.  Normal usage keeps the upper levels of indexes on active tables
in cache.   Leaf level pages may or may not be in cache, depending on
whether values from those pages have been referenced recently.

> 3. Also, about caching, you, of course, take in consideration in cost 
> calculation the fact that (some) pages is already in cache. IOW, if we 
> have two tables one with 10000 rows and the other with 50000 but the 
> latter one is already in cache then you go with it, isn't it? Or you 
> take all the tables/pages the same nevertheless if they are cached or not?
> 

No.  Request optimization is done before the query is run - possibly
minutes or even hours before it is run, so the optimizer has no idea
what pages will be in cache when the query actually runs.  And the same
prepared and optimized query can be run repeatedly, which also affects
what will be found in cache.


Regards,


Ann