Hi all,
I have configured LNS routers to send packets to Radius servers primary and
secondary. Both sharing packets and sending to single mysql cluster.
when one radius server packets are writing/updating other server packets
are locked and waiting for mysql. This i observed using show full
processlist. We are using default storage engine MyISAM. And the writes are
through network i mean sql in different server instead of radius server.
Radius process also crashing due mysql db handles not available, no socket
to handle requests.
We are logging only accounting packets in to db. And only stop queries sent
to radius server.
stop queries in dialup.conf are given below:
accounting_stop_query = "UPDATE ${acct_table2} SET acctstoptime =
DATE_SUB('%S', INTERVAL ( %{%{Acct-Delay-Time}:-0}) SECOND), acctsessiontime
= '%{Acct-Session-Time}', acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}'
<< 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets =
'%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}',
acctterminatecause = '%{Acct-Terminate-Cause}', AcctStatusType =
'%{Acct-Status-Type}', TunnelAssignmentId = '%{Tunnel-Assignment-Id}',
TunnelClientEndpoint = '%{Tunnel-Client-Endpoint}',AcctTunnelConnection =
'%{Acct-Tunnel-Connection}', TunnelType = '%{Tunnel-Type}', AcctInputPackets
= '%{Acct-Input-Packets}',AcctOutputPackets = '%{Acct-Output-Packets}',
TunnelServerEndpoint = '%{Tunnel-Server-Endpoint}', DiscCauseExt =
'%{disc-cause-ext}',PPPDisconnectCause = '%{ppp-disconnect-cause}',
NASPortType = '%{NAS-Port-Type}',acctstopdelay = '%{%{Acct-Delay-Time}:-0}'
WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}'
AND nasipaddress = '%{NAS-IP-Address}'"
accounting_stop_query_alt = "INSERT INTO ${acct_table2} (acctsessionid,
acctuniqueid, username, nasipaddress, nasporttype, acctstoptime,
acctsessiontime, acctauthentic, acctinputoctets, acctoutputoctets,
acctterminatecause, framedprotocol,TunnelAssignmentId,TunnelClientEndpoint,
AcctTunnelConnection, TunnelType, AcctInputPackets, AcctOutputPackets,
TunnelServerEndpoint, AcctStatusType, DiscCauseExt,
PPPDisconnectCause,acctstopdelay ) VALUES ('%{Acct-Session-Id}',
'%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{NAS-IP-Address}',
'%{NAS-Port-Type}', DATE_SUB('%S', INTERVAL ( %{%{Acct-Delay-Time}:-0})
SECOND), '%{Acct-Session-Time}',
'%{Acct-Authentic}','%{%{Acct-Input-Gigawords}:-0}' << 32 |
'%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}', '%{Acct-Terminate-Cause}',
'%{Framed-Protocol}','%{Tunnel-Assignment-Id}',
'%{Tunnel-Client-Endpoint}','%{Acct-Tunnel-Connection}','%{Tunnel-Type}','%{Acct-Input-Packets}',
'%{Acct-Output-Packets}', '%{Tunnel-Server-Endpoint}',
'%{Acct-Status-Type}', '%{disc-cause-ext}', '%{ppp-disconnect-cause}',
'%{%{Acct-Delay-Time}:-0}')"
Any problem in sql queries which leads to sql db slowness?
radiusacct schema:
desc radiusacct;
mysql> desc LWT.radiusacct;
+----------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default
| Extra |
+----------------------+------------------+------+-----+---------------------+----------------+
| RadAcctId | bigint(21) | NO | PRI | NULL
| auto_increment |
| AcctSessionId | varchar(32) | NO | PRI |
| |
| AcctUniqueId | varchar(32) | NO | MUL |
| |
| UserName | varchar(64) | NO | MUL |
| |
| NASIPAddress | varchar(15) | NO | MUL |
| |
| AcctStopTime | datetime | NO | MUL | 0000-00-00 00:00:00
| |
| AcctSessionTime | int(12) | YES | MUL | NULL
| |
| AcctAuthentic | varchar(32) | YES | | NULL
| |
| acctinputoctets | bigint(20) | YES | | NULL
| |
| acctoutputoctets | bigint(20) | YES | | NULL
| |
| AcctTerminateCause | varchar(32) | NO | |
| |
| FramedProtocol | varchar(32) | YES | | NULL
| |
| TunnelServerEndpoint | varchar(15) | NO | |
| |
| TunnelClientEndpoint | varchar(15) | NO | |
| |
| TunnelType | varchar(10) | NO | |
| |
| AcctTunnelConnection | int(10) | YES | | NULL
| |
| AcctInputPackets | int(10) unsigned | YES | | NULL
| |
| AcctOutputPackets | int(10) unsigned | YES | | NULL
| |
| TunnelAssignmentId | varchar(25) | NO | |
| |
| AcctStatusType | varchar(20) | NO | |
| |
| DiscCauseExt | varchar(100) | YES | | NULL
| |
| PPPDisconnectCause | varchar(100) | YES | | NULL
| |
| NASPortType | varchar(32) | YES | | NULL
| |
| acctstopdelay | int(12) | YES | | NULL
| |
+----------------------+------------------+------+-----+---------------------+----------------+
Please suggest to speed up sql db.
Mysql server RAM is 8GB and four processor Xeon.
Thanks,
Rams.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
