Problem with MySQL connection through load balancer
Hello, I am using Freeradius wioth MySQL backend. The SQL connection goes through a load balancer cluster which uses VRRP, furthermore these load balancers firewall the traffic. The problem occurs when the VRRP master goes down, and the other load balancer takes over the VRRP IP address. The problem is that Freeradius stops processing requests and I see erros in the log like: Error: WARNING: Unresponsive thread 7710 for request 0, in component <core> module <queue> Info: WARNING: Child is hung for request 7707 in component <core> module <queue>. I understand that this is not a Freeradius issue, but rather the fact that the VRRP master is now on a different device that does not know about the connection that Freeradius thinks is still established. But instead of trying to create a new connection, Freeradius just continuously drops requests and writes these message to the log. And never recovers. I have left it for up to 15 min, but stays In this state. If I restart radius, it recovers and starts processing as normal. But I don't want to have to do this. I have tried to use the 'lifetime = 120' option in the sql.conf Under normal operation, this will cause radius to close and re-establish the SQL connection every 120 seconds, so I hoped that when in this stuck state, it would do the same and re-establish the connection. But does not. Is there anything someone can suggest? I don't have the ability to sync the session table between the 2 load balancers. Thanks.
On 23/07/14 17:10, Dean Goldhill wrote:
I have tried to use the ‘lifetime = 120’ option in the sql.conf
Under normal operation, this will cause radius to close and re-establish the SQL connection every 120 seconds, so I hoped that when in this stuck state, it would do the same and re-establish the connection. But does not.
The problem is, FreeRADIUS will be blocked inside the mysql libraries. So "lifetime" probably won't help.
Is there anything someone can suggest?
I don’t have the ability to sync the session table between the 2 load balancers.
The problem isn't FreeRADIUS - it's the mysql libraries. They're not detecting the dead connections. Does the load balancer have the ability to return a TCP RST or ICMP error when it receives a packet for an unknown session? If so, this will probably trigger the mysql libraries to drop the connection, and FreeRADIUS to reconnect. Otherwise, you'll need to investigate if MySQL has any form of dead connection detection, and how you can persuade your mysql libraries to do it. TCP keepalives probably won't help - they're usually too slow for this kind of thing. Alternatively, something like: http://dev.mysql.com/downloads/mysql-proxy/ ...could be run locally. We use pgbouncer on postgres for this - it can check the connection is alive before sending a query and reconnect as needed.
Thanks very much. That does clear up where I should be looking. I will update if I find a solution. Sent from my iPhone
On 23 Jul 2014, at 17:39, "Phil Mayers" <p.mayers@imperial.ac.uk> wrote:
On 23/07/14 17:10, Dean Goldhill wrote:
I have tried to use the ‘lifetime = 120’ option in the sql.conf
Under normal operation, this will cause radius to close and re-establish the SQL connection every 120 seconds, so I hoped that when in this stuck state, it would do the same and re-establish the connection. But does not.
The problem is, FreeRADIUS will be blocked inside the mysql libraries. So "lifetime" probably won't help.
Is there anything someone can suggest?
I don’t have the ability to sync the session table between the 2 load balancers.
The problem isn't FreeRADIUS - it's the mysql libraries. They're not detecting the dead connections.
Does the load balancer have the ability to return a TCP RST or ICMP error when it receives a packet for an unknown session? If so, this will probably trigger the mysql libraries to drop the connection, and FreeRADIUS to reconnect.
Otherwise, you'll need to investigate if MySQL has any form of dead connection detection, and how you can persuade your mysql libraries to do it.
TCP keepalives probably won't help - they're usually too slow for this kind of thing.
Alternatively, something like:
http://dev.mysql.com/downloads/mysql-proxy/
...could be run locally. We use pgbouncer on postgres for this - it can check the connection is alive before sending a query and reconnect as needed. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
If no other sane options exist then enabling more aggressive TCP-keepalive probes can often help here. On 23/07/2014 23:59, "Dean Goldhill" <dgoldhill@netutils.com> wrote: >Thanks very much. >That does clear up where I should be looking. > >I will update if I find a solution. > >Sent from my iPhone > >> On 23 Jul 2014, at 17:39, "Phil Mayers" <p.mayers@imperial.ac.uk> wrote: >> >>> On 23/07/14 17:10, Dean Goldhill wrote: >>> >>> I have tried to use the Œlifetime = 120¹ option in the sql.conf >>> >>> Under normal operation, this will cause radius to close and >>>re-establish >>> the SQL connection every 120 seconds, so I hoped that when in this >>>stuck >>> state, it would do the same and re-establish the connection. But does >>>not. >> >> The problem is, FreeRADIUS will be blocked inside the mysql libraries. >>So "lifetime" probably won't help. >> >>> >>> Is there anything someone can suggest? >>> >>> I don¹t have the ability to sync the session table between the 2 load >>> balancers. >> >> The problem isn't FreeRADIUS - it's the mysql libraries. They're not >>detecting the dead connections. >> >> Does the load balancer have the ability to return a TCP RST or ICMP >>error when it receives a packet for an unknown session? If so, this will >>probably trigger the mysql libraries to drop the connection, and >>FreeRADIUS to reconnect. >> >> Otherwise, you'll need to investigate if MySQL has any form of dead >>connection detection, and how you can persuade your mysql libraries to >>do it. >> >> TCP keepalives probably won't help - they're usually too slow for this >>kind of thing. >> >> Alternatively, something like: >> >> http://dev.mysql.com/downloads/mysql-proxy/ >> >> ...could be run locally. We use pgbouncer on postgres for this - it can >>check the connection is alive before sending a query and reconnect as >>needed. >> - >> List info/subscribe/unsubscribe? See >>http://www.freeradius.org/list/users.html >- >List info/subscribe/unsubscribe? See >http://www.freeradius.org/list/users.html Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trademarks of British Sky Broadcasting Group plc and Sky International AG and are used under licence. British Sky Broadcasting Limited (Registration No. 2906991), Sky-In-Home Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited (Registration No. 2340150) are direct or indirect subsidiaries of British Sky Broadcasting Group plc (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
participants (3)
-
Dean Goldhill -
Phil Mayers -
Winfield, Alister