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

Re: A very important message to informix's user -- my experience !!

Expand Messages
  • ???
    Dear all , I use a simple php source which call the following function : ifx_pconnect , ifx_prepare, ifx_do, ifx_fetch_row, and ifx_free_result and then I use
    Message 1 of 8 , Sep 1, 2000
    • 0 Attachment
      Dear all ,

      I use a simple php source which call the following function :
      ifx_pconnect , ifx_prepare, ifx_do, ifx_fetch_row, and ifx_free_result
      and then I use many pc and many browers to call this php page at the same
      time
      with heavy test , all people just press F5 to refresh page , and then I run
      "top"
      at the console to see httpd's action , with MaxRequestPerChild =0 , -406
      happened !!

      Database engine is in another server , monitor those memory segment in
      "onstat -g seg"
      is caused by those suddenly apahce's connections, I know the informix engine
      works fine , so I
      do not care this !!

      So , why a simple php request which called informix functions make the
      resource not freed
      trouble me , my box only run apache ,only one php page will encounter this
      !! since I set
      MaxRequestPerChild =0 , so this trouble may happen some day because the
      resource
      never get freed and the httpd never die unless shutdown apache !! I tested
      it heavily just
      because I do not want to wait for too long for testing this errors !!

      Anyone who are trying to duplicate the case is to use a php file to call
      informix's functions
      and then tested it by request it heavily and run "top" see the RSS filed of
      httpd ,
      till used memory >= all Memory you got , you encounter the trouble !!

      Thanks for all who help me in this case , you guys help a lot ~~
      Gary , long time no see , have a table tennis game with us , would you ?

      Best,
      Mars



      ----- Original Message -----
      From: "Jonathan Leffler" <jleffler@...>
      To: "MarsJudy" <marsjudy@...>
      Cc: <metabase-dev@egroups.com>; "Gary" <gkang@...>;
      <danny.heijl@...>; "Jonathan Leffler" <Jonathan.Leffler@...>;
      "Manuel Lemos" <mlemos@...>
      Sent: Saturday, September 02, 2000 5:41 AM
      Subject: Re: A very important message to informix's user -- my experience !!


      > On Mon, 28 Aug 2000, ??? wrote:
      > >Dear all,
      > >
      > >Until recently, my php4 (php-4.0.1pl2) encountered informix -406 error
      > >sometimes. As I notice from "top", the httpd processes have RSS up to
      > >71M, or 60M, ..., if all the RSS are too big, the error -406 happened.
      > >All my php page using ifx_pconnect to connect informix, and select data
      > >to display !!
      >
      > -406 Memory allocation failed.
      >
      > Something in the current statement required the allocation of memory
      > for data storage, but the memory was not available. Several things can
      > cause this error. For example, your application has a memory leak; you
      > are asking for more resources than the system is configured to allow;
      > or a problem with the UNIX system itself requires rebooting the
      > system.
      >
      > Roll back the current transaction. Look for ways to make this
      statement
      > simpler or move less data. Also, confer with your UNIX system
      > administrator to solve memory problems or to look for ways to make the
      > operating system give this program more virtual memory in which to
      run.
      > On DOS systems, exit to the operating-system command line, free some
      > disk space, and resubmit your program.
      >
      > >The php is compiled as a module of apache 1.3.12, running in rad hat 6.1,
      > >esql/c is 9.30.UC1 !!
      > >
      > > another linux has informix's 7.30.uc7x1 engine !!
      > >
      > >Notice the following that while used is over Mem, even system process is
      > >out !!
      > >
      > >Mem: 517236K av, 514104K used, 3132K free, 1362904K shrd, 16540K buff
      > >
      > >In my env, MaxSpareServers is 10, MinSpareServers is 5, while I refresh
      > >my php page connecting informix very quickly, the httpd grow very fast,
      > >let us say, 150 (MaxClients) !! and then stop for a while, the httpd
      > >process die and die, then 10 httpd survive (MaxSpareServers), notice
      > >from top that RSS grow very fast !! Also notice that RSS always grow,
      > >but never freed (so that one of them is 71MB in my case), and then you
      > >do it over and over again, the RSS grow and grow fast, and then you
      > >will encounter the memory's problem, stop and restart apache works !!
      > >
      > >all my php program use ifx_pconnect because it is much faster than
      > >ifx_connect !! now, my solution is to modify my httpd.conf, let
      > >MaxRequestPerChild = 500 !! this ensure that while a httpd process
      > >accept 500 request, it will die and free any resource it holds !!
      > >
      > >the following is from informix : onstat -g seg :
      > >
      > >Segment Summary:
      > >id key addr size ovhd class blkused blkfree
      > >0 1381386241 10000000 260710400 4836 R 31818 7
      > >8 1381386249 1f8a2000 32768000 1096 V 933 3067
      > >9 1381386250 217e2000 180224 600 M 16 6
      > >10 1381386251 2180e000 8388608 724 V 161 863
      > >11 1381386252 2200e000 8388608 724 V 197 827
      > >12 1381386253 2280e000 8388608 724 V 64 960
      > >13 1381386254 2300e000 8388608 724 V 92 932
      > >14 1381386255 2380e000 8388608 724 V 21 1003
      > >15 1381386256 2400e000 8388608 724 V 62 962
      > >16 1381386257 2480e000 8388608 724 V 41 983
      > >17 1381386258 2500e000 8388608 724 V 2 1022
      > >18 1381386259 2580e000 8388608 724 V 1 1023
      > >19 1381386260 2600e000 8388608 724 V 1 1023
      > >20 1381386261 2680e000 8388608 724 V 5 1019
      > >21 1381386262 2700e000 8388608 724 V 5 1019
      > >22 1381386263 2780e000 8388608 724 V 14 1010
      > >23 1381386264 2800e000 8388608 724 V 13 1011
      > >24 1381386265 2880e000 8388608 724 V 18 1006
      > >25 1381386266 2900e000 8388608 724 V 7 1017
      > >26 1381386267 2980e000 8388608 724 V 1 1023
      > >27 1381386268 2a00e000 8388608 724 V 1 1023
      > >28 1381386269 2a80e000 8388608 724 V 1 1023
      > >29 1381386270 2b00e000 8388608 724 V 1 1023
      > >30 1381386271 2b80e000 8388608 724 V 2 1022
      > >Total: - - 469819392 - -
      > >33477 23874
      > >
      > >
      > >Any comments are welcome !!
      >
      > How much real memory do you have on the machine? I personally never
      > allow Informix permission to grow unfettered -- I always set the maximum
      > amount of shared memory it is allowed to use, based on a simple rule of
      > thumb - 25% of actual main memory. This is a personal decision/choice;
      > it is not specifically recommended by Informix. I don't get to stress
      > machines, and 25% was sensible back in the days when the big machines
      > had 8 MB of main memory; now even the tiniest have more than that.
      > Therefore, you need to review the upper limit. I'm darn sure that you
      > should ensure that the upper limit is significantly smaller than your
      > physical memory -- virtual memory is great, but not that great; you use
      > shared memory to reduce disk accesses, so if you are using so much shared
      > memory that it has to be paged in and out, you are defeating the purpose
      > of using the shared memory (even if the swap space has better performance
      > than the rest of the disk system).
      >
      > You have 21 units of 8 MB, a 256 MB chunk a 32 MB chunk and a 180 kB
      > chunk (probably used for the shared memory connections). That's 456 MB
      > of main memory in use by the server; you must have a big machine! If
      > you don't have over 1/2 GB of main memory on your box, I recommend
      > setting SHMTOTAL in the $ONCONFIG file to a non-zero value in kB, eg
      > something like 16384 on a 64 MB machine.
      >
      > If you are using ifx_pconnect, then each httpd should end up with one
      > connection to the database. You could monitor this with 'onstat -u' to
      > see whether this is accurate.
      >
      > If there are no leaks in the Informix code for PHP (or elsewhere in the
      > Apache and PHP code), then the size of the httpd should stabilize fairly
      > rapidly after it first connects to the database. Note that with
      > persistent connections, it would be critical to ensure that each
      > statement that's used is also released somewhere. It may be that the
      > code in ifx_close does not release all the statements used by the
      > session, so the persistent connection accumulates more and more
      > statements, etc. I've not looked to see. I am assuming that you should
      > call ifx_close() on a persistent connection to notify the code that the
      > connection is not currently in use.
      >
      > --
      > Yours,
      > Jonathan Leffler (Jonathan.Leffler@...) #include <disclaimer.h>
      > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
      > "I don't suffer from insanity; I enjoy every minute of it!"
      >
      >
      >
    • ???
      Dear Danny , You are right !! One of my program in the testing source do have a ifx_prepare in a while loop but do not call ifx_free_result in the loop , so
      Message 2 of 8 , Sep 4, 2000
      • 0 Attachment
        Dear Danny ,

        You are right !! One of my program in the testing source do have
        a ifx_prepare in a while loop but do not call ifx_free_result in the loop ,
        so that cause the informix engine have so many memory segment ,
        I'd like to say sorry , that is my fault !!

        But after I fixed my source , error 406 still happened after a period of
        heavy test !!

        here is my source :

        <?PHP
        session_start();
        if (!isset($counter)) $counter=0;
        $counter=$counter+1;
        session_register("counter");
        echo $counter;
        $DbObj=ifx_pconnect("sit2@IF6","informix","xyztest");
        $SQL3="set isolation to dirty read" ;
        $oRs3=ifx_prepare($SQL3,$DbObj);
        ifx_do($oRs3);
        $SQL="select * from stores7:customer ";
        $oRs=ifx_prepare($SQL,$DbObj);
        ifx_do($oRs);
        $arrayofcustomer=ifx_fetch_row($oRs,"next");
        echo "<table border='1' width=650 > ";
        echo "<tr bgcolor='#63BDED'>";
        echo "<td >fname</td>";
        echo "<td >lname</td>";
        echo "</tr>";
        while (is_array($arrayofcustomer)){
        $fname=$arrayofcustomer["fname"];
        $lname=$arrayofcustomer["lname"];
        echo "<tr >";
        echo "<td >".$fname."</td>";
        echo "<td >".$lname."</td>";
        echo "</tr>";
        $arrayofcustomer=ifx_fetch_row($oRs,"next");
        }
        echo "</table>";

        ifx_free_result($oRs);
        ifx_free_result($oRs3);
        ifx_close($DbObj);
        echo "</table>";
        echo "<p>";

        echo "</CENTER>";
        echo "<p>";
        echo "</HTML>";
        ?>

        After insert 2000 rows of datas in customer , testing this php from browser
        , you'll see the source
        what httpd use , never goes down since apache start ,trace a process id ,the
        RSS grows !!
        then you'll get 406 after a period of time depend your resource in the box
        !!


        Any comments , ideas are welcome !!


        Best,
        Mars




        ----- Original Message -----
        From: "Danny Heijl" <danny.heijl@...>
        To: "???" <marsjudy@...>; <metabase-dev@egroups.com>
        Cc: "Gary" <gkang@...>; <Jonathan.Leffler@...>; "Manuel
        Lemos" <mlemos@...>
        Sent: Saturday, September 02, 2000 8:58 PM
        Subject: Re: A very important message to informix's user -- my experience !!


        > If the memory leak is Informix-related, the most likely cause will be one
        or
        > more missing calls to ifx_free_result().
        >
        > Danny
        > ---
        >
        > ----- Original Message -----
        > From: "???" <marsjudy@...>
        > To: <metabase-dev@egroups.com>
        > Cc: "Gary" <gkang@...>; <danny.heijl@...>;
        > <Jonathan.Leffler@...>; "Manuel Lemos" <mlemos@...>
        > Sent: Monday, August 28, 2000 9:32 AM
        > Subject: A very important message to informix's user -- my experience !!
        >
        >
        > > Dear all ,
        > >
        > > Until recently , my php4 (php-4.0.1pl2) encountered informix -406
        error
        > > sometimes ,
        > > As I notice from "top" , the httpd processes have RSS up to 71M , or
        > 60M,
        > > ... , if all the RSS are too big , the error -406 happened , all my php
        > > page
        > > using ifx_pconnect to connect informix , and select data to display !!
        > >
        > > The php is compiled as a module of apache 1.3.12 , running in rad hat
        > 6.1
        > > ,
        > > esql /c is 9.30.UC1 !!
        > >
        > > another linux has informix's 7.30.uc7x1 engine !!
        > >
        > > Notice the following that while used is over Mem , even system process
        is
        > > out !!
        > >
        > > Mem: 517236K av, 514104K used, 3132K free, 1362904K shrd, 16540K
        buff
        > >
        > > In my env , MaxSpareServers is 10 , MinSpareServers is 5 , while I
        refresh
        > > my php page connecting informix very quickly , the httpd grow very fast
        ,
        > > let us say ,
        > > 150 (MaxClients) !! and then stop for a while , the httpd process die
        and
        > > die ,
        > > then 10 httpd survive (MaxSpareServers) , notice from top that RSS grow
        > very
        > > fast !!
        > > also notice that RSS always grow , but never freed (so that one of them
        > is
        > > 71MB in my case),
        > > and then you do it over and over again , the RSS grow and grow fast ,
        and
        > > then you
        > > will encounter the memory's problem , stop and restart apache works !!
        > >
        > > all my php program use ifx_pconnect because it is much faster than
        > > ifx_connect !!
        > > now , my solution is to modify my httpd.conf , let
        > > MaxRequestPerChild = 500 !! this ensure that while a httpd process
        accept
        > > 500 request ,
        > > it will die and free any resource it holds !!
        > >
        > > the following is from informix : onstat -g seg :
        > >
        > > Segment Summary:
        > > id key addr size ovhd class blkused blkfree
        > > 0 1381386241 10000000 260710400 4836 R 31818 7
        > > 8 1381386249 1f8a2000 32768000 1096 V 933 3067
        > > 9 1381386250 217e2000 180224 600 M 16 6
        > > 10 1381386251 2180e000 8388608 724 V 161 863
        > > 11 1381386252 2200e000 8388608 724 V 197 827
        > > 12 1381386253 2280e000 8388608 724 V 64 960
        > > 13 1381386254 2300e000 8388608 724 V 92 932
        > > 14 1381386255 2380e000 8388608 724 V 21 1003
        > > 15 1381386256 2400e000 8388608 724 V 62 962
        > > 16 1381386257 2480e000 8388608 724 V 41 983
        > > 17 1381386258 2500e000 8388608 724 V 2 1022
        > > 18 1381386259 2580e000 8388608 724 V 1 1023
        > > 19 1381386260 2600e000 8388608 724 V 1 1023
        > > 20 1381386261 2680e000 8388608 724 V 5 1019
        > > 21 1381386262 2700e000 8388608 724 V 5 1019
        > > 22 1381386263 2780e000 8388608 724 V 14 1010
        > > 23 1381386264 2800e000 8388608 724 V 13 1011
        > > 24 1381386265 2880e000 8388608 724 V 18 1006
        > > 25 1381386266 2900e000 8388608 724 V 7 1017
        > > 26 1381386267 2980e000 8388608 724 V 1 1023
        > > 27 1381386268 2a00e000 8388608 724 V 1 1023
        > > 28 1381386269 2a80e000 8388608 724 V 1 1023
        > > 29 1381386270 2b00e000 8388608 724 V 1 1023
        > > 30 1381386271 2b80e000 8388608 724 V 2 1022
        > > Total: - - 469819392 - -
        > > 33477 23874
        > >
        > >
        > > Any comments are welcome !!
        > >
        > > Best,
        > > Mars
        > >
        > >
        > >
        > >
        >
      • Jonathan Leffler
        ... Since you re finished with $oRs3 here, free it here. ... Since you only use lname and fname, consider fetching just those columns instead of using * . ...
        Message 3 of 8 , Sep 5, 2000
        • 0 Attachment
          On Mon, 4 Sep 2000, Mars wrote:
          >Dear Danny,
          >
          > You are right !! One of my program in the testing source do have
          >a ifx_prepare in a while loop but do not call ifx_free_result in the loop ,
          >so that cause the informix engine have so many memory segment ,
          >I'd like to say sorry , that is my fault !!
          >
          > But after I fixed my source , error 406 still happened after a period of
          >heavy test !!
          >
          >here is my source :
          >
          ><?PHP
          > session_start();
          > if (!isset($counter)) $counter=0;
          > $counter=$counter+1;
          > session_register("counter");
          > echo $counter;
          > $DbObj=ifx_pconnect("sit2@IF6","informix","xyztest");
          > $SQL3="set isolation to dirty read" ;
          > $oRs3=ifx_prepare($SQL3,$DbObj);
          > ifx_do($oRs3);

          Since you're finished with $oRs3 here, free it here.

          > $SQL="select * from stores7:customer ";

          Since you only use lname and fname, consider fetching just those columns
          instead of using '*'.

          > $oRs=ifx_prepare($SQL,$DbObj);
          > ifx_do($oRs);
          > $arrayofcustomer=ifx_fetch_row($oRs,"next");

          I'd remove the line above; see below...

          > echo "<table border='1' width=650 > ";
          > echo "<tr bgcolor='#63BDED'>";
          > echo "<td >fname</td>";
          > echo "<td >lname</td>";
          > echo "</tr>";
          > while (is_array($arrayofcustomer)){

          I use the contraction:

          while (is_array($arrayofcustomer = ifx_fetch_row($oRs))){

          > $fname=$arrayofcustomer["fname"];
          > $lname=$arrayofcustomer["lname"];
          > echo "<tr >";
          > echo "<td >".$fname."</td>";
          > echo "<td >".$lname."</td>";
          > echo "</tr>";
          > $arrayofcustomer=ifx_fetch_row($oRs,"next");

          And then this is unnecessary. All the fetch stuff is visible on a
          single line, which reduces the opportunities for error (eg forgetting
          the fetch at the end of the loop).

          > }
          > echo "</table>";
          >
          > ifx_free_result($oRs);
          > ifx_free_result($oRs3);

          Move this (free $oRs3) up to just after the ifx_do($oRs3) so that it is
          clear that you do release it. Better, create a cover function that does
          this automatically. In one of my applications, I use the following
          function to execute a non-cursory SQL statement ($sql) on the given
          database handle ($dbh):

          <?php
          # Do not leave any blank lines before the PHP marker above.

          $Id = "@(#)$Id: ifx_exec.php,v 1.3 2000/07/13 00:44:27 jleffler Exp $";

          $debug_sql = 0;
          $debug_html = 0;
          $sqlerrd["sqlerrd0"] = 0;

          $ifx_exec_defined = 1;

          function ifx_exec($sql, $dbh)
          {
          global $sqlerrd, $debug_sql;
          if ($debug_sql)
          print "<BR> $sql <BR>\n";
          if (!($sth = ifx_query($sql, $dbh)))
          {
          include "sql.error.php";
          ifx_query("ROLLBACK WORK", $dbh);
          ifx_close($dbh);
          return(0);
          }
          $sqlerrd = ifx_getsqlca($sth);
          ifx_free_result($sth);
          return 1;
          }

          ?>

          It assumes a that the database has transactions so that rollback is
          relevant, and adopts a trivial error handling approach -- close the
          database connection when an error occurs. It does, however, also check
          that the things worked. I use $dbh for the database connection because
          I also use Perl and DBI/DBD::Informix and that's the default
          abbreviation for a database handle or connection.

          > ifx_close($DbObj);
          > echo "</table>";
          > echo "<p>";
          >
          > echo "</CENTER>";
          > echo "<p>";
          > echo "</HTML>";
          >?>
          >
          >After insert 2000 rows of datas in customer, testing this php from
          >browser, you'll see the source what httpd use, never goes down since
          >apache start, trace a process id, the RSS grows!! Then you'll get 406
          >after a period of time depend your resource in the box !!
          >
          >
          >Any comments, ideas are welcome !!

          I've not had the time to do the measurements. On the machines I've used
          recently, I've not run into resource exhaustion, but then the machines
          have quite large memories and I've not been working with several
          thousand rows of data.

          Also, I've been using ifx_connect() instead of ifc_pconnect(). Have you
          tried using just ifx_connect()? I know it will be slower, but if you
          use it, do you still run into -406? If using ifx_connect leaves you
          with working code, then we've narrowed down the areas of interest.
          Either your code is leaving some resources in use, or ifx_pconnect() is
          leaving some resources in use after ifx_close() is called.

          Is there a way to find out which resources are in use? Probably not
          unless the ifx.ec code is modified to track them and a method is
          provided to print them.


          --
          Yours,
          Jonathan Leffler (Jonathan.Leffler@...) #include <disclaimer.h>
          Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
          "I don't suffer from insanity; I enjoy every minute of it!"
        Your message has been successfully submitted and would be delivered to recipients shortly.