Proceso automático para insertar 50000 registros en una base de datos MySQL

[letni]

Hola, me gustaría saber como puedo realizar un proceso automático para insertar cincuenta mil registros en una base de datos MySQL.

He hecho un stored procedure para llevar a cabo esto, lo ejecuto y luego de 20 minutos o más el proceso no termina por lo que tengo que cerrar mi cliente.

Sin embargo si pruebo con diez mil registros si que se termina de ejecutar.

¿Estoy haciendo algo mal?

Saludos.

[Genetix]

Que comandos o funciones usas para insertarlo?

[letni]

Utilizo este stored procedure:

Código mysql:

delimiter //
 
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @a = 0;
  REPEAT 
    SET @a = @a + 1;
    insert into foo values (@a, CONCAT("bar no. ", @a));
  UNTIL @a > p1 END REPEAT;
END
//
 

luego lo ejecuto con call dorepeat(50000);

[letni]

Era cuestión de tiempo (casi 37 minutos), he aquí el resultado que me ha dado el cliente MySQL:

Código:

(1 row(s) affected)
Execution Time : 00:36:54:766
Transfer Time  : 00:00:00:000
Total Time     : 00:36:54:766

12,3 horas si inserto el millón de registros que tenía pensado.

[letni]

¿Nadie sabe el por qué de la lentitud?

[gnzsoloyo]

Porque estás insertando uno a uno los registros, y con cada INSERT se actualiza el índice... Es una tarea muy lenta.
Hay dos formas básicas de acelerar el proceso, pero ambas se tienen que implementar desde código de aplicación:
1. Usando LOAD DATA INFILE, que requiere que los datos se vuelquen primero a un archivo plano, separado por comas. Colo el LOAD DATA no opera dentro de los store procedures, no puedes crear uno para este treabajo.
2. Usando inserciones múltiples. Es el caso de crear una inmensa cadena en la aplicación con muchos VALUES a insertar en una sola ejecución:

Código sql:

INSERT INTO Tabla1(campos)
VALUES(valores), (valores), (valores), (valores), (valores), 
    (valores), (valores), (valores);
 

Yo personalmente, he manejado inserciones múltiples de 50 registros, de 100 y de hasta un millar, sin mayores problema. Depende del tamaño del buffer.

[letni]

Pero el método de las inserciones múltiples requiere que meta a mano los valores que van a tomar los campos, si tengo que hacer eso con un millón de registros sí que me eternizo más que con lo otro.

La gracia está en que se generen sólos los campos de los registros para que sea un proceso automático.

No se si me explico.

Saludos.

[DBMark]

¿Podrias borrar el indice antes de insertar los registros? Luego crear de nuevo el indice cuando has insertado todos. Seria mucho mas rapido...

[letni]

He borrado el índice y se está insertando el millón de registros, luego si acaba pongo el resultado.

Saludos.

[DBMark]

Estupendo! Si funciona bien es un truco muy util, aunque no es practico para todas las tablas, Saludos.

[letni]

Aun sigue y han pasado ya 11 minutos.

[DBMark]

Hay otra manera de hacer la consulta mas rapido, pero no se si funciona con MYISAM (es un truco de INNODB)

-- antes de importar

SET unique_checks=0;

-- despues de importar

SET unique_checks=1

La verdad es que nunca he usado esto. :(

[DBMark]

Usa el comando

show variables;

ver el tamano de key_buffer_size
tal vez sea demasiado pequeno para una unsertacion de muchos registros.

Mas aqui:

dev.mysql.com/doc/refman/5.0/es/server-parameters.html

[letni]

Pues todavía sigue y ya han pasado 43 minutos.

[gnzsoloyo]

Iniciado por letni

Pero el método de las inserciones múltiples requiere que meta a mano los valores que van a tomar los campos, si tengo que hacer eso con un millón de registros sí que me eternizo más que con lo otro.

La gracia está en que se generen sólos los campos de los registros para que sea un proceso automático.

No se si me explico.

Saludos.

Vuelvo a mi propuesta.
En realidad, si lo programas adecuadamente, no es necesario ni siquiera "meter a mano los valores", sino programar un método tal que tome una taba dinámicamente, cuyos campos por nombre, tipo y orden coincidan con los campos a insertar, y cuyo nombre de tabla coincida con la tabla a actualizar y dejar que ese método se encarque de construir la sentencia con todos sus detalles y ejecutarla.
Yo eso lo tengo programado en VB.Net y funciona hasta con tablas de 14.000.000 de registros (tengo un método de toma de datos remotos entre diferente PCs que hace la tarea en forma masiva), que es el límite de mis prueba.
Puedo pasartelo para que lo veas cómo es la cosa.

si tengo que hacer eso con un millón de registros sí que me eternizo más que con lo otro.

Ni tan así. El volcado de una tabla con 246.000 registros a un archivo .csv, con las conversiones incluidas, solamente lleva algunos segundos.
Uno de los procesos que tuvimos que preparar en una aplciación, vuelva toda una tabla de hasta 600.000 registros a un archivo plano y los levanta en una tabla de MySQL en unmáximo de 48 segundos, según cronometramos. Y eso con una PC P4 de 2.6 GHz y disco de 80 Gb... algo en este momento bastante básico.
El proceso completo de generación de reporte para la prestación de servicios de conductores de una empresa con 20 conductores, vehiculos y 30 días de servicio duró 4.37 minutos. Incluyendo consolidación de datos y volcado a disco.