Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Sybase databases problems with performance

Boris Gegenheimer
Occasional Contributor

Sybase databases problems with performance

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.
I am what i am
9 REPLIES
Stefan Farrelly
Honored Contributor

Re: Sybase databases problems with performance


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 ?
Im from Palmerston North, New Zealand, but somehow ended up in London...
Dietmar Konermann
Honored Contributor

Re: Sybase databases problems with performance

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.
"Logic is the beginning of wisdom; not the end." -- Spock (Star Trek VI: The Undiscovered Country)
Boris Gegenheimer
Occasional Contributor

Re: Sybase databases problems with performance

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
I am what i am
Pete Randall
Outstanding Contributor

Re: Sybase databases problems with performance

SYBASE=Sorry_You_Bought_A_Slow_Engine

;^)

Pete

Pete

Re: Sybase databases problems with performance

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.
Boris Gegenheimer
Occasional Contributor

Re: Sybase databases problems with performance

We are running the update statistics job every week but the problem remains.
I am what i am
Sandip Ghosh
Honored Contributor

Re: Sybase databases problems with performance

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
Good Luck!!!

Re: Sybase databases problems with performance

Have you run sp_sysmon? That should give you an indication where the problem is.
Subraya Pai
Occasional Visitor

Re: Sybase databases problems with performance

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