Community discussions

MikroTik App
 
User avatar
natanielklug
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 95
Joined: Mon Apr 02, 2007 6:09 pm
Location: Cascavel/PR/Brasil

Freeradius and MySQL

Thu May 09, 2013 2:18 pm

Hello all,

I own a system that we run Freeradius as AAA for our PPPoE customers. Now we have something about 7000 PPPoE Sessions and MySQL is showing some trouble to run. It's not been able to write down accouting (radacct) in time and this is causing us a lot of problems.

I would love to hear from you what is the solution you are using as AAA for your customers and how many connections you have.

Regards,

Nataniel Klug
 
User avatar
boen_robot
Forum Guru
Forum Guru
Posts: 2411
Joined: Thu Aug 31, 2006 4:43 pm
Location: europe://Bulgaria/Plovdiv

Re: Freeradius and MySQL

Thu May 09, 2013 2:39 pm

Setting up a MySQL Cluster is one option. Another one is to simply get an SSD and/or a quad core CPU for your database server, if you don't have one already.
PEAR2_Net_RouterOS(1.0.0b6) - My API client in PHP
(Rate my posts? If you want... no pressure...)
 
SurferTim
Forum Guru
Forum Guru
Posts: 4637
Joined: Mon Jan 07, 2008 10:31 pm
Location: Miramar Beach, Florida

Re: Freeradius and MySQL

Thu May 09, 2013 2:51 pm

If you want to try a temporary patch, you might try increasing the radius interim update interval and the radius timeout.
 
User avatar
natanielklug
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 95
Joined: Mon Apr 02, 2007 6:09 pm
Location: Cascavel/PR/Brasil

Re: Freeradius and MySQL

Thu May 09, 2013 3:22 pm

Thank you guys.

SuferTim,

I've already set interim to 10 minutes and timeout to 5 seconds. It's not working fine and I have a lot of slow queue in MySQL and, for so, my customers are not allowed to log in throw PPPoE.

boen_robot,

I will look into MySQL Cluster. I am not familliar on how to use this but I will be looking into it.
 
SurferTim
Forum Guru
Forum Guru
Posts: 4637
Joined: Mon Jan 07, 2008 10:31 pm
Location: Miramar Beach, Florida

Re: Freeradius and MySQL

Thu May 09, 2013 3:26 pm

Your timeout should be plenty. If it does not interfere with anything else you have running, try 20 minutes on the interim interval. To me, it is better not to get updates as often and freeing up the radius server to handle logins.

Sounds like you need more power somewhere.
 
User avatar
natanielklug
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 95
Joined: Mon Apr 02, 2007 6:09 pm
Location: Cascavel/PR/Brasil

Re: Freeradius and MySQL

Thu May 09, 2013 4:05 pm

Your timeout should be plenty. If it does not interfere with anything else you have running, try 20 minutes on the interim interval. To me, it is better not to get updates as often and freeing up the radius server to handle logins.

Sounds like you need more power somewhere.
Surfer,

The only problem I can see using 20 minutos would be my IPPool (SQLIPPool) witch I should rise it time to something over 20 minutes. My most valuable resource is my IPV4 address range and I need it to be dynamic and fast release when it is not been used.

As I read your message something occured me: I will only have a problem with PPPoE when I got a stalled session and, if this happen, I will need to phisicaly solve the problem most probably dropping connections over a NAS or something like that.

I will try to update this time to something about 20 minutes.

About the power somewhere we are running this over a dual Xeon 2.8 Ghz in a Vmware enviroment. I can set it up to 32 Gb of RAM but nothing of that solved the problem. Looking into MySQL I can't see CPU or Memory going up. It seen to me that MySQL is the problem for some reason it can not handle that much conections.
 
voxframe
Member Candidate
Member Candidate
Posts: 126
Joined: Thu Dec 16, 2010 2:51 pm

Re: Freeradius and MySQL

Thu May 09, 2013 4:52 pm

What is the size of your database?

It sounds like your hardware should be just fine for this kind of work.

Unless the database is massive.
We encountered this problem when we had years worth of old accounting records for nothing.
Perhaps trim off old records?

How many rows and what is the disk size of the database?
 
User avatar
natanielklug
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 95
Joined: Mon Apr 02, 2007 6:09 pm
Location: Cascavel/PR/Brasil

Re: Freeradius and MySQL

Thu May 09, 2013 5:20 pm

What is the size of your database?

It sounds like your hardware should be just fine for this kind of work.

Unless the database is massive.
We encountered this problem when we had years worth of old accounting records for nothing.
Perhaps trim off old records?

How many rows and what is the disk size of the database?
voxframe,

In Brazil, for some reasons, we need to save 5 years of data since 2008. So we have a massive radacct1 table. It has 20 Gb size and a lot of rows. But when we got into the problem we set up a new MySQL server and set it all to run over this new machine. In Radius we got a "discard" packet and when we have a massive drop over our PPPoE clients radius lose connection with MySQL (because MySQL can not handle the number of connections):
Thu May  9 11:11:36 2013 : Error: Discarding duplicate request from client tr-jst port 53876 - ID: 147 due to unfinished request 15135
Thu May  9 11:11:37 2013 : Error: Discarding conflicting packet from client tr-jst port 53876 - ID: 147 due to recent request 15135.
Thu May  9 11:11:37 2013 : Error: Discarding duplicate request from client tr-jst port 35356 - ID: 148 due to unfinished request 15136
Thu May  9 11:11:38 2013 : Error: Discarding conflicting packet from client tr-jst port 35356 - ID: 148 due to recent request 15136.
We found out this is related to the number os PPPoE sessions over some MySQL Acct. So the logical lookout would be increase MySQL capacity. We are trying to setup this Cluster solution but it will take some time as we are not confortble with the solution.
 
SurferTim
Forum Guru
Forum Guru
Posts: 4637
Joined: Mon Jan 07, 2008 10:31 pm
Location: Miramar Beach, Florida

Re: Freeradius and MySQL

Thu May 09, 2013 5:28 pm

If you have 7000 active sessions, and your interim update is set to 10 minutes, then you are getting an average of almost 12 interim updates per second. That is not counting logins.

If your database has become large, that could cause a bottleneck. Maybe you should keep your required records in an archive database of previous weeks/months instead of storing them all in the active database.
 
daffster
just joined
Posts: 14
Joined: Sun Jul 27, 2008 9:18 pm
Contact:

Re: Freeradius and MySQL

Thu May 09, 2013 7:05 pm

You need to configure buffered accounting on your FreeRADIUS Server.
It drastically improves the rate at which accounting updates are written to MySQL.
 
User avatar
natanielklug
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 95
Joined: Mon Apr 02, 2007 6:09 pm
Location: Cascavel/PR/Brasil

Re: Freeradius and MySQL

Thu May 09, 2013 8:16 pm

You need to configure buffered accounting on your FreeRADIUS Server.
It drastically improves the rate at which accounting updates are written to MySQL.
daffster,

I am looking into buffered acct over Freeradius. I had never saw this before. Thank you.
 
nigfinch
just joined
Posts: 2
Joined: Tue Oct 22, 2013 2:51 pm
Location: Limerick, Ireland

Re: Freeradius and MySQL

Tue Nov 06, 2018 4:09 pm

Hi Nataniel,

I know this was a few years ago but did you have any luck with implementing a mysql buffer or fixing the FreeRadius errors ?

Thanks
 
User avatar
natanielklug
Frequent Visitor
Frequent Visitor
Topic Author
Posts: 95
Joined: Mon Apr 02, 2007 6:09 pm
Location: Cascavel/PR/Brasil

Re: Freeradius and MySQL

Tue Nov 06, 2018 9:53 pm

Hello!

Yes and no. I have now about 17000 subscribers over PPPoE. All the IPPool is running over a redundant PostgreSQL and the AAA is running over a MaxScale/GaleraCluster MariaDB with 3 hosts. It's working fine but the IPPool is not working over MariaDB.
---
Best regards,

José Nataniel Centeno Klug
Operation Manager Certto Telecom
+55 45 3333 2135 | www.certto.com.br
Image

Who is online

Users browsing this forum: anav, ccanto, iaraptisJack, jvanhambelgium, sindy and 74 guests