[Nss-mysql-devel] [ 101504 ] id command VERY slow

[nobody]

Support Request #101504, was updated on 2002-Nov-07 22:26
You can respond by visiting: 
http://savannah.nongnu.org/support/?func=detailsupport&support_id=101504&group_id=443

Category: None
Status: Open
Priority: 5
Summary: id command VERY slow

By: gmorin
Date: 2002-Nov-07 23:08
Logged In: YES 
user_id=230
Browser: Mozilla/5.0 (X11; U; Linux ppc; en-US; rv:1.1) Gecko/20020915 
Debian/1.1-1

Hi Marcus,

I understand your point. The problem is that id makes
several hundreds nss API calls in your case. That is why you
see so many queries. nscd implements some caching for nss
information. Unfortunately, it seems to be buggy and
triggers some weird bugs. 

But one minutes looks very long even for your number of
users. Are you sure you have not compiled nss-mysql in debug
mode which would slow it down significantly ? Since you saw
the nss-mysql queries, you should consider using the
"explain" command of MySQL to see if the query uses an index
which would speed things up.

If it's still too slow, try to install nscd and see if that
helps. 

HTH.

Guillaume.

----------------------------------------------------------------------

By: marcus
Date: 2002-Nov-07 22:26
Logged In: NO 
Browser: Mozilla/4.0 (compatible; MSIE 5.15; Mac_PowerPC)

Hi

I am running nss-mysql 0.43 on a redhat linux 7.2 
machine and the 'id' command is incredibly slow. 
By turning on mysql logging I have found that it is 
doing loads of unnecessary group queries.

For example, if I type 'id server' (server is a valid 
username), the following query is used...

select users.username,users.userId + 
2000,NULL,users.realName,users.shell,users.ho
meDir,users.groupId + 2000 from users where 
users.username='server' and users.userId + 2000 
is not null and users.status = 'A' order by 
users.userId + 2000

..which return the user details very quickly. This is 
then followed by...

select users.username,users.userId + 
2000,NULL,users.realName,users.shell,users.ho
meDir,users.groupId + 2000 from users where 
users.userId + 2000=7432 and users.status = 'A' 
order by users.userId + 2000

..which returns the same information. Then, the 
following query is made over 200 times...

select groups.groupName,groups.groupId + 
2000,groups.groupPassword,users.username 
from groups LEFT JOIN usersGroups on 
groups.groupId=usersGroups.groupId LEFT JOIN 
users on usersGroups.userId=users.userId and 
users.status = 'A' where groups.groupId + 2000 = 
2001 and groups.status = 'A'

...which returns every single user in the group.

Also, I built the system to use just one group for all 
16,000+ users because of the earlier issue with 
having large numbers of groups. So, with so many 
rows returned in each of the above queries, it takes 
1min 13secs to complete!

Ideally, could it use a query something like this?...

select users.*, groups.* from users, usersGroups, 
groups where users.username='server' and 
usersGroups.userId=users.userId and 
groups.groupId=usersGroups.groupId

This works instantly.

Thanks in advance.

Marcus


----------------------------------------------------------------------
You can respond by visiting: 
http://savannah.nongnu.org/support/?func=detailsupport&support_id=101504&group_id=443