Sybase 7.0.4 unresponsive, hangs clients

[Former Member]

We have our corporate application running using a Sybase 7.0.4 backend. Randomly the sybase db becomes unresponsive, causing any connected clients to just hang, since they cannot communicate with the database. There is no error anywhere in the event logs, the server is perfectly fine outside of this. The connections aren't broken from the clients, just unresponsive leaving our application in a precarious state. I know this isn't too much to go on but does anyone have any idea what could be going on? Is there any logging anywhere that might give some insight into what is going on?

[VolkerBarth]

Does sa_conn_info() list any blocked connections in these cases? In that case, sa_locks() might give some clues.

[Breck_Carter]

Upgrading to Version 11 or 12 should be considered. Historically speaking, versions up to 9 have had a history of "going catatonic" at some sites, for unknown reasons... at some of the affected sites it became accepted practice to simply reboot the server at the first sign of trouble.

I would not say this is a common problem with those earlier versions, but those who were affected did complain loudly 🙂

I'm sure you know that engineering support ended long ago for these old versions, which means you can still get technical help but if the problem is a bug that needs fixing, it won't get fixed.

And the part about "getting technical help" might not be easy... first you have to get past the "try upgrading" response.

[jeff_albion]

And the part about "getting technical help" might not be easy... first you have to get past the "try upgrading" response.

Technical support will work with you on your current version, but Breck is correct that technical support is limited by the fact that 7.0.4 is no longer in engineering support (it was out of engineering support as of March 31, 2005)

From: http://www.sybase.com/support/aboutsupport/policy

===================

For older versions of Sybase software, limited technical assistance will be provided, so long as you have a current support contract for that product, subject to the availability of trained personnel and resources. Limited assistance consists of helping customers with questions, workarounds and migration to a supported version of the product. It does not include new bug fixes or integration of any bug fixes into previous updates.

===================

You may wish to try the last 7.0.4 EBF (Build 3541) if you haven't done so already.

[justin_willey]

I remember problems related to running out of processor threads on v6 - I'll try to find the details and post them.

[justin_willey]

as far as I can see the problem we had was addressed by adjusting the values of the -gn and -gx server options - whether that will help you ...?

As Breck says, with an upgrade the problem may just go away.

[Former Member]

I will have the team try that. What exactly do those switches do? Do they require a value? If so how do we determine said value.

[justin_willey]

I don't have the v7 docs anymore, but from v8.0.2

-gn - Set the number of requests the database server can handle at one time. Default 20 (for the network server). You will need to try a figure appropriate to your user load and application.

"You may want adjust -gn based on Performance Monitor readings for Requests: Active and Requests: Unscheduled. If the number of active requests is always less than -gn, you can lower -gn. If the number of total requests (active + unscheduled) is often larger than -gn, then you might want to increase -gn."

-gx - "Set the number of operating system threads assigned to the database server process. By default, this is set to one more than the number of CPUs on the machine.

"You may want to increase the option setting beyond the default to reserve threads for external tasks, separate from standard database tasks. For example, you may want to add an extra thread for each concurrent connection using remote data access, or for connections using Java in the database to listen on an external port. In other circumstances, this option should be left at the default value."

[Former Member]

I found this since I posted. The only problem is under performance monitor in win 2000 I don't see ASA as an option in the objects list so I cant get the counters. Am I missing something, I checked every other object in the list and didn't see the counters from the doc...

[Breck_Carter]

Try asking about the Windows performance monitor problem as a new question. Historically there have been problems with the registry, especially with old Windows installations where the registry has been hosed up over time. I don't mean "old" as in W2K, I mean "old" as in "years and years since Windows was first installed."

[Former Member]

Thanks I will.

Let me ask you one more thing, for a starting point, if the box has 4 cores, would it be fair to test it with gx of 8 (since it must be defaulting to 5) and bump gn to 30 from the default of 20?

[Breck_Carter]

Sybase Central has "Performance Monitor" and "Statistics Items" tabs where you can plot Unscheduled Requests... I just looked and can't see Active Requests.

Foxhound works with V7 and shows those statistics in the "Active Req / Waiting Req".

IMO, however, threads and such like aren't the problem... the problem is something bad going on inside the engine. When threads are the issue, there is usually a message that talks about thread deadlock.

[Breck_Carter]

...same descriptions in the V7 docs.

[Breck_Carter]

You are taking regular backups, right?

RIGHT????

[Former Member]

of course we are taking regular backups...

[justin_willey]

I seem to remember a problem where the whole thing got stuffed up because there was no spare thread left when you tried to connect to find out what was happening. It may have been a java-in-the-database related thing. Do the v7 docs have the thread deadlock error message listed Breck?

[justin_willey]

That wouldn't seem unreasonable. It might not cure it - after all you may be looking at a symptom rather than a cause; but if the problem then occurs less frequently it might give you some clues as to what is going on.

It is a real problem trying to find out what causes hanging - by definition the engine can't tell you. With current versions you can get debuging versions etc from support, but I very much doubt that's the case with v7

[Breck_Carter]

Yes, V7 has SQLCode -306 (ordinary deadlock) and -307 (evil deadlock). Both of which are highly visible as connection error messages... they do NOT manifest themselves as catatonia.

[Breck_Carter]

I do not believe increasing -gn will stop a server from going catatonic. It might change throughput and latency (for better or worse) but "waiting for a thread" and "thread deadlock" are not the same as "server unresponsive".

[Breck_Carter]

How many active connections (connections actually doing work) are there?

What does the following query return?

SELECT DB_PROPERTY ( 'ConnCount' ), PROPERTY ( 'ActiveReq' ), PROPERTY ( 'UnschReq' );

[Breck_Carter]

...no offense intended. You would not believe the number of times the answer is "no".

[Former Member]

22, 1, 0 It will fluctuate

[justin_willey]

Ok - continuing my dispute with Breck 🙂 I've found some details.

It was related to the use of the Java vm.

These were the symptoms (actually in another user's description - our original issue was with an earlier version than 8.0.2 but I can't pin it down!):

---

One of our clients has a problem with the database which uses Sybase Adaptive Server Anywhere Network Server Version 8.0.2.4339. The CPU on this dual processor hyperthreading machine goes to 25% (full load on one virtual processor) and the database stops responding. When we try login, Sybase Central or ISQL just hangs. We looked at the transaction log file (converted to a text file) and looked at the log file produced by the -o switch but could not find something unusual.

The problem can occur at any time - we don't see any specific query or database operation which would lead to the problem. It seems to be quite random.

---

The solution was to set -gn to the max number of connections + 1

The reason (from Jason Hinsperger) was that "Any database operations which execute within the server java vm have a thread dedicated to their processing for the duration of the operation. This means that if you have several connections using java, while the server is processing these requests, it will be unable to service any other requests until the java related ones have finished."

I think there was also a specific issue with the Java vm 1.3 which did not occur with 1.1.

[Breck_Carter]

That's essentially "idle". I suggest running Foxhound against your database; perhaps it will show unusual behavior leading up to the period of unresponsiveness. You can get Foxhound to send you an email alert when the database does go unresponsive. http://www.risingroad.com/foxhound/index.html

[Former Member]

When this happens to us the entire system and all connected clients suffer the 'catatonic state' and doesn't come back... This description sounds like things are blocked for a short period of time, or is my condition the result of this problem?

[justin_willey]

With us (from what I remember and it's many years ago now), it stayed stuck. However it only happened because we were using the Java VM on ALL the connections (there were a number of Java functions). Quite why it got like that I can't say; the -gn change gave us a short-term fix and then we re-wrote the Java functions anyway, so it was no longer an issue.

If you AREN'T using any Java then this won't be your problem.

[Breck_Carter]

Are you using Java stored procedures? If not, then the scenario described by Justin probably does not apply... Justin doesn't like to talk about it, but he is a super-intelligent pan-dimensional being from an alternate universe constructed entirely from Higgs bosons, and any one of the problems he deals with on a regular basis with would cause mere mortals like us to implode into specks of nothingness 🙂

[justin_willey]

"constructed entirely from Higgs bosons" - this is deeply disturbing news - it seems there's still a distinct possibility that I don't exist.

[Breck_Carter]

Of course you exist... Dilbert discovered you!