Help: Hundreds of connections PHP->Oracle

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Keith E. Sauvant
    #1

    Help: Hundreds of connections PHP->Oracle

    Hi,

    we got a strange problem connecting to an Oracle database onsite with
    our PHP application: During CSV import job handled by our application,
    PHP opens up to some hundred connections to the database. It then
    eventually fails to connect to the database due to the high number of
    open connections.

    We are not able to reproduce this behaviour on our internal
    installations, we get up to 30 connections until the job is finished.

    The environment:
    SuSe/Novell SLES9
    Oracle 10.2.0
    PHP 4.3.11
    OCI8 1.274 (PEAR OCI8 1.1.1 beta)
    Zend Optimizer 2.6.0

    Does it make any difference if oracle is installed locally or on another
    machine? Is it relevant if we connect to the database using a
    service_name or a SID?

    Any comments, hints, help?

    Best regards from Aachen, Germany
    Keith Sauvant
    Tags: None
    • jonathan.beckett
      #2
      Re: Help: Hundreds of connections PHP->Oracle

      I've seen something about this in the past but can't remember exactly
      why it happened (Oracle not closing connections).

      It was something to do with Auto Commit - the behaviour changed when
      you switched it on or off... I don't have any code examples to hand
      though.

        Comment

        • Mladen Gogala
          #3
          Re: Help: Hundreds of connections PHP->Oracle

          On Wed, 15 Feb 2006 15:31:27 +0100, Keith E. Sauvant wrote:
          [color=blue]
          > Does it make any difference if oracle is installed locally or on another
          > machine? Is it relevant if we connect to the database using a
          > service_name or a SID?
          >
          > Any comments, hints, help?[/color]

          You are probably using persistent connections?

          --
          403 Forbidden
          http://www.mgogala.com


            Comment

            • Mladen Gogala
              #4
              Re: Help: Hundreds of connections PHP->Oracle

              On Wed, 15 Feb 2006 07:44:19 -0800, jonathan.becket t wrote:
              [color=blue]
              > I've seen something about this in the past but can't remember exactly
              > why it happened (Oracle not closing connections).[/color]

              It's persistent connections problem. I believe that this was resolved in
              5.1.2, not before. The problem is that whenever one uses oci_pconnect,
              httpd process opens session. For some reason, when a new HTTP request
              is issued, new httpd process is created and logged into the database.
              When the number of httpd threads is high enough, "sessions" parameter
              is exhausted and nobody can connect any more. There are nuances, I believe
              that the problem only manifests itself with shared servers or something
              like that. If you need it for the commercial use, I wholeheartedly
              recommend Zend Core for Oracle. It's beautiful and most of the OCI bugs
              are fixed.

              --
              403 Forbidden
              http://www.mgogala.com


                Comment

                • Keith E. Sauvant
                  #5
                  Re: Help: Hundreds of connections PHP->Oracle

                  Mladen Gogala schrieb:[color=blue]
                  > On Wed, 15 Feb 2006 15:31:27 +0100, Keith E. Sauvant wrote:
                  >[color=green]
                  >> Does it make any difference if oracle is installed locally or on
                  >> another machine? Is it relevant if we connect to the database using
                  >> a service_name or a SID?
                  >>
                  >> Any comments, hints, help?[/color]
                  >
                  > You are probably using persistent connections?[/color]

                  Yes, we do. Means: we were ;-) I tried without persistent connections
                  yesterday and it had far more comprehensible behaviour. Still waiting
                  for the customer test results.
                  [color=blue]
                  > It's persistent connections problem. I believe that this was resolved
                  > in 5.1.2, not before. The problem is that whenever one uses
                  > oci_pconnect, httpd process opens session. For some reason, when a
                  > new HTTP request is issued, new httpd process is created and logged
                  > into the database.[/color]

                  But: In our case it is ONE PROCESS that starts opening connections. We
                  start a script that runs for some hours. It runs fine for one hour and
                  then it starts opening additional connections to the database. Strange
                  behaviour.
                  [color=blue]
                  > If you need it for the commercial use, I wholeheartedly recommend
                  > Zend Core for Oracle. It's beautiful and most of the OCI bugs are
                  > fixed.[/color]

                  I like the idea of using Zend Core for Oracle. But: PHP5 is still not
                  certified for some of our customers.

                  We use the latest PECL OCI8-Extension. That should be exactly the one
                  Zend Core uses. Am I right?

                  Best regards
                  Keith

                    Comment

                    • Mladen Gogala
                      #6
                      Re: Help: Hundreds of connections PHP->Oracle

                      On Thu, 16 Feb 2006 09:39:11 +0100, Keith E. Sauvant wrote:
                      [color=blue]
                      > We use the latest PECL OCI8-Extension. That should be exactly the one
                      > Zend Core uses. Am I right?[/color]

                      I'm not sure about that. I believe it is a separate branch.

                      --
                      403 Forbidden
                      http://www.mgogala.com


                        Comment

                        • Andy Hassall
                          #7
                          Re: Help: Hundreds of connections PHP->Oracle

                          On Thu, 16 Feb 2006 11:44:03 GMT, Mladen Gogala <gogala@sbcglob al.net> wrote:
                          [color=blue]
                          >On Thu, 16 Feb 2006 09:39:11 +0100, Keith E. Sauvant wrote:
                          >[color=green]
                          >> We use the latest PECL OCI8-Extension. That should be exactly the one
                          >> Zend Core uses. Am I right?[/color]
                          >
                          >I'm not sure about that. I believe it is a separate branch.[/color]

                          The OCI8 extension bundled with Zend Core, the one from PECL and the one in
                          PHP 5.1.2 are all the same branch.

                          --
                          Andy Hassall :: andy@andyh.co.u k :: http://www.andyh.co.uk
                          http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

                            Comment

                            • boozkachu@gmail.com
                              #8
                              Re: Help: Hundreds of connections PHP-&gt;Oracle

                              I would use twisted and cherrypy instead. You wont need nearly as many
                              connections to oracle.

                                Comment

                                • Mladen Gogala
                                  #9
                                  Re: Help: Hundreds of connections PHP-&gt;Oracle

                                  On Fri, 17 Feb 2006 15:34:29 -0800, boozkachu wrote:
                                  [color=blue]
                                  > I would use twisted and cherrypy instead.[/color]

                                  I fail to see how could bakery products help him. On the other
                                  hand, blessed are the cheesemakers, so why not try with cherry
                                  pies? Always look on the bright side of life. For life is quite absurd,
                                  and death's the final word, you must always face the curtain with a bow.
                                  Forget about your sin, give the audience a grin, enjoy it's the last
                                  chance of the hour, so always look on the bright side ....

                                  --
                                  403 Forbidden
                                  http://www.mgogala.com


                                    Comment

                                    • Christopher.Jones@oracle.com
                                      #10
                                      Re: Help: Hundreds of connections PHP-&gt;Oracle

                                      "Keith E. Sauvant" <ne.ws.ksau@spa mgourmet.com> writes:
                                      [color=blue]
                                      > I like the idea of using Zend Core for Oracle. But: PHP5 is still not
                                      > certified for some of our customers.[/color]

                                      Coming late to the party but FWIW, the re-factored OCI8 driver (aka
                                      OCI8 1.1 onwards) was designed to be usable with PHP 4.
                                      [color=blue]
                                      > We use the latest PECL OCI8-Extension. That should be exactly the one
                                      > Zend Core uses. Am I right?[/color]

                                      ZCO picks up the latest PECL build. The release schedules are not
                                      completely syncronized but there was a new PECL OCI8 1.2 bundle on 16
                                      March at http://pecl.php.net/package/oci8 that matches ZCO 1.3.1. (It
                                      will take a short while for the pre-built Windows stack on
                                      http://pecl4win.php.net/ext.php/php_oci8.dll to complete builds of
                                      1.2.)

                                      Another advantage of the re-factored OCI8 driver (aka OCI8 1.1
                                      onwards) is that the the oci_close/ocilogoff function is no-longer a
                                      no-op. You can now force a connection to close. This may be useful
                                      in scripts that only access Oracle for a short time relative to other
                                      PHP processing because resources can be freed for other Oracle users.

                                      Chris

                                        Comment

                                        Previous template Next
                                        Working...