bugNSS MySQL - Bugs: bug #4192, Nonsensical join

 
 

bug #4192: Nonsensical join

Submitter:  Invalid User ID <#20567>
Submitted:  Fri 04 Jul 2003 01:30:18 PM UTC
   
 
Category:  None Severity:  3 - Normal
Item Group:  None Status:  Need Info
Privacy:  Public Assigned to:  gmorin
Open/Closed:  Closed
* Mandatory Fields

Add a New Comment Rich Markup
   

Wed 09 Jul 2003 11:38:54 AM UTC, comment #4: 

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

Invalid User ID <#20567>
Tue 08 Jul 2003 04:27:57 PM UTC, comment #3: 

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.

Guillaume Morin <gmorin>
Group administrator
Tue 08 Jul 2003 03:06:28 PM UTC, comment #2: 

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

Invalid User ID <#20567>
Mon 07 Jul 2003 01:48:12 PM UTC, comment #1: 

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.

Guillaume Morin <gmorin>
Group administrator
Fri 04 Jul 2003 01:30:18 PM UTC, 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

Invalid User ID <#20567>

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

Attach Files:
   
   
Comment:
   

No files currently attached

 

Depends on the following items: None found

Items that depend on this one: None found

 

CC list is empty

 

There are 0 votes so far. Votes easily highlight which items people would like to see resolved in priority, independently of the priority of the item set by tracker managers.

Only logged-in users can vote.

 

Follow 4 latest changes.

Date Changed by Updated Field Previous Value => Replaced by
2005-02-21 gmorin StatusInvalid Need Info
    Open/ClosedOpen Closed
2003-07-07 gmorin Assigned toNone None
2003-07-07 gmorin StatusNone None

Back to the top

Powered by Savane 3.13-caa5.
Corresponding source code