Do you know what your indexes are doing in the dark?
by Volker Weber
Domino has an interesting bug that you may not have heard of. And it may still be affecting the performance of your server.
With Domino R5 Lotus introduced columns that let you sort by a certain field "on demand". You click a column header and all documents are sorted by that column. Of course the client does not sort on demand. Instead there is an additional view index for each of those columns. The more view indexes you have however, the bigger your database grows. Here is the bug: In case you ever had any of those dynamic sort columns, then Domino is still maintaining the index. Even after you removed the sort from the design of your database. It does not help to compact the database, because Domino will still rebuild all of those indexes.
Fortunately there is a tool (english/german) that will diagnose this problem. The free version lets you search a particular database that looks suspiciously large (or slow). In case you find an issue, you can buy a license that will let you scan all databases on a server and permanently remove the broken indexes from the database.
Comments
This problem is fixed in 7.0 and can easily be verified by performing the following steps:
- In the design view list (*not* while the view is open) select Design\Design properties to get the infobox for the currently selected view.
- Click the 2nd tab to see all the items for that view.
- You'll find at least one '$Collation' item in the list. If you see additional '$Collation1', '$Collation2' fields, then you have sortable columns, too.
- If you uncheck all "Click on column header to sort..." options for all columns, save the view and inspect the view design via the infobox, there should be NO '$Collation' items anymore, only the basic '$Collation' item.
Re-saving view using a R7 designer client as described above will properly purge any obsolete collations from the view design.
A fix for an upcoming release R6.5x is being considered but currently not available.
I understand that you need to remove all your "on demand" sortable columns to clean this up. If you leave one of them sortable then Designer does not clean up the unwanted ones. Then you have to add those you want. Is this correct?
In any case, the tool I mention does a much easier job cleaning up your server. It would be nice, if compact could do the same.
No, you do NOT have to remove all your "on demand" sortable columns to clean this up. And yes, I know, if there were a designer client for the mac than you would've tried it out yourself yada yada yada ;-)
I am sure, somebody else will. ;-)
If there were a Desinger client for the mac.....yada, yada, yada.. ;-)
To Thomas:
Unfortunately this is only partially fixed in Notes 7. Notes 7 only removes $CollationX items up to $Collation32, higher numbers stay in the design element (we have seen items up to $Collation66 !!).
Additionally Notes 7 does not remove "orphaned" $CollationX items, e.g. if a view contains only $collation9, but not $collation1 ... $collation8, this item is not removed.
And Notes 7 can produce these "orphaned" items too, here are the steps to reproduce:
Create a view with 2 colums
Set both columns to "Click to sort -> Both"
Save the view
the view now contains $Collation1 ... $Collation4 ->thats ok
Reopen the view in Designer
remove the "click to sort" property from the _first_ column
save the view again
the view now contains $Collation1 ... $Collation4 ->oops, what's that ?
Reopen the view in Designer
remove the "click to sort" property from the second column
save the view again
the view now contains $Collation3 ... $Collation4 ->oops, again ... now we have "orphaned" $CollationX, that are never deleted by any designer.
BTW: The problem is present in Notes 5 too, however with a slightly different behaviour.
Well here is a simple solution for that.
Please IBM buy they rights for that tool and give it away to all your customers.
Andreas,
thanks, very helpful. I've re-opened the bug report (JPKR69TRTC) and appended your comments.
@Thomas Schulte: until they do (or rather: if they don't) you might as well remove the orphaned fields yourself using a LotusScript agent...
@Lothar Mueller: you should be carefull doing that from lotusscript. First you have to find the fields to delete (there may be columns intentionally sorted this way).
It is also highly recomended to purge all indices of a view before doing that, otherwise there may be orphaned space in the NSF;other issues arise if working w/ LS alone; we tried a pure LS solution and had some strange effects, so we went for a clean C solution.
agreed!
Hallo,
wieviel bringt denn dieser Vorgang bei einer 3GB großen und relativ trägen Datenbank mit x Views? Hab mal die kostenlose Variante drüberlaufen lassen, im Analyse-Dokumente steht nun folgendes:
Size (MB) Before Repair: Total 200,39
Size (MB) After Repair: 0,00
Bad Indices: 104
Good Indices: 130
Könnte dieses Vorgehen einen signifikanten Performancesprung in der DB bringen?
To Benjamin Schinhammer:
Genaues kann ich ohne weitere Angaben natürlich nicht sagen. Da , wenn sich Ihre Angaben auf alle Views beziehen, beträgt die Gesamtgröße der aktiven Views 200MB. Da ungefähr die Hälfte der Indizes defekt ist, wird sich die Viewgröße in etwa halbieren, ebenso wird sich in Summe die Reindexzeit halbieren. Ob das die Anwendung spürbar beschleunigt, hängt natürlich von vielen anderen Faktoren ab (Design, Anzahl & Größe der Dokumente, Zugriffe auf Views, etc).
Bei einer Gesamtviewgröße von 200MB bei 3GB DB-Größe ist der Performancegewinn wahrscheinlich nicht sehr groß, aber einen Versuch ist es wert. Eine Performanceanalyse der Datenbank könnte ev. noch weiter helfen.
Bei weiteren Fragen bitte ich Sie sich an uns per Mail (support@holistic-net.de ) zu wenden.
