En el post InnoBD VS MyISAM parte 1 expuse algunas característica de estos dos motores de almacenamiento, y como en muchas otras paginas aun no sabemos que tanta diferencia puede llegar haber, en este post tendremos una idea mas clara sobre la diferencia entre estos dos motores, ya que compartiré una experiencia en la migración de una BD
Ahora bien siempre o hasta este punto hemos escuchado o leído que MyISAM es mucho mas rápido cuando se trata de SELECT’s.
- pero que tan rápido puede llegar hacer?
- Cuanto puede afectar mi aplicación
- como se ve afectado el rendimiento en mi BD?
En la mayoría de los casos sabremos esto hasta que nuestra aplicación haya alcanzado un numero significativamente alto para notar el rendimiento, y probablemente sea un poco tarde, pero bueno en este post les mostrare a que nos referimos cuando decimos que MyISAM es mucho mas rápido que InnoBD.
El ejemplo que usare es el de una tabla con 233,419.
El resultado
- Con InnoBD y sin indices la migración tardaba aproximadamente 15 – 16 horas
- Con MyISAM y con indices la migración se llevo en tan solo 13 – 18 minutos.
Como vemos el resultado simplemente es notable, a continuación el como se llego a este resultado.
El problema. migrar la información de unas facturas y el detalle de estas así como los productos a una nueva base de datos, para este ejemplo ya se habrán migrado las facturas y los productos, solo falta agregar el detalle de las facturas.
La migración se llevara acabo con PHP, ya que sera un script el que lea los registros de una base de datos y los almacene en otra tabla que esta en otra BD distinta.
La migración reorganizaba la información es por eso que se uso PHP.
Aparte del motor de almacenamiento también influye si nuestra tabla tiene indices para las consultas.
Tanto la tabla Productos como la de facturas solo tienen su lleve primaria, pero estas no se utilizan para la obtención de datos.
Prueba 1
Intente migrar la información en lotes de 500 registros, esto por el limite de tiempo de ejecución en PHP y ademas para ver la barrita algo mas animada :), el total de lotes son de 466, pero lo que mas me llama la atención es el tiempo promedio por esos 500, que es de 120 seg, es decir 2 minutos, por lo tanto
(2 x 466) = 932 minutos
932/60 = 15.5 Horas, casi 16 horas
La primera corrida deje trabajando la maquina por ese tiempo, pero eso no fue lo peor, si no que me equivoque en una consulta y todos esos 233,419 registros estaban inconsistentes por lo que debía volver a correr el script, lo cual me obligo a a buscar otra solución.
Por cada registro a migrar se ejecutaban 3 SELECT y 1 INSERT + el SELECT que obtenia el lote de los 500 registros, por lo tanto
3 * 500 = 1500 consultas
1 * 500 = 500 registros
1500/120 = 12.5 Consultas por segundo.
500 / 120 = 4 inserciones por segundo
Hay dos consultas que realizo a una tabla de productos (37,000 registros) y facturas (63,116 registros) ambas se encuentra en InnoDB y solo se ha especificado una llave primaria, estas consultas son indispensables para la migración.
Optimice algo de código PHP y me ahorre 1 consulta, sin entrar en detalle, podría representar la información de esa consulta en un arreglo de 3 registros.
Pero el rendimiento fue prácticamente nulo ya que solo logre bajar como 4 seg en promedio por lote.
Entonces el problema se encontraba en otro lado.
La tabla de productos es InnoBD y solo con una llave primaria, pero mi consulta la hacia en una columna que no estaba indexada.
Después de agregar algunos indices a la tabla facturas
PRUEBA 2
Agregando todos los indices necesarios (4) en la tabla facturas
PRUEBA 3
Agregando el indice necesario en productos (1)
Como vemos el simple uso de indices reduce por mucho el tiempo en ejecución hasta la mitad de tiempo, ahora veremos el resultado eligiendo el motor de almacenamiento mas indicado.
PRUEBA 4
Ahora como lo que quiero es hacer insert sin Atomicidad, entonces todas mis tablas seran MyISAM
PRUEBA 5
Ahora vamos por un poco mas, los lotes serán de 10,000 registros para terminar rápido la migración 🙂
Los resultados hablan por si solos, 47 seg pero el lote es de 10,000 registros 🙂
El rendimiento no solo esta en la programación y en el Hardware, si no también en nuestra BD y como la estructuremos, 18 minutos no se van comparar las 16 horas.
Si tu aplicación va mal recuerda no descartes tu BD.
En este caso la mejor elección fue MyISAM
¡Perfecto! Muy bien ambos artículos, muy bien documentados. Pero da la casualidad que no soy programador, sino más bien un simple mortal que necesitaba saber cuál de los dos motores utilizar.., me explico:
Resulta que trabajando en un blog de WordPress y al instalar un plugin para optimizar las tablas de la DB, me encuentro con una nota la cual advertía que tablas InnoBD no se optimizarían. Investigo y termino cambiando el motor a las tablas (a mano, son pocas) y de ahí la gran duda, ¿con cuál de los dos motores, MyISAM o InnoBD, trabajaría mejor el blog? El último dato comparativo de tu primer post me hace decidir por MyISAM, por lo de la memoria RAM y el almacenamiento en disco duro, ya que en estos momentos lo estoy trabajando en local y al ponerlo en línea en un hosting compartido, estos dos parámetros los veo ideal.
Bien, sin más… Necesito tu consejo, sugerencia u opinión para que a futuro fuese “un estándar” para ese tipo de proyectos.
Por tu ayuda… ¡Gracias!
Para el blog te recomiendo MyIsam, pues la lecturas serán mucho mas rápido, si quieres que tu blog baya mas rápido, debes tener en cuenta las características que te ofrece tu proveedor de hosting yo te recomiendo http://www.a2hosting.com/5170.html ,
Mira el rendimiento:
http://soyprogramador.liz.mx/despues-de-la-migracion-parte-5-de-5/
Saludos!!
Gracias Govaniso.
Creo haber entedido que en cuanto a velocidad del sitio lo ideal es MyISAM, pero además me dices que: “debes tener en cuenta las características que te ofrece tu proveedor de hosting”.
Es decir, si mi hosting SI permite InnoDB, entonces ¿cuál de los dos motores es el “ideal”? o ¿es indistinto uno u otro?, ¿qué hay en cuestiones de compatibilidad a futuro?
Sé que nadie tiene una bola de cristal para adivinar el futuro, pero por experiencia de tu parte ¿cuál seria, no lo ideal, sino mas bien, lo más recomendado?
Agradeciendo tu paciencia, espero tu orientación… Bernardino. ¡Saludos desde México!
En una base de datos puedes tener tanto tablas MyISAM como InnoDB, la incompatibilidad estaria unicamente en la llaves foraneas, pues no puedes tener una llave foranea de una tabla InnoDB a una MyISAM.
algunos proveedores no soportan alguna de las dos, ya son muy raro pero nunca falta.
Por cierto, puedes cambiar de un motor a otro en cual quier momento, no afecta la informacion (pero si las foraneas)