Hi Alan ,team, I am testing scenario when main DB is temporary not available. I set rule in iptables to block all connections to DB. In this situation freeradius should return Accept with some parameters to NAS. authorize section is looks like sql { fail = 1 notfound = 2 } if ( fail ) { update { reply:Class = "outage" control:Auth-Type := Accept reply:Reply-Message := "DB Failed" reply:Acct-Interim-Interval := 600 } } This part works fine, but in traffic dump I see that radius replay with big delay , some times upto 20-30 seconds. In log a lot messages like Mon Jul 17 06:12:23 2023 : Error: Unresponsive child for request 288, in component <core> module Mon Jul 17 06:12:26 2023 : Error: rlm_sql_unixodbc: SQL down 08S01 [Oracle][ODBC][Ora]ORA-03113: end-of-file on communication channel Process ID: 151372 Session ID: 97 Serial number: 5095 Mon Jul 17 06:12:34 2023 : Error: rlm_sql_unixodbc: Connection failed I am trying to tune some parameters for sql connection, but this does not help. pool section retry_delay = 120 lifetime = 30 max_retries = 2 uses = 0 in radius.conf max_request_time = 5 and kernel (for faster detection that tcp session is broken) net.ipv4.tcp_retries2 = 2 net.ipv4.tcp_keepalive_intvl=5 net.ipv4.tcp_keepalive_probes=3 net.ipv4.tcp_keepalive_time=15 Can you recommend which params to change to prevent freeradius stuck on sql connection to DB ? Is it possible to prevent FR trying to connect to DB for some time (i think it is param retry_delay ) for all threads ?
On Jul 16, 2023, at 8:45 PM, Anatoliy <cphlpd@gmail.com> wrote:
Hi Alan ,team, I am testing scenario when main DB is temporary not available. I set rule in iptables to block all connections to DB.
The short answer is "don't do that".
In this situation freeradius should return Accept with some parameters to NAS.
i.e. you'd like FreeRADIUS to do that. What FreeRADIUS "should" do is limited by the available technologies, and time. Not all databases have fully asynchronous APIs. For example, MySQL added async client APIs only in 2019. We welcome patches to help make FreeRADIUS better. Perhaps you could contribute?
This part works fine, but in traffic dump I see that radius replay with big delay , some times upto 20-30 seconds. In log a lot messages like Mon Jul 17 06:12:23 2023 : Error: Unresponsive child for request 288, in component <core> module
Yes. The SQL queries are blocking.
I am trying to tune some parameters for sql connection, but this does not help.
None of the "pool" configuration items say that they help when the SQL server is blocked, or when the TCP connection between FreeRADIUS and the SQL server is blocked.
Can you recommend which params to change to prevent freeradius stuck on sql connection to DB ?
There is no magic configuration which is hidden until you ask on the mailing list. Everything is documented extensively, most commonly in the comments in the configuration files. If you don't see any documentation about dealign with blocked connections, then it doesn't deal with blocked connections.
Is it possible to prevent FR trying to connect to DB for some time (i think it is param retry_delay ) for all threads ?
Sure. Send a patch to fix it. We're working on fixing this in v4. But one of the reasons v4 is taking so long is that it's a very difficult problem to fix. And also that we get very, very, few patches from people outside of the core team. What works well is sending patches, fixes, bug reports, etc. What doesn't work well is "Why doesn't FreeRADIUS work the way I want?" It's Open Source. If you want it to behave differently, submit a patch. If you don't like the way it's working and don't want to submit a patch... well... we have other priorities. Alan DeKok.
Hi Alan , thank you for fast response, Unfortunately , I am really bad in programming , and freeradius will be better without my patches ... Now I am trying to better understand FR logic, When blocking tcp connection with DB , I just want to test scenario when DB down (for MW of due to some issue) So sql query always block core thread, and only option is to find way for faster detect that tcp session to DB fails ? For example max_retries option introduced in 3.2.3. But I think that retry_delay option should prevent all threads from connecting to DB for some time, if previous connection is fails. And these messages tells about this , or my understanding is wrong ? Increasing retry_delay should help faster reply to NAS with Access-Accept. Warning: rlm_sql (sql): Cannot open a new connection due to rate limit after failure On Mon, Jul 17, 2023 at 7:20 AM Alan DeKok <aland@deployingradius.com> wrote:
On Jul 16, 2023, at 8:45 PM, Anatoliy <cphlpd@gmail.com> wrote:
Hi Alan ,team, I am testing scenario when main DB is temporary not available. I set rule in iptables to block all connections to DB.
The short answer is "don't do that".
In this situation freeradius should return Accept with some parameters to NAS.
i.e. you'd like FreeRADIUS to do that.
What FreeRADIUS "should" do is limited by the available technologies, and time. Not all databases have fully asynchronous APIs. For example, MySQL added async client APIs only in 2019.
We welcome patches to help make FreeRADIUS better. Perhaps you could contribute?
This part works fine, but in traffic dump I see that radius replay with big delay , some times upto 20-30 seconds. In log a lot messages like Mon Jul 17 06:12:23 2023 : Error: Unresponsive child for request 288, in component <core> module
Yes. The SQL queries are blocking.
I am trying to tune some parameters for sql connection, but this does not help.
None of the "pool" configuration items say that they help when the SQL server is blocked, or when the TCP connection between FreeRADIUS and the SQL server is blocked.
Can you recommend which params to change to prevent freeradius stuck on sql connection to DB ?
There is no magic configuration which is hidden until you ask on the mailing list. Everything is documented extensively, most commonly in the comments in the configuration files. If you don't see any documentation about dealign with blocked connections, then it doesn't deal with blocked connections.
Is it possible to prevent FR trying to connect to DB for some time (i think it is param retry_delay ) for all threads ?
Sure. Send a patch to fix it.
We're working on fixing this in v4. But one of the reasons v4 is taking so long is that it's a very difficult problem to fix. And also that we get very, very, few patches from people outside of the core team.
What works well is sending patches, fixes, bug reports, etc. What doesn't work well is "Why doesn't FreeRADIUS work the way I want?"
It's Open Source. If you want it to behave differently, submit a patch. If you don't like the way it's working and don't want to submit a patch... well... we have other priorities.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 17/07/2023 03:22, Anatoliy wrote:
So sql query always block core thread, and only option is to find way for faster detect that tcp session to DB fails ? For example max_retries option introduced in 3.2.3.
The max_retries option was added to prevent every possible connection being tried when a query fails - so it does improve the speed of detecting failures. With MySQL / MariaDB as the database you also want to look at the query_timeout option. Not all the database client libraries support setting a timeout, but the MySQL client does.
But I think that retry_delay option should prevent all threads from connecting to DB for some time, if previous connection is fails. And these messages tells about this , or my understanding is wrong ? Increasing retry_delay should help faster reply to NAS with Access-Accept. retry_delay puts a delay on attempting to bring up new database connections if the previous attempt failed. It is a balancing act between persistently attempting to bring up new connections when something is wrong, and just saying "this didn't work last time, we're going to wait a while before we try again", which can cause a slower recovery when the database is available again.
-- Nick Porter
Hi Nick , thank you. On Mon, Jul 17, 2023 at 5:07 PM Nick Porter <nick@portercomputing.co.uk> wrote:
On 17/07/2023 03:22, Anatoliy wrote:
So sql query always block core thread, and only option is to find way for faster detect that tcp session to DB fails ? For example max_retries option introduced in 3.2.3.
The max_retries option was added to prevent every possible connection being tried when a query fails - so it does improve the speed of detecting failures.
With MySQL / MariaDB as the database you also want to look at the query_timeout option. Not all the database client libraries support setting a timeout, but the MySQL client does.
But I think that retry_delay option should prevent all threads from connecting to DB for some time, if previous connection is fails. And these messages tells about this , or my understanding is wrong ? Increasing retry_delay should help faster reply to NAS with Access-Accept. retry_delay puts a delay on attempting to bring up new database connections if the previous attempt failed. It is a balancing act between persistently attempting to bring up new connections when something is wrong, and just saying "this didn't work last time, we're going to wait a while before we try again", which can cause a slower recovery when the database is available again.
-- Nick Porter
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Jul 16, 2023, at 10:22 PM, Anatoliy <cphlpd@gmail.com> wrote:
Hi Alan , thank you for fast response, Unfortunately , I am really bad in programming , and freeradius will be better without my patches ... Now I am trying to better understand FR logic, When blocking tcp connection with DB , I just want to test scenario when DB down (for MW of due to some issue)
The solution here is to make sure that your database stays up. Plus, "TCP connection blocked" is NOT the same as "database is down". When the database is down, existing TCP connections are closed, and new connections are refused. FreeRADIUS handles this situation well. When the TCP connections are blocked as in your example, there are very few ways to recover from it. As I said, many SQL client libraries are not asynchronous. Which means that FreeRADIUS calls the library, and the library tries to connect to the SQL server. If the SQL server (or TCP connection) blocks, then so does the library, and so does FreeRADIUS. There is pretty much nothing that we can do to fix that. We are *not* going to write our own non-blocking SQL client library. It's hard enough to write a RADIUS server.
So sql query always block core thread, and only option is to find way for faster detect that tcp session to DB fails ?
Read my previous message again. I answered this. My answer won't change if you keep asking the question.
For example max_retries option introduced in 3.2.3. But I think that retry_delay option should prevent all threads from connecting to DB for some time, if previous connection is fails.
I think I want a pony for my birthday. That's a nice thought. But it's not realistic.
And these messages tells about this , or my understanding is wrong ? Increasing retry_delay should help faster reply to NAS with Access-Accept.
No. The server configuration is well documented. The "retry_delay" options is well documented. And it's *not* documented as "causing a faster reply to the NAS". I really don't know how to make the documentation any more clear. I'm not sure why you're reading it, and concluding that "retry_delay" does something completely different from what the documentation says. Stop wasting your time trying to say how FreeRADIUS "should" work. There are a lot of complex issues involved. You need to understand all of those issues before having an informed opinion on what "should" happen. It's also difficult to have an informed opinion if the existing documentation is misinterpreted. Alan DeKok.
Hi Alan, there are 2 options how DB can fail. 1) application fail, in this case host os will reply with icmp port unreachable, which should help with faster detection that DB failed 2) Host(where DB running ) failed, of network problem between radius and DB, in this case we will not receive any reply . And I am reproducing this scenario, by blocking incoming tcp packets from port 1521 (oracle) . On Mon, Jul 17, 2023 at 5:53 PM Alan DeKok <aland@deployingradius.com> wrote:
On Jul 16, 2023, at 10:22 PM, Anatoliy <cphlpd@gmail.com> wrote:
Hi Alan , thank you for fast response, Unfortunately , I am really bad in programming , and freeradius will be better without my patches ... Now I am trying to better understand FR logic, When blocking tcp
connection
with DB , I just want to test scenario when DB down (for MW of due to some issue)
The solution here is to make sure that your database stays up. Plus, "TCP connection blocked" is NOT the same as "database is down".
When the database is down, existing TCP connections are closed, and new connections are refused. FreeRADIUS handles this situation well.
When the TCP connections are blocked as in your example, there are very few ways to recover from it. As I said, many SQL client libraries are not asynchronous. Which means that FreeRADIUS calls the library, and the library tries to connect to the SQL server. If the SQL server (or TCP connection) blocks, then so does the library, and so does FreeRADIUS.
There is pretty much nothing that we can do to fix that. We are *not* going to write our own non-blocking SQL client library. It's hard enough to write a RADIUS server.
So sql query always block core thread, and only option is to find way for faster detect that tcp session to DB fails ?
Read my previous message again. I answered this. My answer won't change if you keep asking the question.
For example max_retries option introduced in 3.2.3. But I think that retry_delay option should prevent all threads from connecting to DB for some time, if previous connection is fails.
I think I want a pony for my birthday. That's a nice thought. But it's not realistic.
And these messages tells about this , or my understanding is wrong ? Increasing retry_delay should help faster reply to NAS with Access-Accept.
No.
The server configuration is well documented. The "retry_delay" options is well documented. And it's *not* documented as "causing a faster reply to the NAS".
I really don't know how to make the documentation any more clear. I'm not sure why you're reading it, and concluding that "retry_delay" does something completely different from what the documentation says.
Stop wasting your time trying to say how FreeRADIUS "should" work. There are a lot of complex issues involved. You need to understand all of those issues before having an informed opinion on what "should" happen. It's also difficult to have an informed opinion if the existing documentation is misinterpreted.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Jul 18, 2023, at 12:45 AM, Anatoliy <cphlpd@gmail.com> wrote:
Hi Alan, there are 2 options how DB can fail.
That's nice. You seem to be stuck on how things *should* work. I've explained why things work the way they do, and what limitations are creating the current behavior. I've explained what we're doing to fix it. I've explained how you can help. This is how it works. It's not productive to keep wishing that things were different. Alan DeKok.
I am not stuck on how it should work. I already found solution how faster detect that db fail and how FR can check this state, without stuck on checking DB. Solution is very simple. 1) external script which check db (detect time ~6-8 sec) and write this status to file 2) perl script in FR which read status from file and depending on status send or not request to db Using file for transfering state between script and FR not the best solution but I didnot found anything better(I will be happy if you can recommend better ) \ AK
On 18 Jul 2023, at 22:39, Alan DeKok <aland@deployingradius.com> wrote:
On Jul 18, 2023, at 12:45 AM, Anatoliy <cphlpd@gmail.com> wrote:
Hi Alan, there are 2 options how DB can fail.
That's nice.
You seem to be stuck on how things *should* work.
I've explained why things work the way they do, and what limitations are creating the current behavior. I've explained what we're doing to fix it. I've explained how you can help.
This is how it works. It's not productive to keep wishing that things were different.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Tue, 18 Jul 2023 at 16:45, Anatoliy Kushner <cphlpd@gmail.com> wrote: > I am not stuck on how it should work. I already found solution how faster detect that db fail and how FR can check this state, without stuck on checking DB. > > Solution is very simple. > 1) external script which check db (detect time ~6-8 sec) and write this status to file > 2) perl script in FR which read status from file and depending on status send or not request to db > > Using file for transfering state between script and FR not the best solution but I didnot found anything better(I will be happy if you can recommend better ) We have writable rlm_always instances that can be set via an external trigger: https://github.com/FreeRADIUS/freeradius-server/blob/v3.2.x/raddb/sites-available/resource-check > > On 18 Jul 2023, at 22:39, Alan DeKok <aland@deployingradius.com> wrote: > > > > On Jul 18, 2023, at 12:45 AM, Anatoliy <cphlpd@gmail.com> wrote: > >> > >> Hi Alan, there are 2 options how DB can fail. > > > > That's nice. > > > > You seem to be stuck on how things *should* work. > > > > I've explained why things work the way they do, and what limitations are creating the current behavior. I've explained what we're doing to fix it. I've explained how you can help. > > > > This is how it works. It's not productive to keep wishing that things were different. > > > > Alan DeKok. > > > > - > > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Terry Burton Consulting Ltd is a company registered in England and Wales. Registered number: 11926244. VAT number: GB321512746. Registered office: Office 1 Brunswick House, Brunswick Way, Liverpool, Merseyside, England, L3 4BN.
Hi Terry , thank you for idea , I test resource-check, it works , but
trigger DB check by FR looks not very interesting,
But I am found another soluthis , I setup coa on FR and send status of DB
from external script (where is I can control how to check DB) and update
db_status
server coa {
recv-coa {
if ( &State == "OK" ) {
%{db_online:alive}
} else {
%{db_online:fail}
}
ok
}
With this configuration, I can , for example, mark DB failed , before MW .
and this will prevent unnecessary connections to DB that will fail...
Thanks again for idea
On Tue, Jul 18, 2023 at 10:26 PM Terry Burton <terry.burton@gmail.com>
wrote:
> On Tue, 18 Jul 2023 at 16:45, Anatoliy Kushner <cphlpd@gmail.com> wrote:
> > I am not stuck on how it should work. I already found solution how
> faster detect that db fail and how FR can check this state, without stuck
> on checking DB.
> >
> > Solution is very simple.
> > 1) external script which check db (detect time ~6-8 sec) and write this
> status to file
> > 2) perl script in FR which read status from file and depending on status
> send or not request to db
> >
> > Using file for transfering state between script and FR not the best
> solution but I didnot found anything better(I will be happy if you can
> recommend better )
>
> We have writable rlm_always instances that can be set via an external
> trigger:
>
>
> https://github.com/FreeRADIUS/freeradius-server/blob/v3.2.x/raddb/sites-available/resource-check
>
>
> > > On 18 Jul 2023, at 22:39, Alan DeKok <aland@deployingradius.com>
> wrote:
> > >
> > > On Jul 18, 2023, at 12:45 AM, Anatoliy <cphlpd@gmail.com> wrote:
> > >>
> > >> Hi Alan, there are 2 options how DB can fail.
> > >
> > > That's nice.
> > >
> > > You seem to be stuck on how things *should* work.
> > >
> > > I've explained why things work the way they do, and what limitations
> are creating the current behavior. I've explained what we're doing to fix
> it. I've explained how you can help.
> > >
> > > This is how it works. It's not productive to keep wishing that
> things were different.
> > >
> > > Alan DeKok.
> > >
> > > -
> > > List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
> > -
> > List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
>
>
> --
>
> Terry Burton Consulting Ltd is a company registered in England and
> Wales. Registered number: 11926244. VAT number: GB321512746.
> Registered office: Office 1 Brunswick House, Brunswick Way, Liverpool,
> Merseyside, England, L3 4BN.
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
On Jul 19, 2023, at 2:25 AM, Anatoliy <cphlpd@gmail.com> wrote:
With this configuration, I can , for example, mark DB failed , before MW . and this will prevent unnecessary connections to DB that will fail...
This helps a little bit, but it doesn't help when connections are blocked. The only real solution is to not have the DB go down. While this may seem extreme, I would also suggest that it's a little weird to make FreeRADIUS depend on the DB, and then have the DB randomly go away. The effort should put into making sure the DB doesn't go down. Patching around an unstable DB by poking FreeRADIUS is like putting a tiny bandaid on a large injury. The bandaid may be pretty but it's not going to do a whole lot. Alan DeKok.
Hi Alan , DB that never goes down - is fantastic. At least while customer is understand that investment in DB (which will never goes down) will never payback. On Wed, Jul 19, 2023 at 6:04 PM Alan DeKok <aland@deployingradius.com> wrote:
On Jul 19, 2023, at 2:25 AM, Anatoliy <cphlpd@gmail.com> wrote:
With this configuration, I can , for example, mark DB failed , before MW . and this will prevent unnecessary connections to DB that will fail...
This helps a little bit, but it doesn't help when connections are blocked.
The only real solution is to not have the DB go down. While this may seem extreme, I would also suggest that it's a little weird to make FreeRADIUS depend on the DB, and then have the DB randomly go away.
The effort should put into making sure the DB doesn't go down. Patching around an unstable DB by poking FreeRADIUS is like putting a tiny bandaid on a large injury. The bandaid may be pretty but it's not going to do a whole lot.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
i think there was mention that the DB is Oracle, which may get cost prohibitive, but you can put Highly Available implementations in place to improve things. Oracle has RAC, Data Guard and Active Data Guard. PostgreSQL has no active/active but you can do HA with primary failover with HA Proxy, ETCd and Patroni. MariaDB has active/active with Galera replication and can be behind an HA Proxy VIP too. so many times on this list i have seen attempts to have FR clobbered, in an attempt to make a different service reliable, when the effort needs to be spent on the other service and not FR. LDAP/AD is another where attempts have been made to have FR make up for a lack of HA design in other infrastructure. i am using BGP to advertise routes to IPs that are not on the wire, and therefore can be anycasted. dns, kerberos, ntp, radius and syslog are all stateless protocols that can be anycasted among several instances, all using the same, single IP. i have 2 FR instances setup to listen on 192.168.254.3 and the router has multiple paths configured with multiple routes to the same IP. the FR instances do not know about each other and simply respond to the events sent to them. i am not using the replicate module between the FR instances. no need... the mariadb that they talk to, or the LDAP instances they talk to, are behind an HA Proxy VIP, and the load balancer routes traffic to the selected instance, based on the balancing method i chose, least connections. the kerberos instance FR talks to is over anycast. by putting HA implementations into the other services, i dont have to worry about FR not having a service instance available because one single instance is down or otherwise unavailable. you might look into some HA options for your database, and point FR at a VIP or anycast IP, as appropriate, and not have to jump through hoops, trying to have FR make up for deficiencies elsewhere. HTH, brendan On 7/19/23 8:37 AM, Anatoliy wrote:
Hi Alan , DB that never goes down - is fantastic. At least while customer is understand that investment in DB (which will never goes down) will never payback.
On Wed, Jul 19, 2023 at 6:04 PM Alan DeKok <aland@deployingradius.com> wrote:
On Jul 19, 2023, at 2:25 AM, Anatoliy <cphlpd@gmail.com> wrote:
With this configuration, I can , for example, mark DB failed , before MW . and this will prevent unnecessary connections to DB that will fail... This helps a little bit, but it doesn't help when connections are blocked.
The only real solution is to not have the DB go down. While this may seem extreme, I would also suggest that it's a little weird to make FreeRADIUS depend on the DB, and then have the DB randomly go away.
The effort should put into making sure the DB doesn't go down. Patching around an unstable DB by poking FreeRADIUS is like putting a tiny bandaid on a large injury. The bandaid may be pretty but it's not going to do a whole lot.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Jul 19, 2023, at 8:37 AM, Anatoliy <cphlpd@gmail.com> wrote:
Hi Alan , DB that never goes down - is fantastic. At least while customer is understand that investment in DB (which will never goes down) will never payback.
You know very well that I didn't mean some magic system where nothing ever goes wrong. My point (as I've said repeatedly) is that if your DB is down for extended periods, that's a problem. Which means that you should fix the DB. Add monitoring, replicas, etc. Trying to "fix" FreeRADIUS is wasting your time. That work will not pay back as much as fixing the DB. It makes no sense to go "Hm... the database is down. I know, I won't fix that. Instead, I'll go poke FreeRADIUS!" It is entirely the wrong approach. Perhaps you could listen to the people who've been doing this for decades. They might know what they're talking about. It doesn't make sense to ask for advice, and then go "Nah, I'm not going to do what the experts suggest. I'm going to do something that they say won't help!" See also Brendan's comments. He has some good advice, too. Alan DeKok.
Hi Alan , Brendan , thank you for comments, I need time to think out this On Wed, Jul 19, 2023 at 10:05 PM Alan DeKok <aland@deployingradius.com> wrote:
On Jul 19, 2023, at 8:37 AM, Anatoliy <cphlpd@gmail.com> wrote:
Hi Alan , DB that never goes down - is fantastic. At least while customer is understand that investment in DB (which will never goes down) will never payback.
You know very well that I didn't mean some magic system where nothing ever goes wrong.
My point (as I've said repeatedly) is that if your DB is down for extended periods, that's a problem. Which means that you should fix the DB. Add monitoring, replicas, etc.
Trying to "fix" FreeRADIUS is wasting your time. That work will not pay back as much as fixing the DB.
It makes no sense to go "Hm... the database is down. I know, I won't fix that. Instead, I'll go poke FreeRADIUS!" It is entirely the wrong approach.
Perhaps you could listen to the people who've been doing this for decades. They might know what they're talking about. It doesn't make sense to ask for advice, and then go "Nah, I'm not going to do what the experts suggest. I'm going to do something that they say won't help!"
See also Brendan's comments. He has some good advice, too.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (6)
-
Alan DeKok -
Anatoliy -
Anatoliy Kushner
-
Brendan Kearney -
Nick Porter -
Terry Burton