Re: Rows returned are out of sync with the request.
Stephen Carville schreef: When I make a call to an Oracle 10g database using DBI and SQLRelay (for connection pooling) I seem to get results from the previous SELECT. For example, If I send the following: select a.statid, b.webstatdesc, a.pendid, c.penddesc, a.certno, to_char(sysdate,'MM-DD- hh24:mi:ss') from viewrequestmasall a, requeststatusparm b, pendingreasonparm c where a.statid = b.statid and a.pendid = c.pendid (+) and tranid = 1 and certno = ?' Then execute it with 29898535 for the bind variable, Show us that part of your code, maybe it is just the binding that goes wrong? The tranid and certno in your where don't have table identifiers. If you assume that the result is one record, check for more. -- Affijn, Ruud Gewoon is een tijger.
Re: DBI Installation Problems
In case if anyone else has this ELFCLASS problem in the future when building DBI DBD::Oracle on Solaris 10 using a 32-bit Perl installation, here is what worked for me. I used gcc version 3.2.2, and Active State's Perl version 5.8.8 as a 32-bit app. I suggest that you first redo the header files, as sunfreeware suggests, #cd /usr/local/lib/gcc-lib/sparc-sun-solaris2.10/3.3.2/install-tools/ #./mkheaders Next you can install DBI as it is normally setup... #perl Makefile.PL #make #make test #make install This configuration worked without a hitch for me. However, when using different versions of gcc, it proved impossible. I needed to install DBD::Oracle as well, and found a problem when I tried to make it about the wrong ELFCLASS. Make sure that your LD_LIBRARY_PATH environment variable points to the 32-bit oracle library as oppose to the standard one which seems to be 64-bit. My LD_LIBRARY_PATH looks like, /opt/oracle/app/product/dev/10.2.0:/opt/oracle/app/product/dev/10.2.0/lib32 And then build it as usual, #perl Makefile.PL #make #make install Hope this helps someone, - Jon From: Jeffrey Seger [EMAIL PROTECTED] To: Jon Daily [EMAIL PROTECTED] CC: dbi-users@perl.org Subject: Re: DBI Installation Problems Date: Mon, 21 Aug 2006 19:38:47 -0400 This line: wrong ELF class: ELFCLASS64 at suggests a 32 bit versus 64 bit problem to me. I've only done one 64 bit perl and DBI install and that was on Linux, not Solaris. Perhaps someone more familiar can jump in. However, if it were my machine, I'd recompile perl from source, and make it 64 bit. That is what I did on my 64 bit Linux box. On 8/21/06, Jon Daily [EMAIL PROTECTED] wrote: Hi Jeff, Unfortunately I am using gcc 3.4.4 now, and that is the same version that Active Perl was installed with. I tried running #make install despite all of the errors that #make test gave me, and when I try to use the DBI module I get this error, Can't load '/opt/ActivePerl-5.8/lib/site_perl/5.8.8/sun4-solaris-thread-multi/auto/DBI/DBI.so' for module DBI: ld.so.1: perl-static: fatal: /opt/ActivePerl-5.8/lib/site_perl/5.8.8/sun4-solaris-thread-multi/auto/DBI/DBI.so: wrong ELF class: ELFCLASS64 at /opt/ActivePerl-5.8/lib/5.8.8/sun4-solaris-thread-multi/DynaLoader.pm line 230. at /opt/ActivePerl-5.8/lib/site_perl/5.8.8/sun4-solaris-thread-multi/DBI.pm line 259 Do you have any other suggestions? I really appreciate the help, Thanks, - Jon From: Jeffrey Seger [EMAIL PROTECTED] To: Jon Daily [EMAIL PROTECTED] CC: dbi-users@perl.org Subject: Re: DBI Installation Problems Date: Fri, 18 Aug 2006 23:10:31 -0400 It's important to compile DBI with the same compiler that perl was compiled with. Active State's perl IIRC is a precompiled binary that may well have been compiled with another compiler. You can fin d out by examining the output of perl -V. If it turns out that you don't have the same compiler available, then I suggest re-compiling perl from source. It'll take a bit of time now, but will save you tons as you compile other CPAN modules as well. On 8/18/06, Jon Daily [EMAIL PROTECTED] wrote: I am trying to install the DBI module on a Solaris 10 machine. I am using active perl 5.8.8. I think I am having issues with the bit size compatibility. The Solaris installation is 64-bit, but perl is 32-bit. Perl is working fine however, so i'm not sure why i am having trouble with the DBI install. I first get errors at, #make test I get lots of errors, but this is the typical one, and seems like the biggest problem, Error: Can't load '/opt/software/DBI-1.51/blib/arch/auto/DBI/DBI.so' for module DBI: ld.so.1: perl-static: fatal: /opt/software/DBI-1.51/blib/arch/auto/DBI/DBI.so: wrong ELF class: ELFCLASS64 at /opt/ActivePerl-5.8/lib/5.8.8/sun4-solaris-thread-multi/DynaLoader.pm line 230. No idea what I can do to fix this...do i need a 64-bit version of Perl? Thanks, - Jon -- -- The darkest places in hell are reserved for those who maintain their neutrality in times of moral crisis. Dante Alighieri (1265 - 1321) They who would give up an essential liberty for temporary security, deserve neither liberty or security. Benjamin Franklin Our lives begin to end the day we become silent about things that matter. Martin Luther King The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment IV to the Constitution of the United States -- --
RE: possible leak in 1.52
I hope Tim will chime in and correct me, but since I was the last reporter of
leaks in the DBI, let me try my best:
I tried to reproduce your leak without success. Perhaps you are not letting
your test run for enough iterations. DBI does cache some handles that it only
releases every 120 calls. So you could see what looks like a 1 SV leak, but
you'll see that after 120 iterations they are all freed.
Try running 1000 iterations and see if you still experience a leak.
I recommend using DBD::Sponge (which comes with DBI) so that you are sure
you're not seeing a leak in your driver. Example code follows. Please let us
know what you find.
my $dbh = DBI-connect(dbi:Sponge:,,,{ RaiseError = 1 });
my $sth = $dbh-prepare(
SELECT * FROM FOO,
{ rows = [[qw/1 2/],[qw/3 4/]], NAME = [qw/a b/]}
);
$sth-execute();
while ( my ($a,$b) = $sth-fetchrow_array() ) {
print a:$a,b:$b\n;
}
$sth-finish;
$dbh-disconnect;
-Original Message-
From: Jonathan [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 23, 2006 04:52
To: dbi-users@perl.org
Subject: Re: possible leak in 1.52
it definitely seems to be a leak, and i've been able to reproduce it
outside of mod_perl
it seems that there are a few potential sources / behaviors
a_ the statement handle created by a prepare never seems to
expire
in the lifetime of a db connection
i could trace this as far back as
DBI::_new_handle
b_ if i connect, run 100 'update x where y=z' , then disconnect, no
SV are released
c_ if i then undef my db handle, a lot of SV are freed - but i seem
to have a 138 SV which are not released
d_ if i reconnect , i seem to use only 15 more SV than the original
connect- whereas the original connect used 231 SV over my test
platforms 'weight'
e_ if i undef the reconnect, I seem to have leaked 1 SV since the
first disconnect
unfortunately, thats as far as my DBI knowledge can take me
RE: Rows returned are out of sync with the request.
Stephen Carville wrote: I'm not quite sure how to describe this problem. When I make a call to an Oracle 10g database using DBI and SQLRelay (for connection pooling) I seem to get results from the previous SELECT. What happens when you use DBI *without* SQLRelay? Philip
Re: possible leak in 1.52
On Aug 23, 2006, at 8:55 AM, Ephraim Dan wrote: I hope Tim will chime in and correct me, but since I was the last reporter of leaks in the DBI, let me try my best: great i'll try all that stuff than let you know
Problem on Perl DBI for Oracle-DBD-1.18
Hi DBI experts, Could you please help me to resolve this Perl Oracle DBD problem. I am using Oracle Client 10g, Perl 5.8.5 (default from RedHat ES4 installation) and Oracle-DBD-1.18. The SQLPLUS and Perl cgi program runs and displays output as expected without any errors from the command line I have the following error when running perl CGI script using Oracle DBD from the web browser: install_driver(Oracle) failed: Can't load '/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected I searched on google and tried the suggestions from forums but still get the problem unsolved. These are what I tried: 1. I've added /home/oracle/oracle/product/10.2.0/oraclient/lib to /etc/ld.so.conf and run ldconfig as shown below [EMAIL PROTECTED] ~]$echo $ORACLE_HOME/lib /etc/ld.so.conf = OK [EMAIL PROTECTED] ~]$/sbin/ldconfig: /home/oracle/oracle/product/10.2.0/oraclient/lib/libexpat.so.0 is not a symbolic link [EMAIL PROTECTED] ~]$ldd /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Oracle/Oracle.so libclntsh.so.10.1 = /home/oracle/oracle/product/10.2.0/oraclient/lib/libclntsh.so.10.1 (0x00559000) libdl.so.2 = /lib/libdl.so.2 (0x00111000) libm.so.6 = /lib/tls/libm.so.6 (0x00115000) libpthread.so.0 = /lib/tls/libpthread.so.0 (0x00138000) libnsl.so.1 = /lib/libnsl.so.1 (0x0014a000) libc.so.6 = /lib/tls/libc.so.6 (0x00218000) libnnz10.so = /home/oracle/oracle/product/10.2.0/oraclient/lib/libnnz10.so (0x083c4000) /lib/ld-linux.so.2 (0x00542000) 2. I put the following environment variables in the configuration file httpd.conf: SetEnv LD_LIBRARY_PATH /home/oracle/oracle/product/10.2.0/oraclient/lib SetEnv ORACLE_HOME /home/oracle/oracle/product/10.2.0/oraclient 3. I've given authority/access to every folder/object listed in the above paths to the user I am using. 4. Making sure all files were accesable and also need to make some links from the $ORACLE_HOME/lib dir to the */lib dir that perl can access. 5. All authority/access and symbolic links for libclntsh.so.10.1, libclntsh.so, libocci.so.10.1 and libocci.so are set up properly in /home/oracle/oracle/product/10.2.0/oraclient/lib lrwxrwxrwx 1 oracle oracle 66 Aug 17 08:25 libclntsh.so - /home/oracle/oracle/product/10.2.0/oraclient/lib/libclntsh.so.10.1 -rwxrwxrwx 1 oracle oracle 0 Aug 23 10:34 libclntsh.so.10.1 lrwxrwxrwx 1 oracle oracle 15 Aug 17 08:25 libocci.so - libocci.so.10.1 -rwxrwxrwx 1 oracle oracle 0 Aug 23 10:38 libocci.so.10.1 Could you please suggest me what else I should try to resolve this problem? I searched on google extensively but found no other sources of solutions beside those listed above. Thanks, Keith Lam Corporate Security (IPC) Tel: 416-327-1941
RE: Problem on Perl DBI for Oracle-DBD-1.18
Lam, Keith (MGS) wrote: Hi DBI experts, Could you please help me to resolve this Perl Oracle DBD problem. I am using Oracle Client 10g, Perl 5.8.5 (default from RedHat ES4 installation) and Oracle-DBD-1.18. The SQLPLUS and Perl cgi program runs and displays output as expected without any errors from the command line I have the following error when running perl CGI script using Oracle DBD from the web browser: install_driver(Oracle) failed: Can't load '/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/Oracle/ Oracle.so' for module DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected [snip] 5. All authority/access and symbolic links for libclntsh.so.10.1, libclntsh.so, libocci.so.10.1 and libocci.so are set up properly in /home/oracle/oracle/product/10.2.0/oraclient/lib What are the permissions on that directory itself. If you su to nobody (or whoever your server runs as), can you cd into that dir? Could you please suggest me what else I should try to resolve this problem? I searched on google extensively but found no other sources of solutions beside those listed above. If you're using Apache, you can start it with the -X option to run in single-process debug mode. That will make it easier to use the LD_DEBUG environment variable to track down your problem. # dumps actual library search paths LD_DEBUG=libs httpd -X After the server has started up, you can hit the web page that will load DBD::Oracle, at which point you should see the search path that ld is using to find the library. Regards Philip
Help with my perl script
Group,
I am trying to write a load testing script.
The script goes like this:
++
my $j = 0;
while ($j 300)
{
$dbh[$j] = DBI-connect ( dbi:Oracle:$instance[$i],
$username[$i],
$passwd[$i],
{
PrintError = 1,
RaiseError = 1,
AutoCommit = 1
}
) || die Database Connection not made $DBI::errstr ;# Create a
Database
#do some random, endless select statement here.
$j++;
}
++
What I want is 300 session do the select statement simultaneously. But this
script will do one session after another.
Do I REALLY have to start 300 perl script in order to this testing, or there
is some way in perl that one script can start up 300 session and do their
invidual select simultaneously?
Thanks for your help.
Joe
-
All-new Yahoo! Mail - Fire up a more powerful email and get things done faster.
DBD-DBM too slow
Hi. I installed DBD::DBM through cpan and everything seems fine.
I wanted to benchmark DBD::DBM's performance, so I did simple test.
I populate table that I created with 20 integer and strings.
In mysql, it was going almost 7000 inserts / sec
but in DBD::DBM, the best I could get was 130/sec
Is there some optimization that I should do?
Is it possible to use something like Mmap to speed up DBD::DBM?
Here is my source code.
Thanks.
###
# This will create Table
###
use DBI;
my $dbh = DBI-connect('dbi:DBM:');
$dbh-{RaiseError} = 1;
my $sth = $dbh-do(CREATE TABLE dbmInt (id INTEGER, input TEXT));
my $sth = $dbh-do(CREATE TABLE dbmChar (id CHAR(4), input TEXT));
$dbh-disconnect;
#
# This will populate Table
# mkUtil is nothing but collection of simple functions like random number
generator
# and printing time and messages..
3
use DBI;
use mkUtil;
my $dbh = DBI-connect('dbi:DBM:');
$dbh-{RaiseError} = 1;
my (@sql_c, @sql_i);
my $id = 0;
mkUtil::printTime( DBM: Start Generating SQL Command String);
foreach my $cid (''...'gzzz'){
push(@sql_c, INSERT INTO dbmChar (id, input) VALUES ('$cid',
'DummyTest'));
push(@sql_i, INSERT INTO dbmInt (id, input) VALUES ($id,
'DummyTest'));
$id++;
}
mkUtil::printTime( DBM: Finished Generating SQL Command String);
my $count = 0;
my $startTime = mkUtil::printTime( DBM: INT Insert Start);
foreach my $sql (@sql_i){
my $sth = $dbh-prepare($sql);
$sth-execute;
$count++;
if($count%2000 eq 0){
mkUtil::printTime($count);
}
}
my $endTime = mkUtil::printTime( DBM: INT Insert END);
mkUtil::printAVG(123032, ($endTime - $startTime), DBM: INT Insert AVG (sec));
$dbh-disconnect;
##3
# This will read data (randomly) from table
use DBI;
use mkUtil;
my $dbh = DBI-connect('dbi:DBM:');
$dbh-{RaiseError} = 1;
my $time = 10;
if(defined $ARGV[0]){
$time = $ARGV[0];
}
my $count = 0;
my $startTime = mkUtil::printTime(DBD::DBM:: Integer Select Start);
my $endTime = mkUtil::getTime();
while($endTime - $startTime $time){
my %r = mkUtil::strRand();
my $rInt = $r{'integer'};
my $ary = $dbh-selectall_arrayref(SELECT * FROM dbmInt WHERE id =
$rInt);
foreach $item (@{$ary}){
foreach $inside (@{$item}){
# print $inside\n;
}
}
# sleep(1);
$endTime = mkUtil::getTime();
$count++;
}
$endTime = mkUtil::printTime(DBD::DBM:: Integer Select End);
print COUNT: $count\n;
mkUtil::printAVG($count, $time, DBD::DBM:: Integer Select AVG (sec));
$count = 0;
$startTime = mkUtil::printTime(DBD::DBM:: Char Select Start);
$endTime = mkUtil::getTime();
while($endTime - $startTime $time){
my %r = mkUtil::strRand();
my $rChar = $r{'string'};
my $ary = $dbh-selectall_arrayref(SELECT * FROM dbmChar WHERE id =
'$rChar');
foreach $item(@{$ary}){
foreach $inside (@{$item}){
# print $inside\n;
}
}
# sleep(1);
$endTime = mkUtil::getTime();
$count++;
}
$endTime = mkUtil::printTime(DBD::DBM:: Char Select End);
print COUNT: $count\n;
mkUtil::printAVG($count, $time, DBD::DBM:: Char Select AVG (sec));
$dbh-disconnect;
Re: DBD-DBM too slow
ManKyu Han wrote:
Hi. I installed DBD::DBM through cpan and everything seems fine.
I wanted to benchmark DBD::DBM's performance, so I did simple test.
I populate table that I created with 20 integer and strings.
In mysql, it was going almost 7000 inserts / sec
but in DBD::DBM, the best I could get was 130/sec
Your tests are flawed see below. OTOH, DBD::DBM is unlikely to be able
to compete with MySQL or even SQLite for speed.
Is there some optimization that I should do?
If your SQL needs are light, use DBI::SQL::Nano instead of
SQL::Statement. And, especially note the next point:
foreach my $sql (@sql_i){
my $sth = $dbh-prepare($sql);
$sth-execute;
$count++;
if($count%2000 eq 0){
mkUtil::printTime($count);
}
}
In DBD::DBM, prepare() takes a fair amount of time. If you prepare once
outside the loop then execute many times in the loop using placeholders
you will gain *lots* of time. This is true of most DBDs but especially
true of DBD::DBM and other SQL::Statement based DBDs.
Also, you might consider using DBI's new profiling capabilities or even
Benchmark when doing tests.
--
Jeff
Re: Rows returned are out of sync with the request.
Dr.Ruud wrote:
Stephen Carville schreef:
When I make a call to an Oracle 10g database using DBI and SQLRelay
(for connection pooling) I seem to get results from the previous
SELECT.
For example, If I send the following:
select a.statid, b.webstatdesc, a.pendid, c.penddesc,
a.certno, to_char(sysdate,'MM-DD- hh24:mi:ss')
from viewrequestmasall a, requeststatusparm b, pendingreasonparm c
where a.statid = b.statid
and a.pendid = c.pendid (+)
and tranid = 1
and certno = ?'
Then execute it with 29898535 for the bind variable,
Show us that part of your code, maybe it is just the binding that goes
wrong?
The tranid and certno in your where don't have table identifiers.
If you assume that the result is one record, check for more.
I didn't know that dropping the table ids could make a difference. I'll
try adding them. Thanks.
These are the subs where I setup and make the calls to DBI.
# this gets the status by certno.
# connection handle is already open
use sql;
{ my ($sth);
sub getstatus_by_certno {
my ($certno) = @_;
my ($status,$line,$timeout);
my (%results);
my $SCRIPT = select a.statid, b.webstatdesc, a.pendid, c.penddesc,
a.certno, to_char(sysdate,'MM-DD- hh24:mi:ss')
from viewrequestmasall a, requeststatusparm b, pendingreasonparm c
where a.statid = b.statid
and a.pendid = c.pendid (+)
and tranid = 1
and certno = ?;
unless ($sth) {
$timeout = log_get_timeout();
$sth = edi_prepare_sql($SCRIPT,$timeout);
}
$timeout = log_get_timeout();
%results = edi_run_select($sth,$timeout,$certno);
undef $sth if ($sth);
$line = edi_get_last_results() . \n;
log_db_addlines($line);
# get the staid and associated message
return evaluate_status(%results);
}
}
# from sql.pm
use DBI;
use Sys::SigAction qw (set_sig_handler);
# prepare for execution
# return sth on success or 0/undef on error
#
sub edi_prepare_sql {
my($script,$timeout) = @_;
my ($sth);
$edi_lastresult = ;
$timeout = $EDI_TIMEOUT unless ($timeout);
unless ($edi_dbh) {
$edi_lastresult = connection to DB lost;
return 0;
}
eval {
my $h = set_sig_handler('ALRM',
sub {$sth = 0; die;},
{ mask=[qw(INT ALRM)],safe =
$edi_safe_signal } );
# set alarm
alarm($timeout);
# prepare the script
$sth = $edi_dbh-prepare($script);
# reset alarm
alarm(0);
};
# reset alarm JIC
alarm(0);
if ($@) {
$edi_lastresult = DBI::errstr;
}
unless ($sth) {
$edi_lastresult =
Prepared timed out in $timeout seconds unless ($edi_lastresult);
}
return $sth;
}
sub edi_run_select {
my($sth,$timeout,@bind_vars) = @_;
my ($rv,$val,$cntr,$row);
my (%tbl);
$tbl{0}[0] = ERROR;
$tbl{1}[0] = noservice;
$edi_lastresult = ;
$timeout = $EDI_TIMEOUT unless ($timeout);
# if handle is not there
unless ($sth) {
$edi_lastresult = cannot execute (connection lost?);
return %tbl;
}
eval {
my $h = set_sig_handler('ALRM',
sub {$rv = 0; die;},
{ mask=[qw(INT ALRM)],safe =
$edi_safe_signal } );
# set alarm
alarm($timeout);
# execute the script
$rv = $sth-execute(@bind_vars);
# reset alarm on success
alarm(0);
};
# reset alarm JIC
alarm(0);
# if execute failed
if ($@) {
$edi_lastresult = DBI::errstr;
return %tbl;
}
unless ($rv) {
$edi_lastresult = ($edi_lastresult ||Select timed out in $timeout
seconds:);
return %tbl;
}
$cntr = 0;
# no headers yet...
$tbl{0}[0] = SUCCESS;
$tbl{1} = ();
while ($row = $sth-fetchrow_arrayref) {
$cntr++;
foreach (@$row) {
$val = (trim($_) || );
push @{$tbl{$cntr}},$val;
$edi_lastresult .= ($val || undef) . \t;
}
# sorta tabular format
chop $edi_lastresult;
$edi_lastresult .= \n;
}
# if no rows -- no rows returned is not always an error
unless ($cntr) {
$edi_lastresult = no rows returned;
$tbl{1}[0] = no rows;
}
return %tbl;
}
--
Stephen Carville [EMAIL PROTECTED]
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602
Re: Rows returned are out of sync with the request.
Garrett, Philip (MAN-Corporate) wrote: Stephen Carville wrote: I'm not quite sure how to describe this problem. When I make a call to an Oracle 10g database using DBI and SQLRelay (for connection pooling) I seem to get results from the previous SELECT. What happens when you use DBI *without* SQLRelay? Dunno yet. I have to rewrite this and one other program to use direct connections. I cannot switch to direct connections across the board beacause other programs share the code and make aobur 3000+ connections per hour during the busy part of the day Philip -- Stephen Carville [EMAIL PROTECTED] Unix and Network Admin Nationwide Totalflood 6033 W. Century Blvd Los Angeles, CA 90045 310-342-3602
Re: Help with my perl script
joe bayer wrote:
Group,
I am trying to write a load testing script.
The script goes like this: ++ my $j =
0; while ($j 300) { $dbh[$j] = DBI-connect (
dbi:Oracle:$instance[$i], $username[$i], $passwd[$i], {
PrintError = 1, RaiseError = 1, AutoCommit = 1 } ) || die
Database Connection not made $DBI::errstr ;# Create a Database #do
some random, endless select statement here. $j++; }
++
What I want is 300 session do the select statement simultaneously.
But this script will do one session after another.
Do I REALLY have to start 300 perl script in order to this testing,
or there is some way in perl that one script can start up 300
session and do their invidual select simultaneously?
Check out Parallel::ForkManager.
Thanks for your help.
Joe
- All-new Yahoo! Mail - Fire up a
more powerful email and get things done faster.
--
Stephen Carville [EMAIL PROTECTED]
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602
Re: Help with my perl script
On 8/23/06, Stephen Carville [EMAIL PROTECTED] wrote:
joe bayer wrote:
Group,
I am trying to write a load testing script.
The script goes like this: ++ my $j =
0; while ($j 300) { $dbh[$j] = DBI-connect (
dbi:Oracle:$instance[$i], $username[$i], $passwd[$i], {
PrintError = 1, RaiseError = 1, AutoCommit = 1 } ) || die
Database Connection not made $DBI::errstr ;# Create a Database #do
some random, endless select statement here. $j++; }
++
What I want is 300 session do the select statement simultaneously.
But this script will do one session after another.
Do I REALLY have to start 300 perl script in order to this testing,
or there is some way in perl that one script can start up 300
session and do their invidual select simultaneously?
Check out Parallel::ForkManager.
Thanks for your help.
Joe
- All-new Yahoo! Mail - Fire up a
more powerful email and get things done faster.
Hi Stephen,
Unless I'm missing something (I'm no expert in this arena)
It seems like a script will ecxecute one stmt at a time anyway-
so how about cranking up 300 separate Perl scripts that synchoronize (ie,
soak up all available system resources simultaneously) with a named
semaphore?
(Win)
$sem = Win32::Semaphore-new($initial,$maximum,$name);
(Unix)
$sem = new IPC::Semaphore(IPC_PRIVATE, 10, S_IRWXU | IPC_CREAT);
I would envision you building a 300-line script to start up each individual
DB connect, and a single Perl script to lower the flag - causing the 300
perl scripts to pounce.
It seems like this is a much better test anyway- because I very much doubt
as *single* perl script will have 300 separate DB connections... but then
again... I don't know what your environment needs.
HTH
KC
RE: Help with my perl script
I believe you *are* missing something. You're going to have to
understand how processes and forking work in Unix. The fork() call
invokes a new process. In effect it is a new script on its own.
Stephen's Parallel::ForkManager is just syntactic sugaring around the
same thing.
Semaphores have no meaning unless you have separate tasks running. A
process is a kind of task. You do not need the semaphore for your test
as I understand it. The operating system's scheduler is going to
allocate time slices to each process as it sees fit.
Each forked child process is a real process of its own, and each will
open a connection to the database, independent of the other child
processes. The time statements are actually executed relative to one
another is essentially random, which is I believe what you want.
I suggest you read the description of fork() in the perlfunc man page
*very* carefully, as it does a lot more than you may have realised the
first time through. Also you should try reading up on Unix process
semantics.
Again, I recommend simply using fork(), in a loop.
-Will
-Original Message-
From: Kevin Carothers [mailto:[EMAIL PROTECTED]
Sent: Wednesday 23 August 2006 16:48
To: Stephen Carville
Cc: dbi-users@perl.org; joe bayer
Subject: Re: Help with my perl script
On 8/23/06, Stephen Carville [EMAIL PROTECTED] wrote:
joe bayer wrote:
Group,
I am trying to write a load testing script.
The script goes like this:
++ my $j =
0; while ($j 300) { $dbh[$j] = DBI-connect (
dbi:Oracle:$instance[$i], $username[$i], $passwd[$i], {
PrintError = 1, RaiseError = 1, AutoCommit = 1 } ) || die
Database Connection not made $DBI::errstr ;# Create a
Database #do
some random, endless select statement here. $j++; }
++
What I want is 300 session do the select statement simultaneously.
But this script will do one session after another.
Do I REALLY have to start 300 perl script in order to
this testing,
or there is some way in perl that one script can start up 300
session and do their invidual select simultaneously?
Check out Parallel::ForkManager.
Thanks for your help.
Joe
- All-new Yahoo! Mail - Fire up a
more powerful email and get things done faster.
Hi Stephen,
Unless I'm missing something (I'm no expert in this arena)
It seems like a script will ecxecute one stmt at a time anyway-
so how about cranking up 300 separate Perl scripts that
synchoronize (ie,
soak up all available system resources simultaneously) with a named
semaphore?
(Win)
$sem = Win32::Semaphore-new($initial,$maximum,$name);
(Unix)
$sem = new IPC::Semaphore(IPC_PRIVATE, 10, S_IRWXU | IPC_CREAT);
I would envision you building a 300-line script to start up
each individual
DB connect, and a single Perl script to lower the flag -
causing the 300
perl scripts to pounce.
It seems like this is a much better test anyway- because I
very much doubt
as *single* perl script will have 300 separate DB
connections... but then
again... I don't know what your environment needs.
HTH
KC
- - - - - Appended by Scientific Atlanta, a Cisco company - - - - -
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is
solely intended for the named addressee (or a person responsible for delivering
it to the addressee). If you are not the intended recipient of this message,
you are not authorized to read, print, retain, copy or disseminate this message
or any part of it. If you have received this e-mail in error, please notify the
sender immediately by return e-mail and delete it from your computer.
Re: Help with my perl script
On 8/23/06, Rutherdale, Will [EMAIL PROTECTED] wrote: Again, I recommend simply using fork(), in a loop. a rabbit doesn't need a loop... $start_time = time + 10; ... # one process fork; # two here fork; # four here fork; # eight here fork; # 16 fork; # 32 fork; # 64 fork; # 128 here fork; # 256 here, load on something is high :) # fork; # 512 , etc # and whatever happens here will happen 256 times, in parallel, # or at least as parallel as your OS can give you select(undef,undef,undef,0.003) while(time $start_time); # wait for trigger ... # connect and crash server -- David L Nicol Dickenson on the flag http://cronos.advenge.com/pc/EmilyDickenson/SecondBook/p39.html
Re: Rows returned are out of sync with the request.
Stephen Carville schreef:
Dr.Ruud:
The tranid and certno in your where don't have table
identifiers. If you assume that the result is one record, check for
more.
I didn't know that dropping the table ids could make a difference.
I'll try adding them. Thanks.
They shouldn't make a difference, unless the names are (or once become)
ambiguous.
These are the subs where I setup and make the calls to DBI.
Don't forget the
use warnings ;
use strict ;
# this gets the status by certno.
# connection handle is already open
use sql;
{ my ($sth);
sub getstatus_by_certno {
my ($certno) = @_;
my ($status,$line,$timeout);
my (%results);
my $SCRIPT = select a.statid, b.webstatdesc, a.pendid,
c.penddesc, a.certno, to_char(sysdate,'MM-DD- hh24:mi:ss')
from viewrequestmasall a, requeststatusparm b, pendingreasonparm c
where a.statid = b.statid
and a.pendid = c.pendid (+)
and tranid = 1
and certno = ?;
unless ($sth) {
Can $sth ever be already defined here?
$timeout = log_get_timeout();
$sth = edi_prepare_sql($SCRIPT,$timeout);
}
$timeout = log_get_timeout();
%results = edi_run_select($sth,$timeout,$certno);
undef $sth if ($sth);
The $sths get undefined.
$line = edi_get_last_results() . \n;
log_db_addlines($line);
# get the staid and associated message
return evaluate_status(%results);
}
}
# from sql.pm
use DBI;
use Sys::SigAction qw (set_sig_handler);
# prepare for execution
# return sth on success or 0/undef on error
#
sub edi_prepare_sql {
my($script,$timeout) = @_;
my ($sth);
$edi_lastresult = ;
$timeout = $EDI_TIMEOUT unless ($timeout);
unless ($edi_dbh) {
$edi_lastresult = connection to DB lost;
return 0;
}
eval {
my $h = set_sig_handler('ALRM',
sub {$sth = 0; die;},
{ mask=[qw(INT ALRM)],safe =
$edi_safe_signal } );
# set alarm
alarm($timeout);
# prepare the script
$sth = $edi_dbh-prepare($script);
# reset alarm
alarm(0);
};
# reset alarm JIC
alarm(0);
if ($@) {
$edi_lastresult = DBI::errstr;
}
unless ($sth) {
$edi_lastresult = Prepared timed out in $timeout seconds
unless ($edi_lastresult);
}
Maybe make that something like:
$sth or $edi_lastresult .= = prepare() timed out in $timeout
seconds.\n ;
return $sth;
}
sub edi_run_select {
my($sth,$timeout,@bind_vars) = @_;
my ($rv,$val,$cntr,$row);
my (%tbl);
$tbl{0}[0] = ERROR;
$tbl{1}[0] = noservice;
$edi_lastresult = ;
$timeout = $EDI_TIMEOUT unless ($timeout);
# if handle is not there
unless ($sth) {
$edi_lastresult = cannot execute (connection lost?);
return %tbl;
}
eval {
my $h = set_sig_handler('ALRM',
sub {$rv = 0; die;},
{ mask=[qw(INT ALRM)],safe =
$edi_safe_signal } );
# set alarm
alarm($timeout);
# execute the script
$rv = $sth-execute(@bind_vars);
# reset alarm on success
alarm(0);
};
# reset alarm JIC
alarm(0);
# if execute failed
if ($@) {
$edi_lastresult = DBI::errstr;
return %tbl;
}
unless ($rv) {
$edi_lastresult = ($edi_lastresult ||Select timed out in
$timeout seconds:);
Why the || here? Maybe change that to:
$edi_lastresult .= = execute() timed out in $timeout
seconds.\n ;
return %tbl;
}
$cntr = 0;
# no headers yet...
$tbl{0}[0] = SUCCESS;
$tbl{1} = ();
while ($row = $sth-fetchrow_arrayref) {
$cntr++;
foreach (@$row) {
$val = (trim($_) || );
push @{$tbl{$cntr}},$val;
$edi_lastresult .= ($val || undef) . \t;
}
# sorta tabular format
chop $edi_lastresult;
$edi_lastresult .= \n;
}
# if no rows -- no rows returned is not always an error
unless ($cntr) {
$edi_lastresult = no rows returned;
$tbl{1}[0] = no rows;
}
return %tbl;
}
--
Affijn, Ruud
Gewoon is een tijger.
Re: Rows returned are out of sync with the request.
Dr.Ruud wrote:
Stephen Carville schreef:
Dr.Ruud:
I've been reviewing the sqltrace logs and I thinks this may be a case
where safe signals are biting me in the butt.
- prepare for DBD::SQLRelay::db (DBI::db=HASH(0x8e05078)~0x8de04f4
'select a.statid, b.webstatdesc, a.pendid, c.penddesc,
a.certno, to_char(sysdate,'MM-DD- hh24:mi:ss')
from viewrequestmasall a, requeststatusparm b, pendingreasonparm c
where a.statid = b.statid
and a.pendid = c.pendid (+)
and tranid = 1
and certno = ?') thr#882a008
1FETCH= ( SQLRelay::Connection=SCALAR(0x8dfff7c) ) [1 items]
('driver_connection' from cache) at SQLRelay.pm line 138
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4
'driver_database_handle' DBI::db=HASH(0x8de04f4)) thr#882a008
- STORE= 1 at SQLRelay.pm line 145
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4
'NUM_OF_PARAMS' 1) thr#882a008
- STORE= 1 at SQLRelay.pm line 146
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4
'driver_is_select' 1) thr#882a008
- STORE= 1 at SQLRelay.pm line 147
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4
'driver_cursor' SQLRelay::Cursor=SCALAR(0x8dfc800)) thr#882a008
- STORE= 1 at SQLRelay.pm line 148
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4
'NUM_OF_PARAMS' 1) thr#882a008
- STORE= 1 at SQLRelay.pm line 158
- prepare= DBI::st=HASH(0x8deb994) at sql.pm line 164
-
- execute for DBD::SQLRelay::st (DBI::st=HASH(0x8deb994)~0x8de03a4
'29721783') thr#882a008
-
1FETCH= SQLRelay::Cursor=SCALAR(0x8dfc800) ('driver_cursor' from
cache) at SQLRelay.pm line 349
1FETCH= SQLRelay::Cursor=SCALAR(0x8dfc800) ('driver_cursor' from
cache) at SQLRelay.pm line 275
- $DBI::errstr () FETCH from lasth=HASH
DBD::SQLRelay::st::errstr
- $DBI::errstr= undef
- DESTROY for DBD::SQLRelay::st (DBI::st=HASH(0x8de03a4)~INNER)
thr#882a008
- DESTROY= undef at xml-queue.pl line 309
Here I request the status fro certno = 29721783. However the alarm was
triggered (18 second time out!) and I 'timed out the call and undefed
the script handle. However I suspect the signal was not delivered until
the execute completed. Leading me to the next invocation:
- prepare for DBD::SQLRelay::db (DBI::db=HASH(0x8e05078)~0x8de04f4
'select a.statid, b.webstatdesc, a.pendid, c.penddesc,
a.certno, to_char(sysdate,'MM-DD- hh24:mi:ss')
from viewrequestmasall a, requeststatusparm b, pendingreasonparm c
where a.statid = b.statid
and a.pendid = c.pendid (+)
and tranid = 1
and certno = ?') thr#882a008
1FETCH= ( SQLRelay::Connection=SCALAR(0x8dfff7c) ) [1 items]
('driver_connection' from cache) at SQLRelay.pm line 138
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180
'driver_database_handle' DBI::db=HASH(0x8de04f4)) thr#882a008
- STORE= 1 at SQLRelay.pm line 145
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180
'NUM_OF_PARAMS' 1) thr#882a008
- STORE= 1 at SQLRelay.pm line 146
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180
'driver_is_select' 1) thr#882a008
- STORE= 1 at SQLRelay.pm line 147
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180
'driver_cursor' SQLRelay::Cursor=SCALAR(0x8e4d628)) thr#882a008
- STORE= 1 at SQLRelay.pm line 148
- STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180
'NUM_OF_PARAMS' 1) thr#882a008
- STORE= 1 at SQLRelay.pm line 158
- prepare= DBI::st=HASH(0x8dd7f10) at sql.pm line 164
--
- execute for DBD::SQLRelay::st (DBI::st=HASH(0x8dd7f10)~0x8dd9180
'2521') thr#882a008
--
1FETCH= SQLRelay::Cursor=SCALAR(0x8e4d628) ('driver_cursor' from
cache) at SQLRelay.pm line 349
1FETCH= SQLRelay::Cursor=SCALAR(0x8e4d628) ('driver_cursor' from
cache) at SQLRelay.pm line 275
1 - FETCH for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER
'NUM_OF_FIELDS') thr#882a008
1 - FETCH= undef at SQLRelay.pm line 379
1 - STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER
'NUM_OF_FIELDS' 6) thr#882a008
1 - STORE= 1 at SQLRelay.pm line 379
1 - STORE for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER
'driver_FETCHED_ROWS' 0) thr#882a008
1 - STORE= 1 at SQLRelay.pm line 384
1 - FETCH for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER
'driver_param_inout_list') thr#882a008
1 - FETCH= undef at SQLRelay.pm line 387
1 - rows for DBD::SQLRelay::st (DBI::st=HASH(0x8dd9180)~INNER)
thr#882a008
2FETCH= SQLRelay::Cursor=SCALAR(0x8e4d628) ('driver_cursor' from
cache) at SQLRelay.pm line 446
1 - rows= 0 at SQLRelay.pm line 399
- execute= '0E0' at sql.pm line 212
- fetchrow_arrayref for DBD::SQLRelay::st
(DBI::st=HASH(0x8dd7f10)~0x8dd9180) thr#882a008
1FETCH= 0
RE: possible leak in 1.52
this doesn't apply to my needs, but are DB connects cached as well with a separate 120 counter? I'm sort of out of my league here, but I think DBI caches the db handles as well as statement handles, and all of them are cleared every 120. So even though you are undef'ing them, they are cached. If you run your code in a loop for more than 120 connect/undef pairs, are you seeing the same behavior (i.e. all freed after 120 times)? If so, it sounds like it's expected and hunky-dory. Best, Ephraim
