Contenido Introducción .................................................................................................................................. ........................................................................................................................ .......... 3 Documentación Oficial de las Novedades............................................................... ..................................................................................... ...................... 4 El Equipo de SolidQ – Relacional en España.................................................................................. España.................................................................................. 5 Publicaciones Realizadas de SQL Server 2014....................................................................... 2 014............................................................................... ........ 6 Archivos de Datos como Blobs en Containers de Windows Azure ........................................... 6 Introducción .......................................................................................................................... .................................................................................................................. ........ 6 Escenarios Donde Puede Resultar Útil .......................................................................... .................................................................................. ........ 6 Puesta en Marcha.......................................................... Marcha.................................................................................................................. ........................................................ 7 Conclusiones....................................................................................................... Conclusiones.............................. ............................................................................................ ................... 8 Buffer Pool Extension .................................................................... ................................................................................................................ ............................................ 9 Introducción .......................................................................................................................... .................................................................................................................. ........ 9 Detalles de la Funcionalidad ................................................................................................. 9 Alternativas de Uso Eficiente de SSD .................................................................................. .................................................................................. 10 El Escenario que Podría ser Ideal ........................................................................................ 10 Análisis de Ventajas en Escenarios de Pruebas Estándar de la Industria............................ 10 Conclusiones....................................................................................................... Conclusiones.............................. .......................................................................................... ................. 12 Conectividad SQL Server a Cloud (Windows (W indows Azure)................................................................ 13 Introducción ........................................................................................................................ .................................................................................................................. ...... 13 Un Ejemplo y Pruebas de Impacto ...................................................................................... ...................................................................................... 13 Herramientas de Medición............................................. Medición.................................................................................................. ..................................................... 14 Elementos a Medir y Probar................................................................. ................................................................................................ ............................... 14 Conclusiones....................................................................................................... Conclusiones.............................. .......................................................................................... ................. 14 Durabilidad Diferida de Transacciones ................................................................................... 15 Introducción ........................................................................................................................ .................................................................................................................. ...... 15 Configuración a Nivel de Base de Datos.......................................................... .............................................................................. .................... 15 Definición de las Pruebas .................................................................................................... .................................................................................................... 15 Ejecución de las Pruebas y Análisis de Resultados.............................................................. 16 Conclusiones....................................................................................................... Conclusiones.............................. .......................................................................................... ................. 19 Eficiencia CPU en In-Memory................................................................... .................................................................................................. ............................... 20 Introducción ........................................................................................................................ .................................................................................................................. ...... 20 Escenario de Mejora Presentado en PASS y Análisis .......................................................... 20 Diseño de Arquitecturas basadas en ROI ............................................................................ ............................................................................ 23 Conclusión ........................................................................................................................... ........................................................................................................................... 24 In-Memory Databases con SQL Server 2014 ........................................................................... ........................................................................... 25
Introducción ........................................................................................................................ .................................................................................................................. ...... 25 El Caso de Uso que Probamos ......................................................................... ............................................................................................. .................... 25 Conclusión ........................................................................................................................... ........................................................................................................................... 28 IOPS bajo Control con Resource Governor ............................................................................. ............................................................................. 29 Introducción ........................................................................................................................ .................................................................................................................. ...... 29 Las Pruebas Realizadas ............................................................. ........................................................................................................ ........................................... 29 Conclusión ........................................................................................................................... ........................................................................................................................... 33 Reconstrucción de Índices Particionados en SQL Server 2014 ............................................... 34 Introducción ........................................................................................................................ .................................................................................................................. ...... 34 Instrucción para Reconstruir ONLINE una Partición Partic ión ........................................................... 34 Nuevas Opciones para Reconstruir Índices ................................................................... ......................................................................... ...... 35 Frenos que Detectamos en Clientes de Cara a Implementar Particionado ........................ 35 SQL Server In-Memory OLTP Engine ....................................................................................... ....................................................................................... 36 Introducción ........................................................................................................................ .................................................................................................................. ...... 36 Arquitectura a Alto Nivel y Comunicación entre los Engines .............................................. 36 La Motivación ................................................................................................................ ...................................................................................................................... ...... 36 Optimización de Estructuras en Memoria RAM ......................................................... .................................................................. ......... 37 Eliminar Latches y Bloqueos ................................................................. ................................................................................................ ............................... 38 Compilación de Código Nativo Máquina ......................................................... ............................................................................. .................... 38 Componentes Clave .................................................................. ............................................................................................................ .......................................... 39 Comparación de Rendimiento ............................................................................................ 40 Bibliografía y Documentación de Referencia ............................................................. ...................................................................... ......... 41 Tablas Temporales vs Tablas in-memory ................................................................................ ................................................................................ 42 Introducción ........................................................................................................................ .................................................................................................................. ...... 42 Escenario de Prueba .................................................................. ............................................................................................................ .......................................... 42 Pruebas y Análisis ................................................................................. ................................................................................................................ ............................... 42 Conclusiones....................................................................................................... Conclusiones.............................. .......................................................................................... ................. 46
Introducción La salida de SQL Server 20144 al mercado es inminente, y en los blogs de SolidQ hemos realizado publicaciones periódicas de funcionalidades que consideramos i nteresantes para nuestros clientes. Cuando se incorporan novedades a los productos el ciclo de adopción que solemos seguir se aproxima al siguiente: 1. Nos familiarizamos con la nueva característica 2. Evaluamos los casos de uso propuestos por el fabricante 3. Enlazamos/asociamos los casos de uso del fabricante con las necesidades que detectamos en clientes 4. Preparamos pruebas funcionales de la característica 5. Realizamos pruebas de rendimiento 6. Sacamos conclusiones y proponemos al siguiente nivel (decisores técnicos o decisores de negocio dependiendo de la orientación de la funcionalidad) 7. Realizamos pruebas pseudo-reales de la novedad – generalmente PoC (Proof of Concept) 8. Implementamos la funcionalidad en producción Dependiendo de la complejidad de la funcionalidad, el tiempo necesario puede variar mucho; pondremos dos ejemplos: 1. Implementar las mejoras relacionadas con backup/restore se puede realizar en menos de 2 días debido a la facilidad de las pruebas a realizar. 2. Implementar una PoC de “memory-optimized tables” en memoria puede llevar 1 semana de trabajo perfectamente porque se necesita: Analizar el modelo en cuestión o Realizar cambios necesarios para soportar la funcionalidad o Probar funcionalmente los cambios o Capturar y comparar la actividad antes y después del cambio o Evaluar el ROI – retorno/mejoras que trae el cambio o En el caso de SolidQ, para la mayoría de los aspectos nos encontramos entre el 4) y el 7); por ejemplo, conocemos la funcionalidad “buffer pool extension” pero no la hemos probado en cliente, y por ot ro lado, tenemos clientes muy interesados en “memory -optimized tables” con
los que ya estamos realizando pruebas de concepto. En definitiva, SQL Server 2014 incorpora novedades muy útiles que estamos evaluando para nuestros clientes; el objetivo de este ebook es compartir con la comunidad y nuestros clientes las averiguaciones/investigaciones y pruebas que hemos hecho de cara al lanzamiento de SQL Server 2014. Finalmente, queremos indicar que las pruebas y análisis realizados de rendimiento son con propósito de evaluación sobre SQL Server 2014 CTP2 y no se pueden utilizar para realizar comparativas oficiales; dicho de otra forma, estas pruebas no violan la claúsula Dewitt. Dewitt. Esperamos que disfrutéis de este documento al igual que lo hemos hecho nosotros evaluando primero y compartiendo después el conocimiento adquirido.
Documentación Oficial de las Novedades El motor de SQL Server 2014 incluye las siguientes novedades: http://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx Database Engine Feature Enhancements
Memory-optimized Tables SQL Server Data Files in Windows Azure Host a SQL Server Database in a Windows Azure Virtual Machine Backup and Restore Enhancements New Design for Cardinality Estimation Delayed Durability AlwaysOn Enhancements Partition Switching and Indexing Managing the Lock Priority of Online Operations Columnstore Indexes Buffer Pool Extension Incremental Statistics Resource Governor Enhancements for Physical IO Control Online Index Operation Event Class
Que podrían desglosarse en las siguientes categorías “no-oficiales”:
Alto Rendimiento Alta Disponibilidad y Escalabilidad Integración y aproximación a Windows Azure (IaaS y PaaS)
El Equipo de SolidQ – Relacional en España
Eladio Rincón
Enrique Catalá
Rubén Serna
Rubén Garrigós
Luis J. Morán
Publicaciones Realizadas de SQL Server 2014 Archivos de Datos como Blobs en Containers de Windows Azure Introducción SQL Server 2014 trae la posibilidad de utilizar/crear a rchivos de datos directamente en Windows Azure. Para detalles oficiales de esta nueva características le recomiendo visitar la documentación del producto (SQL Server Data Files in Windows Azure). De esta forma se puede utilizar archivos de datos (o log) en Azure y la máquina que proporciona el servicio (capacidad de cálculo) en tu CPD/hosting. Esta configuración formaría parte de uno de los escenarios híbridos de despliegues de SQL Server 2014.
Escenarios Donde Puede Resultar Útil Elaboramos los escenarios en los que se puede usar y sus consideraciones asociadas:
Capacidad de cómputo controlado: hay muchas dudas – infundadas la mayoría de ellas – respecto a la capacidad de cómputo de las VMs de SQL Server en Azure (IaaS). También existen dudas – más sostenidas – en cuanto a la capacidad de PaaS. Con esta
configuración, puedes tener un escenario en el que la capacidad de computo la tienes en tu CPD/hosting “controlada”, y la capacidad de almacenamiento en Windows
Azure.
Trasiego de información entre IaaS Azure y tu LAN. Al trabajar en WAN, las latencias
entre el cliente y el servidor de BBDD resulta muchas veces inviable. Un ejemplo típic o es Dynamics AX: si el cliente de AX y el servidor de BBDD están en WAN, como AX es una aplicación con mucho trasiego entre cliente y servidor el rendimiento decae notablemente. No queremos criticar AX sino mencionar un ejemplo típico. De hecho, Microsoft tiene un proyecto para ofrecer Dynamics AX en Windows Azure para Abril 2014. “Trayendo” el servidor de BBDD a tu LAN habilitas que el propio servidor distribuya los datos a lao clientes de su LAN, pero el servidor de BBDD necesitará alimentarse de los archivos de datos que están en Windows Azure. Dicho de otra forma: si SQL Server necesita leer datos que no están en memoria (acceso físico a archivos de datos) existirán latencias de acceso a Windows Azure para “recoger” los
datos a procesar y enviar a la aplicación cliente. Capacidad transaccional. No parece buena idea que los archivos de transacciones estén alojados en Windows Azure porque cada transacción realizada (COMMIT) antes de confirmar a aplicación cliente, debe asegurarse del estado ACI D (Durabilidad). Es decir, habría que valorar el índice de transacciones que tiene la aplicación antes de mover el archivo de log (registro de transacciones) a Windows Azure. Capacidad Analítica. Si la aplicación realiza eminentemente lecturas, y el trasiego de datos por memoria es alto, no es buena idea montar los archivos de datos en Windows Azure, porque con mucha frecuencia estarás solicitando datos a Windows Azure con la consecuente latencia WAN. Capacidad de almacenamiento. El precio del almacenamiento en Windows Azure es muy bajo comparando con el coste que puede suponer cada TB en un CPD (muy inferior a 1EUR / TB / Mes). Alta Disponibilidad y Recuperación de Desastres. Se ofrecer como una solución rápida para poder montar IaaS en Windows Azure directamente en caso de desastre; aunque el ofrecimiento es válido, soy más partidario de utilizar backups en Windows Azure que esta línea de argumento. Es difícil por los requerimientos de un OLTP subir el
registro de transacciones a Windows Azure, por lo que asegurarnos que el archivo esté disponible en Windows Azure, no parece suficiente. Sin embargo, si el SLA del cliente permite por ejemplo tener aseguradas copias de seguridad cada 15 minutos, SQL Server 2014, permite realizar “copias inteligentes” directamente a Windows Azure (lo
analizaremos en próxima publicación).
Puesta en Marcha Los pasos para crear la BBDD con archivos en Windows Azure son muy sencillos; necesitas lo siguiente: 1. Cuenta de Windows Azure Storage donde tengas un contenedor de archivos blob. 2. Crear una policy sobre el contenedor para crear una firma de acceso compartido (Shared Access Signature Key) sobre el contenedor. 3. Crear credenciales en SQL Server 2014 para enlazar tu instancia con Azure. 4. Crear la BBDD. Asumiendo que tiene una cuenta de Windows Azure Storage empezaremos por el punto 2); para ello – aunque hay una API de Windows Azure para automatizarlo – como es una acción poco frecuente, utilizarémos Azure Storage Explorer. Los pasos a realizar son los siguientes: Gestión Permisos sobre contenedor
Nivel de acceso a contenedor (privado)
Creación de policy: lectura y escritura
Generación de clave de la policy
Resuelta esta parte, podemos proceder a crear la BBDD desde SQL Server Management Studio: 1) Creación de la credencial:
CREATE CREDENTIAL [https://.blob.core.windows.net/] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sr=c&si=XXXXXXXXXXXXXXXXXXXXXXXXXX' Notas sobre este comando:
Utilizar https
Utilizar el texto de Identity exactamente “SHARED ACCESS SIGNATURE”
El dato SECRET debe empezar por sr=c “y lo que siga”; có pialo del último paso de la
creación del SAS 2) Creación de la BBDD oportuna:
CREATE DATABASE myDB_Azure ON ( NAME = myDB_Azure_data, FILENAME = 'https://.blob.core.windows.net//myDB_Azure.mdf', SIZE = 20MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) LOG ON ( NAME = myDB_Azure_log, FILENAME = N'C:\DBs\myDB_log_Azure.ldf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) Notas sobre este comando:
En nuestro caso, para la siguiente publicación crearemos el archivo de registro de transacciones en local.
Conclusiones Cuanto más importante te resulte tu aplicación, más seguro debes estar de los cambios tecnológicos; en la sección de “escenarios donde puede resultar útil” hemos enumerado lo que serían puntos fuertes y débiles de la tecnología. Como elemento crítico de esta nueva
posibilidad es las latencias que puedan suceder en lo que se asemeja a un entorno WAN. Por ello, debes evaluar qué te aporta y qué comprometes con la tecnología. En siguientes publicaciones haremos algunas pruebas para ver en qué escenarios nos puede servir.
Buffer Pool Extension Introducción En SQL Server 2014 aparece una nueva funcionalidad denominada Buffer Pool Extension que permite ampliar la capacidad de nuestro buffer pool mediante el uso de discos rápidos. Habitualmente situaremos esta extensión en discos SSD o en unidades que tengan garantizado un rendimiento muy elevado comparado con los discos que almacenan los archivos de datos. Esta característica extiende el buffer pool para almacenar únicamente páginas limpias, permitiéndonos mantener un Working Set mayor entre la memoria y el SSD. De esta forma se reducirá las operaciones aleatorias que se realicen sobre los archivos de datos, mejorando el rendimiento. En el siguiente gráfico podemos apreciar donde se sitúa esta caché de nivel 2 (L2) y cómo el acceso es transparente desde el punto de vista del Storage Engine:
Más detalles del funcionamiento de este tipo de cachés se puede encontrar en el documento de Microsoft Research: Turbocharging DBMS Buffer Pool Using SSDs
Detalles de la Funcionalidad En concreto el diseño utilizado en SQL Server 2014 es el denominado Clean-Write (CW) Design mencionado en el anterior documento, donde las páginas almacenadas en el SSD son siempre páginas limpias. Como en muchas caches, el dimensionamiento de éstas para que el rendimiento sea óptimo es importante. En la jerarquía de acceso (dentro de SQL Server, dejando de lado las cachés de los procesadores) tendríamos la L1 (memoria RAM), la L2 (Buffer Pool Extension) y finalmente la L3 podría ser el disco magnético. El tamaño recomendado, aunque debería ser ajustado mediante pruebas es que la L2 – la Buffer Pool Extension – sea entre 4 y 8 veces mayor que la memoria. Por otra parte, se están reportando resultados pobres en sistemas que disponen de cantidades de memoria ya bastante significativas (>64GB) por lo que esta funcionalidad creemos que podría tener un mejor desempeño en entornos virtuales donde la cantidad de memoria asignable puede ser más reducida que en un entorno físico.
Alternativas de Uso Eficiente de SSD También debemos tener en cuenta que si tenemos disponible almacenamiento rápido tipo SSD en el servidor también podríamos sacarle partido para tempdb o para logs de transacciones de bases de datos OLTP. Quizás no sea posible utilizarlo para datos o logs por ser almacenamiento local y nuestra instancia estar en failover cluster pero desde SQL Server 2012 si podemos usarlo para tempdb aunque estemos en una instancia en cluster. También podríamos utilizar este almacenamiento SSD para mantener las particiones o tablas más utilizadas por nuestra aplicación. En definitiva, si tenemos almacenamiento SSD el utilizarlo como Buffer Pool Extension no nos ayudará a aliviar problemas de congestión en tempdb o latencias excesivas en el log de transacciones, ambos problemas con los que nos encontramos muy frecuentemente.
El Escenario que Podría ser Ideal Para que esta funcionalidad pueda llegar a ser una funcionalidad realmente “killer” necesitamos que la implementación Lazy-Cleaning (LC) realizada para el documento de Microsoft, ya probada sobre SQL Server 2008 R2, pueda ver la luz en algún momento. Con esta implementación el SSD se utilizaría como una caché write-back de forma que se pudieran realizar mucho más rápidamente las operaciones de escritura a la vez que el acceso a las páginas sucias se podría realizar directamente desde el SSD y no únicamente desde la memoria. También necesitaríamos una política de “envejecimiento” de las páginas, de reemplazo en la caché dinámica, basada en el uso, la frecuencia del acceso y el tipo de acceso (priorizando los accesos aleatorios al SSD respecto a los secuenciales). En definitiva estaríamos utilizando el SSD como una caché real para el acceso a disco en todos sus sentidos , mejorando de forma importante la capacidad de entrada/salida a disco
Análisis de Ventajas en Escenarios de Pruebas Estándar de la Industria Como muestra de las ventajas que obtendríamos os muestro los dos siguientes gráficos que muestran el beneficio de la implementación LC (en gris) en TPC-C, TPC-E y TPC-H :
Conclusiones La versión actual de la Buffer Pool Extension puede ser útil en escenarios muy específicos donde la cantidad de memoria disponible (o a la que podemos ampliar la máquina) es claramente insuficiente para mantener el Working Set necesario para nuestra carga (que puede llegar a ser de cientos de GB en escenarios DW). En estos casos veremos esperanzas de vida de páginas muy bajos y una actividad sobre los ficheros de datos elevada. En escenarios donde la memoria está correctamente dimensionada las mejoras que podemos obtener con esta característica no serían apreciables por lo que en cierta forma podemos ver esta funcionalidad como una especie de “parche” que podemos aplicar en casos especiales (máquinas virtuales con poca memoria, entornos con Working S ets muy amplios…).
Conectividad SQL Server a Cloud (Windows Azure) Introducción Un factor importante a tener en cuenta cuando queremos migrar a un entorno Cloud como Windows Azure nuestros servidores de base de datos es la latencia y throughput de la red . Algunos escenarios típicos que podrían ser problemáticos:
Aplicaciones cliente-servidor de 2 capas donde el cliente es una aplicación de
escritorio y el servidor es un SQL Server. Lógica de acceso a datos que utiliza cursores de cliente o implementan programáticamente la lógica de acceso a datos de los cursores. Aplicaciones que tienen un bajo grado de paralelismo y tienden a realizar muchas operaciones en serie contra la base de datos. Informes, volcados, etc. que generen un trasvase de un gran volumen de datos entre el servidor y el cliente.
Un Ejemplo y Pruebas de Impacto Imaginemos un proceso que implementa una lógica de tipo cursor en la que itera a lo largo de 100 elementos realizando una sencilla consulta por la clave primaria a una tabla de una base de datos. En estos casos el tamaño de cada petición en base al protocolo TDS será de aproximadamente 8 KB. Con un MTU de 1500 bytes este paquete se descompondrá en 6 paquetes IP. Si procesamos fila a fila esperando la respuesta de la base de datos entre iteraciones la duración del proceso puede pasar desde unos pocos milisegundos en red local hasta más de un minuto en un entorno WAN lento:
En los casos en los que se generan consultas de cierto volumen el ancho de banda total disponible tiene un mayor impacto que la latencia. Por ejemplo si tenemos que devolver 8 MB de datos y realizamos 10 operaciones, presuponiendo una red de 100 Mbps, el impacto de la latencia empezará a ser perceptible a partir de unos 30-40ms:
Herramientas de Medición Aunque desde el punto de vista teórico es muy importante tener claro el impacto que tendrá el aumento de latencia y/o la limitación del throughput al final lo principal es realizar pruebas simulando esta latencia de red. Existen herramientas especializadas en simular enlaces de red con características especiales de latencia, de ancho de banda, jitter, pérdida de paquetes, etc. Por mencionar algunas herramientas tenemos SoftPerfect Connection Emulator, Network Simulator o incluso podemos utilizar la funcionalidad de emulación de redes de Visual Studio.
Elementos a Medir y Probar Por ejemplo si quisiéramos simular un enlace típico desde nuestro CPD a un servidor en la nube de Windows Azure deberíamos configurar:
Ancho de banda de subida/bajada. En general el ancho de banda disponible con Azure
es de al menos 100 Mbps, por lo que habitualmente deberemos configurar los límites de la conexión de nuestro proveedor. Latencia. La latencia con los CPDs de Azure desde España está alrededor de 60 ms para las conexiones habituales. Conviene testear este valor desde una máquina de Azure hasta nuestro CPD y viceversa para ajustarlo ya que en ciertas conexiones la latencia puede ser de hasta 100 ms. Errores, jitter, etc. En general las conexiones empresariales son fiables y no tienen pérdidas de paquetes ni un jitter significativo. Si nuestra aplicación cliente-servidor se tuviera que conectar mediante ADSL/3G o conexiones similares sí podría convenir parametrizar estos valores para simular mejor la realidad del canal.
Conclusiones Cuando se plantea la migración de un servidor de base de datos a la nube debemos tener en cuenta factores más allá que la cantidad de memoria, capacidad de procesamiento y de espacio en disco. Desgraciadamente el pasar de un entorno LAN a WAN puede tener un impacto mucho mayor que el número de CPUs o la cantidad de RAM de nuestra base de datos en la nube. Realizar pruebas, bien mediante una prueba de concepto (PoC) desplegando nuestra base de datos en la nube o mediante el uso de herramientas de simulación de redes es fundamental para que nuestra migración sea exitosa.
Durabilidad Diferida de Transacciones Introducción En ocasiones tenemos sistemas que soportan una carga de escritura elevada en hardware con una entrada/salida inadecuada. Esto produce una percepción de rendimiento pobre cuando las latencias de escritura en el log de transacciones son elevadas. La nueva funcionalidad de Durabilidad Diferida de Transacciones en SQL Server 2014 reduce el impacto de la latencia al mantener en memoria los registros del log de transacciones, empaquetándolos para escribirlos en disco en batches, de forma que se reduzcan el número de operaciones necesarias. La confirmación de las transacciones se realiza tan pronto se escriben los registros en memoria, sin esperar a la confirmación del disco, por lo que solo deberíamos aplicarlo cuando cierta pérdida de datos es aceptable. En cierta forma podemos verlo como una mejora del buffer para transacciones in-flight donde la confirmación no se retrasa al flush de dicho buffer a disco.
Configuración a Nivel de Base de Datos Afortunadamente la aplicación de la durabilidad diferida puede configurarse de forma granular. A nivel de base de datos podemos deshabilitarla (valor por defecto), permitir su uso o forzar su uso para todas las transacciones:
ALTER DATABASE SET DELAYED_DURABILITY = {DISABLED | ALLOWED | FORCED}
Definición de las Pruebas Para comprobar que ventaja de cara al rendimiento nos aporta esta funcionalidad vamos a configurar tres bases de datos distintas cuya única diferencia será la ubicación física del fichero de log:
La más lenta de ellas almacenará el log en un disco de 5400 rpm, la intermedia en un disco SSD y la tercera en un disco SSD con caché DRAM. Para la prueba utilizaremos una tabla con 1 millón de registros sobre la que realizaremos UPDATEs aleatorios con varios threads. Para crear la tabla utilizaremos el siguiente script:
CREATE TABLE test ( id INT IDENTITY (1,1) PRIMARY KEY, contador INT ) GO INSERT INTO test (contador) SELECT TOP 1000000 0 FROM sys.objects s1, sys.objects s2, sys.objects s3, sys.objects s4, sys.objects s5
Ejecución de las Pruebas y Análisis de Resultados En la ejecución de este script ya podemos ver diferencias en base a la calidad del tipo de almacenamiento. En el caso del disco de 5400 rpm la inserción ha necesitado 13 segundos, en el caso del SSD 8 segundos y en el caso del SSD con caché únicamente 4 segundos. Para generar los updates de forma aleatorios utilizaremos el siguiente script el cual encapsularemos en un procedimiento almacenado:
SET NOCOUNT ON; GO DECLARE @i INT = 0 WHILE (@i < 10000) BEGIN UPDATE test SET contador = contador + 1 WHERE id=CONVERT(INT,RAND()*1000000) SET @i = @i + 1 END A continuación lo que haremos es lanzar con 1, 10, 20 y 40 threads simultáneos la ejecución de este procedimiento para las tres bases de datos. Repetiremos este proceso tres veces y promediaremos el resultado. Una vez tengamos estos datos, activaremos l a durabilidad diferida y repetiremos el proceso. A continuación os muestro gráficamente los resultados obtenidos, comenzando con la configuración de durabilidad completa:
Como podemos ver con durabilidad completa la utilización de medios más rápidos para el log de transacciones nos aporta un aumento del número de updates por segundo bastante considerable, especialmente en el momento que tenemos concurrencia de más de un thread. Vemos también como el disco lento con 10 threads ya se satura y no se producen aumentos significativos aumentando el número de threads.
El siguiente gráfico muestra los resultados con durabilidad diferida:
Lo primero que apreciamos es que ya con concurrencias muy bajas, con un 1 solo thread ejecutando 10.000 UPDATEs en serie, conseguimos valores de más de 20.000 UPDATEs por segundo, independientemente del disco utilizado. Vemos también que al aumentar a 10 threads concurrentes obtenemos un incremento de los UPDATEs por segundo muy significativo. Con concurrencias mayores a 10 vemos que en el caso de discos rápidos, la curva se aplana casi totalmente mientras que en el caso del disco lento sigue creciendo hasta los 40 threads. Al final el throughput máximo que podemos obtener sigue ordenado en el orden lógico en función del tipo de almacenamiento pero las diferencias existentes entre ellos son mucho menores que con la durabilidad completa. Observamos también que, con diferencia, el escenario más beneficiado por esta funcionalidad es el del disco lento, que consigue acercarse mucho al rendimiento de los otros discos:
Como prueba adicional hemos repetido la creación de la tabla inicial con la inserción de 1.000.000 de registros. Podemos ver como el uso de durabilidad diferida prácticamente equipara los tiempos a la inserción del escenario más rápido. En base a ello consideramos que los escenarios de potencial uso se amplían a aquellos donde las operaciones de modificación (inserción, modificación y borrado) tengan un peso importante.
Conclusiones La conclusión general que extraemos de esta funcionalidad es que la mejora en el rendimiento nos ha sorprendido muy gratamente. Esperábamos aumentos de rendimiento pero no esperábamos tanta diferencia en el caso de medios rápidos como el SSD + DRAM. Las pruebas muestran que el impacto de esta funcionalidad es muy elevado en aquellas cargas que generan muchas operaciones de entrada/salida en el log. El empaquetado de estas operaciones y la confirmación anticipada de la transacción producen que las escrituras sean de mayor tamaño con lo que el tiempo de respuesta del disco no es tan importante comparado con el throughput. En casos de operaciones puntuales de un tamaño mayor
también el almacenamiento previo en RAM aporta ventajas de rendimiento. En resumen, observamos que en sistemas donde el cuello de botella de la carga sean las escrituras en el log y sea permisible una pequeña cantidad de pérdida de datos esta funcionalidad marcará una gran diferencia de rendimiento a igualdad de hardware .
Eficiencia CPU en In-Memory Introducción Ante el próximo lanzamiento de SQL Server 2014 la mayoría hemos leído bastante sobre las nuevas tablas In-Memory (Heckaton). En base a los resultados quedaba claro que existían ventajas claras en dos escenarios principalmente:
Escenarios de alta concurrencia con muchas esperas por bloqueos/latches. Escenarios donde se necesita aumentar el throughput, aunque no se sufriera de bloqueos/latches.
En la práctica, ambos escenarios suelen darse de forma simultánea. Ante el primer escenario, y presuponiendo que las consultas e indexación sean las correctas, las esperas propias de un modelo pesimista se pueden aliviar utilizando concurrencia optimista (niveles de aislamiento SNAPSHOT o READ COMMITED SNAPSHOT). Ante el segundo escenario tenemos menos herramientas en el modelo clásico y las opciones se reducen a asegurarnos que disponemos del plan de ejecución más efectivo para la operación a realizar y el apoyo de los índices apropiados.
Escenario de Mejora Presentado en PASS y Análisis En la Keynote de PASS de David DeWitt se presentaron unas mediciones muy interesantes orientadas precisamente a este segundo escenario.
La primera de ellas mide el coste de una operación básica (evaluación de un predicado) utilizando un plan de ejecución interpretado (T-SQL clásico) sobre tablas clásicas o tablas en memoria versus un plan de ejecución nativo (procedimientos compilados) sobre tablas en memoria:
Podemos ver que la reducción en el número de instrucciones a ejecutar por fila llega a reducirse casi 10 veces. Esto es fundamental cuando nuestro predicado no es un predicado que podamos optimizar al 100% con un índice y necesitamos realizar siempre un filtrado de las N filas obtenidas del índice para obtener el resultado final. Imaginemos por ejemplo la siguiente consulta que busca aquellas personas que tengan apellidos que van de la “g” hasta la “y” y que además su nombre contenga la letra "ñ”: USE [AdventureWorks2012] GO SELECT [FirstName],[MiddleName] ,[LastName] FROM [AdventureWorks2012] .[Person].[Person] where LastName between 'g' and 'z' and firstname like '%ñ%'
Suponiendo que tenemos un índice que nos permita buscar por apellido, lo que veremos en el plan de ejecución será algo como esto:
En las propiedades del index seek veremos que tenemos una parte del predicado convertido en un seek predicate y otra parte en un predicado de “filtrado”:
Si el número de filas devuelto por el seek predicate es elevado, el c oste de CPU para evaluar fila a fila si se cumple el filtro %ñ% será elevado. Es en estos escenarios donde creemos que debido a la reducción de CPU obtenida al combinar planes de ejecución compilados y tablas en memoria será muy apreciable de cara al throughput total. De la misma Keynote vemos que la eficiencia en los lookups de filas aumenta cuanto mayor es el número de filas devueltas por el lookup:
En el caso de operaciones de escritura (updates) mencionar que si no tenemos colisiones significativas (fijaros que se trata de random updates) el limitante en el rendimiento está en el disco, en las escrituras en el log. Debemos tomar estas cifras como válidas solo para escenarios donde sea viable perder los datos (estados de sesión?) y no como una mejora general aplicable en operaciones habituales de UPDATE:
Finalmente me gustaría comentar la última slide de la keynote:
La tendencia hardware está clara y es seguir aumentando el número de cores disponibles, memoria más barata y un rendimiento por core menor respecto al número de cores ofrecidos. Esto hace que todo sistema que dependa de la “velocidad bruta por core” va a
quedar rezagado por las soluciones que puedan explotar este nuevo modelo. No debemos olvidar que venimos de una racha de muchos años donde los mayores incrementos de rendimiento se producían precisamente por el aumento de la frecuencia de trabajo y la mayor cantidad de instrucciones ejecutadas por ciclo (y no por el número de procesadores).
Diseño de Arquitecturas basadas en ROI Desde el punto de vista de SQL Server on-premise debemos tener en cuenta que este aumento en el número de cores también tiene un componente de coste de licenciamiento importante. Por tanto lo mejor que podemos hacer es diseñar nuestra plataforma de forma que sea capaz de ajustarse a la tipología de máquina que, sumándole el coste de licencias, nos dé un mejor rendimiento global. Por poner un ejemplo, en el caso de SQL Server 2012 o superior una máquina con 2 procesadores con 4-6 cores físicos con Hyperthreading (gama intel E5 v2) con una buena cantidad de caché L3 y un alta frecuencia de trabajo puede ser mucho más eficiente desde el punto de vista de rendimiento/coste comparado con otras alternativas. Obviamente debemos contar con una solución optimizada que pueda trabajar con este tipo de máquinas, sin olvidar que estas configuraciones rinden tanto o más que máquinas de 4 sockets de hace solo unos pocos años. Si no podemos alcanzar la potencia suficiente con un único servidor, lo ideal es que nuestra aplicación se diseñe para pueda trabajar contra distintas bases de datos/instancias de SQL Server (escalabilidad horizontal). Desgraciadamente vemos cada día aplicaciones que sufren de cuellos de botella en las bases de datos causados por operaciones que serían fácilmente cacheables en la capa de negocio. Es decir, muchas veces la carga que se lanza contra la base
de datos dista mucho de ser la “carga mínima” que se requeriría a nivel de capa de datos si
utilizáramos cacheo en otros niveles.
Conclusión En resumen, en casos donde la escalabilidad es muy importante es necesario diseñar arquitecturas distribuidas, replicadas y/o particionadas en varios niveles. No podemos confiar que únicamente las mejoras del hardware y el software de base de datos vayan a solucionar nuestra escalabilidad a futuro. En aquellos casos donde la escalabilidad no es tan crítica podremos obtener aumentos espectaculares de rendimiento con una buena optimización y con el aprovechamiento de las mejoras de SQL Server 2014 .
In-Memory Databases con SQL Server 2014 Introducción Una de las novedades más relevantes de SQL Server 2014 es la posibilidad de utilizar tecnología de bases de datos en memoria. Esta tecnología va mucho allá del cacheo de datos tradicional de SQL Server. Esta nueva funcionalidad puede resultar algo compleja de entender a bajo nivel ya que supone un cambio bastante radical. Sin embargo, el equipo de producto ha hecho un gran trabajo para que desde el punto de vista funcional sea relativamente sencilla de utilizar.
El Caso de Uso que Probamos En este post no vamos a entrar en el bajo nivel de la implementación y nos vamos a centrar en intentar solucionar un caso muy específico donde el motor tradicional mostraba sus limitaciones. El caso que queremos analizar es el comportamiento de inserciones multithread sobre una tabla con una columna identity que es clave primaria y a su vez índice cluster. Vamos a crear primero la tabla de forma tradicional con el identity: CREATE TABLE TEST (ID int identity primary key)
Y realizaremos la siguiente inserción de forma concurrente: INSERT INTO TEST DEFAULT VALUES
Si realizamos 1.000.000 de inserciones de 1 fila con 100 threads concurrentes (10.000 inserciones por thread) el tiempo total de dicho proceso en mi equipo de prueba es de 223 segundos. Esto nos da un tiempo medio por inserción de 0.22 milésimas de segundo lo cual a priori no es un mal tiempo. Debemos tener en cuenta que el equipo de pruebas cuenta con un disco SSD lo cual ayuda a mantener la latencia de disco en valores razonables. Si observamos las esperas veremos que la mayoría de esperas son de tipo PAGELATCH_EX lo cual es lo esperable en este escenario en particular. A continuación vamos a crear esta misma tabla utilizando la nueva funcionalidad de tablas inmemory. El primer paso es añadir un FILEGROUP nuevo a nuestra base de datos para almacenar este nuevo tipo de tablas:
ALTER DATABASE test ADD FILEGROUP test_memory CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE test ADD FILE (name='test_memory', filename='c:\test_memory') TO FILEGROUP test_memory El primer escollo que nos encontramos en una “traducción directa” es que el uso identity no
está soportado en tablas en memoria. Si intentamos crear una tabla in-memory con identity obtenemos el siguiente error:
CREATE TABLE dbo.test ( id INT IDENTITY NOT NULL, CONSTRAINT PK PRIMARY KEY HASH (id) WITH (BUCKET_COUNT = 1000000) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
Como workaround, crearemos la tabla sin identity e insertaremos utilizando como tope el valor máximo existente en la tabla en un procedimiento compilado atómico:
CREATE TABLE dbo.test ( id INT NOT NULL, CONSTRAINT PK PRIMARY KEY HASH (id) WITH (BUCKET_COUNT = 1000000) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO CREATE PROCEDURE [dbo].test_insert WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english') INSERT INTO dbo.test (id) SELECT TOP 1 id+1 FROM dbo.test ORDER BY id DESC END Desgraciadamente nuestra aproximación no es válida ya que en cuanto la tabla contiene más de 8000 filas obtenemos el siguiente error:
Otra alternativa sería el uso de secuencias, que sí están soportadas, pero no lo están en procedimientos almacenados compilados. Por tanto necesitaríamos de un procedimiento almacenado clásico que hiciera de “wrapper” para utilizar la secuencia:
CREATE SEQUENCE seq_1 AS INT START WITH 1 INCREMENT BY 1; GO CREATE PROCEDURE [dbo].test_insert (@id int) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SERIALIZABLE, LANGUAGE = 'us_english') INSERT INTO dbo.test (id) values (@id) END
GO CREATE PROCEDURE test_insert_wrapper AS BEGIN DECLARE @id INT = NEXT VALUE FOR seq_1 EXEC test_insert @id END Con esta alternativa el tiempo de ejecución de un millón de inserciones de 1 fila con 100 threads concurrentes es de 280 segundos. Indicar que en este caso el cuello de botella está en la CPU que se mantiene al 100% durante todo el proceso (4 cores) y es lo que probablemente hace que la solución in-memory no escale mejor. En un sistema con muchos procesadores el resultado podría ser muy distinto. No olvidemos que esta tecnología está diseñada pensando en estos escenarios donde tenemos una cantidad de CPU muy elevada pero que no podemos explotar debido a contención interna del motor. Otra alternativa distinta sería no utilizar secuencias y gestionar nosotros mismos nuestra secuencia con otra tabla en memoria de tipo “contador”. El problema con esta solución es que
deberíamos implementar una lógica de reintentos para los casos en los que colisionen las operaciones de update sobre el contador (que con 100 threads concurrentes son bastante frecuentes). Una de las características de las tablas en memoria es que utilizan una aproximación optimista a la concurrencia. Esta alternativa nos ofrece rendimiento peor en nuestro caso, de más de 370 segundos para el millón de inserciones. Este tipo de soluciones solo serían válidas en los escenarios donde la concurrencia optimista no genere una colisión en la mayoría de casos:
CREATE TABLE dbo.contador ( id int NOT NULL, contador int not null CONSTRAINT PK_2 PRIMARY KEY HASH (id) WITH (BUCKET_COUNT = 1000000) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO INSERT INTO dbo.contador VALUES (1,1) GO CREATE PROCEDURE [dbo].test_insert2 WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SERIALIZABLE, LANGUAGE = 'us_english') INSERT INTO dbo.test (id) SELECT top 1 contador from dbo.contador UPDATE dbo.contador SET contador=contador+1 WHERE id=1 END GO
CREATE PROCEDURE test_insert_wrapper2 AS BEGIN DECLARE @retry INT = 10 WHILE (@retry > 0) BEGIN BEGIN TRY EXEC test_insert2 SET @retry = 0 END TRY BEGIN CATCH SET @retry -= 1 IF (@retry > 0) IF (@@TRANCOUNT>0) ROLLBACK TRANSACTION ELSE THROW END CATCH END END
Conclusión Aunque la tecnología in-memory busca solucionar problemas de contención no siempre va a ser viable implementarla ni tiene que mejorar necesariamente el rendimiento de nuestra solución actual. Será necesario realizar pruebas específicas, con hardware lo más similar al de producción, para asegurarnos que la solución con tablas en memoria es claramente ventajosa.
En nuestra experiencia en muchos casos los problemas de rendimiento en las inserciones vienen de la mano de problemas con la entrada/salida a disco en el servidor. Si ésta es deficiente y tiene latencias excesivas, un throughput deficiente, etc. los tiempos de respuesta de las operaciones de inserción, actualización o borrado serán altos.
IOPS bajo Control con Resource Governor Introducción Una de las nuevas funcionalidades que se incluirán en SQL Server 2014 es la posibilidad de controlar la cantidad de IOPS por volumen que un determinado resource pool puede utilizar.
Las Pruebas Realizadas Antes de nada, prepararemos nuestro entorno de pruebas sobre un SQL Server 2014 CTP2. Crearemos una tabla e insertaremos unas cuantas filas:
Como vamos a forzar un escenario que genere bastantes IOPS, vamos a añadir un índice que luego forzaremos a utilizar en nuestra query :
Nuestra carga será un simple bucle WHILE que ejecutará una consulta y borrará la caché (para forzar la IO a disco):
En el plan de ejecución vemos que se leerá el índice ix_test y se realizarán lookups sobre el índice cluster. Este plan es muy ineficiente pero lo que buscamos es precisamente con dicha ineficiencia generar bastantes IOPS:
Si lanzamos esta carga sin limitar con Resource Governor vemos que obtenemos aproximadamente unas 1600 IOPS por segundo de media:
A continuación vamos a crear una función clasificadora que devuelva siempre el mismo nombre de pool. Esto sería un sinsentido en un entorno de producción, pero es adecuado para la prueba:
El siguiente paso es intentar configurar los límites de IOPS para un Resource Pool. Desgraciadamente si acudimos al interfaz gráfico vemos que no han contemplado dicha opción (esperamos que sí aparezca en RTM):
Deberemos por tanto crear el Resource Pool y el Workload Pool de forma manual. Especificaremos el mínimo y máximo de IOPS dentro del margen que tenemos de IOPS sin limitar. Nuestra intención es que no consumamos más de 1000 IOPS desde este pool y le garanticemos un mínimo de 500:
Una vez configurado, lanzamos la carga con Resource Governor activado y vamos activándolo y desactivándolo sucesivamente para ver cómo realiza el control de las IOPS:
Podemos apreciar que realmente sí realiza un control de las IOPS, pero cuando habilitamos Resource Governor nos está limitando a 320 IOPS aproximadamente pese a que le indicamos un máximo de 1000 y un mínimo de 500. Si lanzamos varias sesiones concurrentes vemos que vamos subiendo de IOPS hasta llegar a unas 750, donde más o menos se estabiliza, quedando aún por debajo del máximo de 1000 establecido.
Conclusión La funcionalidad en CTP2 para limitar las IOPS por volumen de Resource Governor funciona aunque no al 100% (hecho sin embargo normal en versiones no finales). Parece que quedan algunos flecos para afinar de forma que nos acerquemos más a los valores realmente configurados en el Resource Group. Cuando esté disponible la versión final RTM volveremos a analizar el comportamiento de esta nueva característica de SQL Server 2014 para ver si cumple lo prometido.
Reconstrucción de Índices Particionados en SQL Server 2014 Introducción Uno de los problemas históricos desde que se incorporó el particionado nativo en SQL Server 2005 ha sido el no disponer de reconstrucción online de particiones individuales. Esto no hacía tener que elegir entre procesos de reorganización (que son siempre online) o bien realizar en ventanas de mantenimiento las reconstrucciones offline sobre las particiones que se encontraran fragmentadas. Afortunadamente en SQL Server 2014 se permiten las operaciones de reconstrucción de índices online partición a partición. Esta ha sido una de las características más demandadas por la comunidad de SQL Server.
Instrucción para Reconstruir ONLINE una Partición Con SQL Server 2014 podemos reconstruir online una de nuestras particiones de la siguiente forma: ALTER INDEX MyIndex ON MyTable REBUILD PARTITION = 3 WITH (ONLINE = ON)
Además se ha añadido una opción interesante para las operaciones de SWITCH y de reconstrucción online llamada WAIT_AT_LOW_PRIORITY:
Nuevas Opciones para Reconstruir Índices La instrucción tiene las siguientes nuevas opciones:
La opción WAIT_AT_LOW_PRIORITY nos permite que la operación espere con baja prioridad un tiempo determinado (MAX_DURATION) a poder adquirir el bloqueo de esquema (mínimo necesario para poder acometer la operación online). La opción ABORT_AFTER_WAIT nos permitirá determinar qué hacer si llegamos al tiempo máximo definido y no hemos podido adquirir aún el bloqueo. La opción NONE nos permitirá seguir esperando el bloqueo, eso si ya con prioridad normal. La opción SELF nos permitirá abortar la operación que estamos intentando realizar. La opción BLOCKERS realizará un KILL de todas las transacciones que nos impiden continuar.
De cara a futuro sería interesante que esta característica de WAIT_AT_LOW_PRIORITY se pudiera extender a otras operaciones que también suelen sufrir de bloqueos. Me refiero por ejemplo a operaciones como añadir a una tabla una columna. Este tipo de operaciones en sistemas con carga continua puede llegar a eternizarse o incluso requerir de intervención manual (matar procesos bloqueadores). Con estas opciones y otras (como la conocida WITH ROLLBACK IMMEDIATE para ALTER DATABASE) lo que se busca es facilitar la labor del administrador (o de sus scripts de administración) dando prioridad a ciertas operaciones sobre otras.
Frenos que Detectamos en Clientes de Cara a Implementar Particionado Con esta nueva opción en SQL Server 2014 mejoramos el mantenimiento del particionado en lo que a fragmentación respecta. En base a nuestras experiencias en clientes, existen otros frenos importantes a la implantación de particionado. Uno de ellos es la no gestión de estadísticas a nivel de partición y, aunque es posible que se esté trabajando en ello, no vendría mal algo de apoyo en Connect: https://connect.microsoft.com/SQLServer/feedback/details/468517/update-statistics-at-thepartition-level Otro freno a la implantación de particionado es que el particionado no se auto -mantiene automáticamente. Es decir, habitualmente se comienza definiendo para cada tabla/índice a particionar la función de particionado y el esquema de particionado. Estos elementos, aunque se suelen compartir entre varios objetos particionados, requieren que de forma manual se les den mantenimiento. Esto ocurre tanto si se trata de un escenario de ventana deslizante como si se trata de un particionado fijo por meses/trimestres/años, etc. Aunque existen ejemplos de cómo automatizar este mantenimiento creemos que dicha funcionalidad, para al menos los casos básicos, debiera estar incluida en el producto y existieran un conjunto de plantillas predefinidas para facilitar el particionado. Es decir, por ejemplo una plantilla debería permitirnos siguiendo un wizard particionar por fecha una tabla a nivel de mes y mantener una ventana de X meses dentro de la ventana. De forma automática se generarían los nuevos filegroups (si así se configurara) y se modificaría el esquema de particionado para ir añadiendo/borrando particiones. En cierta forma sería una especie de plan de mantenimiento pero orientado a particiones. Un hecho constatable es que el volumen de datos manejados por las bases de datos sigue creciendo por lo que esperamos que en sucesivas versiones de SQL Server el particionado siga mejorando y nos permita aplicarlo en un mayor número de escenarios.
SQL Server In-Memory OLTP Engine Introducción “In-Memory OLTP” es un nuevo motor relacional optimizado para datos residentes en
memoria y cargas de trabajo tipo OLTP. Téngase presente que este motor surge como la motivación en Microsoft, de llegar a aumentar 100 veces el rendimiento de SQL Server en entornos puramente OLTP. El motor en memoria incluido en SQL Server 2014, al contrario de lo que ofrecen otros fabricantes se encuentra completamente integrado en el producto y por tanto no se trata de un sistema distinto. Se trata de un motor relacional completamente transaccional y persistente, que se utiliza exactamente igual que el motor on-disk (mediante T-SQL) y que a simple vista, se encuentra completamente integrado en el producto, posibilitando una aparente transparencia a la hora de utilizarlo.
Arquitectura a Alto Nivel y Comunicación entre los Engines Aunque se trata de un motor completa y radicalmente diferente al actual, ambos motores se comunican perfectamente y se permite por ejemplo realizar consultas que toquen objetos (tablas por ejemplo) de tipo on-disk e in-memory de forma transparente.
Diagrama de comunicación entre el motor in-memory (aka “Hekaton” o “in-memory”) y SQL Components (aka on-disk)
La Motivación Las razones de la aparición de este nuevo motor es que como parte de un proyecto interno de conseguir un aumento de rendimiento de 100X en SQL Server se realizaron investigaciones previas para determinar la mejor forma de hacerlo. Durante esa investigación, el equipo de Microsoft Research llegó a la conclusión de que la única forma de optimizar el rendimiento actual de SQL Server era por estas 3 vias: 1. Reducir el número de ciclos por instrucción 2. Mejorar la escalabilidad (uso de recursos HW mejor aprovechados)
3. Disminuir el número de instrucciones ejecutadas por peticion De dicho análisis, extrajeron la conclusión de que con los puntos 1 y 2, en el mejor de los casos se podrían llegar a obtener 3-4x rendimiento OLTP y que el punto 3 podia llegar a co nseguir un 10X de rendimiento, pero reduciendo un 99% el nº de instrucciones a ejecutar…lo cual aparte
de ser complejo hasta cotas insospechadas, dista mucho del ratio de 100X de mejora de rendimiento buscado. Los principios de diseño con los que se ha creado el nuevo motor In-memory son los siguientes: 1. Optimización de estructuras en memoria RAM 2. Eliminar latches y bloqueos 3. Compilación de código nativo maquina
Optimización de Estructuras en Memoria RAM Los motores relacionales actuales basan su diseño en asumir que los datos se encuentran en disco y se crean estructuras en memoria para mapearlos y que su acceso sea mucho más óptimo. En SQL Server conocemos a dicha área como buffer pool, y es donde residen las páginas con las que básicamente opera el motor relacional on- disk. Entonces…si actualmente ya se trabaja en memoria RAM… ¿por qué un motor en memoria? Parecería que esto no tiene sentido hasta que entramos al detalle de realmente el funcionamiento de dicho motor on-disk. Al asumir estructuras de disco trasladadas a RAM, se incrementa obviamente la complejidad de los mecanismos de control de los datos. Hay que pensar que cuando se diseñaron la mayoría de motores relacionales se carecía principalmente de memoria RAM y se crearon estructuras como los buffer pool para optimizar utilizando el poco tamaño RAM de que s disponia. Pero esto ha cambiado y nos encontramos en una época donde no es difícil comprar hardware con 1Tb de memoria RAM, donde la mayoría de bases de datos OLTP caben de sobra. En estos escenarios se pone el foco con el motor in-memory, escenarios donde la RAM ya no es el problema sino la propia arquitectura on-disk del motor relacional tradicional. No quiero entrar en detalles internos porque llevaría mucho tiempo, pero básicamente tenemos que tener en cuenta que tradicionalmente en un entorno con alta concurrencia, el acceso a una página de datos en RAM debe ser protegida por ejemplo de otros hilos mientras es accedida (concepto de latch). En entornos con alto número de CPUs e hilos accediendo simultáneamente a las mismas estructuras, resulta que el mismo proceso de protección de datos se convierte en cuello de botella del sistema (recordemos que se busca un aumento de rendimiento del 100x) Teniendo esto presente, el motor in-memory OLTP que aparece en SQL Server 2014 posee estructuras pensadas para trabajar directamente en RAM, que no tienen equivalencia directa con nada de lo que se ha visto hasta ahora. Estas estructuras se conocen como in-memory indexes y tenemos de dos tipos:
HASH Bw-tree
NOTA: La durabilidad de los datos se mantiene mediante logging y checkpoints a una
estructura parecida al log de transacciones pero altamente optimizado. Esto queda de momento fuera de este artículo
Eliminar Latches y Bloqueos Dado que hoy por hoy no es dificil tener decenas o cientos de CPU en máquinas de procesamiento OLTP, el problema que salpica como comentamos es que en escenarios de una altísima concurrencia, los propios locks a estructuras de datos de memoria compartida son un cuello de botella importante. Construir un motor OLTP pensando en escalabilidad máxima pasa por tanto por eliminar latches y bloqueos…y es lo que se consigue con el motor In -memory OLTP. En este motor, conceptos como el memory allocator, los índices hash o Bw-tree o los transaction map no se rigen por locks y latches. El motor in-memory OLTP está basado en: 1. Control de concurrencia optimista 2. Multiversion de filas 3. Estructuras latch-free NOTA: En este motor no existen muchos de los conceptos a los que estamos acostumbrados: latch, lock, fill-factor,…
Compilación de Código Nativo Máquina Hasta ahora, tal como lo conocemos, un motor OLTP suele basar sus planes de ejecución en un intérprete que evalúa en tiempo de ejecución el algoritmo seleccionado de acceso a datos. Este tipo de intérpretes poseen una ventaja muy alta debido a su gran flexibilidad pero tienen un problema cuando tenemos puesta la vista en conseguir 100X rendimiento: El número de instrucciones a ejecutar para el acceso más liviano (un simple lookup) tiene más de 1000 instrucciones, con lo que el número de instrucciones a ejecutar es realmente elevado. Al principio del artículo mencionábamos que una de las formas de optimizar el rendimiento era precisamente eliminar el 99% de las instrucciones a ejecutar. Precisamente este motor posee su propio sistema de compilación nativo de instrucciones, para reducir al máximo el nº de ellas y que el rendimiento sea espectacular. El siguiente diagrama (publicado en el paper de Microsoft Research) nos muestra cómo ha cambiado el proceso de generación de código para SP en in-memory OLTP
Lo que se ilustra a grandes rasgos es que una vez mandado a compilar un SP de tipo i nmemory, este acaba siendo compilado a código C en forma de .dll y cargado al SQLOS. Por tanto, lo que a priori era una representación de plan de ejecución, ahora contiene el propio algoritmo de acceso a los datos de forma estática. Lo siguiente es un ejemplo cualquiera de un SP compilado in-memory:
CREATE PROCEDURE [dbo].test_insert WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english') INSERT INTO dbo.test (id) SELECT TOP 1 id+1 FROM dbo.test ORDER by id DESC END
Componentes Clave In-memory OLTP engine posee (al menos en esta CTP2 de SQL Server 2014) los siguientes componentes:
Metadata: Sus objetos son compatibles con el motor on-disk e incluyen las mismas estructuras como tablas, tipos, procedimientos almacenados…Se diferencian de los
objetos on-disk en las restricciones que poseen y las cláusulas para indicar que deben ser creadas en el motor in-memory Query optimization: Si se diseña el código para utilizar únicamente objetos inmemory, el compilador realiza la máxima optimización posible y de puede por tanto obtener el máximo rendimiento de este nuevo motor Query interop: Dado que nada nos limita a realizar consultas T-SQL que toque objetos del motor on-disk e in-memory, el motor query interop se encarga de utilizar el mismo query plan interpretado, pero para acceso a tablas in-memory. De esta forma se pueden acceder a dichas estructuras aunque no se obtenga todo el potencial del motor Transacciones: Se permiten transacciones entre ambos motores Alta disponibilidad: El motor in-memory OLTP está completamente integrado en AlwaysOn, por lo que podemos tener una solución AlwaysOn que haga uso de tablas en memoria Almacenamiento y log: Para mantener la persistencia de los datos en memoria, el motor in-memory OLTP utiliza un sistema de log de transacciones de forma especial. En lugar de escribirlo todo en el log como en un sistema transaccional normal, se utiliza un sistema de checkpoints bajo un grupo de ficheros especial (filestream por ahora) y únicamente se realiza flush al log de transaccciones en el momento de realizar el commit…y además se realiza en bloques, para buscar el máximo
rendimiento posible.
Comparación de Rendimiento Las siguientes tablas de rendimiento las he extraído del propio paper de Microsoft Research sobre “Hekaton” y aunque no estamos e n versión RTM, podemos hacernos una idea de lo que podremos conseguir con este nuevo motor OLTP. Sin duda, estamos de enhorabuena en lo que a mejoras OLTP se avecina en esta futura release de SQL Server 2014.
Bibliografía y Documentación de Referencia
Documento de Microsoft Research sobre “Hekaton” Keynote del Dr Dewitt en PASS2013
Tablas Temporales vs Tablas in-memory Introducción Con la llegada de SQL Server 2014 y las tablas in-memory cabe preguntarse si podrán dichas tablas sustituir escenarios típicos donde se utilizan actualmente tablas temporales. En nuestra experiencia en consultoría vemos habitualmente como las tablas temporales se utilizan como contenedores de resultados intermedios dentro de procesos más complejos. En estos casos se suele o bien crear la tabla temporal de forma explícita con un CREATE TABLE o bien se crea al vuelo mediante el uso de la estructura SELECT INTO.
Escenario de Prueba Con las nuevas tablas in-memory tenemos la posibilidad de definir tablas cuya persistencia sea únicamente de esquema. Es decir, la información que grabemos en ellas nunca se materializará en disco, trabajarán únicamente en memoria. Este tipo de tablas considerarlas como una alternativa más a las tablas temporales tradicionales y usarlas en escenarios en los que la no persistencia de los datos en caso de caída del servicio no es un problema. Para complementar el abanico de posibilidades que tenemos, añadiremos también otro escenario que consiste en almacenar tempdb en memoria volátil. Este sería el caso al utilizar un disco RAM o ciertos aceleradores basados en memoria RAM/flash conectados al bus PCI-E. En definitiva lo que buscamos es minimizar el impacto que tendría un sistema lento de entrada/salida sobre el rendimiento de tempdb. De esta forma podremos evaluar si tempdb, por diseño, nos limita considerablemente el rendimiento o es el sistema de entrada/salida el mayor limitante. Durante las pruebas que hemos realizado hemos tenido en cuenta otros factores como por ejemplo el que la tabla deba crearse y destruirse cada vez o no o que los datos sean aleatorios o no. El objetivo es concluir si determinados patrones se ajustan o no a cada uno de los métodos que hemos comentado y si existe, en el caso de las tablas in-memory, de un impacto sustancial por colisiones de hash.
Pruebas y Análisis Comenzamos analizando el impacto que tiene la creación y destrucción del objeto temporal. El siguiente gráfico nos muestra como el coste en el caso de la tabla in-memory es muy superior al coste de crear una tabla temporal tradicional.
El coste fijo que tenemos que asumir por crear una tabla in-memory es de aproximadamente 1200 ms en mi equipo de prueba, lo cual hace la alternativa in-memory poco válida si tenemos que crear y destruir el objeto frecuentemente. Podemos ver también que el rendimiento del método SELECT INTO se hace más patente a medida que el número de filas aumenta gracias a las optimizaciones que el motor puede hacer en este caso. Vamos a considerar en adelante que el objeto puede mantenerse creado permanentemente o que la frecuencia de creación/destrucción va a ser muy baja respecto al número de accesos. El siguiente gráfico nos muestra los tiempos medios en función del tamaño de fila (100 bytes o 1000 bytes) y el número de filas insertadas:
Podemos ver como en todos los casos el SELECT INTO es la alternativa más rápida, seguida por las tablas in-memory y dejando en última posición la creación explícita de la tabla temporal y la posterior inserción de datos .
El siguiente gráfico muestra las duraciones según el tamaño de fila para los dos métodos de tablas temporales “clásicas” tanto con tempdb en RAM como en disco:
Podemos ver que realmente las diferencias no son apreciables para volúmenes pequeños y únicamente en el caso de que estemos declarando la tabla temporal de forma explícita y realizando un INSERT posteriormente notamos diferencias de rendimiento. Debemos tener
en cuenta que estas pruebas se han realizado en un sistema que no tiene problemas de congestión de entrada/salida a disco. Si hubiésemos realizado estas pruebas en uno de los muchos servidores en los que la entrada/salida está infra-dimensionada el resultado sería muy distinto con seguridad. La conclusión de este punto es que salvo que tengas un mal rendimiento de disco o necesites insertar millones de filas en tus tablas temporales el situar tempdb en un disco RAM o disco muy rápido no va a marcar una diferencia notable en el rendimiento. Por último he querido comparar, para el caso de tablas in-memory, el impacto del uso de datos aleatorios (que minimizan las colisiones de hash) versus el uso de datos idénticos:
Los datos obtenidos indican que para pequeñas cantidades de filas, el impacto de las colisiones no es significativo. Únicamente en el caso de las inserciones de 1 millón de filas con tamaño de fila de 1.000 bytes se aprecia una degradación si los datos no son aleatorios. Si consideramos el volumen de bytes insertados (tamaño de fila multiplicado por el número de fila) podemos ver como para tablas de 1 MB o menos las diferencias son ínfimas pero a partir de 10 MB se van separando claramente cada alternativa, siendo la mejor en todos los casos el SELECT INTO, el intermedio la tabla in-memory y el peor la tabla temporal explícita más el insert:
Por último hemos querido medir también el rendimiento de un SELECT count (*) sobre las tablas en función del método utilizado. El siguiente gráfico muestra como en todos los casos la tabla temporal clásica es más rápida que la tabla in-memory:
Conclusiones A modo de resumen, destacaría las siguientes conclusiones y matizaciones:
El método más eficiente en general para almacenar el resultado de una consulta de forma temporal es el SELECT INTO. El funcionamiento es bueno en todos los casos, para conjuntos pequeños de filas, grandes, con filas anchas o estrechas. Las operaciones tanto de inserción como de consulta en T-SQL nativo sobre tablas temporales resultan más eficientes con tablas temporales clásicas que c on tablas inmemory. Si nuestro proceso completo se puede codificar en un procedimiento almacenado nativo donde únicamente accedemos a tablas in-memory el rendimiento con muy alta probabilidad será mejor. Por desgracia este es un escenario muy limitado y no puede considerarse como el caso habitual del uso de tablas temporales. En ocasiones se requiere crear indexación adicional sobre la tabla temporal. En estos casos en función del número de filas insertadas, el número de índices, etc. puede ser más eficiente definir primero la tabla con los índices o bien añadirlos posteriormente a la inserción de los datos. El rendimiento de tempdb se puede ver muy mermado si la entrada/salida a disco está muy saturada. Este no es el escenario que se muestra en las pruebas pero sí es un escenario que desgraciadamente nos encontramos en muchos entornos de producción. En estos casos aliviar esta saturación moviendo tempdb a un sistema no saturado o a un disco RAM puede dar grandes beneficios desde el punto de vista de rendimiento percibido por el usuario. Las colisiones hash en “tablas in -memory temporales” tienen un impacto bastant e reducido, no siendo significativo salvo cuando el volumen es del orden de millones de filas. En un escenario de tablas con persistencia y/o con concurrencia de distintos procesos el impacto de las colisiones puede ser mucho más significativo.
