Re: [Firebird-devel] AutoOptimize

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

Ann W. Harrison wrote:
> = m. Th = wrote:
>   
>>   
>> 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. 
>>     
>
> Changing thread priority twice for every page lock sounds very
> expensive.  The read/write locking of pages is handled by a general
> cache manager that has no idea what use the thread is going to make
> of the page.  There's no way to read a page unless you've got a read
> lock on it, so the lock can't be released immediately.  The thread
> that fixes index statistics will spend most of its time reading
> index pages, so under your scheme, it will spend most of its time
> at elevated priority.
>   
Yes, of course. But between the high priority intervals we have the idle 
intervals in which the thread is stopped if we have load. It's a pretty 
common scheme. Both M$ Desktop Search and Google's use this. But let's 
back at 'our sheep' :) . Remember my first post. Some things from there: 
updating all 83 indexes from a simple Delphi took 219 milliseconds. The 
biggest table had ~450,000 recs the structure is wide and the biggest 
index on that table is on a VARCHAR(100) (ie. 200 bytes - because of 
WIN1253 charset). This time was achieved on a 'normal' workstation with 
many things on it (in fact, my computer) and the time remained constant 
even the machine was idle, ran a 100.000 recs insert on that table, did 
a SELECT * FROM CONTACTS WHERE NAME CONTAINING 'JOHN' (which implies a 
full-scan, of course) which returned all rows in 2,5 secs, *even* if I 
ran so many 'clients' that the above query returned in ~ 43 secs. (which 
means that the machine was literally crushed by the queries...). But 43 
secs was the time also if my small 'update statistics' app was not run! 
And also, remember that only *my* Delphi thread was low priority, the 
'UPDATE STATISTICS '+cIndexName[i] was sent over XNET and run inside to 
engine at normal priority. This is the reality, not the theory. Test it, 
if you want. I can send you the app. Or post it somewhere (perhaps in 
firebird-support 'files' area). And I saw exactly the phenomenon from 
above on my screen (because after optimizing an index I write a line in 
a memo control - see my first message). IOW, I see on very heavy load 
*only* some 'steps' like:

Updating statistics for Index #1
Updating statistics for Index #2
Updating statistics for Index #3
Updating statistics for Index #4
<...thread stalled for a while...>
Updating statistics for Index #5
Updating statistics for Index #6
<...another stop...>
Updating statistics for Index #7
...aso...

In the other cases all the indexes were updated from one shoot.
But let's make the things worse, if you want. We have 219 milliseconds 
on *any* load for 83 indexes. Some of them for a half of million table. 
In my proposed implementation we don't update allt the indexes at once, 
but only the ones which is needed. In the worst case all the indexes of 
a table. But let's suppose that we have 219 milliseconds for one table 
only. And let's suppose that on a heavy load this will add. Do you think 
that some human will observe a difference between 43 secs and 43,219 
secs? IMHO, I'm a little bit afraid that we tend (at least I tend) to 
discuss the things more theoretically and to cut down a feature because 
of some extreme cases which in a real world will be very useful and will 
grow our user base. And, personally, if someone will post in the 
'support' list that he will have problems with heavy load on its machine 
I will suggest to him (as seen already from the others, much more 
experienced than me) that either he must upgrade its hardware, either he 
has a configuration problem in much more crucial settings (small page 
cache, small MemSortUpperLimit etc.), either he have a problem with its 
application (long running trx, deadlocks aso.)
>   
>> 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). 
>>     
>
> I don't know what uSoft is doing inside SQLServer.  If you have an
> example from an open source project, maybe we can learn from them.
>   
So, it seems that we need to do a hunt...

> A thread that reads every leaf page of every index will certainly
> run into threads that are adding and deleting index nodes from time
> to time.  If it can't get enough priority to release its locks, it
> will eventually tie up the system.
>
>   
Seeing is believing. See my answer above. Also, IMHO, it's worth to give 
a try.
>> Also, the other vendors experience shows ...
>>     
>
> I'm not arguing that we shouldn't find a way to set statistics
> automatically, just that redesigning the whole I/O system of
> Firebird to accommodate the feature seems a little extreme.
>   
100% agreed. If this is the cost, in your place I would not 'pay' it. :)
> I'm also worried about housekeeping features that run at
> random times and tie up production.
>
>   
This is _not_ at random times. Is a quite known technique from real-time 
incremental searches. Start your background job if exists <n> 
milliseconds in which the high priority discrete jobs doesn't happen. If 
these jobs happen after your background job has started, back-off - stop 
execution. Also you can see it in the Windows Explorer. Open your local 
disk and also expand some folders from the tree (assuming that you see 
the folders tree in the left pane). Now choose the local disk. Press and 
hold the DnArrow key. The selection will move very fast. This because 
there exists a delay (400ms according to Microsoft - it is somewhere in 
registry, I don't remember where) which waits for you to finish your 
'arrow play'.  In a way, you implemented it with MaxUnflushedWritesTime 
(IIRC) in firebird.conf. If you want, I write a sample for you to show 
the concept.

> For example, we could make setting statistics on indexes part
> of sweep, or an optional feature of sweep, or another function
> that can be invoked by itself (as sweep can) or as part of
> sweep.
>
>   
Yes, this is simpler and also I proposed that this (ie. a number of 
changes) will be the second trigger for auto-update to start. But far 
better, IMHO, is to be separated from sweep because this is needed 
often, is quicker and doesn't consume so many resources as the sweep. 
Summing up:

1. Start the auto-update thread if (number of changes > <MAX_THRESOLD>) 
and (no changes made in <MAX_MSECS>)
2. Thread will run in low priority. The critical parts (ie. locks) will 
low in high priority. This allows that thread can be 'intrerrupted' by 
OS thread scheduler between the 'quick steps' if is needed.

hth,

m. th.