radius configuration suggestions
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.
On Sun, Sep 6, 2009 at 4:09 PM, ramesh p<rock786143@gmail.com> wrote:
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.
What kind of cluster do you use? Failover cluster (using linux-ha or redhat cluster perhaps)? Locking is an inherent problem of MyIsam. If you REALLY use MySQL Cluster (the product, as in http://www.mysql.com/products/database/cluster/) you'd be using NDB, which doesn't suffer from this problem.
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.
Please suggest to speed up sql db.
Mysql server RAM is 8GB and four processor Xeon.
This is generally MySQL issue, but here are some initial tips: - you can convert the storage engine to Innodb. It's more suitable for workloads with lots of writes, as it doesn't need full table lock. - tune your schema. Index would speed up reads, but slow inserts greatly. Use only the columns you REALLY need. As for freeradius side, take a look at buffered sql/decoupled accounting. This might help up to a certain degree, but it's not perfect. Also it'd require some changes to your query. Search the list archive for a recent discussion about it. -- Fajar
On Sun, Sep 06, 2009 at 02:39:34PM +0530, ramesh p wrote:
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
Try switching it to InnoDB instead, at least on the server where you collect all your data. Get rid of all indexes that you would only use for SELECTs, keep only those needed for your update query. Also make sure that no other queries run on this box (no selects) - create a slave box and do all your selects on the slave. You can have MyISAM tables on the slave and all additional indexes you need to speed up reads. As a bonus, you can also do backups on the slave without affecting your master DB and the radius servers. If this doesn't help you can make your radius boxes log to a local file instead of writing directly to the database and then use radsqlrelay script to do all 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.
Maybe the only thing that needs tuning is the num_sql_socks option in your sql module to match the number of radius servers (max_servers in radiusd.conf)? -- szymon roczniak simon@dischaos.com
participants (4)
-
Alan Buxey -
Fajar A. Nugraha -
ramesh p -
Szymon Roczniak