Una de las tendencias en las plataformas de datos modernas es la virtualización de datos. De forma muy resumida la virtualización de datos nos permite acceder a los datos sin realizar un movimiento/copia de datos de forma transparente, de forma que para nuestras consultas el acceso a dicha información se realiza de la misma forma que a cualquier otra tabla dentro de nuestro SQL Server.
En SQL Server la funcionalidad que nos permite la virtualización de datos se denomina PolyBase. PolyBase apareció en SQL Server 2016 y ha ido mejorando en las sucesivas versiones SQL Server 2017 y SQL Server 2019. Esta funcionalidad está disponible también en la plataforma analítica en cloud Azure Synapse Analytics (antes conocida como Analytics Platform System y previamente a ello conocida como Parallel Data Warehouse) así como en la solución SQL Server Big Data Cluster (sobre Kubernetes on-premise o cloud).
En este artículo vamos a centrarnos en mostrar cómo es el funcionamiento de la configuración escalable (scale out) de PolyBase sobre SQL Server 2019. Para ello comenzaremos preparando las instancias que van a participar en dicha configuración. Durante la instalación es necesario seleccionar el componente PolyBase ya que no se instalará por defecto. Podremos seleccionar opcionalmente también el conector para HDFS:
También se nos solicitará que indiquemos si vamos a querer una configuración standalone (1 único nodo) o una scale-out (n nodos):
El rol de head node en un entorno scale-out requiere de licenciamiento Enterprise mientras que el resto de nodos Compute pueden utilizar licencia standard. También debemos tener en cuenta que actualmente la característica no puede instalarse en más de una instancia por máquina:
Una vez hemos finalizado la instalación veremos que tenemos un par de servicios extra instalados llamados SQL Server PolyBase Engine y SQL Server PolyBase Data Movement:
Para una configuración scale-out sobre instancias SQL Server sobre Windows debemos tener en cuenta que necesitamos utilizar una cuenta de dominio y que todos los nodos pertenezcan al mismo. Si tenemos problemas al levantar alguno de los servicios debemos acudir a una carpeta de logs de PolyBase que se crea dentro de la carpeta de logs de SQL Server para obtener más información:
En mi caso por “pasarme de listo” y haber clonado los nodos me encontré con el problema que, pese a haber renombrado el nombre de la instancia (sp_dropserver y sp_addserver), los servicios de PolyBase en el nodo renombrado no eran capaces de arrancar al intentar conectar con el nombre antiguo:
Ni corto ni perezoso me puse a renombrar en el registro de Windows las entradas correspondientes y a trastear en ciertos ficheros configuración (.config y .xml localizados en C:\Program Files\Microsoft SQL Server\MSSQL15. SQL2019\MSSQL\Binn\PolyBase\) tras lo cual pude conseguir que volviera a funcionar… pero no es algo que pueda recomendar a nadie:
Este bug está documentado pero parece que no resuelto por ahora: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896768-following-a-rename-of-computer-cannot-start-sql-po El workaround recomendado es desinstalar y reinstalar la feature de PolyBase en los nodos afectados.
Una vez tengamos al menos dos nodos instalados y con los servicios funcionando podremos comenzar la configuración. El primer paso es activar la funcionalidad, que viene desactivada por defecto excepto en Big Data Clusters:
exec sp_configure 'PolyBase enabled', 1; RECONFIGURE;
Si conectamos a las instancias que hemos configurado PolyBase podremos ver que disponen de un conjunto de bases de datos preconfiguradas, con muchas tablas con el prefijo “pdw” (parallel datawarehouse) y conceptos muy similares a los que tenemos en Azure Synapse. No debemos olvidar que el código base de SQL Server es compartido entre los distintos “sabores” de la plataforma de datos por lo que tiene sentido encontrarnos este tipo de referencias.
El siguiente paso es configurar el nodo WS2019-2 como “esclavo” de WS2019-1 para que actúe únicamente como nodo de computación. Para ello deberemos lanzar el siguiente comando en WS2019-2
-- head node machine name, head node dms control channel port, head node sql instance name EXEC sp_PolyBase_join_group 'WS2019-1', 16450, 'SQL2019';
Tras esta configuración se nos deshabilitará el engine de PolyBase, ya que no es necesario en los compute nodes (solo se necesita en el head), y se nos solicita reiniciar el servicio de Data Movement para que se reconfigure tras el cambio.
Conceptualmente la arquitectura quedará como en el siguiente diagrama, donde tendremos un nodo de control al que llegarán las consultas y tendrá el Engine de PolyBase y N nodos extra de compute (https://docs.microsoft.com/en-us/sql/relational-databases/PolyBase/PolyBase-scale-out-groups?view=sql-server-ver15):
Desde SSMS podemos comprobar también que la configuración es correcta:
A continuación, procederemos a crear una tabla externa que apunte en este caso a una instancia SQL Server 2017. Crearemos una base de datos “DataVirtualization”, crearemos la master key y crearemos un credencial para conectar a ella:
CREATE DATABASE DataVirtualization GO USE DataVirtualization GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strong_password' CREATE DATABASE SCOPED CREDENTIAL SqlServer2017Credential WITH IDENTITY = 'sqlusername', SECRET = 'strong_password'; El siguiente paso es crear un external data source que apunte a la instancia SQL Server 2017 utilizando el credencial que hemos creado previamente: CREATE EXTERNAL DATA SOURCE SQLServer2017 WITH ( LOCATION = 'sqlserver://HYPERV2:1433', PUSHDOWN = ON, CREDENTIAL = SQLServer2017Credential);
Por defecto SQL Server intentará realizar un PUSHDOWN de predicados salvo que configuramos a OFF dicha opción. Esto es debido a que normalmente filtrar y reducir en origen antes de mover los datos es la opción más eficiente.
Una vez tenemos el externa data source, crearemos la external table teniendo especial cuidado de que los tipos de datos coincidan (por ejemplo los collate de cada columna):
CREATE EXTERNAL TABLE dbo.Posts( [Id] [int] NOT NULL, [AcceptedAnswerId] [int] NULL, [AnswerCount] [int] NULL, [Body] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ClosedDate] [datetime] NULL, [CommentCount] [int] NULL, [CommunityOwnedDate] [datetime] NULL, [CreationDate] [datetime] NOT NULL, [FavoriteCount] [int] NULL, [LastActivityDate] [datetime] NOT NULL, [LastEditDate] [datetime] NULL, [LastEditorDisplayName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS, [LastEditorUserId] [int] NULL, [OwnerUserId] [int] NULL, [ParentId] [int] NULL, [PostTypeId] [int] NOT NULL, [Score] [int] NOT NULL, [Tags] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Title] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ViewCount] [int] NOT NULL) WITH ( LOCATION='StackOverflow2010.dbo.posts', DATA_SOURCE=SqlServer2017 );
El siguiente paso sería probar una consulta sencilla sobre la tabla externa:
Select count(*) from dbo.posts
Una vez comprobamos que funciona, vamos a analizar un poco más en qué se convierte exactamente cuando la lanzamos. Por una parte vemos que se implementa la llamada externa con un operador remote query:
Si trazamos desde el destino qué peticiones le llegan desde los nodos de PolyBase, vemos que tenemos unas primeras consultas orientadas a obtener los metadatos (incluyendo información de particionado) y a continuación una consulta similar a la original (interpretada):
Si en algún caso nos interesa, podemos forzar el pushdown o evitar su uso mediante el uso de una configuración OPTION en la consulta:
SELECT COUNT(*) FROM dbo.Posts OPTION (FORCE EXTERNALPUSHDOWN); SELECT COUNT(*) FROM dbo.Posts OPTION (DISABLE EXTERNALPUSHDOWN);
Si probamos esa opción de deshabilitar el pushdown en nuestra consulta vemos que lo que obtenemos es una consulta que devuelve tantas filas como tenemos en la tabla, pero sin devolver todas sus columnas, y el conteo se realizará ya en el nodo de PolyBase:
En el caso de un conteo normalmente es siempre más beneficioso realizarlo en origen, pero puede haber casos donde el origen está “escaso” de CPU y si enviamos vía pushdown otras operaciones más pesadas, como agregados, puede resultar menos eficiente.
Por forzar un poco la situación, vamos a lanzar una consulta un tanto “absurda” que realice un producto cartesiano de la tabla posts consigo misma y además con las primeras 10 filas de nuevo:
select top 10 p1.favoritecount, count_big(*) from dbo.posts p1, dbo.posts p2,(select top 10 * from dbo.posts) p3 group by p1.favoritecount order by count_big(*) desc
Si lanzamos la consulta directamente en el origen, vemos que necesitamos 21 segundos de CPU y nos lleva 26 segundos devolver la respuesta:
Si lo lanzamos a través de PolyBase vemos que solo necesitamos la mitad de tiempo, 13 segundos:
En la traza del origen de datos, vemos que PolyBase ha decidido leer los datos en dos peticiones, sin realizar los cálculos en origen, y luego procesar el resultado por sí mismo, obteniendo una mejora de rendimiento respecto a lo que sería ejecutar directamente en el origen:
Este es un ejemplo de la potencia que tiene este motor para descomponer la consulta en partes más sencillas y ejecutarla de una forma más óptima. Interpretar estos planes resulta más complicado que los planes tradicionales de SQL Server ya que no disponemos de un interfaz gráfico que nos ayude (como sí disponemos en Synapse). Podemos extraer del plan de ejecución que nos muestra el head-node la parte XML correspondiente y hacer un análisis parcial, como por ejemplo el movimiento y distribución usados:
Si esta funcionalidad se habilita en SSMS deberíamos poder usar EXPLAIN como en Synapse (https://docs.microsoft.com/en-us/sql/t-sql/queries/explain-transact-sql?view=azure-sqldw-latest) y obtendríamos una visualización gráfica (https://azure.microsoft.com/en-us/blog/unlock-query-performance-with-sql-data-warehouse-using-graphical-execution-plans/):
Otra optimización importante que podemos obtener en estos planes es la extracción de datos que estén particionados en paralelo desde varios nodos. Cada compute node generará una parte proporcional de readers para atacar las particiones que le correspondan en paralelo:
Vamos a realizar una prueba creando particionado para nuestra tabla Posts en origen. Utilizaremos un particionado sencillo, en base a rangos de IDs:
USE [StackOverflow2010] GO BEGIN TRANSACTION CREATE PARTITION FUNCTION [test](int) AS RANGE LEFT FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000') CREATE PARTITION SCHEME [test] AS PARTITION [test] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]) ALTER TABLE [dbo].[Posts] DROP CONSTRAINT [PK_Posts__Id] WITH ( ONLINE = OFF ) ALTER TABLE [dbo].[Posts] ADD CONSTRAINT [PK_Posts__Id] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [test]([Id]) COMMIT TRANSACTION
Una vez tenemos la tabla particionada, vamos a lanzar de nuevo nuestro conteo y vamos a ver en qué se convierte en la traza en el SQL Server 2017:
Podemos ver que el tiempo baja a aproximadamente la mitad de los valores que obteníamos antes (casi 6 segundos). En la traza lo que veremos son 6 peticiones, una por partición, que se lanzan repartidas 50-50 entre los dos nodos disponibles, siendo el tiempo total prácticamente el tiempo de la partición más lenta (la 3):
Hay bastantes escenarios donde dividir una petición en N peticiones por partición puede ser beneficioso. Uno de ellos es cuando tenemos latencia entre la fuente de datos y nuestro servidor desde el que leemos la información. Cada vez vemos más problemas en entornos híbridos cuando se intenta hacer una carga desde un servidor a otro (uno on-premise y el otro en cloud, o ambos en cloud pero en distintos proveedores/datacenters) pero utilizando únicamente 1 conexión y un único thread.
En esos escenarios la latencia penaliza mucho y limita la velocidad máxima que podemos obtener. Cuando tratamos estos temas con clientes suelo apoyarme en esta tabla como referencia: https://docs.microsoft.com/en-us/azure/expressroute/expressroute-troubleshooting-network-performance#latencybandwidth-results
Cuando nuestros procesos de carga se han diseñado y probado en escenarios con latencia LAN local, inferior a 1 ms, no han tenido que adaptarse para esta limitación de ancho de banda por sesión. En estos casos la técnica más recomendable es precisamente aplicar esa división de la consulta original en N bloques y lanzarlos en paralelo desde un paquete de SSIS.
Por último, vamos a ver la potencia de ambos factores, el uso del paralelizado y la mejora que aporta el motor. Modificaremos la consulta anterior y vamos añadirle un cálculo que añada más carga de CPU, una desviación típica, así como ajustar los valores de los TOP para que tenga una duración razonable:
set statistics time on select top 100 p1.favoritecount, count_big(*) , STDEV(p1.favoritecount) from dbo.posts p1, (select top 200000 * from dbo.posts) p2 group by p1.favoritecount order by count_big(*) desc
Cuando la lanzamos, vemos que llegamos a usos de CPU elevados en ambos nodos durante varios segundos de forma casi simultánea, demorándose la consulta 46 segundos:
Vamos a añadir otros dos nodos compute al scale-out PolyBase siguiendo los mismos pasos que dimos anteriormente y volveremos a lanzar esta misma consulta. Con esto pasaríamos de 8 cores disponibles para cómputo a 16 cores en total. Al volver a lanzar la consulta lo que vemos es que no llegamos a saturar del todo cada uno de los nodos (aunque sí se reparte la carga equilibradamente) y el tiempo de ejecución baja de 46.7s a 27.3s, un 41% de mejora duplicando la cantidad de CPU y de nodos disponibles. Debemos tener en cuenta que ni el número de particiones disponibles en este ejemplo es elevado ni tampoco están perfectamente balanceadas, por lo que el reparto tampoco ha sido perfecto entre el número de nodos de cómputo:
El siguiente gráfico resume los datos obtenidos en las distintas consultas y configuraciones:
Cuando el coste de la operación es pequeño, las diferencias entre realizar pushdown o no o incluso el usar 1 o más nodos son mínimas. Esto es el caso del count sencillo, conde contar las filas en origen y enviar el resultado o enviar N filas y devolver N en destino puede ser algo bastante indiferente desde el punto de vista de rendimiento.
Cuando la operación ya requiere mayor cómputo, como un producto cartesiano con agregados y conteos, la diferencia de PolyBase ya es sustancial. En un escenario de alto uso de CPU es donde encontramos una reducción del tiempo de respuesta del 50% con dos nodos y otro 40% adicional acumulativo en la configuración con 4 nodos lo que equivale a un 70% de reducción sobre el tiempo total original.
Pese a todo lo comentado en este artículo solo hemos raspado un poco la superficie de PolyBase y mostrado su funcionamiento básico. Si queremos profundizar más y comprender mejor qué está ocurriendo por debajo cuando lanzamos este tipo de operaciones distribuidas existen varias DMVs muy similares a las que tenemos en Synapse/APS/PDW que nos informan de los distintos pasos, tipos de operaciones ejecutadas, sus duraciones, etc.
En conclusión, PolyBase añade a SQL Server un nuevo engine optimizado para operaciones distribuidas con datos remotos. PolyBase nos aporta mejores planes de ejecución y escalado entre varios nodos así como el aprovechamiento de forma transparente del particionado nativo de las fuentes de datos. Si en tu entorno necesitas virtualización de datos y/o distribuir el coste de consultas pesadas entre varios nodos de computación PolyBase Scale-out puede llegar a ser una excelente opción para ti.
