Sybase using 100% of CPUs.

[PatRoy]

Hi. We're running HPUX 11.31 with Sybase 15.0.3 and a total of 8 dataserver engines. The server has 8 cores. 48Gbs of RAM.

Now, I'm no DBA, but I am wondering... 8 engines seems to take ALL of the CPU power. Approx 100% for each engine, totalling 800% usage! Is this normal behavior for Sybase? The DBAs reduced the number of engines to 7, therefor, 7 cores are fully used and 1 is kinda free. Making an average CPU % utilization of 88%.

The "real" problem we're having is that they are about 40 others Windows servers running Sybase 12.5 I believe. They've got a process copying data from all the 12.5 sybase servers to the hpux, sybase 15 server. This particular process takes about 3 times the amount of time it took one week ago. We just moved to HPUX from Tru64 last week. I've checked networking and it seems fine to me!

Results I got from 2 HPUX 11.31 servers, GigE cards were the following using netperf:

TCP STREAM TEST from 0.0.0.0 (0.0.0.0) port 0 AF_INET to NCRCIPSE.pptc.gc.ca (10.1.1.54) port 0 AF_INET
Recv Send Send
Socket Socket Message Elapsed
Size Size Size Time Throughput
bytes bytes bytes secs. 10^6bits/sec

32768 32768 32768 10.01 658.96

Unfortunatally, it's impossible for me to install netperf on one of the 40 Windows server, so I quickly test with a simple FTP of 2 files (115MB and 260MB):

116MB file:
ftp: 116129984 bytes sent in 3.72Seconds 31226.13Kbytes/sec.

261MB file:
ftp: 274409472 bytes sent in 4.34Seconds 63169.77Kbytes/sec.

Seems kinda normal, right?

I just don't know what to look at anymore. I'd first like to eliminate the CPU utilization thing... find out if it's normal, or not.

Thanks so much.

Patrick

[PatRoy]

and also, regarding engines cpu utilization, why is it that one engine won't go higher then 100% even when there is more CPU available? I can't use more than 1 CPU or what?

[PatRoy]

Here's a Glance screenshot.

[TTr]

Something is really not right with your environment. The dataservers should not use 100% CPU unless the database is extremely busy. Some things to check on the UNIX side. How does the rx HW compare to the older compaq? What does the copying of data translate to as far as database activity? Is it some kind of report that requires a lot of CPU? Do you have the recommended setup for the kernel as far as sybase? Did you use the correct sybase software version for itanium? I would ask the sybase DBA to weigh in as well and contact sybase support.

[PatRoy]

HPUX Server:

Model = ia64 hp server rx6600
uname = HP-UX NCRCI B.11.31 U ia64
EVA = EVA6100
NICS = GigE
CPUs = 8 cores
RAM = 48Gbs (35 mlocked by Sybase)
Swapinfo =
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 10240 0 10240 0% 0 - 1 /dev/vg00/lvol2
dev 38912 0 38912 0% 0 - 1 /dev/vg01/swap
reserve - 36345 -36345
memory 46720 44195 2525 95%
total 95872 80540 15332 84% - 0 -


===============
Tru64 Server:

Model = HP Alpha Server GS80
CPU = 4 cores (I think)
RAM = 8GBs
EVA = EVA6000
NICS = 100Mbs (I believe! I think it had a GigE card, but they never used it)

* I don't have access to the old Tru64 server..

===============
Sybase info on HPUX:

Adaptive Server Enterprise/15.0.3/EBF 16376/P/ia64/HP-UX B.11.23/ase1503/2669/64-bit/FBO/Wed Nov 12 06:24:26 2008

Obviously, the UX box should be A LOT faster than the Tru64 one..

Has for the "how" the data is copied over... i.e. "What does the copying of data translate to as far as database activity?"... I need to check with my DBA...

Thanks. P.

[Ishrat]

I have seen this behavior when table scans are being due to inefficient queries. Make sure the index statistics are upto date. Table scans are CPU hoggers.

[Bill Hassell]

100% CPU for a dataserver is perfectly normal for extremely complicated SQL code. Only your DBA can tell whether it is terribly inefficient or running normally. Ask your DBA to run stats on the database engines.

> why can't one dataserver use more than one CPU?

It's the same answer as: why can't 9 women have a baby in just one month? Each dataserver is a single thread or process. Unless the code is written to utilize multiple CPUs, 100% CPU usage is the most that a dataserver will get. Sysadmins thet run a lot of big Java code will see one process using 120% CPU due to parallel threads.

As far as networking, a slow network will reduce your CPU usage because data is too slow to keep the machine busy. You as a sysadmin cannot fix the CPU usage -- the dataservers are doing exactly what they were told to do. Run less dataservers or buy more processors. But usually a good database expert will see bad database and SQL design and easily improve performance 2x-10x. I have seen Oracle run 10x faster on a PC with "exactly the same code". Turns out that the indexes on the the HP box were badly out of balance and searches were partial or sequential rather than indexed (same database). By running stats, the PC would read 2 thousand records while HP-UX would read 2 million for the same query. After a re-balance of the indexes, the HP ran about 3x faster.

Normal network speeds are about 60%-70% of the wire speed so for Gigabit links, 60 MB/sec is about normal for local links.

[rick jones]

As "Mr. Netperf" I will not pretend to know much about databases. I will say thought that when presented with a system where a process is consuming 100% of a CPU and I don't know that it should, I might look at the system calls it is making with glance or perhaps a tusc trace.

Now, as "Mr. Netperf" I will point-out that netperf should compile and run just fine under Windows, and it should be possible to run tests between Windows and HP-UX with netperf. Given the number of platforms on which netperf runs I don't produce nor keep binaries. If you do obtain a binary from a trusted source, while mis-matched versions between netperf and netserver may sometimes "work" I don't "support" that sort of configuration.

WRT the throughputs you see with netperf and FTP, apart from available CPU, I would look at snapshots of netstat -s -p tcp stats from before and after the netperf or ftp test, and run them through "beforeafter" from ftp.cup.hp.com under dist/networking/tools and look at the retransmission rate. If there was much of a retransmission rate - even 1% can have a measureable effect on throughput - I might move down the stack and look at lanadmin statistics on the HP-UX side. Depending on what those showed, I might work my way across the network to the other side, checking switch port stats along the way.

[PatRoy]

Okay, as for what the copy entales, here's the answer I got from my DBA:

"Foldermov runs on the WIP (regional server). It pulls data from ASE on that server and saves it on HPUX ASE. Data volume is huge and the protocol is chatty (it deletes stuff from tables, then inserts rows here and there, on some 40+ tables). The volume is coming from the images transferred by Foldermov".

As for testing netperf no windows... reason I can't test it on there, is only because of security concerns. We are not allowed to install anything on theses servers... Otherwise I would of compiled it for windows as well for sure. Anyways, I was sure networking was fine. Even checking out the stats in lanadmin would show no errors what so ever...

ANYHOW, apparently, the DBAs might have found the problem late last night. And I quote:

"A few things were changed, but we are thinking the one that might have made the big change, was setting something called partitioning (can you believe it!!) on the default data cache inside Sybase ASE."

Cool! It's NOT an UX issue afterall as I suspected! If anything else comes up, I'll update this thread ;)

Thanks so much to all!

Patrick

[Duncan Edmonstone · Accepted solution]

ahh the joy of working with DBAs and application admins...

"We didn't change nothing"

then after you do a *pile* of work proving the OS is functioning as expected this becomes:

"oh well we made a few minor tweaks, but they really shouldn't effect anything"

then when you challenge them on this you finally get to:

"ah actually we made a change which completely alters the behaviour of the app"

for some reason it never seems to work in the other direction does it?

Duncan