Sybase 11.9.2 and HPUX 11.0

[Robb Bailey]

I am running Sybase 11.9.2 on an L1000 with 2 440 MHz processors, 1GB RAM, 1GB swap, HPUX 11.0 . I am having terrible performance. If I use glance to watch processes, my memory usage stays at around 99%. I think I need to adjust the kernel parms, but Sybase was installed with a vendor app so I have no docs.

I am running Oracle 8.1.6 on an NClass, 3 360 MHz processors, 1GB RAM, 1.6 GB swap, HPUX 11.0. This is a much larger db that is running fine. I am including the differences between the kernel parms.

The parms are currently L1000 NClass

db_max_pct 50 20
ksi_alloc_max 2208 32928
max_thread_proc 256 64
maxfiles 60 2040
maxfiles_lim 1024 2047
maxswapchunks 512 832
maxuprc 75 150
maxusers 32 512
ncallout 2064 4132
nclist 612 8292
ncsize 1500 5820
nfile 910 7822
ninode 476 4796
nkthread 2048 7219
nproc 276 4116
semmap 66 72
semmni 64 70
semmns 128 640
shmmax 1000000000 1073741824

Should I change them to more closely match? I did get a file table full message in syslog. If I reboot the machine, preformance is better for a time, but degrades. Are there patches that I need? I would greatly appreciate any guidance. I have contacted the vendor and searched the Sybase and ITRC sites and have not found any concrete suggestions.

Thank you,

Robb Bailey

[Sandip Ghosh]

Looking at your Kernel Parameters it seems that first you need to change the dbc_max_pct to 15%. This is must.

The other parameters like nfiles,nproc,maxuprc and maxfiles needs to be change in the higher side. Look at sar -A or simply sar -v and accordingly change your parameters.

Sandip

[Mark Greene_1]

in addition to the kernel params, take a look at the ps output. How many instances of the Sybase server are running? Are you running a replication server too? Are there any dbcc's or dumps that are running/hung?

HTH
mark

[Martha Mueller]

I feel your pain. We had this same problem when we upgraded Sybase 11.0.3 to 11.9.2. Turns out that Sybase changed the optimizer in one of the in-between versions. We had lots of people from both Sybase and HP in here trying to figure it out. When we finally found out that it was a change to the optimizer, we had to go through most of our code and rewrite a lot of it. In some cases, the DBA's and programmers had to force the queries to use a particular index. There were a few Sybase changes that involved setting some flags.

Good luck.

[Johan Bergwitz]

You can look at performance from Sybase's point of view by the built-in procedure sp_sysmon (from a isql-window). The interpetation of the output should provide a good point in the direction where to look.

If your server is only a database server and Sybase is running on rawdisks you should be able to reduce the buffercache (db_max_pct) to 3-5%. The memory is better used by Sybase.

Also, if you have 99% utilization of the memory you might experience swapping from time to time? Do a 'ps -ef' and look for exessive runtime for the process vhand. Another point of directions could also be the "Global Waits" and the "System Table" view in Glance.

Regards
Johan Bergwitz