Cuando pensamos en general en grandes volúmenes de datos y en entorno analíticos el particionado suele ser un concepto clave para poder tener un rendimiento adecuado. Cuando comparamos los Synapse dedicated SQL pools con SQL Server aparecen diferencias importantes y que nos afectan a cómo abordar este particionado de una forma óptima.
Sistemas MPP, ¿qué son?
Lo primero que tenemos que entender es que Synapse dedicated SQL pool es un sistema MPP (Massive Parallel Processing), una evolución de APS (Microsoft Analytics Platform System) que a su vez lo fue de PDW (SQL Server Parallel Data Warehouse). Los sistemas MPP por su naturaleza nos permiten una escalabilidad mucho mayor que un SMP (Symmetric Multi-Processing) al permitir escalar horizontalmente. A cambio de ello, implican una mayor complejidad y costes de comunicación entre nodos que debemos tener en cuenta.
Desde mi punto de vista, debemos considerar un sistema MPP como alternativa cuando de forma clara se exceda la capacidad de cómputo de un sistema SMP. Suele ser demasiado habitual ir directamente a una solución MPP para volúmenes de datos no demasiado grandes y con necesidades de cómputo perfectamente manejables por un SMP.
También es cierto en algunos casos puede tener sentido ir a MPP por economía de escala, ya que suele ser más barato disponer de 4 nodos con 1-2 sockets y 4 TB de RAM que no un único sistema con 8 sockets y 16 TB de RAM. Esto es también especialmente cierto en entornos virtualizados/cloud donde las máquinas de mayor tamaño tienen un incremento en el precio importante al ser mucho más “nicho” que las más pequeñas. Por tanto al final hablamos de buscar un compromiso entre el total de recursos, coste y complejidad añadida.
Distribuciones en Synapse, un concepto clave
Un concepto clave antes de poder entrar en el particionado sobre Synapse es el concepto de distribución. Una distribución es la unidad básica de almacenamiento y procesamiento para las operaciones paralelas. El número de distribuciones en Synapse es de 60 por lo que, por decirlo de alguna manera, cuando una consulta se ejecuta la estamos dividiendo en 60 pequeñas operaciones que ejecutan en paralelo.
En función del tamaño del dedicated SQL pool, cada nodo de computación manejará entre 1 y 60 distribuciones en los casos más extremos (60 nodos vs 1 único nodo). Si nos fijamos en los tamaños más pequeños únicamente se utiliza 1 compute node y se escala hacia arriba dicho nodo desde 60 GB de memoria hasta 300 GB. Esto en cierta forma apoya la idea que para tamaños pequeños es mejor mantenernos en una configuración de 1 solo nodo, similar a un SMP.
A partir de este punto se comienzan a añadir nodos, cada uno con 300 GB de memoria, hasta llegar al máximo con el DW30000c con 18 TB de memoria en total:
También debemos tener en cuenta que un sistema como Synapse no está pensado para una gran concurrencia y tiene un número limitado de “concurrency slots” que en función de la clasificación que se haga de las operaciones y el tamaño que tengamos nos permitirá ejecutar más o menos consultas de forma concurrente. Según el modelo de clasificación que elijamos (estático o dinámico) el aumentar las DWu nos permitirá mayor concurrencia sobre un mismo volumen de datos (estático) o mayor concurrencia sobre un volumen de datos que aumenta (dinámico).
¿Cómo nos afectan las distribuciones al uso del particionado de datos?
El particionado en Synapse se ejecuta a nivel de distribución, por lo que aplicará sobre un conjunto de 60 distribuciones. Por tanto tenemos que asegurarnos que la volumetría de cada una de esas 60 distribuciones ya resulta ser suficientemente elevada como para que el particionado adicional sobre esta distribución de datos pueda aportarnos ventajas y no causarnos más problemas.
¿Cuál es el caso más habitual de problemas que nos encontraremos? Pues el caso más habitual es que estemos utilizando índices columnares y el volumen tras particionar resulte demasiado pequeño, menos de 1 millón de filas por partición y distribución. Es decir, que si por ejemplo generamos particiones por mes y mantenemos 5 años de datos, tendríamos 60 particiones que multiplicadas por 60 distribuciones nos daría que la volumetría “recomendable” de, al menos, 3600 millones de filas. En muchos casos no tenemos una volumetría tan elevada por lo que lo recomendable sería bajar la granularidad del particionado, por ejemplo 1 partición cada año, de forma que con 300 millones de filas o 60 millones por año ya podamos obtener buenos ratios de compresión con el índice columnar.
Centrándonos en las diferencias al particionar entre SQL Server y Synapse veremos que en Synapse se ha simplificado el particionado y se encuentra estrechamente ligado a la creación de la tabla. Es decir, en Synapse no vamos a tener esquemas de particionado ni funciones de particionado como tenemos en SQL Server, únicamente vamos a tener que indicar la columna por la que particionaremos (por ejemplo la fecha), los puntos ( boundaries) que separan cada una de las particiones y si queremos que los límites estén incluidos en la partición de la izquierda o de la derecha (RANGE LEFT/RIGHT).
Pasando a la acción, SMP vs MPP
Una vez tenemos ya los conceptos básicos claros y sus implicaciones, vamos a realizar algunas pruebas con Synapse y las vamos a comparar con el rendimiento que podamos obtener con un SMP en Azure. Más concretamente vamos a comparar el rendimiento de algunas consultas particionadas y sin particionar en dos escenarios:
- SQL VM con 8 vCore SQL Server EE -> 47€/h vs Synapse DW500c -> 6.367€/h
- SQL VM con 80 vCores SQL Server EE -> 7€/h vs Synapse DW5000c -> 63.67€/h
Preparando los datos… 1000 millones de filas para desayunar.
Para poder realizar la prueba realizaremos una carga inicial de 1000 millones de filas sobre una tabla con un índice columnar clustered sin particionar. Para realizar la carga utilizaremos 10 threads, cada uno de ellos responsable de insertar 100 millones de filas:
Para la carga no hemos planteado ningún tipo de optimización especial, simplemente hemos insertado las filas en batches de 1 millón de filas por lo que los tiempos de carga no serán especialmente buenos.
La estructura de la tabla utilizada es muy sencilla, con un identificador, una columna de texto para darle un poco de volumen y una columna filter que utilizaremos posteriormente para particionar:
Para realizar la carga lo que haremos es insertar valores aleatorios para el payload y el valor del filtro será un valor entre 0 y 9 en base a un cálculo módulo 10 basado en el payload aleatorio de entre 0 y 99 caracteres:
Siguiendo este esquema hemos necesitado 76 minutos para cargar 1000 millones de filas (13 millones de filas por minuto). La carga se ha hecho directamente al columnar en un formato comprimido y por tanto hemos castigado bastante la CPU al tratarse de una máquina con solo 8 cores que han estado trabajando “a tope” durante esos 76 minutos:
Una vez cargados los datos, ¿qué ocurre con la velocidad de lectura tras la carga?
Cuando tenemos un DW uno de los objetivos es poder tener una velocidad de lectura aceptable desde disco para que el rendimiento de los scans no se degrade en exceso.
Por ejemplo, si trabajaramos en on-premise en un portátil con un SSD NVME podemos obtener velocidades típicas de alrededor 2.5 GB/s haciendo un scan de una tabla de SQL Server:
En el caso de la VM de 8 cores utilizada, de la serie L que está optimizada para estos escenarios de alta demanda de entrada/salida, obtenemos valores de aproximadamente 1.6 GB/s que no está mal para tratarse de una máquina virtual de 8 cores en cloud:
La primera de las consultas que lanzaremos sobre los datos cargados es una consulta que intenta buscar la menor de las cadenas generadas aleatorias:
Esta consulta fuerza la lectura de la columna de texto, su descompresión y su filtrado. Durante este tipo de operación saturamos tanto la CPU antes que la entrada/salida, que queda a aproximadamente 1/3 de su capacidad:
El tiempo total de ejecución ha sido de 991 segundos con un consumo de CPU de 6859 segundos (ratio 7 a 1):
También podemos lanzar la misma consulta con un filtro que seleccionará aproximadamente 1/10 parte del total de filas a procesar por lo que se trabajará sobre aproximadamente 100 millones de filas:
El tiempo en este caso se reduce a únicamente 1/3 del total y ya el cuello de botella deja de estar en la CPU (ratio 4 a 1) y pasa a estar en la IO:
Dividiendo el desayuno en bocados más pequeños
A continuación vamos a crear una función de particionado y un esquema de particionado para particionar en 10 particiones de aproximadamente 100 millones de filas cada una:
Crearemos una nueva tabla particionada y copiaremos los 1000 millones de filas partición a partición (las lanzaremos en paralelo):
De nuevo durante esta operación la CPU nos hará de cuello de botella al tener que descomprimir para extraer las filas y tener que volver a comprimir los datos al escribir al nuevo índice columnar particionado:
Esto no es algo que nos sorprenda especialmente, ya que los índices columnares ofrecen importantes ventajas de rendimiento pero tienen como contrapartida que el consumo de CPU suele ser elevado. Por decirlo de alguna forma, permiten aplicar “fuerza bruta” a este tipo de operaciones lo cual puede requerir un dimensionamiento entre entrada/salida y CPU distinto al que estábamos acostumbrados con el almacenamiento rowstore.
Evaluando el impacto del particionado sobre SMP en las lecturas
Una vez la carga esté completada el siguiente paso será probar cuanto impacto tiene el particionado cuando filtramos por la columna de particionado.
Para ello volveremos a lanzar la consulta que lanzamos anteriormente sin particionado:
Podemos ver que en el plan de ejecución se aplica el filtro por partición, seleccionándose la segunda partición (la primera correspondería a filter=0):
Al usar particionado vemos que disminuye la presión que hacemos sobre disco, quedando en alrededor de unos 570 MB/s:
Esto es debido a que la CPU queda totalmente saturada al llegarle ya todas las filas “prefiltradas” y teniendo que trabajar más duro procesando sin parar todas las filas versus descartar 9/10 de las filas como hacíamos antes:
Debido a esto, el uso de IO y CPU que estamos haciendo es más eficiente y ello repercute en unos mejores tiempos tanto de consumo de CPU (-50%), de IO (-90%) y ello repercute en una menor duración (-70%):
¿Qué ocurre con la carga de escritura al escalar nuestro SMP?
El siguiente paso sería realizar un escalado de los recursos de la máquina virtual hasta los 80 cores y volver a testear el comportamiento que tenemos con una máquina mucho más potente.
Como curiosidad respecto a los distintos problemas que pueden acarrear las cargas, si lanzamos decenas de procesos de carga concurrentes, conseguimos aumentar el uso de CPU y se distribuyen correctamente entre todos los cores:
Sin embargo en este caso con el diseño de la tabla basado en un identity nos encontramos con un cuello de botella a la hora de generar dichos valores y realmente en el momento que tenemos más de 16-20 procesos concurrentes ya el rendimiento no mejora prácticamente nada por esta razón. Por tanto, usar un identity no es una buena idea para escalar en rendimiento de escritura.
El tiempo de inserción de los 1000 millones de filas se redujo a algo menos de 30 minutos, algo no proporcional al aumento en 10 veces de los recursos de CPU. En este tipo de casos sería recomendable el uso de secuencias y la preasignación en bloques para cada grupo de datos, es decir, solicitarle a la secuencia “dame 1 millón de IDs” y ya usarlos internamente en el proceso como valores a insertar, sin depender de la secuencia ya para la inserción durante el proceso.
Analizando el impacto en las lecturas tras escalar nuestro SMP
Volviendo al tema de las consultas vamos a comprobar cual es el rendimiento tanto en la consulta no particionada como en la particionada.
Durante la ejecución de la consulta sobre la tabla sin particionar vemos que el uso de la CPU se distribuye entre todos los cores de forma bastante equilibrada:
La velocidad de lectura en este caso sube hasta 4 GB/s
Debemos tener en cuenta que SQL Server “dispara” el Soft Numa automáticamente al detectar más de 8 cores. En total tendremos 10 nodos numa de 8 cores desde el punto de vista de SQL Server:
Si dejamos el MAXDOP al valor por defecto nos encontramos que la ejecución usando todos los cores no es realmente muy eficiente. Con 80 cores la duración es 1/5 de la duración original (pese a haber aumentado 10X los cores):
Si bajamos a 40 cores por ejemplo nos encontramos que el rendimiento algo menos de 1/3 del tiempo que teníamos con 8 cores por lo que aumento de cores nos proporciona un aumento del rendimiento aunque no tanto como esperaríamos:
Si lanzamos la consulta filtrada con un máximo de 80 cores sobre la tabla particionada veremos que la duración se reduce a prácticamente 1/8 del tiempo original:
Si limitamos a 40 cores vemos que la duración se reduce prácticamente en la misma medida:
Si limitamos a 20 cores ya el tiempo se degrada más notablemente aunque sigue siendo 1/3 del tiempo original (debemos tener en cuenta que no solamente aumenta la CPU al escalar, también vemos como la IO en lecturas de la tabla nos sube de 1.6 GB/s hasta 4.5 GB/s):
Con estos resultados podemos ver que en este tipo de configuraciones SMP el escalado vertical es posible que venga bastante condicionado tanto al particionado usado así como la configuración de paralelismo que realicemos. También debemos considerar la capacidad que tenga el hardware subyacente (el host de la máquina virtual en este caso) de ser escalado de forma homogénea y suficiente. Es decir, es habitual que los escalados verticales solo nos permitan aumentar recursos en algunos aspectos pero no en todos (por ejemplo no en ancho de banda a memoria o no en ancho de banda de entrada/salida). Debido a ello y a las propias limitaciones de un sistema SMP, del impacto del NUMA, etc. lo habitual es encontrarnos con escalabilidades no lineales con el aumento de recursos en un SMP.
Conclusión
En esta primera parte hemos visto qué rendimiento podemos esperar de un sistema SMP y su comportamiento ante el particionado y el escalado vertical de recursos. Es cierto que estos resultados usando sistemas especializados SMP para DW podrían ser mejorados, pero hemos querido utilizar como ejemplo un sistema que estén al alcance de muchos bolsillos y esté disponible en un entorno de cloud público, que no requiera de la compra de hardware especializado.
Para aquellos que quieran ampliar información y estén interesados en sistemas SMP especializados para DW les recomiendo que accedan a la web de TPC y en el apartado de TPC-H analicen los reportes completos de las configuraciones hardware y software utilizadas en las pruebas. Los costes totales de estas soluciones no están al alcance de la mayoría de las organizaciones ya que son del orden de varios cientos de miles de euros. Por poner algún ejemplo, tenemos esta configuración de DELL con un coste de hardware más software de 500K: http://www.tpc.org/results/fdr/tpch/dell~tpch~3000~dell_poweredge_r7525~fdr~2021-07-03~v01.pdf o este de HP de 1 millón de euros: http://www.tpc.org/results/fdr/tpch/hpe~tpch~10000~hp_proliant_dl385_gen10_plus~fdr-2021-04-02~v01.pdf)
¿Necesitas sacar más partido a tus datos para impulsar tu proyecto?
Acelera tus procesos de Business Analytics. Toma mejores decisiones, optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica con nuestra consultoría en Business Intelligence.
Quiero Más Información