Re: [Firebird-devel] AutoOptimize

[= m. Th = <th...@va...>]

First, thanks for info, Ann. See my replies bellow.

Ann W. Harrison wrote:
> = m. Th = wrote:
>> 1. That's why I had SetPriority(IDLE_PRIORITY_CLASS, 
>> THREAD_PRIORITY_BELOW_NORMAL) - ....
>>     
>
> 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.
>   
In our particular situation, if the engine design allows perhaps right 
before applying the lock on the page the thread will boost its priority 
to something much higher priority in order to get the required index 
page, release the lock and after this return to it's low priority, 
giving the chance of the other threads to stop it. Besides that, IMHO, 
the chance of 'collision' on exactly same index page between a 'writer' 
and a 'reader' lock, while we have a delayed update procedure is quite 
small. (...remember, M$ 'dare' to do this in sync with /any/ change). 
Also, the other vendors experience shows that the cases in which the 
performance goes down significantly are rare even they collect much more 
statistics than us. And for this situation is good to have a command 
like SET AUTOSTATS INTERVAL <integer>  in which 0 means 'OFF' or 
something similar.

>> 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.
>   
...and the cache is flat? Ie. an index page has the same change to go 
away from cache like a record and/or blob page? Perhaps is better to 
have some priorities like in hybrid disks - a very interesting concept 
to speed things... perhaps is quite easy to adapt it. Basically the 
concept core is to add the 'PageHits' variable to each page and to keep 
in the cache preferentially the pages with more hits because these 
likely will be accessed again.
>> 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.
>   

I see. But, theoretically speaking, isn't worth to 're-think' the query 
right before its run in order to gain the cache advantage? Also, if 
someone prepares the query 'minutes or even hours before' then the 
optimizer chooses the solution based on the statistics at *that* moment 
which can be quite different from now (imagine a batch insert, a huge 
update (like UPDATE BIG_TABLE SET STATUS=1 - which updates a few 
thousands of records) or a Delete which zaps 75% from rows... or 
something else you want... :) ). Or I'm missing something?

> Regards,
>
>
> Ann
>
>   
Thanks a lot for the info,
hth

m. th.

> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
>
>
>