Sloooooow export from the repository

[sanin]

I am experiencing export performance that I find hard to believe would be normal: I started the export operation 4 hours ago and so far only 420 out of 21000 transactions have been RETRIEVED (see attached screenshot).

I am running 3.5.0.4741

Something must be wrong. Any ideas?

[lbauer]

First, I'd suggest upgrading server and clients to Vault 3.5.1. We did some optimization of the Export\Import tool.

Export will be dependent on the type of hardware for the Vault Server and SQL Server. For instance, our own testing of folder export yielded about 5-6 seconds per transaction. This was on a 3.2 GHz P4, 4 GB RAM, striped RAID disk IO.


Some considerations:

Make sure you database is optimized:

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

If other users are using Vault at the same time, it can slow things down.
Try disabling any Shadow Folders, as these act as clients that use system resources.

If other apps are competing for the machine's resources or if it's not a particularly fast machine, that can slow things down as well.

Did you notice any lags in the export -- for instance, in syncing up to the database, etc. Check the Export log (VaultFolderExportImport.txt in the same directory as the ExportImport tool) for transactions that seem to take longer than others, or any error messages.

Also check the Vault Server log file (sgvault.log in %windir%\temp\sgvault) to see if there are error messages related to the export.

[sanin]

Upgrading to the new release is not an option right now as it has been very disruptive in the past (usually the system goes down and then I have to fiddle with it for a couple hours to get it back online).

I attached both logs. From the export log you will see that it takes 30-40 secs per transcation.
I could not see anything meaningful to me in the general vault log. Export starts around 3:40pm on 1/23.

Shadow folders are disabled.

P.S. files with .log extension are not allowed to be uploaded for some reason. You should really change that, it's not obvious that I had to rename YOUR LOG from .log to .txt so it could be uploaded.

[sanin]

I just ran all DB optimization scripts. It did not make any difference...

[lbauer]

There aren't any errors of concern in the log files. This is just going slowly. What are the specs of your Vault Server machine? Is SQL Server on the same machine or a different machine?

Do you have fast drives? Fast I/O is important here. Could anti-virus software be slowing things down?

[sanin]

SQL is on a different box. It's a very decent and competent SQL server and it delivers nice performance for regular use. It's definitely not expected (is it?) to run Vault DB on a monster database server.

I am very puzzled about why this is taking so long, it's pretty much unusable for us and i need to migrate data from one repository to another...

[sanin]

Anyone? What to do here?

[sanin]

I just upgraded to 3.5.1 and performance is unchanged.

I have been a very vocal proponent of Vault and I evangelize it every chance I get, but I must say that this is unacceptable (both the fact that no one is responding to these forum posts, and the export performance).

I need someone tell me what can be done about this problem as well as if there is another way to migrate a part of the source tree to another repository.

[lbauer]

Do you have anti-virus software running on the Vault server machine or the Export Tool machine? That could slow performance if it is checking every read/write.

Besides running the DB maintenance scripts, did you check to see if the file system is fragmented? Disk fragmentation can adversely affect SQL Server performance.

You didn't give the specs for your SQL Server machine. If you have it, try moving the temp database to a super fast disk I/O subsystem - such as 10K-15K rpm HDDs or striped RAID-0 set. The export will use a lot of temp tables, and that may help w/ the bottle neck.

Vault 3.5.2, which will be release in a couple of months will help in some of this area as queries to build old trees has been optimized.

[sanin]

Here is the SQL trace while running the export.

I see that lines like this one:

exec dbo.spgetfolderexportdelta @sessionid = N'peu5req0lk0d3bj4z2zavkm1', @repid = 2, @baserevid = 23894, @targetrevid = 23893

take about 20 seconds to complete. What can be optimized here? It looks like a huge number of reads/writes is executed just to satisfy this sproc call.

I mean I am not going to go out and start buying additional RAID disks and go through all just to be able to run the export. Is there any other way to export the data?

[lbauer]

Basically, the Export is recreating your history, checkin by checkin. It isn't a fast process.

Export\Import is a new feature in 3.5.1, so there may be ways to optimize it down the road. In our testing in-house we generally had transactions of 4 - 6 seconds, so 20 seconds is rather slow. Variables might be hardware configuration/resources, etc.

We could take a look at your Vault database and see if we get the same results on our hardware. Contact support at sourcegear.com ATTN Linda if you want to go this route.

[lbauer]

Another suggestion would be to put your Vault Server logging into debug mode (Vault Admin Tool->Server Options->logging), and we can check the log to see if there are any errors or other obvious problems.

Another option is to do some SQL profiling against the stored procedures to see if we can identify the bottleneck.

[sanin]

So I went through the pain of migrating the whole database to a much faster server and I got the process to perform at about 4-5 seconds a transaction. With 20K+ transactions it would still take good 30 hours but I could live with that.
However, the process failed with an error message some 4K+ transaction into it.

I need some help with this. I attached the screenshot and both logs.

[lbauer]

We'd like to take a look at your database to determine what the problem is. Contact support at Sourcegear.com ATTN Linda, and we can discuss the details.