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:

create dedicated pool sql

Una vez creado el pool crearemos la tabla sobre la que cargaremos los 1000 millones de registros:

CREATE TABLE test_cci (id int identity, payload varchar(4000), filter int)
 

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.

Solicita tu PoC gratuita de Azure Synapse

Explora Synapse y da respuesta a cómo podría ayudarte, realizando una prueba concepto de 10 jornadas sin coste.

Quiero mi prueba gratuita de Azure Synapse

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:

create dedicated pool sql insert into

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():

insert into test_cci (payload,filter)
select TOP 100000000 payload, abs(checksum(payload))%10 as filter
FROM
(
SELECT convert(varchar(4000),newid())+‘-‘+ convert(varchar(4000),newid()) as payload
from sys.objects s1,sys.objects s2,sys.objects s3,sys.objects s4,sys.objects s5, sys.objects s6
) a
 

Si obtenemos el plan de ejecución de esta operación vemos que la estimación es de únicamente 1 fila:

Particionado Synapse vs SQL Server (Parte 2)

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):

insert into test_cci (payload,filter)
select TOP 100000000 payload, abs(checksum(payload))%10 as filter
FROM
(
SELECT convert(varchar(4000),newid())+‘-‘+convert(varchar(4000),newid()) as payload
from sys.dm_pdw_request_steps s1,sys.dm_pdw_request_steps s2,sys.dm_pdw_request_steps s3,sys.dm_pdw_request_steps s4,sys.dm_pdw_request_steps s5, sys.dm_pdw_request_steps s6
) a
 

Una vez ejecutamos esta consulta vemos que en 47 segundos hemos podido insertar 100 millones de filas, que no es un mal resultado:

query executed synapse sql

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:

Particionado Synapse vs SQL Server (Parte 2)

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:

Particionado Synapse vs SQL Server (Parte 2)

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:

Particionado Synapse vs SQL Server (Parte 2)

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:

sql synapse select min response

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:

create table test_cci2 (id int identity,payload varchar(4000),filter int)
WITH (PARTITION ( filter RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9)))

 

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:

insert into test_cci2 (payload,filter)
select payload,filter from test_cci
 

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:

report id sql synapse

Finalmente el proceso falló tras el timeout de 60 minutos:

Particionado Synapse vs SQL Server (Parte 2)

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:

select min(len(payload)) from test_cci3
 
Particionado Synapse vs SQL Server (Parte 2)
select min(len(payload)) from test_cci3 where filter=1
 
query executed successfully synapse sql

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:

Particionado Synapse vs SQL Server (Parte 2)

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:

Particionado Synapse vs SQL Server (Parte 2)

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:

create table test_cci_max (id bigint identity, payload varchar(4000), filter int)
 

En este caso realizaremos las inserciones de 500 millones en 500 millones:

insert into test_cci_max (payload,filter)
select TOP 500000000 payload, abs(checksum(payload))%10 as filter
FROM
(
SELECT convert(varchar(4000),newid())+‘-‘+convert(varchar(4000),newid()) as payload
from sys.dm_pdw_request_steps s1,sys.dm_pdw_request_steps s2,sys.dm_pdw_request_steps s3,sys.dm_pdw_request_steps s4,sys.dm_pdw_request_steps s5, sys.dm_pdw_request_steps s6, sys.dm_pdw_request_steps s7, sys.dm_pdw_request_steps s8
) a
 

Lanzando estas operaciones en menos de una hora habremos cargado 10000 millones de filas:

select count_big(*) from test_cci_max
 
Particionado Synapse vs SQL Server (Parte 2)

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:

exec sp_spaceused ‘test_cci_max’,1
 
test cci synapse max

Antes de escalar el pool, vamos a probar cuanto tiempo tardamos en ejecutar la consulta sin filtrar con el DW500c actual:

select min(len(payload)) from test_cci_max
 
Particionado Synapse vs SQL Server (Parte 2)

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:

Particionado Synapse vs SQL Server (Parte 2)

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é:

select min(len(payload)) from test_cci_max
 
Particionado Synapse vs SQL Server (Parte 2)

La ejecución de la consulta filtrada, ya con la caché caliente, se demoró únicamente 7 segundos:

select min(len(payload)) from test_cci_max where filter=1
 
Particionado Synapse vs SQL Server (Parte 2)
Particionado Synapse vs SQL Server (Parte 2)

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
0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like
Leer más

El tiempo es oro: Cómo predecir series temporales con datos de muchas dimensiones con R – SolidQ Summit 2017

Saber cuánto vamos a vender mañana o el año que viene es el sueño dorado de muchos analistas de negocio. Sin embargo, no nos conformamos con un número, sino que necesitamos predicciones ajustadas a todos los niveles, detalles y segmentaciones posibles, y aquí es donde la predicción puede volverse realmente difícil. Descubre las implementaciones reales afrontando estas predicciones sin importar el nivel de detalle que necesites y sube un peldaño en tus sistemas inteligentes.