Freeside and Radius sql

[scott at ruralnetwork.net]

This is my follow up to Peter Kolbe's question about Freeside's query 
performance with a SQL radius database. 


-------------- next part --------------

In this freeside perl module:

 freeside/FS/FS/cust_svc.pm

The mechanism for handling datetime conversions from Postgresql's native format to "unix timestamp" format, is defined in this routine:

  #select a unix time conversion function based on database type
    my $str2time;
    if ( $dbh->{Driver}->{Name} eq 'mysql' ) {
      $str2time = 'UNIX_TIMESTAMP(';
    } elsif ( $dbh->{Driver}->{Name} eq 'Pg' ) {
      $str2time = 'EXTRACT( EPOCH FROM ';
    } else {
      warn "warning: unknown database type ". $dbh->{Driver}->{Name}.
           "; guessing how to convert to UNIX timestamps";
      $str2time = 'extract(epoch from ';
    }


the important line being:

      $str2time = 'EXTRACT( EPOCH FROM ';

----------

Here are a couple of Postgresql analyze queries I performed.

This first is analogous to Freeside's way of performing the seconds_since_sqlradacct() 
calculation when using Postgresql:

radius=> explain analyze SELECT SUM(acctsessiontime) FROM radacct WHERE UserName =  'scott' AND EXTRACT( EPOCH FROM AcctStartTime) >=  '1083391200' AND EXTRACT( EPOCH FROM AcctStopTime) <  '1083745740' AND EXTRACT( EPOCH FROM AcctStopTime ) > 0 AND AcctStopTime IS NOT NULL;
 Total runtime: 10463.385 ms


The second version is "more-postgres-native" way of calculating the seconds_since_sqlradacct():

radius=> explain analyze SELECT SUM( EXTRACT( EPOCH FROM AcctStopTime ) -  '1083391200' ) FROM radacct WHERE UserName =  'scott' AND EXTRACT( EPOCH FROM AcctStartTime ) < '1083391200' AND EXTRACT( EPOCH FROM AcctStopTime ) >=  '1083391200' AND EXTRACT( EPOCH FROM AcctStopTime ) <  '1083745740' AND EXTRACT( EPOCH FROM AcctStopTime ) > 0 AND AcctStopTime IS NOT NULL;
 Total runtime: 2.395 ms


Obviously, the second version is MUCH faster.

This holds true, even if you create a specially-crafted index due to this limitation of Postgresql as described by Postgresql developer Tom Lane:

http://groups-beta.google.com/group/comp.databases.postgresql.general/msg/23855147a2d383f5?hl=en

> This is ye olde bog-standard "can't index a cross-datatype comparison"
> problem.

----------

My workaround was to customize the  seconds_since_sqlradacct() subroutine to not extract epoch seconds and, instead, operate directly on the Postgresql timestamps, replacing this section:

    #find closed sessions completely within the given range
    my $sth = $dbh->prepare("SELECT SUM(acctsessiontime)
                               FROM radacct
                               WHERE UserName = ?
                                 AND $str2time AcctStartTime) >= ?
                                 AND $str2time AcctStopTime ) <  ?
                                 AND $str2time AcctStopTime ) > 0
                                 AND AcctStopTime IS NOT NULL"
    ) or die $dbh->errstr;

with this:


    #find closed sessions completely within the given range
    my $sth = $dbh->prepare("SELECT SUM($attrib)
                               FROM radacct
                               WHERE UserName = ?
                                 AND $str2time AcctStopTime ) >= ?
                                 AND $str2time AcctStopTime ) <  ?
                                 AND AcctStopTime IS NOT NULL"
    ) or die $dbh->errstr;


There are several others changes I made to this module to make this work, but I feel it's too hackish to publish.

----------


Where do we go from here?  Well, version 8.0 of Postgresql added this interesting feature:

--

http://www.postgresql.org/docs/8.0/static/release-8-0.html

E.4.4.1. Performance Improvements

    * Support cross-data-type index usage (Tom)

      Before this change, many queries would not use an index if the data types did not match exactly. This improvement makes index usage more intuitive and consistent.

----------

So, my plan is to try using unmodified freeside, Postgresql 8.0, and a suitable cross-data-type index, and see if that provides acceptable peformance.

If that doesn't work, then I will refine my existing "hacks" into "patches" and offer these back to Ivan.

----------

Note that there were some time-related bugs in Postgresql that were fixed in only very recently in versions 8.0.3, 7.4.7, and 7.3.10:

http://www.postgresql.org/docs/8.0/interactive/release.html#RELEASE-8-0-3


*  Fix comparisons of TIME WITH TIME ZONE values

The comparison code was wrong in the case where the --enable-integer-datetimes configuration switch had been used. NOTE: if you have an index on a TIME WITH TIME ZONE column, it will need to be REINDEXed after installing this update, because the fix corrects the sort order of column values.

*  Fix EXTRACT(EPOCH) for TIME WITH TIME ZONE values
 Fix mis-display of negative fractional seconds in INTERVAL values

This error only occurred when the --enable-integer-datetimes configuration switch had been used.


Regards,


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