Re: [EXT] Issues connecting to an Amazon RDS instance: creating the database handle is taking a VERY long time
Have you checked *reverse* dns on the ip - 60 of the 61 seconds sound like dns timeout. > On 4 Apr 2025, at 20:56, Johnson, Bruce E - (bjohnson) > wrote: > > > >> On Mar 31, 2025, at 4:20 PM, Johnson, Bruce E - (bjohnson) >> wrote: >> >> Sadly no. It’s still instantaneous on-prem but takes 61 seconds to get the >> database handle with the ip address. (and DNS resolves just fine ) >> >>> On Mar 31, 2025, at 1:32 AM, Mark Lawrence via dbi-users >>> wrote: >>> >>> External Email >>> The DB tools I use (Oracle SQL Developer and DBVisualizer) are working as expected with the instances I have, but trying to run my Perl code using DBI/DBD::Oracle/Oracle Instant Client is giving me extreme delays creating the DB handle; running a simple test program to time the process shows the program taking a minute or longer to create the database handle. >>> >>> My thoughts in such a scenario usually move towards DNS. I don't know if >>> DBI, or DBD::Oracle, or some Oracle library is doing name to address >>> translation, but perhaps IPv6 Happy Eyeballs is in play with a 60 second >>> timeout? >>> my $dbnamel="host=xxx.pharmacy.arizona.edu;sid=xxx"; my $dbnamec="host=.us-west-2.rds.amazonaws.com;sid=xxx"; >>> >>> Maybe try with IP addresses? > > Turned out to be None of the above. > > Weirdly the delay was from using > "$dbnamec="host=.us-west-2.rds.amazonaws.com;sid=xxx”;" in the > connection statement > > When I switched to using the tnsnames.ora configuration name for the > connection > > $dbnamec=“CLOUDTEST”; > > It worked like a charm. > > -- > Bruce Johnson > University of Arizona > College of Pharmacy > Information Technology Group > > Institutions do not have opinions, merely customs > >
Re: [EXT] Issues connecting to an Amazon RDS instance: creating the database handle is taking a VERY long time
On Mar 31, 2025, at 4:20 PM, Johnson, Bruce E - (bjohnson) wrote: Sadly no. It’s still instantaneous on-prem but takes 61 seconds to get the database handle with the ip address. (and DNS resolves just fine ) On Mar 31, 2025, at 1:32 AM, Mark Lawrence via dbi-users wrote: External Email The DB tools I use (Oracle SQL Developer and DBVisualizer) are working as expected with the instances I have, but trying to run my Perl code using DBI/DBD::Oracle/Oracle Instant Client is giving me extreme delays creating the DB handle; running a simple test program to time the process shows the program taking a minute or longer to create the database handle. My thoughts in such a scenario usually move towards DNS. I don't know if DBI, or DBD::Oracle, or some Oracle library is doing name to address translation, but perhaps IPv6 Happy Eyeballs is in play with a 60 second timeout? my $dbnamel="host=xxx.pharmacy.arizona.edu;sid=xxx"; my $dbnamec="host=.us-west-2.rds.amazonaws.com;sid=xxx"; Maybe try with IP addresses? Turned out to be None of the above. Weirdly the delay was from using "$dbnamec="host=.us-west-2.rds.amazonaws.com;sid=xxx”;" in the connection statement When I switched to using the tnsnames.ora configuration name for the connection $dbnamec=“CLOUDTEST”; It worked like a charm. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: [EXT] Issues connecting to an Amazon RDS instance: creating the database handle is taking a VERY long time
On Tue Apr 01, 2025 at 10:53:25AM +, Mark Lawrence via dbi-users wrote: 1521, which I guess is the DBI default. Or rather[1], upon re-reading the link, port 1526, but you get the idea. [1] I'm not an Oracle user.. :-/ -- Mark Lawrence
Re: [EXT] Issues connecting to an Amazon RDS instance: creating the database handle is taking a VERY long time
On Mon Mar 31, 2025 at 11:20:11PM +, Johnson, Bruce E - (bjohnson) wrote: Sadly no. It’s still instantaneous on-prem but takes 61 seconds to get the database handle with the ip address. (and DNS resolves just fine ) Second idea: you aren't explicitly specifying the connection port. There is evidence[1] of someone else observing a timeout related to 1521, which I guess is the DBI default. [1] https://dba.stackexchange.com/questions/60688/perl-dbi-takes-60-seconds-to-connect-to-an-oracle-database-every-single-time What is your cloud database listening on? As the post suggests, setting DBI_TRACE will provide more food for investigation. -- Mark Lawrence
Re: [EXT] Issues connecting to an Amazon RDS instance: creating the database handle is taking a VERY long time
Sadly no. It’s still instantaneous on-prem but takes 61 seconds to get the database handle with the ip address. (and DNS resolves just fine ) On Mar 31, 2025, at 1:32 AM, Mark Lawrence via dbi-users wrote: External Email The DB tools I use (Oracle SQL Developer and DBVisualizer) are working as expected with the instances I have, but trying to run my Perl code using DBI/DBD::Oracle/Oracle Instant Client is giving me extreme delays creating the DB handle; running a simple test program to time the process shows the program taking a minute or longer to create the database handle. My thoughts in such a scenario usually move towards DNS. I don't know if DBI, or DBD::Oracle, or some Oracle library is doing name to address translation, but perhaps IPv6 Happy Eyeballs is in play with a 60 second timeout? my $dbnamel="host=xxx.pharmacy.arizona.edu;sid=xxx"; my $dbnamec="host=.us-west-2.rds.amazonaws.com;sid=xxx"; Maybe try with IP addresses? -- Mark Lawrence -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Issues connecting to an Amazon RDS instance: creating the database handle is taking a VERY long time
The DB tools I use (Oracle SQL Developer and DBVisualizer) are working as expected with the instances I have, but trying to run my Perl code using DBI/DBD::Oracle/Oracle Instant Client is giving me extreme delays creating the DB handle; running a simple test program to time the process shows the program taking a minute or longer to create the database handle. My thoughts in such a scenario usually move towards DNS. I don't know if DBI, or DBD::Oracle, or some Oracle library is doing name to address translation, but perhaps IPv6 Happy Eyeballs is in play with a 60 second timeout? my $dbnamel="host=xxx.pharmacy.arizona.edu;sid=xxx"; my $dbnamec="host=.us-west-2.rds.amazonaws.com;sid=xxx"; Maybe try with IP addresses? -- Mark Lawrence
Issues connecting to an Amazon RDS instance: creating the database handle is taking a VERY long time
My institution provides us with Amazon Relational Database Servers so we can
migrate from on-prem to cloud.
I've started the process of migrating our databases (on premises are Oracle
19c, same as the Amazon Oracle RDS I’m getting).
The DB tools I use (Oracle SQL Developer and DBVisualizer) are working as
expected with the instances I have, but trying to run my Perl code using
DBI/DBD::Oracle/Oracle Instant Client is giving me extreme delays creating the
DB handle; running a simple test program to time the process shows the program
taking a minute or longer to create the database handle.
It works, eventually, so the problem isn’t, afaik, the perl script.
It’s quite simple I get time(), create a db handle, get time() again, and
calculate the difference between original time and the second time.
I do this for each step of the test :
create handle $dbh = DBI->connect("dbi:Oracle:$dbnamec", $login, $dbpassc,
{RaiseError=>1});
The db name are straightforward “host:;sid="
create cursor counting the number of rows in a table, execute, fetch the result
and print, timing it our between each step.
I'm very consistently getting 61 seconds to just create the database handle,
once connected it performs as expected.
Does anyone else here deal with this Amazon service and is there anything
unexpected I need to do to connect?
(the local support folks are woefully unfamiliar with perl, so I figured I’d
ask here first..
Output of the script:
./connectiontest.pl
Cloud Start time is 1743187931
cloud connect time is 61
cloud prepare time is 0
cloud execute time is 0
There are 463 resources in cloud
cloud fetch time is 0
Local Start time is 1743187992
local connect time is 0
local prepare time is 0
local execute time is 0
local fetch time is 0
There are 463 resources locally
This is for a web application so this kind of issue is a non-starter…
This is a fragmnent of the test script, anonymized
#!/usr/bin/perl
use strict;
use DBI;
my $login="xx";
my $dbpassc="x";
my $dbpassl="x";
my $dbnamel="host=xxx.pharmacy.arizona.edu;sid=xxx";
my $dbnamec="host=.us-west-2.rds.amazonaws.com;sid=xxx";
#set up db connection
my ($time, $otime, $etime);
$time=time();
$otime=$time;
print "Cloud Start time is $time\n";
my $ldac = DBI->connect("dbi:Oracle:$dbnamec", $login, $dbpassc,
{RaiseError=>1});
$time =time();
$etime= $time-$otime;
$otime=$time;
print "cloud connect time is $etime\n";
...
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
