Poco a poco, a medida que el almacenamiento se vuelve más rápido, va popularizándose el almacenamiento SSD local, etc. los tiempos de acceso a disco van bajando sustancialmente. El máximo exponente en este sentido lo encontramos en sistemas con SSDs Optane que se caracterizan por tener unas latencias de lectura/escritura mucho más bajas que los SSD tradicionales y además ir directamente conectados al bus PCIe:
Como podemos ver en las especificaciones, tenemos latencias típicas de lectura de ~10 us, es decir de 0.01 ms. En este contexto, o cuando las operaciones SQL no requieran leer/escribir de disco, una latencia de red de “< 1ms” puede convertirse en la mayor espera que sufra nuestro servidor. De hecho, hasta hace poco, cuando un sistema tenía como espera mayoritaria ASYNC_NETWORK_IO normalmente era un “muy buen síntoma” ya que implicaba que no teníamos “esperas significativas” en los sistemas que suelen ser cuello de botella. Esto poco a poco esto va cambiando y nos vamos encontrando entornos donde el rendimiento de la red empieza a ser el cuello de botella principal y la razón de las lentitudes experimentadas bien por los usuarios (especialmente usuarios de negocio) o bien por las aplicaciones (especialmente ETLs y similares).
Los que sigáis este blog recordaréis que hemos comentado este tema en otros posts (RBAR: ‘Row By Agonizing Row’ y La latencia, el archienemigo de los entornos Hybrid Cloud) sin embargo en este vamos a tratar bien aplicaciones que requieren latencias ultra bajas, o bien procesos que, por su diseño RBAR sufren mucho cuando esta latencia aumenta. En estos casos cada microsegundo cuenta, y deberemos optimizar configuraciones a nivel de BIOS, hypervisor, OS, red, etc. si queremos que el rendimiento sea el óptimo.
Desde el punto de vista de red, desgraciadamente, vemos que muchas empresas aún siguen estancadas en circuitería de 1 Gbps. En algunos casos con teams/agregaciones de 2/4 puertos para alcanzar los 2-4 Gbps. El problema de estas agregaciones es que no siempre son “utilizables” de forma sencilla. Es decir, nos proporcionan mayor disponibilidad, mayor ancho de banda total, etc. pero para una sola conexión TCP en muchas ocasiones nos encontraremos limitados el rendimiento de 1 solo puerto a 1 Gbps. En aquellas donde ya se utiliza circuitería a 10 Gbps tampoco es raro encontrarnos con otras limitaciones, como el rendimiento del filtrado del firewall, o un switch “legacy” que conecta a algunos clientes a 1 Gbps o incluso a 100 Mbps.
Ligada al ancho de banda máximo suele ir también la velocidad de conmutación, enrutado, etc. por lo que no solamente estaremos limitados en el throughput, sino también en la latencia que podamos obtener punto a punto. Por ello, vamos a realizar unas pruebas de enfoque RBAR, con loops y cursores locales, sobre varios procesadores y veremos cómo se comportan cuando añadimos la red por medio. Adicionalmente, realizaremos pruebas con máquinas virtuales vs físicas y con máquinas virtuales afinitizadas en la misma máquina versus máquinas en hosts distintos para entender mejor el impacto de cada decisión. Todos los tests los repetiremos tres veces y mostraremos el valor medio obtenido.
Lo primero que haremos es obtener una referencia sintética del rendimiento single thread, que va a ser el dominante a nivel de CPU en estos procesos orientados a cursor/loop. Podemos ver que tenemos diferencias sustanciales entre distintos equipos en función de las generaciones de procesadores y si se tratan de procesadores desktop o server:
Si mostramos también las frecuencias máximas de trabajo vemos claramente la correlación existente, lo cual muestra claramente que no podemos sustituir un core de alta frecuencia de trabajo por “varios” con menor frecuencia sin consecuencias:
Es muy importante tener en cuenta estos factores ya que no es raro encontrarnos con procesos desarrollados y testeados en equipos desktop, con procesadores con mucha frecuencia, que cuando llegan al entorno de QA, o directamente a producción, tienen duraciones mucho mayores de las esperadas. Dicho de otra forma, no esperemos que el servidor de producción vaya a ser más rápido que nuestro portátil o equipo de escritorio ya que muchas veces será más bien lo contrario.
A continuación, vamos a analizar el rendimiento en dos casos típicos que en T-SQL van muy ligado al rendimiento monothread. Por una parte, los loops necesarios en muchos joins, key lookups, etc. y por otra un cursor “vacío” que solo itera, para ver el rendimiento simplemente de la propia estructura de cursor fila a fila.
Para ello vamos a utilizar un par de scripts sencillos, el primero para simular los loops en monothread y el segundo con un cursor forward only:
-- Loop join test, 1 billion rows
set statistics time on
select count_big(*) from (select top 1000000000 t1.object_id from sys.objects t1,sys.objects t2,sys.objects t3,sys.objects t4, sys.objects t5, sys.objects t6) a option (maxdop 1)
-- Cursor test, only 10 million rows
SET NOCOUNT ON
set statistics time off
DECLARE test_cursor CURSOR
READ_ONLY
FOR select top 10000000 t1.object_id from sys.objects t1,sys.objects t2,sys.objects t3,sys.objects t4, sys.objects t5, sys.objects t6 option (maxdop 1)
DECLARE @object_id int
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @object_id
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @object_id=@object_id
END
FETCH NEXT FROM test_cursor INTO @object_id
END
CLOSE test_cursor
DEALLOCATE test_cursor
GO
Estos scripts se ejecutarán de forma local, es decir, de momento no interviene la red para nada, solamente queremos ver la capacidad de estos procesadores y las diferencias entre físico y virtual. Comenzaremos por el loop donde podemos ver que de nuevo tenemos una correlación, inversa en este caso, entre la frecuencia máxima y la duración:
Podemos apreciar que en este tipo de operaciones la frecuencia marca la mayor parte de las diferencias, seguida de una relativamente pequeña penalización por la virtualización (5-10%).
A continuación, mostramos los resultados del test de cursor, donde podemos ver esta misma correlación inversa entre la frecuencia máxima y la duración:
En este caso del cursor podemos ver que la “pendiente”, las diferencias entre un tipo de procesadores y otros, así como virtualizados o no, es considerablemente mayor. Si comparamos los extremos, vemos que llegamos a más que triplicar los tiempos mientras que en el caso del loop no llegábamos (por poco) a duplicar en el peor caso. El impacto de la virtualización en este tipo de cargas basada en cursores puede alcanzar hasta un 30% en los entornos server.
Creo que nunca está de más que recordemos que los cursores deben ser la última alternativa dentro de un lenguaje orientado a conjuntos como es el T-SQL. Desgraciadamente aún los vemos utilizados de forma bastante frecuente en entornos OLTP para la implementación de procesos reutilizando lógica existente. Por ejemplo, en un proceso de cierre mensual, lanzar 100 mil veces y en serie un procedimiento almacenado que procesa una factura individualmente.
A continuación, vamos a realizar pruebas similares pero incorporando un componente de red. Para ello vamos a utilizar los dos escenarios más extremos ya que el número de combinatorias entre todos ellos sería muy elevado. Para evitar que el cargar un grid en Management Studio o el volcado a disco ralentice artificialmente el proceso, configuraremos que se descarten los resultados al recibirlos en el cliente:
En vez de realizar el sumatorio de 1000 millones de filas, vamos a enviarlas del cliente al servidor, como si quisiéramos hacer el cálculo en el cliente. En el caso del cursor, en vez de simplemente hacer un SET de una variable revolveremos con un SELECT el valor de dicha variable:
-- Loop join test client-server, 1 billion rows
set statistics time on
select top 1000000000 t1.object_id from sys.objects t1,sys.objects t2,sys.objects t3,sys.objects t4, sys.objects t5, sys.objects t6 option (maxdop 1)
-- Cursor test, only 10 million rows
SET NOCOUNT ON
set statistics time off
DECLARE test_cursor CURSOR
READ_ONLY
FOR select top 1000000 t1.object_id from sys.objects t1,sys.objects t2,sys.objects t3,sys.objects t4, sys.objects t5, sys.objects t6 option (maxdop 1)
DECLARE @object_id int
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @object_id
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
select @object_id
END
FETCH NEXT FROM test_cursor INTO @object_id
END
CLOSE test_cursor
DEALLOCATE test_cursor
GO
La ejecución media con estos cambios podemos ver que se resiente de forma considerable en todos los escenarios respecto a los tests anteriores debido a la incorporación de la comunicación cliente-servidor. Sin embargo, si analizamos los datos encontraremos algunas curiosidades, que confirman que el rendimiento depende de muchos factores, incluso del orden de dichos factores.
Si analizamos las duraciones del primer test, donde devolvemos 1000 millones de filas entre un cliente y el servidor vemos lo siguiente:
De estos resultados podemos extraer varias conclusiones:
- El uso de una red Infiniband versus una Ethernet de 1 Gbps reduce el tiempo de ejecución en aproximadamente un 10% de media.
- La duración parece condicionada más fuertemente por la velocidad del cliente a la hora de “absorber” las filas generadas por el SQL Server. Los menores tiempos los obtenemos cuando el procesador más rápido hace de cliente (sea cliente físico o virtual).
- Cuando el cliente y el servidor están en el mismo host (físico o virtual) los resultados dependen fuertemente del rendimiento del procesador, por lo que no siempre es la mejor opción mover “el cliente” al servidor si éste no es suficientemente rápido (comparado con el cliente).
Si analizamos los resultados del segundo test, donde devolvemos 10 millones de filas en 10 millones de resultsets entre un cliente y un servidor, vemos lo siguiente:
De estos resultados podemos extraer varias conclusiones:
- Cuando el volumen de filas disminuye (pasamos de 1000 millones a 10 millones) la mejora del Infiniband respecto al Ethernet de 1 Gbps, aunque sigue existiendo, se reduce del 10% anterior a un 2% únicamente.
- La duración sigue condicionada fuertemente por el rendimiento del cliente, siendo claramente más lenta la operación (alrededor de 600 segundos vs 350 segundos) cuando el cliente ejecuta en un procesador lento.
- Cuando el cliente y el servidor están en el mismo host (físico o virtual) los resultados dependen fuertemente del rendimiento del procesador, por lo que no siempre es la mejor opción mover “el cliente” al servidor si éste no es suficientemente rápido.
Otro factor que podemos analizar es el impacto si el cliente se encuentra virtualizado respecto a un cliente físico (mismo procesador, pero físico vs virtual). En todos los casos existe una penalización aunque de distinto grado. En el caso de la operación con 1 billón de filas en 1 resultset cuando el servidor es rápido y el cliente es lento tenemos la mayor penalización de aproximadamente 0.03 ms por fila (30s extra al tiempo total). Cuando el servidor es proporcionalmente más lento que el cliente, esta penalización por la virtualización baja hasta 0,0025 ms por fila (~2.5 segundos extra en total).
Cuando hablamos de un número elevado de resultsets, ocurre justo lo contrario. En tiempo absoluto por fila se añade menos milisegundos cuando el servidor es rápido y el cliente es lento que cuando ocurre lo contrario. La latencia añadida es muy similar tanto en el caso Ethernet de 1 Gbps que en el caso de Infiniband, lo que lleva a pensar que es un efecto derivado de la virtualización de los interfaces de red.
A la vista de estos resultados podría parecer que está poco justificado el uso de adaptadores de alta velocidad, ya que el impacto al virtualizar es similar y en el mejor de los escenarios solo ganamos un 10% de rendimiento. Sin embargo, existen muchos escenarios donde sí son claramente mucho más rápidos, por ejemplo, cuando el tamaño de las filas aumenta (algo típico en entornos DW por ejemplo). Por tener alguna referencia, lanzaremos la primera consulta modificada para que devuelva 8 KB por fila, 8 GB de datos en total:
-- 1 million rows, 8 KB per row = ~8 GB of data set statistics time on select top 1000000 convert(char(8000),'a') from sys.objects t1,sys.objects t2,sys.objects t3,sys.objects t4, sys.objects t5, sys.objects t6
Si observamos la velocidad alcanzada vemos que la tarjeta de 1 Gbps claramente nos genera cuello de botella. Cuando utilizamos la Infiniband de nuevo nos encontramos con que el rendimiento del cliente, el que consume los datos, es determinante. Cuando el procesador lento consume los datos y el rápido los envía no llegamos a 8 Gbps mientras que cuando es el rápido el que los consume superamos los 18 Gbps:
En conclusión, cuando tenemos procesos que van fila a fila tenemos muchos factores que nos pueden afectar el rendimiento. La latencia de red es uno de ellos, especialmente cuando es alta, pero cuando es baja no debemos olvidar que tendremos latencias adicionales por la virtualización o causadas por las diferencias en el ratio de “consumo de filas por segundo” derivadas del rendimiento monothread de los procesadores.
En función del tipo de carga, puede beneficiarnos “acercar” el cliente al servidor, situándolo en el mismo host o en la misma máquina virtual (vm affinity). Sin embargo, no es siempre la mejor estrategia, ya que estaremos compartiendo recursos en el host y puede que el rendimiento sea mejor si tenemos dos máquinas cada una dedicada a un rol puro de cliente o de servidor. Por ejemplo en un rol de cliente puede que solo necesitamos una cantidad de memoria pequeña y unos pocos cores pero “hipervitaminados” a nivel de frecuencia de trabajo.
En aquellos casos donde el número de filas (loops, filas en cursor) es elevado, tenemos un escenario cliente/servidor y el coste computacional de cada iteración es bajo, las pruebas muestran que el rendimiento del cliente es más determinante en los tiempos totales que el rendimiento del servidor. Es muy importante medir los tiempos “muertos” entre iteraciones de los cursores causados por el cliente. Es frecuente que en tunings de este tipo de procesos nos encontremos que el tiempo “en espera de enviar filas” sea un porcentaje muy elevado del tiempo total. Por tanto, es posible que sea más conveniente invertir en mejorar el hardware de nuestro cliente (middleware, servidor de aplicaciones, AOS, etc.) en vez de en el hardware del propio servidor de base de datos.
Finalmente, el uso de interfaces e infraestructura de red de mayor rendimiento (Infiniband, ethernet de 40/100 Gbps) en vez de tarjetas ethernet de 1 Gbps (siempre que funcionen correctamente) nos aportará ventajas relativamente moderadas en entornos OLTP pero que pueden llegar a ser potencialmente muy elevadas en entornos DW donde existen transferencias de datos de mayor volumen donde ethernet de 1 Gbps (o incluso de 10 Gbps) puede generar un cuello de botella importante.
