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>
Sent: 09 February 2021 18:34
To: mod_perl list <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.
