Alexander Clouter wrote: > Steve Bertrand <[email protected]> wrote: >>>> The radacct table in the RADIUS database grows at an enormously fast >>>> rate. Regardless of server resources, trying to search or perform >>>> actions on this table can be a daunting task. >>> Yup. In some cases, people are required to keep this data for months. >> I have all radacct information back to 1999 readily available, and data >> before that archived. It wasn't until ~2003 or so that I changed to >> using SQL. It didn't take long to realize that the SQL server became >> useless after only a few months. >> > I guess I am a 'small cheese' with only 2m rows in my authentication > table? I am using PgSQL and have btree'd my timestamp columns, but it > still takes only 3.6s to pull 10k rows worth of just todays data. > > I thought the point of index'ing your columns (sensibly) meant that the > size of the table was not meant to really be too much of a killer?
A single select isn't an issue: select * from radacct where acctstoptime > '2010-01-01'; 15633 rows in set (0.84 sec) The problem I've noticed is in complex queries. For instance, in our accounting system, when we display a plan for a user, it used to pull out all radacct info for the user for an entire year, and display this info as aggregates per each month. This becomes a nightmare in cases where an ADSL user has a gateway that disconnects automatically every five minutes, but auto reconnects right after. Another situation is if I want to mysqldump the radacct table. It takes far less time to do this on a table with three months of data as opposed to 12+. So, what I do is: - aggregate daily data into a separate table - aggregate the daily aggregated data into a separate monthly table - copy the radacct data to a new radacct-YYYYMM table monthly, then remove that months data from the radacct table (generally, I keep three months active) - the accounting system displays the user month totals from the month aggregated table, so it only has to select 12 rows. The month archive is run daily Steve - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
