On Thu, Feb 11, 2021 at 09:52:16AM +0100, André Warnier (tomcat/perl) wrote: > Isn't this discussion about connection pools and firewalls etc getting a bit > far from the initial subject of the thread ?
Perhaps. But this has become a pretty low volume mailing list. This "thread" has moved me to spend hours looking at changing and/or better understanding the work I have done (pretty old code) and the work I am now starting. For me, I'm re-reading the manual pages for the DBI modules, etc. I've also added another mailing list to follow about DBI. And I will now have some threads to add in the near future. Threads I wouldn't have thought of. But this isn't my mailing list, so breaking these topics into new threads is just fine. Not a problem at all. 8-) Recently, something "clicked on" for me about mod_perl. Which is pretty thrilling for me. ;-} Chris > > On 09.02.2021 23:03, Mithun Bhattacharya wrote: > > I would consider mine a small setup on an internal network and I have > > used both Sybase and SQL Server. In our case the DBA's preferred us to > > remain connected rather than make too many connections - we need DB > > access in bursts - it could be quiet for more than an hour and then > > suddenly we might need hundreds of connections within few minutes (if we > > didnt cache it). Another thing was we were connecting from forked > > processes so at some point everything gets reaped including the > > connections. Our style of coding has been to connect to the DB wherever > > we actually need to fire one or more SQLs and do connect_cached in the > > actual implementation (it is a separate library since we had to place a > > wrapper to acquire credentials) > > > > On Tue, Feb 9, 2021 at 2:34 PM James Smith <j...@sanger.ac.uk > > <mailto:j...@sanger.ac.uk>> wrote: > > > > Mithun, > > > > I’m not sure on what scale you work – but these are from experience in > > sites with > > small to medium load – and we rarely see an appreciable gain in using > > cached or pooled > > connections, just the occasional heartache they cause. > > If you are working on small applications with a minimal number of > > databases on the DB > > server then you may see some performance improvement (but tbh not as > > much as you used > > to – as the servers have changed) Unfortunately I don’t in both my main > > and secondary > > roles, and I know many others who come across these limitations as well. > > > > I’m not saying don’t use persistent or cached connections – but leaving > > it to some > > hidden layers is not necessarily a good thing to do – it can have > > unforeseen side > > effects {and Apache::DBI & PHP pconnect have both shown these up} > > > > If you are working with e.g. with MySQL the overhead of the (socket) > > connection is > > very small, but having more connections open to cope with persistent > > connections > > {memory wise} often needs specifying a much large database server – or > > not being able > > to do all the nice tricks to in memory indexes and queries [to increase > > query > > performance]. Being able to chose which connections you keep open and > > which you > > open/close on a per request basis gives you the benefits of caching > > without the risks > > involved [other than the “lock table” issue].____ > > > > __ __ > > > > __ __ > > > > *From:*Mithun Bhattacharya <mit...@gmail.com <mailto:mit...@gmail.com>> > > *Sent:* 09 February 2021 18:34 > > *To:* mod_perl list <modperl@perl.apache.org > > <mailto:modperl@perl.apache.org>> > > *Subject:* Re: Moving ExecCGI to mod_perl - performance and custom > > 'modules' [EXT]____ > > > > __ __ > > > > Connection caching does work for most use cases - we have to accept > > James works in > > scenarios most developers can't fathom :) ____ > > > > __ __ > > > > If you are just firing off simple SQL's without any triggers or named > > temporary tables > > involved you should be good. The only times we recall tripping on > > cached connection is > > when two different code snippets tried to create the same temporary > > table. Another > > time the code was expecting the disconnect to complete the connection > > cleanup.____ > > > > __ __ > > > > On Tue, Feb 9, 2021 at 11:47 AM Vincent Veyron <vv.li...@wanadoo.fr > > <mailto:vv.li...@wanadoo.fr>> wrote:____ > > > > On Sun, 7 Feb 2021 20:21:34 +0000 > > James Smith <j...@sanger.ac.uk <mailto:j...@sanger.ac.uk>> wrote: > > > > Hi James, > > > > > DBI sharing doesn't really gain you much - and can actually lead > > you into a > > whole world of pain. It isn't actually worth turning it on at all. > > > > > > > Never had a problem with it myself in years of using it, but I wrap > > my queries in > > an eval { } and check $@, so that the scripts are not left hanging; > > also I have a > > postgresql db ;-). > > > > I ran some tests with ab, I do see an improvement in response speed > > : > > > > my $dbh = DBI->connect() > > Concurrency Level: 5 > > Time taken for tests: 22.198 seconds > > Complete requests: 1000 > > Failed requests: 0 > > Total transferred: 8435000 bytes > > HTML transferred: 8176000 bytes > > Requests per second: 45.05 [#/sec] (mean) > > Time per request: 110.990 [ms] (mean) > > Time per request: 22.198 [ms] (mean, across all concurrent > > requests) > > Transfer rate: 371.08 [Kbytes/sec] received > > > > my $dbh = DBI->connect_cached() > > Concurrency Level: 5 > > Time taken for tests: 15.133 seconds > > Complete requests: 1000 > > Failed requests: 0 > > Total transferred: 8435000 bytes > > HTML transferred: 8176000 bytes > > Requests per second: 66.08 [#/sec] (mean) > > Time per request: 75.664 [ms] (mean) > > Time per request: 15.133 [ms] (mean, across all concurrent > > requests) > > Transfer rate: 544.33 [Kbytes/sec] received > > > > > > -- > > > > Bien à vous, Vincent Veyron > > > > https://compta.libremen.com [compta.libremen.com] > > > > <https://urldefense.proofpoint.com/v2/url?u=https-3A__compta.libremen.com&d=DwMFaQ&c=D7ByGjS34AllFgecYw0iC6Zq7qlm8uclZFI0SqQnqBo&r=oH2yp0ge1ecj4oDX0XM7vQ&m=CnIW-j3Bw_IfohZCciiwtkoqvr6nV2hHrNYMPpEOe8E&s=uf6Qi4tnTPryVuPvOKwfZQcFOksecWyn-LYPDVj44lY&e=> > > Logiciel libre de comptabilité générale en partie double > > > > > > ____ > > > > -- The Wellcome Sanger Institute is operated by Genome Research > > Limited, a charity > > registered in England with number 1021457 and a company registered in > > England with > > number 2742969, whose registered office is 215 Euston Road, London, NW1 > > 2BE. > > >
