Re: RFC 2866 - Accounting ON / Accounting OFF packets

[Arran Cudbard-Bell <A.Cudbard-Bell@sussex.ac.uk>]

A.L.M.Buxey@lboro.ac.uk wrote:
> Hi,
>
>   
> > Also the index 'acctsessiontime' is missing for the radacct table in the 
> > default schema; makes the Accounting-On / Accounting-Off queries very slow 
> > doing a table scan on 1.4 million rows... Is this intentional or an 
> > oversight ?
> >
> > Might be an Idea to specify the default engine as InnoDB for the MySQL 
> > schemas. MyISAM (with it's table locks on every update/insert) just doesn't 
> > cut it with a reasonably busy RADIUS server; all connections in the db pool 
> > get used and users start being rejected.
> > Not good ...
> >     
>
> the first issue is certainly a good optimisation and should be present
> in the schema
>
> the second issue is a database engineering decision. the end user should
> decide what storage engine to use - be that the dumb basic MyISAM,
> InnoDB or a distributed cluster engine etc etc.  many sites will already
> have their own chosen ways of managing the DB. perhaps the docs
> should be a little clearer on this.
>   
Quite. I believe you'll probably run into problems with MyISAM if you've 
got a loaded RADIUS server. It's taken around 6 months for serious 
issues to occur. We switched over to some new more 'chatty' firmware on 
our access points, and that seemed to push it over the edge. Database 
equivalent of gridlock, all connections used, authentications starting 
to fail, not pretty.

Disabled SQL based Accounting for now as the data is used more for show 
anyway. Building InnoDB database based around the FR2 schemas.

Anyway ....

freeradius-server-2.0.2/raddb/sql/mysql ac221$ diff -c schema.sql.old 
schema.sql
*** schema.sql.old    Mon Nov  5 08:59:00 2007
--- schema.sql    Thu Mar 13 20:31:38 2008
***************
*** 45,50 ****
--- 45,51 ----
   KEY username (username),
   KEY framedipaddress (framedipaddress),
   KEY acctsessionid (acctsessionid),
+   KEY acctsessiontime (acctsessiontime),
   KEY acctuniqueid (acctuniqueid),
   KEY acctstarttime (acctstarttime),
   KEY acctstoptime (acctstoptime),


Arran

--
Arran Cudbard-Bell (A.Cudbard-Bell@sussex.ac.uk)
Authentication, Authorisation and Accounting Officer
Infrastructure Services | ENG1 E1-1-08 
University Of Sussex, Brighton
EXT:01273 873900 | INT: 3900