MESSAGE
| DATE | 2021-02-09 |
| FROM | Mithun Bhattacharya
|
| SUBJECT | Re: [Hangout - NYLXS] Moving ExecCGI to mod_perl - performance and
|
From hangout-bounces-at-nylxs.com Tue Feb 9 17:05:49 2021
Return-Path:
X-Original-To: archive-at-mrbrklyn.com
Delivered-To: archive-at-mrbrklyn.com
Received: from www2.mrbrklyn.com (www2.mrbrklyn.com [96.57.23.82])
by mrbrklyn.com (Postfix) with ESMTP id 3E17A163FFD;
Tue, 9 Feb 2021 17:05:49 -0500 (EST)
X-Original-To: hangout-at-www2.mrbrklyn.com
Delivered-To: hangout-at-www2.mrbrklyn.com
Received: by mrbrklyn.com (Postfix, from userid 1000)
id 29D84163FBE; Tue, 9 Feb 2021 17:05:45 -0500 (EST)
Resent-From: Ruben Safir
Resent-Date: Tue, 9 Feb 2021 17:05:45 -0500
Resent-Message-ID: <20210209220545.GA25519-at-www2.mrbrklyn.com>
Resent-To: hangout-at-mrbrklyn.com
X-Original-To: ruben-at-mrbrklyn.com
Delivered-To: ruben-at-mrbrklyn.com
Received: from mxout1-ec2-va.apache.org (mxout1-ec2-va.apache.org
[3.227.148.255]) by mrbrklyn.com (Postfix) with ESMTP id 082A5163FB3
for ; Tue, 9 Feb 2021 17:04:07 -0500 (EST)
Received: from mail.apache.org (mailroute1-lw-us.apache.org [207.244.88.153])
by mxout1-ec2-va.apache.org (ASF Mail Server at
mxout1-ec2-va.apache.org) with SMTP id 75CA544B41
for ; Tue, 9 Feb 2021 22:04:07 +0000 (UTC)
Received: (qmail 5195 invoked by uid 500); 9 Feb 2021 22:04:07 -0000
Mailing-List: contact modperl-help-at-perl.apache.org; run by ezmlm
Precedence: bulk
Delivered-To: mailing list modperl-at-perl.apache.org
Received: (qmail 5169 invoked by uid 99); 9 Feb 2021 22:04:06 -0000
Received: from spamproc1-he-fi.apache.org (HELO spamproc1-he-fi.apache.org)
(95.217.134.168)
by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 09 Feb 2021 22:04:06 +0000
Received: from localhost (localhost [127.0.0.1])
by spamproc1-he-fi.apache.org (ASF Mail Server at spamproc1-he-fi.apache.org)
with ESMTP id EA804C0116
for ; Tue, 9 Feb 2021 22:04:05 +0000 (UTC)
X-Virus-Scanned: Debian amavisd-new at spamproc1-he-fi.apache.org
X-Spam-Flag: NO
X-Spam-Score: 0
X-Spam-Level:
X-Spam-Status: No, score=0 tagged_above=-999 required=6.31
tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1,
DKIM_VALID_EF=-0.1, HTML_MESSAGE=0.2, LH_URI_DOM_IN_PATH=0.001,
RCVD_IN_MSPIKE_H2=-0.001, SPF_PASS=-0.001, URIBL_BLOCKED=0.001]
autolearn=disabled
Authentication-Results: spamproc1-he-fi.apache.org (amavisd-new);
dkim=pass (2048-bit key) header.d=gmail.com
Received: from mx1-ec2-va.apache.org ([116.203.227.195])
by localhost (spamproc1-he-fi.apache.org [95.217.134.168]) (amavisd-new,
port 10024) with ESMTP id tD3UR-FzHgj2 for ;
Tue, 9 Feb 2021 22:04:04 +0000 (UTC)
Received-SPF: Pass (mailfrom) identity=mailfrom; client-ip=209.85.217.51;
helo=mail-vs1-f51.google.com; envelope-from=mithnb-at-gmail.com;
receiver=
Received: from mail-vs1-f51.google.com (mail-vs1-f51.google.com
[209.85.217.51])
by mx1-ec2-va.apache.org (ASF Mail Server at mx1-ec2-va.apache.org) with
ESMTPS id 22256BCC6B
for ; Tue, 9 Feb 2021 22:04:04 +0000 (UTC)
Received: by mail-vs1-f51.google.com with SMTP id y123so54805vsy.13
for ; Tue, 09 Feb 2021 14:04:04 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025;
h=mime-version:references:in-reply-to:from:date:message-id:subject:to;
bh=yksXtqPEhebYrStc7YowMmU8tmgbmAS9oJobAfAz1CY=;
b=cvMeP+6VhVSrL3MUuHCBhUz6HgL1NQt+Q4GTBcMgQ71v6ZrW1CZQ+6a9yNjiMUha1J
vvFkv9gQ0Y4zvvXvmQ4J9uGKk/rwRe9Pz4uw3otCAAc9cK2svZE2sTE0LOMYoaQ6xF91
JkdtmlCwSCN7C0GSTfhBXiwIFugqKpBdwQ5CEVl8qcyeQYNzIVYCWso6amfPOe44MwD3
yCeR3wOytuvJKVcXKh+7CGWiBCFsTJ9VmH67QwFARmPugZyfhQRwVUkbq800pMAeDpk4
SBoa7Ie+SQRTEE7DoTx196PUQw26QUx5ptnbbByKdXDeUePFxsprdywOB6ocyHwXdMAB
fIVA==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=1e100.net; s=20161025;
h=x-gm-message-state:mime-version:references:in-reply-to:from:date
:message-id:subject:to;
bh=yksXtqPEhebYrStc7YowMmU8tmgbmAS9oJobAfAz1CY=;
b=twDlIG9BzyzXAYqz9XyCE3I5YRuPHtShEqg+EfMx7+XPESW5Szn3b5SjJ7LqbImnbn
4q5PA2JkZYPoFdhnI59T+X+8AF9v0fi5QchwPuT/Lxivq2c/oKLkpMNOCzpx47EgH4mr
TtExNR2ocPKKXudtJJUsnKYzlLL1FyS52cheKWNbmEZh2j00NhRw4/KE+vFeLTXywTmA
fx1RWDLp69bxXMopmo9oUiPC7bv9Vfr7w/+j+RtRaCMdmaTvn/emVSYdqC/SMrKaml3g
tbWo/2aVu0oGtJ+azvR1Gnp+8GKtAbm4b0R9H9jwGFwyslli1v8XYzswcWZrpWSoC/0B
Oq6Q==
X-Gm-Message-State: AOAM532NT+s+jzZcFghqYB425TNv7GGV9X1PHZRp1Z03aXp175mqfVsw
dANYQpeFo4LSclpdZxkKFg/SZRYZMHSglnI/uYjjePnH
X-Google-Smtp-Source: ABdhPJxhw7y0owNHE7mnCM6ZZZe7vokn2vzwNm+qBudJb4xsQLVDI74fcIVHbRtd+xiCc6rnG2thQgfVGZB8HK4N/Zk=
X-Received: by 2002:a67:1202:: with SMTP id 2mr13529250vss.53.1612908237908;
Tue, 09 Feb 2021 14:03:57 -0800 (PST)
MIME-Version: 1.0
References:
<5DT5OQ.TMVDQL2ALCKH-at-crc.id.au>
<20210207200539.a916a6af80093eb8a56ab4dc-at-wanadoo.fr>
<20210209184659.3e799e6d7ec5901929234d9c-at-wanadoo.fr>
<3f88ee4d68594aad8d71661cb3cb79ff-at-sanger.ac.uk>
In-Reply-To: <3f88ee4d68594aad8d71661cb3cb79ff-at-sanger.ac.uk>
From: Mithun Bhattacharya
Date: Tue, 9 Feb 2021 16:03:46 -0600
Message-ID:
To: mod_perl list
Subject: Re: [Hangout - NYLXS] Moving ExecCGI to mod_perl - performance and
custom 'modules' [EXT]
X-BeenThere: hangout-at-nylxs.com
X-Mailman-Version: 2.1.30rc1
List-Id: NYLXS Tech Talk and Politics
List-Unsubscribe: ,
List-Archive:
List-Post:
List-Help:
List-Subscribe: ,
Content-Type: multipart/mixed; boundary="===============1724250208=="
Errors-To: hangout-bounces-at-nylxs.com
Sender: "Hangout"
--===============1724250208==
Content-Type: multipart/alternative; boundary="000000000000936e2605baee76e2"
--000000000000936e2605baee76e2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
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=E2=80=99m not sure on what scale you work =E2=80=93 but these are from =
experience in
> sites with small to medium load =E2=80=93 and we rarely see an appreciabl=
e 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 =E2=80=93 as the servers have changed=
)
> Unfortunately I don=E2=80=99t in both my main and secondary roles, and I =
know many
> others who come across these limitations as well.
>
> I=E2=80=99m not saying don=E2=80=99t use persistent or cached connections=
=E2=80=93 but leaving it
> to some hidden layers is not necessarily a good thing to do =E2=80=93 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 =E2=80=93 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 =E2=80=9Clock table=E2=80=9D 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 t=
o
> 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 =3D 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 =3D 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 =C3=A0 vous, Vincent Veyron
>
> https://compta.libremen.com [compta.libremen.com]
> om&d=3DDwMFaQ&c=3DD7ByGjS34AllFgecYw0iC6Zq7qlm8uclZFI0SqQnqBo&r=3DoH2yp0ge1=
ecj4oDX0XM7vQ&m=3DCnIW-j3Bw_IfohZCciiwtkoqvr6nV2hHrNYMPpEOe8E&s=3Duf6Qi4tnT=
PryVuPvOKwfZQcFOksecWyn-LYPDVj44lY&e=3D>
> Logiciel libre de comptabilit=C3=A9 g=C3=A9n=C3=A9rale en partie double
>
>
> -- The Wellcome Sanger Institute is operated by Genome Research Limited, =
a
> charity registered in England with number 1021457 and a company registere=
d
> in England with number 2742969, whose registered office is 215 Euston Roa=
d,
> London, NW1 2BE.
>
--000000000000936e2605baee76e2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
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 pref=
erred us to remain connected rather than make too many connections - we nee=
d DB access in bursts - it could be quiet for more than an hour and then su=
ddenly we might need hundreds of connections within few minutes (if we didn=
t cache it). Another thing was we were connecting from forked processes so =
at some point everything gets reaped including the connections. Our style o=
f coding has been to connect to the DB wherever we actually need to fire on=
e or more SQLs and do connect_cached in the actual=C2=A0implementation (it =
is a separate library since we had to place a wrapper to acquire=C2=A0crede=
ntials)
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">
Mithun,
I=E2=80=99m not sure on what scale you work =E2=80=93 but these are from ex=
perience in sites with small to medium load =E2=80=93 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 no=
t as much as you used to =E2=80=93 as the servers have changed) Unfortunate=
ly I don=E2=80=99t in both my main and secondary
roles, and I know many others who come across these limitations as well.r>
I=E2=80=99m not saying don=E2=80=99t use persistent or cached connections =
=E2=80=93 but leaving it to some hidden layers is not necessarily a good th=
ing to do =E2=80=93 it can have unforeseen side effects {and Apache::DBI &a=
mp; PHP pconnect have both shown these up}
If you are working with e.g. with MySQL the overhead of the (socket) connec=
tion is very small, but having more connections open to cope with persisten=
t connections {memory wise} often needs specifying a much large database se=
rver =E2=80=93 or not being able to do all
the nice tricks to in memory indexes and queries [to increase query perfor=
mance]. Being able to chose which connections you keep open and which you o=
pen/close on a per request basis gives you the benefits of caching without =
the risks involved [other than the
=E2=80=9Clock table=E2=80=9D issue]. =C2=A0 =C2=A0 =C2=A0 Connection caching does work for most use cases - we=
have to accept James works in scenarios most developers can't fathom=
=C2=A0:)=C2=A0 If you are just firing off simple SQL's without =
any triggers or named temporary tables involved you should be good. The onl=
y times we recall tripping on cached connection is when two different code =
snippets=C2=A0tried to create the same temporary
table. Another time the code=C2=A0was expecting the disconnect to complete=
the connection cleanup. =C2=A0 On Tue, Feb 9, 2021 at 11:47 AM Vincent Veyron <<=
a href=3D"mailto:vv.lists-at-wanadoo.fr" target=3D"_blank">vv.lists-at-wanadoo.fr=
> wrote: order-left:1pt solid rgb(204,204,204);padding:0cm 0cm 0cm 6pt;margin:5pt 0c=
m 5pt 4.8pt">
On Sun, 7 Feb 2021 20:2=
1:34 +0000
James Smith <js5-at-s=
anger.ac.uk> wrote:
Hi James,
> DBI sharing doesn't really gain you much - and can actually lead y=
ou 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 quer=
ies 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 =3D DBI->connect()
Concurrency Level:=C2=A0 =C2=A0 =C2=A0 5
Time taken for tests:=C2=A0 =C2=A022.198 seconds
Complete requests:=C2=A0 =C2=A0 =C2=A0 1000
Failed requests:=C2=A0 =C2=A0 =C2=A0 =C2=A0 0
Total transferred:=C2=A0 =C2=A0 =C2=A0 8435000 bytes
HTML transferred:=C2=A0 =C2=A0 =C2=A0 =C2=A08176000 bytes
Requests per second:=C2=A0 =C2=A0 45.05 [#/sec] (mean)
Time per request:=C2=A0 =C2=A0 =C2=A0 =C2=A0110.990 [ms] (mean)
Time per request:=C2=A0 =C2=A0 =C2=A0 =C2=A022.198 [ms] (mean, across all c=
oncurrent requests)
Transfer rate:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 371.08 [Kbytes/sec] receiv=
ed
my $dbh =3D DBI->connect_cached()
Concurrency Level:=C2=A0 =C2=A0 =C2=A0 5
Time taken for tests:=C2=A0 =C2=A015.133 seconds
Complete requests:=C2=A0 =C2=A0 =C2=A0 1000
Failed requests:=C2=A0 =C2=A0 =C2=A0 =C2=A0 0
Total transferred:=C2=A0 =C2=A0 =C2=A0 8435000 bytes
HTML transferred:=C2=A0 =C2=A0 =C2=A0 =C2=A08176000 bytes
Requests per second:=C2=A0 =C2=A0 66.08 [#/sec] (mean)
Time per request:=C2=A0 =C2=A0 =C2=A0 =C2=A075.664 [ms] (mean)
Time per request:=C2=A0 =C2=A0 =C2=A0 =C2=A015.133 [ms] (mean, across all c=
oncurrent requests)
Transfer rate:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 544.33 [Kbytes/sec] receiv=
ed
--
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Bien =C3=
=A0 vous, Vincent Veyron
bremen.com&d=3DDwMFaQ&c=3DD7ByGjS34AllFgecYw0iC6Zq7qlm8uclZFI0SqQnq=
Bo&r=3DoH2yp0ge1ecj4oDX0XM7vQ&m=3DCnIW-j3Bw_IfohZCciiwtkoqvr6nV2hHr=
NYMPpEOe8E&s=3Duf6Qi4tnTPryVuPvOKwfZQcFOksecWyn-LYPDVj44lY&e=3D" ta=
rget=3D"_blank">https://compta.libremen.com
[compta.libremen.com]
Logiciel libre de comptabilit=C3=A9 g=C3=A9n=C3=A9rale en partie double
--=20 The Wellcome Sanger Institute is operated by Genome Research=20 Limited, a charity registered in England with number 1021457 and a=20 company registered in England with number 2742969, whose registered=20 office is 215 Euston Road, London, NW1 2BE.=20
--000000000000936e2605baee76e2--
--===============1724250208==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
Hangout mailing list
Hangout-at-nylxs.com
http://lists.mrbrklyn.com/mailman/listinfo/hangout
--===============1724250208==--
--===============1724250208==
Content-Type: multipart/alternative; boundary="000000000000936e2605baee76e2"
--000000000000936e2605baee76e2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
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=E2=80=99m not sure on what scale you work =E2=80=93 but these are from =
experience in
> sites with small to medium load =E2=80=93 and we rarely see an appreciabl=
e 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 =E2=80=93 as the servers have changed=
)
> Unfortunately I don=E2=80=99t in both my main and secondary roles, and I =
know many
> others who come across these limitations as well.
>
> I=E2=80=99m not saying don=E2=80=99t use persistent or cached connections=
=E2=80=93 but leaving it
> to some hidden layers is not necessarily a good thing to do =E2=80=93 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 =E2=80=93 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 =E2=80=9Clock table=E2=80=9D 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 t=
o
> 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 =3D 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 =3D 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 =C3=A0 vous, Vincent Veyron
>
> https://compta.libremen.com [compta.libremen.com]
> om&d=3DDwMFaQ&c=3DD7ByGjS34AllFgecYw0iC6Zq7qlm8uclZFI0SqQnqBo&r=3DoH2yp0ge1=
ecj4oDX0XM7vQ&m=3DCnIW-j3Bw_IfohZCciiwtkoqvr6nV2hHrNYMPpEOe8E&s=3Duf6Qi4tnT=
PryVuPvOKwfZQcFOksecWyn-LYPDVj44lY&e=3D>
> Logiciel libre de comptabilit=C3=A9 g=C3=A9n=C3=A9rale en partie double
>
>
> -- The Wellcome Sanger Institute is operated by Genome Research Limited, =
a
> charity registered in England with number 1021457 and a company registere=
d
> in England with number 2742969, whose registered office is 215 Euston Roa=
d,
> London, NW1 2BE.
>
--000000000000936e2605baee76e2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
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 pref=
erred us to remain connected rather than make too many connections - we nee=
d DB access in bursts - it could be quiet for more than an hour and then su=
ddenly we might need hundreds of connections within few minutes (if we didn=
t cache it). Another thing was we were connecting from forked processes so =
at some point everything gets reaped including the connections. Our style o=
f coding has been to connect to the DB wherever we actually need to fire on=
e or more SQLs and do connect_cached in the actual=C2=A0implementation (it =
is a separate library since we had to place a wrapper to acquire=C2=A0crede=
ntials)
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">
Mithun,
I=E2=80=99m not sure on what scale you work =E2=80=93 but these are from ex=
perience in sites with small to medium load =E2=80=93 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 no=
t as much as you used to =E2=80=93 as the servers have changed) Unfortunate=
ly I don=E2=80=99t in both my main and secondary
roles, and I know many others who come across these limitations as well.r>
I=E2=80=99m not saying don=E2=80=99t use persistent or cached connections =
=E2=80=93 but leaving it to some hidden layers is not necessarily a good th=
ing to do =E2=80=93 it can have unforeseen side effects {and Apache::DBI &a=
mp; PHP pconnect have both shown these up}
If you are working with e.g. with MySQL the overhead of the (socket) connec=
tion is very small, but having more connections open to cope with persisten=
t connections {memory wise} often needs specifying a much large database se=
rver =E2=80=93 or not being able to do all
the nice tricks to in memory indexes and queries [to increase query perfor=
mance]. Being able to chose which connections you keep open and which you o=
pen/close on a per request basis gives you the benefits of caching without =
the risks involved [other than the
=E2=80=9Clock table=E2=80=9D issue]. =C2=A0 =C2=A0 =C2=A0 Connection caching does work for most use cases - we=
have to accept James works in scenarios most developers can't fathom=
=C2=A0:)=C2=A0 If you are just firing off simple SQL's without =
any triggers or named temporary tables involved you should be good. The onl=
y times we recall tripping on cached connection is when two different code =
snippets=C2=A0tried to create the same temporary
table. Another time the code=C2=A0was expecting the disconnect to complete=
the connection cleanup. =C2=A0 On Tue, Feb 9, 2021 at 11:47 AM Vincent Veyron <<=
a href=3D"mailto:vv.lists-at-wanadoo.fr" target=3D"_blank">vv.lists-at-wanadoo.fr=
> wrote: order-left:1pt solid rgb(204,204,204);padding:0cm 0cm 0cm 6pt;margin:5pt 0c=
m 5pt 4.8pt">
On Sun, 7 Feb 2021 20:2=
1:34 +0000
James Smith <js5-at-s=
anger.ac.uk> wrote:
Hi James,
> DBI sharing doesn't really gain you much - and can actually lead y=
ou 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 quer=
ies 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 =3D DBI->connect()
Concurrency Level:=C2=A0 =C2=A0 =C2=A0 5
Time taken for tests:=C2=A0 =C2=A022.198 seconds
Complete requests:=C2=A0 =C2=A0 =C2=A0 1000
Failed requests:=C2=A0 =C2=A0 =C2=A0 =C2=A0 0
Total transferred:=C2=A0 =C2=A0 =C2=A0 8435000 bytes
HTML transferred:=C2=A0 =C2=A0 =C2=A0 =C2=A08176000 bytes
Requests per second:=C2=A0 =C2=A0 45.05 [#/sec] (mean)
Time per request:=C2=A0 =C2=A0 =C2=A0 =C2=A0110.990 [ms] (mean)
Time per request:=C2=A0 =C2=A0 =C2=A0 =C2=A022.198 [ms] (mean, across all c=
oncurrent requests)
Transfer rate:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 371.08 [Kbytes/sec] receiv=
ed
my $dbh =3D DBI->connect_cached()
Concurrency Level:=C2=A0 =C2=A0 =C2=A0 5
Time taken for tests:=C2=A0 =C2=A015.133 seconds
Complete requests:=C2=A0 =C2=A0 =C2=A0 1000
Failed requests:=C2=A0 =C2=A0 =C2=A0 =C2=A0 0
Total transferred:=C2=A0 =C2=A0 =C2=A0 8435000 bytes
HTML transferred:=C2=A0 =C2=A0 =C2=A0 =C2=A08176000 bytes
Requests per second:=C2=A0 =C2=A0 66.08 [#/sec] (mean)
Time per request:=C2=A0 =C2=A0 =C2=A0 =C2=A075.664 [ms] (mean)
Time per request:=C2=A0 =C2=A0 =C2=A0 =C2=A015.133 [ms] (mean, across all c=
oncurrent requests)
Transfer rate:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 544.33 [Kbytes/sec] receiv=
ed
--
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Bien =C3=
=A0 vous, Vincent Veyron
bremen.com&d=3DDwMFaQ&c=3DD7ByGjS34AllFgecYw0iC6Zq7qlm8uclZFI0SqQnq=
Bo&r=3DoH2yp0ge1ecj4oDX0XM7vQ&m=3DCnIW-j3Bw_IfohZCciiwtkoqvr6nV2hHr=
NYMPpEOe8E&s=3Duf6Qi4tnTPryVuPvOKwfZQcFOksecWyn-LYPDVj44lY&e=3D" ta=
rget=3D"_blank">https://compta.libremen.com
[compta.libremen.com]
Logiciel libre de comptabilit=C3=A9 g=C3=A9n=C3=A9rale en partie double
--=20 The Wellcome Sanger Institute is operated by Genome Research=20 Limited, a charity registered in England with number 1021457 and a=20 company registered in England with number 2742969, whose registered=20 office is 215 Euston Road, London, NW1 2BE.=20
--000000000000936e2605baee76e2--
--===============1724250208==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
Hangout mailing list
Hangout-at-nylxs.com
http://lists.mrbrklyn.com/mailman/listinfo/hangout
--===============1724250208==--
|
|
