multiple freeradius instances with sqlippool, is it safe?

[Nick Porter]

As Nathan says, it is worth looking at the most recent queries in 
FreeRADIUS version 3.0.25 - significant optimisation has been done on 
the default IP pool queries of late.

The key is that appropriate record locking is done during the allocation 
of addresses.

With PostgreSQL, the UPDATE ... RETURNING ... syntax can be used to 
reduce the the number of round trips to the database, also reducing the 
time that records are locked.

Presuming you are using a PostgreSQL version >= 9.5, ensure you include 
SKIP LOCKED, which makes a huge difference to the query performance.

Nick

On 22/03/2022 09:00, Nathan Ward wrote:
>> On 22/03/2022, at 9:43 PM, Matteo Sgalaberni <sgala at sgala.com> wrote:
>>
>> Hello,
>>
>> I'm would like to have multiple freeradius instances (processes on different servers) all pointing to the same postgres database.
>> (My goal is to have a freeradius service redundant, I have a postgresql cluster with patroni/haproxy and each radius server point to all of them via a dsn multiple host connection definition and obviously it's connected to the master)
>>
>> I would like to use the sqlipool module. Is it safe use it in this scenario or does exists some side effects (I have some ideas of what could be wrong but I would like a your opinion...)?
>>
>> Do you have suggestions to what to pay attention to in this scenario?
> Yes - provided you use the queries which ship with FreeRADIUS, this should be perfectly safe. I use this in a few networks (not small networks, either) and it works great. I’ve not used the multiple host definition thing you talk about - I have used pgpool as a proxy which proxies to the master.
>
> If you want some extra confidence, you may note that a single FreeRADIUS server establishes multiple connections to the database server and runs transactions in parallel - database connections from different RADIUS servers is no different in terms of concurrency in the database than multiple connections from one server.
>
> Note there has been work in the past several years on those sqlippool queries to improve performance and so on - you should make sure you’re running the latest code.
>
> --
> Nathan Ward
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-- 
Nick Porter

Porter Computing Ltd
Registered in England No 12659380

-------------- next part --------------
A non-text attachment was scrubbed...
Name: OpenPGP_signature
Type: application/pgp-signature
Size: 665 bytes
Desc: OpenPGP digital signature
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20220322/1438a5ce/attachment-0001.sig>