mysql - transacciones - myisam vs innodb ventajas

InnoDB tarda más de una hora en importar archivos de 600MB, MyISAM en pocos minutos (4)

Actualmente estoy trabajando en la creación de un entorno para probar el rendimiento de una aplicación; Estoy probando con MySQL e InnoDB para averiguar cuál nos puede servir mejor. Dentro de este entorno, prepararemos automáticamente la base de datos (carga de volcados existentes) e instrumentaremos nuestras herramientas de prueba.

Me estoy preparando para probar el mismo volcado de datos con MySQL e InnoDB, pero ya no logro llevar la importación inicial a una velocidad utilizable para la parte InnoDB. El volcado inicial tomó más tiempo, pero eso no me preocupaba aún:

$ for i in testdb_myisam testdb_innodb; do time mysqldump --extended-insert $i > $i.sql; done real 0m38.152s user 0m8.381s sys 0m2.612s real 1m16.665s user 0m6.600s sys 0m2.552s

Sin embargo, los tiempos de importación fueron bastante diferentes:

$ for i in testdb_myisam testdb_innodb; do time mysql $i < $i.sql; done real 2m52.821s user 0m10.505s sys 0m1.252s real 87m36.586s user 0m10.637s sys 0m1.208s

Después de la investigación que vine, cambiar las tablas de MyISAM a InnoDB hace que el sistema sea lento y luego se usa el set global innodb_flush_log_at_trx_commit=2 :

$ time mysql testdb_innodb < testdb_innodb.sql real 64m8.348s user 0m10.533s sys 0m1.152s

En mi humilde opinión todavía sorprendentemente lento. También he desactivado log_bin para estas pruebas y aquí hay una lista de todas las variables de mysql .

¿Debo aceptar estos largos tiempos InnoDB o se pueden mejorar? Tengo el control total sobre este servidor MySQL, ya que es puramente para este entorno de prueba.

Puedo aplicar configuraciones especiales solo para la importación inicial y volver a cambiarlas para pruebas de aplicaciones para que coincidan mejor con los entornos de producción.

Actualizar:

Teniendo en cuenta los comentarios, he desactivado la confirmación automática y las diversas comprobaciones:

$ time ( echo "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" / ; cat testdb_innodb.sql ; echo "COMMIT;" ) | mysql testdb_innodb;date real 47m59.019s user 0m10.665s sys 0m2.896s

La velocidad mejoró, pero no tanto. ¿Mi prueba es defectuosa?

Actualización 2:

Pude obtener acceso a una máquina diferente si las importaciones solo llevaran unos 8 minutos. Comparé las configuraciones y apliqué las siguientes configuraciones a mi instalación de MySQL:

innodb_additional_mem_pool_size = 20971520 innodb_buffer_pool_size = 536870912 innodb_file_per_table innodb_log_buffer_size = 8388608 join_buffer_size = 67104768 max_allowed_packet = 5241856 max_binlog_size = 1073741824 max_heap_table_size = 41943040 query_cache_limit = 10485760 query_cache_size = 157286400 read_buffer_size = 20967424 sort_buffer_size = 67108856 table_cache = 256 thread_cache_size = 128 thread_stack = 327680 tmp_table_size = 41943040

Con estos ajustes, ahora tengo unos 25 minutos. Aún muy lejos de los pocos minutos que lleva MyISAM, pero se está volviendo más útil para mí.

¿Has intentado iniciar una transacción desde el principio y comprometerla al final? A partir de la pregunta que vinculó : "Modifique el paso Insertar datos para iniciar una transacción al inicio y para comprometerla al final. Obtendrá una mejora, lo garantizo".

Recuerde que InnoDB es transaccional, MyISAM no lo es. Los motores transaccionales tratan cada extracto como una transacción individual si no se controla explícitamente la transacción. Esto puede ser costoso

¿Probaste los Consejos de carga de datos a granel de las sugerencias de ajuste de rendimiento de InnoDB (especialmente la primera)?

Al importar datos a InnoDB , asegúrese de que MySQL no tenga habilitado el modo de confirmación automática, ya que requiere un registro de descarga en el disco para cada inserción. Para deshabilitar la confirmación automática durante la operación de importación, COMMIT sentencias SET autocommit y COMMIT :
SET autocommit=0; ... SQL import statements ... COMMIT;
Si utiliza la opción --opt , obtendrá archivos de volcado que son rápidos de importar en una tabla InnoDB , incluso sin envolverlos con las sentencias SET autocommit y COMMIT .
Si tiene restricciones UNIQUE en las claves secundarias, puede acelerar las importaciones de tablas desactivando temporalmente las comprobaciones de exclusividad durante la sesión de importación:
SET unique_checks=0; ... SQL import statements ... SET unique_checks=1;
Para las tablas grandes, esto ahorra una gran cantidad de E / S de disco porque InnoDB puede usar su búfer de inserción para escribir registros de índices secundarios en un lote. Asegúrese de que los datos no contengan claves duplicadas.
Si tiene restricciones FOREIGN KEY en sus tablas, puede acelerar las importaciones de tablas desactivando las claves externas durante la sesión de importación:
SET foreign_key_checks=0; ... SQL import statements ... SET foreign_key_checks=1;
Para las tablas grandes, esto puede ahorrar una gran cantidad de E / S de disco.

OMI, vale la pena leer todo el capítulo.

Descubrí que el disco duro es el cuello de botella: los discos antiguos son inútiles, SSD está bien, pero aún está lejos de ser perfecto. Importar a tmpfs y copiar los datos es mucho más rápido, detalles: https://dba.stackexchange.com/a/89367/56667

Tuve problemas para importar mucho y recomendar la respuesta aceptada. Descubrí que también puedes acelerar las cosas significativamente de la siguiente manera:

Dejar caer todos los índices (que no sean la clave principal), cargar los datos y luego volver a agregar los índices
Verificando tu innodb_log_file_size * innodb_log_files_in_group es suficiente para evitar escribir en el disco en una frecuencia sub-segunda

Con respecto al n. ° 2, los valores predeterminados de 5M * 2 no serán suficientes en un sistema moderno. Para más detalles, vea innodb_log_file_size y innodb_log_files_in_group