We have a database which is about 8GB with 400K documents. This database has replicas on about 15 servers all over the world and replicates every 15 minutes into and out of the UK Server. The view indexes size is about 2GB.
Now the problem:
When we open some of the views from a notes client, this makes the whole database slow or unusable until that view is updated and opened in the UI. We have observed that the indexer task kicks off at that time and takes a very long time to update that view. This happens only on some views and not all of them. We did a small test as follows:
View 1 - All docs(400K). We purged the view index and then opened the view from a notes client. It took sometime to open the view which is understandable, but did not clog the database. We are able to use the database normally. We didn’t see the indexer task updating this view from the administrator.
View 2 - with a selection criteria which shouldn’t bring no morethan 100 docs. This time we didn’t purge the index because the view index has already been discarded (inactive for 45 days). We tried to open this view from a notes client. It took a long time again to open this view and also clogged the database until the view has opened in the UI. This time we can see the indexer task updating this view.
What we don’t understand is why the database runs normally in the first case and why the database comes to a standstill in the second case ? We read a lot of posts on this forum relating to views and indexes and also read a lot of technotes relating to how the indexing works, but are unable to solve this problem.
Any thoughts on this are highly appreciated. If you need more information please let me know.
Use log_update=1 to track your indexer activity a bit more closely. Let it run for a day and then collate the results in the log.nsf Misc Events view for that day, just the lines pertaining to the indexer. It’s a pain to do it, but it’ll give you a much better sense of what the indexer task is doing.
If a view is being updated by the server, my experience tells me that regardless of whether the view is big or small, you’ll feel the same pain WHILE THE INDEXER UPDATES. Of course, what is critical here is how long it takes (see #1).
If a view is being updated locally on the desktop, it’s an entirely different beast, and it’s unclear to me from your description above whether this may be the case.
In some cases, such as ReaderNames, the indexer isn’t necessarily the one holding things up because there is a lot of other work being performed. You can do one of those CLIENT_CLOCK debugs with your notes client to get a sense of where the delays are. This is also a bit of a hassle, unless you’ve done it a bunch of times, but it’s free and not too hard to figure out.
I’d be interested in whether your test is truly as clean as you think it is. No disrespect at all, it’s just hard in a production environment to strip out other variables. For example, replication every 15 minutes is definitely something that stands out for me here. The update task is going to try to kick off about 1 minute later. I just wonder if that could have bumped into your testing.
Subject: RE: Opening a view cloggs the whole database
Raphael
Use log_update=1 to track your indexer activity a bit more closely. Let it run for a day and then collate the results in the log.nsf Misc Events view for that day, just the lines pertaining to the indexer. It’s a pain to do it, but it’ll give you a much better sense of what the indexer task is doing.
=> We shall try and run this log_update=1 asap.
If a view is being updated by the server, my experience tells me that regardless of whether the view is big or small, you’ll feel the same pain WHILE THE INDEXER UPDATES. Of course, what is critical here is how long it takes (see #1).
=> We’re not bothered about the server updates because the indexer runs only 3 times a day between 8am and 5pm.
If a view is being updated locally on the desktop, it’s an entirely different beast, and it’s unclear to me from your description above whether this may be the case.
=> I think this is where we’re a bit confused. If a view is opened in a client, does this starts the indexer on the server ? The problem we’re having is not by the scheduled indexer task. The problem is when we try and open a view from a notes client it cloggs up the whole database and at this point we found the indexer task running on that particular view.
In some cases, such as ReaderNames, the indexer isn’t necessarily the one holding things up because there is a lot of other work being performed. You can do one of those CLIENT_CLOCK debugs with your notes client to get a sense of where the delays are. This is also a bit of a hassle, unless you’ve done it a bunch of times, but it’s free and not too hard to figure out.
=> We had tweaked the performace of this database recently. We followed all the performance enhancement tips for this database including the database properties, fine tuning the server tasks(indexer) etc. We came to a point where we thought the performance is ok for a few weeks and then suddenly this problem has started. Also we don’t have any readers/authors fields or time/date formulas or @username etc in any of the views.
I’d be interested in whether your test is truly as clean as you think it is. No disrespect at all, it’s just hard in a production environment to strip out other variables. For example, replication every 15 minutes is definitely something that stands out for me here. The update task is going to try to kick off about 1 minute later. I just wonder if that could have bumped into your testing.
=>Even though the replication is every 15 mins we had a program document so that the indexer runs only 3 times during the day between 8am and 5pm on this database. When we did this test we’re pretty sure the scheduled indexer is not running. Also we did this test several times and each time when we try to open a view (for which index has been discarded - inactive after 45 days) we’re able to reproduce this problem.
Subject: RE: Opening a view cloggs the whole database
Sai,
Couple more suggestions:
Confirm that View1 and View2 are both shared views, not private or ‘shared, private on first use’ views.
Before running your test you said you purged view1. I’m not sure what that means. If you let statlog run, would it have reported a 0kb size for that view at that time? If so, then the view had to be rebuilt, just as you said view2 was rebuilt.
“And then suddenly this problem started.” It might be worth thinking about when exactly the problem started. Was it right after a design or data or software update? Right after new users came on board?
Subject: RE: Opening a view cloggs the whole database
Raphael
Confirm that View1 and View2 are both shared views, not private or ‘shared, private on first use’ views.
=> Yes both View1 and View2 are shared views.
Before running your test you said you purged view1. I’m not sure what that means. If you let statlog run, would it have reported a 0kb size for that view at that time? If so, then the view had to be rebuilt, just as you said view2 was rebuilt.
=> Yes We had purged the view index for View1. Yes the view index size was 0KB after purging. When we opened this view from a notes client it took sometime for the view to open but the database didn’t become slow and was usable and also we didn’t see the indexer task updating this view from the server tasks.
“And then suddenly this problem started.” It might be worth thinking about when exactly the problem started. Was it right after a design or data or software update? Right after new users came on board?
=> It’s quite normal for this database to get design updates atleast once in every 10 days or so. What I mean by “And then suddenly this problem started” was, after tweaking the database to enhance performance it behaved well for a couple of weeks and this problem has occured. Nothing much has changed from that time.
Subject: RE: Opening a view cloggs the whole database
Sai,
Hard to troubleshoot this way … but if it were me then:
I suspect that whether you saw the indexer working at the console is a red herring. It’s niggling something in the back of my head but I can’t recall. At any rate, you can pursue that by using debug_nif=1 or some other debug params, but it can be a bit of a rat hole, to mix my metaphors.
I would focus on performance in general, the results of that log_update=1, etc.
Subject: RE: Opening a view cloggs the whole database
Raphael
Thank you very much for your help. We shall try and see if we can find any solution for this problem. I shall keep posting if we find anything interesting.