Forum OpenACS Development: NaviServer ns_db stats

Collapse
Posted by Dave Bauer on
I am wondering what time unit is used for the ns_db_stats command that returns the average wait time for a connection within a pool. I could not find this in the documentation, and I looked around in the code, but I was not able to track it down.
Collapse
2: Re: NaviDerver ns_db stats (response to 1)
Posted by Gustaf Neumann on

The command ns_db stats returns aggregated times in seconds. To get the average times, one has to divide the times by the number of operations (figures are from openacs.org)

pool1 {statements 490062 gethandles 68522 handles 15 used 15 waittime 0.767247 sqltime 873.609304} 
pool2 {statements 19304 gethandles 439 handles 5 used 5 waittime 0.351877 sqltime 9.091417} 
pool3 {statements 435 gethandles 31 handles 5 used 1 waittime 0.121623 sqltime 0.411116}

Actually, the nsstats module of NaviServer provides these values in pretty format (see below):
https://bitbucket.org/naviserver/nsstats

pool1:  statements 490.2K gethandles 68.5K handles 15 used 15 waittime 0.767287 sqltime 873.816553 avgwaittime 11.2µs avgsqltime 1.8ms
pool2:  statements 19.3K gethandles 439 handles 5 used 5 waittime 0.351877 sqltime 9.091417 avgwaittime 801.5µs avgsqltime 471µs
pool3:  statements 435 gethandles 31 handles 5 used 1 waittime 0.121623 sqltime 0.411116 avgwaittime 3.9ms avgsqltime 945.1µs

In order to track single slow queries, one can activate logging of slow queries in the error.log, which makes it easier to track the origin (e.g. background operations, what kind of requests, etc.). To turn on SQL logging of slow queries, try in ds/shell or configure it in the configuration file

   ns_db logminduration pool1 0.5
   ns_db logminduration pool2 0.2
   ns_db logminduration pool3 0.2
   ns_logctl severity "Debug(sql)" -color blue on