transacciones - ¿Por qué TRANSACTION/COMMIT mejora el rendimiento tanto con PHP/MySQL(InnoDB)?

transacciones en phpmyadmin

Question

He estado trabajando con la importación de grandes archivos de datos CSV; usualmente menos de 100,000 registros. Estoy trabajando con PHP y MySQL (tablas InnoDB). Necesitaba usar PHP para transformar algunos campos y hacer algo de procesamiento de texto antes de MySQL INSERT s (parte de process_note_data() en el código a continuación). Los LOAD DATA de MySQL no eran factibles, así que no los sugiera.

Recientemente intenté mejorar la velocidad de este proceso mediante el uso de transacciones MySQL usando START TRANSACTION y COMMIT . El aumento en el rendimiento fue sorprendente. El tiempo de procesamiento se redujo en un factor de 20. Entonces, un proceso de 20 minutos solo tomó aproximadamente 1 minuto.

PREGUNTAS.

1.) ¿Alguien entiende por qué hubo tal aumento de rendimiento (20 minutos a 1 minuto)?

2.) ¿Debería preocuparme lo grande que puede llegar la transacción con 100,000 registros?

3.) ¿Debería preocuparme por una gran cantidad de insertos y / o actualizaciones en la transacción?

/*
 * Customer Notes Data:
 * Rows are either a meeting, call or note!
 */
$row = 1;
$data = array();
$fields = array();
$line = '''';

$db->query(''SET autocommit=0;'');
$db->query(''START TRANSACTION;'');

if (($handle = fopen("modules/".$currentModule."/Data/customernote.csv", "r")) !== FALSE) {
  while (($data = fgetcsv($handle, 4096, '','', ''"'')) !== FALSE && $row < 999000) {
    //Row 1 - CSV header row with field names
    if ($row == 1) {
      $csv_fields = $data;
    } elseif ($row > 1) {
      $fields = $this->process_note_data($data, $csv_fields, $row);
    }
    $row++;
  } // end while
  fclose($handle);
}

$db->query(''COMMIT;'');
$db->query(''SET autocommit=1;'');

Nota: El procesamiento de texto / campo se realiza en la llamada a $this->process_note_data() que luego llama a otra clase auxiliar que tiene el código de instrucción INSERT . No tuve suficiente espacio para incluir todo el código. $db->query() es un objeto de base de datos típico para consultas MySQL.

Answer 1

1. Por favor revisa este enlace:

   https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html

   InnoDB debe vaciar el registro en el disco en cada compromiso de transacción si esa transacción realizó modificaciones en la base de datos. Cuando a cada cambio le sigue una confirmación (como ocurre con la configuración de confirmación automática predeterminada), el rendimiento de E / S del dispositivo de almacenamiento limita el número de operaciones potenciales por segundo.

2. Las grandes transacciones pueden afectar el rendimiento durante la confirmación (verifique arriba)

3. Solo en caso de reversión, sin embargo, puede optimizarse usando algunas configuraciones (verifique el enlace)

Answer 2

Mi propia pequeña prueba en .Net (4 campos por registros):

INSERTAR 1 registro, sin transacción: 60 ms

INSERTAR 1 registro, usando transacción: 158 ms

INSERTAR 200 registros utilizando transacciones, confirmar después de cada registro: 17778 ms

INSERT 200 registros sin transacciones: 4940 ms

INSERTAR 200 registros utilizando transacciones, solo confirmar después del último registro: 4552 ms

INSERTAR 1000 registros usando transacciones, solo confirmar después del último registro: 21795 ms

Cliente en Dinamarca, servidor en Bélgica (Google cloud f1-micro).

Quise poner esto en un comentario, pero el formato no es bueno ... así que aquí está mi disculpa por adelantado ;-)