Re: Slow connection to Oracle 9i
From: Henri Asseily (henri_at_bizrate.com)
Date: 10/19/04
- Next message: Steffen Goeldner: "Re: Slow connection to Oracle 9i"
- Previous message: James Moosmann: "RE: Invalid cursor state when using PRINT in MSSQL"
- In reply to: Chuck Fox: "Re: Slow connection to Oracle 9i"
- Next in thread: Steffen Goeldner: "Re: Slow connection to Oracle 9i"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Mon, 18 Oct 2004 22:36:59 -0700 To: "Chuck Fox" <ChuckFox2@aol.com>
Please read the documentation for Apache::DBI.
In Apache 1.3 and mod_perl 1.xx, when using Apache::DBI in conjunction
with DBI you get the following:
Each Apache process runs one perl instance. Each perl instance loads up
one instance of Apache::DBI. Each instance of Apache::DBI automatically
'caches' all DBI connections using the connect params as the key.
So for example, let's say that you set up Apache to have a maximum of
50 clients, with an average of 30 clients active at any one time.
Suppose also that you will be connecting to 2 databases, with the
following params:
DBI->connect("dbi:Oracle:database=db_A;server=serv_A",'user1','pass1',
\%params1);
DBI->connect("dbi:Oracle:database=db_B;server=serv_B",'user1','pass1',
\%params2);
Then you will get a maximum of 50 open connections on server A, and 50
open connections on server B. Your average connections will be 30 on
each server. Each Apache child will connect once to each database
server, so you have 2 db connections per child. Each connection will
stay open for a very long time, and only die when the Apache child
dies.
If you don't use Apache::DBI, then you will open and close connects
every time a script or handler fires, and that's not fun.
For Apache 2.0 and mod_perl 2.xx, things are more complicated and I
only know how it was supposed to work (i.e. I don't know the current
state of things).
On Oct 18, 2004, at 5:02 PM, Chuck Fox wrote:
> Paul,
>
> Without knowing much about Oracle (although I can talk your ear off
> about Sybase), you want to "cache" connections to the server and set a
> limit (maybe its 1!) and use this pool of connections to connect to
> your server.
>
> Major advantage:
> No overhead for creating the connection on startup.
>
> Major disadvantage:
> Need to goto java afaik. Apache has some modules written for it
> to do something like this, hit google. There maybe a dbi module that I
> am unaware of that can provide this capability to your webserver via
> cgi/bin perl. Hit CPAN search.
>
>
>
> Paul Appleby wrote on 10/18/2004, 7:57 PM:
>> I realize I may not have been totally clear in my earlier post, which
>> should have said:
>>
>> My CGI application will be called by different visitors to the web
>> site at different times, but they all connect to the same database
>> using the same connection variables, i.e. the same user/password.
>> Are you saying the first visitor to the web site's connection can be
>> left open for all the other visitors? How?
>>
>> Perhaps, though you understand it as such without this clarification.
>>
>> Paul
>>
>>
>>> On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote:
>>>> My CGI application will be called by different users at different
>>>> times. Are you saying the first user's connection can be left open
>>>> for all the other users? How?
>>>
>>> Apart from the other (good) advice here, which you should follow
>>> first,
>>> it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a
>>> different user. See the reauthenticate method in the docs.
>>>
>>> Tim.
>>>
>>>> Paul
>>>>
>>>>> Most people with experience with Oracle know that opening oracle
>>>>> connections
>>>>> is SLOW!
>>>>>
>>>>> Oracle does not appear to consider that a problem, just like they
>>>>> do not
>>>>> consider slow performance for doing DDL a problem
>>>>>
>>>>> Applications that require near real time (OLTP) response times open
>>>>> connections once, and hold open oracle connections across
>>>>> transactions.
>>>>> This is true regardless of the language on the client side. That
>>>>> is why,
>>>>> for instance, Websphere caches pooled connections in the java
>>>>> world.
>>>>>
>>>>> Lincoln
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
>>>>> Sent: Monday, October 18, 2004 12:06 PM
>>>>> To: Paul Appleby
>>>>> Cc: dbi-users@perl.org
>>>>> Subject: Re: Slow connection to Oracle 9i
>>>>>
>>>>>
>>>>> On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote:
>>>>>>
>>>>>>> DBD::Oracle::dr::load_dbnames is only called by data_sources()
>>>>>>> so don't call data_sources() unless you really need to.
>>>>>>
>>>>>> I really do need to call data_sources() but the time it takes to
>>>>>> retrieve data, as shown above, using "Time::HiRes" is only
>>>>>> 0.0100140571594238 seconds. So that's not the issue.
>>>>>
>>>>> dprofpp showed it to take approx the same time as login:
>>>>>
>>>>>> %Time ExclSec CumulS #Calls sec/call Csec/c Name
>>>>>> 21.6 0.090 0.090 1 0.0900 0.0900
>>>>>> DBD::Oracle::db::_login
>>>>>> 21.6 0.090 0.159 1 0.0899 0.1592
>>>>>> DBD::Oracle::dr::load_dbnames
>>>>>> 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN
>>>>>
>>>>> Anyway, I think there's little you can do from DBI to make Oracle
>>>>> connections faster than you already have. Look to changes on the
>>>>> Oracle side - for which other mailing lists (such as oracle-l) are
>>>>> more suitable.
>>>>>
>>>>> Tim.
>>>>
>>>>
>>>> --
>>>> Sincerely,
>>>>
>>>> Paul Appleby
>>
>>
>> --
>> Sincerely,
>>
>> Paul Appleby
>
> --
> Your Friendly Neighborhood DBA,
>
> Chuck
- Next message: Steffen Goeldner: "Re: Slow connection to Oracle 9i"
- Previous message: James Moosmann: "RE: Invalid cursor state when using PRINT in MSSQL"
- In reply to: Chuck Fox: "Re: Slow connection to Oracle 9i"
- Next in thread: Steffen Goeldner: "Re: Slow connection to Oracle 9i"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
