[RADIATOR] reusing Database connections efficiently

[Bruno Tiago Rodrigues]

Our organization has a Radiator server handling a large volume of requests
and we're trying to squeeze ops and optimize as much as possible.

On one of our recent audits we ran to the production server, we found out
that for each accounting request we need to get data from the underlying
database from the related authentication packet before processing the
accounting itself.

There's a PreAuthHook running for each Handler which basically clones the
Radiator database connection properties to establish a connection and fetch
data from the authentication table.

#!/usr/bin/perl

sub
{
use DBI;
my $p = ${$_[0]};
my $username = $p->get_attr('User-Name');
my $anumber = $p->get_attr('Calling-Station-Id');

my $dbconn = &main::getVariable('dbconn');
my $dbuser = &main::getVariable('dbuser');
my $dbpass = &main::getVariable('dbpass');

my $dbh = DBI->connect($dbconn, $dbuser, $dbpass);

($country_code, $imei, $rat) = &get_location_info($username, $anumber,
$dbh);

$p->add_attr('3GPP-SGSN-MCC-MNC', $country_code);
$p->add_attr('3GPP-IMEISV', $imei);
$p->add_attr('3GPP-RAT-TYPE', $rat);

&main::log($main::LOG_INFO,"Got extra RADIUS parameters from database for
user: $username");
}

sub get_location_info {
my $query = 'SELECT * FROM (SELECT country_code, imei, rat, row_number()
over(order by timestamp desc) rn FROM authentication WHERE username =
\''.$_[0].'\' and anumber = \''.$_[1].'\' order by TIMESTAMP desc) tbl
WHERE tbl.rn <= 1';
my $sth = $_[2]->prepare($query);
$sth->execute();
my @result=$sth->fetchrow_array();
$sth->finish();
return @result;
}
}

According to our DBAs, however, this is hurting the database because it
requires establishing a connection to the database, logging in and fetching
data. Logging in, for auditing purposes, is taking a huge toll on the
response times.

Is there any efficient way to reuse an existing handle bound to the
instance or persistently get a DBI->connect handle on a Startup Hook for
each instance and then reuse it inside the PreAuthHook (eventually
reconnecting if necessary)?



Bruno Tiago Rodrigues
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.open.com.au/pipermail/radiator/attachments/20190930/ecbcf359/attachment.html>