En esta segunda parte sobre Particionado en Synapse vs SQL Server vamos a centrarnos en el particionado sobre los Synapse dedicated SQL pools y en comparar las diferencias que encontramos entre un sistema SMP y MPP en estos escenarios de carga y consulta masiva.
Pasando del mundo SMP al MPP
En el anterior artículo introdujimos las diferencias entre ambos tipos de sistemas y realizamos pruebas con sistemas SMP escalándolos desde 8 cores hasta 80 cores.
En base a dichos resultados ya podemos hacernos una idea de qué podemos esperar en este tipo de sistemas SMP cuando los escalamos verticalmente. Ahora vamos a pasar al mundo MPP de Synapse para realizar este mismo tipo de operaciones y poder comparar el rendimiento.
Preparando el SQL pool y cargando 1000 millones de filas
Comenzaremos creando un Synapse dedicated SQL pool de tamaño DW500c:
Una vez creado el pool crearemos la tabla sobre la que cargaremos los 1000 millones de registros:
Podemos ver que no hemos tenido que indicar que queremos un índice cluster explícitamente ya que en Synapse por defecto las tablas se crean con índices columnares clúster si no indicamos lo contrario. La distribución por defecto es round_robin que en este caso, donde no vamos a cruzar por otras tablas, nos vale perfectamente como una forma de distribuir los datos uniformemente sobre las 60 distribuciones.
Cargando datos sin particionar: MPP 1 – SMP 0
Para realizar la carga vamos a subir el tamaño de las operaciones a 100 millones para poder así asegurarnos un buen reparto y compresión. Al intentar lanzar la operación de inserción nos encontramos con un error:
Aunque Synapse se apoya en SQL Server es habitual encontrarnos con diferencias cuando migramos código desde versiones “tradicionales”. Procederemos a sustituir dicho código por otro que nos genere cadenas aleatorias más sencillo y que se apoye únicamente en newid():
Si obtenemos el plan de ejecución de esta operación vemos que la estimación es de únicamente 1 fila:
La razón es que utilizar sys.objects por defecto en Synapse únicamente devuelve los objetos de usuario, en nuestro caso únicamente 1 tabla, por lo que el producto cartesiano devolverá únicamente una fila.
Por tanto sustituiremos esta tabla por alguna otra que tenga cierta cantidad de filas. En nuestro caso usamos sys.dm_pdw_request_steps pero podría valer cualquiera con al menos unas pocas decenas de filas (para que el producto cartesiano genere al menos 100 millones de filas):
Una vez ejecutamos esta consulta vemos que en 47 segundos hemos podido insertar 100 millones de filas, que no es un mal resultado:
Insertaremos los 900 millones restantes con esta misma consulta e intentaremos lanzar la carga en paralelo para ver cómo responde pero lo que nos encontramos es que se quedan las operaciones suspendidas:
Debemos tener en cuenta que un DW500c es relativamente pequeño y este comportamiento de suspensión de operaciones es previsible y algo que debemos tener en cuenta en el dimensionamiento. De todas formas en 5 minutos terminó el último de los inserts que lanzamos concurrentemente:
Recordemos que en la primera inserción de 1000 millones de filas sobre la máquina con 8 cores nos llevó 76 minutos y algo menos de 30 minutos con la máquina de 80 cores por lo que poder hacer esta carga de 1000 millones de filas en menos de 6 minutos es algo ya diferenciador de la potencia que podemos obtener con los pools dedicados de Synapse.
Consultando datos sin particionar: MPP 2 – SMP 0
El siguiente paso es comenzar a testear las consultas. Comenzaremos con la consulta que no filtra ninguna de los 1000 millones de filas y busca entre todas ellas la cadena de longitud mínima:
De nuevo obtenemos un resultado muy bueno, con 32 segundos si lo comparamos con los 991 segundos que necesitamos con la máquina de 8 cores y los 201 segundos de la máquina de 80 cores.
Con la consulta filtrada (que reduce a un 10% el total de filas a evaluar) el resultado es muy bueno también y en 5 segundos tenemos la respuesta:
Cargando datos particionados: MPP 3 – SMP 0
El siguiente paso es crear una tabla particionada y cargarla con los mismos 1000 millones de filas. Intuitivamente estos tiempos ya nos deberían dar una “pista” que posiblemente particionar una tabla de 1000 millones de filas puede no aportarnos una ventaja significativa dada la velocidad que estamos obteniendo.
Comenzaremos creando la tabla e indicando los puntos de partición:
El siguiente paso es realizar la carga de los datos, para lo que inicialmente utilizaremos la tabla que hemos cargado previamente como fuente de filas:
En este caso el rendimiento de la carga no es nada bueno y si nos fijamos en los pasos que se están realizando podemos ver que el paso que ejecuta en el DMS (Data Movement Service) es el que requiere la mayor parte:
Finalmente el proceso falló tras el timeout de 60 minutos:
Por tanto, es crítico disminuir el movimiento de datos entre nodos para tener un buen rendimiento. Algo muy similar a lo que nos ocurre con Polybase cuando tenemos una configuración scale-out.
Por ejemplo si directamente creamos una tabla particionada (test_cci3) y generamos el contenido de las filas desde cero, con el script inicial, el tiempo de carga de cada tanda de 100 millones de filas es similar al que teníamos previamente (entre 40 y 50 segundos) por lo que el tiempo total de carga fue de unos 7 minutos para los 1000 millones de filas.
Sobre esta tabla particionada test_cci3 vamos a lanzar el par de consultas para comprar su rendimiento con la tabla sin particionar:
Podemos ver que hemos pasado de 32 segundos a 19 segundos y de 5 segundos a 4 segundos en el caso de la consulta filtrada en una primera ejecución por lo que existe una ligera mejora, pero que no parece justificar un particionado.
Entre otras cosas no parece muy justificable debido a que aumenta el espacio ocupado sustancialmente, un 50% aproximadamente en este caso, y tampoco mejora el rendimiento de forma tan notable:
Durante la ejecución de las consultas podemos ver cómo de “apretado” ha estado el pool. En nuestro caso no hemos llegado a consumir todos los recursos disponibles:
Escalando a DW5000c y subiendo a 10000 millones de filas: MPP 4 – SMP 0
Aumentar a DW5000c con las duraciones que estamos teniendo de consultas y con el mismo volumen probablemente no tenga sentido, ya que poco margen queda de mejora a esos pocos segundos. Debido a ello, aunque no vamos a poder comparar los tiempos directamente con el SMP, vamos a crear una nueva tabla con 10000 millones de filas y para ello modificaremos el tipo de dato de la columna ID a bigint:
En este caso realizaremos las inserciones de 500 millones en 500 millones:
Lanzando estas operaciones en menos de una hora habremos cargado 10000 millones de filas:
El tamaño de la tabla resultado es de unos 518 GB, lo cual tratándose de un índice columnar podría traducirse a unos 5.2 TB en rowstore clásico:
Antes de escalar el pool, vamos a probar cuanto tiempo tardamos en ejecutar la consulta sin filtrar con el DW500c actual:
No es un mal resultado en realidad, para un volumen 10 veces más grande la duración se ha incrementado de 32 segundos a 343 segundos. Dicho de otra forma, el escalado en tiempo ha sido prácticamente lineal, lo cual es lo mejor que podemos esperar.
El siguiente paso lógico es ver si, aumentando 10 veces el tamaño del pool a un DW5000c, podemos volver a tener estos tiempos de 32 segundos o menos para un volumen de 10000 millones de filas.
Para ello primero modificaremos el tamaño del pool:
Debemos tener en cuenta que este tipo de operaciones no son instantáneas, pueden llevar varios minutos, y pueden provocar un breve corte/parón del servicio mientras se escala. Es recomendable planificar estas operaciones cuando no tengamos operaciones pesadas, especialmente de carga, en marcha para evitar rollbacks potencialmente costosos. En nuestro caso hemos necesitado 7 minutos para hacer el escalado y tener el pool redimensionado listo para su uso.
Una vez realizado el escalado la primera ejecución de la consulta se demoró 53 segundos y la segunda únicamente 12 segundos gracias al efecto de la mayor cantidad de memoria y por tanto de caché:
La ejecución de la consulta filtrada, ya con la caché caliente, se demoró únicamente 7 segundos:
En esta segunda parte de este post hemos visto cómo un sistema MPP ante cargas y consultas “sencillas” donde nos centramos en el volumen y en los tiempos tenemos unos resultados muy buenos. Este tipo de operaciones sencillas buscan mostrar el potencial del motor desde el punto de vista del rendimiento del hardware subyacente y en estos casos la agregación del hardware subyacente del sistema MPP aplasta al SMP.
En el mundo real las consultas se complican, a menudo excesivamente, haciendo que otros aspectos acaben siendo críticos para el rendimiento. Es decir, antes de decantarse por una alternativa SMP o MPP debemos considerar toda la tipología de cargas que tendrá que soportar la plataforma y cuáles serán realmente más críticas desde el punto de vista de rendimiento y/o tiempos de respuesta.
Recomendamos encarecidamente que ante problemas de rendimiento se revise la documentación donde podemos encontrar buenas prácticas (https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-dedicated-sql-pool ) así como detalles sobre el sistema de gestión de concurrencia/carga automático existente en Synapse (https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/resource-classes-for-workload-management)
Conclusión
La conclusión más evidente que podemos sacar de estas pruebas realizadas es que el refrán “zapatero a tus zapatos” también aplica a estos casos. Claramente la solución MPP brilla en aquellas situaciones donde podemos escalar de forma prácticamente lineal con el aumento de nodos disponibles. Este tipo de técnicas de escalabilidad horizontal, con matices, se lleva utilizando desde que alcanzo a recordar (por ejemplo usando vistas particionadas distribuidas). El objetivo final es agregar recursos de CPU, memoria y entrada/salida de varias máquinas de forma que tengamos la ilusión de tener una única tabla en una única base de datos cuando en realidad lo que tenemos por debajo son múltiples bases de datos y múltiples instancias SQL coordinadas para atender una misma petición.
En el caso de Synapse la escalabilidad y la gestión de este tipo de arquitecturas se ha simplificado mucho y nos ofrece una gran capacidad de cómputo, memoria y entrada/salida capaz de satisfacer necesidades analíticas ante volúmenes elevados de forma ágil. Los grandes retos de estas arquitecturas siguen presentes en Synapse y se centran especialmente en diseñar estrategias de particionado adecuadas y, especialmente, en conseguir planes de ejecución distribuidos que sean eficientes para las consultas complejas.
Para llegar a conseguir buenos resultados globalmente en un MPP se requerirá de un concienzudo diseño de la base de datos, de una correcta distribución de los datos y un largo etcétera por lo que el rol del arquitecto de datos es extremadamente crítico para poder obtener todo el potencial a nuestros datos en este tipo de soluciones.
¡Has llegado al final! Parece que te ha gustado nuestro post sobre Azure Synapse.
Recuerda que, puedes solicitar tu prueba gratuita de Azure Synapse para explorar todo el serivicio y dar respuesta a cómo podría ayudarte en el desarrollo de tu negocio. Solicítala ya y realiza una prueba de concepto de 10 jornadas sin coste.
Quiero mi prueba gratuita de Azure Synapse