On Mon, Jan 23, 2012 at 6:54 PM, Alan Buxey <A.L.M.Buxey@lboro.ac.uk> wrote:

Hi,

...

In freeradius sql modules, queries are based on username instead of user id. Is it anything related to performance or it went in this way without any decision?

Using username has two problems:

* updating a username, or deleting one needs querying at least three tables.

* Comparing strings needs more time than comparing integers when fetching data.

umm, because in most cases, users have typed in usernames - if you tell the users what their userid is, then sure, they could use that instead.... but thats highly inflexible

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 It works, and is more "ideal" (especially when you have a super-large number of users), but definitely NOT something I'd recommend to be a generic implementation due to the complexity. -- Fajar

On Mon, Jan 23, 2012 at 7:23 PM, Alan DeKok <aland@deployingradius.com> wrote:

 One deployment I did mandated user IDs for "cleanliness" of the SQL schema.  I added a User-Name table, and used its Id as a foreign key in other tables.

 It had the nice property that if you deleted the user, all of their configuration went away.  Including their historical accounting data.

 It caused problems for billing, but the SQL team thought it was a great idea.

... and that's why business requirements should always come first :D

...

It works, and is more "ideal" (especially when you have a super-large number of users), but definitely NOT something I'd recommend to be a generic implementation due to the complexity.

 It doesn't save much.  The main issue is that *most* users have minimal user-specific information.  So using an Id doesn't get you much, because it's not really used.  And for accounting, you're doing a row insert anyways.  So it doesn't make much difference if column 1 is an Id or a User-Name.

Well, to be fair, an index on a varchar(64) (the default on username table) would always use 64 bytes regardles of the actual bytes used for username. Changing it to int (userid) would reduce it to 4 bytes, so you'd save 60 bytes on the index and also tens of bytes on the data (depending on how long your average username is) at the expense of additional complexity and/or join. Additional savings can also be achieved by normalizing attribute column. The savings however will only be apparent if you also normalize radacct, since it's usually MUCH larger than rad(group)check/reply. If you're space-limited (e.g. using mysql cluster), it can mean a lot.

From db point of view only, normalizing is a "better" approach. Real-life implementation might have other priorities so normalizing is not always the best choice.

-- Fajar

On Mon, Jan 23, 2012 at 8:18 PM, Alan DeKok <aland@deployingradius.com> wrote:

Fajar A. Nugraha wrote:

...

From db point of view only, normalizing is a "better" approach. Real-life implementation might have other priorities so normalizing is not always the best choice.

 What would be better would be to have a "live" table of online users, and a "historical" table of old user activity.  The "live" table would be small, so updates would be fast.  The "historical" table would only get appended to, so it could hold a lot of data.

I kinda promised a patch for something like that, didn't I :P ?

 Maybe for 3.0.

Hopefuly I can start working on it again after 2.2.0 is released. DHCP is more "fun" for now :) My basic idea for the implementation is based on one of my implementations, but kinda generalized. Something like: - create a module instance that a user can choose, but does not replace the existing sql module example (e.g. "mysql5" instance of sql module) - use stored procedure, since it's the cleanest way to move data around from "live" to "historical" (without any need for external crontab). Which is also one of the reason for choosing "mysql5" instance name, since stored procedure is only supported from mysql5 onwards. - have a radzap-like function to help user force-move data from live to historical (e.g for lost acct-stop case) - (optional) normalization of username and attribute. This would require importing dictionaries to a table. - if it's tested-enough, then maybe start porting it for other db It's still a rough sketch, so if anybody have a better working implementation, or a better concept (e.g. something that can work for all db with as minimal change to existing configuration as possible), or have some spare time to start implementing it, raise your hand :) -- Fajar

On 01/23/2012 12:45 PM, Fajar A. Nugraha wrote:

Well, to be fair, an index on a varchar(64) (the default on username table) would always use 64 bytes regardles of the actual bytes used

This is an implementation-specific statement. Not everyone uses MySQL.