Big performance problems with Vault 3.0.1

[valik]

We need some help, we are currently going from VSS to Vault for many reasons and people are getting desperate because of the poor performance of Vault. We were using also SourceOffsite and were very happy in combination with VSS, but stability and performance (of VSS) were the main reasons to change to Vault.

Here a summary of the issues:

- checkout takes 15-18 seconds
- Undo checkout take 10-14 seconds
- checkin (without any changes) 14-16 seconds
- retrieving repository structure takes first time quite a while (around 30 seconds), if somebody else performs a checkout/checkin/undo checkout it takes 14-16 seconds
- double clicking on a sourcefile from the repository shows the file immediately (less than 1 sec)
- get latest version, is also very fast, see previous line
- show differences is very fast

The message 'Ending transaction' appears in the status bar and it stays a while depending on the operation performed (see details above).

Here the specs about client and server been used:

- SourceGear Vault Client 3.0.1 (2769)

- SourceGear Vault Server 3.0.1:
- Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
- Standard Edition on Windows NT 5.0 (Windows 2000) (Build 2195: Service Pack 4)
- 640 MB Ram
- Pentium 3 500 Mhz
- 38 GB harddisk (15 Mbytes/sec disk), mirrored disk

About the repository
- 143595 Revision
- 43115 files
- 5048 folders

Regards,

Valik

[jclausius]

We need some help, we are currently going from VSS to Vault for many reasons...

Is it safe to assume the files were imported from VSS? If so, have you run any database maintenance on the sgvault database? Things like updating the statistics, defragging indices, etc.

Also, in my experience, a badly fragmented hard drive can cause big performance issues. This includes the drive for Vault server's working folder ( from vault.config ) as well as the drive where SQL Server stores the Vault database.

How many users are accessing the Vault server?

[valik]

We need some help, we are currently going from VSS to Vault for many reasons...

Is it safe to assume the files were imported from VSS?

Yes we deed.

If so, have you run any database maintenance on the sgvault database? Things like updating the statistics, defragging indices, etc.

Not that I'm aware of. which command should be executed?

Also, in my experience, a badly fragmented hard drive can cause big performance issues. This includes the drive for Vault server's working folder ( from vault.config ) as well as the drive where SQL Server stores the Vault database.

We already did a defrag on the OS level. I'll again do an analisis.

How many users are accessing the Vault server?

We have currently 30 users registered, but between 2 and 3 are currently active.

[jclausius]

You mentioned you are using SQL Server. Within SQL Server's Enterprise Manager, there is a Maintenance Job Wizard. I would recommend looking at creating / running a maintenance job weekly.

Also, are your repositories configured to have Folder security enabled? If you do not need it, disable this for each repository in the Admin Tool. For each and every checkout list update, a lookup is checked to make sure a user has access to a folder. Removing these checks can help improve performance.

In any case, if my guess is correct after the import the statistics / indices need to be updated with the new table distributions.


I'm also concerned about your disk subsystems. You mention your I/O is at 15 MB/s. Basic SATA / SCSI drives today are 10x that speed ( 150 MB/s SATA and 160 MB/s for Ultra 160 SCSI). Just a reminder that Vault will be constrained by the speed of SQL Server, which tends to like a lot of memory and very fast I/O.

---------

For those readers without Enterprise Manager, I would suggest reading up on the DBCC CHECKDB, DBCC DBREINDEX, DBCC INDEXDEFRAG, and UPDATE STATISTICS commands.

I wrote up a simple script utilizing a couple of these commands. Some of these commands (like DBREINDEX) will bring your server to a halt, so only use them when you know no one will be using Vault.

Code: Select all

USE sgvault
GO

DECLARE c CURSOR FOR SELECT name FROM sysobjects WHERE type = N'U' ORDER BY name
OPEN c

DECLARE @@name [nvarchar](64), 
	@@stmt [nvarchar](256)

SET @@stmt = N'--'
FETCH FROM c INTO @@name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- statement to completely rebuild the index
   --SET @@stmt = N'DBCC DBREINDEX(''sgvault.dbo.' + @@name + ''', '''', 0)' -- commented out with hyphen hyphen

   -- statement to update table's statistics 
   --SET @@stmt = N'UPDATE STATISTICS ' + @@name -- commented out with hyphen hyphen

   -- just print the name for now.
   PRINT N'Table Name: ' + @@name
   
   -- execute the command assigned to @@stmt
   EXEC sp_executesql @@stmt
   FETCH NEXT FROM c INTO @@name
END

CLOSE c
DEALLOCATE c 

[valik]

You mentioned you are using SQL Server. Within SQL Server's Enterprise Manager, there is a Maintenance Job Wizard. I would recommend looking at creating / running a maintenance job weekly.

I did make a new job with the following specs:

- Reorganize data and index pages (10%)
- Check database integrity (include indexes)

then I did run this job onced. After that I did again the same tests already mentioned (checkout, checkin, etc.) without any improvement still had to wait for several seconds (15-30 seconds)

Also, are your repositories configured to have Folder security enabled? If you do not need it, disable this for each repository in the Admin Tool.
For each and every checkout list update, a lookup is checked to make sure a user has access to a folder. Removing these checks can help improve performance.

I did disable it without any improvement, still we require this to be on.

In any case, if my guess is correct after the import the statistics / indices need to be updated with the new table distributions.

I did also run your script below (uncommenting both SET operations). No improvement.

I'm also concerned about your disk subsystems. You mention your I/O is at 15 MB/s. Basic SATA / SCSI drives today are 10x that speed ( 150 MB/s SATA and 160 MB/s for Ultra 160 SCSI). Just a reminder that Vault will be constrained by the speed of SQL Server, which tends to like a lot of memory and very fast I/O.

I did check with another machine. Using a backup and importing it in a different server where Vault and Sql Server are installed. No improvement encountered. I think we are getting confused with the MB, I mean a performance of 15 MBYTES per second which corresponds with your 150 MBITS per second
---------

For those readers without Enterprise Manager, I would suggest reading up on the DBCC CHECKDB, DBCC DBREINDEX, DBCC INDEXDEFRAG, and UPDATE STATISTICS commands.

I wrote up a simple script utilizing a couple of these commands. Some of these commands (like DBREINDEX) will bring your server to a halt, so only use them when you know no one will be using Vault.

Code: Select all

USE sgvault
GO

DECLARE c CURSOR FOR SELECT name FROM sysobjects WHERE type = N'U' ORDER BY name
OPEN c

DECLARE @@name [nvarchar](64), 
	@@stmt [nvarchar](256)

SET @@stmt = N'--'
FETCH FROM c INTO @@name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- statement to completely rebuild the index
   --SET @@stmt = N'DBCC DBREINDEX(''sgvault.dbo.' + @@name + ''', '''', 0)' -- commented out with hyphen hyphen

   -- statement to update table's statistics 
   --SET @@stmt = N'UPDATE STATISTICS ' + @@name -- commented out with hyphen hyphen

   -- just print the name for now.
   PRINT N'Table Name: ' + @@name
   
   -- execute the command assigned to @@stmt
   EXEC sp_executesql @@stmt
   FETCH NEXT FROM c INTO @@name
END

CLOSE c
DEALLOCATE c 

I have attached a logfile and I did turn 'debug' on, there you can see what's going on.

I really would like the performance to be improved otherwise I have the feeling that we made the wrong choice in buying Vault.

regards,

Valik

[jclausius]

I did check with another machine. Using a backup and importing it in a different server where Vault and Sql Server are installed. No improvement encountered. I think we are getting confused with the MB, I mean a performance of 15 MBYTES per second which corresponds with your 150 MBITS per second

No, I do mean megabytes. ATA-100 (100 MBytes/sec), ATA-133 (133 MBytes/sec), SATA (150 MBytes/sec), etc. These of course are theoritical transfer speeds between the controller and physical device and those rates are never really seen in practice. I think the actual transfer rate is about 60% of that, which puts ATA-100 at 62MBytes / sec and ATA-133 around 82.5 MBytes/sec.

I have attached a logfile and I did turn 'debug' on, there you can see what's going on.

The log file does show my suspicions. There is something strange in the server code related to checkout lists.

From the server log, you can see there is an entry of "GetCheckOutListChanges" at 12:24:55. This takes 10 seconds to complete, which might be explained by the very first time a machine logged into the server.

The next "GetCheckOutListChanges" occurs at 12:34:55. This entry also takes 10 seconds to complete. This is where things get strange. Normally, any subsequent call where nothing changed takes less than 30 milliseconds to complete.

The checkout list didn't change and no one made any changes to security rights for the repository, a repositories folder, or anything else, this second call should have returned almost immediately.


Its almost as if the user's checkout timestamp was not updated. Are you familiar enough with SQL to run a stored procedure? If so, can I ask you for some information?

We'll need the repository's id ( SELECT repid, name FROM sgvault.dbo.tblrepositories ), and the user's id for Valik ( SELECT userid, name FROM sgvault.dbo.tblusers ).

With this information in hand, what does the following stored proc return:
EXEC sgvault.dbo.spgetcurrentcheckoutinfo @repid = REPID, @userid = VALIKS_USERID


We may need to enable some client side debugging to see what is transferred between server and client, but this seems like a good starting point to find the problem for your installation.

[jclausius]

Addendum:

We'll need two runs of the stored procedure.

1) Run the spgetcurrentcheckoutinfo once.
2) Do a checkout of one file from the Vault GUI client
3) Re-run the stored procedure:

EXEC sgvault.dbo.spgetcurrentcheckoutinfo @repid = REPID, @userid = VALIKS_USERID

What results are the results for both runs of the stored proc?

[jclausius]

I did check with another machine. Using a backup and importing it in a different server where Vault and Sql Server are installed.

Out of curiosity, did you use the same client machine for each test?

Can you run a test using the client which is also installed on the Vault server? Does this have any impact on things?

[Don Thimsen]

Valik,

Have you looked into the resource utilization on the Windows side? Is the vault server being shared by other applications? 640MB might not be enough memory for the server. Use the Windows Task Manager and look at the performance tab. You might be doing a lot of paging to your swap file which might explain why operation is so slow.

Don

[valik]

I did check with another machine. Using a backup and importing it in a different server where Vault and Sql Server are installed. No improvement encountered. I think we are getting confused with the MB, I mean a performance of 15 MBYTES per second which corresponds with your 150 MBITS per second

No, I do mean megabytes. ATA-100 (100 MBytes/sec), ATA-133 (133 MBytes/sec), SATA (150 MBytes/sec), etc. These of course are theoritical transfer speeds between the controller and physical device and those rates are never really seen in practice. I think the actual transfer rate is about 60% of that, which puts ATA-100 at 62MBytes / sec and ATA-133 around 82.5 MBytes/sec.

I have attached a logfile and I did turn 'debug' on, there you can see what's going on.

The log file does show my suspicions. There is something strange in the server code related to checkout lists.

From the server log, you can see there is an entry of "GetCheckOutListChanges" at 12:24:55. This takes 10 seconds to complete, which might be explained by the very first time a machine logged into the server.

The next "GetCheckOutListChanges" occurs at 12:34:55. This entry also takes 10 seconds to complete. This is where things get strange. Normally, any subsequent call where nothing changed takes less than 30 milliseconds to complete.

The checkout list didn't change and no one made any changes to security rights for the repository, a repositories folder, or anything else, this second call should have returned almost immediately.


Its almost as if the user's checkout timestamp was not updated. Are you familiar enough with SQL to run a stored procedure? If so, can I ask you for some information?

We'll need the repository's id ( SELECT repid, name FROM sgvault.dbo.tblrepositories ), and the user's id for Valik ( SELECT userid, name FROM sgvault.dbo.tblusers ).

With this information in hand, what does the following stored proc return:
EXEC sgvault.dbo.spgetcurrentcheckoutinfo @repid = REPID, @userid = VALIKS_USERID


We may need to enable some client side debugging to see what is transferred between server and client, but this seems like a good starting point to find the problem for your installation.

this is what I did:
1. EXEC sgvault.dbo.spgetcurrentcheckoutinfo @repid = 2, @userid = 21
result: 21906 2005-01-10 14:19:49.647
2. login and checkout a file
3. EXEC sgvault.dbo.spgetcurrentcheckoutinfo @repid = 2, @userid = 21
result: 21907 2005-01-10 14:19:49.647

I'm running all this tests locally on the vault server machine and using a local Vault Client.

the specs of this machine are slightly better:
- 1.5 GB Ram
- Athlon 2 CPU's (1.5 Ghz)

See attachment for more details.

Regards,

Valik

[jclausius]

OK... From your doc, it seems subsequent GetCheckoutListChanges are returning in the expected amount of time, so I don't think the problem is client related.


Let's take a closer look at the checkout list table. Can you post ( or send to me in an email for more privacy ) the message output ( not the actual result set ) of the following SQL code:

Code: Select all

DECLARE @@listid bigint

SELECT @@listid = currentcolistid FROM sgvault.dbo.tblrepositories WHERE repid = 2

SET STATISTICS IO ON 

SELECT li.colistid, li.objid, li.hostname, li.locktype, li.folderobjid, li.lockwhen, 
	li.fullpath, li.localpath, li.comment, li.miscinfo, li.userid, u.login, v.objversion, o.lastobjversion 
	FROM sgvault.dbo.tblcheckoutlistitems li 
	INNER JOIN sgvault.dbo.tblfsobjects o ON ((o.repid = 2) AND (o.objid = li.objid)) 
	INNER JOIN sgvault.dbo.tblusers u ON (u.userid = li.userid) 
	INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = li.objverid)
	WHERE (li.colistid = @@listid)

SET STATISTICS IO OFF
GO

Also, let's take a look at the plan:

1) Run ( by itself )
SET SHOWPLAN_ALL ON

2) Run the following
DECLARE @@listid bigint
SELECT @@listid = currentcolistid FROM sgvault.dbo.tblrepositories WHERE repid = 2
SELECT li.colistid, li.objid, li.hostname, li.locktype, li.folderobjid, li.lockwhen,
li.fullpath, li.localpath, li.comment, li.miscinfo, li.userid, u.login, v.objversion, o.lastobjversion
FROM sgvault.dbo.tblcheckoutlistitems li
INNER JOIN sgvault.dbo.tblfsobjects o ON ((o.repid = 2) AND (o.objid = li.objid))
INNER JOIN sgvault.dbo.tblusers u ON (u.userid = li.userid)
INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = li.objverid)
WHERE (li.colistid = @@listid)

3) Run ( by itself ):
SET SHOWPLAN_ALL OFF

4) Highlight the top/left cell in the results so everything is selected. Then invoke the context menu and choose Save As. Save the file ( in Tab delimited format if its not too much to ask ).

[valik]

OK... From your doc, it seems subsequent GetCheckoutListChanges are returning in the expected amount of time, so I don't think the problem is client related.


Let's take a closer look at the checkout list table. Can you post ( or send to me in an email for more privacy ) the message output ( not the actual result set ) of the following SQL code:

Code: Select all

DECLARE @@listid bigint

SELECT @@listid = currentcolistid FROM sgvault.dbo.tblrepositories WHERE repid = 2

SET STATISTICS IO ON 

SELECT li.colistid, li.objid, li.hostname, li.locktype, li.folderobjid, li.lockwhen, 
	li.fullpath, li.localpath, li.comment, li.miscinfo, li.userid, u.login, v.objversion, o.lastobjversion 
	FROM sgvault.dbo.tblcheckoutlistitems li 
	INNER JOIN sgvault.dbo.tblfsobjects o ON ((o.repid = 2) AND (o.objid = li.objid)) 
	INNER JOIN sgvault.dbo.tblusers u ON (u.userid = li.userid) 
	INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = li.objverid)
	WHERE (li.colistid = @@listid)

SET STATISTICS IO OFF
GO

Also, let's take a look at the plan:

1) Run ( by itself )
SET SHOWPLAN_ALL ON

2) Run the following
DECLARE @@listid bigint
SELECT @@listid = currentcolistid FROM sgvault.dbo.tblrepositories WHERE repid = 2
SELECT li.colistid, li.objid, li.hostname, li.locktype, li.folderobjid, li.lockwhen,
li.fullpath, li.localpath, li.comment, li.miscinfo, li.userid, u.login, v.objversion, o.lastobjversion
FROM sgvault.dbo.tblcheckoutlistitems li
INNER JOIN sgvault.dbo.tblfsobjects o ON ((o.repid = 2) AND (o.objid = li.objid))
INNER JOIN sgvault.dbo.tblusers u ON (u.userid = li.userid)
INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid = li.objverid)
WHERE (li.colistid = @@listid)

3) Run ( by itself ):
SET SHOWPLAN_ALL OFF

4) Highlight the top/left cell in the results so everything is selected. Then invoke the context menu and choose Save As. Save the file ( in Tab delimited format if its not too much to ask ).

I'll perform the above mentioned things but before I do that I noticed something very interesting.

- login with 'admin' account
- retrieves the repository structure (20-30 sec)
- performs a checkout, it return within 1 second (great)
- performs an undo checkout, it returns within 1 second

This is the speed I would expect it is incredibely fast, even using a remote Vault client (not on the Vault Server)

Maybe this helps.

Valik

[jclausius]

This would explain things if determining security was the problem.

Didn't you mention in an earlier post, when tested w/ Folder Security disabled from within the Admin Tool, the checkout updates were still taking 15+ seconds?

Note - when disabling Folder Security, make sure to hit OK on the repository properties dialog.

[jclausius]

An update - I just received a private message from Valik, and things look OK from the checkout list perspective.

We are now investigating what part calculating folder security plays on his checkout list.

[valik]

This would explain things if determining security was the problem.

Didn't you mention in an earlier post, when tested w/ Folder Security disabled from within the Admin Tool, the checkout updates were still taking 15+ seconds?

Note - when disabling Folder Security, make sure to hit OK on the repository properties dialog.

I did again tested with Folder Security disabled and indeed it is almost as fast as with the 'Admin' account (2-3 seconds) on a remote machine and 1 sec on the local machine. This is the performance I like but we still Folder Security to be enabled. What would be the next step.

Valik