He estado trabajando con la importación de grandes archivos CSV de los datos; por lo general de menos de 100.000 registros. Estoy trabajando con PHP y MySQL (tablas InnoDB). Necesitaba usar PHP para transformar algunos de los campos y hacer algo de procesamiento de texto antes de MySQL INSERTs (parte de process_note_data() en el código de abajo). MySQL LOAD DATA no era viable, así que por favor no se lo sugiera.

Recientemente he tratado de mejorar la velocidad de este proceso mediante el uso de MySQL transacciones utilizando START TRANSACTION y COMMIT. El aumento de rendimiento fue sorprendente. Tiempo de procesamiento(s) se redujo en un factor de 20. Así, a 20 minutos en procesar sólo tomó alrededor de 1 minuto.

PREGUNTAS.

1.) ¿Alguien entiende por qué existe ese aumento de rendimiento (20 minutos a 1 min)?

2.) Debería estar preocupado acerca de cómo los grandes de la transacción puede conseguir con 100.000 registros?

3.) Debería estar preocupado con un gran número de inserciones 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 texto y de campo, el procesamiento se realiza en la llamada a $this->process_note_data() que, a continuación, llama a otra clase auxiliar que tiene la INSERT código de instrucción. Yo no tenía suficiente espacio para incluir todo el código. $db->query() es un típico objeto de base de datos para las consultas de MySQL.

  • Yo no veo ninguna actualización/inserte aquí, tal vez la eliminación de estos resultó en una ejecución más rápida 😉
  • El texto/procesamiento de campo se realiza en la llamada a $this->process_note_data (), que luego se llama a otra clase auxiliar que tiene la carátula. Yo no tenía suficiente espacio para incluir todo el código. Nota, no es adecuada inserción de todos los registros.
  • transacción ~= in memory processing; comprometerse ~= flush to storage
  • Usted no necesita (y no debe) SET autocommit=0; y de vuelta otra vez; de aquí: «Con el INICIO de la TRANSACCIÓN, autocommit permanece desactivado hasta que finalice la transacción con COMMIT o ROLLBACK.»
InformationsquelleAutor jjwdesign | 2013-02-03

2 Comentarios

  1. 22
    1. Por favor consulte este enlace:

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

      InnoDB debe limpiar el registro en el disco en cada confirmación de la transacción si la transacción hecho modificaciones a la base de datos. Cuando cada cambio es seguido por un commit (como con el valor predeterminado de confirmación automática de ajuste), el rendimiento de e/S del dispositivo de almacenamiento pone un límite en el número de posibles operaciones por segundo.

    2. Grandes transacciones que pueden afectar al rendimiento durante la confirmación (marca de verificación)

    3. Sólo en caso de reversión, sin embargo puede ser optimizado con algunos ajustes (consulte el enlace)

    • Te recomiendo COMETER ing cada 1.000 INSERTA en el fin de reducir el procesamiento en COMETER o soy yo preocuparse de esto un poco demasiado?
    • 1k debería estar bien, pero depende de H/W. sugeriría para ejecutar algunas pruebas aquí. De todos modos – ser cuidadoso acerca de la coherencia de los datos (es decir, – cargado 20k de 100 mil registros y el sistema se estrelló).
    • No hay mucho que el procesamiento se realiza en COMMIT tiempo, así que no hay razón para que periódicamente cometer para que. Sin embargo, si el sistema se bloquea durante una transacción muy grande está en curso, puede requerir una gran cantidad de tiempo a la reversión (y esto se hará durante el inicio del servidor mientras que la no recepción de solicitudes). Con solo 100k filas, sin embargo, usted probablemente no debería preocuparse por eso.
  2. 15

    Mi propia pequeña prueba en .Neto (4 campos de pr. registros:

    INSERTAR 1 registro, no hay transacción:60 ms

    INSERTAR 1 registro, mediante la transacción:158 ms

    INSERTAR 200 registros mediante transacciones de confirmación después de cada registro:17778
    ms

    INSERTAR 200 registros de uso de ninguna de las transacciones:4940 ms

    INSERTAR 200 registros mediante transacciones, solo se confirma después de la última
    registro:4552 ms

    INSERTAR registros de 1000 uso de transacciones, sólo se comprometan después del último registro:21795 ms

    Cliente en Dinamarca, el servidor en Bélgica (en la nube de Google f1-micro).

    Quise poner esto en un comentario, pero el formato no es bueno….así que aquí está mi disculpa de antemano 😉

    • Con sólo 200 registros, no vas a ver mucha diferencia. Con más de 100.000+ registros, usted debe comenzar a ver una gran ventaja en el rendimiento.
    • Muy interesante la diferencia entre cometer posición mediante transacciones… Gracias!!

Dejar respuesta

Please enter your comment!
Please enter your name here