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