MyISAM versus InnoDB

Estoy trabajando en proyectos que implican muchas escrituras de bases de datos, diría ( 70% inserciones y 30% lecturas ). Esta relación también incluiría actualizaciones que considero que son una lectura y una escritura. Las lecturas pueden estar sucias (por ejemplo, no necesito información 100% precisa en el momento de la lectura).
La tarea en cuestión realizará más de 1 millón de transacciones de base de datos por hora.

He leído un montón de cosas en la web sobre las diferencias entre MyISAM e InnoDB, y MyISAM parece ser la elección obvia para la base de datos / tablas particulares que utilizaré para esta tarea. De lo que parece estar leyendo, InnoDB es bueno si se necesitan transacciones ya que el locking de nivel de fila es compatible.

¿Alguien tiene alguna experiencia con este tipo de carga (o superior)? ¿MyISAM es el camino a seguir?

He discutido brevemente esta pregunta en una tabla para que pueda concluir si va con InnoDB o MyISAM .

Aquí hay una pequeña descripción de qué motor de almacenamiento db debe usar en qué situación:

                                                  MyISAM InnoDB
 -------------------------------------------------- --------------
 Búsqueda obligatoria de texto completo Sí 5.6.4
 -------------------------------------------------- --------------
 Requerir transacciones Sí
 -------------------------------------------------- --------------
 Consultas de selección frecuentes Sí      
 -------------------------------------------------- --------------
 Inserción frecuente, actualización, eliminar Sí
 -------------------------------------------------- --------------
 Bloqueo de filas (procesamiento múltiple en una sola tabla) Sí
 -------------------------------------------------- --------------
 Diseño de base relacional Sí

Para resumir:

 Lectura frecuente, casi sin escritura => MyISAM
 Búsqueda de texto completo en MySQL <= 5.5 => MyISAM

En todas las demás circunstancias, InnoDB suele ser la mejor manera de hacerlo.

No soy un experto en bases de datos, y no hablo por experiencia. Sin embargo:

Las tablas MyISAM usan locking a nivel de tabla . En función de sus estimaciones de tráfico, tiene cerca de 200 escrituras por segundo. Con MyISAM, solo uno de estos podría estar en progreso en cualquier momento . Debe asegurarse de que su hardware pueda mantenerse al día con estas transacciones para evitar que se sature, es decir, una sola consulta puede tomar no más de 5 ms.

Eso me sugiere que necesitaría un motor de almacenamiento que admita el locking a nivel de fila, es decir, InnoDB.

Por otro lado, debería ser bastante trivial escribir unos pocos scripts simples para simular la carga con cada motor de almacenamiento, luego comparar los resultados.

La gente a menudo habla de rendimiento, lecturas y escrituras, claves externas, etc. pero hay otra característica imprescindible para un motor de almacenamiento en mi opinión: actualizaciones atómicas.

Prueba esto:

  1. Emita una ACTUALIZACIÓN contra su tabla MyISAM que demora 5 segundos.
  2. Mientras la ACTUALIZACIÓN está en progreso, digamos 2.5 segundos, presione Ctrl-C para interrumpirla.
  3. Observe los efectos sobre la mesa. ¿Cuántas filas se actualizaron? ¿Cuántos no fueron actualizados? ¿La tabla es incluso legible o se corrompió al presionar Ctrl-C?
  4. Pruebe el mismo experimento con UPDATE contra una tabla InnoDB, interrumpiendo la consulta en progreso.
  5. Observe la tabla InnoDB. Se actualizaron cero filas. InnoDB le ha asegurado que tiene actualizaciones atómicas, y si no se puede comprometer la actualización completa, revierte todo el cambio. Además, la tabla no está corrupta. Esto funciona incluso si usa killall -9 mysqld para simular un locking.

El rendimiento es deseable, por supuesto, pero no perder datos debería superar eso.

He trabajado en un sistema de alto volumen utilizando MySQL y he probado tanto MyISAM como InnoDB.

Descubrí que el locking a nivel de tabla en MyISAM ocasionaba graves problemas de rendimiento para nuestra carga de trabajo, que son similares a los suyos. Desafortunadamente, también descubrí que el rendimiento en InnoDB también era peor de lo que esperaba.

Al final, resolví el problema de contención fragmentando los datos de manera que las inserciones entraran en una tabla “activa” y seleccionaran que nunca se consultó la tabla activa.

Esto también permitió borrar (los datos fueron sensibles al tiempo y solo retuvimos el valor de X días) para que ocurran en tablas “caducas” que nuevamente no fueron afectadas por las consultas de selección. InnoDB parece tener un rendimiento bajo en las eliminaciones masivas, por lo que si planea purgar los datos, es posible que desee estructurarlos de tal forma que los datos antiguos estén en una tabla obsoleta que simplemente se puede descartar en lugar de ejecutar eliminaciones en él.

Por supuesto, no tengo idea de cuál es tu aplicación, pero espero que esto te dé una idea de algunos de los problemas con MyISAM e InnoDB.

Un poco tarde para el juego … pero aquí hay una publicación bastante completa que escribí hace unos meses , que detalla las principales diferencias entre MYISAM e InnoDB. Tome una taza de té (y tal vez una galleta) y disfrute.


La principal diferencia entre MyISAM e InnoDB está en la integridad referencial y las transacciones. También hay otras diferencias, como locking, reversiones y búsquedas de texto completo.

Integridad referencial

La integridad referencial asegura que las relaciones entre tablas permanezcan consistentes. Más específicamente, esto significa que cuando una tabla (por ejemplo, Listados) tiene una clave externa (por ejemplo, ID de producto) apuntando a una tabla diferente (por ejemplo, Productos), cuando las actualizaciones o eliminaciones se producen en la tabla señalada, estos cambios se transfieren a la vinculación mesa. En nuestro ejemplo, si se cambia el nombre de un producto, las claves externas de la tabla de enlace también se actualizarán; si se elimina un producto de la tabla “Productos”, también se eliminarán los listados que apunten a la entrada eliminada. Además, cualquier nueva lista debe tener esa clave externa apuntando a una entrada existente válida.

InnoDB es un DBMS relacional (RDBMS) y, por lo tanto, tiene integridad referencial, mientras que MyISAM no.

Transacciones y atomicidad

Los datos en una tabla se administran utilizando instrucciones de Lenguaje de Manipulación de Datos (DML), como SELECCIONAR, INSERTAR, ACTUALIZAR y ELIMINAR. Una transacción agrupa dos o más declaraciones DML juntas en una sola unidad de trabajo, por lo que se aplica toda la unidad, o nada de eso.

MyISAM no admite transacciones mientras que InnoDB sí.

Si se interrumpe una operación mientras se utiliza una tabla MyISAM, la operación se interrumpe de inmediato, y las filas (o incluso los datos dentro de cada fila) que se vean afectadas siguen afectadas, incluso si la operación no se completó.

Si se interrumpe una operación mientras se usa una tabla InnoDB, porque usa transacciones, que tiene atomicidad, cualquier transacción que no llegó a completarse no tendrá efecto, ya que no se realiza ninguna confirmación.

Bloqueo de tabla vs locking de filas

Cuando una consulta se ejecuta contra una tabla MyISAM, se bloqueará la tabla completa en la que está consultando. Esto significa que las consultas posteriores solo se ejecutarán después de que se finalice la actual. Si está leyendo una tabla grande y / o hay operaciones frecuentes de lectura y escritura, esto puede significar un gran retraso en las consultas.

Cuando una consulta se ejecuta en una tabla InnoDB, solo las filas que están involucradas están bloqueadas, el rest de la tabla permanece disponible para las operaciones CRUD. Esto significa que las consultas se pueden ejecutar simultáneamente en la misma tabla, siempre que no usen la misma fila.

Esta característica en InnoDB se conoce como concurrencia. Por muy bueno que sea la concurrencia, existe una desventaja importante que se aplica a un rango selecto de tablas, en el sentido de que hay una sobrecarga al cambiar entre hilos del kernel, y debe establecer un límite en los hilos del kernel para evitar que el servidor se detenga. .

Transacciones y retrocesos

Cuando ejecuta una operación en MyISAM, los cambios se establecen; en InnoDB, esos cambios pueden revertirse. Los comandos más comunes utilizados para controlar las transacciones son COMMIT, ROLLBACK y SAVEPOINT. 1. COMPROMISO: puede escribir múltiples operaciones DML, pero los cambios solo se guardarán cuando se realice un COMPROMISO 2. ROLLBACK: puede descartar cualquier operación que aún no se haya cometido aún 3. SAVEPOINT – establece un punto en la lista de operaciones a las cuales una operación ROLLBACK puede retroceder a

Confiabilidad

MyISAM no ofrece integridad de datos: las fallas de hardware, las paradas sucias y las operaciones canceladas pueden hacer que los datos se dañen. Esto requeriría una reparación completa o reconstrucciones de los índices y tablas.

InnoDB, por otro lado, utiliza un registro transaccional, un búfer de doble escritura y sums de verificación y validación automáticas para evitar la corrupción. Antes de que InnoDB realice cambios, registra los datos antes de las transacciones en un archivo de espacio de tablas del sistema llamado ibdata1. Si se produce un locking, InnoDB se autorecupe a través de la reproducción de esos registros.

Indexación FULLTEXT

InnoDB no admite indexación FULLTEXT hasta MySQL versión 5.6.4. A partir de la redacción de esta publicación, la versión de MySQL de muchos proveedores de alojamiento compartido sigue estando por debajo de 5.6.4, lo que significa que la indexación de FULLTEXT no es compatible con las tablas de InnoDB.

Sin embargo, esta no es una razón válida para usar MyISAM. Lo mejor es cambiar a un proveedor de hosting que admita versiones actualizadas de MySQL. No es que una tabla MyISAM que usa indexación FULLTEXT no se pueda convertir a una tabla InnoDB.

Conclusión

En conclusión, InnoDB debería ser su motor de almacenamiento predeterminado. Elija MyISAM u otros tipos de datos cuando atiendan una necesidad específica.

Para una carga con más escrituras y lecturas, se beneficiará con InnoDB. Debido a que InnoDB proporciona locking de filas en lugar de locking de tablas, los SELECT pueden ser simultáneos, no solo entre sí, sino también con muchos INSERT . Sin embargo, a menos que tenga la intención de usar transacciones de SQL, configure InnoDB commit flush en 2 ( innodb_flush_log_at_trx_commit ). Esto le devuelve una gran cantidad de rendimiento en bruto que de otro modo perdería al mover tablas de MyISAM a InnoDB.

Además, considere agregar replicación. Esto le proporciona cierta escala de lectura y, dado que afirmó que sus lecturas no tienen que estar actualizadas, puede dejar que la replicación se retrase un poco. Solo asegúrate de que pueda ponerse al día con cualquier cosa que no sea el tráfico más pesado, o siempre estará detrás y nunca se pondrá al día. Sin embargo, si va por este camino, le recomiendo que aísle la lectura de los esclavos y la administración de retraso de replicación en su manejador de base de datos. Es mucho más simple si el código de la aplicación no lo sabe.

Finalmente, tenga en cuenta las diferentes cargas de tabla. No tendrá la misma proporción de lectura / escritura en todas las tablas. Algunas tablas más pequeñas con casi el 100% de lecturas podrían permitirse permanecer MyISAM. Del mismo modo, si tiene algunas tablas que están cerca del 100% de escritura, puede beneficiarse de INSERT DELAYED , pero eso solo se admite en MyISAM (la cláusula DELAYED se ignora para una tabla InnoDB).

Pero punto de referencia para estar seguro.

Para agregar a la amplia selección de respuestas aquí que cubre las diferencias mecánicas entre los dos motores, presento un estudio empírico de comparación de velocidad.

En términos de velocidad pura, MyISAM no siempre es más rápido que InnoDB pero, en mi experiencia, tiende a ser más rápido para los entornos de trabajo PURE LEED en un factor de aproximadamente 2.0-2.5 veces. Claramente, esto no es apropiado para todos los entornos, como otros han escrito, MyISAM carece de cosas como transacciones y claves externas.

He hecho un poco de benchmarking a continuación: he usado python para bucles y la biblioteca timeit para comparaciones de tiempo. Para mayor interés, también incluí el motor de memoria, esto ofrece el mejor rendimiento en general, aunque solo es adecuado para tablas más pequeñas (se encuentra continuamente The table 'tbl' is full cuando se excede el límite de memoria MySQL). Los cuatro tipos de seleccionar que miro son:

  1. SELECTs vainilla
  2. conteos
  3. SELECTs condicional
  4. sub-selecciones indexadas y no indexadas

En primer lugar, creé tres tablas con el siguiente SQL

 CREATE TABLE data_interrogation.test_table_myisam ( index_col BIGINT NOT NULL AUTO_INCREMENT, value1 DOUBLE, value2 DOUBLE, value3 DOUBLE, value4 DOUBLE, PRIMARY KEY (index_col) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 

con ‘MyISAM’ sustituido por ‘InnoDB’ y ‘memoria’ en la segunda y tercera tablas.

1) Selecciones de vainilla

Consulta: SELECT * FROM tbl WHERE index_col = xx

Resultado: dibujar

Comparación de selecciones de vainilla por diferentes motores de base de datos

La velocidad de estos es ampliamente similar y, como se espera, es lineal en el número de columnas que se seleccionarán. InnoDB parece ligeramente más rápido que MyISAM, pero esto es realmente marginal.

Código:

 import timeit import MySQLdb import MySQLdb.cursors import random from random import randint db = MySQLdb.connect(host="...", user="...", passwd="...", db="...", cursorclass=MySQLdb.cursors.DictCursor) cur = db.cursor() lengthOfTable = 100000 # Fill up the tables with random data for x in xrange(lengthOfTable): rand1 = random.random() rand2 = random.random() rand3 = random.random() rand4 = random.random() insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" cur.execute(insertString) cur.execute(insertString2) cur.execute(insertString3) db.commit() # Define a function to pull a certain number of records from these tables def selectRandomRecords(testTable,numberOfRecords): for x in xrange(numberOfRecords): rand1 = randint(0,lengthOfTable) selectString = "SELECT * FROM " + testTable + " WHERE index_col = " + str(rand1) cur.execute(selectString) setupString = "from __main__ import selectRandomRecords" # Test time taken using timeit myisam_times = [] innodb_times = [] memory_times = [] for theLength in [3,10,30,100,300,1000,3000,10000]: innodb_times.append( timeit.timeit('selectRandomRecords("test_table_innodb",' + str(theLength) + ')', number=100, setup=setupString) ) myisam_times.append( timeit.timeit('selectRandomRecords("test_table_myisam",' + str(theLength) + ')', number=100, setup=setupString) ) memory_times.append( timeit.timeit('selectRandomRecords("test_table_memory",' + str(theLength) + ')', number=100, setup=setupString) ) 

2) Cuenta

Consulta: SELECT count(*) FROM tbl

Resultado: MyISAM gana

Comparación de recuentos por diferentes motores de base de datos

Éste demuestra una gran diferencia entre MyISAM e InnoDB: MyISAM (y la memoria) realiza un seguimiento del número de registros en la tabla, por lo que esta transacción es rápida y O (1). La cantidad de tiempo requerida para contar InnoDB aumenta de forma super-lineal con el tamaño de la tabla en el rango que investigué. Sospecho que muchas de las aceleraciones de consultas MyISAM que se observan en la práctica se deben a efectos similares.

Código:

 myisam_times = [] innodb_times = [] memory_times = [] # Define a function to count the records def countRecords(testTable): selectString = "SELECT count(*) FROM " + testTable cur.execute(selectString) setupString = "from __main__ import countRecords" # Truncate the tables and re-fill with a set amount of data for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]: truncateString = "TRUNCATE test_table_innodb" truncateString2 = "TRUNCATE test_table_myisam" truncateString3 = "TRUNCATE test_table_memory" cur.execute(truncateString) cur.execute(truncateString2) cur.execute(truncateString3) for x in xrange(theLength): rand1 = random.random() rand2 = random.random() rand3 = random.random() rand4 = random.random() insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" cur.execute(insertString) cur.execute(insertString2) cur.execute(insertString3) db.commit() # Count and time the query innodb_times.append( timeit.timeit('countRecords("test_table_innodb")', number=100, setup=setupString) ) myisam_times.append( timeit.timeit('countRecords("test_table_myisam")', number=100, setup=setupString) ) memory_times.append( timeit.timeit('countRecords("test_table_memory")', number=100, setup=setupString) ) 

3) Selecciones condicionales

Consulta: SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

Resultado: MyISAM gana

Comparación de selecciones condicionales por diferentes motores de base de datos

Aquí, MyISAM y la memoria realizan aproximadamente lo mismo, y superan a InnoDB en aproximadamente un 50% para tablas más grandes. Este es el tipo de consulta para la cual los beneficios de MyISAM parecen estar maximizados.

Código:

 myisam_times = [] innodb_times = [] memory_times = [] # Define a function to perform conditional selects def conditionalSelect(testTable): selectString = "SELECT * FROM " + testTable + " WHERE value1 < 0.5 AND value2 < 0.5 AND value3 < 0.5 AND value4 < 0.5" cur.execute(selectString) setupString = "from __main__ import conditionalSelect" # Truncate the tables and re-fill with a set amount of data for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]: truncateString = "TRUNCATE test_table_innodb" truncateString2 = "TRUNCATE test_table_myisam" truncateString3 = "TRUNCATE test_table_memory" cur.execute(truncateString) cur.execute(truncateString2) cur.execute(truncateString3) for x in xrange(theLength): rand1 = random.random() rand2 = random.random() rand3 = random.random() rand4 = random.random() insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" cur.execute(insertString) cur.execute(insertString2) cur.execute(insertString3) db.commit() # Count and time the query innodb_times.append( timeit.timeit('conditionalSelect("test_table_innodb")', number=100, setup=setupString) ) myisam_times.append( timeit.timeit('conditionalSelect("test_table_myisam")', number=100, setup=setupString) ) memory_times.append( timeit.timeit('conditionalSelect("test_table_memory")', number=100, setup=setupString) ) 

4) Sub-selects

Resultado: InnoDB gana

Para esta consulta, creé un conjunto adicional de tablas para la selección secundaria. Cada uno es simplemente dos columnas de BIGINT, una con un índice de clave principal y otra sin ningún índice. Debido al gran tamaño de la mesa, no probé el motor de memoria. El comando de creación de tabla SQL era

 CREATE TABLE subselect_myisam ( index_col bigint NOT NULL, non_index_col bigint, PRIMARY KEY (index_col) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

donde una vez más, 'MyISAM' se sustituye por 'InnoDB' en la segunda tabla.

En esta consulta, dejo el tamaño de la tabla de selección en 1000000 y, en su lugar, varío el tamaño de las columnas seleccionadas.

Comparación de sub selecciones por diferentes motores de base de datos

Aquí el InnoDB gana fácilmente. Después de llegar a una tabla de tamaño razonable, ambos motores se escalan linealmente con el tamaño de la selección secundaria. El índice acelera el comando MyISAM pero curiosamente tiene poco efecto en la velocidad de InnoDB. subSelect.png

Código:

 myisam_times = [] innodb_times = [] myisam_times_2 = [] innodb_times_2 = [] def subSelectRecordsIndexed(testTable,testSubSelect): selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT index_col FROM " + testSubSelect + " )" cur.execute(selectString) setupString = "from __main__ import subSelectRecordsIndexed" def subSelectRecordsNotIndexed(testTable,testSubSelect): selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT non_index_col FROM " + testSubSelect + " )" cur.execute(selectString) setupString2 = "from __main__ import subSelectRecordsNotIndexed" # Truncate the old tables, and re-fill with 1000000 records truncateString = "TRUNCATE test_table_innodb" truncateString2 = "TRUNCATE test_table_myisam" cur.execute(truncateString) cur.execute(truncateString2) lengthOfTable = 1000000 # Fill up the tables with random data for x in xrange(lengthOfTable): rand1 = random.random() rand2 = random.random() rand3 = random.random() rand4 = random.random() insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" cur.execute(insertString) cur.execute(insertString2) for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]: truncateString = "TRUNCATE subselect_innodb" truncateString2 = "TRUNCATE subselect_myisam" cur.execute(truncateString) cur.execute(truncateString2) # For each length, empty the table and re-fill it with random data rand_sample = sorted(random.sample(xrange(lengthOfTable), theLength)) rand_sample_2 = random.sample(xrange(lengthOfTable), theLength) for (the_value_1,the_value_2) in zip(rand_sample,rand_sample_2): insertString = "INSERT INTO subselect_innodb (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")" insertString2 = "INSERT INTO subselect_myisam (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")" cur.execute(insertString) cur.execute(insertString2) db.commit() # Finally, time the queries innodb_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString) ) myisam_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString) ) innodb_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString2) ) myisam_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString2) ) 

Creo que el mensaje principal de todo esto es que si realmente le preocupa la velocidad, necesita comparar las consultas que está haciendo en lugar de hacer suposiciones sobre qué motor será más adecuado.

Un poco fuera de tema, pero para fines de documentación e integridad, me gustaría agregar lo siguiente.

En general, usar InnoDB dará como resultado una aplicación mucho MENOS compleja, probablemente también más libre de errores. Debido a que puede poner toda la integridad referencial (restricciones de clave externa) en el modelo de datos, no necesita ni cerca del código de aplicación que necesite con MyISAM.

Cada vez que inserte, elimine o reemplace un registro, TENDRÁ que verificar y mantener las relaciones. Por ejemplo, si elimina un elemento primario, también se deberían eliminar todos los elementos secundarios. Por ejemplo, incluso en un sistema de blog simple, si elimina un registro de publicación de blog, tendrá que eliminar los registros de comentarios, los “me gusta”, etc. En InnoDB esto lo hace automáticamente el motor de la base de datos (si especificó las restricciones en el modelo) ) y no requiere código de aplicación. En MyISAM esto tendrá que estar codificado en la aplicación, lo cual es muy difícil en los servidores web. Los servidores web son por naturaleza muy concurrentes / paralelos y debido a que estas acciones deben ser atómicas y MyISAM no admite transacciones reales, el uso de MyISAM para servidores web es arriesgado / propenso a errores.

También en la mayoría de los casos, InnoDB funcionará mucho mejor, por múltiples motivos, uno de ellos es capaz de utilizar el locking de nivel de registro en lugar de locking a nivel de tabla. No solo en una situación donde las escrituras son más frecuentes que las lecturas, también en situaciones con uniones complejas en grandes conjuntos de datos. Notamos un aumento de rendimiento de 3 veces solo usando tablas InnoDB sobre tablas MyISAM para uniones muy grandes (tomando varios minutos).

Diría que, en general, InnoDB (utilizando un modelo de datos 3NF completo con integridad referencial) debería ser la opción predeterminada al usar MySQL. MyISAM solo debe usarse en casos muy específicos. Lo más probable es que rinda menos, como resultado una aplicación más grande y con errores.

Habiendo dicho ésto. Datamodelling es un arte que rara vez se encuentra entre los diseñadores / progtwigdores web. Sin ofender, pero explica que MyISAM sea usado tanto.

InnoDB ofrece:

 ACID transactions row-level locking foreign key constraints automatic crash recovery table compression (read/write) spatial data types (no spatial indexes) 

En InnoDB, todos los datos en una fila a excepción de TEXT y BLOB pueden ocupar 8,000 bytes como máximo. No hay indexación de texto completo disponible para InnoDB. En InnoDB, el COUNT (*) s (cuando WHERE, GROUP BY o JOIN no se usa) se ejecuta más despacio que en MyISAM porque el recuento de filas no se almacena internamente. InnoDB almacena datos e índices en un archivo. InnoDB usa un grupo de búferes para almacenar tanto los datos como los índices.

MyISAM ofrece:

 fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used) full text indexing smaller disk footprint very high table compression (read only) spatial data types and indexes (R-tree) 

MyISAM tiene locking a nivel de tabla, pero no locking a nivel de fila. Sin transacciones. Sin recuperación automática de fallos, pero sí ofrece la funcionalidad de la tabla de reparación. Sin restricciones de clave externa. Las tablas MyISAM generalmente son de tamaño más compacto en el disco en comparación con las tablas InnoDB. Las tablas MyISAM pueden reducirse aún más en tamaño al comprimir con myisampack si es necesario, pero se vuelven de solo lectura. MyISAM almacena índices en un archivo y datos en otro. MyISAM utiliza búferes de claves para almacenar índices en el caché y deja la administración del almacenamiento en caché de datos en el sistema operativo.

En general, recomendaría InnoDB para la mayoría de los propósitos y MyISAM solo para usos especializados. InnoDB ahora es el motor predeterminado en las nuevas versiones de MySQL.

Si usa MyISAM, no realizará ninguna transacción por hora, a menos que considere que cada statement de DML sea una transacción (que en cualquier caso, no será duradera o atómica en caso de una falla).

Por lo tanto, creo que debes usar InnoDB.

300 transacciones por segundo suena bastante. Si realmente necesita que estas transacciones sean duraderas en caso de fallo de alimentación, asegúrese de que su subsistema de E / S pueda gestionar fácilmente tantas escrituras por segundo. Necesitará al menos un controlador RAID con caché respaldado por batería.

Si puede tomar un pequeño golpe de durabilidad, puede usar InnoDB con innodb_flush_log_at_trx_commit establecido en 0 o 2 (ver documentos para más detalles), puede mejorar el rendimiento.

Hay una serie de parches que pueden boost la concurrencia de Google y otros, estos pueden ser de interés si aún no puede obtener el rendimiento suficiente sin ellos.

La pregunta y la mayoría de las respuestas están desactualizadas .

Yes, it is an old wives’ tale that MyISAM is faster than InnoDB. notice the Question’s date: 2008; it is now almost a decade later. InnoDB has made significant performance strides since then.

The dtwigtic graph was for the one case where MyISAM wins: COUNT(*) without a WHERE clause. But is that really what you spend your time doing?

If you run concurrency test, InnoDB is very likely to win, even against MEMORY .

If you do any writes while benchmarking SELECTs , MyISAM and MEMORY are likely to lose because of table-level locking.

In fact, Oracle is so sure that InnoDB is better that they have all but removed MyISAM from 8.0.

The Question was written early in the days of 5.1. Since then, these major versions were marked “General Availability”:

  • 2010: 5.5 (.8 in Dec.)
  • 2013: 5.6 (.10 in Feb.)
  • 2015: 5.7 (.9 in Oct.)
  • 2018: 8.0 (.11 in Apr.)

Bottom line: Don’t use MyISAM

Please note that my formal education and experience is with Oracle, while my work with MySQL has been entirely personal and on my own time, so if I say things that are true for Oracle but are not true for MySQL, I apologize. While the two systems share a lot, the relational theory/algebra is the same, and relational databases are still relational databases, there are still plenty of differences!!

I particularly like (as well as row-level locking) that InnoDB is transaction-based, meaning that you may be updating/inserting/creating/altering/dropping/etc several times for one “operation” of your web application. The problem that arises is that if only some of those changes/operations end up being committed, but others do not, you will most times (depending on the specific design of the database) end up with a database with conflicting data/structure.

Note: With Oracle, create/alter/drop statements are called “DDL” (Data Definition) statements, and implicitly trigger a commit. Insert/update/delete statements, called “DML” (Data Manipulation), are not committed automatically, but only when a DDL, commit, or exit/quit is performed (or if you set your session to “auto-commit”, or if your client auto-commits). It’s imperative to be aware of that when working with Oracle, but I am not sure how MySQL handles the two types of statements. Because of this, I want to make it clear that I’m not sure of this when it comes to MySQL; only with Oracle.

An example of when transaction-based engines excel:

Let’s say that I or you are on a web-page to sign up to attend a free event, and one of the main purposes of the system is to only allow up to 100 people to sign up, since that is the limit of the seating for the event. Once 100 sign-ups are reached, the system would disable further signups, at least until others cancel.

In this case, there may be a table for guests (name, phone, email, etc.), and a second table which tracks the number of guests that have signed up. We thus have two operations for one “transaction”. Now suppose that after the guest info is added to the GUESTS table, there is a connection loss, or an error with the same impact. The GUESTS table was updated (inserted into), but the connection was lost before the “available seats” could be updated.

Now we have a guest added to the guest table, but the number of available seats is now incorrect (for example, value is 85 when it’s actually 84).

Of course there are many ways to handle this, such as tracking available seats with “100 minus number of rows in guests table,” or some code that checks that the info is consistent, etc…. But with a transaction-based database engine such as InnoDB, either ALL of the operations are committed, or NONE of them are. This can be helpful in many cases, but like I said, it’s not the ONLY way to be safe, no (a nice way, however, handled by the database, not the programmer/script-writer).

That’s all “transaction-based” essentially means in this context, unless I’m missing something — that either the whole transaction succeeds as it should, or nothing is changed, since making only partial changes could make a minor to SEVERE mess of the database, perhaps even corrupting it…

But I’ll say it one more time, it’s not the only way to avoid making a mess. But it is one of the methods that the engine itself handles, leaving you to code/script with only needing to worry about “was the transaction successful or not, and what do I do if not (such as retry),” instead of manually writing code to check it “manually” from outside of the database, and doing a lot more work for such events.

Lastly, a note about table-locking vs row-locking:

DISCLAIMER: I may be wrong in all that follows in regard to MySQL, and the hypothetical/example situations are things to look into, but I may be wrong in what exactly is possible to cause corruption with MySQL. The examples are however very real in general programming, even if MySQL has more mechanisms to avoid such things…

Anyway, I am fairly confident in agreeing with those who have argued that how many connections are allowed at a time does not work around a locked table. In fact, multiple connections are the entire point of locking a table!! So that other processes/users/apps are not able to corrupt the database by making changes at the same time.

How would two or more connections working on the same row make a REALLY BAD DAY for you?? Suppose there are two processes both want/need to update the same value in the same row, let’s say because the row is a record of a bus tour, and each of the two processes simultaneously want to update the “riders” or “available_seats” field as “the current value plus 1.”

Let’s do this hypothetically, step by step:

  1. Process one reads the current value, let’s say it’s empty, thus ‘0’ so far.
  2. Process two reads the current value as well, which is still 0.
  3. Process one writes (current + 1) which is 1.
  4. Process two should be writing 2, but since it read the current value before process one write the new value, it too writes 1 to the table.

I’m not certain that two connections could intermingle like that, both reading before the first one writes… But if not, then I would still see a problem with:

  1. Process one reads the current value, which is 0.
  2. Process one writes (current + 1), which is 1.
  3. Process two reads the current value now. But while process one DID write (update), it has not committed the data, thus only that same process can read the new value that it updated, while all others see the older value, until there is a commit.

Also, at least with Oracle databases, there are isolation levels, which I will not waste our time trying to paraphrase. Here is a good article on that subject, and each isolation level having it’s pros and cons, which would go along with how important transaction-based engines may be in a database…

Lastly, there may likely be different safeguards in place within MyISAM, instead of foreign-keys and transaction-based interaction. Well, for one, there is the fact that an entire table is locked, which makes it less likely that transactions/FKs are needed .

And alas, if you are aware of these concurrency issues, yes you can play it less safe and just write your applications, set up your systems so that such errors are not possible (your code is then responsible, rather than the database itself). However, in my opinion, I would say that it is always best to use as many safeguards as possible, programming defensively, and always being aware that human error is impossible to completely avoid. It happens to everyone, and anyone who says they are immune to it must be lying, or hasn’t done more than write a “Hello World” application/script. 😉

I hope that SOME of that is helpful to some one, and even more-so, I hope that I have not just now been a culprit of assumptions and being a human in error!! My apologies if so, but the examples are good to think about, research the risk of, and so on, even if they are not potential in this specific context.

Feel free to correct me, edit this “answer,” even vote it down. Just please try to improve, rather than correcting a bad assumption of mine with another. 😉

This is my first response, so please forgive the length due to all the disclaimers, etc… I just don’t want to sound arrogant when I am not absolutely certain!

MYISAM:

  1. MYISAM supports Table-level Locking

  2. MyISAM designed for need of speed

  3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
  4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
  5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.

INNODB:

  1. InnoDB supports Row-level Locking
  2. InnoDB designed for maximum performance when processing high volume of data
  3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
  4. InnoDB stores its tables and indexes in a tablespace
  5. InnoDB supports transaction. You can commit and rollback with InnoDB

I think this is an excellent article on explaining the differences and when you should use one over the other: http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

Also check out some drop-in replacements for MySQL itself:

MariaDB

http://mariadb.org/

MariaDB is a database server that offers drop-in replacement functionality for MySQL. MariaDB is built by some of the original authors of MySQL, with assistance from the broader community of Free and open source software developers. In addition to the core functionality of MySQL, MariaDB offers a rich set of feature enhancements including alternate storage engines, server optimizations, and patches.

Percona Server

https://launchpad.net/percona-server

An enhanced drop-in replacement for MySQL, with better performance, improved diagnostics, and added features.

MyISAM

The MyISAM engine is the default engine in most MySQL installations and is a derivative of the original ISAM engine type supported in the early versions of the MySQL system. The engine provides the best combination of performance and functionality, although it lacks transaction capabilities (use the InnoDB or BDB engines) and uses table-level locking .

FlashMAX and FlashMAX Connect: Leading the Flash Platform Transformation Download Now Unless you need transactions, there are few databases and applications that cannot effectively be stored using the MyISAM engine. However, very high-performance applications where there are large numbers of data inserts/updates compared to the number of reads can cause performance proboelsm for the MyISAM engine. It was originally designed with the idea that more than 90% of the database access to a MyISAM table would be reads, rather than writes.

With table-level locking, a database with a high number of row inserts or updates becomes a performance bottleneck as the table is locked while data is added. Luckily this limitation also works well within the restrictions of a non-transaction database.

MyISAM Summary

Name -MyISAM

Introduced -v3.23

Default install -Yes

Data limitations -None

Index limitations -64 indexes per table (32 pre 4.1.2); Max 16 columns per index

Transaction support -No

Locking level -Table


InnoDB

The InnoDB Engine is provided by Innobase Oy and supports all of the database functionality (and more) of MyISAM engine and also adds full transaction capabilities (with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance) and row level locking of data.

The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk. This enables very fast recovery, and works even on very large data sets. By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.

As with MyISAM , there are few data types that cannot effectively be stored in an InnoDB database. In fact, there are no significant reasons why you shouldn’t always use an InnoDB database. The management overhead for InnoDB is slightly more onerous, and getting the optimization right for the sizes of in-memory and on disk caches and database files can be complex at first. However, it also means that you get more flexibility over these values and once set, the performance benefits can easily outweigh the initial time spent. Alternatively, you can let MySQL manage this automatically for you.

If you are willing (and able) to configure the InnoDB settings for your server, then I would recommend that you spend the time to optimize your server configuration and then use the InnoDB engine as the default.

InnoDB Summary

Name -InnoDB

Introduced -v3.23 (source only), v4.0 (source and binary)

Default install -No

Data limitations -None

Index limitations -None

Transaction support -Yes (ACID compliant)

Locking level -Row

In my experience, MyISAM was a better choice as long as you don’t do DELETEs, UPDATEs, a whole lot of single INSERT, transactions, and full-text indexing. BTW, CHECK TABLE is horrible. As the table gets older in terms of the number of rows, you don’t know when it will end.

I’ve figure out that even though Myisam has locking contention, it’s still faster than InnoDb in most scenarios because of the rapid lock acquisition scheme it uses. I’ve tried several times Innodb and always fall back to MyIsam for one reason or the other. Also InnoDB can be very CPU intensive in huge write loads.

Every application has it’s own performance profile for using a database, and chances are it will change over time.

The best thing you can do is to test your options. Switching between MyISAM and InnoDB is trivial, so load some test data and fire jmeter against your site and see what happens.

I tried to run insertion of random data into MyISAM and InnoDB tables. The result was quite shocking. MyISAM needed a few seconds less for inserting 1 million rows than InnoDB for just 10 thousand!

myisam is a NOGO for that type of workload (high concurrency writes), i dont have that much experience with innodb (tested it 3 times and found in each case that the performance sucked, but it’s been a while since the last test) if you’re not forced to run mysql, consider giving postgres a try as it handles concurrent writes MUCH better

I know this won’t be popular but here goes:

myISAM lacks support for database essentials like transactions and referential integrity which often results in glitchy / buggy applications. You cannot not learn proper database design fundamentals if they are not even supported by your db engine.

Not using referential integrity or transactions in the database world is like not using object oriented programming in the software world.

InnoDB exists now, use that instead! Even MySQL developers have finally conceded to change this to the default engine in newer versions, despite myISAM being the original engine that was the default in all legacy systems.

No it does not matter if you are reading or writing or what performance considerations you have, using myISAM can result in a variety of problems, such as this one I just ran into: I was performing a database sync and at the same time someone else accessed an application that accessed a table set to myISAM. Due to the lack of transaction support and the generally poor reliability of this engine, this crashed the entire database and I had to manually restart mysql!

Over the past 15 years of development I have used many databases and engines. myISAM crashed on me about a dozen times during this period, other databases, only once! And that was a microsoft SQL database where some developer wrote faulty CLR code (common language runtime – basically C# code that executes inside the database) by the way, it was not the database engine’s fault exactly.

I agree with the other answers here that say that quality high-availability, high-performance applications should not use myISAM as it will not work, it is not robust or stable enough to result in a frustration-free experience. See Bill Karwin’s answer for more details.

PS Gotta love it when myISAM fanboys downvote but can’t tell you which part of this answer is incorrect.

For that ratio of read/writes I would guess InnoDB will perform better. Since you are fine with dirty reads, you might (if you afford) replicate to a slave and let all your reads go to the slave. Also, consider inserting in bulk, rather than one record at a time.

Almost every time I start a new project I Google this same question to see if I come up with any new answers.

It eventually boils down to – I take the latest version of MySQL and run tests.

I have tables where I want to do key/value lookups… and that’s all. I need to get the value (0-512 bytes) for a hash key. There is not a lot of transactions on this DB. The table gets updates occasionally (in it’s entirety), but 0 transactions.

So we’re not talking about a complex system here, we are talking about a simple lookup,.. and how (other than making the table RAM resident) we can optimize performance.

I also do tests on other databases (ie NoSQL) to see if there is anywhere I can get an advantage. The biggest advantage I have found is in key mapping but as far as the lookup goes, MyISAM is currently topping them all.

Albeit, I wouldn’t perform financial transactions with MyISAM tables but for simple lookups, you should test it out.. typically 2x to 5x the queries/sec.

Test it, I welcome debate.

If it is 70% inserts and 30% reads then it is more like on the InnoDB side.

In short, InnoDB is good if you are working on something that needs a reliable database that can handles a lot of INSERT and UPDATE instructions.

and, MyISAM is good if you needs a database that will mostly be taking a lot of read (SELECT) instructions rather than write (INSERT and UPDATES), considering its drawback on the table-lock thing.

you may want to check out;
Pros and Cons of InnoDB
Pros and Cons of MyISAM

bottomline: if you are working offline with selects on large chunks of data, MyISAM will probably give you better (much better) speeds.

there are some situations when MyISAM is infinitely more efficient than InnoDB: when manipulating large data dumps offline (because of table lock).

example: I was converting a csv file (15M records) from NOAA which uses VARCHAR fields as keys. InnoDB was taking forever, even with large chunks of memory available.

this an example of the csv (first and third fields are keys).

 USC00178998,20130101,TMAX,-22,,,7,0700 USC00178998,20130101,TMIN,-117,,,7,0700 USC00178998,20130101,TOBS,-28,,,7,0700 USC00178998,20130101,PRCP,0,T,,7,0700 USC00178998,20130101,SNOW,0,T,,7, 

since what i need to do is run a batch offline update of observed weather phenomena, i use MyISAM table for receiving data and run JOINS on the keys so that i can clean the incoming file and replace VARCHAR fields with INT keys (which are related to external tables where the original VARCHAR values are stored).