title: using PHP & MySQL... How do I free memory?
body: <p><strong>Requirement:</strong>    </p>
  
  <p>We have two similar tables in two servers. First table in server having unique key columns A,B,C and we are inserting Table1 rows into Table2 which having unique key columns B,C,D. </p>
  
  <p>Table1 is having approx 5 millions rows and Table2 will insert approx .3 millions rows due to different unique key columns constraints. </p>
  
  <p>Here requirement is to fetch all the rows from Table1 and insert into Table2 if no same record is existing in Table2 and in case of record match, increase the count and update the 'cron_modified_date' column in Table2.</p>
  
  <p>PHP version is 5.5 and MySQL version is 5.7 for this setup and DB server is having 6 GB RAM.</p>
  
  <p>While executing below script, processing speed is getting very slow  after processing 2 millions records and RAM is not freeing up and after sometime all RAM is consumed by script and after that script is not processing at all.</p>
  
  <p>As you can see, I am resetting the variables and closing the DB  connection as well but its not freeing up the DB  server RAM. After some reading, i came to know, may be PHP  garbage collection needs to call manually to free  up the resources but its also not freeing up  the RAM.</p>
  
  <p>What I am doing wrong here and how to process millions records using the PHP, MYSQL? </p>
  
  <p>Any other way to free up the RAM while  executing the script and so that script should compete the execution?</p>
  
  <pre><code>/* Fetch records count for batch insert*/
  
  $queryCount = "SELECT count(*) as totalRecords FROM TABLE1 where created_date &gt; = '2018-02-10'";
  $rowsCount = $GLOBALS['db']-&gt;execRaw( $queryCount)-&gt;fetchAll();
  
  $recordsPerIteration = 50000 ;
  $totalCount = $rowsCount[0]['totalRecords']; 
  $start = 0;
  
  gc_disable() ;
  if ( $totalCount &gt; 0 ) {
      while ( $totalCount &gt; 0 ) {
      $query = "SELECT *  FROM TABLE1
                  WHERE where created_date &gt; = '2018-02-10'
                  ORDER BY suggestion_id DESC 
                  LIMIT ".$start.",".$recordsPerIteration;
  
      print "sql is $query" ;
  
      $getAllRows = $GLOBALS['db']-&gt;execRaw( $query )-&gt;fetchAll();
      $GLOBALS['db']-&gt;queryString = null;
      $GLOBALS['db']-&gt;close() ;
  
      foreach ($getAllRows as  $getRow) {
  
          $insertRow  = " INSERT INTO TABLE2 (
                              Name,
                              Company,
                              ProductName,
                              Status,
                              cron_modified_date)
                  VALUE (   
                              ".$GLOBALS['db_ab']-&gt;quote($getRow['Name']).", 
                              ".$GLOBALS['db_ab']-&gt;quote($getRow['Company']).", 
                              ".$GLOBALS['db_ab']-&gt;quote($getRow['ProductName']).",
                              ".$getRow['Status'].",
                              ".$GLOBALS['db_ab']-&gt;quote($getRow['created_date'])."
                          )
                      ON DUPLICATE KEY UPDATE count = (count + 1) , cron_modified_date =  '".$getRow['created_date']."'" ;
  
                  $GLOBALS['db_ab']-&gt;execRaw( $insertRow ) ;
                  $GLOBALS['db_ab']-&gt;queryString = null;
                  $getRow = null;
                  $insertRow = null;
                  $GLOBALS['db_ab']-&gt;close() ;
             }
            gc_enable() ;
            $totalCount   = $totalCount- $recordsPerIteration;
            $start        += $recordsPerIteration ;
            $getAllRows = null;
            gc_collect_cycles() ;
      }
  
  }
  </code></pre>
  
  <h2><strong><em>Solution</em></strong></h2>
  
  <hr>
  
  <p>After suggestions provided by @ABelikov and few hit &amp; trail methods... Finally below  code is working perfectly fine and freeing up  the RAM after every 50K records inserts.</p>
  
  <p>Below are major findings </p>
  
  <ul>
  <li>Free up the DB connections variables after every major operations which involves large data operations and reconnect the DB so that DB buffer flushed out.</li>
  <li><p>Club the insert statements and execute the inserts in one go. Don't execute single record insertion in loop. </p>
  
  <p>Thanks guys for valuable suggestions and helping out.</p>
  
  <pre><code>/* Fetch records count for batch insert*/
  
  
  $queryCount = "SELECT count(*) as totalRecords FROM TABLE1 where created_date &gt; = '2018-02-10'";
  $rowsCount = $GLOBALS['db']-&gt;execRaw( $queryCount)-&gt;fetchAll();
  
  $recordsPerIteration = 50000 ;
  $totalCount = $rowsCount[0]['totalRecords']; 
  $start = 0;
  
  if ( $totalCount &gt; 0 ) {
     while ( $totalCount &gt; 0 ) {
         $query = "SELECT *  FROM TABLE1
              WHERE where created_date &gt; = '2018-02-10'
              ORDER BY suggestion_id DESC 
              LIMIT ".$start.",".$recordsPerIteration;
  
  print "sql is $query" ;
  
  $getAllRows = $GLOBALS['db']-&gt;execRaw( $query )-&gt;fetchAll();
  $GLOBALS['db']-&gt;queryString = null;
  $GLOBALS['db']-&gt;close() ;
  
  $insertRow  = " INSERT INTO TABLE2 (
                          Name,
                          Company,
                          ProductName,
                          Status,
                          cron_modified_date)
              VALUE (  " ;
  
  
  foreach ($getAllRows as  $getRow) {
  
  
          $insertRow  .= (".$GLOBALS['db_ab']-&gt;quote($getRow['Name']).", 
                          ".$GLOBALS['db_ab']-&gt;quote($getRow['Company']).", 
                          ".$GLOBALS['db_ab']-&gt;quote($getRow['ProductName']).",
                          ".$getRow['Status'].",
                          ".$GLOBALS['db_ab']-&gt;quote($getRow['created_date'])."),";
          }
  
  $insertRow=rtrim($insertRow,','); // Remove last ','
  $insertRow.= " ON DUPLICATE KEY UPDATE count = (count + 1) , cron_modified_date =  '".$getRow['created_date']."'" ;
  
  $GLOBALS['db_ab']-&gt;execRaw( $insertRow ) ;              
  //Flushing all data to freeup RAM
  $GLOBALS['db_ab'] = null ;
  $GLOBALS['db'] = null ;
  $insertRow = null;
  
  $totalCount = $totalCount- $recordsPerIteration;
  $start      += $recordsPerIteration ;
  $getAllRows = array();
  $getAllRows = null;
  print " \n Records needs to process ".$totalCount."\n";
  
  }
  
  }
  </code></pre></li>
  </ul>