Durante esta semana tuve que trabajar en otro problema de optimización de un proceso basado en "datos".
Cuando se migran servidores, se actualizan sistemas operativos, se instalan nuevos motores de bases de datos, siempre hay cambios y a veces no son los esperados. Es decir, cambiar para peor.
En este caso, el problema es con un servidor MySQL y distintos motores de datos (InnoDB y MyISAM), aunque todo este proceso puede ser aplicable a otros motores, este post es el detalle de lo que detecté, verifiqué y algunas soluciones.
Ante un problema, recomiendo auditar todas las partes, virtualización, S.O., motor de base de datos, estructuras internas, configuraciones, redes, etc. Cada uno de los componentes debe ser verificado para aislarlo del problema central, como en una gran función completa multivariable, cada una de las variables debe ser una constante para que al final obtenamos la ecuación simplificada.
Debemos definir lo que queremos verificar, planificar los cambios uno a uno, medir científicamente y pensar todas las opciones posibles, usar pensamiento lateral, leer mucho en internet, comparar con otras situaciones, preparar el lote de pruebas y documentar todo.
Todo debe acabar siendo un gráfico simple o unas pocas líneas explicativas con las conclusiones. También debemos incluir alternativas de mejoras y/o otros caminos a seguir. El proceso de análisis debe ser "reproducible", proveer "datos numéricos" y usar una metodología científica.
Entorno Inicial y problema reportado.
* Servidor Viejo con Linux 32 bits, 4 gb ram, Percona Server 5.5.29, dos tablas MyISAM con un join que guarda datos en una nueva tabla MyISAM.
* Servidor Nuevo con Linux 64 bits, 4 gb ram, MariaDB 10.2, 2 tablas InnoDB con un join y el destino es una tabla InnoDB.
* Los servidores son virtuales y están ubicados en un datacenter.
* El proceso es leer dos tablas de una base de datos y guardar registros en otra tabla de otra base de datos. (Todas en el mismo servidor MySQL)
* Los Usuarios reportan que ese proceso demora "mucho mas" en el servidor nuevo que en el viejo.
Objetivo del trabajo.
* Identificar en que se consume el tiempo del proceso, tratar de reducir los tiempos y entender porque los cambios fueron a peor.
* Definir un proceso de medición para procesos OLTP, el servidor optimizado debe tener buenos tiempos en procesos Batch y OLTP.
* Presentar los resultados fácilmente y ver alternativas.
Procesos de comparación.
* Creamos un proceso simple, paso a paso para las mediciones:
+ Reiniciar el Motor MariaDB para comenzar con un entorno limpio.
+ Crear tablas con distintas opciones.
+ Realizar el proceso de carga (leer 2 tablas y grabar 1)
+ Tomar tiempo del proceso.
+ Cambiar opciones de configuración del motor.
+ Comenzar nuevamente desde el principio.
* Usaremos 1M de registros en una de las tablas de lectura. 1.4 gb. 11 campos de lectura.
* También 300.000 registros para la segunda tabla de lectura. 400 mb. 1 campos de lectura.
* La tabla generada será de 904.000 registros con 12 campos almacenados. (1 de la 2da tabla y 11 de la primera tabla)
Opciones evaluadas :
+ Usar una tabla InnoDB con todos los índices existentes del modelo real.
+ Usar una tabla InnoDB sin índices.
+ Usar una tabla MyISAM con todos los índices.
+ Usar una tabla MyISAM sin índices.
+ Mover las tablas de lectura y de escritura a una nueva base de datos. (las 3 en una misma base de datos)
+ Cambiar el CHAR_SET para verificar si la degradación del proceso es por usar UTF8.
+ Probar guardar los datos en una tabla Comprimida.
+ Crear una tabla destino en memoria para evitar grabaciones en disco (solo habrá lecturas) y guardará el resultado en RAM.
+ Medir velocidad del disco de datos (comando dd) para ver si es suficientemente rápido.
+ Cambiar el proceso para que lea únicamente de una tabla y grabe en una. (resultado incompleto pero quitar la variable del join)
+ Crear un procedimiento que guarde datos en la tabla destino (proceso de grabación unicamente, sin lecturas)
+ Modificar el tipo de datos de campos de la tabla destino.
Para evitar generar degradación en el servidor viejo productivo, usé únicamente el servidor nuevo de TEST.
Lo aislé de todo otro proceso, paré los demás servicios, controlé que no haya acceso de personas o procesos y verifiqué un reinicio del motor para que en cada prueba comencemos desde cero.
La comparativa se realizó usando un servidor de TEST aislado, sin utilizar recursos externos, corriendo los procesos localmente en una sesión BASH, reiniciando el srv MariaDB 10.2 antes de cada parte, midiendo los procesos por las propias herramientas del motor, repitiendo cada proceso 3 veces y almacenando el promedio.
Luego de 3 dias de trabajo constantes (aunque continuaré), las conclusiones obtenidas son las siguiente:
* Usar las tres tablas MyISAM SIN todos los índices creados, requiere 3.53 segundos de proceso.
* Usar las tres tablas MyISAM CON todos los índices creados, requiere 17.1 segundos.
* Usar las tres tablas InnoDB SIN todos los índices creados, requiere 21 segundos.
* Las tres table InnoDB CON los índices, requiere 49 segundos.
* Usar tablas InnoDB consumo 2.86x veces el tiempo comparado con MyISAM.
* Realicé pruebas con 10K, 100K y 1M de registros, con MyISAM el crecimiento es proporcional.
* En el caso de InnoDB, el crecimiento es exponencial, mas registros mucho mayor es el tiempo necesario.
* Convertir la tabla de MyISAM a InnoDB demora 54.35 segundos y acaba midiendo 478 mb
* Convertirla de InnoDB a MyISAM demora 16.9 segundos y acaba midiendo 144mb+81mb (D+I)
* Comparando el proceso de carga con MyISAM y CON/SIN índices, CON necesita 4.83x mas que SIN.
* Comparando InnoDB, CON necesita 6.54x mas que SIN.
* Crear una base de datos nueva y crear dentro las 2 tablas orígenes y la de destino, necesitaba el mismo tiempo que 2 tablas en una base y el destino en otra base de datos.
* Evalué hacer la carga en una tabla InnoDB SIN índices y crearlos luego, pero el tiempo ganado (49-21=28 segs) se volvía a perder creando los índices (50.58 segs), mientras que insertar los registros en la tabla CON los índices creados demoraba 49 segs, cargar los registros en una tabla SIN índices y luego reindexar demoraba 21 + 50.58 secs, en total 71.58 segs.
* El aumento de tiempos en InnoDB sobre MyISAM, era proporcional a la medida de las tablas en disco. Eso quiere decir que en ambos motores el tiempo se consumía guardando los datos al disco a una velocidad aproximada.
En InnoDB los ficheros medían 3.3x mas que MyISAM, y el tiempo era 2.86x mas.
* Almacenar la tabla en RAM requería 53 segundos.
* Almacenar los registros en una tabla con "Row_Compress" (comprimida) requería 97 segundos.
* El tiempo de leer todos los registros del join era de 19 segundos, el resto del tiempo era usado para guardarlos en la tabla destino.
* Pasar el tipo de SET de caracteres a ASCII necesitó 53 segundos.
* Si leía los registros de una sola tabla (sin el JOIN) requería 45 segundos.
* Usar "LOCK TABLES" requería 51 segundos.
* Crear un SP para guardar 1M de registros en la tabla destino (sin leer nada de disco) requería 50 segundos.
* Si la tabla tenia dos cambios como "INT" necesitaba 50 segundos, no cambiaba mucho el tiempo total.
* Cambiar los parámetros del disco en el servidor virtual Línux, permitió pasar de 58MB/s a 155MB/s, mientras que mi equipo portátil con un disco SSD me da 287MB/s.
La configuración del motor MariaDB 10.2 que me dió mejores resultados es la siguiente, aunque seguiré haciendo pruebas :
[mysqld]
slow_query_log=0
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=3500M
thread_cache_size=16
skip-name-resolve=1
tmp_table_size=128M
max_allowed_packet=64M
performance_schema=0
innodb_file_format=barracuda
innodb_strict_mode=1
innodb_flush_log_at_trx_commit=2
transaction-isolation=READ-COMMITTED
sql-mode=''
Mientras que InnoDB es un motor pensado para transacciones con propiedades ACID, con commit/rollback, consistencia y bloqueo parcial.
MyISAM sigue siendo un motor simple, con bloqueos completos de objetos, atomicidad en las transacciones pero con una gran velocidad.
Usar InnoDB para procesos batch (almacenar 1M de registros) requiere 2.86x mas tiempo con usando MyISAM.
No hay comentarios:
Publicar un comentario