The MySQL schema should be changed, so that raddb is by default InnoDB.
This is exactly the reason I have been using PostgreSQL for the last 6 or so years.
Rather than using a different database for "archived" records just put the archived
data into different tables. The SQL accounting system I built for Cistron Radius
creates its tables as required automaticaly every month. I use a version of xlat that
I wrote myself to define table and log names based on the timestamp of the event.
I archive "ancient" detail tables, but keep summary tables for up to three years,
because our customers want to check how their usage patterns change {I have no
idea why, but when I changed it to a year I got lots of complaints}.
This method works very well for my current system because no table holds more
than one months data. When I move to FreeRadius in the near future I will have
to come up with an inovative way to handle this problem. I have been helping
the development team with the PostgreSQL driver while ensuring none of my
entries break the MySQL driver.
The main issue I come across with tables that span no more than one month, is
that if a user is online past the end of the month, their time is accounted in the
next month. I need to come up with a way to "split" the records that overlap the
end of a month {or year} that does not damage the integrity of the record. I have
been considering a termination code of "Split" or somthing like it indicating the
record "Continues" into the next table, but I havn't yet decided how to indicate
that a record is a "Continuation" from a previous table.
It might be a good idea to have this type of functionality included with
FreeRadius in some way. I am interested in other peoples opinions on this matter
and would like to hear your ideas.
Guy
Jeff Warnica wrote:
Not really a FreeRADIUS question, but anyway:
For complex queries and/or queries with large result sets MySQL, using MyISAM tables, will produce a temporary table to fulfill the request. To ensure that it gets a consistent (temporary) table, MySQL locks the entire (real) table during this process, preventing writes. Reads can happen, but (I think) any reads that are complex enough to cause a temporary table will also queue up.
InnoDB table types do not suffer from this limitation.
Even if you can change table types it may be worth while to EXPLAIN some of your SELECT queries, and read the MySQL docs on general query optimizations. With about 5 minutes of effort, I managed to optimize someone else's queries from ~20s to ~5s. Still locks the table, but for significantly less time.
On Thu, 2004-03-11 at 16:31, Mike Sturdee wrote:
What might be causing this, or what could I do to resolve this?
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
