I record our radius logs in a firly basic table, schema is as follows:
----------------------------------------------------------
CREATE TABLE "history_new" (
"username" character varying(50) NOT NULL,
"time_stamp" int4 NOT NULL,
"acctstatustype" character varying(8) NOT NULL ,
"acctdelay" int2 NOT NULL,
"acctinputoctets" int4 ,
"acctoutputoctets" int4 ,
"acctsessionid" character varying(30),
"acctsessiontime" int4 ,
"acctterminatecause" character varying(50),
"nasidentifier" character varying(22),
"nasport" character varying(4),
"framedipaddress" character varying(16),
"callingstationid" character varying(16),
"ascenddatarate" character varying(16),
"calledstationid" character varying(16)
);
-----------------------------------------------------------
I then create an index on the username column by:
---------------------------------------
CREATE INDEX "i_h_uh" on HISTORY using hash (username);
---------------------------------------
I use the 'hash' type as queries regarding usage will always be of the
form "select ...... where username='xxx';"
I also calculate a 'summary' in the form of a table I call sumlog:
-----------------------------------------------------------
CREATE TABLE "sumlog" (
"username" character varying(8) NOT NULL,
"period" character varying(8) NOT NULL,
"sumtime" int4 DEFAULT '0' ,
"mbup" float8 DEFAULT '0.0' ,
"mbdn" float8 DEFAULT '0.0' );
------------------------------------------------------------
This table is populated by the following query:
-----------------------------------------------------
insert into sumlog
select s.username,
to_char(timestamp(h.time_stamp),'YYYY-MM') as date,
sum(h.acctsessiontime),
sum(float8(h.acctinputoctets)/1000000),
sum(float8(h.acctoutputoctets)/1000000)
from subscribers as s,history as h
where s.username=h.username
group by s.username,date;
-----------------------------------------------------------
This works fine, but as the database size is constantly growing the
summary table takes a while to calculate...
I was thinking of doing the following:
1. only update 'sumlog' for the current period (eg. 2001-04)
2. adding an index on the timestamp column to speed-up the query for
the insert into sumlog.
My questions are:
1. is it possible to create an index entry on the function applied to
the time_stamp.
eg. something of the form [ create index "i_ts" on history
(to_char(timestamp(h.time_stamp),'YYYY-MM')) ]
2. what is the best way to access the data from the history table for a
known period..
eg. knowing period='2001-04' is there a better function to use than
'to_char' against the timestamp, any sort of indexing I should use,
etc...
I've been going around in circles, and I'm sure I've missed some
basic/common-sense sort of step, but now I'm running out of time to
spend on it....
Has anyone here done anything similar??
--
Dale Walker < [EMAIL PROTECTED] >
Independent Computer Retailers (ICR) Pty Ltd
http://www.icr.com.au/
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
