Odd performance problem using Sybase ASE on HP/UX 11.0

[Ravi Kosaraju_1]

We have a production Sybase ASE (12.5.2) server running on HP/UX 11.0 64-bit with 8 cpus. One of our month-end activity involves purging large amounts of billing data (about 1.6 million rows) from various tables. This "purge" is invoked via a stored procedure. Generally the it takes about 1 hour to run. However, lately the purge process seems to "hang" (we waited as long as 4 hours) with the i/o rate dipping down to about 1 i/o every 10 seconds. The only way around (we thought) was to run update statistics prior to running the purge but that too seems to not really fix the problem.

This problem does not exist on any other server (development or QA, both having similar server configurations except that the production server where the problem occurs is running on an FC-60 array whereas the dev/qa servers are using the MTI array.)

When I opened a case with Sybase, they seemed to indicate that this could be an hardware issue on HP.

Any thoughts/ideas?

Thanks

[A. Clay Stephenson]

Unless someone is really able to use "The Force" to diagnose your problem, some metrics would really help. I would gather some Glance data both when the system is performimg well and performing poorly. Are you running raw or cooked i/o? When the physical i/o rates are so low, what are the logical i/o rates?

One thing to check is are the indices on production and test exactly the same AND are the data volumes comparable? I have seen cases where deletes slow to a crawl on indices with highly duplicate data. For example, if you have many, many duplicate dates. Some databases implement duplicate B-tree entries using secondary linked-list so that a delete by date has to find the B-tree entry and then traverse the linked-list until it finds the matching rowid, for example. One workaround, if this is the problem, is to create a composite index of date and some other column (possibly rowid) so that there are no duplicates and all the searches take place in the B-tree itself.

Without data, this is a snipe hunt.

[Steven E. Protter]

Attaching a set of scripts I describe as "The Force"

They collect data so you can figure out where the bottleneck actually is.

SEP

[Ravi Kosaraju_1]

Hi A. Clay/Steven

Thanks for the reply and the attachment. I will get our Unix admin to run this the next time we have the problem, the exact timing of which is a bit unpredictable since updating the statistics on the db seems to ease the problem a bit.

We are using raw disks for all the devices, no file system devices. I will check the i/o rates the next time the problem recurs.

As for differences between the test and prod envs, from a db perspective, I have loaded the exact prod copies into the test servers so the indices, tables, PKs etc all should match. Not sure what the Sybase implementation of B-trees is, our clustered index does have duplicate entries. This however has not changed over the past 3 years so I don't suspect that being the culprit.

I do understand that my question is a bit too generic and it may not even be an HP/hardware issue. The scripts that Steven attached are definitely a right step for us to proceed.

I will post any results that I could find once we encounter the problem again.

Thanks again

[A. Clay Stephenson]

Okay, now that we know it's raw i/o, are you using the 'rdsk' or the 'dsk' device nodes?

As a shot in the dark, check the timeslice value. If it is set to 1, you could see the kinds of problems that you describe although I am really leaning towards a software problem; but again, w/o data I am using "The Force".