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 150000 records are created in the acct table.
The table is indexed. I've attached accounting schema&sql.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','YYYY-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),
FRAMEDIPADDRESS VARCHAR2(15),
CDMACORRELATIONID VARCHAR2(10),
CDMAHAAGENT VARCHAR2(15),
USERNAME VARCHAR2(128),
CDMAPCFIPADDRESS VARCHAR2(15),
CDMABSMSCADDR VARCHAR2(32),
CDMAUSERID NUMBER(12),
CDMAIPTECH NUMBER(12),
CDMACOMPTUNIND NUMBER(12),
CDMARELEASEIND NUMBER(12),
ACCTINPUTOCTETS NUMBER(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),
CDMAAIRPRIORITY NUMBER(12),
CDMARPSESSIONID NUMBER(21),
ACCTAUTHENTIC VARCHAR2(32),
ACCTSESSIONTIME NUMBER(12),
ACCTTERMINATECAUSE VARCHAR2(32),
NASPORTTYPE VARCHAR2(32),
NASPORT NUMBER(12),
SERVICETYPE VARCHAR2(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
INITRANS 1
MAXTRANS 255
TABLESPACE radius
STORAGE (
INITIAL 120000K
NEXT 50000K
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
-- Creating Primary Key for ACCT
ALTER TABLE acct
ADD PRIMARY KEY (radacctid)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE radius
STORAGE (
INITIAL 8192K
NEXT 2048K
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
INITRANS 2
MAXTRANS 255
TABLESPACE radius
STORAGE (
INITIAL 81920K
NEXT 8192K
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 records logged to the
# same SQL table, leave this as is. If you want them in
# different tables, put the start table in acct_table1
# and stop table in acct_table2
acct_table1 = "acct"
acct_table2 = "acct"
authcheck_table = "radcheck"
authreply_table = "radreply"
groupcheck_table = "radgroupcheck"
groupreply_table = "radgroupreply"
usergroup_table = "usergroup"
# Remove stale session if checkrad does not see a double login
deletestalesessions = yes
# Print all SQL statements when in debug mode (-x)
#sqltrace = yes
sqltrace = no
#sqltracefile = ${logdir}/sqltrace.sql
# number of sql connections to make to server
num_sql_socks = 40
# number of seconds to dely retrying on a failed database
# connection (per_socket)
connect_failure_retry_delay = 60
#######################################################################
# Query config: Username
#######################################################################
# This is the username that will get substituted, escaped, and added
# as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used
below
# everywhere a username substitution is needed so you you can be sure
# the username passed from the client is escaped properly.
#
# Uncomment the next line, if you want the sql_user_name to mean:
#
# Use Stripped-User-Name, if it's there.
# Else use User-Name, if it's there,
# Else use hard-coded string "DEFAULT" as the user name.
#sql_user_name = "%{Stripped-User-Name:-%{Realm:-DEFAULT}}"
#sql_user_name = "%{Realm:-%{Stripped-User-Name}}"
#####sql_user_name = "%{Stripped-User-Name:-%{User-Name:-DEFAULT}}"
sql_user_name = "%{DEFAULT:-%{User-Name}}"
#
#sql_user_name = "%{User-Name:-%{DEFAULT}}"
#######################################################################
# Default profile
#######################################################################
# This is the default profile. It is found in SQL by group membership.
# That means that this profile must be a member of at least one group
# which will contain the corresponding check and reply items.
# This profile will be queried in the authorize section for every user.
# The point is to assign all users a default profile without having to
# manually add each one to a group that will contain the profile.
# The SQL module will also honor the User-Profile attribute. This
# attribute can be set anywhere in the authorize section (ie the users
# file). It is found exactly as the default profile is found.
# If it is set then it will *overwrite* the default profile setting.
# The idea is to select profiles based on checks on the incoming
packets,
# not on user group membership. For example:
# -- users file --
# DEFAULT Service-Type == Outbound-User, User-Profile :=
"outbound"
# DEFAULT Service-Type == Framed-User, User-Profile := "framed"
#
# By default the default_user_profile is not set
#
#default_user_profile = "DEFAULT"
#
# Determines if we will query the default_user_profile or the
User-Profile
# if the user is not found. If the profile is found then we consider
the user
# found. By default this is set to 'no'.
#
# query_on_not_found = yes
#######################################################################
# Authorization Queries
#######################################################################
# These queries compare the check items for the user
# in ${authcheck_table} and setup the reply items in
# ${authreply_table}. You can use any query/tables
# you want, but the return data for each row MUST
# be in the following order:
#
# 0. Row ID (currently unused)
# 1. UserName/GroupName
# 2. Item Attr Name
# 3. Item Attr Value
# 4. Item Attr Operation
#######################################################################
authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM
${authcheck_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
##authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM
${authcheck_table} WHERE ( Username like '%{Realm}' AND Realm =
'%{Huntgroup-Name}' AND Realm != '' ) OR ( Username = '%{SQL-User-Name}' AND
Realm = '' ) ORDER BY id "
##### other #### authorize_check_query = "SELECT id,UserName,Attribute,Value,op
FROM ${authcheck_table} WHERE ( Username = '%{SQL-User-Name}' AND '%{Realm}' IS
NOT NULL ) OR Username = '%{SQL-User-Name}' ORDER BY id "
authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM
${authreply_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
###authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM
${authreply_table} WHERE ( Username like '%{Realm}' AND Realm =
'%{Huntgroup-Name}' AND Realm != '' ) OR ( Username = '%{SQL-User-Name}' AND
Realm = '' ) ORDER BY id "
authorize_group_check_query = "SELECT
${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op
FROM ${groupcheck_table},${usergroup_table} WHERE
(${usergroup_table}.Username = '%{SQL-User-Name}' or ${usergroup_table}.CLID =
'%{Calling-Station-Id}') AND ${usergroup_table}.GroupName =
${groupcheck_table}.GroupName ORDER BY
${usergroup_table}.PRIORITY,${groupcheck_table}.id"
authorize_group_reply_query = "SELECT
${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op
FROM ${groupreply_table},${usergroup_table} WHERE
(${usergroup_table}.Username = '%{SQL-User-Name}' OR ${usergroup_table}.CLID =
'%{Calling-Station-Id}') AND ${usergroup_table}.GroupName =
${groupreply_table}.GroupName ORDER BY ${groupreply_table}.id"
#######################################################################
# Accounting Queries
#######################################################################
# accounting_onoff_query - query for Accounting On/Off packets
# accounting_update_query - query for Accounting update packets
# accounting_start_query - query for Accounting start packets
# accounting_start_query_alt - query for Accounting start packets
# (alternate in case first query fails)
# accounting_stop_query - query for Accounting stop packets
# accounting_stop_query_alt - query for Accounting start packets
# (alternate in case first query doesn't
# affect any existing rows in the table)
#######################################################################
accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S',
AcctSessionTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime),
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay =
%{Acct-Delay-Time} WHERE AcctSessionTime=0 AND AcctStopTime=0 AND NASIPAddress=
'%{NAS-IP-Address}' AND AcctStartTime <= '%S'"
accounting_update_query = "UPDATE ${acct_table2} SET \
FramedIPAddress = '%{Framed-IP-Address}', \
CDMAReleaseInd = '%{3GPP2-Release-Indicator}', \
CDMABadFrameCount = '%{3GPP2-Bad-PPP-Frame-Count}', \
CDMAActiveTime = '%{3GPP2-Active-Time}', \
CDMANumActive = '%{3GPP2-Num-Active}', \
CDMAReceivedHDLCOctets = '%{3GPP2-Received-HDLC-Octets}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Octets}', \
AcctOutputOctets = '%{Acct-Output-Octets}', \
AcctInputPackets = '%{Acct-Input-Packets}', \
AcctOutputPackets = '%{Acct-Output-Packets}', \
AcctTerminateCause = '%{Acct-Terminate-Cause}', \
AcctUpdateTime = '%{Event-Timestamp}', \
AcctStopDelay = '%{Acct-Delay-Time}' \
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
accounting_update_query_alt = "INSERT into ${acct_table2} VALUES (\
'', \
'%{Acct-Session-Id}', \
'%{Calling-Station-Id}', \
'%{Framed-IP-Address}', \
'%{3GPP2-Correlation-Id}', \
'%{3GPP2-Home-Agent-IP-Address}', \
'%{User-Name}', \
'%{3GPP2-PCF-IP-Address}', \
'%{3GPP2-BSID}', \
'%{3GPP2-User-ID}', \
'%{3GPP2-IP-Technology}', \
'%{3GPP2-Compulsory-Tunnel-Indicator}', \
'%{3GPP2-Release-Indicator}', \
'%{Acct-Input-Octets}', \
'%{Acct-Output-Octets}', \
'%{Acct-Input-Packets}', \
'%{Acct-Output-Packets}', \
'%{3GPP2-Bad-PPP-Frame-Count}', \
'%{3GPP2-Active-Time}', \
'%{3GPP2-Number-Active-Transitions}', \
'%{3GPP2-Received-HDLC-Octets}', \
'%{3GPP2-IP-QoS}', \
'%{3GPP2-Airlink-Priority}', \
'%{3GPP2-R-P-Session-ID}', \
'%{Acct-Authentic}', \
'%{Acct-Session-Time}', \
'%{Acct-Terminate-Cause}', \
'%{NAS-Port-Type}', \
'%{NAS-Port}', \
'%{Service-Type}', \
'%{NAS-IP-Address}', \
'%{NAS-Identifier}', \
'%{Acct-Unique-Session-Id}', \
'%{Realm}', \
'%{Tunnel-Server-Endpoint}', \
'%{Tunnel-Client-Endpoint}', \
'%{Tunnel-Assignment-Id}', \
'%{Tunnel-Type}', \
'%{Acct-Tunnel-Connection}', \
'%{Tunnel-Client-Auth-Id}', \
'%{Tunnel-Server-Auth-Id}', \
'', \
'%{Event-Timestamp}', \
'', \
'%{Framed-Protocol}', \
'0', \
'%{Acct-Delay-Time}')"
accounting_start_query = "INSERT into ${acct_table1} (\
RadAcctId, \
AcctSessionId, \
CallingStationId, \
FramedIPAddress, \
CDMACorrelationId, \
CDMAHAAgent, \
UserName, \
CDMAPCFIPAddress, \
CDMABSMSCAddr, \
CDMAUserId, \
CDMAIPTech, \
CDMACompTunInd, \
CDMABadFrameCount, \
CDMAActiveTime, \
CDMANumActive, \
CDMAReceivedHDLCOctets, \
CDMAIPQoS, \
CDMAAirPriority, \
CDMARPSessionID, \
AcctAuthentic, \
NASPortType, \
NASPort, \
ServiceType, \
NASIPAddress, \
NASIdentifier, \
AcctUniqueId, \
Realm, \
TunnelServerEndpoint, \
TunnelClientEndpoint, \
TunnelAssignmentId, \
TunnelType, \
AcctTunnelConnection, \
TunnelClientAuthId, \
TunnelServerAuthId, \
AcctStartTime, \
AcctStopTime, \
FramedProtocol, \
AcctStartDelay) \
values (\
'', \
'%{Acct-Session-Id}', \
'%{Calling-Station-Id}', \
'%{Framed-IP-Address}', \
'%{3GPP2-Correlation-Id}', \
'%{3GPP2-Home-Agent-IP-Address}', \
'%{User-Name}', \
'%{3GPP2-PCF-IP-Address}', \
'%{3GPP2-BSID}', \
'%{3GPP2-User-Id}', \
'%{3GPP2-IP-Technology}', \
'%{3GPP2-Compulsory-Tunnel-Indicator}', \
'%{3GPP2-Bad-PPP-Frame-Count}', \
'%{3GPP2-Active-Time}', \
'%{3GPP2-Number-Active-Transitions}', \
'%{3GPP2-Received-HDLC-Octets}', \
'%{3GPP2-IP-QoS}', \
'%{3GPP2-Airlink-Priority}', \
'%{3GPP2-R-P-Session-ID}', \
'%{Acct-Authentic}', \
'%{NAS-Port-Type}', \
'%{NAS-Port}', \
'%{Service-Type}', \
'%{NAS-IP-Address}', \
'%{NAS-Identifier}', \
'%{Acct-Unique-Session-Id}', \
'%{Realm}', \
'%{Tunnel-Server-Endpoint}', \
'%{Tunnel-Client-Endpoint}', \
'%{Tunnel-Assignment-Id}', \
'%{Tunnel-Type}', \
'%{Acct-Tunnel-Connection}', \
'%{Tunnel-Client-Auth-Id}', \
'%{Tunnel-Server-Auth-Id}', \
'%{Event-Timestamp}', \
'', \
'%{Framed-Protocol}', \
'%{Acct-Delay-Time}')"
accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime
= '%{Event-Timestamp}', AcctStartDelay = '%{Acct-Delay-Time}' WHERE
AcctUniqueId = '%{Acct-Unique-Session-Id}'"
accounting_stop_query = "UPDATE ${acct_table2} SET \
FramedIPAddress = '%{Framed-IP-Address}', \
CDMAReleaseInd = '%{3GPP2-Release-Indicator}', \
CDMABadFrameCount = '%{3GPP2-Bad-PPP-Frame-Count}', \
CDMAActiveTime = '%{3GPP2-Active-Time}', \
CDMANumActive = '%{3GPP2-Number-Active-Transitions}', \
CDMAReceivedHDLCOctets = '%{3GPP2-Received-HDLC-Octets}', \
AcctStopTime = '%{Event-Timestamp}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Octets}', \
AcctOutputOctets = '%{Acct-Output-Octets}', \
AcctInputPackets = '%{Acct-Input-Packets}', \
AcctOutputPackets = '%{Acct-Output-Packets}', \
AcctTerminateCause = '%{Acct-Terminate-Cause}', \
AcctStopDelay = '%{Acct-Delay-Time}' \
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
accounting_stop_query_alt = "INSERT into ${acct_table2} VALUES (\
'', \
'%{Acct-Session-Id}', \
'%{Calling-Station-Id}', \
'%{Framed-IP-Address}', \
'%{3GPP2-Correlation-Id}', \
'%{3GPP2-Home-Agent-IP-Address}', \
'%{User-Name}', \
'%{3GPP2-PCF-IP-Address}', \
'%{3GPP2-BSID}', \
'%{3GPP2-User-ID}', \
'%{3GPP2-IP-Technology}', \
'%{3GPP2-Compulsory-Tunnel-Indicator}', \
'%{3GPP2-Release-Indicator}', \
'%{Acct-Input-Octets}', \
'%{Acct-Output-Octets}', \
'%{Acct-Input-Packets}', \
'%{Acct-Output-Packets}', \
'%{3GPP2-Bad-PPP-Frame-Count}', \
'%{3GPP2-Active-Time}', \
'%{3GPP2-Number-Active-Transitions}', \
'%{3GPP2-Received-HDLC-Octets}', \
'%{3GPP2-IP-QoS}', \
'%{3GPP2-Airlink-Priority}', \
'%{3GPP2-R-P-Session-ID}', \
'%{Acct-Authentic}', \
'%{Acct-Session-Time}', \
'%{Acct-Terminate-Cause}', \
'%{NAS-Port-Type}', \
'%{NAS-Port}', \
'%{Service-Type}', \
'%{NAS-IP-Address}', \
'%{NAS-Identifier}', \
'%{Acct-Unique-Session-Id}', \
'%{Realm}', \
'%{Tunnel-Server-Endpoint}', \
'%{Tunnel-Client-Endpoint}', \
'%{Tunnel-Assignment-Id}', \
'%{Tunnel-Type}', \
'%{Acct-Tunnel-Connection}', \
'%{Tunnel-Client-Auth-Id}', \
'%{Tunnel-Server-Auth-Id}', \
'', \
'', \
'%{Event-Timestamp}', \
'%{Framed-Protocol}', \
'0', \
'%{Acct-Delay-Time}')"
#######################################################################
# Simultaneous Use Checking Queries
#######################################################################
# simul_count_query - query for the number of current connections
# - If this is not defined, no simultaneouls use
checking
# - will be performed by this module instance
# simul_verify_query - query to return details of current
connections for verification
# - Leave blank or commented out to disable
verification step
# - Note that the returned field order should not
be changed.
#######################################################################
# Uncomment simul_count_query to enable simultaneous use checking
# simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE
UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName,
NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM
${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
#######################################################################
# Group Membership Queries
#######################################################################
# group_membership_query - Check user group membership
#######################################################################
group_membership_query = "SELECT GroupName FROM ${usergroup_table}
WHERE UserName='%{SQL-User-Name}' OR CLID='%{Calling-Station-Id}' order by
priority"
}
