Menu
▾
▴
Re: [Firebird-devel] AutoOptimize
|
From: = m. Th = <th...@va...> - 2007-01-02 16:54:09
|
Arno Brinkman wrote: > Hi, > > >> //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 >> > > Why only not unique ones? > Sorry, Arno, but the unique indexes doesn't have constant selectivity? Or it seems that I'm missing something... of course, if you plan to gather more statistics, a thing which I wish that you'll do, then, of course also the statistics of these indexes will be updated. > 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 > >> > > It's not interesting to track modifications of index. > All indexes which belong to the same table should update the statistics simultaneously else the statistics become > "unbalanced" compared to each other. > Yes, but the "separate" indexes from the same table doesn't update at (approximative) same moment in time? > Tracking modifications (U/D/I) of a table (or data-pages updates) would be enough. > Using that number to calculate the percent that has changed of the table x should prepare the "update statistics batch > for table x" in the scheduler. > > For "D" and "I" - agree :).For "U" how can we track when the selectivity should be /really/ updated? (see the Microsoft papers from Laco's message and from mine). Let's have a common usage case: In an ERP we have an ACC table with the following structure (*all* the fields are indexed) ACCID INTEGER, /* PK */ NAME VARCHAR(70), /* The account name */ DB01 DOUBLE PRECISION, DB02 DOUBLE PRECISION, ... DB12 DOUBLE PRECISION, CR01 DOUBLE PRECISION, CR02 DOUBLE PRECISION, ... CR12 DOUBLE PRECISION, etc. On daily basis, the debit and credit fields (which are 24) are updated frequently on different records. So, when the max_thresold will hit (let's say) because of DB01 (because we are in January) then all 25 indexes will be recalculated (the PK not because is unique, CIIW, the NAME and the other 24 numeric fields). The main overhead IMHO comes from the field NAME (due to fact that is very large compared with others - 210 bytes on UNICODE) which is a very static index in day-by-day usage. Hence a rather 'small' efficiency, IMHO. But, of course, you know better than me the engine internals, so if the things became complex / worse feel free to do as you wish. Anyway, my small benchmark shows that updating the statistics isn't a performance hit. Also, some 'reading' for you :) : Oracle: http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/burleson_cbo_pt1.html http://www.oracle.com/technology/pub/articles/lewis_cbo.html http://www.dba-oracle.com/art_otn_cbo_p1.htm (also see the other parts) DB2: http://www-128.ibm.com/developerworks/db2/library/techarticle/lohman/0107lohman.pdf http://www-128.ibm.com/developerworks/db2/library/techarticle/0210mullins/0210mullins.html http://www-128.ibm.com/developerworks/db2/library/techarticle/0301mullins/0301mullins.html MS SQL (- see my message for Laco) PostgreSQL: http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10119&cNode=0T1L6L (for beginning...) http://www.postgresql.org/docs/8.1/interactive/planner-stats-details.html http://www.postgresql.org/docs/8.1/interactive/planner-stats.html http://www.postgresql.org/docs/8.1/interactive/view-pg-stats.html Perhaps is good to have a look also at http://neilconway.org/talks/optimizer/optimizer.pdf MySQL: (not so good info...) http://dev.mysql.com/doc/refman/5.0/en/myisam-index-statistics.html http://dev.mysql.com/doc/refman/5.0/en/myisam-key-cache.html http://www.phpfreaks.com/manuals/mysql/optimization.html http://www.mysqlperformanceblog.com/ http://bugs.mysql.com/bug.php?id=19301 :-) http://bugs.mysql.com/bug.php?id=20932 :-)) And, generally any link from these pages... Also have a look at http://tweakers.net/reviews/657/6 Happy reading! hth, m. th. > Regards, > Arno Brinkman > ABVisie > > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > General database developer support: > http://www.databasedevelopmentforum.com > > Firebird open source database (based on IB-OE) with many SQL-99 features: > http://www.firebirdsql.org > http://www.firebirdsql.info > > Support list for Interbase and Firebird users: > fir...@ya... > > Nederlandse firebird nieuwsgroep: > news://newsgroups.firebirdsql.info > > > ------------------------------------------------------------------------- > 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 > > . > > |
