I only have a few comments to make.
1. Most SQL databases of any consequence have no problem with hundreds of
thousands or millions of rows. They really are very fast, provided they are
hosted on a reasonable box (doenst have to be super fast, just 100-200MHz or so
and a fastish disk), and the indexes are sensibly defined. So a very reasonable
strategy would usually be to have one accounting table per month or....
2. Many people just have one accounting table that they periodically purge of
data older than their billing cycle (eg more than 3 months old). Some databases
(like Oracle) like to have their indexes recreated after such a purge, though.
3. One table per day probbaly doesnt buy you very much. Probably it loses you
something.
4. Lon might be able to use some or all of radacct.cgi for his web page showing
customer usage.
5. Lons observation about not bothering to save Starts in the accounting
database is correct. Theres nothing there thats not in the Stops.
Hope that helps.
Cheers.
On Mar 11, 6:28pm, Lon R. Stockton, Jr. wrote:
> Subject: Re: (RADIATOR) SQL help
>
> On Thu, 11 Mar 1999, Ferhat Dilman wrote:
>
> > By the way, a daily working tables really work? 31 tables in SQL every
> > month? Daily tables are more reasonable since it is 11st of March and it is
> > around 400,000 records already in the database.
>
> You have to determine what information you want to keep and what you can
> discard....i.e. do you really have a need to know the compression used
> on a given call three months ago? a year ago?
>
> In my operation, I only need that really specific data for about a
> month so I can do troubleshooting and the like. After that, I only
> need summaries. I need a summary that allows me to see usage statistics
> by customer for billing and statistical purposes, and I need a summary
> to see usage and other misc stats by nas/slot/channel for internal
> planning and the like.
>
> So, my plan is:
>
> 1) set my nas to send every radius attrib it can.
>
> 2) set my radiator to stuff it all in my calldetail table
>
> 3) provide access to calldetail via a webpage, where an
> admin can see 'em all, and a customer can see records
> that pertain to their account(s). This is for troubleshooting
> and customer satisfaction.
>
> 4) Daily, I update my custusage and nasusage summary tables.
> As an example, custusage table is thus-ish:
> create table custusage (
> acctid text not null,
> username text not null,
> sumdate date not null,
> numcalls int, timeused int, oct_in int, oct_out int);
> Simply put, #calls and usage info for a given customer on
> a given day. (the acctid is a local-to-us thing...simply
> a foreign key into my customer master table which maps cust
> accounts with actual usernames.)
>
> 5) Also daily, I delete records from the calldetail that are
> older than 30 days (possible modification is to move older
> records to another table, dropping all data that I really
> really never have to have...at the moment, I just can't
> see needing any of it). And don't forget to vacuum. (:
>
> 6) Again, access to summary tables via httpd, as appropriate
> for internal use and customer satisfaction (lordy don't
> they love the pretty little graphs).
>
> I also don't record start records in my calldetail, they contain
> nothing of any consequence. Anything I'd possibly use start records
> for will be handled by my sessiontable.
>
> The point is more that you'll obviously have to consider some kind
> of data reduction to keep the info you want/need readily accessable
> without having to have obscene amounts of hard drive space. Sure,
> you never have to lose the calldetail stuff...nothing says you
> must delete it all, just start burning cds. But you also want to
> be able to casually issue a [select month,avg(numcalls),avg(duration)
> from custsum where date(stoptime)>='01/01/95' group by month]
> query without having to find and mount the right cd(s).
>
> Disclamer: I'm not a database administrator, so I officially don't
> know what I'm talking about. I haven't actually done all of the
> above yet: I just got Radiator, don't have 30 days of data in my
> tables yet, the summary tables are still being designed, and
> the httpd server isn't even on the machine yet.
>
> But, daz the plan. Any comments, suggestions, or potshots delivered
> approximately now would prove helpful...a month or two from now
> and I'll be finding out if my plan is worth a crap in a much harder
> to fix scenario. (:
>
> Lon Stockton
> MoonStar
>
>
>
> ===
> To unsubscribe, email '[EMAIL PROTECTED]' with
> 'unsubscribe radiator' in the body of the message.
>-- End of excerpt from Lon R. Stockton, Jr.
--
Mike McCauley [EMAIL PROTECTED]
Open System Consultants Pty. Ltd Unix, Perl, Motif, C++, WWW
24 Bateman St Hampton, VIC 3188 Australia Consulting and development
Phone, Fax: +61 3 9598-0985 http://www.open.com.au
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, external, etc etc on Unix, Win95/8, NT, Rhapsody
===
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
