Re: Why using username instead of user id?
I couldn't get my answer!
On one of my systems, we use ids. It's somewhat complicated though: - use stored procedures, as I need to use several statements to achieve the same functionality. It's better than joins (which is very expensive in mysql cluster) - lookup user id based on username (on an additional table, obviously) - lookup other properties (e.g. radcheck, radreply, etc.) based on the id
The only extra step required is to lookup user id from another table. Then we can use it instead of username in all queries. To achieve this, we can set User-Name to that id. Maybe I'm missing something. I really want that "live" and "historical" tables implemented. I have 35000 users and I need fetching data from radacct for statistical analysis. It's a heavy table! Morteza Milani
Morteza Milani wrote:
The only extra step required is to lookup user id from another table. Then we can use it instead of username in all queries. To achieve this, we can set User-Name to that id. Maybe I'm missing something.
Not much. It requires changes to the system, but it's possible.
I really want that "live" and "historical" tables implemented. I have 35000 users and I need fetching data from radacct for statistical analysis. It's a heavy table!
Huh? 35K users is a small to medium sized system. I've seen systems with 500K to 1M users, all on one MySQL DB! The system has a lot of RAM and a few CPUs, but it's not really complicated. If doing queries on 35K users is hard, you need either to add indexes, or to add more RAM to the system. Changing the schema to use Id's rather than User-Names will make at best a few percentage difference. Adding RAM can make 10x difference. Alan DeKok.
On Mon, Jan 23, 2012 at 9:45 PM, Morteza Milani <fireboy.mrtz@gmail.com> wrote:
I couldn't get my answer!
The answer is there. Whether or not you like it is another matter.
On one of my systems, we use ids. It's somewhat complicated though: - use stored procedures, as I need to use several statements to achieve the same functionality. It's better than joins (which is very expensive in mysql cluster) - lookup user id based on username (on an additional table, obviously) - lookup other properties (e.g. radcheck, radreply, etc.) based on the id
The only extra step required is to lookup user id from another table. Then we can use it instead of username in all queries.
Correct.
To achieve this, we can set User-Name to that id.
If you mean "use that id as key on further database lookup", then yes, correct.
Maybe I'm missing something.
You seem to miss the fact that the defaults that FR use are ... well ... defaults. You don't HAVE to use it :) The changes you want are implementable without the need to touch a single line of FR's C code. You just need to adjust the sql queries (e.g. sql/mysql/dialup.conf), and implement the change in the db (e.g. using stored procedure, modify schema, etc.). Of course you can also change the code, but my point is you don't HAVE to. The defaults are simple enough for newbies to impelement and understand, and good enough for small-medium implementations. If you are an expert, or have a large implementation, or have special needs, then you can make your own changes or hire an expert to do so. And if you think some of the changes might benefit others and you're willing to share, fell free to write a how-to, edit the wiki, or submit a patch.
I really want that "live" and "historical" tables implemented.
welcome to the club :) I believe I've written several times (mostly on freeradius-users) on the general idea on how to do this. The main problem with my current implementation is that it's complex and specific to my needs, which I don't think is well-suited for general use. Creating a generic version takes time and effort, which I don't have right now. If you can help or have a better implementation, please contribute. If not, then wait until someone has the time to write it :)
I have 35000 users and I need fetching data from radacct for statistical analysis.> It's a heavy table!
statistical analysis is a different thing altogether. It'll STILL be heavy for the db, even when you split live and historical. The main benefit that we discussed earlier is that separating those two will make FR perform faster (e.g. for checking simultaneous-use during user authentication). I agree with Alan, if this is your MAIN problem, then index and more RAM might be a more suitable solution. Also faster disks (e.g. SSD, or many HDD in raid 10) if you have the budget. Or simply hire a dba to get more accurate suggestion specific to your particular implementation. -- Fajar
participants (3)
-
Alan DeKok -
Fajar A. Nugraha -
Morteza Milani