CPU Usage on SQL Anywhere 12

[former_SQLA_member1694898]

We have two similarly sized 12 DBs running on identical Sun Solaris Sparc quad CPU boxes. These are pretty stout units with 32G of RAM and 4 processors. When I do the prstat command to see what processes are using the CPU, I notice that in idle times (middle of the night - pretty much 0 users on the system) the one box reports that dbsrv12 is using about 1.3 percent of the CPU while on the other box it reports that dbsrv12 is using 10 - 15 percent of the CPU all the time. I have tried to see what traffic is going in and out of port 2638 on the high CPU machine and it appears to be negligible - a simple query here and there over 5 minutes. How can we determine what the DB engine is doing when it is showing 15% CPU in the middle of the night. We just uprgraded to the latest EBF and the problem persists. If I look at the connections, there doesn't appear to be anything doing anything. What is the best way to determine what queries might be running or what is taking up the CPU? Perhaps this is normal? It doesn't appear to affect performance any. The engine still responds instantly to any request.

[MCMartin]

On the busy box do you have any connections with name starting INT:... ?

[Former Member]

In addition to the above information ...

Watching just the TCP/IP traffic on the default port may not be 'all inclusive'. The server can also process requests submitted via connections made over shared memory as well. It can also service http requests if you have specified a -xs listener. It is even possible to automatically listen on other ports if the default port was in use.

In addition to the 'maintenance type' tasks noted earlier, you could be running scheduled events, and maintenance plans defined and firing off at those times. These will show up as connection activity.

If this is a problem for you, you can try capturing information using a mininal level of request logging or tracing to see what is really going on here. Additionally, if enabled, you can trace the HTTP requests via the logging opton in the -xs http{...;logfile=...;... } listener setup.

HTH

[jeff_albion]

Without knowing anything about the actual database load, there are indeed defined background processes that the database will try to run in 'idle' times, to try and take advantage of 'quiet points' in the database query load:

• The Database Page Cleaner
• The Statistics Governor
• Checkpoints

---

It doesn't appear to affect performance any. The engine still responds instantly to any request.

I would say this is most important observation of all - does this background behaviour affect what you need the database server for?

[former_SQLA_member1694898]

We fixed the problem. It appears to have been caused when we brought down the database but did not stop an apached tomcat server that had connections to the DB. We decided to bring the DB down again and make sure every program that connected to it was stopped as well. Once we did this, everything came back up and the CPU went down to normal. It seems the problem had something to do with an apache tomcat connection pool that had stale connections in it.