<div>Hi all,</div>
<div> </div>
<div>I have configured LNS routers to send packets to Radius servers primary and secondary. Both sharing packets and sending to single mysql cluster.</div>
<div> </div>
<div> 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.</div>

<div> </div>
<div>We are logging only accounting packets in to db. And only stop queries sent to radius server.</div>
<div>stop queries in dialup.conf are given below:</div>
<div>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}'"</div>

<div> </div>
<div>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}')"<br>
</div>
<div> Any problem in sql queries which leads to sql db slowness?</div>
<div> </div>
<div>radiusacct schema:</div>
<div> </div>
<div>desc radiusacct;</div>
<div>mysql> desc LWT.radiusacct;<br>+----------------------+------------------+------+-----+---------------------+----------------+<br>| Field                | Type             | Null | Key | Default             | Extra          |<br>
+----------------------+------------------+------+-----+---------------------+----------------+<br>| RadAcctId            | bigint(21)       | NO   | PRI | NULL                | auto_increment |<br>| AcctSessionId        | varchar(32)      | NO   | PRI |                     |                |<br>
| AcctUniqueId         | varchar(32)      | NO   | MUL |                     |                |<br>| UserName             | varchar(64)      | NO   | MUL |                     |                |<br>| NASIPAddress         | varchar(15)      | NO   | MUL |                     |                |<br>
| AcctStopTime         | datetime         | NO   | MUL | 0000-00-00 00:00:00 |                |<br>| AcctSessionTime      | int(12)          | YES  | MUL | NULL                |                |<br>| AcctAuthentic        | varchar(32)      | YES  |     | NULL                |                |<br>
| acctinputoctets      | bigint(20)       | YES  |     | NULL                |                |<br>| acctoutputoctets     | bigint(20)       | YES  |     | NULL                |                |<br>| AcctTerminateCause   | varchar(32)      | NO   |     |                     |                |<br>
| FramedProtocol       | varchar(32)      | YES  |     | NULL                |                |<br>| TunnelServerEndpoint | varchar(15)      | NO   |     |                     |                |<br>| TunnelClientEndpoint | varchar(15)      | NO   |     |                     |                |<br>
| TunnelType           | varchar(10)      | NO   |     |                     |                |<br>| AcctTunnelConnection | int(10)          | YES  |     | NULL                |                |<br>| AcctInputPackets     | int(10) unsigned | YES  |     | NULL                |                |<br>
| AcctOutputPackets    | int(10) unsigned | YES  |     | NULL                |                |<br>| TunnelAssignmentId   | varchar(25)      | NO   |     |                     |                |<br>| AcctStatusType       | varchar(20)      | NO   |     |                     |                |<br>
| DiscCauseExt         | varchar(100)     | YES  |     | NULL                |                |<br>| PPPDisconnectCause   | varchar(100)     | YES  |     | NULL                |                |<br>| NASPortType          | varchar(32)      | YES  |     | NULL                |                |<br>
| acctstopdelay        | int(12)          | YES  |     | NULL                |                |<br>+----------------------+------------------+------+-----+---------------------+----------------+<br></div>
<div>Please suggest to speed up sql db.</div>
<div> </div>
<div>Mysql server RAM is 8GB and four processor Xeon.</div>
<div> </div>
<div> </div>
<div>Thanks,</div>
<div>Rams.</div>
<div> </div>
<div> </div>