Importing & access to radius usage info within Freeside

[scott at ruralnetwork.net]

rls-freeside at powerband.net.au writes: 

> ivan wrote:
>> On Wed, Jun 16, 2004 at 08:56:46PM +1000, The Salisburys wrote:
>>> As freeside folks say adfinitum
>>> don't use mysql [..]
>> From the FAQ:
>>   Note: the above only applies to the database used by the Freeside   
>> software itself. Freeside can integrate with RADIUS servers running   
>> MySQL (any version) or any other database.
> 
> 	True. We've used Postgres (7.3 btw, I missed that in the
> original) for Freeside's own DBs. 
> 
> 	But as per the FAQ & in the Config & code, there's no
> need (that we see) to move our Radius DBs from mySQL to Postgres
> within Freeradius. 
> 
> 	But Roger (The Salisburys) - are in inferring that you've
> got no problems with radius-derived "data usage" billing via Freeside
> using Postgres for both your radiusd & Freeside DBs? 
> 
> Thanks,
> Richard. 
> 

My two bits here ... 

I'm using Postres to store accounting data in freeradius.  Since I'm using 
the radrelay feature of freeradius to keep the primary and secondary RADIUS 
servers in sync, the accounting data appears on both servers.  Because 
Freeside sums the accounting data from all RADIUS servers it knows about, I 
had to modify the Freeside code to only query one of these RADIUS servers 
for the accounting data. 

I noticed that the queries for a users' monthly usages were taking a long 
time.  By experimenting with the format of the queries, using the postgresql 
'explain analyze' command, turning on the logging of query execution times 
in postgresql, and searching the postgresql mailing lists archives, I 
learned that: 

1. The queries generated by freeside for calculating sql radius seconds will 
not make use of indexes.
2. Creating appropriate indexes on the freeradius postgresql tables and 
getting the freeside queries to use them would speed up these type of 
queries by a factor of roughly 1000.
3. The upcoming version 7.5 release of Postgresql "may" make it possible for 
the existing freeside queries to make use of indexes on a freeradius 
postgresql database.
http://archives.postgresql.org/pgsql-general/2004-06/msg00211.php 

Finally, while still in need of a lot of work, I hacked the relevant 
routines of Freeside to use the unix timestamp datatype that postgresql uses 
internally for its SQL comparisions, instead of converting those timestamps 
to EPOCH seconds.  It makes the queries a lot faster.  When I figure out a 
better way to do this, I'll submit a better patch.  But here is my current 
patch, which does also requires some additional time-related perl modules. 

Regards, 

Scott Langley
scott at ruralnetwork.net
Systems Administrator
Rural Network Services 


-------------- next part --------------
A non-text attachment was scrubbed...
Name: pg_radius_seconds.diff
Type: text/x-diff
Size: 5415 bytes
Desc: not available
Url : http://420.am/pipermail/freeside-users/attachments/20040616/7c9c3e2a/pg_radius_seconds.bin