[Firebird-devel] AutoOptimize

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

Hi,

Investigating a little bit the posiblity of updating the index
selectivities on the fly I have a test database with many table, one of
them is a table with ~150.000 contacts and a pretty large structure. I
did also an index on the NAME VARCHAR(100) field. Running the Data
Generator on this table in order to generate another 100k records
(commit at 10k) the insert average was about 440 rows/sec. (Test bed:
AMD Athlon 64 XP @ 3 GHz, 1GB RAM, Win XP Pro, SATA HDD, Fb 2.0 SS,
Datagenerator UI 0.35 - http://datagenerator.sourceforge.net - powered
by Zeos). Db: ODS 11.0 - Page size: 16k, Forced writes, pages allocated:
5378. When I run my little app (freely available, if someone wants)
which has the code bellow (perhaps the SourceForge list engine will remove the
indentation...) the insert speed remains constant and all the 108
indexes from the db were updated in 110 miliseconds. It was quite clear
how my little app stalled at some steps leaving the Fb and the
Datagenerator to do their work. In fact the most time/resource consuming
from my app is the connect part and the retrieving of the index names.
Now here is the code and at the end some comments...

begin
   ibdMain.Username:=edtUser.Text;
   ibdMain.Password:=edtPwd.Text;
   ibdMain.Connect; //just connect to the database. (this is not needed in the server thread)

   SetPriority(IDLE_PRIORITY_CLASS, THREAD_PRIORITY_BELOW_NORMAL); //this is the 'sensible' thing!

   ibdMain.StartTransaction; //perhaps is closer to reality the 'AutoCommit' mode...

   //Fetch all the index names - also this part isn't needed.
   ibtMain.SQL.Text:='SELECT I.RDB$INDEX_NAME FROM RDB$INDICES I WHERE I.RDB$INDEX_INACTIVE=0 AND RDB$UNIQUE_FLAG=0'; 
//do only for active indexes and not unique ones
   ibtMain.Open;

   //speedups
   fdIdxName:=ibtMain.Fields[0];

   t:=GetTickCount; //this is for benchmarking only...
   with ibtMain do //begin dataset scan
   begin
     DisableControls;
     First;
     while not Eof do
     begin
       try
         cIdxName:=fdIdxName.AsString; //speedup
         Log('Updating statistics for '+cIdxName);
         ibtMain.ExecuteImmediate('SET STATISTICS INDEX '+cIdxName, nil);

       finally //we need this because the index could be deleted until we reach here...
       end; //do nothing. We just 'eat' the exception

       Next;
     end;

     Close; //We keep OAT, OIT aso. for a while but this is only a demo here...
   end; //dataset scan

   ibdMain.Commit;

   Log('Done in '+IntToStr(GetTickCount-t)+' msecs');
   ibdMain.Disconnect; //cleanup
end;

Another test which I did was on the same table now having 345,362 recs while inserting another 100k recs I ran the app 
again. All the indexes were updated in 219 msecs. The inserting speed remained the same. It seems that the engine scales 
pretty well. Yet another test was to open 20 local 'clients' on the same db which did a SELECT * FROM CONTACTS WHERE 
NAME CONTAINING 'JOHN' (which implies a full scan - now the table contains 445,362 recs) at different time intervals 
between (these time intervals were close one to each other - some seconds). The queries returned the result in approx. 
43 secs even if run the index optimizing routine during the stress test even if not. The optimizing routine completed in 
the same time: 219 msecs.

IMHO, if you want to implement this in the engine perhaps this is a
way... (is rather a draft here...)

1. Add in firebird.conf ChangesToOptimize (=0 disabled) and
TimeToOptimize (see UnflushedWrites engine). (Which is the number of Changes to reach and the amount of time from the 
last change to pass in order to start the automatic optimization)

2. Add in the idx structure an int called, le't say 'modifications'
which at creation time is 0 and last_optimize_time and a thread
doOptimize. Set to this the IDLE_PRIORITY_CLASS,
THREAD_PRIORITY_BELOW_NORMAL
3. In IDX_modifiy, if everything is ok and the index isn't unique then
using a similar code with that from UnflushedWrites (see CCH_flush from
cch.cpp), or use timers for the delay or WM_TIMER (on Windows). dunno for Linux.
     <execute the doOptimize thread which will cal inside the
IDX_statistics with the appropiate values >

Any comments?

hth,

m. th.