Optimizar los INSERT masivos de MySQL

Tengo una aplicación que necesita ejecutar un script diario; el script diario consiste en download un file CSV con 1,000,000 de filas e insert esas filas en una tabla.

Alojo mi aplicación en Dreamhost. Creé un ciclo while que recorre todas las filas del CSV y realiza una consulta INSERT para cada uno. El caso es que recibo un "500 Internal Server Error". Incluso si lo corto en 1000 files con 1000 filas cada uno, no puedo insert más de 40 o 50 mil filas en el mismo ciclo.

¿Hay alguna manera de que pueda optimizar la input? También estoy considerando ir con un server dedicado; ¿Qué piensas?

¡Gracias!

Pedro

La mayoría de las bases de datos tienen un process optimizado de inserción masiva: MySQL es la syntax LOAD DATA FILE .

Para cargar un file CSV, use:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; 

Insertar valores múltiples, en lugar de hacer

 insert into table values(1,2); 

hacer

 insert into table values (1,2),(2,3),(4,5); 

Hasta un número apropiado de filas a la vez.

O bien, realice la import masiva, que es la forma más eficiente de cargar datos, consulte

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Normalmente, yo diría que simplemente use LOAD DATA INFILE, pero parece que no puede hacerlo con su entorno de alojamiento compartido.

No he usado MySQL en algunos años, pero tienen un documento muy bueno que describe cómo acelerar las inserciones para inserciones masivas: http://dev.mysql.com/doc/refman/5.0/en/insert-speed .html

Algunas ideas que pueden extraerse de esto:

  • Desactivar / habilitar keys alnetworkingedor de las inserciones:

    ALTER TABLE tbl_name DISABLE KEYS; ALTER TABLE tbl_name ENABLE KEYS;

  • Usa muchos valores en tus instrucciones de inserción.

    Es decir: INSERTAR EN LA tabla (col1, col2) VALORES (val1, val2), (.., ..), …

    Si recuerdo correctamente, puede tener hasta 4096 valores por instrucción de inserción.

  • Ejecute un command FLUSH TABLES incluso antes de comenzar, para asegurarse de que no haya grabaciones de disco pendientes que puedan dañar su desempeño de inserción.

Creo que esto hará las cosas rápido. Sugeriría usar LOCK TABLES, pero creo que deshabilitar las teclas hace que sea discutible.

ACTUALIZAR

Después de leer esto, me di count de que deshabilitando sus keys puede eliminar las comprobaciones de coinheritance que son importantes para la carga de files. Puedes arreglar esto por:

  • Asegurándose de que su tabla no tenga datos que "colisionen" con los nuevos datos que se están cargando (si está empezando desde cero, una instrucción TRUNCATE será útil aquí).
  • Escribir una secuencia de commands para limpiar sus datos de input para garantizar que no haya duplicates localmente. La comprobación de duplicates probablemente le esté costando mucho time de database de todos modos.
  • Si haces esto, las TECLAS DE HABILITACIÓN no deberían fallar.

Puede crear un script cronjob que agregue x loggings a la database con una sola request. La secuencia de commands de Cronjob verificará si la última import no ha agregado todas las filas necesarias sino que toma otras x filas.

Entonces puede agregar tantas filas como necesite.

Si tienes tu server dedicado, es más fácil. Simplemente ejecuta el bucle con todas las consultas de inserción.

Por supuesto, puedes intentar establecer time_limit en 0 (si está trabajando en dreamhost) o hacerlo más grande.

Su secuencia de commands PHP probablemente finalice porque excedió el límite de time del script. Dado que estás en un host compartido, no estás de suerte.

Si cambia a un server dedicado y obtiene acceso al shell, la mejor manera sería usar la herramienta de command-line mysql para insert los datos.

La sugerencia de OMG Ponies es genial, pero también he formateado 'manualmente' los datos en el mismo formatting que usa mysqldump, y luego los cargué de esa manera. Muy rapido.

¿Has intentado hacer transactions? Simplemente envíe el command BEGIN a MySQL, haga todas sus inserciones y luego COMMIT . Esto lo aceleraría significativamente, pero como dijo Casablanca, tu guión probablemente también se agote.

Me he encontrado con este problema antes y nos hemos equivocado bastante, pero tendrás que hacer un poco más para que funcione mejor.

Descubrí que, en mi situación, no podía MySQL aceptar una statement GRANDE de INSERT, pero descubrí que si la dividía en grupos de aproximadamente 10k INSERTS en un momento como el que se sugiere, entonces lo hará con bastante rapidez. Una cosa a tener en count es que al hacer múltiples INSERTES como este, lo más probable es que aciertes el límite de time de espera de PHP, pero esto se puede evitar reiniciando el time con set_time_limit ($ seconds) , encontré que hacerlo después de cada INSERT exitoso funcionaba muy bien .

Tienes que tener cuidado al hacer esto, porque podrías encontrarte en un ciclo de crash con un timout ilimitado y para eso yo sugeriría probarlo para asegurarte de que cada INSERT fuera exitoso al search errores reportados por MySQL con mysql_errno () o mysql_error () . También puede detectar errores al verificar el número de filas afectadas por INSERT con mysql_affected_rows () . Luego podría detenerse después de que ocurra el primer error.

Sería mejor si usa sqlloader. Necesitaría dos cosas: primer file de control que especifique las acciones que SQL Loader debería hacer y el segundo file csv que desea cargar. Aquí está el enlace a continuación que lo ayudaría. http://www.oracle-dba-online.com/sql_loader.htm

Vaya a phpmyadmin y select la tabla en la que desea insert.

En la pestaña "operaciones", y luego en la opción / sección "opciones de tabla", cambie el motor de almacenamiento de InnoDB a MyISAM.

Una vez tuve un desafío similar. Que la pases bien.