I understand your proposal, but this was just an example. In a typical configuration, if Radiator make the Authentication and the Accounting, what happens is (only regarding radonline, the session database) : - Auth Request coming in -> One DELETE - Acct Start ticket -> One DELETE + One INSERT. - Acct Stop Ticket -> One DELETE (which we have to do).
My first idea was to replace the INSERT by a "REPLACE INTO". Which is totally do-able inside the config file. But it's of no use, because hardcoded in SessSQL.pm, there is a DELETE done before running the insert request. Hence the "DontDeleteBeforeAdd" config keyword. Then, this pointed me to Handler.pm which does a DELETE on auth (which is useless when everything works fine ... :-). Hence the second config keyword : "DontDeleteOnAuth". In summary, I replace the following behavior : DELETE / DELETE / INSERT / DELETE by : REPLACE INTO / DELETE Maybe the implementation choices I have made are arguable, but this is a big performance improvement, specifically when the SQL database has locks contentions on the radonline table. Thanks for your feedback. ----- Original Message ----- From: "Hugh Irvine" <[EMAIL PROTECTED]> To: "Frederic Olivie" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, August 29, 2002 3:44 AM Subject: Re: (RADIATOR) Proposal for new config keywords Hello Frederic - I would have thought that a better answer would be to configure seperate Handlers for each BAS and only do the database accesses for your own. Something like this: # define Client clauses <Client 1.1.1.1> Identifier TelcoBAS ..... </Client> <Client 2.2.2.2> Identifier LocalBAS ..... </Client> # define Handlers <Handler Client-Identifier = TelcoBAS> ..... </Handler> <Handler Client-Identifier = LocalBAS> ..... </Handler> regards Hugh On Thursday, August 29, 2002, at 03:40 AM, Frederic Olivie wrote: > Some users might want to lighten up the load on their sessions SQL > table. On a typical ADSL setup, the following scenario occurs : > - One auth ticket sent by the TELCO for it's local BAS -> DELETE > - One auth ticket sent by our own BAS -> DELETE > - One session start ticket sent by the TELCO's BAS -> DELETE / INSERT > - One session stop ticket sent by our BAS -> DELETE / INSERT > > - Then, the normal Stop tickets mechanism which we don't touch. > > This makes a total of 4 DELETEs + 2 INSERTs. > > I propose the following mechanism : > > 1) As the Auth ticket will be followed by a Start ticket, the first > delete is made optional. The only case where this would be a problem > in the current behavior of radiator would be the following one : > - You use the simultaneous sessions limit (MaxSessions) feature > - User foo is disconnected the hard way and no Stop ticket is received. > - Use foo reconnects on exactly the same NAS/NASPORT and nobody has > reconnected on it before him. > > This is most unlikely to happen, and even if I did not do it, it would > be pretty simple to add a test to SessSQL's exceeded function to test > for same NAS/NASPORT before incrementing $count. > > 2) As some databases can do it easily (MySQL is one), the DELETE/INSERT > mechanism can be replaced by a single REPLACE INTO which replaces the > entry identified by the table's primary key (fine if your accounting > table has a primary key on the NAS/NASPORT pair), and inserts it if > it does not exist. > > I added two flags to the SessSQL handler : > > - DontDeleteOnAuth > - DontDeleteBeforeAdd > > The first one is pretty straightforward, but the second one implies > that the user configures his instance of radiator to make a "replace > into" instead of an insert. e.g : > > <SessionDatabase SQL> > [... cut some stuff ...] > DontDeleteBeforeAdd > > AddQuery replace into RADONLINE (USERNAME, NASIDENTIFIER, > NASPORT, ACCTSESSIONID, TIME_STAMP, FRAMEDIPADDRESS, NASPORTTYPE, > SERVICETYPE) values ('%u', '%N', 0%{NAS-Port}, '%{Acct-Session-Id}', > %{Timestamp}, '%{Framed-IP-Address}', '%{NAS-Port-Type}', > '%{Service-Type}') > > </SessionDatabase> > > This also means that the primary key on the table is based on : > NASIDENTIFIER/NASPORT. > > As a result of using those two flags, and in the case we are > talking about above, we go from : > > 4 DELETEs + 2 INSERTs. > > to : > > 2 "REPLACE INTO" > > Which is much, much faster because radiator only has 2 queries to > run (and wait a return for), and because the database backend has > very limited locking involved. > > Modem sessions handling meet the same situation but we only loose > one DELETE. > > The patch is pretty simple and involves only two files : > (The bug correction I sent earlier is not included in this diff. > It's based on pure 3.1 code). > > *** Handler.pm Tue Aug 27 21:54:28 2002 > --- Handler.pm.patched Wed Aug 28 15:37:10 2002 > *************** > *** 188,195 **** > if ($p->code eq 'Access-Request') > { > # If we lost a Stop for this port, clean up the session database > $sessdb->delete($original_username, $nas_id, $nas_port, $p, > ! $session_id, $framed_ip_address); > > # Issue a denial and bomb out > return $self->handlerResult($p, $main::REJECT, 'MaxSessions > exceeded') > --- 188,196 ---- > if ($p->code eq 'Access-Request') > { > # If we lost a Stop for this port, clean up the session database > + # if flag "DontDeleteOnAUth" is not present. > $sessdb->delete($original_username, $nas_id, $nas_port, $p, > ! $session_id, $framed_ip_address) if (!defined > $sessdb->{DontDeleteOnAuth}) ; > > # Issue a denial and bomb out > return $self->handlerResult($p, $main::REJECT, 'MaxSessions > exceeded') > > > *** SessSQL.pm Tue Aug 27 22:03:57 2002 > --- SessSQL.pm.patched Wed Aug 28 16:19:41 2002 > *************** > *** 21,27 **** > 'DeleteQuery' => 'string', > 'ClearNasQuery' => 'string', > 'CountQuery' => 'string', > ! 'CountNasSessionsQuery' => 'string' > ); > > ##################################################################### > --- 21,29 ---- > 'DeleteQuery' => 'string', > 'ClearNasQuery' => 'string', > 'CountQuery' => 'string', > ! 'CountNasSessionsQuery' => 'string', > ! 'DontDeleteBeforeAdd' => 'flag', > ! 'DontDeleteOnAuth' => 'flag' > ); > > ##################################################################### > *************** > *** 61,69 **** > > $self->log($main::LOG_DEBUG, > "$self->{Identifier} Adding session for $name, $nas_id, > $nas_port", $p); > ! if ($self->{DeleteQuery}) > { > # Delete any existing session on this port first: its clearly > defunct > $self->do(&Radius::Util::format_special > ($self->{DeleteQuery}, $p, undef, > $name, $nas_id, $nas_port)); > --- 63,72 ---- > > $self->log($main::LOG_DEBUG, > "$self->{Identifier} Adding session for $name, $nas_id, > $nas_port", $p); > ! if (!defined $self->{DontDeleteBeforeAdd} && $self->{DeleteQuery}) > { > # Delete any existing session on this port first: its clearly > defunct > + # Don't do it if "DontDeleteBeforeAdd" flag is on. > $self->do(&Radius::Util::format_special > ($self->{DeleteQuery}, $p, undef, > $name, $nas_id, $nas_port)); > > -- > Frédéric Olivié (Alf) > Mailto: [EMAIL PROTECTED] > Phoneto: +33 603 03 33 50 > > Very funny Scotty... Now beam down my clothes (Capt. James T. Kirk. > Starship Enterprise). > === > Archive at http://www.open.com.au/archives/radiator/ > Announcements on [EMAIL PROTECTED] > To unsubscribe, email '[EMAIL PROTECTED]' with > 'unsubscribe radiator' in the body of the message. > > -- Radiator: the most portable, flexible and configurable RADIUS server anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X. - Nets: internetwork inventory and management - graphical, extensible, flexible with hardware, software, platform and database independence. === Archive at http://www.open.com.au/archives/radiator/ Announcements on [EMAIL PROTECTED] To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.