Hi, we've been having fun with the SQL backend connection from our FR 2.1.4 yesterday night, when auth load suddenly increased tenfold. The issue at hand was that we execute a query in post-auth that takes ~ 300 milliseconds. We have configured the rlm_sql_mysql instance for 5 sockets to the MySQL backend, which means an approximate 15 logins per second sustainable (which is way more than enough for us during normal operation). As the auth load increased, so did the number of queries, and it went beyond 15. Auths would have failed (but luckily the query was in a redundant { ... ok } block, and so nothing serious happened). It's important to note that the actual *load* on the MySQL backend was negligible all the time, so this is not the usually-reported "database too slow" problem. These queries simply are quite sophisticated, and they take some time to execute. In short: more sockets would have solved the problem. But you wouldn't want so many sockets day-to-day. Something dynamic would be a really nice feature. In a way just like the thread pool of FreeRADIUS itself is managed: define a minimum number of SQL sockets, and a min-spare, max-spare, max-total pair so that the number of connections grows and shrinks as required. Does that feature make sense? Greetings, Stefan Winter -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473
Hi, back in the day of using MySQL I set ours to 10 which is way more than enough (and negligible for MySQL connectivity usage) - I also found that values over 10 caused problems - the system just hanging...so 10 it was. however, what we also did was check the queries to find out how long they took and why they took that long. your value of 300ms is extremely long - I would run a few EXPLAINs on your DB to find why they are that high...and then create eg the required indexes on the tables to make them more efficient. we went from 200ms to 90ms after just 3 indexes were put onto the system. we also moved away from live SQL accounting to the virtual server redundant detail logging method which massively reduced load and time for the RADIUS server to do its work. alan
Stefan Winter wrote:
In short: more sockets would have solved the problem. But you wouldn't want so many sockets day-to-day. Something dynamic would be a really nice feature. In a way just like the thread pool of FreeRADIUS itself is managed: define a minimum number of SQL sockets, and a min-spare, max-spare, max-total pair so that the number of connections grows and shrinks as required.
Does that feature make sense?
Yes and no. I'm wary of adding yet *another* pool implementation to the server. I'd rather just get rid of the SQL pool code entirely. Instead, it should create one SQL socket per thread, and keep re-using that in the same thread. More threads will give you more sockets. When the thread goes away, the socket can be closed, too. Alan DeKok.
Hi,
Yes and no. I'm wary of adding yet *another* pool implementation to the server.
I'd rather just get rid of the SQL pool code entirely. Instead, it should create one SQL socket per thread, and keep re-using that in the same thread. More threads will give you more sockets. When the thread goes away, the socket can be closed, too.
Sounds interesting. There are some things to consider I guess. Threads are not bound to specific virtual servers, right? Then they may be called for multiple uses, multiple sql module instances and may thus require multiple *different* sockets to *different* servers. One idea would be to allow every thread to use one socket per sql module instance it uses. And/or, bind threads to virtual servers so that they always get to use the same sql modules, as configured in that virtual server. And someone deploying it needs to keep a firm eye on the maximum number of sockets his SQL server could get since SQL servers typically impose a max per-user, per-IP or global connection limit. So 15 threads, three SQL modules each can already get up to a max of 45 sockets in use... Greetings, Stefan Winter -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473
participants (3)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
Stefan Winter