Jump to content

Max number of passwords per list?


jtstuedle

Recommended Posts

Trialing Passwordstate in our environment. Expecting a large number of users so I’ve specced a webserver with 12GB of RAM, and a separate SQL DB server that has more than enough memory to load the entire DB in memory (128GB+). They’re on the same virtual host with a 10Gb connection between them.


I’m pushing through some heavy use case testing and seeing some pretty slow grid load times on a password list with ~50k entries. Anywhere from 10-30 seconds on average to move between pages in the grid. 
 

I’ve triggered an issue twice now while using the APIs to rapidly load entries where the list fails to load in the UI and tells me to check the error console. I can reload the page and it will (slowly) load the list up.

 

Error Code = Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'., StackTrace = at Passwordstate.Passwordstate.Password.QueryPasswords(String PasswordListID, String SearchCriteria)
 

 

The rest of the site remains fairly responsive, as well as loading other lists, even as I’m sending in thousands of API requests to the password endpoint so I’m pretty sure it’s not a resource issue. 
 

Is there any guidance on a theoretical “limit” on the max number of entries per password list?
 

Any suggestions on speeding the web UI up when dealing with a large number of entries? It seems like maybe there’s some slowness when dealing with pagination of the large number of entries. 

Link to comment
Share on other sites

Digging into the performance hit of very large password lists a little bit more to see if it's something specific to just my trial install.

 

It looks like anytime you load the password grid up, the web UI queries the entire dataset from the database using the below query (and does the same for subsequent grid page changes - so moving from page 1 -> page 2 -> page 3 all seem to make repeatedly this same query). Executing the below SQL directly against a list with ~141k entries takes anywhere from 35-40 seconds to fully return the data. That's for every grid page change, too.

 

exec sp_executesql N'SELECT Passwords.PasswordID, Passwords.Title, Passwords.UserName, Passwords.Description, Passwords.GenericField1, Passwords.GenericField2, Passwords.GenericField3, Passwords.GenericField4, Passwords.GenericField5, Passwords.GenericField6, Passwords.GenericField7, Passwords.GenericField8, Passwords.GenericField9, Passwords.GenericField10, AccountTypes.AccountType, AccountTypes.ImageFileName, Passwords.Notes, Passwords.URL, Passwords.Password, Passwords.ExpiryDate, Passwords.AllowExport, Passwords.WebUser_ID, Passwords.WebPassword_ID, Passwords.ScriptID, Passwords.LastUpdated, Passwords.HeartbeatEnabled, Passwords.HeartbeatPoll, Passwords.HeartbeatStatus, Passwords.RequiresCheckOut, Passwords.ChangeOnCheckin, Passwords.CheckInAt, Passwords.CheckOutUserID, Passwords.CheckOutUserName, Passwords.PasswordListID, Passwords.GUID, Passwords.OTPUri, PasswordListsACL.UserID, PasswordLists.PrivatePasswordList, PasswordLists.SiteID, FavoritePasswords.FavoritePasswordID, MAX(PasswordsACL.PasswordACLID) AS PasswordACLID, MAX(PasswordDocuments.DocumentID) As DocumentID, Passwords.LinkID, Passwords.PasswordResetEnabled, PasswordLists.PasswordList, PasswordLists.TreePath, PasswordLists.PropagatePermissions, PasswordLists.ProvideAccessReason, ADDomains.ADDomainNetBIOS, Hosts.HostName, Hosts.SQLInstanceName, Hosts.SessionRecording, ADDomains.ADDomainID, Passwords.ValidationScriptID, Hosts.HostID, Passwords.ResetStatus, Passwords.LastResetDate, Hosts.Unmanaged, MAX(ResetTasksQueue.QueuedAt) As QueuedAt, (SELECT COUNT(ResetTasks.TaskID) FROM ResetTasks WHERE (ResetTasks.PasswordID = Passwords.PasswordID)) AS Dependencies, PasswordLists.HidePasswords FROM [PasswordListsACL] INNER JOIN [Passwords] ON PasswordListsACL.PasswordListID = Passwords.PasswordListID INNER JOIN [PasswordLists] ON PasswordLists.PasswordListID = Passwords.PasswordListID FULL OUTER JOIN [PasswordsACL] ON PasswordsACL.PasswordID = Passwords.PasswordID FULL OUTER JOIN [PasswordDocuments] ON PasswordDocuments.PasswordID = Passwords.PasswordID FULL OUTER JOIN [FavoritePasswords] ON PasswordListsACL.UserID = FavoritePasswords.UserID And Passwords.PasswordID = FavoritePasswords.PasswordID FULL OUTER JOIN [AccountTypes] ON Passwords.AccountTypeID = AccountTypes.AccountTypeID LEFT OUTER JOIN [ADDomains] ON ADDomains.ADDomainID = Passwords.ADDomainID LEFT OUTER JOIN [Hosts] ON Hosts.HostID = Passwords.HostID LEFT OUTER JOIN [ResetTasksQueue] ON ResetTasksQueue.PasswordID = Passwords.PasswordID WHERE (PasswordListsACL.UserID = @UserID) And (Passwords.PasswordListID = @PasswordListID) And (PasswordListsACL.Permissions <> ''G'') AND (Passwords.Deleted = 0) GROUP BY Passwords.PasswordID, Passwords.Title, Passwords.UserName, Passwords.Description, Passwords.GenericField1, Passwords.GenericField2, Passwords.GenericField3, Passwords.GenericField4, Passwords.GenericField5, Passwords.GenericField6, Passwords.GenericField7, Passwords.GenericField8, Passwords.GenericField9, Passwords.GenericField10, AccountTypes.AccountType, AccountTypes.ImageFileName, Passwords.Notes, Passwords.URL, Passwords.Password, Passwords.ExpiryDate, Passwords.AllowExport, Passwords.WebUser_ID, Passwords.WebPassword_ID, Passwords.ScriptID, Passwords.LastUpdated, Passwords.HeartbeatEnabled, Passwords.HeartbeatPoll, Passwords.HeartbeatStatus, Passwords.RequiresCheckOut, Passwords.ChangeOnCheckin, Passwords.CheckInAt, Passwords.CheckOutUserID, Passwords.CheckOutUserName, Passwords.PasswordListID, Passwords.GUID, Passwords.OTPUri, PasswordListsACL.UserID, PasswordLists.PrivatePasswordList, PasswordLists.SiteID, FavoritePasswords.FavoritePasswordID, Passwords.LinkID, Passwords.PasswordResetEnabled, PasswordLists.PasswordList, PasswordLists.TreePath, PasswordLists.PropagatePermissions, PasswordLists.ProvideAccessReason, ADDomains.ADDomainNetBIOS, Hosts.HostName, Hosts.SQLInstanceName, Hosts.SessionRecording, ADDomains.ADDomainID, Passwords.ValidationScriptID, Hosts.HostID, Passwords.ResetStatus, Passwords.LastResetDate, Hosts.Unmanaged, PasswordLists.HidePasswords ORDER BY Passwords.Title',N'@UserID nvarchar(26),@PasswordListID nvarchar(2)',@UserID=N'user@example.com',@PasswordListID=N'123'

 

 

Adding an OFFSET to the end of the query so SQL only returns the rows needed to display the current grid speeds the query up by 6-7 times (average 5-6 second execution by doing the offset at the end of the query).

 

OFFSET <RequestedPageNumber * GridReturnSize>  ROWS FETCH NEXT <GridReturnSize> ROWS ONLY

 

@support - I'm going to drop the link to this forum post over to your sales/support email as well.

 

Thanks!

 

Link to comment
Share on other sites

Hi jtsuedle,

 

With some testing today, we have 50000 password in one list, and this list takes around 8 - 10 seconds to load the initial grid.  This is because it does retrieve all the entries upon initial loading of the Password List.  You are right that each time you page to the next page in the password list, it will re-query all passwords again, but I'd recommend maybe instead of paging through the password list, that you instead perform a search which should take 1 second.

 

Do you foresee you storing 50000 passwords within one list?  Possibly you could separate out the passwords into multiple lists which is what most Passwordstate users would do.  Possibly you have 50000 deksopt machiens and you want to store all Admin accounts in one list though?

 

Regards,

Support

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...