Re: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
As a long time Oracle DBA and perl mangler I have refrained from joining this conversation until now. Connection caching is a configuration option in DBI, not a coding requirement. For web servers it is a single line edit in startup.pl import DBICache versus import DBI if I recall incorrectly. NO CODE CHANGES REQUIRED Now caching connections will require clean code. Code that was acceptable on a short term connection causes problems on long term connections. Cursors left open, virtual connections not closed, etc are non-issues using DBI but huge problems with DBICache Applications doing heavy sql or batch work should not use DBICache. Just from an administrative perspective. Session less applications like web servers SHALL use it. And I use the legal meaning for shall I don't care what database is on the end of a DBI connection, I mangle em all, poorly YMMV Dave -- Dave Morgan Senior Consultant, 100 Alberta Limited dave.mor...@100.com 403 399 2442
Re: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
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 +
> 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 $@, 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,
>
RE: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
The advantage of the web proxy is not from securing your app - although there are things you can do on the reverse proxy to secure less secure apps It's main advantage is that it doesn't run a large software stack - and so it makes it harder for people to compromise your front end and then compromise your internal networks, and even then they have to get from that DMZ into your main infrastructure. We go a step further at work. We have a DMZ <- a web zone <- internal zone - so even if you can compromise the DMZ and the web servers you still don't have direct access to the other machines - taking servers + desktop machines - something like 30-50K cores. -Original Message- From: Clive Eisen Sent: 09 February 2021 19:23 To: Rafael Caceres Cc: James Smith ; Vincent Veyron ; modperl@perl.apache.org Subject: Re: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT] > On 9 Feb 2021, at 19:16, Rafael Caceres wrote: > > Another thing that can be done is keep the app server + DB inside your LAN > and place a reverse proxy on your DMZ, that adds some level of protection. Not really - the only protection is if all your apis or web pages are secure - the reverse proxy does not help or hinder that. — C -- 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.
RE: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
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
mailto:vv.li...@wanadoo.fr>> wrote:
On Sun, 7 Feb 2021 20:21:34 +
James Smith 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]
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.
Re: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
> On 9 Feb 2021, at 19:16, Rafael Caceres wrote: > > Another thing that can be done is keep the app server + DB inside your LAN > and place a reverse proxy on your DMZ, that adds some level of protection. Not really - the only protection is if all your apis or web pages are secure - the reverse proxy does not help or hinder that. — C
Re: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
Another thing that can be done is keep the app server + DB inside your LAN and place a reverse proxy on your DMZ, that adds some level of protection. Rafael On Feb 9, 2021, 2:08 PM -0500, Clive Eisen , wrote: On 9 Feb 2021, at 18:45, James Smith wrote: It doesn't matter what db - and whether you wrap it in eval it is a problem (postgres has a similar problem - the one with least problems is MySQL) - if you have a secure environment where your databases are in a firewalled zone it will happen to all of them... It's a nasty bit of networking - it does mean our meant to be secure enterprise level apps running against Oracle and less secure and less stable than the other apps we have (go figure!)… 20 years ago I had exactly this argument with Amex when we wanted to use it for payment on the site I then worked for. They said put a firewall between the app and db layerx I said it's a dedicated nic/vlan on both sides and the ONLY port that is open is the db - what is a firewall going to add to that. Eventually they agreed. Security people who say firewall firewall firewall will solve all your security issues (or even some of them) are useless. Most of them do it by the book - which should in all case just be the starting point. Just my 2p — C
Re: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
> On 9 Feb 2021, at 18:45, James Smith wrote: > > It doesn't matter what db - and whether you wrap it in eval it is a problem > (postgres has a similar problem - the one with least problems is MySQL) - if > you have a secure environment where your databases are in a firewalled zone > it will happen to all of them... It's a nasty bit of networking - it does > mean our meant to be secure enterprise level apps running against Oracle and > less secure and less stable than the other apps we have (go figure!)… 20 years ago I had exactly this argument with Amex when we wanted to use it for payment on the site I then worked for. They said put a firewall between the app and db layerx I said it's a dedicated nic/vlan on both sides and the ONLY port that is open is the db - what is a firewall going to add to that. Eventually they agreed. Security people who say firewall firewall firewall will solve all your security issues (or even some of them) are useless. Most of them do it by the book - which should in all case just be the starting point. Just my 2p — C
RE: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
It doesn't matter what db - and whether you wrap it in eval it is a problem
(postgres has a similar problem - the one with least problems is MySQL) - if
you have a secure environment where your databases are in a firewalled zone it
will happen to all of them... It's a nasty bit of networking - it does mean our
meant to be secure enterprise level apps running against Oracle and less secure
and less stable than the other apps we have (go figure!)...
-Original Message-
From: Vincent Veyron
Sent: 09 February 2021 17:47
To: modperl@perl.apache.org
Subject: Re: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
On Sun, 7 Feb 2021 20:21:34 +
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 $@, 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://urldefense.proofpoint.com/v2/url?u=https-3A__compta.libremen.com=DwIFAw=D7ByGjS34AllFgecYw0iC6Zq7qlm8uclZFI0SqQnqBo=oH2yp0ge1ecj4oDX0XM7vQ=u0vYr2KXDAvFiif8YsX-7Uho_gsySe2x9Z3OHcD_Br4=7Hyp0l39Edk8cAZK0idIxVxKi3OQXhkR96T0T42b2tM=
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.
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 +
> 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 $@, 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
> Logiciel libre de comptabilité générale en partie double
>
>
>
>
Re: Moving ExecCGI to mod_perl - performance and custom 'modules' [EXT]
On Sun, 7 Feb 2021 20:21:34 +
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 $@, 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
Logiciel libre de comptabilité générale en partie double
