Loading ...
Sorry, an error occurred while loading the content.

Re: DBI persistence problem

Expand Messages
  • Malcolm J Harwood
    ... Are you running Apache::DBI? If not, are you sure that the disconnect call is being reached on each connection? Are you running under PerlRun, Registry or
    Message 1 of 8 , Dec 1, 2004
    • 0 Attachment
      On Wednesday 1 December 2004 08:00 pm, Richard N. Fogle wrote:

      > Basically, we have one MySQL cluster setup for reads and the other
      > cluster for writes - the reads cluster alternates between master and
      > slave. This works fine. The problem we're having is each time the CGI
      > is called it seems to make a persistent connection to the database
      > despite the fact that we have a $dbh->disconnect(); at the end of the
      > code.

      Are you running Apache::DBI?
      If not, are you sure that the disconnect call is being reached on each
      connection?
      Are you running under PerlRun, Registry or your own handler?

      > This causes the application to work well for a period of time,
      > but it will quickly run the databases out of processes (despite raising
      > max_connections to 1024+) and when we go into MySQL to look at the
      > process list it shows a bunch of sleeping connections!

      How many webserver processes do you have running?

      > Appreciate any assistance anyone could provide, is mod_perl caching
      > these scripts

      That is one of the things mod_perl does.

      > and making the database connections persistent?

      That depends on what handler you are using, amongst other things.


      --
      "Let's just say that if complete and utter chaos was lightning, he'd
      be the sort to stand on a hilltop in a thunderstorm wearing wet copper
      armour and shouting 'All gods are bastards'."
      -- Rincewind about Twoflower, The Colour of Magic

      --
      Report problems: http://perl.apache.org/bugs/
      Mail list info: http://perl.apache.org/maillist/modperl.html
      List etiquette: http://perl.apache.org/maillist/email-etiquette.html
    • Perrin Harkins
      ... Are you using Apache::DBI anywhere? Make sure. Have you verified that the disconnect really gets called? You can turn DBI tracing on to find out what s
      Message 2 of 8 , Dec 1, 2004
      • 0 Attachment
        On Wed, 2004-12-01 at 19:00 -0600, Richard N. Fogle wrote:
        > The problem we're having is each time the CGI is called it seems to
        > make a persistent connection to the database despite the fact that we
        > have a $dbh->disconnect(); at the end of the code.

        Are you using Apache::DBI anywhere? Make sure. Have you verified that
        the disconnect really gets called? You can turn DBI tracing on to find
        out what's happening. I would suggest that you might be trapping a copy
        of your $dbh in a closure or a global, but that shouldn't matter if you
        actually call disconnect() on it.

        > We tried placing the connect strings in startup.PL

        Don't do that! Opening database connections before the server forks
        will wreak havoc when multiple processes try to use the same socket.

        - Perrin


        --
        Report problems: http://perl.apache.org/bugs/
        Mail list info: http://perl.apache.org/maillist/modperl.html
        List etiquette: http://perl.apache.org/maillist/email-etiquette.html
      • Malcolm J Harwood
        ... Odd. Normally (in my limited experience) it has the reverse effect as you aren t creating and destroying a connection every time. ... Worth checking
        Message 3 of 8 , Dec 1, 2004
        • 0 Attachment
          On Wednesday 1 December 2004 08:26 pm, Richard N. Fogle wrote:

          > 1. We disabled Apache::DBI - the server can generate thousands of
          > queries per second and this feature literally made the CPU catch fire.

          Odd. Normally (in my limited experience) it has the reverse effect as you
          aren't creating and destroying a connection every time.

          > 3. We have a disconnect at the end of each perl CGI. Not sure if it is
          > being reached, see no plausible reason why it shouldn't - the code isn't
          > that complex.

          Worth checking anyway.

          > 4. This is what we have in httpd.conf:
          > SetHandler perl-script
          > PerlHandler Apache::Registry

          Registry will keep any globals around, so if $dbh is global it wont destroy it
          (though if disconnect is being called, it should disconnect it).

          > 5. We easily reach 1024 webserver processes, apache 1.3.

          If each one is connecting to the db server (assuming you don't have any
          interprocess connection pooling), then that's your 1024+ db connections right
          there. If the connections are made but not disconnected until the end of the
          script, you would (I think) see a lot of "idle" connections that don't have
          an active query (it's already completed) because they haven't reached the end
          of the script and been disconnected yet.


          --
          I always wanted to be somebody, but I should have been more specific.
          - Lily Tomlin

          --
          Report problems: http://perl.apache.org/bugs/
          Mail list info: http://perl.apache.org/maillist/modperl.html
          List etiquette: http://perl.apache.org/maillist/email-etiquette.html
        • Richard N. Fogle
          Malcom, Thank you for your response.. It does sound like a possibility that the script isn t all the way into completion as we have query- process
          Message 4 of 8 , Dec 1, 2004
          • 0 Attachment
            Malcom,

            Thank you for your response..  It does sound like a possibility that the script isn't all the way into completion as we have query->process result->query->process result->query->disconnect (figuratively) scripts.  We were thinking of using SQLRelay as a connection pool and concentrator, thinking it would be less expensive to wrap a connection/cursor through a unix socket into a pool vs. throwing all this at a remote server.  Does that sound off-base in anyone's opinion?

            Regards,

            Rich

            On Wed, 2004-12-01 at 18:10 -0500, Malcolm J Harwood wrote:
            On Wednesday 1 December 2004 08:26 pm, Richard N. Fogle wrote:
            
            > 1.  We disabled Apache::DBI - the server can generate thousands of
            > queries per second and this feature literally made the CPU catch fire.
            
            Odd. Normally (in my limited experience) it has the reverse effect as you 
            aren't creating and destroying a connection every time.
            
            > 3.  We have a disconnect at the end of each perl CGI.  Not sure if it is
            > being reached, see no plausible reason why it shouldn't - the code isn't
            > that complex.
            
            Worth checking anyway.
            
            > 4.  This is what we have in httpd.conf:
            > SetHandler perl-script
            > PerlHandler Apache::Registry
            
            Registry will keep any globals around, so if $dbh is global it wont destroy it 
            (though if disconnect is being called, it should disconnect it).
            
            > 5.  We easily reach 1024 webserver processes, apache 1.3.
            
            If each one is connecting to the db server (assuming you don't have any 
            interprocess connection pooling), then that's your 1024+ db connections right 
            there. If the connections are made but not disconnected until the end of the 
            script, you would (I think) see a lot of "idle" connections that don't have 
            an active query (it's already completed) because they haven't reached the end 
            of the script and been disconnected yet.
            
            
            -- 
            I always wanted to be somebody, but I should have been more specific.
            - Lily Tomlin
            
            
          • Richard N. Fogle
            Hello, We re having quite the time with CGIs being called in mod_perl, listed ... use DBI my $write_dbh =
            Message 5 of 8 , Dec 1, 2004
            • 0 Attachment
              Hello,

              We're having quite the time with CGIs being called in mod_perl, listed below is an example of the DBI connection code:

              ----SNIP----
              use DBI
              my $write_dbh = DBI->connect("DBI:mysql:writeDB:192.168.1.10","foouser","foopass") || die $DBI::errstr;
              my $read_dbh;
              if ( int(rand(4)) ) { $read_dbh = DBI->connect("DBI:mysql:readDB1::192.168.1.20","foouser","foopass") || se
              tReadToWrite($read_dbh); }
              else { $read_dbh = DBI->connect("DBI:mysql:readDB2:192.168.1.21","foouser","foopass") || setReadToWrite($r
              ead_dbh); }
              ----SNIP----

              Basically, we have one MySQL cluster setup for reads and the other cluster for writes - the reads cluster alternates between master and slave.  This works fine.  The problem we're having is each time the CGI is called it seems to make a persistent connection to the database despite the fact that we have a $dbh->disconnect(); at the end of the code.  This causes the application to work well for a period of time, but it will quickly run the databases out of processes (despite raising max_connections to 1024+) and when we go into MySQL to look at the process list it shows a bunch of sleeping connections! 

              We have no real explanation other than perhaps mod_perl, or our (mis)use thereof, may be adding to this.  We have mod_perl loaded in apache but nothing in our startup.PL, nor anything special in httpd.conf.  Appreciate it if anyone has had this problem in the past and could shed some light on what is going on.  This is a very high-load application and there is no way the webservers would survive without mod_perl.  The server-side app consists of about 20 scripts, each with their own connect string as described above and their own disconnect strings.  We tried placing the connect strings in startup.PL and into a seperate file that gets shared but that just drives the load average through the roof.  Appreciate any assistance anyone could provide, is mod_perl caching these scripts and making the database connections persistent?

              Regards,

              Rich



            • Richard N. Fogle
              Malcom and Perrin, Thank you for your quick replies! 1. We disabled Apache::DBI - the server can generate thousands of queries per second and this feature
              Message 6 of 8 , Dec 1, 2004
              • 0 Attachment
                Malcom and Perrin,

                Thank you for your quick replies!

                1.  We disabled Apache::DBI - the server can generate thousands of queries per second and this feature literally made the CPU catch fire.
                2.  Understood about placing the connect strings in startup.PL.  That's why we wrote the list first :)
                3.  We have a disconnect at the end of each perl CGI.  Not sure if it is being reached, see no plausible reason why it shouldn't - the code isn't that complex.
                4.  This is what we have in httpd.conf:
                SetHandler perl-script
                PerlHandler Apache::Registry
                PerlSendHeader On
                Options +ExecCGI
                5.  We easily reach 1024 webserver processes, apache 1.3.
                6.  We have not verified anything with tracing.

                Does this answer all the questions?


                On Wed, 2004-12-01 at 17:39 -0500, Perrin Harkins wrote:
                On Wed, 2004-12-01 at 19:00 -0600, Richard N. Fogle wrote:
                > The problem we're having is each time the CGI is called it seems to
                > make a persistent connection to the database despite the fact that we
                > have a $dbh->disconnect(); at the end of the code.
                
                Are you using Apache::DBI anywhere?  Make sure.  Have you verified that
                the disconnect really gets called?  You can turn DBI tracing on to find
                out what's happening.  I would suggest that you might be trapping a copy
                of your $dbh in a closure or a global, but that shouldn't matter if you
                actually call disconnect() on it.
                
                > We tried placing the connect strings in startup.PL
                
                Don't do that!  Opening database connections before the server forks
                will wreak havoc when multiple processes try to use the same socket.
                
                - Perrin
                
                
                
              • Perrin Harkins
                ... I don t know anyone who has used SQLRelay, so I couldn t say if it works or not. If it does, please report back to us. However, this really sounds like
                Message 7 of 8 , Dec 2, 2004
                • 0 Attachment
                  On Wed, 2004-12-01 at 17:48 -0600, Richard N. Fogle wrote:
                  > We were thinking of using SQLRelay as a connection pool and
                  > concentrator, thinking it would be less expensive to wrap a
                  > connection/cursor through a unix socket into a pool vs. throwing all
                  > this at a remote server. Does that sound off-base in anyone's
                  > opinion?

                  I don't know anyone who has used SQLRelay, so I couldn't say if it works
                  or not. If it does, please report back to us.

                  However, this really sounds like side-stepping the problem. If you
                  aren't using Apache::DBI, your connections should not be persistent.
                  You should really turn on DBI tracing and/or do some more debugging to
                  find out what is going wrong in your code.

                  - Perrin


                  --
                  Report problems: http://perl.apache.org/bugs/
                  Mail list info: http://perl.apache.org/maillist/modperl.html
                  List etiquette: http://perl.apache.org/maillist/email-etiquette.html
                • Rob Mueller
                  ... This really sounds like a lot more httpd mod_perl processes than you should need, even on a heavily loaded system. Are you using a light frontend
                  Message 8 of 8 , Dec 2, 2004
                  • 0 Attachment
                    > 5. We easily reach 1024 webserver processes, apache 1.3.

                    This really sounds like a lot more httpd mod_perl processes than you should
                    need, even on a heavily loaded system. Are you using a light frontend
                    proxy/accelerator?

                    http://perl.apache.org/docs/1.0/guide/strategy.html

                    If not, you definitely should be as that will deal with all the slow clients
                    out there much better than chewing up your valuable mod_perl enabled httpd
                    procs and database connections.

                    Rob


                    --
                    Report problems: http://perl.apache.org/bugs/
                    Mail list info: http://perl.apache.org/maillist/modperl.html
                    List etiquette: http://perl.apache.org/maillist/email-etiquette.html
                  Your message has been successfully submitted and would be delivered to recipients shortly.