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,
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
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
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
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
Will do. Thanks.
Paul
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
basex-talk@mailman.uni-konstanz.de
-
Christian Grün -
Paul Swennenhuis