Keeping a database optimized
What is the best way to keep the indexes of a database up-to-date? I have created a database with the option UPDINDEX set to ON (and db-info tells me that it is indeed switched on)
and I have also created text and attribute indexes.
Even so, after several insertions (using XQUERY insert node) I see that the UPTODATE flag is no longer checked and I have to run OPTIMIZE ALL to get back my performance.
a) Is there a way to keep UPTODATE checked under all circumstances? And if not
b) Is is advisable to run OPTIMIZE ALL after every insertion? (I guess not)
Hi Paul,
Quoted text · Paul Swennenhuis · 2 lines
> Even so, after several insertions (using XQUERY insert node) I see that the > UPTODATE flag is no longer checked
Basically, this is fine, because only the value index structures will be updated (excluding database statistics, meta data and the fulltext index).
Quoted text · Paul Swennenhuis · 2 lines
> and I have to run OPTIMIZE ALL to get > back my performance.
Maybe you can compare the query plans before and after an update and see what has changed?
Quoted text · Paul Swennenhuis · 3 lines
> a) Is there a way to keep UPTODATE checked under all circumstances? > And if not > b) Is is advisable to run OPTIMIZE ALL after every insertion? (I guess not)
You can set AUTOOPTIMIZE to true [1]. This works fine as long as your database doesn't grow too large. In every case, it's faster than OPTIMIZE ALL, because only those data structures will be reorganized that have become outdated.
Besides that, using OPTIMIZE (without ALL) may already be sufficient.
Hope this helps, Christian
Thanks Christian,
AUTOOPTIMIZE seems to work quite well.
So what database size do you consider to be "too large"?
0.5 Gb? 1 Gb? Or ... ?
Paul
Quoted text · Christian Grün · 27 lines
> Hi Paul, > >> Even so, after several insertions (using XQUERY insert node) I see that the >> UPTODATE flag is no longer checked > Basically, this is fine, because only the value index structures will > be updated (excluding database statistics, meta data and the fulltext > index). > >> and I have to run OPTIMIZE ALL to get >> back my performance. > Maybe you can compare the query plans before and after an update and > see what has changed? > >> a) Is there a way to keep UPTODATE checked under all circumstances? >> And if not >> b) Is is advisable to run OPTIMIZE ALL after every insertion? (I guess not) > You can set AUTOOPTIMIZE to true [1]. This works fine as long as your > database doesn't grow too large. In every case, it's faster than > OPTIMIZE ALL, because only those data structures will be reorganized > that have become outdated. > > Besides that, using OPTIMIZE (without ALL) may already be sufficient. > > Hope this helps, > Christian > > [1] http://docs.basex.org/wiki/Options#AUTOOPTIMIZE
Quoted text · Paul Swennenhuis · 2 lines
> So what database size do you consider to be "too large"? > 0.5 Gb? 1 Gb? Or ... ?
Hm… It really depends on your use case (1 ms can be too slow for the one use case, 1 second can be fast enough for another). Maybe you can simply do some benchmarking and see how it works out for you?
Quoted text · Paul Swennenhuis · 38 lines
> > Paul > > >> Hi Paul, >> >>> Even so, after several insertions (using XQUERY insert node) I see that >>> the >>> UPTODATE flag is no longer checked >> >> Basically, this is fine, because only the value index structures will >> be updated (excluding database statistics, meta data and the fulltext >> index). >> >>> and I have to run OPTIMIZE ALL to get >>> back my performance. >> >> Maybe you can compare the query plans before and after an update and >> see what has changed? >> >>> a) Is there a way to keep UPTODATE checked under all circumstances? >>> And if not >>> b) Is is advisable to run OPTIMIZE ALL after every insertion? (I guess >>> not) >> >> You can set AUTOOPTIMIZE to true [1]. This works fine as long as your >> database doesn't grow too large. In every case, it's faster than >> OPTIMIZE ALL, because only those data structures will be reorganized >> that have become outdated. >> >> Besides that, using OPTIMIZE (without ALL) may already be sufficient. >> >> Hope this helps, >> Christian >> >> [1] http://docs.basex.org/wiki/Options#AUTOOPTIMIZE > >
Will do. Thanks.
Paul
Quoted text · Christian Grün · 40 lines
>> So what database size do you consider to be "too large"? >> 0.5 Gb? 1 Gb? Or ... ? > Hm… It really depends on your use case (1 ms can be too slow for the > one use case, 1 second can be fast enough for another). Maybe you can > simply do some benchmarking and see how it works out for you? > > >> Paul >> >> >>> Hi Paul, >>> >>>> Even so, after several insertions (using XQUERY insert node) I see that >>>> the >>>> UPTODATE flag is no longer checked >>> Basically, this is fine, because only the value index structures will >>> be updated (excluding database statistics, meta data and the fulltext >>> index). >>> >>>> and I have to run OPTIMIZE ALL to get >>>> back my performance. >>> Maybe you can compare the query plans before and after an update and >>> see what has changed? >>> >>>> a) Is there a way to keep UPTODATE checked under all circumstances? >>>> And if not >>>> b) Is is advisable to run OPTIMIZE ALL after every insertion? (I guess >>>> not) >>> You can set AUTOOPTIMIZE to true [1]. This works fine as long as your >>> database doesn't grow too large. In every case, it's faster than >>> OPTIMIZE ALL, because only those data structures will be reorganized >>> that have become outdated. >>> >>> Besides that, using OPTIMIZE (without ALL) may already be sufficient. >>> >>> Hope this helps, >>> Christian >>> >>> [1] http://docs.basex.org/wiki/Options#AUTOOPTIMIZE >>
Written for BaseX 8.3. BaseX may have changed since: please check the current documentation.