Vault performance / Retrieving repository structure...

crt · Post by **crt** » Wed Jul 19, 2006 2:33 pm

First, some background - my company has been using SGV now for several years and have over 100 licenses. Over that time we've had ongoing complaints from developers about performance, primarily when opening a project in vstudio or starting the Vault client. Our IT team has beefed up the Vault app server and database several times, but the complaints still persist.
Recently there has been a big push to move to TFS, at least partly because of the perceived impact on productivity from the SGV slowness. Personally I'd prefer to stay on SGV due to the cost and complexity of TFS implementation, but if I can't improve the performance problems it will be hard for me to argue against - so I've decided to make a last ditch effort to see if performance can be improved.
I've read the forums extensively over the years and found several posts with similar problems, but the suggested solutions (such as changing the TreeManagerSize setting) don't seem to do the trick.
I've done my own investigation using the Vault debug logs and SQL Profiler and here is what I've found:

-Our primary repository has 64706 files, 6146 folders, and 143840 revisions
-A secondary repository has 16540 files, 2181 folders and 10473 revisions
-The primary slowness appears to occur at the "Retrieving repository structure information from the server..." and corresponds to "GetRepositoryDelta() - db tree diff. " entries in the log files and "spgetrepositorydelta" calls on the database.
-These calls take anywhere from 30 seconds to 3+ minutes
-Over a ~5 hour period since the server restarted to enable debugging, there were almost 40 of these requests
-The vast majority of GetRepositoryTreeDelta requests are using in-memory diff (3000+ requests in the same period)
-From the "Base" versions listed in the "db tree diff" log entries, it seems the Vault does not make any attempt to cache "older" trees (trees that were not current since the last time it was restarted) - almost half the "db tree diff" entries had the same base version (a version that was prior to the current version when the vault server was restarted)
-Doing a complete repository structure download (by deleting the CacheMember_Repository file) takes only 3-4 seconds (for a ~6MB cache file) - compared to the 30-180 seconds to download a "db diff" update

My findings seem to back the complaints of developers - most of the time Vault is fine, but sometimes it mysteriously takes several minutes to open a project or the Vault client. It appears that those cases are when Vault is doing the "db diff" either because the tree has fallen out of cache or Vault was restarted since that tree was current and thus it never gets cached.

The last point in my findings is the most disturbing - if downloading the entire repository structure takes a fraction of the time of doing a "db diff", why even bother doing the "db diff"? Since the source tree for the "db diff" is never cached, future requests for the same source result in even more "db diff" calls.
Is there ANY way to disable "db diff" operations entirely, and send the entire repository tree for cases where a memory diff cannot be performed?"
I'm aware of the "TreeDeltaCompressionThreshold" setting, but I don't think that does quite what I'm talking about (but I haven't tried messing with it).

Please help us get this resolved! I'm really dreading having to move to TFS if it can be avoided.

lbauer · Post by **lbauer** » Wed Jul 19, 2006 3:57 pm

Your analysis is correct -- the more versions out of date the client is, the longer it takes to do the tree diff. Deleting the client-side cache bypasses the diff, as the entire tree is downloaded.

Why even bother doing the "db diff"

We had reasons for doing this when Vault was first developed, but over time, it has become less useful. We are aware of the issues involving spgetrepositorydelta and and are evaluating various ways to optimize client tree updates. Eliminating the tree diff may be an option.

After the release of Vault 3.5 (due this week), our next priority will be to address the tree diff issues in a Vault 3.5 maintenance release.

In the meantime:

Increasing the tree manager size can help, since it causes Vault to store more trees in memory.

What is you current TreeManagerSize setting? How much memory do you have on the Vault server machine?

crt · Post by **crt** » Wed Jul 19, 2006 4:01 pm

Right now the setting is at -1, since other settings didn't have an (obvious) effect.
The vault service machine has 2GB of RAM, and the DB is on a separate machine.

It would be *really* nice if we could find some sort of work-around in the near term to stop the vault service from doing db-diffs - we're going to be making a decision on TFS in the next couple weeks.

Post by **jclausius** » Wed Jul 19, 2006 8:53 pm

I understand. We wanted to see this fixed in Vault 3.5, but there just wasn't enough time. It will be
available in the upcoming months.

In the meantime, a couple of suggestions for a work-around:

A) Is there a database maintenance plan? If not try to:
1) Update sgvault's database statistics daily.
2) Defrag sgvault's database indices weekly.
3) Rebuild sgvault's database indices bi-monthly.

B) Determine how many commits may occur in any given repository for a two or three day period. Then use
this number for TreeManagerSize in vault.config. If you don't know, try a TreeManagerSize value around 250,
and restart Vault (iisreset.exe).

C) If any user is waiting on the DB Diff, they need to either let the DB diff complete or shut down all Vault
clients and then delete their cache files (CacheMember_Repository and CacheMember_LastStructureGetTime).

The trick is to find a balance for the Tree Manager Cache Size so everyone's refresh can find a repository version
(avoiding the DB Diff routine), but small enough to allow the Vault / ASP.Net web service process to still have
available memory for normal operations. Once you have this value, everyone's refresh problems should disappear.

ian_sg · Post by **ian_sg** » Thu Jul 20, 2006 7:03 am

There is a knowledge base post that describes how to set up the maintenance jobs Jeff describes, in case you haven't already seen it.

Post by **jclausius** » Thu Jul 20, 2006 7:53 am

A couple of other thoughts on Vault performance.

A) Disk Subsystms - Both Vault Client and Server make use of a working folder and temp files to do their work. Files of varying sizes
are created and deleted in the %TEMP% directory on the client and the working folder on the server.

This may impact your system in a couple of ways:

Anti-Virus tools. If you have an active virus scanner, try to configure it to ignore the temp and working folders as this will
reduce any bottleneck for disk I/O.
Disk fragmentation. If you do not defrag your disk drives for both Client and Server on a regular basis, you may want to
occasionally run a defrag tool on your systems' drives.

B) SQL Server growth considerations. Check your SQL Server file growth settings. SQL Server can be configured to "grow" your
database based on a percentage of the overall size. For example, if you have a 2000 MB database and a setting of 10% growth,
when the 2000 MB is filled SQL Server will allocate 200 MB for the file. Then when the 2200MB is filled, SQL Server will allocate
an additional 220MB for the file.

For larger sized databases, consider a fixed growth rate based on MB. For example, the above setting could be configured to
always allocate 150MB when the file is filled.

Additionally, ensure the fragmentation on the SQL Server I/O subsystem is minimal so that does not slow down SQL Server from
reading the sgvault database or allocating more space when it is needed.

crt · Post by **crt** » Wed Aug 02, 2006 5:55 pm

FYI - I've found another reason the db diff's tend to occur more often than needed - Vault doesn't save out it's repository cache until it exits.

Looking through our log files, here is a common scenario I see - a user comes in after a weekend or a few days off and fires up the Vault client. They get hit with a db diff because their tree is too out of date.
They then fire up an IDE and open a project - and get hit by the DB diff again because the repository disk cache hasn't been updated and the IDE is using the old cache.

Many of our developers use multiple IDEs at the same time - each one they fire up causes another 3 minute delay to do a db diff unless they close the vault client and write out the updated repository cache first.

Post by **jclausius** » Thu Aug 03, 2006 8:19 am

That is a good point. The local store is not saved until the client is shut down. When running multiple IDEs, this will cause problems, unless one of those caches is retrieved.

The current work-around is still to set TreeManagerSize to a number big enough to store all the trees to prevent the DB repository diff, but small enough to not choke off memory altogether.

We plan on optimizing DB repository diff in Vault 3.5.1.

crt · Post by **crt** » Thu Aug 03, 2006 11:03 am

In addition to optimizing it, please offer an option to disable it entirely (and just send the full tree when a mem-diff can't be done). As mentioned in my post, this would instantly fix my problem since a full tree can be sent in under 6 seconds.

Post by **jclausius** » Thu Aug 03, 2006 11:46 am

Will do.

kasti · Post by **kasti** » Thu Sep 14, 2006 11:46 pm

Did the optimizations of the repository diffs make it into 3.5.1 like mentioned above? Can't see it in the release notes....

Thanks,
Herbert.

Post by **jclausius** » Fri Sep 15, 2006 8:41 am

Note - This enhancement is not included in the current Vault 3.5.1 release - which was quickly released to address a problem with Date Time synchronization and a Visual Studio Dialog problem.

The enhancement will be in a more inclusive Vault 3.5 maintenance release which should be out later this year.

crt · Post by **crt** » Tue Sep 19, 2006 4:04 pm

Please include this as soon as possible! Just the option to disable DB diffs and send the full tree would be a 1000% improvement!

crt · Post by **crt** » Tue Nov 07, 2006 2:42 pm

Well, it looks like you've officially lost us as a customer over this. We finally got approval to move to TFS, and even though it is far more expensive and we've been otherwise happy with Vault, our engineers just can't stop complaining about the performance. I'm really disappointed that 5 months after providing a detailed report of a critical performance issue that appears to be well known affecting many other users as well nothing has been done.

lbauer · Post by **lbauer** » Tue Nov 07, 2006 3:39 pm

We are planning to include this performance enhancement in a maintenance release to Vault 3.5, either by the end of this year or early next year. As with any major change, it takes a significant amount of testing to ensure that it works as intended and does not introduce new problems. We understand the need for a quick solution, but we must be sure our implementation is done correctly, rather than quickly.

In the meantime, for users experiencing similar issues:

There are other ways to improve Vault peformance, such as changing the Tree Manager Size as mentioned earlier in this thread. This setting is in the Vault.config file in the Vault Service directory. Edit this line, replacing the -1 with the number of trees for Vault to store in memory.

<TreeManagerSize>-1</TreeManagerSize>

We'd suggest starting with 200 and monitoring memory.

For more information on Tree Manager Size and optimizing Vault, see this KB article:

http://support.sourcegear.com/viewtopic.php?t=3714

To crt: we're always sorry to lose a customer. If you'd like discuss this further, email Linda at SourceGear.com. Perhaps we can persuade you to stay with Vault. It would be unfortunate if, in the time it took to implement TFS, Vault already had the changes you needed.