topic Re: Sybase databases problems with performance in Operating System - HP-UX

Sybase databases problems with performance

Boris Gegenheimer — Mon, 14 Oct 2002 05:54:26 GMT

Hello i have a customer that runs Sybase 12.0 now and he previously ran 11.5.1
He went about it in this way he exported the databases then installed 12.0 then built the databases and then imported the information in the other databases to the new ones. The problems that has arisen since are performance problems the system is working much slower than before the upgrade.Has anybody encountered the same problems? My customer runs K-class servers with a Serviceguard cluster 11.00 HP/UX with the latest patches. Is there any kernel parameters that should be different when you move from 11.5.1 to 12.0?

Thank you for your help in advance if you need more info just give a hint.

Re: Sybase databases problems with performance

Stefan Farrelly — Mon, 14 Oct 2002 06:08:40 GMT

Theres not much to go on here. Is Sybase 12.0 now more cpu intensive or more i/o intensive or more memory intensive ?? give us a clue.
Also - is Sybase 12.0 32 or 64bit ? and 11.5.1 ?

Re: Sybase databases problems with performance

Dietmar Konermann — Mon, 14 Oct 2002 06:12:36 GMT

Hi, Boris!

Were HP-UX patches installed along with upgrading to Sybase 12?

I yes, then you should check if the asyncdsk driver is really used by the database (quick-check: fuser /dev/async).

Beginning with PHKL_21535 (superseded by PHKL_27919) you need MLOCK priviledges to use asyncdsk... see the patch's special installation instructions.

Guest a guess...
Dietmar.

Re: Sybase databases problems with performance

Boris Gegenheimer — Mon, 14 Oct 2002 06:51:01 GMT

1. It looks like it has become more cpu intensive the memory is not affected.
2. It is the 32 bit op system and Sybase version.It is all 32 bit.
3. There are some instances using the async driver but i could not determine if it was the database.
4. The patches you suggested are not installed so this could be a reason.

regards Boris

Re: Sybase databases problems with performance

Pete Randall — Mon, 14 Oct 2002 09:31:21 GMT

SYBASE=Sorry_You_Bought_A_Slow_Engine

;^)

Pete

Re: Sybase databases problems with performance

Larry O'Reilly — Tue, 15 Oct 2002 11:59:21 GMT

I believe the optimizer was changed between 11.5 and 12. If you haven't run update statistics, you need to run that on all your tables or at least the large ones. Then use recompile or drop and recreate your stored procedures. Update statistics should be run regularly on tables that have a lot of inserts or updates to indexed columns.

Re: Sybase databases problems with performance

Boris Gegenheimer — Tue, 15 Oct 2002 12:06:59 GMT

We are running the update statistics job every week but the problem remains.

Re: Sybase databases problems with performance

Sandip Ghosh — Tue, 15 Oct 2002 12:24:27 GMT

You can look for the following:

1. asyncdsk driver must be included in your hpux kernel.

2. shmmax hpux kernel parameter should be equil to or bigger than your physical memory size.

3. check your sybase memory -> sp_configure "total memory", don't leave it at default which is too small for good cache peformance.

4. check out the runnable process search count -> sp_configure 'runnable process search count', if it is not at default then change it back to default.

5. while your machine is slow, run "sar -Mud 2 9" to see if you ran into CPU or disk bound situation. If disk bounded, go back to your sql program to make sure they don't cause table scan problems. If CPU bounded then you got whole lot more work to do. Also run "top" to make sure there are no other program that occupied the CPU.

6. make sure no livelocks in Sybase, while your machine is slow check output from "sp_who", look at the "block by" column.

Sandip

Re: Sybase databases problems with performance

Larry O'Reilly — Tue, 15 Oct 2002 12:29:27 GMT

Have you run sp_sysmon? That should give you an indication where the problem is.

Re: Sybase databases problems with performance

Subraya Pai — Mon, 04 Nov 2002 07:43:57 GMT

Hiya,

Try changing the CONFIG parameter TCP NO DELAY to 1.

On ASE12.0 by default it is 0, which means ASE would wait until the TDS packets are full.

For apps atht use small TDS, may benefit significantly by turning this off.

This is a static parameter in 12.0 and needs a recycle.

Cheers