SQL Anywhere monitoring tools

[Former Member]

We just had an incident at a customer's site Monday evening where a SQL Anywhere 12.0.1 database that had been running since February 15, 2015 suddenly started having performance issues.  The entire system was not impacted but one critical area that sends data from our system to another system started to fall behind.  This customer runs a 24x7 distribution center and I did not want to bring them down so we kept trying to figure out what was going on.  Part of that was stopping and restarting the application that sends the data.  That would help for a few minutes but messages would begin to back up again.

One thing we noticed is the database engine was consuming 80 to 90 percent of the CPU consistently which is not the behavior we normally see.  The database engine runs on a VM computer by itself.  No other applications are on this computer.

After a few hours of this, I decided to punt and asked them to allow us to stop our application and also the database engine.

After we restarted the database engine and our application, everything has been fine since.

The CPU usage of the database is now around 50 percent with spikes to 80 but then back down and sometimes even into the 20 percent range.  This behavior is more what we are accustomed to seeing.

Anyway, my question is what kind of monitoring and troubleshooting tools are available (I'm aware of the monitor program that can be licensed) and what would be recommended?

Thank you

[former_member188493]

Have a look at the Foxhound 3 Database Monitor.

[Former Member]

I see it appears to poll the database every 10 seconds.  Do you know how hard of a hit this is on the database?

[former_member188493]

The only time Foxhound's sampling load on a target database is noticeable is when that database is already overloaded by some kind of performance bottleneck... in that case, you can either (a) use the information Foxhound is gathering to find the bottleneck and solve the problem or (b) turn Foxhound off and try some other approach. Application Profiling (also known as Database Tracing) is one such other approach, but that will severely affect performance of an overloaded database (and sometimes even for a well-performing database).

Having said that, in the case of a busy target database the Foxhound own SQL Anywhere 16 engine should probably be run on some other computer so Foxhound's own database processing will not compete with the target engine.

Foxhound's performance is linear with the number of target database connections being recorded, and turning on the target database dbsrv12 -zp -zl -zt options will have following effects: (a) greatly increase the usefulness of the Foxhound reporting, (b) increase the load on Foxhound's own database, and (c) slightly increase the load on the target database (to honor the -zp -zl -zt options).

[Former Member]

Is there any limited trial for Foxhound (just a few days) just to install it and see how it works?

[former_member188493]

Send me an email: breck dot carter at gmail dot com