SQL Server performance y cache.

Optimización de SQL Server mediante cache en SQL.

Hace unas semanas tuve que implementar un proceso de optimización para SQL Server.

El problema inicial es una aplicación que solicita datos a un motor SQL Server, produce un query que demora unos 12 segundos para obtener los datos a enviar al cliente, ese query se ejecuta durante todo el dia, todas las veces que lo pida un cliente, pero desde "negocios" acordaron actualizar el proceso cada 8 horas. Existe un estimado de 5000 usuarios activos pidiendo sus datos. La medida del resultado del query es de unos 300 kb.

También existe otro proceso similar donde se definió una actualización cada hora.
Hay miles de usuarios pidiendo decenas de veces los mismos datos cada NN segundos.

Aunque 5000 usuarios no es un número muy grande, miles de sus peticiones durante todo el dia tiran al servidor SQL, el consumo de CPU es muy alto, aumenta la demora para resolver cada petición (de 12 a 40 segundos) hasta que entra en un espiral del que no puede salir, la cantidad de peticiones comienza a ser mayor que la cantidad que puede resolver.

Como muchas veces pasa, el aplicativo tiene muchos años, el volúmen de datos aumentó mucho, diferentes razones hacen que los 5000 clientes hagan varias peticiones (de 2x a 5x en algunos casos), dependiendo de cuantos equipos usan durante el dia, usan mas de un equipo (PCs, teléfonos, tables...) al final todo implica miles de peticiones cada minuto.

Luego de varias pruebas encontramos que existe un máximo de 10 peticiones que se pueden realizar al mismo tiempo y que el motor SQL puede resolver, si se aumenta ese valor comienzan a aumentar los tiempos de respuesta de cada una, si continúan o aumentan las peticiones ya no hay solución.

Es un aplicativo crítico, visible desde internet, muy usado todo el dia y con datos que deben ser actualizados cada hora ó cada ocho horas.

Luego de darle unas cuantas vueltas y evaluar varias alternativas, lo mas rápido y simple era implementar un cache del lado del motor SQL. Si, conocemos Redis y otras soluciones de cache, pero las otras soluciones requerían muchos mas cambios en distintos componentes, el "cache de SQL" es un punto único a cambiar. (un SP.)

La solución requería unas cuantas horas de programación de parte de un DBA experto, pruebas y salir al "live" rápidamente.


SOLUCION IMPLEMENTADA

Crear una tabla caché para cada proceso (2 tablas independientes), que contiene unos 25 campos (que son los que se envían desde el motor SQL al Aplicativo), ademas de tres campos adicionales, una fecha/hora (timestamp), un campo "id_usuario" y un campo ID_PK (PK, incremental).

En los 25 campos se guardaría los registros procesados (entre 100 y 500) que son los datos pedidos por los usuarios, en el campo fecha guardamos un timestamp del momento del cálculo, el campo ID_USUARIO contendría el ID del usuario que hizo la petición y el IDPK incremental sirve para que queden almacenados en el mismo órden en que fueron generados.

Los datos deben ser únicos, agrupados y ordenados, estas condiciones son las que demoran el query original.

Luego creamos un índice para que la lectura de esos registros para cada usuario sera la mas rápida posible.

El campo ID_USUARIO tiene datos redundantes pero me permitirá simplificar los procesos y tiempos. Se podría usar otra tabla normalizada, pero por razones de optimización lo descartamos.

Desde el APP se invocaba a un SP (Stored Procedure) pasando un identificador de usuario. El SP devolvía el join/where/group/order de unas 30 tablas en formato de 25 campos por 100 a 500 registros. (los dos SPs eran similares aunque el contenido era distinto)

Las tablas caché miden actualmente unos 2gb cada una, pero resuelven el problema de las NN peticiones repetidas, limitando el reproceso a una/ocho horas.

El nuevo SP incluye estas partes:
* El campo pasado como parámetro se usa para buscar en la tabla "cache" si tenemos datos para ese usuario, obtiene el primer registro y obtiene la fecha de la última grabación.
* Si no había datos, tendremos que generarlos.
* Si había datos pero había pasado el tiempo de "Caché válido", tendremos que generarlo.
* En caso contrario enviamos los registros que tenemos en nuestra tabla caché.
(ese proceso envía los datos en 1 segundo, pasando de 12-30 segs a solo 1)

* El SP entonces calcula los datos actualizados (demora entre 12 y 30 segundos).
* Se los envía al usuario.
* Borra los registros de la tabla cache para ese usuario.
* Luego los guarda en el cache (agregando timestamp, IDUsuario y el automático del IDPK)


SP Simplificado.

CREATE PROCEDURE proceso1
@user_id int -- parametro de entrada (ID)
begin

.......

-- @CUANDO es ahora
-- cuando es el campo de generación del cache.

SET @ACEPTO = ISNULL((
-- cuantos minutos pasaron desde la ultima peticion
SELECT TOP 1 ABS(DATEDIFF(minute, cuando, @CUANDO)) FROM CACHE WHERE user_id = @user_id
),0)

SET @ACEPTO2 = ISNULL((
-- en caso de tener cache cual fue la fecha de generacion
SELECT TOP 1 FECHAFIN FROM CACHE WHERE user_id = @user_id
),'2017-01-01')

-- solución para renovar el cache luego de 60 minutos.
-- si pasaron menos de 60 minutos,
-- mas de CERO, es decir "Tengo cache"
-- y los datos guardados corresponden a hoy
IF ( @Acepto > 0 AND @Acepto < 60 and @Acepto2 = @DiaFinD )
begin
-- el cache todavía es válido, lo envío
select * from CACHE WHERE user_id = @user_id;
END
ELSE
begin
-- aqui reprocesar y guardar en cache
-- muchos selects, se guarda el resultado en una tabla temporal @1
create table @1 ()
insert into @1 select * from xxxxxxx

-- borro datos del cache para ese usuario
DELETE FROM CACHE WHERE user_id = @user_id

-- meto en el cache los datos nuevos para ese usuario
INSERT INTO CACHE
SELECT *, @user_id, CONVERT(date, @diaFinD), @cuando FROM @t1

SELECT * FROM @t1
END

MariaDB versiones.

Un link a un post sobre Motores MySQL y MariaDB montados sobre Docker.

https://dockertips.com/MySQL_ultimas

Tiempos y Performance MariaDB

Un post con muchos detalles de pruebas realizadas usando MariaDB.

https://www.linkedin.com/pulse/problemas-performance-y-mediciones-en-mariadb-102-cialdella-c-/?published=t

Problemas, Performance y Mediciones en MariaDB 10.2.

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.