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