[Nss-mysql-devel] [bug #4192] Nonsensical join

[nobody]

=================== BUG #4192: LATEST MODIFICATIONS ==================
http://savannah.nongnu.org/bugs/?func=detailbug&bug_id=4192&group_id=443

Changes by: Marcus Don <address@hidden>
Date: Wed 07/09/2003 at 11:38 (GMT)

------------------ Additional Follow-up Comments ----------------------------
Hi

I will try the CVS version next week, but in the meantime I have the perfect 
solution. There is a patch for qpopper available from 
http://asteroid-b612.org/software/ that enables you to configure qpopper to 
validate directly against the mysql database. I have tried it and it works fine 
(several thousand times faster!). Obviously, this circumvents nss_mysql 
altogether, but it gives complete control over the sql query that is used for 
user authentication.

Thanks for you help

Marcus



=================== BUG #4192: FULL BUG SNAPSHOT ===================


Submitted by: marcusjhdon             Project: NSS MySQL                    
Submitted on: Fri 07/04/2003 at 13:30
Category:  None                       Severity:  5 - Major                  
Bug Group:  None                      Resolution:  Invalid                  
Assigned to:  gmorin                  Status:  Open                         

Summary:  Nonsensical join

Original Submission:  Hi

I have noticed that when we get an ipop login, the following query is used by 
nss_mysql:

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 1 = 1 and 
groups.status = 'A'

The result of this is to return every single user, of which we have around 
30,000! We have a load balancers cluster of 4 machines, but our pop server is 
now so busy that if one machine goes offline, the others struggle to cope 
unless we stop ipop.

Is this likely to be a bug with nss_mysql or ipop? Also, are you still working 
on this project, since I notice there has been little activity recently?

Thanks in advance.

Marcus

Follow-up Comments
*******************

-------------------------------------------------------
Date: Wed 07/09/2003 at 11:38       By: marcusjhdon
Hi

I will try the CVS version next week, but in the meantime I have the perfect 
solution. There is a patch for qpopper available from 
http://asteroid-b612.org/software/ that enables you to configure qpopper to 
validate directly against the mysql database. I have tried it and it works fine 
(several thousand times faster!). Obviously, this circumvents nss_mysql 
altogether, but it gives complete control over the sql query that is used for 
user authentication.

Thanks for you help

Marcus

-------------------------------------------------------
Date: Tue 07/08/2003 at 16:27       By: gmorin
Marcus,

Can you try the CVS version and tell me if it is better ? There is actually 
some room for improvement. The single query nss-mysql appears to be slow. 
Splitting it in two has been reported to be much faster. But I've not had the 
time to change that. I am not even sure this is the way to go since newer MySQL 
versions might handle this one more appropriately.

Guillaume.

-------------------------------------------------------
Date: Tue 07/08/2003 at 15:06       By: marcusjhdon
Hi

Thanks for your speedy reply. We are using nscd, with quite a large cache size 
for passwords - but since we have so many users this isn't enough by itself. 
The tables are also correctly indexed, but the query still takes 3-8 seconds 
depending on load.

I can see now that the problem is definately with ipop, so I tried installing 
qpopper instead. But, qpopper also uses the same library call to validate login 
details, so there was no improvement.

So the only options left are either to find another pop daemon that does things 
differently, or to change the source code of ipop or qpopper. If we do look at 
the source code, can you tell me what library call(s) it should use to just 
return details for the current user?

Regards

Marcus

PS We are currently using nss_mysql 0.41 because we found a problem with mysql 
connections not closing after upgrading to nss_mysql 0.43, which I believe we 
had some discussion about at the time. Has this issue now been resolved with 
the version in the CVS repository? If so, I will upgrade - though this is 
unlikely to help much with the current problem because it isn't nss_mysql that 
is at fault.

Regards

Marcus

-------------------------------------------------------
Date: Mon 07/07/2003 at 13:48       By: gmorin
Marcus,

It is an ipop issue. NSS MySQL implement the NSS API so I guess ipop calls 
getgrent which returns all results. I am still maintaining the project. I have 
moved to a new job 
(and a new country) so I have been very busy recently. 

What I suggest is to try the CVS version which should be faster and try to 
install nscd. Some people have reported problems with it and some said it works 
flawless. You might want to give it a try.

You can also use the MySQL "EXPLAIN" command to see if the query you pasted 
makes a good use of indexes.

Guillaume.


CC list is empty


No files currently attached


For detailed info, follow this link:
http://savannah.nongnu.org/bugs/?func=detailbug&bug_id=4192&group_id=443

_______________________________________________
  Message sent via/by Savannah
  http://savannah.nongnu.org/