Requisito:

Tenemos dos mesas similares en dos servidores. La primera tabla en el servidor tiene columnas de clave únicas A, B, C y estamos insertando filas de Table1 en Table2 que tienen columnas de clave únicas B, C, D.

Table1 tiene aproximadamente 5 millones de filas y Table2 insertará aproximadamente .3 millones de filas debido a diferentes restricciones de columnas clave únicas.

Aquí el requisito es buscar todas las filas de Table1 e insertarlas en Table2 si no existe el mismo registro en Table2 y, en caso de coincidencia de registros, aumentar el recuento y actualizar la columna 'cron_modified_date' en Table2.

La versión de PHP es 5.5 y la versión de MySQL es 5.7 para esta configuración y el servidor de base de datos tiene 6 GB de RAM.

Mientras se ejecuta el siguiente script, la velocidad de procesamiento se vuelve muy lenta después de procesar 2 millones de registros y la RAM no se libera y, después de algún tiempo, el script consume toda la RAM y, después, el script no se procesa en absoluto.

Como puede ver, estoy restableciendo las variables y cerrando la conexión de la base de datos también, pero no está liberando la RAM del servidor de la base de datos. Después de leer un poco, me di cuenta de que puede ser que la recolección de basura de PHP deba llamar manualmente para liberar los recursos, pero tampoco está liberando la RAM.

¿Qué estoy haciendo mal aquí y cómo procesar millones de registros usando PHP, MYSQL?

¿Alguna otra forma de liberar la RAM mientras se ejecuta el script y para que el script compita la ejecución?

/* Fetch records count for batch insert*/

$queryCount = "SELECT count(*) as totalRecords FROM TABLE1 where created_date > = '2018-02-10'";
$rowsCount = $GLOBALS['db']->execRaw( $queryCount)->fetchAll();

$recordsPerIteration = 50000 ;
$totalCount = $rowsCount[0]['totalRecords']; 
$start = 0;

gc_disable() ;
if ( $totalCount > 0 ) {
    while ( $totalCount > 0 ) {
    $query = "SELECT *  FROM TABLE1
                WHERE where created_date > = '2018-02-10'
                ORDER BY suggestion_id DESC 
                LIMIT ".$start.",".$recordsPerIteration;

    print "sql is $query" ;

    $getAllRows = $GLOBALS['db']->execRaw( $query )->fetchAll();
    $GLOBALS['db']->queryString = null;
    $GLOBALS['db']->close() ;

    foreach ($getAllRows as  $getRow) {

        $insertRow  = " INSERT INTO TABLE2 (
                            Name,
                            Company,
                            ProductName,
                            Status,
                            cron_modified_date)
                VALUE (   
                            ".$GLOBALS['db_ab']->quote($getRow['Name']).", 
                            ".$GLOBALS['db_ab']->quote($getRow['Company']).", 
                            ".$GLOBALS['db_ab']->quote($getRow['ProductName']).",
                            ".$getRow['Status'].",
                            ".$GLOBALS['db_ab']->quote($getRow['created_date'])."
                        )
                    ON DUPLICATE KEY UPDATE count = (count + 1) , cron_modified_date =  '".$getRow['created_date']."'" ;

                $GLOBALS['db_ab']->execRaw( $insertRow ) ;
                $GLOBALS['db_ab']->queryString = null;
                $getRow = null;
                $insertRow = null;
                $GLOBALS['db_ab']->close() ;
           }
          gc_enable() ;
          $totalCount   = $totalCount- $recordsPerIteration;
          $start        += $recordsPerIteration ;
          $getAllRows = null;
          gc_collect_cycles() ;
    }

}

Solución


Después de las sugerencias proporcionadas por @ABelikov y algunos métodos de hit & trail ... Finalmente, el código a continuación funciona perfectamente bien y libera la RAM después de cada 50K inserciones de registros.

A continuación se muestran los principales hallazgos

  • Libere las variables de conexión de la base de datos después de cada operación importante que implique grandes operaciones de datos y vuelva a conectar la base de datos para que el búfer de la base de datos se vacíe.
  • Club de las declaraciones de inserción y ejecutar las inserciones de una vez. No ejecute la inserción de un solo registro en bucle.

    Gracias a todos por sus valiosas sugerencias y su ayuda.

    /* Fetch records count for batch insert*/
    
    
    $queryCount = "SELECT count(*) as totalRecords FROM TABLE1 where created_date > = '2018-02-10'";
    $rowsCount = $GLOBALS['db']->execRaw( $queryCount)->fetchAll();
    
    $recordsPerIteration = 50000 ;
    $totalCount = $rowsCount[0]['totalRecords']; 
    $start = 0;
    
    if ( $totalCount > 0 ) {
       while ( $totalCount > 0 ) {
           $query = "SELECT *  FROM TABLE1
                WHERE where created_date > = '2018-02-10'
                ORDER BY suggestion_id DESC 
                LIMIT ".$start.",".$recordsPerIteration;
    
    print "sql is $query" ;
    
    $getAllRows = $GLOBALS['db']->execRaw( $query )->fetchAll();
    $GLOBALS['db']->queryString = null;
    $GLOBALS['db']->close() ;
    
    $insertRow  = " INSERT INTO TABLE2 (
                            Name,
                            Company,
                            ProductName,
                            Status,
                            cron_modified_date)
                VALUE (  " ;
    
    
    foreach ($getAllRows as  $getRow) {
    
    
            $insertRow  .= (".$GLOBALS['db_ab']->quote($getRow['Name']).", 
                            ".$GLOBALS['db_ab']->quote($getRow['Company']).", 
                            ".$GLOBALS['db_ab']->quote($getRow['ProductName']).",
                            ".$getRow['Status'].",
                            ".$GLOBALS['db_ab']->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']->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";
    
    }
    
    }
    
5
EternalSunShine 15 feb. 2018 a las 07:50

2 respuestas

La mejor respuesta

1. Insertar solución de varias filas

Puede acelerar su secuencia de comandos utilizando "insertar varias filas", consulte aquí https : //dev.mysql.com/doc/refman/5.5/en/insert.html

INSERT INTO tbl_name (a, b, c) VALUES (1,2,3), (4,5,6), (7,8,9);

Necesita mantener solo la parte de VALORES en su foreach y mover todos

 $insertRow  = " INSERT INTO TABLE2 (
                             Name,
                             Company,
                             ProductName,
                             Status,
                             cron_modified_date) VALUES ";
 foreach ($getAllRows as  $getRow) {
     $insertRow.="(".$GLOBALS['db_ab']->quote($getRow['Name']).",
                   ".$GLOBALS['db_ab']->quote($getRow['Company']).", 
                   ".$GLOBALS['db_ab']->quote($getRow['ProductName']).",
                   ".$getRow['Status'].",
                   ".$GLOBALS['db_ab']->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']->execRaw( $insertRow ) ;
 $GLOBALS['db_ab']->queryString = null;
 $getRow = null;
 $insertRow = null;
 $GLOBALS['db_ab']->close() ;

Eso ayudará solo si cada "cuerpo" de su foreach suele ejecutarse más de una vez

2. Solución del lado del servidor MySQL

Intente utilizar TRANSACCIÓN https://dev.mysql.com/doc/ refman / 5.7 / en / commit.html http://php.net /manual/en/pdo.begintransaction.php

Simplemente comience uno al comienzo del script y confirme al final. Depende de su servidor, realmente puede ayudar. ¡Pero ten cuidado! Depende de los conjuntos de configuración de su servidor MySQL. Necesita pruebas.

1
ABelikov 15 feb. 2018 a las 13:54
  • PHP no tiene un límite práctico de memoria; MySQL lo hace.
  • La mayor parte del tiempo se dedica a transferir datos entre MySQL y PHP.

A menos que haya entendido mal la tarea, estás trabajando demasiado en ella. Simplemente déjelo a MySQL para hacer todo el trabajo. Sin matriz, sin fragmentación, solo un solo SQL:

INSERT INTO table2
          (Name, Company, ProductName, Status, cron_modified_date, count)
    SELECT Name, Company, ProductName, Status, created_date, 1
        FROM table1
    ON DUPLICATE KEY UPDATE
        count = count + 1
        cron_modified_date = created_date;

Tenga en cuenta que puede necesitar utilizar la pseudo función VALUES() en algunas de las actualizaciones.

Esto evita recuperar todas las filas (o incluso 5000) en PHP, que es probablemente de donde viene el problema de la memoria. Una variable simple en PHP ocupa unos 40 bytes. MySQL está diseñado para trabajar con cualquier número de filas sin consumir RAM.

0
Rick James 22 feb. 2018 a las 13:24