Calculando un grado de paralelismo “razonable”

SQL Server

http://blogs.solidq.com/es/sql-server/calculando-un-grado-de-paralelismo-razonable/

Un parámetro de configuración que genera habitualmente discusiones de lo más controvertidas es el grado máximo de paralelismo (max degree of parallelism). En este artículo vamos a ver las recomendaciones generales y también una aproximación a cómo podemos calcular este valor de una forma más empírica para nuestras consultas.

Para sistemas con la carga más típica que nos solemos encontrar (mayor parte de OLTP y algunas consultas analíticas) las recomendaciones generales son un buen punto de partida:

• En sistemas NUMA, no configurar un grado de paralelismo mayor que el número de cores físicos por nodo NUMA.
• No configurar el grado de paralelismo máximo por encima de 8.

También es importante que en el caso que nuestra instancia vaya a ser utilizada por software de terceros revisemos las recomendaciones del fabricante al respecto. Por poner un ejemplo habitual, Dynamics AX recomienda un grado de paralelismo 1. Al tratarse de un parámetro a nivel de instancia y no de base de datos deberemos buscar un valor de compromiso pensando en todas las aplicaciones que vayamos a ejecutar. En casos extremos tendremos que aislar en instancias independientes las distintas aplicaciones o, si disponemos de Enterprise Edition, podremos utilizar Resource Governor para controlar este parámetro. También es importante revisar el algoritmo utilizado por SQL Server para obtener el valor de paralelismo a utilizar que podemos encontrar en el siempre interesante blog de CSS SQL Escalation Services:

http://blogs.msdn.com/b/psssql/archive/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop.aspx



En otros casos nos puede interesar ajustar el grado de paralelismo más óptimo para un conjunto reducido de consultas. Para ello tendremos que tener en cuenta tanto la capacidad de aprovechamiento del paralelismo como también el grado de concurrencia que van a tener dichas consultas. Dicho de otra forma, una consulta que se ejecute en una ventana de mantenimiento y sin concurrencia podrá ser más “avariciosa” en el consumo de recursos que una que se ejecute durante la jornada laboral con  una concurrencia media de 5 consultas concurrentes. Una posible técnica para evaluar cuál sería ese grado de paralelismo apropiado sería lanzar la consulta con distintos grados de paralelismo y medir los consumos de recursos, esperas, etc. que se generan. Por ejemplo podemos utilizar una sesión de xevents para capturar las esperas por sesión y una traza de Profiler para capturar las ejecuciones con distinto grado de paralelismo.

Imaginemos que queremos analizar el comportamiento de la siguiente consulta lanzada sobre “tablas engordadas” de AdventureWorks (Script de Adam Machanic disponible en http://sqlblog.com/blogs/adam_machanic/attachment/39106.ashx):
 

1 2 3 4 5 6 7 8

SELECT     BP.ProductID,     cnt = COUNT_BIG(*) FROM dbo.bigProduct AS BP JOIN dbo.bigTransactionHistory AS BTH ON BTH.ProductID = BP.ProductID GROUP BY BP.ProductID ORDER BY BP.ProductID

Un script que nos puede servir para capturar las esperas para distintos grados de paralelismo (de 8 a 1 en este ejemplo) sería el siguiente:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'eventos_session')     DROP EVENT SESSION eventos_session ON SERVER GO DECLARE @create nvarchar(max) = ' CREATE EVENT SESSION eventos_session ON SERVER ADD EVENT sqlos.wait_info     (WHERE sqlserver.session_id = ' + convert(nvarchar(max),@@spid) + ') ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS); ' EXEC (@create) GO declare @iterar int = 5 declare @query nvarchar(max) = ' IF OBJECT_ID(''tempdb..#temp'') IS NOT NULL DROP TABLE #temp SELECT     BP.ProductID,     cnt = COUNT_BIG(*) into #temp FROM dbo.bigProduct AS BP JOIN dbo.bigTransactionHistory AS BTH ON BTH.ProductID = BP.ProductID GROUP BY BP.ProductID ORDER BY BP.ProductID ' declare @sql nvarchar(max) declare @maxdop int = 8 declare @fecha datetime declare @fecha2 datetime IF OBJECT_ID('tempdb..#esperas') IS NOT NULL DROP TABLE #esperas create table #esperas ( maxdop int, duracion_ms int, wait_type varchar(max), total bigint, total_wait_time_ms bigint, total_resource_wait_time_ms bigint, total_signal_wait_time_ms bigint ) while (@maxdop >= 1) begin ALTER EVENT SESSION eventos_session ON SERVER STATE = START; set @fecha=getdate() set @sql= @query + ' OPTION (RECOMPILE, QUERYTRACEON 8649, MAXDOP ' + convert(varchar(max),@MAXDOP) + ')' exec(@sql) set @fecha2=getdate() IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t SELECT CAST (target_data AS XML) xml into #t FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address WHERE s.name = 'eventos_session' AND t.target_name = 'ring_buffer' INSERT INTO #esperas select @maxdop, datediff(ms, @fecha, @fecha2) duracion_ms, table2.wait_type, count(*) total, SUM (convert(bigint,table2.duration)) AS total_wait_time_ms, SUM (convert(bigint,table2.duration))-SUM (convert(bigint,table2.signal_duration)) AS total_resource_wait_time_ms, SUM (convert(bigint,table2.signal_duration)) AS total_signal_wait_time_ms FROM -- Credit to Jonathan Kehayias for the xevent extraction query template (   SELECT  XEvent.query('.') XEvent from #t t CROSS APPLY t.xml.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   ) table1 CROSS APPLY (  SELECT *    FROM    (   -- Get the Event Name    SELECT    'event_name' AS 'name',    RTRIM(LTRIM(XEvent.value('(event/@name)[1]', 'nvarchar(max)'))) AS value    UNION ALL    -- Get the Event Timestamp    SELECT    'timestamp' AS 'name',    RTRIM(LTRIM(XEvent.value('(event/@timestamp)[1]', 'nvarchar(40)'))) AS value    UNION ALL    -- Get the Event data columns    SELECT    RTRIM(LTRIM(XData.value('@name[1]','nvarchar(max)'))) AS 'name',    RTRIM(LTRIM(COALESCE(NULLIF(XData.value('(text)[1]','nvarchar(max)'), ''), XData.value('(value)[1]','nvarchar(max)')))) AS 'value'    FROM XEvent.nodes('event/data') AS Data (XData)    UNION ALL    -- Get the Event action columns    SELECT    XData.value('@name[1]','nvarchar(max)') AS 'name'    ,COALESCE(NULLIF(XData.value('(text)[1]','nvarchar(max)'), ''), XData.value('(value)[1]','nvarchar(max)')) AS 'value'    FROM XEvent.nodes('event/action') AS Data (XData)   ) AS tab    PIVOT    (   MAX(value)    -- Specify columns for PIVOT Operation    FOR name IN    (   [event_name], [timestamp], [wait_type], [opcode], [duration], [signal_duration]    )    ) AS pvt ) AS table2 WHERE table2.[opcode] = 'End' and table2.wait_type in ('SOS_SCHEDULER_YIELD','CXPACKET') GROUP BY table2.wait_type ALTER EVENT SESSION eventos_session ON SERVER STATE = STOP;      -- Bajamos de 2 en dos y probamos por último en serie, maxdop=1 SET @maxdop = @maxdop -2 if @maxdop = 0 set @maxdop=1 END GO select * from #esperas

En el script básicamente componemos la sesión de eventos extendidos de forma dinámica (para poder filtrar por la sesión actual) y luego iteramos ejecutando la consulta con distintos niveles de paralelismo máximo. Como salida de este script obtendremos una salida similar a esta:

maxdop	duracion_ms	wait_type	total	total_wait_time_ms	total_resource_wait_time_ms	total_signal_wait_time_ms
8	2130	CXPACKET	377	4084	4058	26
8	2130	SOS_SCHEDULER_YIELD	123	0	0	0
6	2373	CXPACKET	401	4593	4591	2
6	2373	SOS_SCHEDULER_YIELD	98	0	0	0
4	2730	CXPACKET	272	4998	4984	14
4	2730	SOS_SCHEDULER_YIELD	226	0	0	0
2	3686	CXPACKET	33	2476	2470	6
2	3686	SOS_SCHEDULER_YIELD	390	0	0	0
1	5506	SOS_SCHEDULER_YIELD	495	0	0	0

Como vemos en el caso de planes paralelos tenemos entre 2 y 4 segundos de tiempos “perdidos” en sincronización entre threads y el propio tiempo del thread 0, “padre” o controlador del resto. Sería ideal que pudiéramos obtener desglosada esta espera de CXPACKET de forma que nos quedáramos solo con el tiempo de sincronización entre threads excluyendo el tiempo del thread padre.

Por otra parte con Profiler podemos obtener las distintas ejecuciones, sus tiempos y consumos agregados de CPU:



Si comparamos las duraciones reales con las ideales podemos ver como la “brecha” es menor cuando el paralelismo es 2 que cuando éste es 4,6 u 8.



Si comparamos el consumo de CPU por thread vemos un comportamiento similar entre el valor ideal (reparto perfecto y sin costes de sincronización) y la realidad:



También podemos observar el porcentaje de mejora (reducción en % de la duración) vs el porcentaje de aumento de consumo de recursos (aumento en % de milisegundos de CPU) :



Llegados a este punto deberíamos plantearnos cuantos cores tenemos disponibles, cuanta concurrencia de este tipo de consultas vamos a tener y cuantos cores queremos dejar libres para el resto de carga del servidor. En este caso no tenemos “regresiones” ya que aunque aumentemos el paralelismo hasta 8 siempre obtenemos alguna mejora. En algunos casos, normalmente cuando excedemos el tamaño del nodo NUMA en lo que al número de cores respecta, podemos encontrarnos con “saltos” donde pasar de por ejemplo 8 cores a 9 suponga un aumento en el tiempo de respuesta. En general la curva de la tendencia es similar a la que tenemos aunque con un grado de “aplanamiento” distinto en la reducción del tiempo total de ejecución. Existen consultas que, en un hardware concreto, escalan muy bien hasta el 100% de los cores disponibles y otras que a partir de unos pocos cores ya no resultan más eficientes o incluso se vuelven menos eficientes cuantos más threads se involucran.

En resumen, si esta consulta concreta se fuese a ejecutar sin concurrencia y con todos los recursos disponibles para su ejecución optaríamos por el grado de paralelismo 8, ya que es el que obtiene un mejor tiempo de respuesta. Sin embargo si el grado de concurrencia fuese a ser notable, por ejemplo 10 queries concurrentes en un sistema con 32 cores, optaríamos por un grado de paralelismo de 2, con el que se podría llegar a consumir hasta 20 cores en total. Si necesitáramos consumir menos de esos 20 cores concurrentemente o el número de queries a soportar fuese mayor optaríamos por el plan serie por su mayor eficiencia desde el punto de consumo de CPU.

Por último no podemos olvidar que existen factores adicionales que tenemos que tener en cuenta ya que pueden darnos resultados muy distintos durante las pruebas y cuando estemos en un escenario de producción. Un caso habitual que nos encontramos en nuestras consultorías es el impacto de mezclar cargas analíticas con OLTP. Los continuos “picotazos” de CPU que las consultas OLTP demandan de los distintos cores hacen que el tiempo total y las diferencias de tiempos entre threads se acrecienten, haciendo los planes paralelos mucho menos eficientes que en un servidor sin carga. En esos casos una buena herramienta es forzar el particionado de la carga mediante resource governor, mapeando nodos numa a puerto TCP, etc.  de forma que dediquemos un conjunto de cores del servidor para la carga OLTP exclusivamente y el resto para las consultas pesadas de tipo analítico.