How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

I'm expanding, and adding extra web servers to handle my load. My Apache/mod_perl environment maintains a new connection for each process, and these processes seem to chew up a lot of server resources.

What is the normal acceptable way to maintain large numbers of simultaneous connections?

I have enabled "shared server" support in my database, but I suspect it's not being utilized, since I only get a few hundred connections before hitting the "too many connections" error.

Am I supposed to be passing a switch in with my initical "connection" requests to request a shared (instead of dedicated) connection maybe ?

Thanks all
- Chris.

RE: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

Chris Drake wrote:
> I'm expanding, and adding extra web servers to handle my load. My
> Apache/mod_perl environment maintains a new connection for each
> process, and these processes seem to chew up a lot of server
> resources. =20
>=20
> What is the normal acceptable way to maintain large numbers of
> simultaneous connections?=20
>=20
> I have enabled "shared server" support in my database, but I suspect
> it's not being utilized, since I only get a few hundred connections
> before hitting the "too many connections" error. =20
>=20
> Am I supposed to be passing a switch in with my initical "connection"
> requests to request a shared (instead of dedicated) connection maybe
> ?

You'll want to read the Oracle Net Services Administrator's Guide.
http://www.lc.leidenuniv.nl/awcourse/oracle/network.920/a965 80/mts.htm#4
53189

From a higher-level architectural viewpoint, you could use some load
balancing. Look into SQLRelay. It provides out-of-process connection
pooling that can work for mod_perl servers.
http://sqlrelay.sourceforge.net/

Regards,
Philip

Re[2]: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

Hi Philip & Robert,

Thanks for those excellent references & help offers. Do you (or
anyone) know whether or not I should even be *using* a shared server,
and have you any idea about how many dedicated connections is "too
many" on a dual-3.8ghz Xeon Linux PC with 8gigs or RAM running
Oracle 10g?

The reason I ask is because this guy says "do not use shared servers"
http://www.dba-oracle.com/t_mts_multithreaded_servers_shared .htm
.... irritatingly, during install - Oracle gives no hints about what
numbers to put into "processes" and "sessions", so I've no idea if I
should be putting 200, 2000, 20000, or even if I should be changing
these at all...

Kind Regards,
Chris Drake


Friday, November 17, 2006, 2:39:29 AM, Garrett, Philip wrote:

GPMC> Chris Drake wrote:
>> I'm expanding, and adding extra web servers to handle my load. My
>> Apache/mod_perl environment maintains a new connection for each
>> process, and these processes seem to chew up a lot of server
>> resources.
>>
>> What is the normal acceptable way to maintain large numbers of
>> simultaneous connections?
>>
>> I have enabled "shared server" support in my database, but I suspect
>> it's not being utilized, since I only get a few hundred connections
>> before hitting the "too many connections" error.
>>
>> Am I supposed to be passing a switch in with my initical "connection"
>> requests to request a shared (instead of dedicated) connection maybe
>> ?

GPMC> You'll want to read the Oracle Net Services Administrator's Guide.
GPMC> http://www.lc.leidenuniv.nl/awcourse/oracle/network.920/a965 80/mts.htm#4
GPMC> 53189

>>From a higher-level architectural viewpoint, you could use some load
GPMC> balancing. Look into SQLRelay. It provides out-of-process connection
GPMC> pooling that can work for mod_perl servers.
GPMC> http://sqlrelay.sourceforge.net/

GPMC> Regards,
GPMC> Philip



Friday, November 17, 2006, 2:30:08 AM, Robert Hicks wrote:

RH> Chris Drake wrote:
>> Hi All,
>>
>> Is there a way to specify that a connection should use the Oracle
>> "shared server" feature? I'm only getting a max of a hundred or so
>> connections, each kicking off it's own oracle process to handle it:
>> unless I'm mistaken, a new process means it's *not* a shared
>> connection?
>>
>> Kind Regards,
>> Chris Drake
>>

RH> If you don't get your answer here the dbi.perl ng would be the place to
RH> go for an answer.

RH> If you can't get there let me know and I can post for you and post back
RH> here the answer(s).

RH> Robert


RH> ------------------------------------------------------------ ---------
RH> Web Archive:
RH> http://www.mail-archive.com/cgiapp@lists.erlbaum.net/
RH> http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2
RH> To unsubscribe, e-mail: cgiapp-unsubscribe@lists.erlbaum.net
RH> For additional commands, e-mail: cgiapp-help@lists.erlbaum.net

Re: How should I connect via DBD::Oracle to efficiently obtain 2000+simultaneous connections?

Chris Drake wrote:
> Hi Philip & Robert,
>
> Thanks for those excellent references & help offers. Do you (or
> anyone) know whether or not I should even be *using* a shared server,
> and have you any idea about how many dedicated connections is "too
> many" on a dual-3.8ghz Xeon Linux PC with 8gigs or RAM running
> Oracle 10g?
>
> The reason I ask is because this guy says "do not use shared servers"
> http://www.dba-oracle.com/t_mts_multithreaded_servers_shared .htm
> ... irritatingly, during install - Oracle gives no hints about what
> numbers to put into "processes" and "sessions", so I've no idea if I
> should be putting 200, 2000, 20000, or even if I should be changing
> these at all...
>
> Kind Regards,
> Chris Drake

If a guy from Oracle says "Unless you have a real reason to use MTS --
don't." then you need to decide if you *really* need MTS, I guess.

I have never used an MTS.

Robert

Re: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

Doing some digging around, I discover that each Oracle connection consumes
4megs of RAM when "doing nothing".

that would add up to 8gigs for 2000 connections... and since I've only got
8gigs in total, this is about 4 times more than I can spare.

It would seem that I will definitely need some kind of connection pooling -
does anyone disagree ?

# pmap -d 13040 | tail -1
mapped: 810872K writeable/private: 3392K shared: 722944K

RE: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

It sounds to me like you have a good case for using some type of
pooling.

Here are a few options off the top of my head:
1) Pooling via SQLRelay
2) Pooling via MTS
3) Use fewer mod_perl processes by putting a lightweight
reverse proxy in front of them. This doesn't pool
connections, just reduces the number of connections required.
See Stas Bekman's overview at http://tinyurl.com/y5k8ka
(perl.apache.org) if you haven't already.

You might also want to consider aggressive caching (memcached perhaps?)
to take some load off of your database server.

Regards,
Philip

-----Original Message-----
From: Chris [mailto:dispo41@geek.net.au]=20
Sent: Friday, November 17, 2006 9:02 PM
To: dbi-users@perl.org
Subject: Re: How should I connect via DBD::Oracle to efficiently obtain
2000+ simultaneous connections?

Doing some digging around, I discover that each Oracle connection
consumes=20
4megs of RAM when "doing nothing".

that would add up to 8gigs for 2000 connections... and since I've only
got=20
8gigs in total, this is about 4 times more than I can spare.

It would seem that I will definitely need some kind of connection
pooling -=20
does anyone disagree ?

# pmap -d 13040 | tail -1
mapped: 810872K writeable/private: 3392K shared: 722944K

Re: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

On Sat, 18 Nov 2006 13:02:26 +1100, Chris wrote:

Hi Chris

> Doing some digging around, I discover that each Oracle connection
> consumes 4megs of RAM when "doing nothing".

:-(.

> It would seem that I will definitely need some kind of connection
> pooling - does anyone disagree ?

Lateral thinking would suggest a smarter database server :-).

--
Cheers
Ron Savage, ron@savage.net.au on 19/11/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: How should I connect via DBD::Oracle to efficiently obtain 2000+simultaneous connections?

--------------080300060707090006070405
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Chris Drake schrieb:
> I'm expanding, and adding extra web servers to handle my load. My Apache/mod_perl environment maintains a new connection for each process, and these processes seem to chew up a lot of server resources.
>
> What is the normal acceptable way to maintain large numbers of simultaneous connections?
>
> I have enabled "shared server" support in my database, but I suspect it's not being utilized, since I only get a few hundred connections before hitting the "too many connections" error.
>
> Am I supposed to be passing a switch in with my initical "connection" requests to request a shared (instead of dedicated) connection maybe ?
>
> Thanks all
> - Chris.
>
>

Actually, this has nothing to with Perl, at least my answer :-}

Using shared server means additional CPU overhead per call. If your
clients are "lazy", meaning they produce a lot of idle time, it may
help. If the more sluggish response of the DB connection doues not
disturb, use it (Orace 9iR2 or newer).

To find out, if your sessions are shared: V$session is your friend - the
column SERVER is NULL on shared connections.

To force shared connections you should force them in tnsnames.ora by
setting (SERVER=SHARED).

HTH
Johannes Gritsch


--------------080300060707090006070405--

Re: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

I will second the memcache suggestion. (his other solutes age good as
well).

It is quick and easy to use with a good perl interface that will take
quite the load of the DB especially if you have alot of static data.

A very quick and easy solution.

here is the link to memcache

http://www.danga.com/memcached/


""Garrett, Philip (MAN-Corporate)"" wrote in
message news:D9C13100F14E4C4795A1E83B125B40350232B7BD@MSCEXCHS02.man .co...
It sounds to me like you have a good case for using some type of
pooling.

Here are a few options off the top of my head:
1) Pooling via SQLRelay
2) Pooling via MTS
3) Use fewer mod_perl processes by putting a lightweight
reverse proxy in front of them. This doesn't pool
connections, just reduces the number of connections required.
See Stas Bekman's overview at http://tinyurl.com/y5k8ka
(perl.apache.org) if you haven't already.

You might also want to consider aggressive caching (memcached perhaps?)
to take some load off of your database server.

Regards,
Philip

-----Original Message-----
From: Chris [mailto:dispo41@geek.net.au]
Sent: Friday, November 17, 2006 9:02 PM
To: dbi-users@perl.org
Subject: Re: How should I connect via DBD::Oracle to efficiently obtain
2000+ simultaneous connections?

Doing some digging around, I discover that each Oracle connection
consumes
4megs of RAM when "doing nothing".

that would add up to 8gigs for 2000 connections... and since I've only
got
8gigs in total, this is about 4 times more than I can spare.

It would seem that I will definitely need some kind of connection
pooling -
does anyone disagree ?

# pmap -d 13040 | tail -1
mapped: 810872K writeable/private: 3392K shared: 722944K

Re: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

--rz+pwK2yUstbofK6
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2006-11-19 18:43:15 +1100, Ron Savage wrote:
> On Sat, 18 Nov 2006 13:02:26 +1100, Chris wrote:
> > Doing some digging around, I discover that each Oracle connection
> > consumes 4megs of RAM when "doing nothing".
>=20
> :-(.

Is that with or without MTS?


> > It would seem that I will definitely need some kind of connection
> > pooling - does anyone disagree ?
>=20
> Lateral thinking would suggest a smarter database server :-).

MTS *is* designed to allow many concurrent connections to the database
with (relatively) little RAM usage. The arguments against MTS I've read
in the articles referenced in this thread were all along the lines of
"modern database servers have more than enough RAM, so MTS is obsolete".=20
(Probably not only because of increasing RAM sizes but also because
people are migrating from forms-based applications to web-based
applications, which can support many per DB connection).

I'm wondering about the design of the web application, though: 2000+
apache processes all pounding on the same database does sound a bit
strange. I suspect that there would be some optimization potential in
the web server configuration (I haven't seriously played with mod_perl2
yet, but I vaguely remember seeing parameters which might be helpful for
this. Reverse proxy has already been mentioned. Fastcgi might also be
a possible solution.)

hp



--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--rz+pwK2yUstbofK6
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iQDQAwUBRWLQxFLjemazOuKpAQKdgwXTB1cY42grecfXBID4rpmnIBx3YJf9 IuBx
H1KtXHLq93QD7t7ib4as/xganVpQUeGEnksUIb4/2GbbMgOaht732P312F5W lt4r
OpUE38ummYdOWjNU1F9TMuVR0ebVxKdn2phksqJy2NPjyZRaykrbY4ojvwXQ hhz0
M197WB244nO2P/rde6zXuGvAFiPuSQhIrTdEGeNCjWD5fIFLJcxZE2ytpjdd QLnc
j77eMJ8Ku6wXfyxHyH27TuXylA==
=rPkJ
-----END PGP SIGNATURE-----

--rz+pwK2yUstbofK6--

Re: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

------=_Part_3001_536881.1166656930017
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 11/16/06, Chris Drake wrote:
>
> I'm expanding, and adding extra web servers to handle my load. My
> Apache/mod_perl environment maintains a new connection for each process, and
> these processes seem to chew up a lot of server resources.



Check out connection pooling.

I have not used it, but it is designed for your scenario.
You do need a test environment that mimics production
to really test an implementation of this.

http://download-west.oracle.com/docs/cd/B19306_01/network.10 2/b14212/intro.htm#sthref67



--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

------=_Part_3001_536881.1166656930017--

Re: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?

------=_Part_3025_15302854.1166657356710
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 11/17/06, Chris wrote:
>
> Doing some digging around, I discover that each Oracle connection consumes
> 4megs of RAM when "doing nothing".
>
> that would add up to 8gigs for 2000 connections... and since I've only got
> 8gigs in total, this is about 4 times more than I can spare.
>
> It would seem that I will definitely need some kind of connection pooling
> -
> does anyone disagree ?
>
> # pmap -d 13040 | tail -1
> mapped: 810872K writeable/private: 3392K shared: 722944K


Is this linux?

If so, pmap does not really give an accurate accounting of memory.

To get an accurate accounting requires parsing the data in /proc/PID/maps
and determining how much memory is in use. This is what pmap and ps
should do. They both double or triple count a few numbers.

The point is, you don't really know how much memory you are using if
this is a linux box.


--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

------=_Part_3025_15302854.1166657356710--