Re: Apache::DBI or What ?
Hi! Thanks a lot for this. I am not sure now if I will be able to use it. I am afraid that if I do I will suddenly become its main supporter :) I also still don't see how a connection can be reconnected at such a high level. Even when I looked into the mySQL C api I didn't see anything that would help, which ment that the real stuff was barried deeper still. But the caching of connection handles makes sense, and that might still be worth while. Between this and mod_accel I have a lot to mess with. Thanks, Eric At 01:52 PM 3/30/02 -0500, Hank Leininger wrote: On 2002-03-27, Eric Frazier [EMAIL PROTECTED] wrote: If you have any idea where to find this code I would be thankful. I can only find this.. [snip] At 04:47 PM 3/26/02 -0500, John D Groenveld wrote: Jeff Horn posted this a couple years ago either here or @ dbi-users # Purpose: This version of Apache::DBI maintains a SINGLE persistent # connection to each database. When the user changes on Ask and ye shall receive ;) http://marc.theaimsgroup.com/?l=apache-modperlm=96614841600181w=2 Or just grab the last attachment, which is the replacement DBI.pm: http://marc.theaimsgroup.com/?l=apache-modperlm=96614841600181q=p6 -- Hank Leininger [EMAIL PROTECTED] http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Learn about the net, not the .Net!
Re: Apache::DBI or What ?
Eric Frazier wrote: I also still don't see how a connection can be reconnected at such a high level. It can't, unless your database specifically supports the command reauthenticate. Oracle does, which is what he wrote this for, but I don't think it's a standard part of SQL syntax so it will probably not work with MySQL. Of course MySQL connects really quickly, so you can probably get perfectly good performance without using Apache::DBI at all. - Perrin
Re: Apache::DBI or What ?
On 2002-03-27, Eric Frazier [EMAIL PROTECTED] wrote: If you have any idea where to find this code I would be thankful. I can only find this.. [snip] At 04:47 PM 3/26/02 -0500, John D Groenveld wrote: Jeff Horn posted this a couple years ago either here or @ dbi-users # Purpose: This version of Apache::DBI maintains a SINGLE persistent # connection to each database. When the user changes on Ask and ye shall receive ;) http://marc.theaimsgroup.com/?l=apache-modperlm=96614841600181w=2 Or just grab the last attachment, which is the replacement DBI.pm: http://marc.theaimsgroup.com/?l=apache-modperlm=96614841600181q=p6 -- Hank Leininger [EMAIL PROTECTED]
Re: Apache::DBI or What ?
There are databases that allow you to change the current user without reconnecting. In fact someone posted a module to do this a while back, but I can't remember which database it was for. Seems like it was Sybase or Informix. Jeff Horn posted this a couple years ago either here or @ dbi-users # Hacked Up Version of Apache::DBI # # Hacking by: Jeff Horn ([EMAIL PROTECTED]) # # Purpose: This version of Apache::DBI maintains a SINGLE persistent # connection to each database. When the user changes on # subsequent connect calls, the DBI-func( ., ., 'reauthtenticate') # is called to quickly authenticate the new user on the existing # connection. I've used it for a few projects, I haven't really follow how changes over the last year to Edmund Mergl's Apache::DBI or Tim Bunce's DBI may have affected it. I recall one problem being when using different attributes to connect methods. Have fun. John [EMAIL PROTECTED]
Re: Apache::DBI or What ?
Hi, If you have any idea where to find this code I would be thankful. I can only find this.. http://aspn.activestate.com/ASPN/Mail/Message/perl-DBI/298774 Thanks, Eric At 04:47 PM 3/26/02 -0500, John D Groenveld wrote: There are databases that allow you to change the current user without reconnecting. In fact someone posted a module to do this a while back, but I can't remember which database it was for. Seems like it was Sybase or Informix. Jeff Horn posted this a couple years ago either here or @ dbi-users # Hacked Up Version of Apache::DBI # # Hacking by: Jeff Horn ([EMAIL PROTECTED]) # # Purpose: This version of Apache::DBI maintains a SINGLE persistent # connection to each database. When the user changes on # subsequent connect calls, the DBI-func( ., ., 'reauthtenticate') # is called to quickly authenticate the new user on the existing # connection. I've used it for a few projects, I haven't really follow how changes over the last year to Edmund Mergl's Apache::DBI or Tim Bunce's DBI may have affected it. I recall one problem being when using different attributes to connect methods. Have fun. John [EMAIL PROTECTED] http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Learn about the net, not the .Net!
Re: Apache::DBI or What ?
On Sun, 24 Mar 2002, Andrew Ho wrote: What would be ideal is if the database would allow you to change the user on the current connection. I know PostgreSQL will allow this using the command line interface psql tool (just do \connect database user), but I'm not sure if you can do this using DBI. Does anyone know if any datbases support this sort of thing? This occurred to me in the case of Oracle (one of my co-workers was facing a very similar problem in the preliminary stages of one of his designs), and I actually had asked our DBAs about this (since the Oracle SQL*Plus also allows you to change users). As I suspected (from the similar connect terminology), our DBAs confirmed that Oracle just does a disconnect and reconnect under the hood. I would bet the psql client does the same thing. First, I'll suggest that there are hopefully other areas you can look at optimizing that will get you a bigger bang for your time - in my test environment (old hardware), it takes 7.4 ms per disconnect/reconnect/rebind and 4.8 ms per rebind. Admittedly, I'm dealing with LDAP instead of SQL, and I've no idea how they compare. If the TCP connection were retained, this could still be a significant win. *Any* reduction in the connection overhead is an improvement. If there are a million connects per day, and this saves a milli-second per connect (believable to me, as at least three packets don't need to be sent - syn, syn ack, and fin. My TCP's a bit fuzzy, but I think there's a couple more, and there's also the mod_perl disconnect/reconnect overhead), that's over 15 minutes of response time and about 560,000,000 bits of network bandwidth (assuming the DB is not on the same machine) saved. Admittedly, at 100Mb/s, that's only 6 seconds. It may, in some cases, still be necessary to move access control from the DB into ones application, so one can maintain a single connection which never rebinds, but I think it's better to utilize the security in the DB instead of coding ones own - more eyes have looked over it. We're talking about a fairly small unit of time; it may very well be better to throw money if you are near your performance limit. Ed
Re: Apache::DBI or What ?
Hi, It might well be that in my particular case, I don't have anything to worry about the connection time per each user most likely won't kill me or even cause problems at first. But I am trying to build a system, and I don't want to skip any reasonable efficences I can build in from the start. In my situation the user will only be doing inserts from the mod_perl side, so I am thinking I can make use of something like Apache::File or some kind of tmp file where I will store the tab delimited data for the user insert, and on the cleanup handler phase does a COPY FROM file which is a Postgress thing to the user's table. I need for that data to be available to that same user by JDBC pretty much immediatly. But does a copy from a file like this work better than a persistent connection but one that is doing insert after insert which is not nearly as efficent as the bulk copy? As I understand it the COPY from after a droping indexes would be the fastest way to insert data into a table for postgress, period. I guess I will just have to test it out and see. The other thing I am wondering about, is that even if I have a bunch of users, why couldn't I just make my persistent connection user have rights to all of the other user tables? Well, I guess I could do that, but I am expecting one of you will tell me why that is a bad idea :) Something about it seem too easy after all of this. I was tending to want to use the DBs security also for the same reasons, so maybe that is what bothers me about the above. Thanks, this discussion has helped me out tremendously, just to know others have the same problem even. Eric First, I'll suggest that there are hopefully other areas you can look at optimizing that will get you a bigger bang for your time - in my test environment (old hardware), it takes 7.4 ms per disconnect/reconnect/rebind and 4.8 ms per rebind. Admittedly, I'm dealing with LDAP instead of SQL, and I've no idea how they compare. If the TCP connection were retained, this could still be a significant win. *Any* reduction in the connection overhead is an improvement. If there are a million connects per day, and this saves a milli-second per connect (believable to me, as at least three packets don't need to be sent - syn, syn ack, and fin. My TCP's a bit fuzzy, but I think there's a couple more, and there's also the mod_perl disconnect/reconnect overhead), that's over 15 minutes of response time and about 560,000,000 bits of network bandwidth (assuming the DB is not on the same machine) saved. Admittedly, at 100Mb/s, that's only 6 seconds. It may, in some cases, still be necessary to move access control from the DB into ones application, so one can maintain a single connection which never rebinds, but I think it's better to utilize the security in the DB instead of coding ones own - more eyes have looked over it. We're talking about a fairly small unit of time; it may very well be better to throw money if you are near your performance limit. Ed http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone)
Re: Apache::DBI or What ?
Ed Grimm wrote: First, I'll suggest that there are hopefully other areas you can look at optimizing that will get you a bigger bang for your time - in my test environment (old hardware), it takes 7.4 ms per disconnect/reconnect/rebind and 4.8 ms per rebind. Admittedly, I'm dealing with LDAP instead of SQL, and I've no idea how they compare. Connecting to Oracle can take 30 seconds on a busy system. It doesn't usually take that long, but it often takes a second or two. It is a very important optimization to hold the connection. Postgres may not be as bad, but it's common for SQL systems to have slow connection times. There are databases that allow you to change the current user without reconnecting. In fact someone posted a module to do this a while back, but I can't remember which database it was for. Seems like it was Sybase or Informix. - Perrin
Re: Apache::DBI or What ?
On Fri, 2002-03-22 at 11:42, Andrew Ho wrote: Hello, EFI will have many different users, users as in database users. So am I EFjust screwed and won't be able to keep connections open? Do you mean users as in actual RDBMS level users? In other words, when you say database users you mean different username/passwords used from, say, a command-line SQL client? If your answer is yes then indeed, this is exactly the situation the Guide is talking about--you're screwed and probably shouldn't keep connections open. You'll have to incur a connect on each HTTP request. What would be ideal is if the database would allow you to change the user on the current connection. I know PostgreSQL will allow this using the command line interface psql tool (just do \connect database user), but I'm not sure if you can do this using DBI. If this was possible, then you could always connect to DBI using the same username/password so Apache::DBI gives you a cached connection, and then once you have the connection, you can change to the user you want. Does anyone know if any datbases support this sort of thing? Cees EFI am using Postgress, I am wondering how big DBs deal with this sort of EFthing. I am also wondering what the actual overhead is in starting the EFconnection and if there is anything that I could to to limit that EFwithout validating a specific user. In large DB projects where you expect high levels of concurrency I haven't heard of anybody using RDBMS-level users for authentication purposes. Humbly, Andrew -- Andrew Ho http://www.tellme.com/ [EMAIL PROTECTED] Engineer [EMAIL PROTECTED] Voice 650-930-9062 Tellme Networks, Inc. 1-800-555-TELLFax 650-930-9101 --
Re: Apache::DBI or What ?
Hello, CHWhat would be ideal is if the database would allow you to change the CHuser on the current connection. I know PostgreSQL will allow this using CHthe command line interface psql tool (just do \connect database CHuser), but I'm not sure if you can do this using DBI. CH CHDoes anyone know if any datbases support this sort of thing? This occurred to me in the case of Oracle (one of my co-workers was facing a very similar problem in the preliminary stages of one of his designs), and I actually had asked our DBAs about this (since the Oracle SQL*Plus also allows you to change users). As I suspected (from the similar connect terminology), our DBAs confirmed that Oracle just does a disconnect and reconnect under the hood. I would bet the psql client does the same thing. I don't have a PostgreSQL installation handy at the moment, but I seem to recall it has a statement logger similar to MySQL's... if you have it handy, you could try the \connect from psql and see if it results in a disconnect and reconnect. Humbly, Andrew -- Andrew Ho http://www.tellme.com/ [EMAIL PROTECTED] Engineer [EMAIL PROTECTED] Voice 650-930-9062 Tellme Networks, Inc. 1-800-555-TELLFax 650-930-9101 --
Re: Apache::DBI or What ?
We encountered just this situation when we started to move from a win32 application connecting to an RDBMS to a web based app. On the win32 app, the DB authenticated each user with a loginid/pw. Since some users still use the win32 app, we can't just abandon the DB authentication, so here's what we did: Since the web users generaly login to the site once a day and then work for awhile, (we keep the login associated to a session cookie in the DB), we run the login script as a CGI, test the loginid/passwd in a connect, store the session info, then issue a redirect. All of the other pages use Apache::DBI with a web-user DB login. This allows us take advantage of the persistent connections for most of the requests. One trick here, if you're using the DB to enforce business rules based on the login, then you'll have to encorporate those rules into your mod_perl programs -- effectively giving the web-user the power to do whatever any of the users might need to. Fortunately, we were able to do this fairly easily. I'm open to opinions on this approach. Eric Frazier wrote: Hi, I was all happy and rolling along when I read this in the docs. With this limitation in mind, there are scenarios, where the usage of Apache::DBI is depreciated. Think about a heavy loaded Web-site where every user connects to the database with a unique userid. Every server would create many database handles each of which spawning a new backend process. In a short time this would kill the web server. I will have many different users, users as in database users. So am I just screwed and won't be able to keep connections open? Or maybe the idea would be to go ahead and let that happen, but timeout the connection in 5 mintues or so? That way I wouldn't have open connectoins from user bob from 5 hours ago still siting around. Or am I totaly not getting it at all? I am using Postgress, I am wondering how big DBs deal with this sort of thing. I am also wondering what the actual overhead is in starting the connection and if there is anything that I could to to limit that without validating a specific user. Last of all, I might not be posting this to the right place, but I hope so. It seems to me there is a grey area when it comes to Apache modules when you are using them with mod_perl. Or else I just don't know enough yet to see there is not a grey area :) Thanks, Eric http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) -- Kevin Berggren 760-480-1828 System Maker, Inc 3913 Sierra Linda Dr. Escondido, CA 92025
Apache::DBI or What ?
Hi, I was all happy and rolling along when I read this in the docs. With this limitation in mind, there are scenarios, where the usage of Apache::DBI is depreciated. Think about a heavy loaded Web-site where every user connects to the database with a unique userid. Every server would create many database handles each of which spawning a new backend process. In a short time this would kill the web server. I will have many different users, users as in database users. So am I just screwed and won't be able to keep connections open? Or maybe the idea would be to go ahead and let that happen, but timeout the connection in 5 mintues or so? That way I wouldn't have open connectoins from user bob from 5 hours ago still siting around. Or am I totaly not getting it at all? I am using Postgress, I am wondering how big DBs deal with this sort of thing. I am also wondering what the actual overhead is in starting the connection and if there is anything that I could to to limit that without validating a specific user. Last of all, I might not be posting this to the right place, but I hope so. It seems to me there is a grey area when it comes to Apache modules when you are using them with mod_perl. Or else I just don't know enough yet to see there is not a grey area :) Thanks, Eric http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone)
Re: Apache::DBI or What ?
Hello, EFI will have many different users, users as in database users. So am I EFjust screwed and won't be able to keep connections open? Do you mean users as in actual RDBMS level users? In other words, when you say database users you mean different username/passwords used from, say, a command-line SQL client? If your answer is yes then indeed, this is exactly the situation the Guide is talking about--you're screwed and probably shouldn't keep connections open. You'll have to incur a connect on each HTTP request. EFI am using Postgress, I am wondering how big DBs deal with this sort of EFthing. I am also wondering what the actual overhead is in starting the EFconnection and if there is anything that I could to to limit that EFwithout validating a specific user. In large DB projects where you expect high levels of concurrency I haven't heard of anybody using RDBMS-level users for authentication purposes. Humbly, Andrew -- Andrew Ho http://www.tellme.com/ [EMAIL PROTECTED] Engineer [EMAIL PROTECTED] Voice 650-930-9062 Tellme Networks, Inc. 1-800-555-TELLFax 650-930-9101 --
