I have given nss-mysql a try on RH7.0 and it seems to work ok with a couple exceptions.
For some reason (and I realize this is not a nss-mysql problem but rather an NSS problem) when I use all methods (passwd, group, shadow) even if I get a hit in the local passwd, nss-mysql still insists on looking up the user. I tried using [SUCCESS=return] in my /etc/nsswitch.conf to no avail. My /etc/nsswitch.conf is currently set as:
passwd: files mysql
group: files mysql
shadow: files mysql
I would assume that if I get a user/group match in /etc/passwd and/or /etc/group that it would stop there. Is this assumption correct?
Got any ideas?
Also, what is the purpose of the groups.first_gid field? I notice regardless of where it is set my MySQL server gets hit with group queries from 1 to the actual group id of the user. Couldn't nss-mysql just issue one query for the user to get the group(s)?
I have a couple other suggestions for improvment also...
Hitting MySQL every time with a new real_connect I know takes some overhead. Is there anyway to use persistent connections in nss-mysql? I don't know enough C to implement it myself.
Also, since MySQL does in fact support the db.table syntax why not eliminate the db altogether or test for presence of it in the nss-mysql*.conf files? For example if there is a users/groups/shadow .database in the conf files then fire a mysql_selectdb, otherwise assume that the db/table is in the db.table syntax on the user/groups/shadow .table lines and test for presence of a db.table or not.
A mysql_selectdb is a bunch of overhead I have found out from testing, using db.table syntax eliminates that extra overhead.
One more thing (although I don't know enough about NSS operation..I should keep my mouth shut)...how about combining the sql queries into a single join or something, when using passwd, group, shadow? It would seem that everything can be returned from an single optimized query rather than hitting the server multiple times.
I wish I knew a little more C...I would love to assist on this project.
-Dave
California Internet Connection
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sorry for the delay , I've been very busy.
>I would assume that if I get a user/group match in >/etc/passwd and/or /etc/group that it would stop there. Is >this assumption correct?
According to the glibc info pages, this is correct.
>what is the purpose of the groups.first_gid field?
Yes, it sucks. I've written the group service to fit with the SourceForge database that has no gid field.
>Couldn't nss-mysql just issue one query for the user to get >the group(s)?
I guess this is the initgroups function. This has to check every group for every member.
>Hitting MySQL every time with a new real_connect I know >takes some overhead. Is there anyway to use persistent
>connections in nss-mysql ?
Persistent connections could be a way to go. But we need to find a way to close the connection when the program exits.
>A mysql_selectdb is a bunch of overhead I have found out >from testing, using db.table syntax eliminates that extra
>overhead.
Hmm, does not sound logical to me. I will check the MySQL FAQ.
>How about combining the sql queries into a single join or >something, when using passwd, group, shadow? It would >seem that everything can be returned from an single >optimized query rather than hitting the server multiple times.
I do not see what you mean.
Regards,
Guillaume.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I don't know how persistent connections work with C because I have only used them with PHP but essentially you don't close the connection but rather just free the memory (mysql_free_result I think) then when you do a connect a second time the client looks for a lingering connection to the same host with the same user/pass and uses that instead.
I have tested performance with using and not using persistent and using persistent saves quite a bit of overhead.
Combine persistent connections with using a db.table syntax and there will be quite a savings. I had to benchmark all these individual items when I wrote a DB abstraction layer recently where performance was a #1 concern.
The db.table syntax I am referring to is to simply reference the target table and prefix it with the db it is in. If you watch the mysql logs of a server while you issue queries you will find:
(With non persistent and standard syntax)
2009058 Connect mysql@hostname on
2009058 Init DB system
2009058 Query select * from passwd
2009058 Quit
(With persistent and db.table syntax)
2009058 Statistics
2009058 Query select * from system.passwd
Know what I mean?
Then combine the above with an optimized join and I think nss_mysql might be ready to handle an ls -l in a directory with 5000 entries...;)
What I was referring to for a join is to return everything needed for a getpwnam (or whatever it is called) from a single query.
Maybe I am misunderstanding the way NSS operates, but I would think that when nss wants info for a user it asks the methods set in nsswitch.conf and expects back all the info in a passwd struct?
well if the shadow is used and needed to be returned couldn't nss_mysql issue something like:
select sp.pw_name, ss.pw_passwd, sp.pw_uid, sp.pw_gid, etc..etc from system.passwd sp left join system.shadow on sp.pw_name = ss.pw_name
Something like that would return everything needed to fill a struct in a single query.
Also when a group name is needed couldn't a simple select * from system.groups where gid = 1234 || grpname = 'grpnam'
Maybe Im just rambling...
-Dave
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
>essentially you don't close the connection but rather just
>free the memory (mysql_free_result I think) then when you
>do a connect a second time the client looks for a lingering
>connection to the same host with the same>user/pass and
>uses that instead.
Yes, I see what you mean. The problem is that the persistent connection is something implemented by the PHP team and it is rather difficult to add it for nss-mysql. You've to play with System V IPC (semaphores and shared memory)
I do agree that persistent connection would speed up the whole thing. But I have still some doubts about the db.table syntax.
>What I was referring to for a join is to return everything
>needed for a getpwnam(or whatever it is called) from a single
>query.
This is exactly what is done. Maybe you want some caching, indeed that could be a way to go. But every single calls do only one query.
You have a lot of good idea. I would love if you could get a good C book and start to learn :)
Regards,
Guillaume.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Oops...I didn't know they (PHP) had to use shared memory for persistent connections. That would be fun to implement huh?...
What doubts do you have about the db.table syntax? The db.table syntax is fully implemented in MySQL. I use that method almost exclusively now after finding the performance savings. Using db.table completely eliminates a select_db call.
I see in the MySQL C API that the mysql_real_connect does in fact take a db as an argument whereas in the mysql_connect it does not. Calling mysql_real_connect with a db arg I would assume that the mysql client makes the db the current db after a successful call. If this is the case and the passwd/shadow/group tables are within that db then there is no need at all to call a mysql_select_db at all. (I know passwd/shadow/group are not necessarily going to be in the same db). At any rate, whether or not you even have a db selected after a connect you can do a db.table query (ANY type of query) to get to ANY table on the MySQL server (abiding by permissions of course).
I did use the db.table syntax when using nss_mysql and it worked fine. Actually what I did to speed it up is just comment out your mysql_select_db after the connect then in the nss_mysql.conf files I just specified db.table. I was just thinking that it would be nice to have that configurable rather than having to comment it out. For instance:
if there is a db value in the *.conf files AND the table values in the *.conf files are NOT in the syntax db.table then issue the select_db, otherwise skip the select_db and use db.table syntax.
I can implement these changes easily and I will do so..I just have to figure out how to use diff to give you the modifications...;)
With regard to the group lookup issue, is it absolutley necessary to have a first_gid field? I don't understand the functionality of it or the need to send multiple queries for all the groups. Is this a NSS issue or nss_mysql thing?
I will get started implementing some of these changes now (as in within the next 20 minutes) because I have an immediate need for a FAST, stable passwd/shadow db system opposed to the NSS files system. For my use though I will probably stick with /etc/group lookups and just use passwd/shadow in MySQL.
I am curious as to whether or not useradd, userdel, passwd and other passwd db modification applications actually use NSS or not? If this is the case implementing MySQL updates/inserts could not be too difficult I would think....maybe so.
Have you heard of any NSS or other implementation which allows for a username in the format user@domain.com? The limitation I see so far is merely a length limitation (too many chars). For my application I have a multitude of domains on a box and not being able to use the same username is a pain in the butt. I am contemplating using this method to uniquely identify the users.
-Dave
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
>Oops...I didn't know they (PHP) had to use shared memory
>for persistent connections.
>That would be fun to implement huh?...
I did not say that PHP uses shared memory. But for NSS MySQL we would need to share a limited number of persistent connections between all processes. Therefore the most efficient way is to use Sys V IPC.
And yes, it would be fun, but time-consuming :)
>What doubts do you have about the db.table syntax? The
>db.table syntax is fully implemented in MySQL. I use that
>method almost exclusively now after finding the performance
>savings. Using db.table completely eliminates a select_db
>call.
I am doubtful about the actual performance gain with that method.
>Calling mysql_real_connect with a db arg I would assume
>that the mysql client makes the db the current db after a
>successful call
That is right. I've removed the call to mysql_init_db in the CVS version. I think it will faster.
>With regard to the group lookup issue, is it absolutely
>necessary to have a first_gid field? I don't understand
>the functionality of it or the need to send multiple queries
>for all the groups. Is this a NSS issue or nss_mysql thing?
It is a complete nss mysql field. Since I did not have a GID column, I do GID = group_id + first_gid. That is the point of that.
>I am curious as to whether or not useradd, userdel, passwd
>and other passwd db modification applications actually
> use NSS or not?
This kind of actions do not use NSS.
>Have you heard of any NSS or other implementation which
>allows for a username in the format user@domain.com?
no, sorry.
Regards,
Guillaume
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
I have given nss-mysql a try on RH7.0 and it seems to work ok with a couple exceptions.
For some reason (and I realize this is not a nss-mysql problem but rather an NSS problem) when I use all methods (passwd, group, shadow) even if I get a hit in the local passwd, nss-mysql still insists on looking up the user. I tried using [SUCCESS=return] in my /etc/nsswitch.conf to no avail. My /etc/nsswitch.conf is currently set as:
passwd: files mysql
group: files mysql
shadow: files mysql
I would assume that if I get a user/group match in /etc/passwd and/or /etc/group that it would stop there. Is this assumption correct?
Got any ideas?
Also, what is the purpose of the groups.first_gid field? I notice regardless of where it is set my MySQL server gets hit with group queries from 1 to the actual group id of the user. Couldn't nss-mysql just issue one query for the user to get the group(s)?
I have a couple other suggestions for improvment also...
Hitting MySQL every time with a new real_connect I know takes some overhead. Is there anyway to use persistent connections in nss-mysql? I don't know enough C to implement it myself.
Also, since MySQL does in fact support the db.table syntax why not eliminate the db altogether or test for presence of it in the nss-mysql*.conf files? For example if there is a users/groups/shadow .database in the conf files then fire a mysql_selectdb, otherwise assume that the db/table is in the db.table syntax on the user/groups/shadow .table lines and test for presence of a db.table or not.
A mysql_selectdb is a bunch of overhead I have found out from testing, using db.table syntax eliminates that extra overhead.
One more thing (although I don't know enough about NSS operation..I should keep my mouth shut)...how about combining the sql queries into a single join or something, when using passwd, group, shadow? It would seem that everything can be returned from an single optimized query rather than hitting the server multiple times.
I wish I knew a little more C...I would love to assist on this project.
-Dave
California Internet Connection
Hi,
Sorry for the delay , I've been very busy.
>I would assume that if I get a user/group match in >/etc/passwd and/or /etc/group that it would stop there. Is >this assumption correct?
According to the glibc info pages, this is correct.
>what is the purpose of the groups.first_gid field?
Yes, it sucks. I've written the group service to fit with the SourceForge database that has no gid field.
>Couldn't nss-mysql just issue one query for the user to get >the group(s)?
I guess this is the initgroups function. This has to check every group for every member.
>Hitting MySQL every time with a new real_connect I know >takes some overhead. Is there anyway to use persistent
>connections in nss-mysql ?
Persistent connections could be a way to go. But we need to find a way to close the connection when the program exits.
>A mysql_selectdb is a bunch of overhead I have found out >from testing, using db.table syntax eliminates that extra
>overhead.
Hmm, does not sound logical to me. I will check the MySQL FAQ.
>How about combining the sql queries into a single join or >something, when using passwd, group, shadow? It would >seem that everything can be returned from an single >optimized query rather than hitting the server multiple times.
I do not see what you mean.
Regards,
Guillaume.
Hi,
I don't know how persistent connections work with C because I have only used them with PHP but essentially you don't close the connection but rather just free the memory (mysql_free_result I think) then when you do a connect a second time the client looks for a lingering connection to the same host with the same user/pass and uses that instead.
I have tested performance with using and not using persistent and using persistent saves quite a bit of overhead.
Combine persistent connections with using a db.table syntax and there will be quite a savings. I had to benchmark all these individual items when I wrote a DB abstraction layer recently where performance was a #1 concern.
The db.table syntax I am referring to is to simply reference the target table and prefix it with the db it is in. If you watch the mysql logs of a server while you issue queries you will find:
(With non persistent and standard syntax)
2009058 Connect mysql@hostname on
2009058 Init DB system
2009058 Query select * from passwd
2009058 Quit
(With persistent and db.table syntax)
2009058 Statistics
2009058 Query select * from system.passwd
Know what I mean?
Then combine the above with an optimized join and I think nss_mysql might be ready to handle an ls -l in a directory with 5000 entries...;)
What I was referring to for a join is to return everything needed for a getpwnam (or whatever it is called) from a single query.
Maybe I am misunderstanding the way NSS operates, but I would think that when nss wants info for a user it asks the methods set in nsswitch.conf and expects back all the info in a passwd struct?
well if the shadow is used and needed to be returned couldn't nss_mysql issue something like:
select sp.pw_name, ss.pw_passwd, sp.pw_uid, sp.pw_gid, etc..etc from system.passwd sp left join system.shadow on sp.pw_name = ss.pw_name
Something like that would return everything needed to fill a struct in a single query.
Also when a group name is needed couldn't a simple select * from system.groups where gid = 1234 || grpname = 'grpnam'
Maybe Im just rambling...
-Dave
>essentially you don't close the connection but rather just
>free the memory (mysql_free_result I think) then when you
>do a connect a second time the client looks for a lingering
>connection to the same host with the same>user/pass and
>uses that instead.
Yes, I see what you mean. The problem is that the persistent connection is something implemented by the PHP team and it is rather difficult to add it for nss-mysql. You've to play with System V IPC (semaphores and shared memory)
I do agree that persistent connection would speed up the whole thing. But I have still some doubts about the db.table syntax.
>What I was referring to for a join is to return everything
>needed for a getpwnam(or whatever it is called) from a single
>query.
This is exactly what is done. Maybe you want some caching, indeed that could be a way to go. But every single calls do only one query.
You have a lot of good idea. I would love if you could get a good C book and start to learn :)
Regards,
Guillaume.
Oops...I didn't know they (PHP) had to use shared memory for persistent connections. That would be fun to implement huh?...
What doubts do you have about the db.table syntax? The db.table syntax is fully implemented in MySQL. I use that method almost exclusively now after finding the performance savings. Using db.table completely eliminates a select_db call.
I see in the MySQL C API that the mysql_real_connect does in fact take a db as an argument whereas in the mysql_connect it does not. Calling mysql_real_connect with a db arg I would assume that the mysql client makes the db the current db after a successful call. If this is the case and the passwd/shadow/group tables are within that db then there is no need at all to call a mysql_select_db at all. (I know passwd/shadow/group are not necessarily going to be in the same db). At any rate, whether or not you even have a db selected after a connect you can do a db.table query (ANY type of query) to get to ANY table on the MySQL server (abiding by permissions of course).
I did use the db.table syntax when using nss_mysql and it worked fine. Actually what I did to speed it up is just comment out your mysql_select_db after the connect then in the nss_mysql.conf files I just specified db.table. I was just thinking that it would be nice to have that configurable rather than having to comment it out. For instance:
if there is a db value in the *.conf files AND the table values in the *.conf files are NOT in the syntax db.table then issue the select_db, otherwise skip the select_db and use db.table syntax.
I can implement these changes easily and I will do so..I just have to figure out how to use diff to give you the modifications...;)
With regard to the group lookup issue, is it absolutley necessary to have a first_gid field? I don't understand the functionality of it or the need to send multiple queries for all the groups. Is this a NSS issue or nss_mysql thing?
I will get started implementing some of these changes now (as in within the next 20 minutes) because I have an immediate need for a FAST, stable passwd/shadow db system opposed to the NSS files system. For my use though I will probably stick with /etc/group lookups and just use passwd/shadow in MySQL.
I am curious as to whether or not useradd, userdel, passwd and other passwd db modification applications actually use NSS or not? If this is the case implementing MySQL updates/inserts could not be too difficult I would think....maybe so.
Have you heard of any NSS or other implementation which allows for a username in the format user@domain.com? The limitation I see so far is merely a length limitation (too many chars). For my application I have a multitude of domains on a box and not being able to use the same username is a pain in the butt. I am contemplating using this method to uniquely identify the users.
-Dave
>Oops...I didn't know they (PHP) had to use shared memory
>for persistent connections.
>That would be fun to implement huh?...
I did not say that PHP uses shared memory. But for NSS MySQL we would need to share a limited number of persistent connections between all processes. Therefore the most efficient way is to use Sys V IPC.
And yes, it would be fun, but time-consuming :)
>What doubts do you have about the db.table syntax? The
>db.table syntax is fully implemented in MySQL. I use that
>method almost exclusively now after finding the performance
>savings. Using db.table completely eliminates a select_db
>call.
I am doubtful about the actual performance gain with that method.
>Calling mysql_real_connect with a db arg I would assume
>that the mysql client makes the db the current db after a
>successful call
That is right. I've removed the call to mysql_init_db in the CVS version. I think it will faster.
>With regard to the group lookup issue, is it absolutely
>necessary to have a first_gid field? I don't understand
>the functionality of it or the need to send multiple queries
>for all the groups. Is this a NSS issue or nss_mysql thing?
It is a complete nss mysql field. Since I did not have a GID column, I do GID = group_id + first_gid. That is the point of that.
>I am curious as to whether or not useradd, userdel, passwd
>and other passwd db modification applications actually
> use NSS or not?
This kind of actions do not use NSS.
>Have you heard of any NSS or other implementation which
>allows for a username in the format user@domain.com?
no, sorry.
Regards,
Guillaume