MESSAGE
| DATE | 2021-02-11 |
| FROM | From: =?UTF-8?Q?Andr=c3=a9_Warnier_=28tomcat/perl=29?=
|
| SUBJECT | Re: [Hangout - NYLXS] Moving ExecCGI to mod_perl - performance and
|
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 > 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 >
> *Sent:* 09 February 2021 18:34
> *To:* mod_perl list >
> *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 > > wrote:____
>
> On Sun, 7 Feb 2021 20:21:34 +0000
> James Smith > 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 $-at-, 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]
>
> 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.
>
_______________________________________________
Hangout mailing list
Hangout-at-nylxs.com
http://lists.mrbrklyn.com/mailman/listinfo/hangout
|
|
