Sybase Performance Problem - clients running too slow

[Mladen Despic]

We have Sybase ASE 11.9.2 on HP-UX 11.00
and some 500 clients running custom built apps all connecting to the same Sybase server port.

At peak times, clients wait up to 2 hours for certain transactions that should normally take less than 5 minutes to complete. Measureware/Glance and other performance data (such as network bandwidth) show no CPU, Disk, Memory, or Network performance bottleneck. We are suspecting issues with table locking.

Being unfamiliar with Sybase internals (or with any database for that matter), I've been trying to monitor system performance from Measureware and Glance.
I've noticed a very high 'select' system call rate (over 10000 calls per second), but I don't know if this is normal or not.

We are not aware of any tools that would tell us what's causing Sybase transactions to wait for so long.

Would somebody try to shed some light?

[Andreas D. Skjervold]

Hi
I'm not very familiar with Sybase , but think I have heard that Sybase (older versions anyway) have a poorer table locking functionality than for instance Oracle has.
If your 500 users perform transactions on the same table, they infact have to stand in line to wait for the table lock.
Oracle on the other hand has row locking and rollback segment functionality which maintain read consistency and update capability for multiple users.

I think?

Andreas

[Roger Baptiste]

<>

Seems to be a database
issue.

<< We are suspecting issues with table locking. >>

Most probably, yes.

<>

On a 3Tb oracle database
system, i see around 4000-7000
select system calls per second. The select system
call is looking for which
of its arguments file descriptors is ready to
read/write/execute.

To drill this further,
look on what this processes
are waiting on. Glance->s->w
If it''s cpu wait percentage
is huge, turn the problem
over to the sybase dba ;-)
There isn''t much you can
do about it from the sysadmin
perspective.

-Raj

[Mike Hassell]

Mladen,

I would say this is probably related to poor database design. I myself am not a DBA, however I have been working with Sybase ASE 11.9.2 for about a year now and have learned a few things in the process. Sybase ASE 11.9.2 has "row" level locking which is a huge upgrade in performance over ASE 11.0.

From a system administartor perspective you should be looking into the large amount of system calls and or forks as evidence that the application itself is performing poorly during peak times.

If you have a login to your production database that has "sa" (basically the root user of the database) then you can use a perl script like 'sybmon.pl' to monitor the database as it works. This util acts somewhat like Glance Plus, but on a more basic level and will show you the active locks and blocks in the database as they occur, giving you even more evidence of why the application is performing slowly. Sybmon.pl requires perl to be install on your box as well as a module or two, but as I recall it was somewhat easy to install and has been a handy util for myself, be sure to take a look at it:

http://www.isug.com/Sybase_FAQ/ASE/section9.html#9.3.6

Hope that helps a bit.

-Mike

[Larry O'Reilly]

You should execute sp_sysmon during those periods. That output will give you a very good idea where the problem is. There is lots of output so you will want to send the results to a file or use the section option to look at specific elements such as locks or taskmgmt. Syntax is sp_sysmon interval [,section [, applmon]]. For example, sp_sysmon "00:10:00",taskmgmt

If you know the specific sql or stored procedure that is running slow, try using showplan to determine whether the query plan is good. Look for table scans. Also, set statistics i/o on and look for excessive physical or logical i/o.

[Henrique Silva_3]

Being familiar with both sybase and oracle and being a sys admin also, I must say that your problem has nothing to do with sybase per see, for sybase is very fast and reliable and has many features that Oracle does not.

This is clearly a problem with database design. Do not waste your time trying to figure it out what is going on on the system side, for sybase is like an OS within an OS, so, as long as your OS is tuned for I/O and CPU usage, depending what else is running on this box, then your tuning should ALL be done on the database side, and that includes talking to your developers to see what kind of queries they are running and setting up indexes on such tables, partitioning table if everyone is looking at the same table, to eliminate contention, etc,etc. A good sybase dba should be able to run sysmon on the database or analize the queries and start fixing things right away.

Cheers,

Henrique Silva
IT Engineer
UST R&D Lab
HP Burlington, MA

[Mladen Despic]

Thanks to all who replied.
I've forwarded your suggestions to our database administrator.

I have another question about syscalls: Should the 'select' syscall rate somehow depend on the size of the database? Our database is less than 30Gb in size, the 'select' syscall rate is usually between 5000 and 7000 calls per second, although it is occasionally over 10000 calls per seconds.

Any further thoughts on this?

[harry d brown jr]

There are patches available that address sybase issues. I would suggest that if you haven't already, install the Sept 2001 patch bundles.

live free or die
harry

[Philip Chan_1]

The "select" sys call from the dataserver process meaning that it has issued an asynchronous I/O on disk device and is awaiting a reply. In fact, the dataserver process is looping on the "select" call waiting for a reply from the disk. The looping frequency is controllable in Sybase and is governed by the parameter "runnable process search count".

Do sp_configure "runnable process search count" and see what values has been entered. If this is not your problem then potentially your system may have problem with async I/O. In that case patching the OS may help.

~Philip