Performance en MariaDB y distintos engines.
Me pidieron que vea que estaba ocurriendo con un servidor MariaDB donde las nuevas versiones daban peores tiempos de respuesta que versiones mucho mas viejas.
Usando un contenedor con MariaDB 10.2, en unos dias realizaré el mismo proceso pero en MySQL 8.0.
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 a 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 obtengamos una 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 (nada de “me parece” o “mejor” o “menor”, sólo números).
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, las mismas dos tablas InnoDB con un join y el destino es una tabla InnoDB.
-
Los servidores son virtuales.
-
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:
- Usar el contenedor de MariaDB 10.2 como repositorio (y un volúmen en mi equipo real).
- 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.
Todos estos procesos son mucho mas sencillos al tener un Contenedor que podemos parar y arrancar, y en el mismo script (Bash), programamos también los querys a enviar al motor.
-
Usaremos 1M de registros en una de las tablas de lectura. 1.4 gb. 11 campos de lectura. Tabla InnoDB.
-
También 300.000 registros para la segunda tabla de lectura. 400 mb. 1 campos de lectura. Tabla InnoDB.
-
La tabla InnoDB generada será de 904.000 registros con 12 campos almacenados (1 de la 2da tabla y 11 de la primera tabla)
** Información sobre el contenedor Docker **
Estado inicial luego del reinicio del contenedor.
# docker stop mariadb102; docker start mariadb102
# docker stats
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
011f36f901ad mariadb102 0.11% 157.8MiB / 7.681GiB 2.01% 8.49kB / 814B 79.9MB / 2.22MB 31
Corriendo el proceso con varios engines.
# InnoDB
Query OK, 909269 rows affected (43.89 sec)
Query OK, 909269 rows affected (43.44 sec)
Query OK, 909269 rows affected (43.40 sec)
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
011f36f901ad mariadb102 77.02% 354.9MiB / 7.681GiB 4.51% 13.8kB / 2.32kB 270MB / 2.33GB 32
# MyISAM
Query OK, 909269 rows affected (12.24 sec)
Query OK, 909269 rows affected (12.25 sec)
Query OK, 909269 rows affected (11.93 sec)
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
011f36f901ad mariadb102 100.99% 372.7MiB / 7.681GiB 4.74% 16.2kB / 3.15kB 641MB / 5.91GB 32
# ARIA
Query OK, 909269 rows affected (16.91 sec)
Query OK, 909269 rows affected (16.49 sec)
Query OK, 909269 rows affected (16.69 sec)
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
011f36f901ad mariadb102 100.78% 444.1MiB / 7.681GiB 5.65% 17.8kB / 4.15kB 1GB / 6.03GB 31
#
Query OK, 909269 rows affected (43.10 sec)
Query OK, 909269 rows affected (44.29 sec)
Query OK, 909269 rows affected (43.06 sec)
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
011f36f901ad mariadb102 70.18% 426.8MiB / 7.681GiB 5.43% 39kB / 7.03kB 1.8GB / 14.6GB 31
Comando para ver los engines de cada tabla-.
SELECT TABLE_NAME, TABLE_SCHEMA, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA='AAA';
Los tiempos son constantes “SIN/CON” paradas y arranques del contenedor.
Opciones evaluadas (Distintos tipos de tabla destino) :
-
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 quita una 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.
Utilizamos en este caso un Contenedor aislado, sin otros servicios corriendo en mi equipo real. Luego de cada prueba, se reiniciaba el contenedor.
Luego de 5 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 51 segundos.
-
Usar tablas InnoDB consumo 2.42x veces el tiempo comparado con MyISAM. Los ficheros generados en InnoDB son 2.16x los que usa 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 distintas bases 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 51 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 al guardar los datos al disco a una velocidad aproximada.
-
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, un poco menos que los 56 segs de UTF8.
-
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.
-
Probé también correr varias veces el mismo proceso “SIN” reiniciar el motor MariaDB, los tiempos fueron ligeramente menores (de 21 a 19 segundos), estim. 10%. Esto continúa ratificamdo mi teoría que el gran problema es la grabación de registros en disco.
-
Hacer la carga usando MyISAM “SIN” indices demora 12.89 segs, repetirla varias veces “SIN” reiniciar el motor, promedio de 10.36 segs.
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=''
strace -c -f -p $( pidof mysqld )
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
44.81 10.451114 2086 5009 589 futex
38.43 8.963999 1947 4604 io_getevents
7.67 1.788277 10582 169 nanosleep
6.52 1.520083 152008 10 6 restart_syscall
1.38 0.322231 24 13549 pread64
0.70 0.164365 319 515 pwrite64
0.23 0.053298 49 1078 fsync
0.18 0.042644 11 3778 io_submit
0.08 0.017768 58 306 sched_yield
0.00 0.000693 4 169 fallocate
0.00 0.000033 11 3 1 recvfrom
0.00 0.000026 26 1 sendto
0.00 0.000000 0 1 write
0.00 0.000000 0 1 fstat
0.00 0.000000 0 7 mprotect
------ ----------- ----------- --------- --------- ----------------
100.00 23.324531 29200 596 total
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.
Aria es el nuevo “MyISAM” promovido por MariaDB, necesita 31 segundos comparado con el viejo motor MyISAM que necesita 19. Puede ser mejor motor y con mas opciones pero el tiempo de los procesos aumenta.
Detalle de las tablas creadas y sus motores asignados.
| TABLE_NAME | TABLE_SCHEMA | ENGINE |
| addition | AAA | InnoDB | CON INDICES
| addition2 | AAA | InnoDB | SIN INDICES
| addition3 | AAA | MyISAM | CON INDICES
| addition4 | AAA | MyISAM | SIN INDICES
| addition9 | AAA | InnoDB | SOLO GRABAR
| additiona | AAA | Aria | ARIA ENGINE
| additionc | AAA | InnoDB | COMPRIMIDO
Usar InnoDB para procesos batch (almacenar 1M de registros) requiere 2.66x (aprox.) mas tiempo con usando MyISAM.
Para evaluar la velocidad del disco que obtiene el contenedor, corremos este comando, aunque el disco dice que llega a los 550 MB/s, en mi caso solo alcanzó 375, es bastante.
dd if=/dev/zero of=/var/lib/mysql/testfile bs=1G count=1 oflag=direct
1+0 records in
1+0 records out
1073741824 bytes (1,1 GB, 1,0 GiB) copied, 2,86339 s, 375 MB/s
Los tiempos promedios según cada motor : (corriendo 5 veces cada pruebas y parando el motor luego de cada una)
MyISAM 19
Aria 30
InnoDB 56
InnoDB Comprimida 97 segundos.....
En el gráfico veremos pruebas realidas con 10.000, 100.000 y 1M de registros. La línea roja es InnoDB y la Azul MyISAM.
GRAFICO1: 