Descargue un PDF de este Artículo
La estrategia de profundidad en colas de E/S de SQL Server deja muchos temas abiertos. A continuación revisamos evidencias científicas sobre cómo mejorarlo con un modelo más amplio del sistema basado en estudios sobre los discos, los SSD y el rendimiento del almacenamiento de la información.
Estoy trabajando en la configuración de una matriz de unidades de estado sólido (SSD), empezando con unos pocos dispositivos, y aumentando hasta tal vez 20 unidades a través de dos controladores y puertos de serie 4 x 4 SCSI (SAS). Durante las pruebas iniciales, observé latencia de disco muy alta, en el rango de 100 ms o más para lecturas y hasta 400ms o más en escrituras en determinadas operaciones. Esto ocurre durante períodos de profundidad de cola de disco muy alta. Así, surge la pregunta:
1. ¿Mejora el rendimiento la alta profundidad de cola y la latencia?
2. ¿Causan problemas, incluida la disminución de la capacidad de respuesta,
con otras operaciones?
3. ¿Se pueden evitar estos problemas sin renunciar a rendimiento?
Vamos a comenzar la búsqueda de respuestas con algunos principios sobre el rendimiento de las unidades de disco duro, para continuar con arrays de discos, almacenamiento en caché de controladores RAID, y finalmente al sistema completo de almacenamiento de información, incluyendo redes locales de almacenamiento (SANs), teniendo en cuenta las características SSD. Podemos rastrear las causas del buen rendimiento en el almacenamiento habitual que a menudo se cita sin referencia a su alcance o a su contexto. Y con esta información, podremos entender por qué el modelo simple de profundidad en la cola de entrada/salida utilizado SQL Server deja muchos temas abiertos y podría mejorarse con un modelo más amplio de sistema basado en estudios sobre los discos, los SSD y el rendimiento del almacenamiento de la información.
Teoría IOPS del disco duro
La teoría estándar de los índices de E/S aleatoria en unidades de disco es que el tiempo medio de acceso es la suma de la latencia de rotación, el tiempo medio de búsqueda, el tiempo de transferencia, la sobrecarga de operaciones y las demoras en la propagación de búsquedas. Para E/S de bloques pequeños, sólo los dos primeros elementos contribuyen significativamente. La latencia de rotación promedio para una unidad de disco de 15 K es de 2 ms, y el del disco de 3,5 pulgadas típico de 15 K lleva un tiempo promedio de 3.4ms en búsquedas. Los dos ingredientes principales -más los otros- suman un tiempo de acceso promedio total de alrededor de 5.5ms. Los dos puntos clave son:
1. El acceso aleatorio a los datos distribuidos en todo el disco
2. En profundidad de cola 1, se emite una I/O; la siguiente se emite después de
la finalización de la primera.
Un disco de 15 K es capaz de 180 operaciones de E/S por segundo (IOPS) en profundidad de cola 1 para accesos aleatorios distribuidos por todo el disco. Pero demasiada gente se olvida de mencionar esos dos puntos principales.
Cuando los accesos de datos son a una gama limitada de cilindros de disco, el tiempo de búsqueda promedio es menor, y rendimiento de IOPS de profundidad 1 de cola es mayor.
Profundidad de IOPS vs Colas Aleatorias de lectura de disco
Ahora consideremos la implicación del segundo cuantificador: el de lectura aleatoria IOPS frente a la profundidad de la cola. A mayor profundidad de cola, hay más E/S pendientes de que finalización, ya sea lanzadas simultáneamente, o como consecuencia de otros procesos de E/S. El controlador de la unidad de disco puede reordenar la E/S, con un efecto neto de reducir el tiempo entre cada E/S para aumentar las IOPS, a expensas de una latencia superior para cada E/S individual.
Hay una pequeña ganancia en la profundidad de la cola 2, quizás a 200 IOPS; y mayores ganancias a profundidad de cola 4 en 240 IOPS; y alrededor de 40-50 de IOPS con cada duplicación de la profundidad de la cola a 32, con una pequeña ganancia en la profundidad de cola 64. (Desde 2005, las unidades de disco tiene una cola de tareas de 64 de profundidad; las actuales tienen una cola de 128 de profundidad). Con cada duplicación de la profundidad de la cola, casi se duplica la latencia.
La figura 1 muestra el impacto del efecto a corto plazo y la profundidad de la cola en IOPS. Aumentar la profundidad de la cola para E/S distribuidas en todo el disco mejora la IOPS a 400 en profundidad de cola 64. El efecto de recorrido corto mejora el rendimiento en la profundidad de la cola 1 a casi 300 IOPS con 2,8% de utilización. Cuando se combinan los dos efectos, es posible alcanzar unos 600 IOPS por disco.
La figura 2 muestra el efecto del recorrido corto y la profundidad de cola frente a la latencia de acceso. Aumentar la profundidad de cola para accesos de datos distribuidos por todo el disco tiene un precio de latencia alto. Restringir el acceso de datos a un rango estrecho reduce significativamente la latencia en profundidades de cola altas.
Individualmente, ambos efectos mejoran el rendimiento del disco duro, pero los dos combinados producen beneficios más evidentes. En transacciones en línea (OLTP), el procesamiento del tiempo de respuesta (y, por tanto, latencia de I/O de disco) es tan importante como el rendimiento. Por lo tanto, la industria aprobó la regla común de mantener un (tiempo) promedio de profundidad de cola por debajo de 2 por disco (haciendo caso omiso de picos transitorios) para OLTP. Esta regla fue habitual en los días de las unidades de 5400 rpm y 7.200 rpm.
En procesos por lotes, donde no hay una persona a la espera de completar cada transacción, la estrategia consiste en una profundidad de cola de disco alta para el mejor rendimiento. Sin embargo, en el almacenamiento de sistemas de datos y soporte de decisiones (DSSs), todas estas normas están fuera de lugar. Si existe capacidad no utilizada, se desperdician las prestaciones. Téngase en cuenta la importancia de los cuantificadores; sin embargo, todavía hay gente que piensa que es aceptable la norma de "profundidad de cola/disco por debajo de 2" sin calificación alguna.
Controladores RAID y arrays de discos
Hace tiempo, teníamos sólo un montón de discos (JBOD). Los principales RDBMS manejaban esta situación mediante el apoyo de varios archivos y varios grupos de archivos, para cada base de datos. Entonces se hizo la luz (me he equivocado de libro, lo siento) el RAID y los controladores RAID. Y la gente vio que el RAID era bueno, con menos "discos" para administrar a nivel de base de datos y del sistema operativo. Un array de discos es, para el sistema operativo, como un único disco, y los contadores de rendimiento normalmente se leen desde el sistema operativo, no del sistema de almacenamiento.
La regla de profundidad de cola 2 por disco (y sus calificadores) no se traducen directamente en la profundidad de la cola determinada por el contador de rendimiento de sistema operativo. Por lo que se hizo popular mencionar una regla de latencia: que los datos de acceso no deben ser superiores a, aproximadamente, 10-20 MS, correspondientes a la regla de profundidad de cola 2 por disco (probablemente en discos a 7200 rpm y 10 krpm), olvidando desde entonces que existen calificadores adicionales.
En general, la latencia de acceso a datos por debajo de 10 ms es, normalmente, una indicación de que el tiempo de respuesta de la transacción debe ser muy bueno. La latencia en el rango 10 MS-20 MS supone un tiempo de respuesta aceptable de transacción. La latencia de 20 MS corresponde a un disco muy cargado. Más importante aún, cualquier aumento transitorio podría empujar la E/S de disco hacia el rango de profundidad de cola muy alto, con picos fuertes en el tiempo de respuesta. Así que, incluso si el tiempo promedio de respuesta por transacción se considerase aceptable, podría haber una cola de distribución notable experimentando una capacidad de respuesta muy pobre.
Latencia de escritura de registro
En los tiempos de las conexión directas, el consejo era disponer de un par de discos RAID 1 dedicados para cada registro de transacciones (LOG) de volumen alto de la base de datos. Rara vez se mencionaba, pero una E/S de escritura del registro de tipo secuencial en bloques pequeños podía lograr latencias del orden de 0.3ms y alrededor de 3.000-5.000 IOPS.
Los proveedores de SAN frecuentemente sugerían no molestarse con discos físicos dedicados para cada LOG de alto volumen de transacciones. Confiaban en que todo estará bien. Cuando se usan discos dedicados e incluso con un procesador dedicado, el SAN todavía no puede conseguir latencias de escritura de registro muy bajas. Como los sistemas de almacenamiento SAN se fueron generalizando, Microsoft cambió SQL Server para permitir más escrituras al vuelo de los registros de transacciones. (Con el Service Pack 4 de SQL Server 2000 y en 2005 RTM, se permiten 8 registros de E/S por base de datos. SQL Server 2005 SP1 permite 8 para 32 bits y 32 para SQL Server de 64 bits y 480 KB. SQL Server 2008 permite 3840KB).
Escritura aleatoria en RAID con bloques pequeños
La sobrecarga de escritura a nivel de RAID está fuera del alcance de este artículo. Sin embargo, tenga en cuenta que aunque a la gente le gusta hablar de los RAID 5 y 10 sin reglas de calificación, la regla comúnmente citada relativa al rendimiento en escritura de RAID 5 sólo se aplica a las escrituras aleatorias de bloques pequeños. En un controlador de almacenamiento en caché esperaríamos tener E/S con características IOPS similares a la lectura, ajustada para la sobrecarga a nivel de RAID, tanto teóricamente, como para controladores específicos.
Almacenamiento en caché de controladores RAID y E/S de lectura
En otras discusiones en mi blog y en mi sitio Web, he explicado por qué la memoria caché de lectura es contraproducente. En esencia, el propio motor de base de datos es una caché de datos mucho más cercana y menos costosa de acceder que la memoria caché en el controlador de almacenamiento. Además, en un sistema configurado correctamente, el motor de base de datos debe tener una caché del búfer mucho mayor que el sistema de almacenamiento. Es muy poco probable que se acceda nuevamente a la caché de controlador de almacenamiento. Finalmente, el costo de almacenamiento en caché de lectura es importante en un sistema de almacenamiento configurado para un alto rendimiento de IOPS. La caché de lectura en los controladores de almacenamiento suponen una sobrecarga con bloques a los que casi nunca se accederá nuevamente.
El caché de lectura está deshabilitado generalmente en sistemas de “benchmarks” TPC por las razones que acabo de citar. Una fuente reputada afirmó que una caché pequeña, de 2 MB (¡no GB!) es la estrategia preferida par para permitir la lectura anticipada en LUN. Recuerdo que alguien dijo que un servidor específico con 48 GB de memoria mostró una mejora de rendimiento de E/S cuando el caché de SAN aumentó de 80 GB a 120 GB. Lo que demuestra este resultado podría argumentarse desde diferentes ángulos.
Controladores RAID de almacenamiento en caché y E/S de escritura
Y ahora vamos con las características de rendimiento de E/S en escritura para un controlador RAID de almacenamiento en caché. Hemos bordeado el tema de la E/S de escritura hasta ahora. Hay una razón para ello. La figura 3 muestra el patrón IOPS de escritura aleatoria de bloques pequeños con un controlador RAID de almacenamiento en caché.
Cuando SQL Server o los sistemas operativos envían una o más peticiones de escritura al controlador RAID, la E/S se escribe en la memoria caché de controlador, y se envía una señal de finalización al remitente (al que hizo la petición). Luego se envía la siguiente E/S. No hay casi ninguna variación en IOPS frente a profundidad de la cola. La latencia es muy baja hasta que el volumen de escritura llega al límite IOPS. Más allá de esto, se llena el caché de escritura y la latencia crece hasta el bloqueo del proceso.
Grandes sistemas de almacenamiento con grandes grupos RAID
Como los sistemas se volvieron más potentes, con un crecimiento de la capacidad de computación de 40% por año y rendimientos promedio de los discos duros de menos de 10% por año (de 7.200 a 10.000, luego a 15.000, y entonces nada hasta SSD), era necesario construir sistemas de almacenamiento de información con un gran número de discos. Durante este período, los sistemas SAN se generalizaron, especialmente para los sistemas de almacenamiento grandes.
Pronto se observó que el SAN no podría conseguir los rendimientos esperados de IOPS con un gran número de discos. Una de las causas se remonta a la configuración de profundidad de cola de adaptador de Bus de Host Fibre Channel (FC HBA) predeterminado de 32 (por adaptador, ahora por objetivo). Las argumentaciones seguían basándose en las ideas de los proveedores de SAN sobre el almacenamiento compartido. Para evitar que un host generase demasiada carga, la I/O se reguló con el ajuste de profundidad de cola HBA para que todos los hosts pudieran obtener una parte del volumen de E/S.
Si tuviésemos que medir el IOPS frente el ajuste de profundidad de cola HBA en LUNs configurados para muchos discos, deberíamos encontrar que el rendimiento de IOPS aumenta con la mayor profundidad de cola, hasta llegar a su máximo. Este comportamiento es el mismo que se ha descrito anteriormente en la sección de grupo RAID IOPS respeto al de la profundidad de cola.
Ajuste de profundidad de cola de FC HBA
Tenga en cuenta que en los primeros días, el ajuste de profundidad de cola HBA se aplicaba a cada puerto FC HBA o al propio HBA. En el más reciente Emulex FC HBA, el valor predeterminado ahora es la profundidad 32 de cola por LUN, con la opción por LUN o para todo el objetivo. (QLogic utiliza el término regulador de ejecución). Supongo que en uno de los pocos informes de benchmark TPC-C con sistema de almacenamiento SAN, se hizo una referencia a cambiar la profundidad de la cola HBA de 32 a 254 sin más explicaciones.
Los sistemas de TPC-C tienen arrays de discos muy grandes. Por supuesto, resulta adecuado marcar el ajuste de profundidad de cola HBA hasta el máximo. Finalmente, esto se nota, y la recomendación de cambiar la profundidad de la cola HBA de 32 a 255 se abrió paso en varios documentos de Microsoft. Los que he visto no dieron ninguna explicación sobre causas y efectos o sus medidas de soporte.
Así pues, ¿Qué sucede ahora en un SAN con un array de discos pequeños, especialmente si el ajuste de profundidad de cola es por LUN y cada LUN se compone de cuatro discos? ¿Se debe incrementar el valor de profundidad de cola a 254? Sugiero seguir mis directrices para IOPS y latencia frente a la profundidad de la cola, con ajustes para el número de discos por LUN. Y sopesar todo eso dependiendo de si su objetivo es la capacidad de respuesta OLTP o el rendimiento en procesos batch/DSS.
E/S secuenciales
Toda la discusión de IOPS frente a profundidad en cola hasta ahora no pertenecía a la E/S secuenciales de disco. Para E/S secuenciales de grandes bloques, una profundidad de cola de 1 por LUN podría ser suficiente para generar el máximo ancho de banda de E/S, si las E/S fueran lo suficientemente grandes como para abarcar todos los discos en el LUN. Me inclino a pensar que el producto del tamaño de E/S x profundidad de cola debe ser mayor que el número de discos de la matriz x el tamaño del RAID. El razonamiento es que cada disco tendrá E/S para procesar, pero no he verificado esta hipótesis.
Aumentar la profundidad de la cola más allá del mínimo necesario para alcanzar un valor cercano al ancho de banda máximo sólo servirá para aumentar la latencia. En una carga de trabajo mixto y grandes-bloques pequeños de E/S, tal vez una mayor profundidad de la cola en los bloques grandes podría mejorar el rendimiento, pero esto no ha sido estudiado. En un SAN, se ha sugerido que una mayor profundidad de la cola puede ser necesaria para alcanzar el máximo ancho de banda secuencial, junto con varios LUNs por grupo RAID. No se ha proporcionado una explicación satisfactoria.
Características de E/s SQL Server
Hay varios documentos de Microsoft que describen la E/S de SQL Server en detalle. Una selección podría incluir:
• El artículo técnico para SQL Server por Emily Wilson, Mike Ruthruff, Thomas
• Los Libros en Pantalla de SQL Server 2008 R2 tienen, bajo el apartado de
gestión del búfer, una discusión de Craig Freedman sobre
Random
En breve, en una operación de exploración de una tabla, SQL Server emitirá E/S para intentar permanecer 1024 páginas por delante de la exploración con Enterprise Edition y 128 páginas por delante en la Standard Edition.
SQL Server: E/S sincrónica y asincrónica
De acuerdo a las fuentes, tanto en los accesos aleatorios de 8 KB como para búsquedas por clave y para recolección de filas, SQL Server cambia de modo síncrono a modo asincrónico cada 25 filas, según una estimación.
Ahora consideremos la situación de un sistema de procesamiento de transacciones que también se ocupa de informes. Las operaciones consisten en varios E/S en serie emitidas en profundidad de cola 1. El informe es una sola consulta que genera varios cientos de E/S emitidas asincrónicamente en profundidad de la cola alta. Supongamos que con sólo las transacciones procesadas, la profundidad promedio de cola por disco es 1, y la latencia promedio es de 5 ms. Y supongamos que una transacción requiere 20 E/S sincrónica completas en 100 MS: un tiempo de respuesta razonable. Ahora el informe se ejecuta, generando la E/S asincrónica y produciendo una profundidad de cola 8 por disco y una latencia de 30 MS. El informe se ejecuta correctamente porque el sistema de almacenamiento genera 350 IOPS por disco. Pero la transacción con series de 20 peticiones de E/S en serie emitidas ahora tardan 600ms en completarse. En esencia, el informe tiene el efecto de tener prioridad más alta que el procesamiento de transacciones.
¿Y qué pasa con Tempdb?
Las E/S de SQL Server a Tempdb se producen a menudo en profundidad de cola alta. Esto es porque la consulta tiene operaciones de hash u ordenaciones. Si no fuesen grandes, el hash u ordenación se podría ser hecho en memoria. Así que la actividad en Tempdb es con frecuencia por consultas de gran tamaño que generan E/S asincrónica en profundidad de cola alta.
Si sólo conocía la regla simple de latencia de I/O por debajo de 20 MS, podría sacar la conclusión de que los discos de la Tempdb están sobrecargados porque las latencias promedio son muy altas. De hecho, lo que está sucediendo es que SQL Server simplemente está siguiendo la estrategia de mejor rendimiento mediante una métrica de rendimiento. La métrica adecuada consiste en determinar si Tempdb puede lograr un volumen suficiente de E/S, y no si la E/S de Tempdb debe ser baja.
Profundidad de cola alta SQL en SSD
En una consulta de exploración de una tabla sin sugerencias de bloqueo, se observó una profundidad de lectura de cola de más de 1300. El tamaño de E/S fue de 8 KB y leer la latencia se fue por encima de 200ms incluso en almacenamiento SSD. Con bloqueo de tabla, el tamaño de E/S era de alrededor de 500 K (probablemente en su mayoría 512 K más unos pequeños bloques de E/S), una latencia de disco de menos de 50ms y una profundidad de cola de alrededor de 40.
Para búsqueda por clave, observamos 8 KB de E/S, y la profundidad de la cola fue alrededor de 160 con 7ms latencia. Con el almacenamiento de disco duro y 20 o más discos, la profundidad de cola de 160 funciona a 8 por disco, un número razonable para buena E/S pero no una latencia excesiva.
Marc Bevand en el blog de Zorinaq señaló que las IOPS en profundidad de cola 1 son esencialmente una medida de latencia. Supongamos que un SSD está clasificado en la latencia de 100μs y 30 K de IOPS para 8 KB de E/S (30 K x 8 KB = 240 MB). A continuación, la profundidad de la cola 1 IOPS debe ser 10 K (1.000.000 μs/s / 100μs). Por tanto, la teoría es que se puede requerir llegar a 30 K IOPS en profundidad de cola 3 o superior. Mantener la profundidad de cola en el mínimo necesario para máximas IOPS no degrada el rendimiento de la consulta que poder genera el enorme volumen de E/S y para proporcionar una buena capacidad de respuesta para otras consultas simultáneas.
Alta latencia de escritura en la creación de índices agrupados
Se observó que el comando CREATE CLUSTERED INDEX generar una escritura de muy alta latencia. La profundidad de cola fue de 500, la latencia de 600ms + y el tamaño de E/S un promedio de 100 KB. Desde esta alta latencia no debería ocurrir durante una jornada de trabajo normal, es un motivo de preocupación. Aun así, no tiene sentido emitir tantos E/S significativas. Con un controlador RAID de almacenamiento en caché o SSD, el ancho de banda de E/S de escritura puede estar saturado incluso en profundidad de cola baja. Provocar E/S tan alta sólo hace que el sistema se vuelva lento en la respuesta para cualquier función que requiera E/S en las unidades afectadas.
Afinación de operaciones de Stat y asíncronas
Antes concluir esta mirada en profundidad de la cola de E/S, quiero plantear brevemente el tema de la afinación de operaciones de Stat. Mucha gente solo utiliza el ajuste de esperas con estadísticas como la métrica.
Considere el siguiente ejemplo. Nuestro sistema de almacenamiento se compone de 100 discos. Una consulta genera 1 millón de operaciones de E/S. Si la I/O se emite en forma sincrónica en profundidad de cola 1 por disco ó 100 al sistema total de almacenamiento, entonces la IOPS es 200 por disco o 20.000 para el sistema de almacenamiento y la latencia de disco es 5 ms. La consulta debería durar unos 50 segundos. El tiempo de espera total es de 5 ms por E/S, o 5.000 segundos para 1 M de E/S.
Ahora consideremos la E/S asíncrona, generando una profundidad de cola por disco de 16, para 400 IOPS por disco y latencia de 40ms. La consulta puede completar ahora 1 M de E/S en 25 segundos, pero el tiempo de espera total es de 40.000 segundos.
Es importante centrarse en la métrica verdadera y siempre evaluar contadores de rendimiento del sistema, no solo las estadísticas del tiempo de espera.
Resumen de profundidad de cola de E/S
Hemos explorado brevemente los componentes clave que se ven afectados por la estrategia de profundidad de cola de E/S. Los puntos principales a considerar son los siguientes:
• Los IOPS de lectura aleatoria en discos duros pueden mejorar el
funcionamiento en mayor profundidad de cola a expensas de la latencia.
• Las E/S secuenciales no son o no es necesario que requieran una operación de
profundidad de cola alta más allá de lo que es necesario para mantener
ocupados todos los discos. Permanecer 1024 páginas por delante en un
análisis de tabla parece razonable para E/S de grandes bloques, pero yo no
inundaría la cola con E/S de 8 KB. Esta estrategia debe ser ajustada según el
tamaño de E/S, o tal vez deberíamos preguntarnos por qué a veces se emiten
de E/S de 8 K, si la tabla no está fragmentada.
• Las escrituras aleatorias en un controlador RAID con caché de escritura no
necesitan una profundidad de cola profunda para obtener el mejor
rendimiento.
• Los sistemas de almacenamiento SSD no necesitan profundidad de cola muy
alta para máximo rendimiento.
SQL Server parece seguir una estrategia definida en profundidad de cola de E/S, dependiendo sólo de la edición (Standard o Enterprise). No es por el número de discos detrás de cada LUN; ni por el modelo de uso (OLTP frente a almacén de datos/DSS).
Sin embargo, aquí va una propuesta de estrategia a seguir para optimizar el rendimiento:
1. Las E/S secuenciales no deberían usar la estrategia de ir 1024 páginas por
delante si es el tamaño de E/S 8 KB.
2. Escribir E/S en controladores con memoria caché de escritura debe utilizar
menor profundidad de cola.
3. Es importante ajustar la profundidad de cola de las E/S aleatorias de lectura
basadas en el tipo de almacenamiento: unidad de disco duro o SSD.
4. Es útil ajustar la E/S de lectura aleatoria HDD por modelo de uso: OLTP o
DW / DSS.
5. Es útil ajustar la E/S de lectura aleatoria HDD basada en discos por LUN.
Algunos de los ajustes anteriores podrían detectarse automáticamente. Otros pueden requerir un parámetro, como el uso de sp_configure. Nos gustaría disponer de una respuesta universal aplicada independiente de la acción del usuario, pero realizar ajustes personalizados para sus necesidades podría mejorar considerablemente la facilidad de uso de SQL Server. Hoy, un número de operaciones puede hacer que SQL Server deje de responder completamente debido a las sobrecargas de cola de disco, incluso con almacenamiento SSD. Sólo sistemas de almacenamiento muy grandes, perfectamente configurados serían inmunes.
Sobre el Autor
Joe Chang (blog | website) es un consultor de SQL Server en Solid Quality Mentors y desarrollador de herramientas, especializándose en el modelado del rendimiento cuantitativo de bases de datos y el análisis, benchmarking, almacenamiento y rendimiento de E/S. Ha desarrollado varias herramientas gratuitas, incluyendo ExecStats para referencias cruzadas de planes de ejecución, SQL Trace para análisis de SQL Server Profilers, SQL Clone para estadísticas de distribución y SQLSystem para monitoreo de rendimiento en sistemas de análisis de índice.