All Connections Blocked (Server Hangs) - ASA 12

[Former Member]

We have been experiencing a strange issue since we upgrade from ASA 9 to ASA 12 in November. Periodically the server freezes. By that I mean that all databases attached to the server are non-responsive.

When they come back, looking in the SQL Monitor shows that all connections on one of our production databases were locked on an certain query ( and it's different every time ).

Does anyone have any ideas on how I can trouble shoot this problem, or have you experienced anything similar.

Thanks

[jeff_albion]

Which specific build and version of SQL Anywhere 12 are you using? (dbsrv12 -v)? Do you have an ability to apply an EBF?

[Former Member]

12.0.1.3817

Yes, I can apply an EBF if absolutely needed. We run in a 24/7 production environment, but a few minutes of downtime wont hurt.

[jeff_albion]

By that I mean that all databases attached to the server are non-responsive.

And just to confirm, this means you have tried connecting over dbisql over shared memory and you cannot log in to the database during these times? And any connected clients are now 'waiting'?

Does anyone have any ideas on how I can trouble shoot this problem

Yes, in technical support we have tools to take memory dump captures of the database server in order to determine exactly what is happening during these 'periods of inactivity', even when other clients are not responding - however, you will need to have a technical support plan in order to obtain these tools and have the output analyzed. Do you have a technical support plan with us?

Yes, I can apply an EBF if absolutely needed. We run in a 24/7 production environment

You're running a more recent version, so that certainly cuts down on fixed bugs - but admittedly, not the currently latest patch. In this case, I assume you would rather have a 'smoking gun' for the issue before you would be advised to patch. If so, you would definitely be advised to open a technical support case.

When they come back, looking in the SQL Monitor shows that all connections on one of our production databases were locked on an certain query ( and it's different every time ).

Have you been able to determine if just running this SQL statement right after this issue happens can reproduce the blocking issue?

[Former Member]

1. Correct

2. I don't think we have a support plan ( I am double checking though ).

3. A smoking gun

4. Running the same SQL, or executing the same code within the app does not reproduce the problem, even when done immediately following an episode.

Thanks for your help. Happy to answer any questions if it gets me any closer. What is the procedure for opening a case if we don't have a support plan. Years ago I opened a 1-time support case for an issue, but I can't tell if that is offered anymore.

[jeff_albion]

Years ago I opened a 1-time support case for an issue, but I can't tell if that is offered anymore.

With Sybase's formal integration into SAP, credit-card "one-time" technical support cases are unfortunately no longer offered. You should speak with a sales representative about your options on obtaining an SAP technical support plan:

http://www54.sap.com/services-support/support.html

If this is related to a third-party vendor's product that has integrated the SQL Anywhere database server, you should first contact the third-party software vendor for support assistance.

[Former Member]

We had the same problem when switching one of our customers to SA12. We opened a support case and had a lengthy discussion about this being an error or not. But that's another story.

In the end raising the minimum for the multi programming level to 10 ( -gnl 10) remedied the problem. The default for this setting is the number of logical cpus you have licensed and are available to the engine. This means that when there is a period of low activity the engine reduces the multi programming level down to e. g. a level of 2. If then 2 connections "suddenly" issue a long(er) running statement, the server didn't respond to any request for up to 60 seconds in our case. This "hangs" happened up to 2-3 times per hour and our customer was not "amused" to say the least. In my opinion this default is just too low, at least for our application, so we now always set this option to 10 on every server with "Dynamic tuning of the multiprogramming level" ( >= 12).

[Former Member]

Looks like our current multi-programming level is set to 20 with a min /max of 80/20.

I will research these setting more though to see if these setting seem correct. Thanks for your response.

Steve

[MCMartin]

I wouldn't say it's a bug, but I would see this as a design flaw too.

[VolkerBarth]

Interestingly enough, I have never seen the current MPL go down to the minimum value, even on idle test machines with no queries within hours and days... - they seem to stay around 10-14 when the defaults are used (i.e. minumum = 20, default = 20, maximum = 80).

[Former Member]

The help says that the default minimum MPL is not 10 or 20 as you stated, but:

The minimum of the value of the -gtc server option and the number of logical CPUs on the computer.

Afaik number of logical "and licensed" CPUs would be more precise, btw.
In our case a virtual server was used with only 2 logical cpus, so the mpl really dropped to 2. We used the -gn option before to increase the number of available threads. But this setting is just a starting point since version 12. The real MPL can only be seen in the server message log when you add the "-gns" option ('AutoMultiProgrammingLevelStatistics'), to quote the Sybase support contact (Jan. 2012).

[VolkerBarth]

Yes, Markus is correct, the default value for the MinMultiProgrammingLevel property is not 20 but 2 in my case... However, as stated, I think this value has never been used as current value...