(RADIATOR) Suggestion: log SQL timings & stats

[Andy M]

Hi,

We are running Radiator 3.9 on a Linux platform, and using an Oracle 9i database to store the end user address pools (the RADPOOL table). We find that the biggest single factor (by far) that determines response time and overall capacity (requests per second) of an individual Radiator process is the time taken to run the database queries.

In order to monitor these times, we have written a small addition to the SqlDb.pm module in order to log, for each SQL statement run, the following:

 - Timestamp
 - Time taken (using Time::HiRes)
 - SQL statement
 - Bind variables passed
 - Type of call - do() or prepareAndExecute()

We have found this to be very useful in fine-tuning the SQL statements called, and would like to keep it in place.

However, the code we have written has two or three drawbacks:

1. The logfile naming etc is quite basic - in terms of file rotation, naming based on date etc
2. Logging each individual SQL statement could be problematic when usage is high (when often all that is required is a minimum/average/maximum time, or occasionally each individual statement for short periods of time when diagnosing problems)
3. It isn't supported by yourselves - and so needs to be added by us on each new release (which may be difficult if, for instance, you were to ever make extensive changes to the SqlDb.pm module)

So - would you be interested in implementing something within the main Radiator application which would enable us to get an idea of the times of each SQL statement run? Or - would anyone else find this useful?

The requirements might be something like:

1. Optionally log each individual SQL statement, along with time, time taken (using Time::HiRes), bind variables passed, and type of call (do/prepareAndExecute), in a way that is defined in the config file and flexible in terms of file location, naming etc.
2. Optionally log stats for SQL statements, summarised by SQL statement (e.g. one statement might be: "update RADPOOL set STATE=0,TIME_STAMP=? where YIADDR=?" - with variation being in the bind variables passed), including minimum, average, and maximum times, and the number of times called in that time period. This would be logged in the standard StatsLog module.

Cheers,

Andy
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.open.com.au/pipermail/radiator/attachments/20040826/a53f612a/attachment.html>