JoeW has asked for the wisdom of the Perl Monks concerning the following question:

Can someone please advise on why I get errors opening the file in the code below. The errors start about half way through the 9th iteration of 25 threads, and are "Too many open files" errors. The error only happens when running in threads, and only when the DBI connect/disconnect are used. This shouldn't affect the open file count at all should it? I'm fairly new to Perl so not sure if I've done something weird. This is on Perl 5.8.8. on Solaris 10. 
use threads ();
use DBI;
use DBD::Oracle;

my $thrds=25;
my $iter=11;
my @threads;

for (my $j=0; $j<$iter; $j++) {
    &start($j);
}

sub start {
    my $k=$_[0];
    for (my $i=0; $i<$thrds; $i++) {
        $threads[$i] = threads->new(\&RunThread,$k, $i);
    }
    for (my $i=0; $i<$thrds; $i++) { $threads[$i]->join; }
}

sub RunThread {
    my $dbh = DBI->connect("dbi:Oracle:ora", "user","pass") or die "fa
+iled connect";
    my ($x, $y)=@_;
    open (my $fh2, ">/tmp/da-$x-$y") or die "failed in thread $! at it
+er $x thread $y " ;
    close ($fh2);
    $dbh->disconnect or die "disconnect failed";
}

[download]

Replies are listed 'Best First'.
Re: Too many open files error with DBD Oracle
by BrowserUk (Patriarch) on Jun 23, 2010 at 14:54 UTC

    If you want to use threads in conjunction with DBI, it behoves you to read the pod for the appropriate DBD::*. In this particular case, this might be useful information:

    ora_dbh_share

    Needs at least Perl 5.8.0 compiled with ithreads. Allows to share database connections between threads. The first connect will make the connection, all following calls to connect with the same ora_dbh_share attribute will use the same database connection. The value must be a reference to a already shared scalar which is initialized to an empty string. 

      our $orashr : shared = '' ;

  $dbh = DBI->connect ($dsn, $user, $passwd, {ora_dbh_share => \$orash
+r}) ;

    [download]

    That said, your snippet is concealing why you are using threads.

    1. It calls start() 11 times synchronously.
    2. start() creates 25 threads and then waits for the to complete.
    3. Each thread,
      1. creates a (*new*) connection to the DB;
      2. Opens a file;
      3. Closes the file;
      4. Drops the connection to the DB.

    It is hard to see why you would be failing for a lack of file handles, when you should have at most 25 open at any given time. Unless Perl on Solaris, or Solaris itself is terminally broken?

    The question of whether you could actually benefit from multiple connections to Oracle is entirely obscured.

[reply]
[d/l]
[select]
      For info, the actual program in which this logic appears does this:

      At Start - opens a large file containing SQL statements, 1 per line.
      Creates 25 threads.
      Reads SQL file, and passes SQL statements to threads one at a time, using shared variables. At each line, waits for a free thread to run statement.

      Each thread runs each SQL statement sent to it, and logs information (response time) in a file, one for each thread. Then signals back to Start that it's ready for another statement.

      At end of file, all threads disconnect and finish, and another file is processed, as above.

      The application, which worked fine until I increased the number of input files, is a test harness to measure SQL response times.

      I think threads is the best way to do this...
[reply]
        For info, the actual program in which this logic appears does this:

        Now that would be interesting to see...

        But for this specific problem, I modified your thread code as follows:

        sub RunThread {
    my $tid = threads->tid;
    my $dbh = DBI->connect("DBD::Pg",...) or die "failed connect";
    my ($x, $y)=@_;
    open (my $fh2, ">/tmp/da-$x-$y") or die "failed in thread $! at it
+er $x thread $y " ;
    print "$tid : ", fileno( $fh2 ); sleep 1;
    close ($fh2);
    $dbh->disconnect or die "disconnect failed";
}

        [download]

        When I run that, I get the following which shows that the filenos underlaying the file handles are being reused by each new batch of threads. If you don't see similar, then it probably means that Solaris/Perl on your system is downlevel or broken. 

        [19:04:06.98] c:\test>DBjunk.pl
1 : 3
2 : 4
3 : 5
4 : 6
5 : 7
6 : 8
8 : 9
10 : 10
9 : 11
7 : 12
12 : 13
14 : 14
13 : 15
11 : 16
15 : 17
16 : 3
17 : 4
19 : 5
18 : 6
20 : 7
21 : 8
22 : 9
23 : 10
24 : 11
25 : 12
27 : 3
28 : 4
26 : 5
29 : 6
30 : 7
31 : 8
32 : 3
33 : 4
34 : 5
35 : 6
36 : 7
37 : 8
38 : 9
39 : 10
40 : 11
41 : 12

        [download]
        Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
        "Science is about questioning the status quo. Questioning authority".
        In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]
[d/l]
[select]

        Maybe your open filehandles get cloned for every thread? You could instead first create your threads and then later on allocate the resources only needed in one thread.

[reply]
Re: Too many open files error with DBD Oracle
by marto (Cardinal) on Jun 23, 2010 at 14:25 UTC

    What does ulimit -a show for 'open files'? Check how many files you have open at time of failure. From the README.solaris:

    "It is possible to build a threaded version of perl on Solaris. The entire perl thread implementation is still experimental, however, so beware."

[reply]
[d/l]
      256 for open files. How can I see how many files I actually have open? The logic of the code should not lead to more than 25 at a time.
[reply]
Re: Too many open files error with DBD Oracle
by Fletch (Bishop) on Jun 23, 2010 at 14:57 UTC

    Anecdotal, but keep this in mind:

    At one time Solaris had a ridiculously (FSVO rediculous) low default for number of file descriptors per process (not per thread; also not to mention I want to say there was a buglet that the stdio library used an unsigned int so even if you raised the resource limit things going through fopen etc would still crap out quickly (again, FSVO quickly; I remember easily hitting it often with mod_perl)).

    To debug you might use truss and watch for open(2)-like calls (things like socket(2) as well) and close calls watching that they match up to make sure something's not leaking descriptors. That number 9 * 25 is 225 which is awful close to 254 which was where the stdio problem reared its head.

    Update: Reformatted and extended.

    The cake is a lie.
    The cake is a lie.
    The cake is a lie.

[reply]
      Excellent, thanks - I hadn't used truss before - very useful.

      Assuming when I see
      open("/data/oracle/product/10.2.0.4/rdbms/mesg/ocius.msb", O_RDONLY) = 99
      I should subsequently see
      close(99)

      Then I seem to have found a problem with above mentioned file open - there are 225 of them, and none is closed.
      Not sure where this leaves me - is this a bug in DBD that I should raise?
[reply]
[reply]

Back to Seekers of Perl Wisdom