[freeside] Re: Freeside and Radius sql
Peter Kolbe
peter at venturenet.co.za
Tue May 17 22:56:50 PDT 2005
thanks Scott.
will try that
Peter
----- Original Message -----
From: <scott at ruralnetwork.net>
To: <ivan-freeside at sisd.com>
Sent: Tuesday, May 17, 2005 10:01 PM
Subject: [freeside] Re: Freeside and Radius sql
> This is my follow up to Peter Kolbe's question about Freeside's query
> performance with a SQL radius database.
>
>
>
--------------------------------------------------------------------------------
>
> 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
>
>
>
--------------------------------------------------------------------------
The information transmitted is intended only for the person to whom it is
addressed and may contain confidential and/or privileged material.
Although SCDS / Venturenet scans incoming and outgoing emails and email
attachments for viruses we cannot guarantee a communication to be free of
all viruses nor accept any responsibility for viruses.
Although SCDS / Venturenet monitors incoming and outgoing emails for
inappropriate content, we cannot be held responsible for the views or
expressions of the author.
The views expressed may not necessarily be those of SCDS / Venturenet and
as such, cannot be held responsible for any loss or injury resulting from
the contents of a message.
--------------------------------------------------------------------------
More information about the freeside-users
mailing list