RFC 2866 - Accounting ON / Accounting OFF packets
Hi, RFC 2866 (5.5) States 'An Accounting-Request packet MUST have an Acct-Session-Id' but for Accounting Request packets with 'Acct-Status-Type' set to Accounting-On or Accounting-Off, there can be no Acct-Session-Id, as these are global events on the NAS... Unless the NAS is meant to send an Accounting-On or Accounting-Off packet for every session on the access point, when an Accounting-On or Accounting-Off event occurs... Does anyone know of a later RFC that clarifies this ? 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 ... Thanks, 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
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 ?
In the postgres schema, there's a conditional compound index which makes that query fast/indexed, so I suspect it's an oversight.
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 ?
In the postgres schema, there's a conditional compound index which makes that query fast/indexed, so I suspect it's an oversight.
postgres is, in general, so much better from a default install too. we spent some time tweaking the MySQL...but a trivial side-grade to using postgres paid immediate dividends... non-blocking inserts/updates etc. very nice. alan
AcctSessionTime is not indexed in default 1.1.6 or 1.1.7 mysql schema either. Ivan Kalik Kalik Informatika ISP Dana 13/3/2008, "Phil Mayers" <p.mayers@imperial.ac.uk> piše:
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 ?
In the postgres schema, there's a conditional compound index which makes that query fast/indexed, so I suspect it's an oversight. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
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. alan
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
Hi,
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.
we hit a similar wall - and decided to stop doing 'live live live!' accounting - use the SQL_LOG and sqlrelay tool to insert the accounting updates into the DB as quickly as they can behind the scenes. its wrong to tie up the FreeRADIUS threads with the accounting stuff - as , you pointed out, its a lot of SQL grunt and that delays everything else. strangely it is a very sudden tipping point too...all it takes is that extra few milliseconds and things go to pot rapidly! alan
A.L.M.Buxey@lboro.ac.uk wrote:
we hit a similar wall - and decided to stop doing 'live live live!' accounting - use the SQL_LOG and sqlrelay tool to insert the accounting updates into the DB as quickly as they can behind the scenes.
The detail file reading has been fixed in CVS head. So you *should* be able to have one daemon that does everything: 1812 -> radiusd -> detail detail -> radiusd -> sql If the system is lightly loaded, the detail file gets rotated rapidly. If the system is heavily loaded, the detail file grows until the system is lightly loaded. Alan DeKok.
A.L.M.Buxey@lboro.ac.uk wrote:
Hi,
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.
we hit a similar wall - and decided to stop doing 'live live live!' accounting - use the SQL_LOG and sqlrelay tool to insert the accounting updates into the DB as quickly as they can behind the scenes. its wrong to tie up the FreeRADIUS threads with the accounting stuff - as , you pointed out, its a lot of SQL grunt and that delays everything else. strangely it is a very sudden tipping point too...all it takes is that extra few milliseconds and things go to pot rapidly!
Oh yes ! Very spectacular indeed. The way your doing things seems very sensible. 'live live live!' accounting will always run into problems when there are spikes in the volume of accounting packets. Course there are still thread limits on the server, but at least you've eliminated one of the major bottlenecks. What was the problem with SQL relay in 2.02 ? Thanks, Arran
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- 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
Alan DeKok wrote:
Arran Cudbard-Bell wrote:
What was the problem with SQL relay in 2.02 ?
There wasn't a problem with that, but with reading the "detail" files. It's fixed in CVS head.
The fault was complicated... it's a lot better now. :)
Good to hear :) Could you commit that minor change to the MySQL schema. MySQL 5 doesn't require compound indexes, as of 5 it can use multiple indexes for the same query. http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html So adding an index on acctsessiontime should fix the problem. Thanks, 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
Hi,
The way your doing things seems very sensible. 'live live live!' accounting will always run into problems when there are spikes in the volume of accounting packets. Course there are still thread limits on the server, but at least you've eliminated one of the major bottlenecks.
What was the problem with SQL relay in 2.02 ?
not sure about current issues/problems - the SQL calls need to be updated to match those in the 'live live live!' (LLL ? ;-) ) SQL accounting calls.. apart from that - nothing immediately springs to mind. alan
A.L.M.Buxey@lboro.ac.uk wrote:
Hi,
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.
we hit a similar wall - and decided to stop doing 'live live live!' accounting - use the SQL_LOG and sqlrelay tool to insert the accounting updates into the DB as quickly as they can behind the
I can second this. It's an EXCELLENT solution. It also means your radius servers keep going (and catch up) if the database goes away. We also run a local read-only SQL server on each radius server to do group lookups etc.
participants (5)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
Arran Cudbard-Bell -
Ivan Kalik -
Phil Mayers