Isn't this discussion about connection pools and firewalls etc getting a bit far from the
initial subject of the thread ?
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.
