Video Screencast Help

DA performance issues - recommendation

Created: 15 Apr 2014 • Updated: 22 Apr 2014 | 14 comments
This issue has been solved. See solution.

Hi

Running EV/CA/DA 9.0.2

Have a single EV SQL server, and a single CA/DA SQL server.

SQL servers are 2008 R2 VMs, 4 CPU with 32GB of RAM. Planning on upping CPUs to 8GB.

We've had some performance issues recently in the environmnent around CA and DA, mainly due to a recent 10-fold increase in DA usage, with massive searches, acceptances, legal holds, etc. We went from 7 million messages searched for in Feb to 100 million messages searched for in Mar, and some of these massive searches were accepted, put on legal hold etc.

DA database size went from 200GB in early Mar, to 500GB in late Mar, now around 385GB.  CA database is fairly constant around 68GB.

Question 1 - what reduces DA database size? Deleting cases? Taking legal holds off?

Other than not increasing DA usage 10-fold and being mindful of massive legal holds, etc., I am wondering what to do to improve performance, outside of the standard tech docs such as http://www.symantec.com/business/support/index?page=content&id=TECH63230

With a DA database of 500GB, maintenance is very time consuming with the time needed for SQL dumps, index reorgs, etc.

DA10 SQL best practise guide states:

You may need to roll over customer databases to remain within the available storage capacity. The database storage requirements are high. To reduce storage costs, you may want to roll over the database periodically to a new customer database (that is, create all new cases in a new database), and then archive the old database when it is no longer active. Alternatively, you can move the old customer database to slower storage and disable the associated Discovery Accelerator customer tasks. This would allow the customer database to be brought online quickly.

Question 2 - at what point should database be rolled over? My understanding is that there isn't a real way to "close" or "archive" a DA customer database. The best approach is to stop any activity in it (e.g. scheduled searches) and advise users to use a new database. What does disabling the DA customer task do? Does that make the database inaccessible to end users?

The "Co-locating multiple customer databases" section says it may pay to split DBs between different SQL servers, but doesn't give much guidelines around at what point this should be done.

Question 3 - "Additional customer databases may create an impact at the Discovery Accelerator server and the underlying Enterprise Vault infrastructure, which needs to be considered before implementation" - what sort of impact? Is this if multiple customer databases are created on a single SQL server instead of multiple servers?

Any advice appreciated. Thanks.

 

 

Operating Systems:

Comments 14 CommentsJump to latest comment

TonySterling's picture

Here are my replies:

 

Question 1 - what reduces DA database size? Deleting cases? Taking legal holds off?

Deleting the cases will remove those entries from the database.  The log file will grow whilst the items are deleted so you need to account for that. 

Question 2 - at what point should database be rolled over? 

This is going to depend on your SQL Server capicity, as you have noted, your current server is being impacted so you may have reached this point.

Question 3 - "Additional customer databases may create an impact at the Discovery Accelerator server and the underlying Enterprise Vault infrastructure, which needs to be considered before implementation" - what sort of impact? Is this if multiple customer databases are created on a single SQL server instead of multiple servers?

You would not want to put more active customer databases on the same sql server, that would defeat the purpose.  If you are going to put the current customer database into a read only type role then that might be ok.

One question, is it possible to leave this current SQL Server in place for CA and then move DA to a SQL server with more resources?  If the current search load is going to remain you might need to consider that.

SOLUTION
goatboy's picture

Deleting the cases will remove those entries from the database.  The log file will grow whilst the items are deleted so you need to account for that.

Thanks, I don't think we delete cases so I'm wondering why we are down to 385GB from 500GB. I know we've taken some massive legal holds off, would that also reduce the size of the database?

One question, is it possible to leave this current SQL Server in place for CA and then move DA to a SQL server with more resources?  If the current search load is going to remain you might need to consider that.

Yes, that's a possibility.

What does disabling the DA customer task do? Does that make the database inaccessible to end users? I am wondering what the best approach is to "retiring" the exisiting customer database. There is no official way to make it read only, is there? (maybe outside of changing users' DA roles in the database).

 

TonySterling's picture

Personally I don't think you should disable the customer task, if you do users can view the items.  You could change the permissions so they can't run searches but still be able to view the cases and items.

As for the size difference, could it have been the logs getting truncated or is this specifically the database size?  Legal hold is tracked in the tblIntDiscoveredItems table and not on its own, so I am not sure how it could have lowered the size of the db.

goatboy's picture

Personally I don't think you should disable the customer task, if you do users can view the items.  You could change the permissions so they can't run searches but still be able to view the cases and items.

What does disabling the customer task actually do? I can't find doco on it. Is it automatically re-enabled for any reason, like when restarting Accelerator services?

As for the size difference, could it have been the logs getting truncated or is this specifically the database size?  Legal hold is tracked in the tblIntDiscoveredItems table and not on its own, so I am not sure how it could have lowered the size of the db.

That size is purely for the MDF file, we track log size separately. I'm not sure either why the size fluctuated so much. Does the size grow significantly if there are massive (say 20 million item) searches done but NOT accepted? Or does the size grow when searches are accepted?

TonySterling's picture

So high level, the Customer background tasks process is responsible for all the Discovery Accelerator tasks for that customer database.

If you did have a bunch of unaccepted searches that could be part of it but I wouldn't think it would account for 115GB, unless you had a TON of pending searches!

 

 

Kenneth Adams's picture

Greetings, goatboy;

Tony is correct in the information he has provided.  For example, the Customer Background Tasks are what controls what a DA Reviewer can do inside the customer and what background operations run.  When the Customer Background Tasks are stopped, users can still get into the Customer through the DA Client, but they cannot run new searches or exports.  They cannot mark items as reviewed, but they can review items and search criteria. They can also see what searches and exports were run.

When a search runs and is in a pending state, we collect somewhere between 5 KB and 25 KB of data for each item.  When a search is rejected, this data is removed from the database.  When the search is accepted, this data is moved from a holding table to the tblIntDiscoveredItems table.  It is from the tblIntDiscoveredItems table that we obtain information used in the preview and export processing for each item.

For your database's mdf file to shrink from 500 GB to 385 GB, you would have had to have either massive numbers of pending items that were rejected, or you had Research Folders that contained 1000's of items that were deleted, or you had Cases that contained 1000's of items that were deleted, or any combination of these 3 possibilities.

You can check the tblCase table to see if any cases or Research Folders have been deleted (ever) by looking for any entry with a Status ID of 24.  To do this, just run the following SQL query against your DA Customer database:

SELECT CaseID, Name, ModifiedDate, EndDate, FolderType FROM tblCase WHERE StatusID = 24;

You can also check to see if any searches were rejected that may add up to the number of hits needed to reach 115 GB of data (at 25KB each).  Do do this, just run the following SQL query against your DA Customer database, too:

SELECT SearchID, Name, CaseID, NumHits FROM tblIntSearches WHERE StatusID = 858;

As your mdf file size shrank, your SQL DBA must have performed our recommended maintenance with the 'DB Shrink' option selected and configured to return the space to the operating system.  We normally don't recommend returning the space to the operating system unless absolutely necessary for the SQL Server's continued operation as the db would have to request new space into which it can grow when it uses what ever available space was left in the db.  This growth request has an impact on the application performance as processing can slow while the storage subsystem allocates the space for the db.

If you are able to stand up a new SQL Server to host just the DA databases (i.e., the DA configuration and customer dbs), you may be able to continue using your existing DA customer.  It may also be that you are at the effective limit for that customer and need to create a new DA customer.  If you chose to create a new DA customer but want to keep access to the current DA customer, I would recommend that you stop the current customer's Customer Background Tasks through the EVBAAdmin site and make sure anyone with access to the Vault Service Account's credentials knows not to start them again. Note that you need to evaluate what is best for your environment, though.  My recommendation to just stop the Customer's Background Tasks may not be the best solution for your environment.  Maybe changing permissions within roles is the best solution for your environment.  You need to determine what is best.

I hope this helps.  Sorry for the length if this posting.  Please let us know if you need anything more for this posting.

 

Ken Adams

Backline Support for CA, DA, ACE, UCE, PSTD, ARMS, EVDC
US Support Region

SOLUTION
goatboy's picture

Thank you, very helpful. And please don't apologise for the length of the posting, these forums are really invaluable, so I appreciate the time you took to answer in detail! smiley

goatboy's picture

I tested stopping the customer's tasks in my 9.0.2 lab.

Here was the behavior:

 

1. I could submit a search, but it just sat there showing archives as "not started" with 0 hits

2. I could submit an export but it just sat there with 0 items exported.

Once I started the task, both competed.

 

Is this expected behavior with the customer's task stopped?

TonySterling's picture

Yes, they get queued by DA until the tasks starts.  If you want to prevent users from running searches I would recommend editing the roles as you probably will need to export at some point.

Kenneth Adams's picture

Goatboy,

Tony is correct in that stopping the Customer's Background Tasks (CBTs) will still allow searches and exports to be created. They just won't run until the CBTs are started again.  Once the CBTs are started, ALL new searches and exports would be kicked off, which could overload your environment until those searches and exports are either completed or stopped.

Changing permissions in role assignments could be a more appropriate course of action to take, but care must be taken to determine which permissions are best to be removed in your environment.

Ken Adams

Backline Support for CA, DA, ACE, UCE, PSTD, ARMS, EVDC
US Support Region

goatboy's picture

Thanks, seems much easier to disable the customer tasks, and much easier to change back if end users need access to the old environment. But has those risks you mentioned.

Is it possible to use the SQL queries here - http://www.symantec.com/business/support/index?page=content&id=TECH63230 -  to see if there are any pending searches/exports and then cancel the via another SQL query? Or is this course of action not recommended?

 

 

Kenneth Adams's picture

You can certainly use the SQL queries in TECH63220 to determine if you have any running, paused, or pending acceptance searches and any running or paused exports.  Those are just queries that don't modify anything in the database.  They just take a little of the SQL Server's resources in order to run.

We don't recommend using SQL to accept or reject searches as a rule of thumb as you should be able to select multiple searches within a Case and accept or reject them.  That said, we can do it if you have 100's of searches across lots of Cases, some cases of which you don't have ready access to while using the Vault Service Account.  You would need to open a Support Case and provide proof that you have such a large number of searches to process.

Ken Adams

Backline Support for CA, DA, ACE, UCE, PSTD, ARMS, EVDC
US Support Region

goatboy's picture

Thanks, that tested OK. With the CBTs stopped, I can find searches in SQL and reject searches in DA, but while I can find exports in SQL, I can't stop them in DA -  they are stuck in Preparing state until the CBT is started. I will think about the best way to move forward. Thanks for everyone's help!

Kenneth Adams's picture

I recommend starting the CBT long enough to stop the exports, then stop the CBT again.

 

Ken Adams

Backline Support for CA, DA, ACE, UCE, PSTD, ARMS, EVDC
US Support Region