correctly set num_sql_sock in sql accoutnig
Hello, Is there a way to know how many sql sockets needs to be open at startup for a particular accounting db? I want to have sufficent number of such sockets but don't want to loose time and ressources opening too much of them. thx
Is there a way to know how many sql sockets needs to be open at startup for a particular accounting db? I want to have sufficent number of such sockets but don't want to loose time and ressources opening too much of them.
doc/tuning_guide. There is no point in opening more than 20-25 unless you have a database cluster. Ivan Kalik Kalik Informatika ISP
Alexandre Chapellon wrote:
Is there a way to know how many sql sockets needs to be open at startup for a particular accounting db?
How long do database writes take? How many writes can you do simultaneously? How many packets/s does the server get? Generally, you should have as many SQL sockets as threads. Any more, and they won't be used. Any less, and the threads might starve.
I want to have sufficent number of such sockets but don't want to loose time and ressources opening too much of them.
The LDAP && SQL modules really need to move to a dynamic connection pool. It would be much easier for the administrators. Alan DeKok.
Le lundi 14 septembre 2009 à 22:32 +0200, Alan DeKok a écrit :
Alexandre Chapellon wrote:
Is there a way to know how many sql sockets needs to be open at startup for a particular accounting db?
How long do database writes take?
How many writes can you do simultaneously?
As many as simultaneous query I guess...
How many packets/s does the server get?
For the authtentication database I have about 10 authentication queries/s but regularly reaches 30/s when problems on BAS or DSLAM outcome (max value since last year is ~80/s).
Generally, you should have as many SQL sockets as threads.
Thread like in start_servers and max_servers in radiusd.conf?
Any more, and they won't be used. Any less, and the threads might starve.
I want to have sufficent number of such sockets but don't want to loose time and ressources opening too much of them.
The LDAP && SQL modules really need to move to a dynamic connection pool. It would be much easier for the administrators.
Indeed
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
I forgot to include infos in previous mail... sorry about that Le lundi 14 septembre 2009 à 22:32 +0200, Alan DeKok a écrit :
Alexandre Chapellon wrote:
Is there a way to know how many sql sockets needs to be open at startup for a particular accounting db?
How long do database writes take?
insert in acccouting db takes ~ 10ms delete from accouting db takes ~ 25ms
How many writes can you do simultaneously?
As many as simultaneous query I guess...
How many packets/s does the server get?
For the authtentication database I have about 10 authentication queries/s but regularly reaches 30/s when problems on BAS or DSLAM outcome (max value since last year is ~80/s).
Generally, you should have as many SQL sockets as threads.
Thread like in start_servers and max_servers in radiusd.conf?
Any more, and they won't be used. Any less, and the threads might starve.
I want to have sufficent number of such sockets but don't want to loose time and ressources opening too much of them.
The LDAP && SQL modules really need to move to a dynamic connection pool. It would be much easier for the administrators.
Indeed
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Alexandre Chapellon wrote:
How long do database writes take? insert in acccouting db takes ~ 10ms delete from accouting db takes ~ 25ms
So it can handle roughly 40-100 packets per second, depending on the traffic.
For the authtentication database I have about 10 authentication queries/s but regularly reaches 30/s when problems on BAS or DSLAM outcome (max value since last year is ~80/s).
I'd say you're running pretty close to the border of what is stable. You're OK for normal traffic, but large spikes might cause problems (delays in writes, etc.) If you're planning on growing the number of users you have, I'd take a look at upgrading the DB.
Generally, you should have as many SQL sockets as threads. Thread like in start_servers and max_servers in radiusd.conf?
Yes. Alan DeKok.
Le mardi 15 septembre 2009 à 00:13 +0200, Alan DeKok a écrit :
Alexandre Chapellon wrote:
How long do database writes take? insert in acccouting db takes ~ 10ms delete from accouting db takes ~ 25ms
So it can handle roughly 40-100 packets per second, depending on the traffic.
with a single freeradius process isn't it? I just notice something strange... I have start_servers set to 5 but when starting freeradius ps only report one freeradius proccess... is it something normal? regards
For the authtentication database I have about 10 authentication queries/s but regularly reaches 30/s when problems on BAS or DSLAM outcome (max value since last year is ~80/s).
I'd say you're running pretty close to the border of what is stable. You're OK for normal traffic, but large spikes might cause problems (delays in writes, etc.)
If you're planning on growing the number of users you have, I'd take a look at upgrading the DB.
Generally, you should have as many SQL sockets as threads. Thread like in start_servers and max_servers in radiusd.conf?
Yes.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi,
How long do database writes take?
insert in acccouting db takes ~ 10ms delete from accouting db takes ~ 25ms
For the authtentication database I have about 10 authentication queries/s but regularly reaches 30/s when problems on BAS or DSLAM outcome (max value since last year is ~80/s).
from those details it looks like you could handle 100 inserts per seond with a single process and 40 deletes per second. worse case you came up with was 80/s - which is fine for inserts but tricky for deletes - so you'd need just 2 sockets on a rainy day. - but then you've got things like table locking if using MySQL with eg myISAM engine so you'd have to probably double that to deal with the slower queries - make it 4 sockets. I've only made FreeRADIUS cry with mySQL when hitting it with really big slow queries at which point it choked all its handles and started to get really angry but a couple of optimisations later with some indexes and a change of DB engine (InnoDB) and things were fine - to ensure future happiness a migration to postgres was undertaken (pretty painless on the FR side of things...updating other code was more ...interesting... lets say) and we then moved to buffered_sql for the heavy stuff - that thread chugs along in the background inserting/updating table values when it needs to. FreeRADIUS and accouting DB took up too much time a while back.. a distraction that I could have done without but since the move to a better DB (I know..i'm stirring now..) and moving to 'out of band' DB updates its 'just done its job(tm)' PS there is an upcoming webcast about FreeRADIUS and mysql clusters - if you are into MySQL and performance/reliability I'd wholely recommend getting onto that. alan
Thanks Alan, I'm using postgresql Le lundi 14 septembre 2009 à 23:15 +0100, Alan Buxey a écrit :
Hi,
How long do database writes take?
insert in acccouting db takes ~ 10ms delete from accouting db takes ~ 25ms
For the authtentication database I have about 10 authentication queries/s but regularly reaches 30/s when problems on BAS or DSLAM outcome (max value since last year is ~80/s).
from those details it looks like you could handle 100 inserts per seond with a single process and 40 deletes per second. worse case you came up with was 80/s - which is fine for inserts but tricky for deletes - so you'd need just 2 sockets on a rainy day.
- but then you've got things like table locking if using MySQL with eg myISAM engine so you'd have to probably double that to deal with the slower queries - make it 4 sockets.
I've only made FreeRADIUS cry with mySQL when hitting it with really big slow queries at which point it choked all its handles and started to get really angry but a couple of optimisations later with some indexes and a change of DB engine (InnoDB) and things were fine - to ensure future happiness a migration to postgres was undertaken (pretty painless on the FR side of things...updating other code was more ...interesting... lets say) and we then moved to buffered_sql for the heavy stuff - that thread chugs along in the background inserting/updating table values when it needs to.
FreeRADIUS and accouting DB took up too much time a while back.. a distraction that I could have done without but since the move to a better DB (I know..i'm stirring now..) and moving to 'out of band' DB updates its 'just done its job(tm)'
PS there is an upcoming webcast about FreeRADIUS and mysql clusters - if you are into MySQL and performance/reliability I'd wholely recommend getting onto that.
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (4)
-
Alan Buxey -
Alan DeKok -
Alexandre Chapellon -
Ivan Kalik