Alan DeKok wrote:
...
A 386 should be able to handle that. Find out why your DB is so
slow, and fix it.
The problem comes up after about 15 records are created in the acct table.
The table is indexed. I've attached accounting schemasql.conf. May be some DBAs
are here in the list? Any advice will be appretiated.
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
--
Sincerely Yours,
Alexander Serkin,
Skylink, Moscow
/*
* Function to convert unix timestamp into local date format
*/
CREATE OR REPLACE
FUNCTION from_unixtime (unix_time IN NUMBER)
RETURN DATE IS
BEGIN
return FROM_TZ(CAST(TO_DATE('1970-01-01 00:00:00','-MM-DD
HH24:MI:SS')+unix_time/86400 AS TIMESTAMP), 'GMT')AT TIME ZONE 'Europe/Moscow';
END;
/
DROP TABLE acct;
CREATE TABLE acct (
RADACCTID NUMBER NOT NULL,
ACCTSESSIONID VARCHAR2(32),
CALLINGSTATIONID VARCHAR2(15),
FRAMEDIPADDRESSVARCHAR2(15),
CDMACORRELATIONID VARCHAR2(10),
CDMAHAAGENTVARCHAR2(15),
USERNAME VARCHAR2(128),
CDMAPCFIPADDRESS VARCHAR2(15),
CDMABSMSCADDR VARCHAR2(32),
CDMAUSERID NUMBER(12),
CDMAIPTECH NUMBER(12),
CDMACOMPTUNIND NUMBER(12),
CDMARELEASEIND NUMBER(12),
ACCTINPUTOCTETSNUMBER(12),
ACCTOUTPUTOCTETS NUMBER(12),
ACCTINPUTPACKETS NUMBER(12),
ACCTOUTPUTPACKETS NUMBER(12),
CDMABADFRAMECOUNT NUMBER(12),
CDMAACTIVETIME NUMBER(12),
CDMANUMACTIVE NUMBER(12),
CDMARECEIVEDHDLCOCTETS NUMBER(12),
CDMAIPQOS NUMBER(12),
CDMAAIRPRIORITYNUMBER(12),
CDMARPSESSIONIDNUMBER(21),
ACCTAUTHENTIC VARCHAR2(32),
ACCTSESSIONTIMENUMBER(12),
ACCTTERMINATECAUSE VARCHAR2(32),
NASPORTTYPEVARCHAR2(32),
NASPORTNUMBER(12),
SERVICETYPEVARCHAR2(32),
NASIPADDRESS VARCHAR2(15),
NASIDENTIFIER VARCHAR2(32),
ACCTUNIQUEID VARCHAR2(17),
REALM VARCHAR2(64),
TUNNELSERVERENDPOINT VARCHAR2(15),
TUNNELCLIENTENDPOINT VARCHAR2(15),
TUNNELASSIGNMENTID VARCHAR2(32),
TUNNELTYPE VARCHAR2(15),
ACCTTUNNELCONNECTION VARCHAR2(32),
TUNNELCLIENTAUTHID VARCHAR2(32),
TUNNELSERVERAUTHID VARCHAR2(32),
ACCTSTARTTIME NUMBER(21),
ACCTUPDATETIME NUMBER(21),
ACCTSTOPTIME NUMBER(21),
FRAMEDPROTOCOL VARCHAR2(32),
ACCTSTARTDELAY NUMBER(12),
ACCTSTOPDELAY NUMBER(12))
PCTFREE 10
PCTUSED 90
INITRANS1
MAXTRANS255
TABLESPACE radius
STORAGE (
INITIAL 12K
NEXT5K
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
-- Creating Primary Key for ACCT
ALTER TABLE acct
ADD PRIMARY KEY (radacctid)
USING INDEX
PCTFREE 10
INITRANS2
MAXTRANS255
TABLESPACE radius
STORAGE (
INITIAL 8192K
NEXT2048K
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
DROP INDEX acct_idx1;
CREATE UNIQUE INDEX acct_idx1 ON
acct(ACCTSESSIONID,CALLINGSTATIONID,FRAMEDIPADDRESS,CDMACORRELATIONID,NASIPADDRESS,ACCTUNIQUEID,ACCTSTARTTIME,ACCTUPDATETIME,ACCTSTOPTIME)
PCTFREE 10
INITRANS2
MAXTRANS255
TABLESPACE radius
STORAGE (
INITIAL 81920K
NEXT8192K
MINEXTENTS 1
MAXEXTENTS 2147483645
);
DROP SEQUENCE acct_seq;
CREATE SEQUENCE acct_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER ACCT_SERIALNUMBER
BEFORE INSERT ON acct
FOR EACH ROW
BEGIN
SELECT acct_seq.nextval into :new.radacctid from dual;
END;
/
COMMIT;
#
# Configuration for the SQL module, when using MySQL.
#
# The database schema is available at:
#
# src/radiusd/src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql
#
# If you are using PostgreSQL, please use 'postgresql.conf', instead.
# If you are using Oracle, please use 'oracle.conf', instead.
# If you are using MS-SQL, please use 'mssql.conf', instead.
#
# $Id: sql.conf,v 1.26.4.1 2003/08/26 12:26:57 phampson Exp $
#
sql {
# Database type
# Current supported are: rlm_sql_mysql, rlm_sql_postgresql,
# rlm_sql_iodbc, rlm_sql_oracle, rlm_sql_unixodbc, rlm_sql_freetds
# driver = rlm_sql_mysql
driver = rlm_sql_oracle
# Connect info for Oracle
server = localhost
login = radius
password = password
# Database table configuration for Mysql
# radius_db = radius
# Database table configuration for Oracle
radius_db =
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=CDMA)))
# If you want both stop and start
