num_sql_socks

[Peter Nixon]

On Sat 23 Sep 2006 00:02, Kris wrote:
> >> In the tuning guide, where it states "Tune the num_sql_socks to be
> >> larger than the
>
> number
>
> >> of simultaneous authentication/accounting requests" does this mean if I
> >> have 6700 user accounts, I would want to set the num_sql_socks to be
> >> larger than that since it is possible that all will try to connect at
> >> one time, say given, a network outage/reinstatement?
> >
> >it means set it to be as many requests as you let FreeRADIUS deal with
> >max_servers is involved here - as is max_requests. if you have a maximum
> >of 32 freeradius threads dealing with requests then you really need
> >that number of SQL connections...or the SQL is your bottle-neck
> >
> >alan
> >-
> >List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
>
> Last stupid question for me then... I'm trying to figure out why my
> freeradius/mysql locks up when we return from a network outage w/ thousands
> of simultaneous requests.  I assumed because I'm a MySQL novice it was
> because of my MySQL settings causing slow responses. I've checked the
> indexes, ran the select/update statements through explain, even set up slow
> query logging, and I'm not finding an issue (ie it didn't jump out and bite
> me)  I'm in the process of adding a replication server for backup SQL for
> freeradius, but from what I've read, it seems Freeradius w/ MySQL should be
> able to handle it even on one system. Any suggestions on what I may be
> overlooking or should be testing?

There are a couple of things you can do to solve help solve this problem.

Firstly, I had similar problems (about 4 years ago) with MySQL locking up/core 
dumping under load. A switch to PostgreSQL solved it and I haven't used MySQL 
since. (I had assumed that MySQL had improved since then but I guess not)

Secondly, you need to split your Auth and Acct queries into different database 
pools. You can do this in one of the following ways:

* Setup 2 identical sql modules called sql1 and sql2 and use sql1 in authorize 
{} and sql2 in accounting {}

* Use radrelay for accounting

* Use rlm_sql_log for accounting

All 3 methods will allow you to give a fixed number of sql connection pool to 
Auth and a separate fixed connection pool to Accounting. Which method you use 
depends on exactly what you are trying to achieve with radrelay being my 
preference for systems that you need to really scale. In all instances, if 
you have a master/slave database setup, have your auth queries running 
against the slave and acct to the master (Or a completely different 
Accounting DB server)

Hope that helps

Cheers
-- 

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20060923/a261a002/attachment.pgp>