(RADIATOR) Suggestion: log SQL timings & stats

[Hugh Irvine]

Hi Andy -

This is an interesting idea and I have copied this mail to Mike for his 
consideration.

regards

Hugh


On 26 Aug 2004, at 21:05, Andy M wrote:

> 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
>  
>

NB: have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?

-- 
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-
CATool: Private Certificate Authority for Unix and Unix-like systems.

--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.