Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

multiprocessing in perl

by shijumic (Novice)
on Apr 15, 2009 at 04:18 UTC ( [id://757545]=perlquestion: print w/replies, xml ) Need Help??

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

I have an issue with a long running perl script, which timesout oracle connection.
So I got a requirement to resolve this issue by querying an emplty table at regular intervals to keep the cconnection alive.
So I want to do it in 3 steps inside a script.
1)need to call a method to query a table at regular intervals . how can I do this? using some infinite loop? also how can i exit from it by calling another method to kill it .
$obj->keep_connection_alive() { this method will query a blank table at regular intervals }

2)At the same time,in parallel with keep_connection_alive(), I need to call another method to load my files. This is the function that takes long time and creates timeout issues
3)after loading the files,i need to kill the keep_connection_alive()
Can I use some threads? Actually i need a multiprocesssing program that run keep_connection_alive() and load_my_file() in parallel and exit out from keep_connection_alive() when I am done with oad_my_file()
I am new to perl..Any help will be greatly appriciated..Thanks

Replies are listed 'Best First'.
Re: multiprocessing in perl
by fzellinger (Acolyte) on Apr 15, 2009 at 04:41 UTC
    I have been using perl with Oracle (via DBI) for many years, and your initial problem statement (timeout) sounds wrong. Please post more information about what error messages you get from example perl code. I have never had timeout issues where perl was the problem, and thus the solution. I have had many "timeout" failures where the root cause of the problem was webserver (apache) or Oracle database related.

    For perl CGI script called via the webserver, I have had the webserver timeout because the CGI script was not completing within a short enough time period. I detected this by looking at the webserver error log. I had to configure apache to wait longer.

    For long-running Oracle SQL operations, I have had "timout-like" issues where the Oracle session had resource limits setup such that my session would be terminated. In these instances, the Oracle DBI statement would return an error message to perl which I would print. Then, I would work on adjusting my Oracle resources to allow my long queries to work (or, I would break my queries into shorter bits, perhaps using intermediate tables or PL/SQL routines).

    Whenever a perl script runs DBI to connect to an Oracle instance, a "session" is created on the Oracle server. In all my years of working with Oracle, I have only been able to have a single Oracle session run a single SQL statement at one time...never multiple SQL statements simultaneously. (Oracle can do "parallel" query execution, but that is still a single SQL statement, just broken into bits and executed in parallel). I think that if you tried to use perl threading or forking, you would be forced to create two separate DBI database handles, which would result in two separate Oracle sessions. The sessions would be completely independent, so that you may be executing "keep_alive" SQL on one session, while the other session keeps running until it terminates.

    There may be some part of perl+DBI+Oracle which I don't know about, but based on what I DO know, the keep_connection_alive() approach you are suggesting is incorrect.

    Feel free to e-mail me more details on the SQL issues you are running into and we can try to work on that bit. Also please specify more details about the environment you are running and look for any error logs and messages which would shed more light on the exact nature of your problem.

      Hi fzellinger,
      Is there any way I can ping the oracle server at regular intervals.Will that resolve this timeout issue.
      This scripts are running in windows boxes, the loading jobs what exactly does is save the files in a work server and report those details to oracle database using SQL loader method.
      The problem is that loading job takes much time and the oracle connection has to wait long time to get this done and report it to the database.
      That is why i thought of another method that pings the oracle server or run a simple query at regular intervals to keep the connection alive while the loading job also runs.
      I will provide you the exact timeout error message once i get that from the other team.
        Shijumic, thanks for explaining your problem further. If I understand correctly, your perl program is periodically posting small bits of information to the Oracle server, but doing a lot of other tasks on the local computer in between posts to Oracle. So, your Oracle session is not performing some long running task, but it instead is sitting idle for long time periods.

        from your initial post, I had assumed that your perl script had submitted some large, time-consuming SQL statement to Oracle, and that Oracle failed to complete it and disconnected your session.

        Assuming that your Oracle connection/session is sitting idle for long time periods, then yes, you could try to keep the session alive by periodically performing small SQL tasks just to do something.

        However, this may not work anyway. Oracle user accounts can have various resource limits set which may have a FIXED time window that even if you perform keep_alive() type ping queries, your session may still be terminated after a fixed connect time.

        If you can connect to Oracle (using SQLPLus) you should be able to query your USER_RESOURCE_LIMITS. Here is what I see for my account on Oracle 10.2:

        -------------------------------- ----------------------------------------
        IDLE_TIME			 480
        Most of my settings are UNLIMITED, but the IDLE_TIME is set to 480 (minutes?). If you have limits on CPU usage, or CONNECT_TIME, you may be kicked out even if you are not idle.

        You could perform some keep_alive() queries if your session is being terminated because of your IDLE_TIME setting, but I would recommend something else: voluntarily disconnect your Oracle session, and re-connect when you need to use it again. Here is why:

        When you connect to Oracle, the Oracle server creates an Oracle session for you. This Oracle session consumes various server resources like memory and processes. If your session is idle 99% of the time, then you are consuming session resources which cannot be used by other users. If many users create sessions which sit idle, then the Oracle administrator may have to get more resources (hardware) to support the demand.

        If you are spending creating Oracle sessions that last more than 2 minutes, and are spending more than 50% of your time idle, then I would recommend disconnecting and reconnecting as needed. These are of course just a randomly guessed rule of can decide what guidelines to follow for your own situation.

Re: multiprocessing in perl
by BrowserUk (Patriarch) on Apr 15, 2009 at 15:05 UTC

    Something as simple as this could be all you need (untested):

    use threads; use threads::shared; use DBI; my $dbh = DBI->connect( ); ## Do stuff my $done :shared = 0; async { $dbh->ping while !$done and sleep 300; ## Adjust frequency to suit }->detach; ## system 'yourLongRunningCmd'; $done = 1; sleep 1; ## Thread disappears ## The rest of your script

    but you'll have to see if pinging from another thread keeps the connection alive on your system.

    Alternatively, if the command runs so long, why not just disconnect from the DB and re-establish a connection when the command is finished?

    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.
      why I am not disconnect and reconnect to db because, every entry to the database marked as each transactions. So i need to keep the connection and report the full loading into the database as one transaction

      I tried approach explained by BrowserUK,in the following way in

      $dbh = $conn->databaseHandle();# I am calling this from my connection +class print "dbh: ". $dbh."\n"; async { $dbh->ping while !$done and sleep 15; ## Adjust frequency to suit }->detach; # system 'yourLongRunningCmd'; &long_running_script; sub long_running_script { print "Testing... long_running test is beign called. will sleep for 3 +0 sec\n"; sleep(30); } $done = 1; sleep 1; ## Thread disappears print "Thread disappears..Done";
      ing i But I am getting the following error:

      thread failed to start: DBD::Oracle::db ping failed: handle 2 is owned by thread 814e008 not current thread 852d948 (handles can't be shared between threads and your driver may need a CLONE method added) at line 30.
      how can I use clone() to resolve this problem.
        Shijumic, thanks for the additional information. I think I understand your problem a little better now. I think that your program flow is something like:
        1. Create Oracle connection
        2. Do a bunch of work in perl (not using Oracle connection)
        3. Insert entry into Oracle table indicating #2 is done
        4. Do a bunch of work in perl (not using Oracle connection)
        5. Insert entry into Oracle table indicating #4 is done
        6. etc...

        Because the work being done in #2, #4, etc does not use the Oracle connection, the connection is idle and get's dropped by the Oracle server. At some point (say step #7) when you try to use the Oracle connection again, you get a Does this accurately describe your program?

        If this is correct, then you should be getting an Oracle error "ORA-02396: exceeded maximum idle time, please connect again"

        Assuming that you need to solve this problem in perl only, then there are a couple of options:
        1: break your long-running tasks into a loop and periodically use the Oracle connection to reset the IDLE counter. Hopefully you can break your long-running task into a loop over smaller tasks.
        2. use threading or forking to allow periodic Oracle connection exercising in parallel with the long-running task. My concern about this approach is that I think perl forking/threading will not work with a single network connection handle to the Oracle session, and that you will be forced to create a new, separate Oracle connection...which is a waste of time because each session will have its own IDLE counter.

        Please look into your USER_RESOURCE_LIMITS in Oracle and capture the perl error message from Oracle so that we know what the real problem is here.

        You mention that you want all of the postings of information to Oracle to be "one transaction". Presumably you want to do this so that if part of your perl job fails, you can use Oracle's ROLLBACK functionality to undo any uncommited work in the transaction. Transactional work is a cool feature of database software like Oracle, but if you have very long-running processes, you should not use this, because you are holding many Oracle resources (temp talbes, rollback segments) idle for long time periods. In many instances, Oracle may clobber these idle resources, resulting in other errors (ROLLBACK segment too old).

        Instead, I would recommend an approach that I have used in the past. I capture the perl script start time and process number and use it as a key in my Oracle log table. I connect to Oracle, add entries to my Oracle log table under my key, then disconnect. I do this repeatedly for all the tasks in my long-running perl job. At the very end of my perl job, I reconnect to Oracle one last time and update the log table, setting a column like UPDATED='Y' for all records under my key. If my perl job had failed, then all my Oracle log table entries would not have had UPDATED='Y' and I would know that the job had failed.

        Are there some other requirements you are subject to which prevent you from using this approach?

        how can I use clone() to resolve this problem.

        Unfortunately, that means that the DBD driver would need, possibly extensive modifications. Which unless you are well versed in DBD internals, you should probably leave to the authors.

        However, perhaps you can turn things around and put the long running part in a thread, and ping the DB from the main thread like so (also untested):

        use threads; use threads::shared; $dbh = $conn->databaseHandle();# I am calling this from my connection +class print "dbh: ". $dbh."\n"; my $done :shared = 0; async { # system 'yourLongRunningCmd'; &long_running_script; $done = 1; }->detach; $dbh->ping while !$done and sleep 15; ## Adjust frequency to suit print "Thread disappears..Done"; exit; sub long_running_script { print "Testing... long_running test is beign called. will sleep fo +r 30 sec\n"; sleep(30); }

        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.

        Read DBI on threads. Basically, you will need to open a second connection from within your second thread, and then it's up to DBD::Oracle to be threadsafe.

Re: multiprocessing in perl
by wol (Hermit) on Apr 15, 2009 at 09:07 UTC
    Hi - welcome to the Monastary.

    Judging from the comments above, the thread approach may not be what's needed, but it's hard to say for sure. If it does turn out to be necessary, then I think threads should cover most of what you need.

    use JAPH;
    print JAPH::asString();

Re: multiprocessing in perl
by mamboking (Monk) on Apr 15, 2009 at 19:36 UTC
    How about preprocessing the data? Process the data into everything you need for one transaction and then load one transaction at a time. Then, if necessary, you can close the connection between each transaction.
      Thanks much

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://757545]
Approved by NetWallah
Front-paged by targetsmart
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2024-06-21 08:39 GMT
Find Nodes?
    Voting Booth?

    No recent polls found

    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.