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.
