Slow database performance

A database with slow performance can be caused by a lot of reasons. My last two cases were probably due to readers fields and deletion stubs.

If you have a slow view with a lot of documents in it, check if the documents have readers fields, it's a common cause and there are several suggested actions to take. If the user accessing the view only has access to a small part of all the documents it will render very slowly. A page (memory unit) will be read into memory consisting of approximately 300 documents. When scrolling down the view it will be recalculated. If you have access to all documents it will render pretty much as usual.

The other day I was troubleshooting a database which was slow to open with slow views. It had a view that it took about 2 minutes to open. The database had 50.000 documents and 129 views and this and several other views used readers fields so I figured that would be the problem.
I copied the database with all documents and accessed it with the same user and they behaved very differently. The same view that took about 2 minutes to open in the original database was opened in about 5 seconds in the copy. The only difference was the size of the database. The original was about 990MB and the copy started at 108MB.

So I figured that would be due to view indexes. An it probabaly were to some point but together with the view index it turned out that deletion stubs were part of it.

Five days a week, on each day, approximately 40.000 documents was deleted an new ones were created from an excel file (this was an old solution to update them). The purge interval was set to 120 days. So this means that there were about 40.000 * 88 (only weekdays) = 3.520.000 deletion stubs in the database. Since a deletion stub is about 100 bytes (at least it was in version 6) it means that the they used approximately 352MB of the space and the database consisted of a minimum of 352MB of deletion stubs although the count is more impressive.

The number of deletion stubs probably together with a corrupt view index caused the database to perform very slow. Fixup and Updall took a very long time as well and had to be aborted.

A copy of the database solved the problem but a long-term solution is of course to recode the agent to update the documents instead of replacing them and to make the purge interval more frequent.


Technorati tags:
Readers fields,Deletion stubs,Slow database performance, Domino, Lotus Notes