Crystal Reports 2008 Server, CMC log in slows over time

[Former Member]

We have Crystal Reports Server 2008.  This is the second time I have dealt with this issue and I have no idea how to resolve this or what causes it.  Over time, logging into the CMC web console gets slower and slower.  Same thing when opening the crystal reports 2008 client tool, logging into the "enterprise" server (our server), then once I expand the enterprise tree, it take almost ten minutes for our reports to show up.  Same thing with the CMC log in, it takes that long to log into the console.  Once open clicking on various things is a little slow too.  Even worse, if I go into the Infoview page, log in, once in I try to navigate publications, it's super slow.  Once a reports is open, the report runs fine.  Any scheduled reports seem to run just fine in the background however it's almost unusably slow when we try to log into the CMC over web UI or through the crystal client by logging into enterprise or using infoview looking for reports or publications.  The server operating system is windows 2008 and the default install was chosen with MySQL.  The server OS performance is fine, all resources are showing a low utilization percentage.

In the past, I fixed this simply by reinstalling the whole server, manually re-uploading reports and recreating publications and schedules.  I really don't want to do this again.  There must be an explanation to this behavior and a way to resolve.

Any help is greatly appreciated.

[former_member191664 · Accepted solution]

How many XI3.1 objects are there in your CMS repository?  If it is > 100,000 objects, try add the following to the end of the command line for each CMS you have in the XI3.1 cluster to see if this improve the CMS query performance.

-maxobjectsincache 100000 -threads 150 -nbqthreads 10

2nd, a CMS can manage no more than 400 concurrent login sessions as sizing guide mandates.  Thus, add one more CMS node if you have a large XI3.1 environment.

Also, check the release notes for XI3.1 Service Pack7 and before your current XI3.1 version for the fixes on CMS query performance.

Regards,

Jin-Chong

[Former Member · Accepted solution]

Here is what I can show you, hope this helps.  Let me know what else I can provide.

[DellSC · Accepted solution]

That is a LOT of failed jobs.  I would go to the Instance Manager in the CMC, filter to failed jobs, and start deleting failed jobs.  The problem is that you have over 12 million of them, so it's going to take forever to do them manually with up to 500 at a time (depending on how you've set your CMC preferences...)

You could also write either Java or .NET code using the SDK to automate the process of doing this - it wouldn't be too difficult to write.

-Dell

[Former Member · Accepted solution]

Ok, sounds like a good idea.  I tried to do a "find" but it runs forever and returns 0 results, so I think it is timing out, this seems to be part of the same initial problem.  Looks like I may have to do a mass delete using code like you suggested.  However, how do I do that?

[DellSC · Accepted solution]

Java or .NET?

-Dell

[Former Member · Accepted solution]

probably .net, I have a programmer who uses that language.

[DellSC · Accepted solution]

Ok, the programmer will have to install the .NET SDK from the Client Tools install for CRS in order to start.  He'll need to use Visual Studio 2010 or newer.  I have some sample code on my website for the basics of using the .NET SDK: http://www.dellstinnett.com/sample_code.html. Specifically look for "BI 4.0 fp3, Visual Studio 2010" under ".NET SDK Tutorials".  This will provide the basics for logging in to the system and querying the CMS.

You will need to decide on a date for the earliest instance that will be kept in the system.

The basic flow for the program will be this:

1.  Log in to the CMS.

2.  Start Loop

3.  Run the following query:

Select top 10000 SI_ID from CI_INFOOBJECTS where SI_INSTANCE = 1 and SI_SCHEDULE_STATUS <> 9 and SI_CREATION_TIME < [earliest date to keep]

This will get the first 10000 instances that are not schedules.  (Default without "top 10000" is only 1,000 objects.)

4.  For each InfoObject in the result set, call .DeleteNow().

5.  End loop.  Break out of loop when there is nothing returned from the query.

6.  Log out of the CMS

-Dell

[former_member191664 · Accepted solution]

On top of what Dell recommendation to remove the existing failures from CMS repository, couple things need to consider when all errors are cleared.

1. You are using MySQL on localhost:3306 for CMS repository.  Consider to setup an Oracle database for your CMS repository on another DB server to offload the database load from your BOXI3.1 server host,

2. there must be some recurring jobs that keeping failing, and you'd want pause or delete those trouble maker from clocking your CMS again later.

Regards,

Jin-Chong

[DellSC · Accepted solution]

I'm going to change the query to just pull failures instead of all instances prior to a specific date.  The query should be:

Select top 10000 SI_ID from CI_INFOOBJECTS where SI_INSTANCE = 1 and SI_SCHEDULE_STATUS = 3

For this you won't need a date.

-Dell

[Former Member]

Ok the issue has been resolved, thanks to all of you actually.  I had to change the limits on everything uploaded to the server.  This slowly started to remove the failed jobs.  Then once enough were removed the timeouts were not as bad.  This in turn allowed the .net application to run successfully and delete failed jobs without timeout errors.  I now have 0 failed jobs out of over 12 million and the cmc and infoview web gui is now moving swiftly.  I think you all for your help, I gave you all a helpful answer mark.  There is no actual "Correct Answer" here.

[Former Member]

We are having a hard time with the delete commands.  A slew of errors come up, the most common is command timeout.  We think we identified the problematic report or publication with all the failures however every time we try something it times out, from any method.  We used the .NET program to send the commands.  Any other ideas?

[Former Member]

You can set the Instance Limits on the folder in which the problematic report is present to say around 10. Then to activate the limit, just scheduled the report just once(Recurrence -> now). This will activate the instance limit and BO will start deleting all the old instances. Of course that will also take time.

[DellSC]

In your code, how many InfoObjects are you querying for in the same batch?  Thinking about it, in this case I might cut down the number of instances to a couple hundred at a time instead of the 10,000 I posted above to see whether that will speed up the query so that it doesn't time out.

-Dell

[Former Member]

My programmer tried just one at a time, he said after 11 minutes it timed out again.  COMException (0x80040808): Transport error: No response from server, timeout exceeded.

Is there a way to increase the timeout time?

[Former Member]

Thank you, I have tried your suggestion, I guess we will see if it helps.  However FYI, I have even tried deleting the publication, and it just sits and spins never getting anywhere and never deleting.  The instance however in instance manager has been deleted.

[Former Member]

So far we have went from 12 million to 6 million failed jobs after setting the maximum instance limit.

[Former Member]

Good which means CMS is attempting the deletion in batches. Is the count still declining?

[Former Member]

so far yes, the count appears to be continually declining, slowly over time.  Would turning off auditing help speed things up as well?  If so how do I turn it off?

[Former Member]

U can stop auditing through CMC -> Auditing :-

[Former Member]

I don't see that feature, yet auditing says it is enabled on the settings page.  See first screen shot above.

[Former Member]

Sorry.It is in the drop down and not in Application. Check my edited post.

[Former Member]

sorry but I still don't see it....

[Former Member]

You don't seem to have rights to many applications. Do you have any other admin user which have rights to full list of applications?

[Former Member]

no, that is the master administrator account.  The only admin account.

[Former Member]

Was your installation successful without any error?

[Former Member]

Yes it was, no errors that I can remember but this was a couple years ago now.  None the less, I think we got it.  It was a combination of solutions.  I will reply to the main post with more details down below.

[Former Member]

Great, thank you all, I will give this a try and report back or with questions.

[former_member11696]

Moved to Bi Platform forum