Confusion on Apache::DBI
All, Sorry if this question sounds stupid.. but I am new to mod_perl and Apache::DBI.. I have successfully installed Apache Server 1.3.12 and mod_perl 1.24 and Apache::DBI 0.87 on Solaris 2.7. And I have tested all successfully... But confusion arised when I created a startup.pl file and tried to initiate DB connection in startup.pl file using 'connect_on_init' from Apache::DBI As I understood, if you have 'connect_on_init' in startup file, you have preloaded DB handle when first request comes in. And you don't need to open new db connection... But if I check the error_log file, I still have entry saying something like following: 10803 Apache::DBI need ping: yes 10803 Apache::DBI new connect to 'DatabaseNameUsernamePasswordAutoCommit=1PrintError=1' 10803 Apache::DBI disconnect (overloaded) And I will get this for first 4-5 request but after that each time I am getting 10803 Apache::DBI need ping: yes 10803 Apache::DBI already connected to 'DatabaseNameUsernamePasswordAutoCommit=1PrintError=1' 10803 Apache::DBI disconnect (overloaded) Why is that??? Shouldn't I supposed to get 'already connected to' statement right from the first request And how many open connection can I have at the same time?? As per my understood, for Apache::DBI connection pooling is based per server process. So, I guess as many server process you have, you can have db connection handle open at the same time.. AM I RIGHT HERE? Please clear my doubts on this... And thanks for your help ... Niral
RE: Confusion on Apache::DBI
-Original Message- From: Niral Trivedi [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 18, 2000 1:11 PM To: '[EMAIL PROTECTED]' Subject: Confusion on Apache::DBI All, Sorry if this question sounds stupid.. but I am new to mod_perl and Apache::DBI.. I have successfully installed Apache Server 1.3.12 and mod_perl 1.24 and Apache::DBI 0.87 on Solaris 2.7. And I have tested all successfully... But confusion arised when I created a startup.pl file and tried to initiate DB connection in startup.pl file using 'connect_on_init' from Apache::DBI As I understood, if you have 'connect_on_init' in startup file, you have preloaded DB handle when first request comes in. And you don't need to open new db connection... But if I check the error_log file, I still have entry saying something like following: Apache::DBI caches by connect-string, so make sure that your DBI-connect call is identical to your Apache::DBI-connect_on_init call, including all the extra parameters. 10803 Apache::DBI need ping: yes 10803 Apache::DBI new connect to 'DatabaseNameUsernamePasswordAutoCommit=1PrintError=1' 10803 Apache::DBI disconnect (overloaded) And I will get this for first 4-5 request but after that each time I am getting are you sure that these 4-5 requests are for the same child? pay close attention to the pid... 10803 Apache::DBI need ping: yes 10803 Apache::DBI already connected to 'DatabaseNameUsernamePasswordAutoCommit=1PrintError=1' 10803 Apache::DBI disconnect (overloaded) Why is that??? Shouldn't I supposed to get 'already connected to' statement right from the first request yes, so long as you called connect_on_init properly. but don't forget, when a child is born, you'll get the 'new connect' output. connect doesn't happen at startup, per-se. that is, the parent process doesn't cache the handle, the child does... And how many open connection can I have at the same time?? As per my understood, for Apache::DBI connection pooling is based per server process. So, I guess as many server process you have, you can have db connection handle open at the same time.. AM I RIGHT HERE? yes, you will have as many database handles as you have httpd children... HTH --Geoff Please clear my doubts on this... And thanks for your help ... Niral
Re: Confusion on Apache::DBI
Thanks Geoff, You were right... I was using "DBI:mysql:DBNAME::localhost" as connect string in startup file whereas "DBI:mysql:DBNAME" in my mod_perl script.. I have changed that and it worked... Now, another question.. As we agreed, we can have as many db handle available as server processes are running.. Now, can we set number of db connection per server process in script or in startup script or any other way??? Because what I see is in this way... Let's say a child process can handle 10 request and then dies.. now it has one db connection available.. and it is processing one request which uses available db handle.. now what happen if another request comes in and ask for same db handle??? does that request has to wait for that or what??? If yes, then is there any way we can set number of connection per process?? I mean each child process can have 5 db connection open at the same time.. and it can grow upto 10.. So, if all 5 db handle are in use, and if 6th request comes in then process will open another connetion and it can do like this upto max of 10 connection.. Is it possible??? has anybody done this kind of things before Thanks again for your help.. Niral Geoffrey Young wrote:
RE: Confusion on Apache::DBI
-Original Message- From: Niral Trivedi [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 18, 2000 3:15 PM To: Geoffrey Young Cc: '[EMAIL PROTECTED]' Subject: Re: Confusion on Apache::DBI Thanks Geoff, You were right... I was using "DBI:mysql:DBNAME::localhost" as connect string in startup file whereas "DBI:mysql:DBNAME" in my mod_perl script.. I have changed that and it worked... Now, another question.. As we agreed, we can have as many db handle available as server processes are running.. Now, can we set number of db connection per server process in script or in startup script or any other way??? Because what I see is in this way... Let's say a child process can handle 10 request and then dies.. now it has one db connection available.. I believe that once the child dies, $dbh goes out of scope and DBI cleans up the connection. I could be wrong, though... and it is processing one request which uses available db handle.. now what happen if another request comes in and ask for same db handle??? well, another request won't come in and ask for the _same_ handle that died with the other child - that's the nature of Apache::DBI, one handle per child. It's not a pool of shared connections, really... Apache will either serve the new request to an existing child (which would get a cached connection) or initialize a new child (which would subsequently open a new connection and cache it)... does that request has to wait for that or what??? If yes, then is there any way we can set number of connection per process?? I mean each child process can have 5 db connection open at the same time.. and it can grow upto 10.. So, if all 5 db handle are in use, and if 6th request comes in then process will open another connetion and it can do like this upto max of 10 connection.. Is it possible??? has anybody done this kind of things before folks have talked about this type of stuff on the dbi-users list - it comes up every so often... I don't know that anyone has implemented a solution, though you might try looking at DBI::ProxyServer - I think it does something like this (though I haven't looked at it myself) --Geoff Thanks again for your help.. Niral Geoffrey Young wrote:
RE: Confusion on Apache::DBI
-Original Message- From: Niral Trivedi [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 18, 2000 3:57 PM To: Geoffrey Young Cc: '[EMAIL PROTECTED]' Subject: Re: Confusion on Apache::DBI Geoff, I know, once child dies, db handle goes out of scope and DBI cleans up the connection.. What I was asking is, we have directive called 'MaxRequestPerChild' for Apache server configuration which basically says how many request one child can process before it dies. So, if we have in 'httpd.conf' file 'MaxRequestPerChild 1000', that means, one child process will serv 1000 request and then it will die. Now, with Apache::DBI, we'll have one DBI handle per child process during the server startup. Now, let's say one child has started its processing and hasn't served any request yet. Now, first request comes in and it will look for DB handle, which is available and start processing it. Now, second request comes in for same child process and request for DBI handle, which is still serving first request. how would the child be available to serve another request but not have completed its DBI work? are you forking off a new process or something? So, what happened at this time to second request?? Does it have to wait for DBI handle to be free?? That's my question.. I think, second request has to wait.. i.e. it will be the same as normal CGI.. only difference is, we'll save overhead of opening a DB connection each time request comes in.. Correct me if I am wrong.. And I'll look in DBI::Proxy .. Thanks again for your help Geoff.. Niral Geoffrey Young wrote: -Original Message- From: Niral Trivedi [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 18, 2000 3:15 PM To: Geoffrey Young Cc: '[EMAIL PROTECTED]' Subject: Re: Confusion on Apache::DBI Thanks Geoff, You were right... I was using "DBI:mysql:DBNAME::localhost" as connect string in startup file whereas "DBI:mysql:DBNAME" in my mod_perl script.. I have changed that and it worked... Now, another question.. As we agreed, we can have as many db handle available as server processes are running.. Now, can we set number of db connection per server process in script or in startup script or any other way??? Because what I see is in this way... Let's say a child process can handle 10 request and then dies.. now it has one db connection available.. I believe that once the child dies, $dbh goes out of scope and DBI cleans up the connection. I could be wrong, though... and it is processing one request which uses available db handle.. now what happen if another request comes in and ask for same db handle??? well, another request won't come in and ask for the _same_ handle that died with the other child - that's the nature of Apache::DBI, one handle per child. It's not a pool of shared connections, really... Apache will either serve the new request to an existing child (which would get a cached connection) or initialize a new child (which would subsequently open a new connection and cache it)... does that request has to wait for that or what??? If yes, then is there any way we can set number of connection per process?? I mean each child process can have 5 db connection open at the same time.. and it can grow upto 10.. So, if all 5 db handle are in use, and if 6th request comes in then process will open another connetion and it can do like this upto max of 10 connection.. Is it possible??? has anybody done this kind of things before folks have talked about this type of stuff on the dbi-users list - it comes up every so often... I don't know that anyone has implemented a solution, though you might try looking at DBI::ProxyServer - I think it does something like this (though I haven't looked at it myself) --Geoff Thanks again for your help.. Niral Geoffrey Young wrote:
Re: Confusion on Apache::DBI
On Thu, 18 May 2000, Niral Trivedi wrote: Now, with Apache::DBI, we'll have one DBI handle per child process during the server startup. Now, let's say one child has started its processing and hasn't served any request yet. Now, first request comes in and it will look for DB handle, which is available and start processing it. Now, second request comes in for same child process and request for DBI handle, which is still serving first request. Niral, you're not understanding Apache's multi-process daemon approach. No child process will ever try to serve more than request at once. These are not multi-threaded processes. - Perrin
Re: Confusion on Apache::DBI
I understood now... Thank you all for your responses.. Niral Perrin Harkins wrote: On Thu, 18 May 2000, Niral Trivedi wrote: Now, with Apache::DBI, we'll have one DBI handle per child process during the server startup. Now, let's say one child has started its processing and hasn't served any request yet. Now, first request comes in and it will look for DB handle, which is available and start processing it. Now, second request comes in for same child process and request for DBI handle, which is still serving first request. Niral, you're not understanding Apache's multi-process daemon approach. No child process will ever try to serve more than request at once. These are not multi-threaded processes. - Perrin